创建表名

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for jdbc_test
-- ----------------------------
DROP TABLE IF EXISTS `jdbc_test`;
CREATE TABLE `jdbc_test`  (
  `id` int NOT NULL,
  `username` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of jdbc_test
-- ----------------------------
INSERT INTO `jdbc_test` VALUES (1, '张三');
INSERT INTO `jdbc_test` VALUES (2, '李四');

SET FOREIGN_KEY_CHECKS = 1;

代码实现

添加

    // 插入一条记录 id=3、username=王五
    @SneakyThrows
    @Test
    void JDBC_Test_ADD() {
        String DB_Url = "jdbc:mysql://127.0.0.1:3306/zlk?useUnicode=true";
        String sql = "insert into jdbc_test values(?,?)";

        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection(DB_Url, "root", "root");
        //获取 PreparedStatement 对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //封装SQL
        preparedStatement.setInt(1, 3);
        preparedStatement.setString(2, "王五");
        //PreparedStatement 执行执行操作,获取结果集
        boolean execute = preparedStatement.execute();
        if (execute) {
            System.out.println("插入成功");
        } else {
            System.out.println("这句话是废话，如果上面执行不成功，一般就是由异常了！");
        }

        //释放资源
        preparedStatement.close();
        connection.close();
    }

删除

    // 删除姓名为王五的记录
    @SneakyThrows
    @Test
    void JDBC_Test_Delete() {
        String DB_Url = "jdbc:mysql://127.0.0.1:3306/zlk?useUnicode=true";
        String sql = "delete from jdbc_test where username = ?";

        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection(DB_Url, "root", "root");
        //获取 PreparedStatement 对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //封装SQL 注意第一位参数的索引下标是 1 而不是0
        preparedStatement.setString(1, "王五");
        //PreparedStatement 执行执行操作,获取结果集
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("删除成功，受到影响的行数是：" + i);
        } else {
            System.out.println("这句话是废话，如果上面执行不成功，一般就是由异常了！");
        }

        //释放资源
        preparedStatement.close();
        connection.close();
    }

修改

    // 将王五的id 修改为4
    @SneakyThrows
    @Test
    void JDBC_Test_Update() {
        String DB_Url = "jdbc:mysql://127.0.0.1:3306/zlk?useUnicode=true";
        String sql = "update jdbc_test set id = ? where username = ? limit ?,?";
        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection(DB_Url, "root", "root");
        //获取 PreparedStatement 对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //封装SQL
        preparedStatement.setInt(1, 4);
        preparedStatement.setString(2, "王五");

        //PreparedStatement 执行执行操作,获取结果集
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("修改成功，受影响的行数：" + i);
        } else {
            System.out.println("这句话是废话，如果上面执行不成功，一般就是由异常了！");
        }

        //释放资源
        preparedStatement.close();
        connection.close();
    }

查询

    // 查询第一页 并展示十条记录
    @SneakyThrows
    @Test
    void JDBC_Test_Select() {
        String DB_Url = "jdbc:mysql://127.0.0.1:3306/zlk?useUnicode=true";
        String sql = "select * from jdbc_test limit ?,?";
        int pageNum = 1;
        int pageSize = 10;
        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection(DB_Url, "root", "root");
        //获取 PreparedStatement 对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, (pageNum - 1) * pageSize);
        preparedStatement.setInt(2, pageSize);
        //PreparedStatement 执行执行操作,获取结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        List<User> userList = new ArrayList<>();
        //遍历结果集取出对象
        while (resultSet.next()) {
            //从1开始，起始索引并不是0
            User userOldWay = new User(resultSet.getInt(1), resultSet.getString(2));
            userList.add(userOldWay);
        }
        System.out.println(userList);

        //释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

JDBC缺点：

• 代码重复
• 还要自己封装对象
• 不停都创建对象
• 不停的销毁对象

所以程序员都不会再用JDBC了，而是使用ORM Mybatis 框架