1 | |
|
2 | |
|
3 | |
|
4 | |
|
5 | |
|
6 | |
|
7 | |
|
8 | |
|
9 | |
|
10 | |
|
11 | |
|
12 | |
|
13 | |
|
14 | |
|
15 | |
package org.apache.tapestry.contrib.table.model.sql; |
16 | |
|
17 | |
import org.apache.commons.logging.Log; |
18 | |
import org.apache.commons.logging.LogFactory; |
19 | |
import org.apache.tapestry.contrib.table.model.ITablePagingState; |
20 | |
import org.apache.tapestry.contrib.table.model.ITableSortingState; |
21 | |
import org.apache.tapestry.contrib.table.model.simple.SimpleTableState; |
22 | |
|
23 | |
import java.sql.Connection; |
24 | |
import java.sql.ResultSet; |
25 | |
import java.sql.SQLException; |
26 | |
import java.sql.Statement; |
27 | |
|
28 | |
|
29 | |
|
30 | |
|
31 | |
public class SimpleSqlTableDataSource implements ISqlTableDataSource |
32 | |
{ |
33 | |
|
34 | 0 | private static final Log LOG = LogFactory |
35 | 0 | .getLog(SimpleSqlTableDataSource.class); |
36 | |
|
37 | |
private ISqlConnectionSource m_objConnSource; |
38 | |
private String m_strTableName; |
39 | |
private String m_strWhereClause; |
40 | |
|
41 | |
public SimpleSqlTableDataSource(ISqlConnectionSource objConnSource, |
42 | |
String strTableName) |
43 | |
{ |
44 | 0 | this(objConnSource, strTableName, null); |
45 | 0 | } |
46 | |
|
47 | |
public SimpleSqlTableDataSource(ISqlConnectionSource objConnSource, |
48 | |
String strTableName, String strWhereClause) |
49 | 0 | { |
50 | 0 | setConnSource(objConnSource); |
51 | 0 | setTableName(strTableName); |
52 | 0 | setWhereClause(strWhereClause); |
53 | 0 | } |
54 | |
|
55 | |
|
56 | |
|
57 | |
|
58 | |
public int getRowCount() |
59 | |
throws SQLException |
60 | |
{ |
61 | 0 | String strQuery = generateCountQuery(); |
62 | 0 | LOG.trace("Invoking query to count rows: " + strQuery); |
63 | |
|
64 | 0 | Connection objConn = getConnSource().obtainConnection(); |
65 | |
try |
66 | |
{ |
67 | 0 | Statement objStmt = objConn.createStatement(); |
68 | |
try |
69 | |
{ |
70 | 0 | ResultSet objRS = objStmt.executeQuery(strQuery); |
71 | 0 | objRS.next(); |
72 | 0 | return objRS.getInt(1); |
73 | |
} |
74 | |
finally |
75 | |
{ |
76 | 0 | objStmt.close(); |
77 | |
} |
78 | |
} |
79 | |
finally |
80 | |
{ |
81 | 0 | getConnSource().returnConnection(objConn); |
82 | |
} |
83 | |
} |
84 | |
|
85 | |
|
86 | |
|
87 | |
|
88 | |
|
89 | |
public ResultSet getCurrentRows(SqlTableColumnModel objColumnModel, |
90 | |
SimpleTableState objState) |
91 | |
throws SQLException |
92 | |
{ |
93 | 0 | String strQuery = generateDataQuery(objColumnModel, objState); |
94 | 0 | LOG.trace("Invoking query to load current rows: " + strQuery); |
95 | |
|
96 | 0 | Connection objConn = getConnSource().obtainConnection(); |
97 | 0 | Statement objStmt = objConn.createStatement(); |
98 | 0 | return objStmt.executeQuery(strQuery); |
99 | |
} |
100 | |
|
101 | |
|
102 | |
|
103 | |
|
104 | |
public void closeResultSet(ResultSet objResultSet) |
105 | |
{ |
106 | |
try |
107 | |
{ |
108 | 0 | Statement objStmt = objResultSet.getStatement(); |
109 | 0 | Connection objConn = objStmt.getConnection(); |
110 | |
try |
111 | |
{ |
112 | 0 | objResultSet.close(); |
113 | 0 | objStmt.close(); |
114 | |
} |
115 | 0 | catch (SQLException e) |
116 | |
{ |
117 | |
|
118 | 0 | } |
119 | 0 | getConnSource().returnConnection(objConn); |
120 | |
} |
121 | 0 | catch (SQLException e) |
122 | |
{ |
123 | 0 | LOG.warn("Error while closing the result set", e); |
124 | 0 | } |
125 | 0 | } |
126 | |
|
127 | |
protected String quoteObjectName(String strObject) |
128 | |
{ |
129 | 0 | return strObject; |
130 | |
} |
131 | |
|
132 | |
|
133 | |
|
134 | |
|
135 | |
|
136 | |
|
137 | |
public String getTableName() |
138 | |
{ |
139 | 0 | return m_strTableName; |
140 | |
} |
141 | |
|
142 | |
|
143 | |
|
144 | |
|
145 | |
|
146 | |
|
147 | |
|
148 | |
public void setTableName(String tableName) |
149 | |
{ |
150 | 0 | m_strTableName = tableName; |
151 | 0 | } |
152 | |
|
153 | |
|
154 | |
|
155 | |
|
156 | |
|
157 | |
|
158 | |
public ISqlConnectionSource getConnSource() |
159 | |
{ |
160 | 0 | return m_objConnSource; |
161 | |
} |
162 | |
|
163 | |
|
164 | |
|
165 | |
|
166 | |
|
167 | |
|
168 | |
|
169 | |
public void setConnSource(ISqlConnectionSource connSource) |
170 | |
{ |
171 | 0 | m_objConnSource = connSource; |
172 | 0 | } |
173 | |
|
174 | |
|
175 | |
|
176 | |
|
177 | |
|
178 | |
|
179 | |
public String getWhereClause() |
180 | |
{ |
181 | 0 | return m_strWhereClause; |
182 | |
} |
183 | |
|
184 | |
|
185 | |
|
186 | |
|
187 | |
|
188 | |
|
189 | |
|
190 | |
public void setWhereClause(String whereClause) |
191 | |
{ |
192 | 0 | m_strWhereClause = whereClause; |
193 | 0 | } |
194 | |
|
195 | |
protected String generateColumnList(SqlTableColumnModel objColumnModel) |
196 | |
{ |
197 | |
|
198 | 0 | StringBuffer objColumnBuf = new StringBuffer(); |
199 | 0 | for(int i = 0; i < objColumnModel.getColumnCount(); i++) |
200 | |
{ |
201 | 0 | SqlTableColumn objColumn = objColumnModel.getSqlColumn(i); |
202 | 0 | if (i > 0) |
203 | 0 | objColumnBuf.append(", "); |
204 | |
|
205 | 0 | objColumnBuf.append(quoteObjectName(objColumn.getColumnName())); |
206 | |
} |
207 | |
|
208 | 0 | return objColumnBuf.toString(); |
209 | |
} |
210 | |
|
211 | |
protected String generateWhereClause() |
212 | |
{ |
213 | 0 | String strWhereClause = getWhereClause(); |
214 | 0 | if (strWhereClause == null || strWhereClause.equals("")) |
215 | 0 | return ""; |
216 | |
|
217 | 0 | return "WHERE " + strWhereClause + " "; |
218 | |
} |
219 | |
|
220 | |
protected String generateOrderByClause(ITableSortingState objSortingState) |
221 | |
{ |
222 | |
|
223 | 0 | StringBuffer objSortingBuf = new StringBuffer(); |
224 | 0 | if (objSortingState.getSortColumn() != null) |
225 | |
{ |
226 | 0 | objSortingBuf.append("ORDER BY "); |
227 | 0 | objSortingBuf.append(objSortingState.getSortColumn()); |
228 | |
|
229 | 0 | if (objSortingState.getSortOrder() == ITableSortingState.SORT_ASCENDING) |
230 | 0 | objSortingBuf.append(" ASC "); |
231 | |
|
232 | 0 | else objSortingBuf.append(" DESC "); |
233 | |
} |
234 | |
|
235 | 0 | return objSortingBuf.toString(); |
236 | |
} |
237 | |
|
238 | |
protected String generateLimitClause(ITablePagingState objPagingState) |
239 | |
{ |
240 | 0 | int nPageSize = objPagingState.getPageSize(); |
241 | 0 | int nStart = objPagingState.getCurrentPage() * nPageSize; |
242 | |
|
243 | 0 | return "LIMIT " + nPageSize + " OFFSET " + nStart + " "; |
244 | |
} |
245 | |
|
246 | |
protected String generateDataQuery(SqlTableColumnModel objColumnModel, |
247 | |
SimpleTableState objState) |
248 | |
{ |
249 | 0 | return "SELECT " + generateColumnList(objColumnModel) |
250 | |
+ " FROM " + getTableName() + " " + generateWhereClause() |
251 | |
+ generateOrderByClause(objState.getSortingState()) |
252 | |
+ generateLimitClause(objState.getPagingState()); |
253 | |
} |
254 | |
|
255 | |
protected String generateCountQuery() |
256 | |
{ |
257 | 0 | return "SELECT COUNT(*) FROM " + getTableName() + " " |
258 | |
+ generateWhereClause(); |
259 | |
} |
260 | |
} |