JDBC之連線sqlserver資料庫

1.準備工作

開啟SQLserver管理配置工具，選擇SQLserver網路配置，選擇MSSQLSERVER協議，啟用TCP/IP

如下圖所示

2.在SQLSERVER中編寫並執行SQL指令碼

drop database if exists test;

create database test;

use test;

CREATE TABLE dept
( dno CHAR(3) PRIMARY KEY,
  dname VARCHAR(20) not null,
  phone CHAR(4)
);

CREATE TABLE emp
( eno CHAR(3) PRIMARY KEY,
  ename VARCHAR(10) not null,
  sex CHAR(2),
  birthday DATE,
  sal int not null,
  dno CHAR(3),
  foreign key(dno) references dept(dno)
);

insert into dept values('d01','人事處','2255'),
                       ('d02','財務處','3395'),
					   ('d03','裝置處','3787');

insert into	emp values('e01','張小紅','女','1975-3-12','3400','d01'),
                      ('e02','何東明','男','1966-12-8','4000','d02'),
					  ('e03','李群生','男','1980-4-23','2100','d03');
					   

3.編寫JDBC程式

package com.msjdbc;


import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class Test {
    public static void main(String[] args) {
        Connection conn;
        Statement stmt;
        ResultSet rs;
        String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test;";
        String sql = "select * from emp";
        try {
            // 連線資料庫
            conn = DriverManager.getConnection(url, "sa", "123789");
            // 建立Statement物件
            stmt = conn.createStatement();
            /**
             * Statement createStatement() 建立一個 Statement 物件來將 SQL 語句傳送到資料庫。
             */
            // 執行資料庫查詢語句
            rs = stmt.executeQuery(sql);
            /**
             * ResultSet executeQuery(String sql) throws SQLException 執行給定的 SQL
             * 語句，該語句返回單個 ResultSet 物件
             */
            while (rs.next()) {
                String id = rs.getString("eno");
                String name = rs.getString("ename");
                String age = rs.getString("sex");
                System.out.println("eno:" + id + "\tename:" + name + "\tsex:" + age);
            }
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("資料庫連線失敗");
        }
    }
}

4.嘗試執行

效果如下