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*/

 // 導(dǎo)入驅(qū)動(dòng)jar包
    // 注冊驅(qū)動(dòng)
    // mysql5之后的驅(qū)動(dòng)java包可以省略注冊驅(qū)動(dòng)的步驟
    // Class.forName("com.mysql.cj.jdbc.Driver");
    // 連接字符串 jdbc:mysql://ip:port/數(shù)據(jù)庫/characterEncoding=字符集&serverTimezone=使用什么時(shí)間&useSSL=是否開啟SSL
    String url =
        "jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";
    // 獲取數(shù)據(jù)庫連接對(duì)象
    Connection connection = DriverManager.getConnection(url, "root", "543241338");
    // 定義sql語句
    String sql = "select * from users";
    // 獲取sql執(zhí)行對(duì)象
    Statement statement = connection.createStatement();
    // 執(zhí)行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();

建議使用參數(shù)化方式

 /** 參數(shù)化查詢方法 */
    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);
    // 返回值操作成功條數(shù)
    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);
    // 返回值操作成功條數(shù)
    int i = preparedStatement.executeUpdate();
    System.out.println(i);
    preparedStatement.close();
    connection.close();

批量插入操作

/** 批量執(zhí)行方法 */
    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();
    // 執(zhí)行所有批處理
    preparedStatement.executeBatch();
    preparedStatement.close();
    connection.close();

事物的使用

/** 事物的使用 */
    PreparedStatement preparedStatement = null;
    try {
      // 關(guān)閉自動(dòng)提交開啟事物
      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();
      // 執(zhí)行所有批處理
      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