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.ResultSet;
018    import java.sql.SQLException;
019    import java.util.Iterator;
020    
021    import org.apache.commons.logging.Log;
022    import org.apache.commons.logging.LogFactory;
023    import org.apache.tapestry.contrib.table.model.ITableColumnModel;
024    import org.apache.tapestry.contrib.table.model.common.AbstractTableModel;
025    import org.apache.tapestry.contrib.table.model.simple.SimpleTableState;
026    
027    /**
028     * An implementation of ITableModel that obtains its data through SQL queries.
029     * This is a very efficient model, since it uses SQL to perform the data sorting
030     * (through ORDER BY) and obtains only the data on the current page (through
031     * LIMIT/OFFSET).
032     * <p>
033     * This object is typically created in the following manner:
034     * 
035     * <pre>
036     * ISqlConnectionSource objConnSrc = new SimpleSqlConnectionSource(
037     *         &quot;jdbc:postgresql://localhost/testdb&quot;, &quot;testdb&quot;, &quot;testdb&quot;);
038     * 
039     * ISqlTableDataSource objDataSrc = new SimpleSqlTableDataSource(objConnSrc,
040     *         &quot;test_table&quot;);
041     * 
042     * SqlTableColumnModel objColumnModel = new SqlTableColumnModel(
043     *         new SqlTableColumn[] {
044     *                 new SqlTableColumn(&quot;language&quot;, &quot;Language&quot;, true),
045     *                 new SqlTableColumn(&quot;country&quot;, &quot;Country&quot;, true),
046     *                 new SqlTableColumn(&quot;variant&quot;, &quot;Variant&quot;, true),
047     *                 new SqlTableColumn(&quot;intvalue&quot;, &quot;Integer&quot;, true),
048     *                 new SqlTableColumn(&quot;floatvalue&quot;, &quot;Float&quot;, true) });
049     * 
050     * ITableModel objTableModel = new SqlTableModel(objDataSrc, objColumnModel);
051     * 
052     * return objTableModel;
053     * </pre>
054     * 
055     * @author mindbridge
056     */
057    public class SqlTableModel extends AbstractTableModel
058    {
059    
060        private static final long serialVersionUID = 1L;
061        private static final Log LOG = LogFactory.getLog(SqlTableModel.class);
062    
063        private ISqlTableDataSource m_objDataSource;
064        private SqlTableColumnModel m_objColumnModel;
065    
066        {
067            try
068            {
069                Class.forName("org.hsqldb.jdbcDriver");
070            }
071            catch (Exception e)
072            {
073                System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
074                e.printStackTrace();
075            }
076        }
077    
078        public SqlTableModel(ISqlTableDataSource objDataSource,
079                SqlTableColumnModel objColumnModel)
080        {
081            this(objDataSource, objColumnModel, new SimpleTableState());
082        }
083    
084        public SqlTableModel(ISqlTableDataSource objDataSource,
085                SqlTableColumnModel objColumnModel, SimpleTableState objState)
086        {
087            super(objState);
088            m_objDataSource = objDataSource;
089            m_objColumnModel = objColumnModel;
090        }
091    
092        /**
093         * @see org.apache.tapestry.contrib.table.model.ITableModel#getColumnModel()
094         */
095        public ITableColumnModel getColumnModel()
096        {
097            return m_objColumnModel;
098        }
099    
100        public SqlTableColumnModel getSqlColumnModel()
101        {
102            return m_objColumnModel;
103        }
104    
105        /**
106         * @see org.apache.tapestry.contrib.table.model.ITableModel#getCurrentPageRows()
107         */
108        public Iterator getCurrentPageRows()
109        {
110            try
111            {
112                ResultSet objResultSet = getSqlDataSource().getCurrentRows(
113                        getSqlColumnModel(), getState());
114    
115                return new ResultSetIterator(objResultSet)
116                {
117    
118                    protected void notifyEnd()
119                    {
120                        getSqlDataSource().closeResultSet(getResultSet());
121                    }
122                };
123            }
124            catch (SQLException e)
125            {
126                LOG.error("Cannot get current page rows", e);
127                return new ResultSetIterator(null);
128            }
129        }
130    
131        /**
132         * Returns the dataSource.
133         * 
134         * @return ISqlTableDataSource
135         */
136        public ISqlTableDataSource getSqlDataSource()
137        {
138            return m_objDataSource;
139        }
140    
141        /**
142         * @see org.apache.tapestry.contrib.table.model.common.AbstractTableModel#getRowCount()
143         */
144        protected int getRowCount()
145        {
146            try
147            {
148                return m_objDataSource.getRowCount();
149            }
150            catch (SQLException e)
151            {
152                LOG.error("Cannot get row count", e);
153                return 1;
154            }
155        }
156    
157    }