|
1
15 package org.apache.tapestry.contrib.table.model.sql;
16
17 import java.sql.Connection  ;
18 import java.sql.ResultSet ;
19 import java.sql.SQLException ;
20 import java.sql.Statement ;
21
22 import org.apache.commons.logging.Log;
23 import org.apache.commons.logging.LogFactory;
24 import org.apache.tapestry.contrib.table.model.ITablePagingState;
25 import org.apache.tapestry.contrib.table.model.ITableSortingState;
26 import org.apache.tapestry.contrib.table.model.simple.SimpleTableState;
27
28
32 public class SimpleSqlTableDataSource implements ISqlTableDataSource
33 {
34 private static final Log LOG =
35 LogFactory.getLog(SimpleSqlTableDataSource.class);
36
37 private ISqlConnectionSource m_objConnSource;
38 private String m_strTableName;
39 private String m_strWhereClause;
40
41 public SimpleSqlTableDataSource(
42 ISqlConnectionSource objConnSource,
43 String strTableName)
44 {
45 this(objConnSource, strTableName, null);
46 }
47
48 public SimpleSqlTableDataSource(
49 ISqlConnectionSource objConnSource,
50 String strTableName,
51 String strWhereClause)
52 {
53 setConnSource(objConnSource);
54 setTableName(strTableName);
55 setWhereClause(strWhereClause);
56 }
57
58
61 public int getRowCount() throws SQLException
62 {
63 String strQuery = generateCountQuery();
64 LOG.trace("Invoking query to count rows: " + strQuery);
65
66 Connection objConn = getConnSource().obtainConnection();
67 try
68 {
69 Statement objStmt = objConn.createStatement();
70 try
71 {
72 ResultSet objRS = objStmt.executeQuery(strQuery);
73 objRS.next();
74 return objRS.getInt(1);
75 }
76 finally
77 {
78 objStmt.close();
79 }
80 }
81 finally
82 {
83 getConnSource().returnConnection(objConn);
84 }
85 }
86
87
90 public ResultSet getCurrentRows(
91 SqlTableColumnModel objColumnModel,
92 SimpleTableState objState)
93 throws SQLException
94 {
95 String strQuery = generateDataQuery(objColumnModel, objState);
96 LOG.trace("Invoking query to load current rows: " + strQuery);
97
98 Connection objConn = getConnSource().obtainConnection();
99 Statement objStmt = objConn.createStatement();
100 return objStmt.executeQuery(strQuery);
101 }
102
103
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 }
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
138 public String getTableName()
139 {
140 return m_strTableName;
141 }
142
143
147 public void setTableName(String tableName)
148 {
149 m_strTableName = tableName;
150 }
151
152
156 public ISqlConnectionSource getConnSource()
157 {
158 return m_objConnSource;
159 }
160
161
165 public void setConnSource(ISqlConnectionSource connSource)
166 {
167 m_objConnSource = connSource;
168 }
169
170
174 public String getWhereClause()
175 {
176 return m_strWhereClause;
177 }
178
179
183 public void setWhereClause(String whereClause)
184 {
185 m_strWhereClause = whereClause;
186 }
187
188 protected String generateColumnList(SqlTableColumnModel objColumnModel)
189 {
190 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 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 }
266 |
Popular Tags |
Java API By Example, From Geeks To Geeks.