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    }