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
015package org.apache.tapestry.contrib.table.model.sql;
016
017import java.sql.Connection;
018import java.sql.ResultSet;
019import java.sql.SQLException;
020import java.sql.Statement;
021
022import org.apache.commons.logging.Log;
023import org.apache.commons.logging.LogFactory;
024import org.apache.tapestry.contrib.table.model.ITablePagingState;
025import org.apache.tapestry.contrib.table.model.ITableSortingState;
026import org.apache.tapestry.contrib.table.model.simple.SimpleTableState;
027
028/**
029 * 
030 * @author mindbridge
031 */
032public 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}