使用 JDBC 連接資料庫(SQL Server)

使用 JDBC 連接資料庫相當簡單,首先根據自己的需要下載對應的 jar 檔,以 SQL Server 2000 為例,需要下載 sqljdbc4.jar ,下載之後 add 到專案中, 接著在程式碼中連接資料庫,一個簡單的範例如下
ConnToSQLServer.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnToSQLServer{
    //show tables in database
    public static String COMM_SHOW_ALLTABLES = "select * from sysobjects where xtype = 'U';";
    //show version of database
    public static String COMM_SHOW_VERSION = "SELECT @@VERSION AS 'SQL Server Version'";
    //show all databases
    public static String COMM_SHOW_ALLDATABASER = "SELECT * FROM master.dbo.sysdatabases";
    //show system config of database
    public static String COMM_SHOW_CONFIGURES = "SELECT * FROM master.dbo.sysconfigures";
    Connection conn;
    String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String url;
    String ipNum;
    String portNum;
    String dbName;
    String userName;
    String passWord;
    /**
     *
     * @param ipnum : ip
     * @param portnum : port
     * @param dbname : dbname
     * @param username
     * @param password
     */
    public F_ConnToSQLServer(String ipnum,String portnum,
            String dbname,String username,String password){
        ipNum = ipnum;
        portNum = portnum;
        dbName = dbname;
        userName = username;
        passWord = password;
        try {
            Class.forName(driver);
            url= "jdbc:sqlserver://"+ipNum+";databaseName="+dbName+";integratedSecurity=false";
            try {
                if (userName == null && passWord == null) {
                    conn = DriverManager.getConnection(url);
                } else {
                    // url , user , password
                    conn = DriverManager.getConnection(url, userName, passWord);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public Connection getConn(){
        return conn;
    }
}

 
只要建立 ConnToSQLServer 物件,在建構子中傳入帳號和密碼，就能建立和資料庫連線
使用範例
JdbcExam.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectSqlServerExam {
    public static void main(String[] args) {
        F_ConnToSQLServer connpgsql = new F_ConnToSQLServer("192.168.17.128",
                "5432", "dbname", "user", "password");
        Connection conn = connpgsql.getConn();
        String comm = "select * from tablename";
        execComm(conn, comm);
    }
    public static void execComm(Connection conn, String comm) {
        Statement stmt;
        ResultSet rs;
        ResultSetMetaData rsmd = null;
        try {
            stmt = conn.createStatement();
            if (stmt.execute(comm)) {
                rs = stmt.getResultSet();
                rsmd = rs.getMetaData();
                while (rs.next()) {
                    for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                        if (i != rsmd.getColumnCount()) {
                            System.out.print(rs.getString(i) + "  ");
                        } else {
                            System.out.print(rs.getString(i));
                        }
                    }
                    System.out.println();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

最後就能在主控台看到結果了