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 }