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.尝试运行

效果如下