【JDBC】preparedstatement执行 dml 语句练习

PS：curd是增删改查的缩写

新学知识：如何获取一列的数据

具体代码实现：

JUnit测试包是IDEA中自带提供的，直接 alt + enter 导入即可。

package com.atguigu.api.statement.preparedstatement;

import org.junit.Test;

import java.sql.*;
import java.util.*;

/**
 * 写四个Test方法（public开头 并且 Test方法不能有返回值，方法名随便写，但不能有形参列表）
 */
public class PSCURDPart {

    /**
     * 插入
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    //测试方法需要导入junit的测试包
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/itcast?user=root&password=123456");

        //3.编写sql语句，动态值的部分使用?代替
        String sql = "insert into employee(id, salary) values(?, ?);";

        //4.创建preparedStatement，并且传入SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.占位符赋值
        //其实是可以写getInt的，但建议统一写Object，因为这样就不需要考虑了
        preparedStatement.setObject(1, 1);
        preparedStatement.setObject(2, 23798347);

        //6.发送sql语句
        int rows = preparedStatement.executeUpdate();//返回的是影响的一个行数

        //7.输出结果
        if (rows > 0) {
            System.out.println("插入成功");
        } else {
            System.out.println("插入失败");
        }

        //8.关闭资源
        connection.close();
        preparedStatement.close();
    }

    /**
     * 更新
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/itcast?user=root&password=123456");

        //3.编写sql语句
        String sql = "update employee set id = ?, salary = ? where id = ?";

        //4.创建preparedstatement对象，并传入sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.给?赋值
        preparedStatement.setObject(1, 10);
        preparedStatement.setObject(2, 199);
        preparedStatement.setObject(3, 1);

        //6.发送sql语句
        int rows = preparedStatement.executeUpdate();

        //7.处理返回结果
        if (rows > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }

        //8.关闭资源
        connection.close();
        preparedStatement.close();
    }

    /**
     * 删除
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.提供连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/itcast?user=root&password=123456");

        //3.编写sql语句
        String sql = "delete from Employee where id = ?;";

        //4.创建preparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //5.给?赋值
        preparedStatement.setObject(1, 3);

        //6.发送sql语句
        int rows = preparedStatement.executeUpdate();

        //7.处理返回结果
        if (rows > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }

        //8.关闭资源
        connection.close();
        preparedStatement.close();
    }
}