1/*
2 * Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.
3 *
4 * Redistribution and use in source and binary forms, with or without
5 * modification, are permitted provided that the following conditions
6 * are met:
7 *
8 *   - Redistributions of source code must retain the above copyright
9 *     notice, this list of conditions and the following disclaimer.
10 *
11 *   - Redistributions in binary form must reproduce the above copyright
12 *     notice, this list of conditions and the following disclaimer in the
13 *     documentation and/or other materials provided with the distribution.
14 *
15 *   - Neither the name of Oracle nor the names of its
16 *     contributors may be used to endorse or promote products derived
17 *     from this software without specific prior written permission.
18 *
19 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
20 * IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
21 * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
22 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE COPYRIGHT OWNER OR
23 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
24 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
25 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
26 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
27 * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
28 * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
29 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
30 */
31
32/*
33 * This source code is provided to illustrate the usage of a given feature
34 * or technique and has been deliberately simplified. Additional steps
35 * required for a production-quality application, such as security checks,
36 * input validation and proper error handling, might not be present in
37 * this sample code.
38 */
39
40
41
42import java.sql.Connection;
43import java.sql.DriverManager;
44import java.sql.ResultSet;
45import java.sql.ResultSetMetaData;
46import java.sql.SQLException;
47import java.sql.Statement;
48import java.sql.Types;
49import java.util.ArrayList;
50import java.util.List;
51import javax.swing.table.AbstractTableModel;
52
53
54/**
55 * An adaptor, transforming the JDBC interface to the TableModel interface.
56 *
57 * @author Philip Milne
58 */
59@SuppressWarnings("serial")
60public class JDBCAdapter extends AbstractTableModel {
61
62    Connection connection;
63    Statement statement;
64    ResultSet resultSet;
65    String[] columnNames = {};
66    List<List<Object>> rows = new ArrayList<List<Object>>();
67    ResultSetMetaData metaData;
68
69    public JDBCAdapter(String url, String driverName,
70            String user, String passwd) {
71        try {
72            Class.forName(driverName);
73            System.out.println("Opening db connection");
74
75            connection = DriverManager.getConnection(url, user, passwd);
76            statement = connection.createStatement();
77        } catch (ClassNotFoundException ex) {
78            System.err.println("Cannot find the database driver classes.");
79            System.err.println(ex);
80        } catch (SQLException ex) {
81            System.err.println("Cannot connect to this database.");
82            System.err.println(ex);
83        }
84    }
85
86    public void executeQuery(String query) {
87        if (connection == null || statement == null) {
88            System.err.println("There is no database to execute the query.");
89            return;
90        }
91        try {
92            resultSet = statement.executeQuery(query);
93            metaData = resultSet.getMetaData();
94
95            int numberOfColumns = metaData.getColumnCount();
96            columnNames = new String[numberOfColumns];
97            // Get the column names and cache them.
98            // Then we can close the connection.
99            for (int column = 0; column < numberOfColumns; column++) {
100                columnNames[column] = metaData.getColumnLabel(column + 1);
101            }
102
103            // Get all rows.
104            rows = new ArrayList<List<Object>>();
105            while (resultSet.next()) {
106                List<Object> newRow = new ArrayList<Object>();
107                for (int i = 1; i <= getColumnCount(); i++) {
108                    newRow.add(resultSet.getObject(i));
109                }
110                rows.add(newRow);
111            }
112            //  close(); Need to copy the metaData, bug in jdbc:odbc driver.
113
114            // Tell the listeners a new table has arrived.
115            fireTableChanged(null);
116        } catch (SQLException ex) {
117            System.err.println(ex);
118        }
119    }
120
121    public void close() throws SQLException {
122        System.out.println("Closing db connection");
123        resultSet.close();
124        statement.close();
125        connection.close();
126    }
127
128    @Override
129    protected void finalize() throws Throwable {
130        close();
131        super.finalize();
132    }
133
134    //////////////////////////////////////////////////////////////////////////
135    //
136    //             Implementation of the TableModel Interface
137    //
138    //////////////////////////////////////////////////////////////////////////
139    // MetaData
140    @Override
141    public String getColumnName(int column) {
142        if (columnNames[column] != null) {
143            return columnNames[column];
144        } else {
145            return "";
146        }
147    }
148
149    @Override
150    public Class<?> getColumnClass(int column) {
151        int type;
152        try {
153            type = metaData.getColumnType(column + 1);
154        } catch (SQLException e) {
155            return super.getColumnClass(column);
156        }
157
158        switch (type) {
159            case Types.CHAR:
160            case Types.VARCHAR:
161            case Types.LONGVARCHAR:
162                return String.class;
163
164            case Types.BIT:
165                return Boolean.class;
166
167            case Types.TINYINT:
168            case Types.SMALLINT:
169            case Types.INTEGER:
170                return Integer.class;
171
172            case Types.BIGINT:
173                return Long.class;
174
175            case Types.FLOAT:
176            case Types.DOUBLE:
177                return Double.class;
178
179            case Types.DATE:
180                return java.sql.Date.class;
181
182            default:
183                return Object.class;
184        }
185    }
186
187    @Override
188    public boolean isCellEditable(int row, int column) {
189        try {
190            return metaData.isWritable(column + 1);
191        } catch (SQLException e) {
192            return false;
193        }
194    }
195
196    public int getColumnCount() {
197        return columnNames.length;
198    }
199
200    // Data methods
201    public int getRowCount() {
202        return rows.size();
203    }
204
205    public Object getValueAt(int aRow, int aColumn) {
206        List<Object> row = rows.get(aRow);
207        return row.get(aColumn);
208    }
209
210    public String dbRepresentation(int column, Object value) {
211        int type;
212
213        if (value == null) {
214            return "null";
215        }
216
217        try {
218            type = metaData.getColumnType(column + 1);
219        } catch (SQLException e) {
220            return value.toString();
221        }
222
223        switch (type) {
224            case Types.INTEGER:
225            case Types.DOUBLE:
226            case Types.FLOAT:
227                return value.toString();
228            case Types.BIT:
229                return ((Boolean) value).booleanValue() ? "1" : "0";
230            case Types.DATE:
231                return value.toString(); // This will need some conversion.
232            default:
233                return "\"" + value.toString() + "\"";
234        }
235
236    }
237
238    @Override
239    public void setValueAt(Object value, int row, int column) {
240        try {
241            String tableName = metaData.getTableName(column + 1);
242            // Some of the drivers seem buggy, tableName should not be null.
243            if (tableName == null) {
244                System.out.println("Table name returned null.");
245            }
246            String columnName = getColumnName(column);
247            String query =
248                    "update " + tableName + " set " + columnName + " = "
249                    + dbRepresentation(column, value) + " where ";
250            // We don't have a model of the schema so we don't know the
251            // primary keys or which columns to lock on. To demonstrate
252            // that editing is possible, we'll just lock on everything.
253            for (int col = 0; col < getColumnCount(); col++) {
254                String colName = getColumnName(col);
255                if (colName.equals("")) {
256                    continue;
257                }
258                if (col != 0) {
259                    query = query + " and ";
260                }
261                query = query + colName + " = " + dbRepresentation(col,
262                        getValueAt(row, col));
263            }
264            System.out.println(query);
265            System.out.println("Not sending update to database");
266            // statement.executeQuery(query);
267        } catch (SQLException e) {
268            //     e.printStackTrace();
269            System.err.println("Update failed");
270        }
271        List<Object> dataRow = rows.get(row);
272        dataRow.set(column, value);
273
274    }
275}
276