JDBC之连接sqlserver数据库

柔情只为你懂 2024-02-18 18:52 59阅读 0赞

1.准备工作

打开SQLserver管理配置工具，选择SQLserver网络配置，选择MSSQLSERVER协议，启用TCP/IP

如下图所示

![20180323142715162][]

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

效果如下

![20180323143209556][]

[20180323142715162]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3c30ccc145d54f12be3bec313e125fd2.png
[20180323143209556]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/cbfee2c2de5b4962ab8e7f299cc86b53.png