JDBC(Java DataBase Connectivity) 基础知识

JDBC(Java DataBase Connectivity)是Java程式用來連結資料庫的程式庫(java.sql.*)。其中主要的類別有

DriverManager: 用來產生資料庫連線(Connection)
Connection: 用來產生下達命令給資料庫的敘述(Statement, PreparedStatement和CallableStatement)
Statement: 用來下達查詢(executeQuery(), 傳回ResultSet)和更新(executeUpdate(), 傳回整數代表異動的資料數目)命令,
ResultSet: 利用其next()和getXXX()方法抓出查詢的結果
PreparedStatement: 先送出命令的模板, 然後再傳遞所需要的參數
CallableStatement: 呼叫資料庫內的Stored Procedure
DatabaseMetaData: 用來查詢資料庫綱要
下面的JDBCBridgeTest.java說明上面幾種Class的用法

import java.sql.*;
import ncnu.sql.*;
public class JDBCBridgeTest {
    public static void main(String[] argv) {
        try {
            Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
            String pass = "password";
            String db = "jdbc:odbc:ncnu";
            if (argv.length>0) {
                pass = argv[0];
            }
            if (argv.length>1) {
                db = argv[1];
            }
            Connection con = DriverManager.getConnection (db, "sa", pass);
            con.setAutoCommit(true);
            PreparedStatement pstmt;
            Statement stmt;
            ResultSet rs;
            stmt = con.createStatement();
            int count = 0;
            long start = System.currentTimeMillis();
            rs = stmt.executeQuery("select courseid,year,class,studentid from selected");
            while (rs.next()) {
                count++;
                rs.getBytes(1);
                rs.getBytes(2);
                rs.getBytes(3);
                rs.getBytes(4);
            }
            rs.close();
            System.out.println("getBytes version for "+count+" rows: "+(System.currentTimeMillis()-start));
            start = System.currentTimeMillis();
            rs = stmt.executeQuery("select courseid,year,class,studentid from selected");
            while (rs.next()) {
                SQL.toString(rs.getBytes(1));
                SQL.toString(rs.getBytes(2));
                SQL.toString(rs.getBytes(3));
                SQL.toString(rs.getBytes(4));
            }
            rs.close();
            System.out.println("SQL.toString version for "+count+" rows: "+(System.currentTimeMillis()-start));
            start = System.currentTimeMillis();
            rs = stmt.executeQuery("select courseid,year,class,studentid from selected");
            while (rs.next()) {
                rs.getString(1);
                rs.getString(2);
                rs.getString(3);
                rs.getString(4);
            }
            rs.close();
            System.out.println("System getString version for "+count+" rows: "+(System.currentTimeMillis()-start));
            stmt.executeUpdate("delete printlog");
            con.commit();
            start = System.currentTimeMillis();
            for (int i=1; i<=10000; i++) {
                stmt.executeUpdate("insert printlog(printid,studentid,class,type,copy,year,email,success,ip,price) values("+i+",'90213001','B','中文歷年成績單',1,'901','[e-mail]','Y','163.22.22.22',20)");
                con.commit();
            }
            System.out.println("Statement insert 10000 rows: "+(System.currentTimeMillis()-start));
            start = System.currentTimeMillis();
            for (int i=1; i<=10000; i++) {
                stmt.executeUpdate("delete printlog where printid="+i);
                con.commit();
            }
            System.out.println("Statement delete 10000 rows: "+(System.currentTimeMillis()-start));
            stmt.close();
            start = System.currentTimeMillis();
            pstmt = con.prepareStatement("insert printlog(printid,studentid,class,type,copy,year,email,success,ip,price) values(?,?,?,?,?,?,?,?,?,?)");
            for (int i=1;i<=10000; i++) {
                pstmt.setInt(1,i);
                pstmt.setString(2,"90213001");
                pstmt.setString(3,"B");
                pstmt.setString(4,"中文歷年成績單");
                pstmt.setInt(5,3);
                pstmt.setString(6,"901");
                pstmt.setString(7,"[e-mail]");
                pstmt.setString(8,"Y");
                pstmt.setString(9,"163.22.22.22");
                pstmt.setInt(10,20);
                pstmt.executeUpdate();
                con.commit();
            }
            System.out.println("PreparedStatement version1 insert 10000 rows: "+(System.currentTimeMillis()-start));
            pstmt.close();
            start = System.currentTimeMillis();
            pstmt = con.prepareStatement("delete printlog where printid=?");
            for (int i=1; i<=10000; i++) {
                pstmt.setInt(1,i);
                pstmt.executeUpdate();
                con.commit();
            }
            pstmt.close();
            System.out.println("PrepareStatement delete 10000 rows: "+(System.currentTimeMillis()-start));
            start = System.currentTimeMillis();
            pstmt = con.prepareStatement("insert printlog(printid,studentid,class,type,copy,year,email,success,ip,price) values(?,?,?,?,?,?,?,?,?,?)");
            for (int i=1;i<=10000; i++) {
                pstmt.setInt(1,i);
                pstmt.setBytes(2,SQL.toAscii("90213001"));
                pstmt.setBytes(3,SQL.toAscii("B"));
                pstmt.setBytes(4,SQL.toAscii("中文歷年成績單"));
                pstmt.setInt(5,3);
                pstmt.setBytes(6,SQL.toAscii("901"));
                pstmt.setBytes(7,SQL.toAscii("[e-mail]"));
                pstmt.setBytes(8,SQL.toAscii("N"));
                pstmt.setBytes(9,SQL.toAscii("163.22.22.22"));
                pstmt.setInt(10,20);
                pstmt.executeUpdate();
                con.commit();
            }
            System.out.println("PreparedStatement version2 insert 10000 rows: "+(System.currentTimeMillis()-start));
            pstmt.close();
            stmt = con.createStatement();
            stmt.executeUpdate("delete printlog");
            stmt.close();
            con.close();
       } catch (Exception ex) {
            ex.printStackTrace();
            System.exit(1);
        }
    }
}

SQL.java的Source Code如下

package ncnu.sql;
import sun.io.*;
import ncnu.rule.Environment;
/**
* The class is used to work around a bug in JDBC drivers. The driver truncate the leading
* byte of Unicode character before sending to DBMS. This only works for ASCII character.
* To handle strings to DBMS, you have to use SQL.toSQL() to wrap the output
* string. JDBC drivers also treat the data from DBMS as ASCII code by adding 0 before every
* bytes of the incoming data. This also produce errors for Non-ASCII characters. To work
* around this bug, you have to wrap the incoming data with SQL.fromSQL()
*/
public class SQL {
    static ByteToCharConverter toChar;
    static CharToByteConverter toByte;
    static boolean trans = true;
    static {
        try {
            if (System.getProperty("java.version").startsWith("1.1")) {
                trans = true;
            } else {
                trans = false;
            }
            String encoding = "Big5";
            String domain = Environment.getDomain();
            if (domain.endsWith(".tw")) {
                encoding = "Big5";
            }
            SQL.toChar = ByteToCharConverter.getConverter(encoding);
            SQL.toByte = CharToByteConverter.getConverter(encoding);
        } catch(Exception ex) {
            ex.printStackTrace();
            System.exit(1);
        }
    }
    public static void setEncoding(String encoding) {
        try {
            SQL.toChar = ByteToCharConverter.getConverter(encoding);
            SQL.toByte = CharToByteConverter.getConverter(encoding);
        } catch(Exception ex) {}
    }
    /**
     * convert a string to ASCII byte array
     */
    public static byte[] toAscii(String s) {
        if (s==null) return new byte[0];
        try {
            synchronized(toByte) {
                return toByte.convertAll(s.toCharArray());
            }
        } catch(Exception ex) {
            System.out.println(ex);
            return new byte[0];
        }
    }
    public static char[] toChars(byte[] data) {
        if (data==null) {
            return new char[0];
        }
        try {
            synchronized(toChar) {
                return toChar.convertAll(data);
            }
        } catch(Exception ex) {
            System.out.println(ex);
            return new char[0];
        }
    }
    public static String toStringFast(byte[] data) {
        if (data==null) {
            return "";
        }
        char[] tmp = new char[data.length];
        for (int i=0; i<data.length; i++) {
            tmp[i] = (char)data[i];
        }
        return new String(tmp);
    }
    public static String quote(String s) {
        if (s.indexOf("'")<0) {
            return s;
        }
        StringBuffer sb = new StringBuffer();
        for (int i=0; i<s.length(); i++) {
            char c = s.charAt(i);
            sb.append(c);
            if (c=='\'') {
                sb.append(c);
            }
        }
        return sb.toString();
    }
    public static String toString(byte[] data) {
        if (data==null) {
            return "";
        }
        try {
            synchronized(toChar) {
                return new String(toChar.convertAll(data));
            }
        } catch(Exception ex) {
            System.out.println(ex);
            return "";
        }
    }
    /**
     * Convert a string for output to DBMS
     * @param s The string needs to be converted.
     * @return A string which will be truncated by JDBC to produce Big5 characters.
     */
    public static String toSQL(String s) {
        if (s==null) return "";
        if (!trans) return s;
        byte[] orig;
        try {
            synchronized(toChar) {
                orig = toByte.convertAll(s.toCharArray());
            }
            char[] dest = new char[orig.length];
            for (int i=0; i < orig.length; i++)
                dest[i] = (char)orig[i];
            return new String(dest);
        } catch (Exception e) {
            e.printStackTrace();
            return s;
        }
    }
    /**
     * Convert an incorrect string produeced by JDBC drivers to correct Unicode string.
     * @param s The string needs to be converted.
     * @return A correct Unicode string.
     */
    public static String fromSQL(String s) {
        int i, j;
        if (s==null) return "";
        if (!trans) return s;
        char[] orig = s.toCharArray();
        byte[] dest = new byte[orig.length];
        for (i=0; i < orig.length; i++)
            dest[i] = (byte) orig[i];
        try {
            synchronized(toChar) {
                char[] tmp = toChar.convertAll(dest);
                return new String(tmp);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return s;
        }
    }
}

查詢資料庫綱要
package ncnu.sql;
/* Program Name: Schema.java
   Subject: 資料綱要讀取程式
   Author: 俞旭昇 Shiuh-Sheng Yu
           National ChiNan University
           Department of Information Management
   Edit Date: 01/10/2000
   Last Update Date: 01/10/2000
   ToolKit: JDK1.1
*/

import java.sql.*;
import java.util.*;
import java.io.*;

public class Schema implements Serializable {
    Vector tables=null;
    String catalogSeparator=null, catalog, schema;
    Schema (Connection con, String catalog, String schema) {
        this.catalog = catalog;
        this.schema = schema;
        tables = new Vector();
        ForeignKey fk;
        try {
            ResultSet rs;
            DatabaseMetaData md = con.getMetaData();
            catalogSeparator = md.getCatalogSeparator();
            String[] types = new String[1];
            types[0] = "TABLE";
            // get all user defined tables
            rs = md.getTables(catalog,schema,null,types);
            while (rs.next()) {
                Table table = new Table();
                tables.addElement(table);
                table.catalogName = rs.getString(1);
                table.schemaName = rs.getString(2);
                table.tableName = rs.getString(3);
                table.totalName = getTotalName(table.catalogName,table.schemaName,table.tableName);
                table.tableType = rs.getString(4);
                table.remarks = rs.getString(5);
            }
            rs.close();
            // get all columns
            for (int i=0; i < tables.size(); i++) {
                Table check = (Table)tables.elementAt(i);
                rs = md.getColumns(check.catalogName, check.schemaName,check.tableName,null);
                while (rs.next()) {
                    Column col = new Column();
                    col.tableName = check.totalName;
                    col.columnName = rs.getString(4).trim();
                    col.dataType = rs.getInt(5);
                    col.typeName = rs.getString(6).trim();
                    col.columnSize = rs.getInt(7);
                    col.decimalDigits = rs.getInt(9);
                    col.radix = rs.getInt(10);
                    col.remarks = SQL.fromSQL(rs.getString(12));
                    col.columnDefault = SQL.fromSQL(rs.getString(13)).trim();
                    while (col.columnDefault.startsWith("'") || col.columnDefault.startsWith("(")) {
                        col.columnDefault = col.columnDefault.substring(1,col.columnDefault.length());
                    }
                    while (col.columnDefault.endsWith("'") || col.columnDefault.endsWith(")")) {
                        col.columnDefault = col.columnDefault.substring(0,col.columnDefault.length()-1);
                    }
                    col.isNullable = SQL.toString(rs.getBytes(18)).trim();
                    if (col.remarks==null || col.remarks.equals("")) {
                        col.remarks=col.columnName;
                    }
                    check.addColumn(col);
                }
                rs.close();
            }
            // get Primary key
            for (int i=0; i < tables.size(); i++) {
                Table check = (Table)tables.elementAt(i);
                rs = md.getPrimaryKeys(check.catalogName, check.schemaName,check.tableName);
                while (rs.next()) {
                    check.primaryKey.addElement(findColumn(check,rs.getString(4)));
                }
                rs.close();
            }
            // get Foreign Keys
            for (int i=0; i < tables.size(); i++) {
                Table check = (Table)tables.elementAt(i);
                rs = md.getImportedKeys(check.catalogName, check.schemaName,check.tableName);
                fk = null;
                while (rs.next()) {
                    String tmp1 = rs.getString(1); // remote primary catalog
                    String tmp2 = rs.getString(2); // remote primary schema
                    String tmp3 = rs.getString(3); // remote primary table
                    String tmp4 = rs.getString(4); // remote primary column
                    String tmp8 = rs.getString(8); // local foreign column
                    int tmp9 = rs.getInt(9);
                    if (tmp9 == 1) {
                        if (fk != null) {
                            check.addForeignKey(fk);
                        }
                        fk = new ForeignKey();
                    }
                    fk.primaryTable = findTable(tmp1,tmp2,tmp3);
                    fk.foreignTable = check;
                    fk.primary.addElement(findColumn(tmp1,tmp2,tmp3,tmp4));
                    fk.foreign.addElement(findColumn(check,tmp8));
                }
                rs.close();
                if (fk != null) {
                    check.addForeignKey(fk);
                }
            }
            // get Reference By
            for (int i=0; i < tables.size(); i++) {
                Table check = (Table)tables.elementAt(i);
                rs = md.getExportedKeys(check.catalogName, check.schemaName,check.tableName);
                fk = null;
                while (rs.next()) {
                    String tmp4 = rs.getString(4); // local primary column
                    String tmp5 = rs.getString(5); // remote foreign catalog
                    String tmp6 = rs.getString(6); // remote foreign schema
                    String tmp7 = rs.getString(7); // remote foreign table
                    String tmp8 = rs.getString(8); // remote foreign column
                    int tmp9 = rs.getInt(9);
                    if (tmp9 == 1) {
                        if (fk != null) {
                            check.addReferenceBy(fk);
                        }
                        fk = new ForeignKey();
                    }
                    fk.primaryTable = check;
                    fk.foreignTable = findTable(tmp5,tmp6,tmp7);
                    fk.primary.addElement(findColumn(check,tmp4));
                    fk.foreign.addElement(findColumn(tmp5,tmp6,tmp7,tmp8));
                }
                rs.close();
                if (fk != null) {
                    check.addReferenceBy(fk);
                }
            }
        } catch (SQLException ex) {
            ShowSQLException.show(ex);
        } finally {
            try {
               con.commit();
            } catch (Exception e2) {
            }
        }
    }

    public Table findTable(String catalog, String schema, String table) {
        return findTable(getTotalName(catalog, schema, table));
    }

    public Table findTable(String totalName) {
        int i, j;
        for (i=j=0; totalName.indexOf(catalogSeparator,i) != -1; j++) {
            i = totalName.indexOf(catalogSeparator,i) + catalogSeparator.length();
        }
        // How many full name still need to fill? eg student.name ==> ncnu.dbo.student.name
        if (j==0) { // we miss all
            totalName = catalog+catalogSeparator+schema+catalogSeparator+totalName;
        } else if (j==1) { // we miss catalogName
            totalName = catalog+catalogSeparator+totalName;
        } else { // we have all
        }
        for (i=0; i < tables.size(); i++) {
            if (((Table)tables.elementAt(i)).totalName.equals(totalName)) {
                return (Table)tables.elementAt(i);
            }
        }
        return null;
    }

    public Column findColumn(String totalName, String columnName) {
        Table t = findTable(totalName);
        if (t == null) {
            return null;
        }
        return findColumn(t, columnName);
    }

    public Column findColumn(String catalog, String schema, String table, String column) {
        Table t = findTable(catalog, schema, table);
        if (t == null) {
            return null;
        }
        return findColumn(t, column);
    }

    public Column findColumn(Table t, String column) {
        for (int i=0; i < t.columns.size(); i++) {
            if (((Column)t.columns.elementAt(i)).columnName.equals(column)) {
                return (Column)t.columns.elementAt(i);
            }
        }
        return null;
    }

    public String getTotalName(String catalog, String schema, String table) {
        String totalName = "";
        if (catalog!=null && catalog.trim().length()>0) {
            totalName = catalog.trim()+catalogSeparator;
        }
        if (schema!=null && schema.trim().length()>0) {
            totalName += schema.trim()+catalogSeparator;
        }
        if (table!=null && table.trim().length()>0) {
            totalName += table.trim();
        }
        return totalName;
    }

    public String getCatalogSeparator() {
        return catalogSeparator;
    }
    public Vector getTables() {
        return tables;
    }
}

Table.java如下

package ncnu.sql;
/* Program Name: Table.java
   Subject: 定義資料表格的屬性
   CopyRight: 俞旭昇 Shiuh-Sheng Yu
           National Chi-Nan University
           Institute of Management Information
   Edit Date: 01/03/1998
   Last Update Date: 01/04/1997
   ToolKit: JDK1.1.5
*/
import java.util.*;
import java.io.Serializable;
public class Table implements Serializable {
    String catalogName;
    String schemaName;
    String tableName;
    String totalName;
    String tableType;
    String remarks;
    Vector columns;
    Vector primaryKey;
    Vector foreignKeys;
    Vector referenceBy;
    Table() {
        columns = new Vector();
        foreignKeys = new Vector();
        primaryKey = new Vector();
        referenceBy = new Vector();
    }
    void addColumn(Column c) {
        columns.addElement(c);
    }
    Column getColumn(String cname) {
        for (int i=0; i < columns.size(); i++) {
            Column c = (Column)columns.elementAt(i);
            if (c.columnName.equalsIgnoreCase(cname)) {
                return c;
            }
        }
        return null;
    }
    void addForeignKey(ForeignKey fk) {
        foreignKeys.addElement(fk);
    }
    void removeForeignKey(Table t) {
        for (int i=0; i < foreignKeys.size(); i++) {
            ForeignKey fk = (ForeignKey)foreignKeys.elementAt(i);
            if (fk.primaryTable==t) {
                foreignKeys.removeElementAt(i);
                i--;
            }
        }
    }
    void addReferenceBy(ForeignKey fk) {
        referenceBy.addElement(fk);
    }
    void print() {
        System.out.println("Table: "+catalogName+"."+schemaName+"."+tableName);
        for (int i=0; i < columns.size(); i++) {
            ((Column)columns.elementAt(i)).print();
        }
        if (primaryKey != null) {
            System.out.println("PrimaryKey:");
            for (int i=0; i < primaryKey.size(); i++) {
                ((Column)primaryKey.elementAt(i)).print();
            }
        }
        if (foreignKeys != null) {
            for (int i=0; i < foreignKeys.size(); i++) {
                System.out.println("ForeignKey:");
                ((ForeignKey)foreignKeys.elementAt(i)).print();
            }
        }
        if (referenceBy != null) {
            for (int i=0; i < referenceBy.size(); i++) {
                System.out.println("Reference By:");
                ((ForeignKey)referenceBy.elementAt(i)).print();
            }
        }
    }
}

ForeignKey.java如下

package ncnu.sql;
/* Program Name: ForeignKey.java
   Subject: Foreign Key, used bye View.java
   CopyRight: 俞旭昇 Shiuh-Sheng Yu
           National ChiNan University
           Department of Information Management
   Edit Date: 01/03/1998
   Last Update Date: 08/21/1998
   ToolKit: JDK1.1.6
*/
import java.util.Vector;
import java.io.Serializable;
public class ForeignKey implements Serializable {
    Table primaryTable;
    Table foreignTable;
    Vector primary; // vector of Columns
    Vector foreign; // vecotr of Columns
    ForeignKey() {
        primary = new Vector();
        foreign = new Vector();
    }
    String getJoinCondition(String sep) {
        String cond = "";
        for (int i=0; i < primary.size(); i++) {
            Column x = (Column)primary.elementAt(i);
            Column y = (Column)foreign.elementAt(i);
            cond += x.tableName+sep+x.columnName+"="+y.tableName+sep+y.columnName+" and ";
        }
        return cond;
    }
    void print() {
        System.out.println("ForeignKey:");
        for (int i=0; i<primary.size(); i++) {
            ((Column)primary.elementAt(i)).print();
        }
        System.out.println("references");
        for (int i=0; i<foreign.size(); i++) {
            ((Column)foreign.elementAt(i)).print();
        }
    }
}

Column.java如下

package ncnu.sql;
/* Program Name: Column.java
   Subject: 定義資料欄位的屬性
   CopyRight: 俞旭昇 Shiuh-Sheng Yu
           National Chi-Nan University
           Institute of Management Information
   Edit Date: 01/03/1998
   Last Update Date: 01/04/1997
   ToolKit: JDK1.1.5
*/

import java.io.Serializable;
import java.sql.Types;
public class Column implements Serializable {
    String tableName;
    String columnName;
    String typeName;
    int dataType;
    int columnSize;
    int decimalDigits;
    int radix;
    String isNullable;
    String remarks;
    String columnDefault;
    public static boolean isNumeric(int type) {
        switch (type) {
            case Types.BIGINT:
            case Types.TINYINT:
            case Types.SMALLINT:
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.INTEGER:
            case Types.REAL:
            case Types.DOUBLE:
            case Types.BIT:
            return true;
        }
        return false;
    }
    void print() {
        System.out.print(tableName+"."+columnName+" "+typeName+" "+columnSize+" ");
        if (typeName.equals("numeric")) {
            System.out.print("decimal:"+decimalDigits+" radix:"+radix+" ");
        }
        System.out.println(isNullable+" "+remarks+" default:"+columnDefault);
    }
}

来自：http://programming.im.ncnu.edu.tw/J_index.html