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 org.apache.commons.logging.Log;
018    import org.apache.commons.logging.LogFactory;
019    import org.apache.tapestry.contrib.table.model.ITableColumnModel;
020    import org.apache.tapestry.contrib.table.model.common.AbstractTableModel;
021    import org.apache.tapestry.contrib.table.model.simple.SimpleTableState;
022    
023    import java.sql.ResultSet;
024    import java.sql.SQLException;
025    import java.util.Iterator;
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(getSqlColumnModel(), getState());
113    
114                return new ResultSetIterator(objResultSet)
115                {
116    
117                    protected void notifyEnd()
118                    {
119                        getSqlDataSource().closeResultSet(getResultSet());
120                    }
121                };
122            }
123            catch (SQLException e)
124            {
125                LOG.error("Cannot get current page rows", e);
126                return new ResultSetIterator(null);
127            }
128        }
129    
130        /**
131         * Returns the dataSource.
132         * 
133         * @return ISqlTableDataSource
134         */
135        public ISqlTableDataSource getSqlDataSource()
136        {
137            return m_objDataSource;
138        }
139    
140        /**
141         * @see org.apache.tapestry.contrib.table.model.common.AbstractTableModel#getRowCount()
142         */
143        public int getRowCount()
144        {
145            try
146            {
147                return m_objDataSource.getRowCount();
148            }
149            catch (SQLException e)
150            {
151                LOG.error("Cannot get row count", e);
152                return 1;
153            }
154        }
155    
156    }