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 ////////////////////////////////////////////////////////////////////////// 129 // 130 // Implementation of the TableModel Interface 131 // 132 ////////////////////////////////////////////////////////////////////////// 133 // MetaData 134 @Override 135 public String getColumnName(int column) { 136 if (columnNames[column] != null) { 137 return columnNames[column]; 138 } else { 139 return ""; 140 } 141 } 142 143 @Override 144 public Class<?> getColumnClass(int column) { 145 int type; 146 try { 147 type = metaData.getColumnType(column + 1); 148 } catch (SQLException e) { 149 return super.getColumnClass(column); 150 } 151 152 switch (type) { 153 case Types.CHAR: 154 case Types.VARCHAR: 155 case Types.LONGVARCHAR: 156 return String.class; 157 158 case Types.BIT: 159 return Boolean.class; 160 161 case Types.TINYINT: 162 case Types.SMALLINT: 163 case Types.INTEGER: 164 return Integer.class; 165 166 case Types.BIGINT: 167 return Long.class; 168 169 case Types.FLOAT: 170 case Types.DOUBLE: 171 return Double.class; 172 173 case Types.DATE: 174 return java.sql.Date.class; 175 176 default: 177 return Object.class; 178 } 179 } 180 181 @Override 182 public boolean isCellEditable(int row, int column) { 183 try { 184 return metaData.isWritable(column + 1); 185 } catch (SQLException e) { 186 return false; 187 } 188 } 189 190 public int getColumnCount() { 191 return columnNames.length; 192 } 193 194 // Data methods 195 public int getRowCount() { 196 return rows.size(); 197 } 198 199 public Object getValueAt(int aRow, int aColumn) { 200 List<Object> row = rows.get(aRow); 201 return row.get(aColumn); 202 } 203 204 public String dbRepresentation(int column, Object value) { 205 int type; 206 207 if (value == null) { 208 return "null"; 209 } 210 211 try { 212 type = metaData.getColumnType(column + 1); 213 } catch (SQLException e) { 214 return value.toString(); 215 } 216 217 switch (type) { 218 case Types.INTEGER: 219 case Types.DOUBLE: 220 case Types.FLOAT: 221 return value.toString(); 222 case Types.BIT: 223 return ((Boolean) value).booleanValue() ? "1" : "0"; 224 case Types.DATE: 225 return value.toString(); // This will need some conversion. 226 default: 227 return "\"" + value.toString() + "\""; 228 } 229 230 } 231 232 @Override 233 public void setValueAt(Object value, int row, int column) { 234 try { 235 String tableName = metaData.getTableName(column + 1); 236 // Some of the drivers seem buggy, tableName should not be null. 237 if (tableName == null) { 238 System.out.println("Table name returned null."); 239 } 240 String columnName = getColumnName(column); 241 String query = 242 "update " + tableName + " set " + columnName + " = " 243 + dbRepresentation(column, value) + " where "; 244 // We don't have a model of the schema so we don't know the 245 // primary keys or which columns to lock on. To demonstrate 246 // that editing is possible, we'll just lock on everything. 247 for (int col = 0; col < getColumnCount(); col++) { 248 String colName = getColumnName(col); 249 if (colName.equals("")) { 250 continue; 251 } 252 if (col != 0) { 253 query = query + " and "; 254 } 255 query = query + colName + " = " + dbRepresentation(col, 256 getValueAt(row, col)); 257 } 258 System.out.println(query); 259 System.out.println("Not sending update to database"); 260 // statement.executeQuery(query); 261 } catch (SQLException e) { 262 // e.printStackTrace(); 263 System.err.println("Update failed"); 264 } 265 List<Object> dataRow = rows.get(row); 266 dataRow.set(column, value); 267 268 } 269 }