001 // Copyright 2004, 2005 The Apache Software Foundation
002 //
003 // Licensed under the Apache License, Version 2.0 (the "License");
004 // you may not use this file except in compliance with the License.
005 // You may obtain a copy of the License at
006 //
007 // http://www.apache.org/licenses/LICENSE-2.0
008 //
009 // Unless required by applicable law or agreed to in writing, software
010 // distributed under the License is distributed on an "AS IS" BASIS,
011 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
012 // See the License for the specific language governing permissions and
013 // limitations under the License.
014
015 package org.apache.tapestry.contrib.table.model.sql;
016
017 import java.sql.Connection;
018 import java.sql.ResultSet;
019 import java.sql.SQLException;
020 import java.sql.Statement;
021
022 import org.apache.commons.logging.Log;
023 import org.apache.commons.logging.LogFactory;
024 import org.apache.tapestry.contrib.table.model.ITablePagingState;
025 import org.apache.tapestry.contrib.table.model.ITableSortingState;
026 import org.apache.tapestry.contrib.table.model.simple.SimpleTableState;
027
028 /**
029 *
030 * @author mindbridge
031 */
032 public class SimpleSqlTableDataSource implements ISqlTableDataSource
033 {
034 private static final Log LOG =
035 LogFactory.getLog(SimpleSqlTableDataSource.class);
036
037 private ISqlConnectionSource m_objConnSource;
038 private String m_strTableName;
039 private String m_strWhereClause;
040
041 public SimpleSqlTableDataSource(
042 ISqlConnectionSource objConnSource,
043 String strTableName)
044 {
045 this(objConnSource, strTableName, null);
046 }
047
048 public SimpleSqlTableDataSource(
049 ISqlConnectionSource objConnSource,
050 String strTableName,
051 String strWhereClause)
052 {
053 setConnSource(objConnSource);
054 setTableName(strTableName);
055 setWhereClause(strWhereClause);
056 }
057
058 /**
059 * @see org.apache.tapestry.contrib.table.model.sql.ISqlTableDataSource#getRowCount()
060 */
061 public int getRowCount() throws SQLException
062 {
063 String strQuery = generateCountQuery();
064 LOG.trace("Invoking query to count rows: " + strQuery);
065
066 Connection objConn = getConnSource().obtainConnection();
067 try
068 {
069 Statement objStmt = objConn.createStatement();
070 try
071 {
072 ResultSet objRS = objStmt.executeQuery(strQuery);
073 objRS.next();
074 return objRS.getInt(1);
075 }
076 finally
077 {
078 objStmt.close();
079 }
080 }
081 finally
082 {
083 getConnSource().returnConnection(objConn);
084 }
085 }
086
087 /**
088 * @see org.apache.tapestry.contrib.table.model.sql.ISqlTableDataSource#getCurrentRows(SqlTableColumnModel, SimpleTableState)
089 */
090 public ResultSet getCurrentRows(
091 SqlTableColumnModel objColumnModel,
092 SimpleTableState objState)
093 throws SQLException
094 {
095 String strQuery = generateDataQuery(objColumnModel, objState);
096 LOG.trace("Invoking query to load current rows: " + strQuery);
097
098 Connection objConn = getConnSource().obtainConnection();
099 Statement objStmt = objConn.createStatement();
100 return objStmt.executeQuery(strQuery);
101 }
102
103 /**
104 * @see org.apache.tapestry.contrib.table.model.sql.ISqlTableDataSource#closeResultSet(ResultSet)
105 */
106 public void closeResultSet(ResultSet objResultSet)
107 {
108 try
109 {
110 Statement objStmt = objResultSet.getStatement();
111 Connection objConn = objStmt.getConnection();
112 try
113 {
114 objResultSet.close();
115 objStmt.close();
116 }
117 catch (SQLException e)
118 {
119 // ignore
120 }
121 getConnSource().returnConnection(objConn);
122 }
123 catch (SQLException e)
124 {
125 LOG.warn("Error while closing the result set", e);
126 }
127 }
128
129 protected String quoteObjectName(String strObject)
130 {
131 return strObject;
132 }
133
134 /**
135 * Returns the tableName.
136 * @return String
137 */
138 public String getTableName()
139 {
140 return m_strTableName;
141 }
142
143 /**
144 * Sets the tableName.
145 * @param tableName The tableName to set
146 */
147 public void setTableName(String tableName)
148 {
149 m_strTableName = tableName;
150 }
151
152 /**
153 * Returns the connSource.
154 * @return ISqlConnectionSource
155 */
156 public ISqlConnectionSource getConnSource()
157 {
158 return m_objConnSource;
159 }
160
161 /**
162 * Sets the connSource.
163 * @param connSource The connSource to set
164 */
165 public void setConnSource(ISqlConnectionSource connSource)
166 {
167 m_objConnSource = connSource;
168 }
169
170 /**
171 * Returns the whereClause.
172 * @return String
173 */
174 public String getWhereClause()
175 {
176 return m_strWhereClause;
177 }
178
179 /**
180 * Sets the whereClause.
181 * @param whereClause The whereClause to set
182 */
183 public void setWhereClause(String whereClause)
184 {
185 m_strWhereClause = whereClause;
186 }
187
188 protected String generateColumnList(SqlTableColumnModel objColumnModel)
189 {
190 // build the column selection
191 StringBuffer objColumnBuf = new StringBuffer();
192 for (int i = 0; i < objColumnModel.getColumnCount(); i++)
193 {
194 SqlTableColumn objColumn = objColumnModel.getSqlColumn(i);
195 if (i > 0)
196 objColumnBuf.append(", ");
197 objColumnBuf.append(quoteObjectName(objColumn.getColumnName()));
198 }
199
200 return objColumnBuf.toString();
201 }
202
203 protected String generateWhereClause()
204 {
205 String strWhereClause = getWhereClause();
206 if (strWhereClause == null || strWhereClause.equals(""))
207 return "";
208 return "WHERE " + strWhereClause + " ";
209 }
210
211 protected String generateOrderByClause(ITableSortingState objSortingState)
212 {
213 // build the sorting clause
214 StringBuffer objSortingBuf = new StringBuffer();
215 if (objSortingState.getSortColumn() != null)
216 {
217 objSortingBuf.append("ORDER BY ");
218 objSortingBuf.append(objSortingState.getSortColumn());
219 if (objSortingState.getSortOrder()
220 == ITableSortingState.SORT_ASCENDING)
221 objSortingBuf.append(" ASC ");
222 else
223 objSortingBuf.append(" DESC ");
224 }
225
226 return objSortingBuf.toString();
227 }
228
229 protected String generateLimitClause(ITablePagingState objPagingState)
230 {
231 int nPageSize = objPagingState.getPageSize();
232 int nStart = objPagingState.getCurrentPage() * nPageSize;
233 String strPagingBuf = "LIMIT " + nPageSize + " OFFSET " + nStart + " ";
234 return strPagingBuf;
235 }
236
237 protected String generateDataQuery(
238 SqlTableColumnModel objColumnModel,
239 SimpleTableState objState)
240 {
241 String strQuery =
242 "SELECT "
243 + generateColumnList(objColumnModel)
244 + " FROM "
245 + getTableName()
246 + " "
247 + generateWhereClause()
248 + generateOrderByClause(objState.getSortingState())
249 + generateLimitClause(objState.getPagingState());
250
251 return strQuery;
252 }
253
254 protected String generateCountQuery()
255 {
256 String strQuery =
257 "SELECT COUNT(*) FROM "
258 + getTableName()
259 + " "
260 + generateWhereClause();
261
262 return strQuery;
263 }
264
265 }