JDBC实现CRUD

doties

已于 2023-11-19 11:05:37 修改

阅读量194

点赞数 5

分类专栏： jdbc 文章标签： java

于 2023-11-19 10:51:09 首次发布

本文链接：https://blog.csdn.net/aspe9x/article/details/134487932

版权

jdbc 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

JDBC实现CRUD

建表sql

CREATE TABLE `t_user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR (30) NOT NULL COMMENT '姓名',
  `sex` VARCHAR (1) COMMENT '性别，0：女，1：男'
) ;

insert操作

@Test
public void testInsert() {
    // 定义链接参数
    String url = "jdbc:mysql://192.168.1.200:3306/test";
    String user = "admin";
    String password = "test123";

    Connection connection = null;
    Statement statement = null;

    // sql参数
    String name = "'张三'";
    int sex = 1;

    try {
        // 加载驱动类，并将实例注册到DdriverManager
        Class.forName("com.mysql.jdbc.Driver");
        // 获取链接
        connection = connection = DriverManager.getConnection(url, user, password);
        // 创建Statement对象
        statement = connection.createStatement();

        String insertSql = "insert into t_user(name, sex) values(" + name + ", " + sex + ")";
        int i = statement.executeUpdate(insertSql, Statement.RETURN_GENERATED_KEYS);    // Statement.RETURN_GENERATED_KEYS 用于主键回填
        System.out.println("影响行数：" + i);
        ResultSet generatedKeys = statement.getGeneratedKeys();
        if (generatedKeys.next()) {
            // 获取主键
            System.out.println("主键：" + generatedKeys.getInt(1));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 关闭资源
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

select操作

@Test
public void testSelect() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.1.200:3306/test", "admin", "test123");
        Statement statement = connection.createStatement();

        String selectParam = "0 or 1=1";
        String selectSql = "select name from t_user where id = " + selectParam;
        ResultSet resultSet = statement.executeQuery(selectSql);
        while(resultSet.next()) {
            String string = resultSet.getString(1);
            System.out.println(string);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } // 需要关闭资源，此处省略。。。
}

/**
 * 上述案例不难看出Statement采用拼接方式处理sql, 存在注入问题
 *  通过PreparedStatement预编译sql可以解决
 */
@Test
public void testSelectByPreparedStatement() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://192.168.1.200:3306/test", "admin", "test123");
        preparedStatement = connection.prepareStatement("select id, name, sex from t_user where id = ?"); // 使用占位符预编译sql
        preparedStatement.setInt(1, 1); // 设置sql参数，第一个单数表示参数位置（第几个问号），第二个参数表是对应位置的参数值
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt(1);   // 通过index取值，第一个字段的columnIndex为1
            String name = resultSet.getString("name"); // 通过字段名称取值
            int sex = resultSet.getInt("sex");
            System.out.println("id:" + id + ", name:" + name + ", sex:" + sex);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

update操作

@Test
public void testUpdate() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.1.200:3306/test", "admin", "test123");
        PreparedStatement preparedStatement = connection.prepareStatement("update t_user set name = ? where id = ?");
        preparedStatement.setString(1, "李四");
        preparedStatement.setInt(2, 1);
        int i = preparedStatement.executeUpdate();
        System.out.println("影响行数：" + i);

    } catch (Exception e) {
        e.printStackTrace();
    } // 需要关闭资源，此处省略。。。

}

delete操作

@Test
public void testDelete() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.1.200:3306/test", "admin", "test123");
        PreparedStatement preparedStatement = connection.prepareStatement("delete from t_user where id = ?");
        preparedStatement.setInt(1, 1);
        int i = preparedStatement.executeUpdate();
        System.out.println("影响行数：" + i);
    } catch (Exception e) {
        e.printStackTrace();
    }
} // 需要关闭资源，此处省略。。。