jdbcadapter.java

用JAVA写的数据库程序,Oracle的JDBC驱动程序,SqlServer的JDBC驱动程序,配置数据库连接

package com.jimw.mysqlplus;

import java.util.Vector;
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.table.AbstractTableModel;
import javax.swing.event.TableModelEvent;

public class JDBCAdapter extends AbstractTableModel {
    boolean             isOpen = false;
    Connection          connection;
    Statement           statement;
    ResultSet           resultSet;
    String[]            columnNames = {};
    Vector		rows = new Vector();
    ResultSetMetaData   metaData;
    String              errMsg;
    private boolean commitFlag = false;

    public JDBCAdapter(String url, String driverName,
                       String user, String passwd) {
        try {
            Class.forName(driverName);
            System.out.println("Opening db connection");

            connection = DriverManager.getConnection(url, user, passwd);
            connection.setAutoCommit(commitFlag);
            statement = connection.createStatement();
            isOpen = true;
        }
        catch (ClassNotFoundException ex) {
            System.err.println("Cannot find the database driver classes.");
            System.err.println(ex);
        }
        catch (SQLException ex) {
            System.err.println("Cannot connect to this database.");
            System.err.println(ex);
        }
     }

    public boolean execute(String sql) {
        boolean ret = false;
        if ( sql.charAt(sql.length()-1) == ';' ) {
            sql = sql.substring(0,sql.length()-1);
        }
        switch (analysisSql(sql)) {
            case 0:ret = executeQuery(sql);break;
            case 1:
            case 2:
            case 3:ret = executeUpdate(sql);break;
            case 4:commit();break;
            case 5:rollback();break;
            default:errMsg="错误的SQL语句";
        }
        return ret;
    }

    //判断是否是正确的SQL语句
    private int analysisSql(String sql) {
        int pos = -1;
        int i = 0;

        sql = sql.toUpperCase();
        for ( i = 0; i < MySqlPlusFrame.command.length; i++) {
            if ( (pos = sql.indexOf(MySqlPlusFrame.command[i])) == 0) break;
        }
        if (pos ==0) {
            if ( (i == 4 || i == 5) &&
                 (!sql.equals(MySqlPlusFrame.command[i])) )
                return -1;
            return i;
        }
        return -1;
    }

    private boolean executeUpdate(String sql) {
        if (connection == null || statement == null) {
            errMsg = "There is no database to execute the query.";
        }
        try {
            int ret = statement.executeUpdate(sql);
            errMsg = "Successed...\nChanged "+ret+" records";
        } catch (Exception ex) {
            errMsg = ex.getMessage();
        }
        return false;
    }

    private boolean executeQuery(String query) {
        if (connection == null || statement == null) {
            errMsg = "There is no database to execute the query.";
            return false;
        }
        try {
            resultSet = statement.executeQuery(query);
            metaData = resultSet.getMetaData();

            int numberOfColumns =  metaData.getColumnCount();

            columnNames = new String[numberOfColumns];
            // Get the column names and cache them.
            // Then we can close the connection.
            for(int column = 0; column < numberOfColumns; column++) {
                columnNames[column] = metaData.getColumnLabel(column+1);
            }

            // Get all rows.
            rows = new Vector();
            while (resultSet.next()) {
                Vector newRow = new Vector();
                for (int i = 1; i <= getColumnCount(); i++) {
	            newRow.addElement(resultSet.getObject(i));
                }
                rows.addElement(newRow);
            }
            //  close(); Need to copy the metaData, bug in jdbc:odbc driver.
            fireTableChanged(null); // Tell the listeners a new table has arrived.
            errMsg = "Total number of record is:" + rows.size();
            return true;
        }
        catch (SQLException ex) {
            errMsg = ex.getMessage();
            return false;
        }
    }

    public void commit() {
        try {
            connection.commit();
            errMsg = "Commit finished...";
        } catch ( java.sql.SQLException ex) {
            errMsg = ex.getMessage();
        }
    }

    public void rollback() {
        try {
            connection.rollback();
            errMsg = "Rollback finished...";
        } catch ( java.sql.SQLException ex) {
            errMsg = ex.getMessage();
        }
    }

    public void setAutoCommit() {
        commitFlag = !commitFlag;
        try {
            connection.setAutoCommit(commitFlag);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public boolean getAutoCommitflag() {
        return commitFlag;
    }

    public void close() throws SQLException {
        System.out.println("Closing db connection");
        if (!commitFlag)
            connection.rollback();
        if (resultSet != null)
          resultSet.close();
        statement.close();
        connection.close();
    }

    protected void finalize() throws Throwable {
        close();
        super.finalize();
    }

    //////////////////////////////////////////////////////////////////////////
    //
    //             Implementation of the TableModel Interface
    //
    //////////////////////////////////////////////////////////////////////////

    // MetaData

    public String getColumnName(int column) {
        if (columnNames[column] != null) {
            return columnNames[column];
        } else {
            return "";
        }
    }

    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;
        }
    }

    public boolean isCellEditable(int row, int column) {
//        try {
//            return metaData.isWritable(column+1);
//        }
//        catch (SQLException e) {
//            return false;
//        }
          return false;
    }

    public int getColumnCount() {
        return columnNames.length;
    }

    // Data methods

    public int getRowCount() {
        return rows.size();
    }

    public Object getValueAt(int aRow, int aColumn) {
        Vector row = (Vector)rows.elementAt(aRow);
        return row.elementAt(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()+"\"";
        }

    }

    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");
        }
        Vector dataRow = (Vector)rows.elementAt(row);
        dataRow.setElementAt(value, column);

    }

    public String getErrorMessage() {
        return errMsg;
    }

    public boolean isOpen() {
        return isOpen;
    }
}