SQLServerJDBC封装

1.测试SQL数据

-- 创建表
DROP DATABASE IF EXISTS user

CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    sex VARCHAR(2) DEFAULT '男' CHECK(sex IN('男', '女')),
    age INT
)

2.封装连接操作

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBUtil {
    // 1.创建链接
    private static final String URL = "jdbc:sqlserver://127.0.0.1:1433/test?characterEncoding=utf8&useSSL=false";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "0x11223344";
    private static DataSource dataSource = new SQLServerDataSource();

    static {
        ((SQLServerDataSource) dataSource).setURL(URL);
        ((SQLServerDataSource) dataSource).setUser(USERNAME);
        ((SQLServerDataSource) dataSource).setPassword(PASSWORD);
    }

    // 2.获取链接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // 3.关闭资源
    public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3.测试的一个简单的UserDAO类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class UserDAO {
    private static final Scanner scanner = new Scanner(System.in);

    // 1.增加用户
    public static void Create() {
        System.out.println("新用户注册操作：");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.链接
            connection = DBUtil.getConnection();
            // 2.拼装 SQL
            String sql = "INSERT INTO user VALUES(null,?,?,?)";
            System.out.print("用户名：");
            String name = scanner.nextLine();
            System.out.print("性别：");
            String sex = scanner.nextLine();
            System.out.print("年龄：");
            int age = scanner.nextInt();
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setString(2, sex);
            statement.setInt(3, age);
            // 3.执行 SQL
            int ret = statement.executeUpdate();
            if (ret == 1) {
                System.out.println("注册成功");
            } else {
                System.out.println("注册失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
    }

    // 2.删除用户【根据用户ID删除】
    public static void Delete() {
        System.out.println("销户操作：");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.链接
            connection = DBUtil.getConnection();
            // 2.拼装 SQL
            System.out.print("输入销户ID：");
            int id = scanner.nextInt();
            String sql = "DELETE FROM user WHERE id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            // 3.执行 SQL
            int ret = statement.executeUpdate();
            if (ret == 1) {
                System.out.println("销户成功成功");
            } else {
                System.out.println("销户失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
    }

    // 3.修改用户【根据用户ID修改】
    public static void Update() {
        System.out.println("更新用户信息操作：");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.链接
            connection = DBUtil.getConnection();
            // 2.拼装 SQL
            System.out.print("输入修改的的用户ID：");
            int id = scanner.nextInt();
            System.out.print("新的用户名：");
            String name = scanner.nextLine();
            System.out.print("新的性别：");
            String sex = scanner.nextLine();
            System.out.print("新的年龄：");
            int age = scanner.nextInt();
            String sql = "UPDATE user SET name=?, sex=?, age=? WHERE id=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setString(2, sex);
            statement.setInt(3, age);
            statement.setInt(4, id);
            // 3.执行 SQL
            int ret = statement.executeUpdate();
            if (ret == 1) {
                System.out.println("修改成功");
            } else {
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
    }

    // 4.查询用户【根据ID查询】
    public static void Retrieve() {
        System.out.println("销户操作：");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.链接
            connection = DBUtil.getConnection();
            // 2.拼装 SQL
            System.out.print("输入查询的用户ID：");
            int id = scanner.nextInt();
            String sql = "SELECT * FROM user WHERE id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            // 3.执行 SQL
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                System.out.println(
                        "姓名：" + name +
                                "性别：" + sex +
                                "年龄：" + age
                );
            } else {
                System.out.println("没有次用户ID");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
    }

    public static void main(String[] args) {
        // 1.增加用户
        Create();
        // 2.删除用户【根据用户ID删除】
        Delete();
        // 3.修改用户【根据用户ID修改】
        Update();
        // 4.查询用户【根据ID查询】
        Retrieve();
    }
}