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.ResultSet;
018import java.sql.SQLException;
019import java.util.Iterator;
020
021import org.apache.commons.logging.Log;
022import org.apache.commons.logging.LogFactory;
023import org.apache.tapestry.contrib.table.model.ITableColumnModel;
024import org.apache.tapestry.contrib.table.model.common.AbstractTableModel;
025import 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 */
057public 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}