1、JDBC的使用

MYSQL脚本

CREATE TABLE USERS(
    ID INT(10) NOT NULL AUTO_INCREMENT  COMMENT 'primary key' ,
    NAME VARCHAR(30)    COMMENT 'user name' ,
    AGE INT(10)    COMMENT 'user age' ,
    CREATED_AT DATETIME    COMMENT 'created time' ,
    UPDATED_AT DATETIME    COMMENT 'updated time' ,
    PRIMARY KEY (ID)
) COMMENT = 'user ';/*SQL@Run*/

 // 导入驱动jar包
    // 注册驱动
    // mysql5之后的驱动java包可以省略注册驱动的步骤
    // Class.forName("com.mysql.cj.jdbc.Driver");
    // 连接字符串 jdbc:mysql://ip:port/数据库/characterEncoding=字符集&serverTimezone=使用什么时间&useSSL=是否开启SSL
    String url =
        "jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";
    // 获取数据库连接对象
    Connection connection = DriverManager.getConnection(url, "root", "543241338");
    // 定义sql语句
    String sql = "select * from users";
    // 获取sql执行对象
    Statement statement = connection.createStatement();
    // 执行sql语
    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()) {
      System.out.println(resultSet.getInt("id"));
      System.out.println(resultSet.getString("name"));
      System.out.println(resultSet.getString("age"));
    }
    statement.close();
    connection.close();

建议使用参数化方式

 /** 参数化查询方法 */
    PreparedStatement preparedStatement =
        connection.prepareStatement("SELECT * FROM users WHERE id=?");
    preparedStatement.setInt(1, 1);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
      System.out.println(resultSet.getInt("id"));
      System.out.println(resultSet.getString("name"));
      System.out.println(resultSet.getString("age"));
    }
    preparedStatement.close();
    connection.close();

更新操作

/** 更新操作 */
    PreparedStatement preparedStatement = connection.prepareStatement("UPDATE users SET age=?");
    preparedStatement.setInt(1, 18);
    // 返回值操作成功条数
    int i = preparedStatement.executeUpdate();
    System.out.println(i);
    preparedStatement.close();
    connection.close();

删除操作

/** 删除操作 */
    PreparedStatement preparedStatement =
        connection.prepareStatement("DELETE FROM users WHERE id=?");
    preparedStatement.setInt(1, 2);
    // 返回值操作成功条数
    int i = preparedStatement.executeUpdate();
    System.out.println(i);
    preparedStatement.close();
    connection.close();

批量插入操作

/** 批量执行方法 */
    PreparedStatement preparedStatement =
        connection.prepareStatement(
            "INSERT INTO users (name, age, created_at, updated_at) VALUES (?,?,?,?)");
    preparedStatement.setString(1, "tip");
    preparedStatement.setInt(2, 20);
    preparedStatement.setDate(3, new java.sql.Date(new java.util.Date().getTime()));
    preparedStatement.setDate(4, new java.sql.Date(new java.util.Date().getTime()));
    // 添加批处理操作
    preparedStatement.addBatch();
    preparedStatement.setString(1, "土鳖");
    preparedStatement.setInt(2, 20);
    preparedStatement.setDate(3, new java.sql.Date(new java.util.Date().getTime()));
    preparedStatement.setDate(4, new java.sql.Date(new java.util.Date().getTime()));
    // 添加批处理操作
    preparedStatement.addBatch();
    // 执行所有批处理
    preparedStatement.executeBatch();
    preparedStatement.close();
    connection.close();

事物的使用

/** 事物的使用 */
    PreparedStatement preparedStatement = null;
    try {
      // 关闭自动提交开启事物
      connection.setAutoCommit(false);
      preparedStatement =
          connection.prepareStatement(
              "INSERT INTO users (name, age, created_at, updated_at) VALUES (?,?,?,?)");
      preparedStatement.setString(1, "tip");
      preparedStatement.setInt(2, 20);
      preparedStatement.setDate(3, new java.sql.Date(new java.util.Date().getTime()));
      preparedStatement.setDate(4, new java.sql.Date(new java.util.Date().getTime()));
      // 添加批处理操作
      preparedStatement.addBatch();
      preparedStatement.setString(1, "土鳖");
      preparedStatement.setInt(2, 20);
      preparedStatement.setDate(3, new java.sql.Date(new java.util.Date().getTime()));
      preparedStatement.setDate(4, new java.sql.Date(new java.util.Date().getTime()));
      // 添加批处理操作
      preparedStatement.addBatch();
      // 执行所有批处理
      preparedStatement.executeBatch();
      // 提交事物 一次提交所有操作
      connection.commit();

    } catch (Exception ex) {
      // 事物回滚
      connection.rollback();
    } finally {
      if (preparedStatement != null) {
        preparedStatement.close();
      }
      if (connection != null) {
        connection.close();
      }
    }

教程可以参考 https://www.yiibai.com/jdbc