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
42 import java.sql.Connection;
43 import java.sql.DriverManager;
44 import java.sql.ResultSet;
45 import java.sql.ResultSetMetaData;
46 import java.sql.SQLException;
47 import java.sql.Statement;
48 import java.sql.Types;
49 import java.util.ArrayList;
50 import java.util.List;
51 import 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")
60 public 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 }