JDBC串連SQL Server 2000

今天寫了一個通過JDBC串連SQL SERVER的類供初學者參考,最近因為要做一個作業所以又開始看JAVA,在接觸.NET後簡直感覺這2種平台有很多相似的地方.

資料庫的測試代碼,由於時間有限這個沒有寫注釋,在SQL SERVER 2000中通過測試:

CREATE DATABASE test on

(
       name='test_data',
       filename='D:/project/java/DBtest2/test_data.mdf',
       size=1MB,
       maxsize=10MB
)

log on

(
   name='test_log',
   filename='D:/project/java/DBtest2/test_log.ldf',
   size=1MB,
   maxsize=5MB
)
use test

create table friends
(
    f_id int identity(1,1) primary key,
    f_name varchar(20) not null,
    f_address varchar(30),
    f_tel varchar(19)unique,
    f_hiredate datetime,
    f_salary money
)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Shawn','Chengdu',150008712341,'2006-11-12',1000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Chris','NorthOA',150008712321,'2005-5-22',2000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Cayla','Chengdu',150002512341,'2004-7-6',3000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Sean','Chengdu',150002716341,'2006-1-1',4000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('Matt','Mahiami',150008718541,'2006-4-25',5000)
insert into friends(f_name,f_address,f_tel,f_hiredate,f_salary) values('John','USA',150008582341,'2003-2-28',6000)

select *from friends

以下是JAVA的代碼,資料庫的ODBC配置請自己搞定:

/*
利用Java串連SQL SERVER的測試代碼.僅供初學者使用.
Shawn@CopyLeft
*/
import java.sql.*;     //需要加入的包

//類的定義
class DBConnect {
    private String con1 = "sun.jdbc.odbc.JdbcOdbcDriver"; //串連SQL所需要的字串
    private String url = "jdbc:odbc:test";
    private String user = "sa", password = ""; //這裡請根據您的資料庫使用者和密碼自行修改
    Connection con;           //用於串連資料庫用的
    PreparedStatement ps;     //其實用Statement也行,PreparedStatement整合了Statement.
    ResultSet rs;             //一個集合,可以用於執行SQL命令

    //建構函式
    DBConnect() {
        try {
            Class.forName(con1); //Class.forName()用於將一些類載入到JVM
            this.Connect();   //函數調用
            try {
                this.execute();
            } catch (SQLException ex) {
                System.out.println(ex.toString());
            }
        } catch (ClassNotFoundException ce) {
            System.out.println(ce);
        }
    }

    public void Connect() {
        try {
            con = DriverManager.getConnection(url, user, password); //做這部之前先把ODBC配置好
            if (con != null) {
                System.out.println("Connection Sucessfully!");
            }

        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
    }

    public void execute() throws SQLException {
        ps = con.prepareStatement("select *from friends"); //把SQL語句搞給ps
        rs = ps.executeQuery();                            //這裡執行,之後讓rs知道資訊
        while (rs.next())                                 //這裡必須加next(),位移量移動.
        {
            System.out.print(rs.getString(2) + "/t");
            System.out.print(rs.getString(3) + "/t");
            System.out.print(rs.getString(4) + "/t");
            System.out.print(rs.getDate(5) + "/t");
            System.out.print(rs.getInt(6) + "/t");
            System.out.println("");
        }
    }

    public void close()                        //用於釋放資源,Java裡沒有解構函式,但是通過重寫protected void finalize(),
    {                                        //之後在調用System.runFinalization()和System.gc()可以提醒JVM執行finalize()以釋放,
        try{                                 //在以前的J2SE版本裡可以通過以上方法調用finalize(),但目前的J2SE5.0隻能提醒JVM,但JVM不一定執行
        rs.close();                          //最好的方案還是自己寫析構close();
        ps.close();
        con.close();
        }catch(SQLException ce)
        {
            System.out.println(ce.toString());
        }
        System.out.println("Connection released!!!");

    }

    public static void main(String[] args) {
        DBConnect dbc=new DBConnect();      
        dbc.close();
    }
}