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