学习总结（2022.05.10-2022.05.16）

// 1.加载驱动  
      DriverManager.registerDriver(new Driver());

// String url = "协议 + ip + 端口 + 路径 + 参数";
      String url = "jdbc:mysql://localhost:3306/40th?useSSL=false&characterEncoding=utf8";
      String username = "root";
      String password = "123456";

      // 2.发送用户名和密码，建立连接
      // 返回的当前是一个Connection接口，但是实际上在运行的时候，返回是Connection接口的实现类的实例
      Connection connection = DriverManager.getConnection(url, username, password);

      // 3.获取statement对象
      Statement statement = connection.createStatement();

      // 4.发送SQL语句
// 增删改使用executeUpdate方法
      int affectedRows = statement.executeUpdate("insert into stu values (4,'云飞兄',20,'358班')");
// 查使用executeQuery方法
ResultSet resultSet = statement.executeQuery("select * from stu");

      // 5.解析结果集
      System.out.println("affectedRows:" + affectedRows);
while (resultSet.next()) {
	int id = resultSet.getInt("id");
	String name = resultSet.getString("name");
          int age = resultSet.getInt("age");
          String className = resultSet.getString("class");

          System.out.println("id:" + id);
          System.out.println("name:" + name);
          System.out.println("age:" + age);
          System.out.println("className:" + className);
}

      // 6.断开连接
resultSet.close();
      statement.close();
      connection.close();

// 在创建PreparedStatement的时候，PreparedStatement会把当前这个没有参数的SQL语句，发送给MySQL服务器，执行预编译
// 预编译：其实就是去解析这个SQL语句中的关键字，变成MySQL可以执行的命令
// 在预编译之后，后续输入的字符串，就只会被MySQL当成纯文本来解析
PreparedStatement preparedStatement = connection.prepareStatement("select * from user where name = ? and password = ?");

// 设置参数
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);

// 传递参数，执行SQL语句
ResultSet resultSet = preparedStatement.executeQuery();

// for循环
public static void batchUseForEach() throws SQLException {

    Statement statement = connection.createStatement();

    for (int i = 0; i < 10000; i++) {

        String sql = "insert into user values ("+i+",'foreach',null,null)";

        statement.executeUpdate(sql);

    }
}

// Statement处理
public static void batchUseStatement() throws SQLException {

    Statement statement = connection.createStatement();

    for (int i = 10000; i < 20000; i++) {
        String sql = "insert into user values ("+i+",'batchUseStatement',null,null)";
        statement.addBatch(sql);
    }

    // 发送SQL语句，执行
    statement.executeBatch();
}

// PreparedStatement处理
public static void batchUsePrepapreStatement() throws SQLException {

    // 获取PreparedStatement
    PreparedStatement preparedStatement = connection.prepareStatement("insert into user values (?,?,null,null)");

    // 循环，设置参数
    for (int i = 20000; i < 30000; i++) {
        preparedStatement.setInt(1,i);
        preparedStatement.setString(2,"PrepapreStatement");
        preparedStatement.addBatch();
    }

    // 把参数发送给MySQL服务器，执行SQL语句
    preparedStatement.executeBatch();
}

//  开启事务
connection.setAutoCommit(false);

// 提交事务
connection.commit();

// 回滚事务
connection.rollback();

// 加载配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("dbcp.properties"));

// 获取连接
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();

dataSource = new ComboPooledDataSource();

dataSource = DruidDataSourceFactory.createDataSource(properties);

// 关闭连接
public static void close(Connection conn) throws SQLException;

// 关闭ResultSet
public static void close(ResultSet rs) throws SQLException;

// 关闭statement
public static void close(Statement stmt) throws SQLException;

// 提交并且关闭
public static void commitAndClose(Connection conn) throws SQLException;

// 构造无参的queryRunner
QueryRunner queryRunner = new QueryRunner();
// 构造QueryRunner
DataSource dataSource = DruidUtils.getDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);

// 获取增删改影响的行数
int affectedRows = queryRunner.update("update stu set name = ? where id = ?", "嘉靖皇帝", 4);