JDBCAdapter.java revision 4378
0N/A/*
553N/A * Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.
0N/A *
0N/A * Redistribution and use in source and binary forms, with or without
0N/A * modification, are permitted provided that the following conditions
0N/A * are met:
0N/A *
0N/A * - Redistributions of source code must retain the above copyright
0N/A * notice, this list of conditions and the following disclaimer.
0N/A *
0N/A * - Redistributions in binary form must reproduce the above copyright
0N/A * notice, this list of conditions and the following disclaimer in the
0N/A * documentation and/or other materials provided with the distribution.
0N/A *
0N/A * - Neither the name of Oracle nor the names of its
0N/A * contributors may be used to endorse or promote products derived
0N/A * from this software without specific prior written permission.
0N/A *
553N/A * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
553N/A * IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
553N/A * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
0N/A * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
0N/A * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
0N/A * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
0N/A * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
0N/A * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
0N/A * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
0N/A * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
0N/A * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
0N/A */
131N/A
0N/A/*
0N/A * This source code is provided to illustrate the usage of a given feature
0N/A * or technique and has been deliberately simplified. Additional steps
0N/A * required for a production-quality application, such as security checks,
0N/A * input validation and proper error handling, might not be present in
0N/A * this sample code.
0N/A */
0N/A
0N/A
0N/A
0N/Aimport java.sql.Connection;
0N/Aimport java.sql.DriverManager;
0N/Aimport java.sql.ResultSet;
0N/Aimport java.sql.ResultSetMetaData;
0N/Aimport java.sql.SQLException;
0N/Aimport java.sql.Statement;
0N/Aimport java.sql.Types;
0N/Aimport java.util.ArrayList;
0N/Aimport java.util.List;
0N/Aimport javax.swing.table.AbstractTableModel;
0N/A
0N/A
0N/A/**
0N/A * An adaptor, transforming the JDBC interface to the TableModel interface.
0N/A *
0N/A * @author Philip Milne
0N/A */
0N/A@SuppressWarnings("serial")
0N/Apublic class JDBCAdapter extends AbstractTableModel {
0N/A
0N/A Connection connection;
0N/A Statement statement;
0N/A ResultSet resultSet;
0N/A String[] columnNames = {};
0N/A List<List<Object>> rows = new ArrayList<List<Object>>();
0N/A ResultSetMetaData metaData;
0N/A
0N/A public JDBCAdapter(String url, String driverName,
0N/A String user, String passwd) {
0N/A try {
0N/A Class.forName(driverName);
0N/A System.out.println("Opening db connection");
0N/A
0N/A connection = DriverManager.getConnection(url, user, passwd);
0N/A statement = connection.createStatement();
0N/A } catch (ClassNotFoundException ex) {
0N/A System.err.println("Cannot find the database driver classes.");
0N/A System.err.println(ex);
0N/A } catch (SQLException ex) {
0N/A System.err.println("Cannot connect to this database.");
0N/A System.err.println(ex);
0N/A }
0N/A }
0N/A
0N/A public void executeQuery(String query) {
0N/A if (connection == null || statement == null) {
0N/A System.err.println("There is no database to execute the query.");
0N/A return;
0N/A }
0N/A try {
0N/A resultSet = statement.executeQuery(query);
0N/A metaData = resultSet.getMetaData();
0N/A
0N/A int numberOfColumns = metaData.getColumnCount();
0N/A columnNames = new String[numberOfColumns];
0N/A // Get the column names and cache them.
0N/A // Then we can close the connection.
0N/A for (int column = 0; column < numberOfColumns; column++) {
0N/A columnNames[column] = metaData.getColumnLabel(column + 1);
0N/A }
0N/A
0N/A // Get all rows.
0N/A rows = new ArrayList<List<Object>>();
0N/A while (resultSet.next()) {
0N/A List<Object> newRow = new ArrayList<Object>();
0N/A for (int i = 1; i <= getColumnCount(); i++) {
0N/A newRow.add(resultSet.getObject(i));
0N/A }
0N/A rows.add(newRow);
0N/A }
0N/A // close(); Need to copy the metaData, bug in jdbc:odbc driver.
0N/A
0N/A // Tell the listeners a new table has arrived.
0N/A fireTableChanged(null);
0N/A } catch (SQLException ex) {
System.err.println(ex);
}
}
public void close() throws SQLException {
System.out.println("Closing db connection");
resultSet.close();
statement.close();
connection.close();
}
@Override
protected void finalize() throws Throwable {
close();
super.finalize();
}
//////////////////////////////////////////////////////////////////////////
//
// Implementation of the TableModel Interface
//
//////////////////////////////////////////////////////////////////////////
// MetaData
@Override
public String getColumnName(int column) {
if (columnNames[column] != null) {
return columnNames[column];
} else {
return "";
}
}
@Override
public Class<?> getColumnClass(int column) {
int type;
try {
type = metaData.getColumnType(column + 1);
} catch (SQLException e) {
return super.getColumnClass(column);
}
switch (type) {
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
return String.class;
case Types.BIT:
return Boolean.class;
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
return Integer.class;
case Types.BIGINT:
return Long.class;
case Types.FLOAT:
case Types.DOUBLE:
return Double.class;
case Types.DATE:
return java.sql.Date.class;
default:
return Object.class;
}
}
@Override
public boolean isCellEditable(int row, int column) {
try {
return metaData.isWritable(column + 1);
} catch (SQLException e) {
return false;
}
}
public int getColumnCount() {
return columnNames.length;
}
// Data methods
public int getRowCount() {
return rows.size();
}
public Object getValueAt(int aRow, int aColumn) {
List<Object> row = rows.get(aRow);
return row.get(aColumn);
}
public String dbRepresentation(int column, Object value) {
int type;
if (value == null) {
return "null";
}
try {
type = metaData.getColumnType(column + 1);
} catch (SQLException e) {
return value.toString();
}
switch (type) {
case Types.INTEGER:
case Types.DOUBLE:
case Types.FLOAT:
return value.toString();
case Types.BIT:
return ((Boolean) value).booleanValue() ? "1" : "0";
case Types.DATE:
return value.toString(); // This will need some conversion.
default:
return "\"" + value.toString() + "\"";
}
}
@Override
public void setValueAt(Object value, int row, int column) {
try {
String tableName = metaData.getTableName(column + 1);
// Some of the drivers seem buggy, tableName should not be null.
if (tableName == null) {
System.out.println("Table name returned null.");
}
String columnName = getColumnName(column);
String query =
"update " + tableName + " set " + columnName + " = "
+ dbRepresentation(column, value) + " where ";
// We don't have a model of the schema so we don't know the
// primary keys or which columns to lock on. To demonstrate
// that editing is possible, we'll just lock on everything.
for (int col = 0; col < getColumnCount(); col++) {
String colName = getColumnName(col);
if (colName.equals("")) {
continue;
}
if (col != 0) {
query = query + " and ";
}
query = query + colName + " = " + dbRepresentation(col,
getValueAt(row, col));
}
System.out.println(query);
System.out.println("Not sending update to database");
// statement.executeQuery(query);
} catch (SQLException e) {
// e.printStackTrace();
System.err.println("Update failed");
}
List<Object> dataRow = rows.get(row);
dataRow.set(column, value);
}
}