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
030     * the data sorting (through ORDER BY) and obtains only the data
031     * on the current page (through LIMIT/OFFSET). 
032     * <p>
033     * This object is typically created in the following manner:
034     * <pre> 
035     *    ISqlConnectionSource objConnSrc = 
036     *        new SimpleSqlConnectionSource("jdbc:postgresql://localhost/testdb", "testdb", "testdb");
037     *
038     *    ISqlTableDataSource objDataSrc = 
039     *        new SimpleSqlTableDataSource(objConnSrc, "test_table");
040     *
041     *    SqlTableColumnModel objColumnModel = 
042     *        new SqlTableColumnModel(new SqlTableColumn[] {
043     *            new SqlTableColumn("language", "Language", true),
044     *            new SqlTableColumn("country", "Country", true),
045     *            new SqlTableColumn("variant", "Variant", true),
046     *            new SqlTableColumn("intvalue", "Integer", true),
047     *            new SqlTableColumn("floatvalue", "Float", true)
048     *        });
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            private static final long serialVersionUID = 1L;
060            private static final Log LOG = LogFactory.getLog(SqlTableModel.class);
061    
062            private ISqlTableDataSource m_objDataSource;
063            private SqlTableColumnModel m_objColumnModel;
064        
065        {
066            try {
067                Class.forName ( "org.hsqldb.jdbcDriver" );
068            } catch (Exception e) {
069                System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
070                e.printStackTrace();
071            }
072        }
073    
074            public SqlTableModel(
075                    ISqlTableDataSource objDataSource,
076                    SqlTableColumnModel objColumnModel)
077            {
078                    this(objDataSource, objColumnModel, new SimpleTableState());
079            }
080    
081            public SqlTableModel(
082                    ISqlTableDataSource objDataSource,
083                    SqlTableColumnModel objColumnModel,
084                    SimpleTableState objState)
085            {
086            super(objState);
087                    m_objDataSource = objDataSource;
088                    m_objColumnModel = objColumnModel;
089            }
090    
091            /**
092             * @see org.apache.tapestry.contrib.table.model.ITableModel#getColumnModel()
093             */
094            public ITableColumnModel getColumnModel()
095            {
096                    return m_objColumnModel;
097            }
098    
099            public SqlTableColumnModel getSqlColumnModel()
100            {
101                    return m_objColumnModel;
102            }
103    
104            /**
105             * @see org.apache.tapestry.contrib.table.model.ITableModel#getCurrentPageRows()
106             */
107            public Iterator getCurrentPageRows()
108            {
109                    try
110                    {
111                            ResultSet objResultSet =
112                                    getSqlDataSource().getCurrentRows(
113                                            getSqlColumnModel(),
114                                            getState());
115    
116                            return new ResultSetIterator(objResultSet)
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             * @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        protected 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    }