🚀进修MySQL~(伍)

PreparedStatement
- SQL 注入问题
- 区别用法
IDEA+SQL
JDBC+事务
连接池
各名词区别

PreparedStatement

SQL 注入问题

SQL 注入问题及解决

通过修改 SQL 指令字符串,使 SQL 服务器执行不正规或者恶意的 SQL 命令
比如客户端可以通过修改 SQL 字符串,可以使服务器传回原本不能显示的数据,或者修改非指定的数据.

区别用法

PreparedStatement 可以防止 SQL 注入,而且效率更高.

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import twenty_one.jdbc.demo2.utils.JDBCUtils;

public class TestPreparedStatement {
  private static Connection connection = null;
  private static PreparedStatement preparedStatement = null;
  private static ResultSet resultSet = null;

  public static void main(String[] args) throws SQLException {
    // 连接
    connection = JDBCUtils.getConnection();

    // ! 插入
    // 问号'?'为占位符
    String insert = "INSERT INTO jdbcstudy.users(NAME, PASSWORD, email, birthday)  VALUES (?,?,?,?);";
    //预编译sql
    preparedStatement = connection.prepareStatement(insert);
    //手动赋值
    preparedStatement.setString(1, "weidows");
    preparedStatement.setInt(2, 123456);
    preparedStatement.setString(3, "utsuko27@qq.com");
    preparedStatement.setDate(4, new Date(new java.util.Date().getTime()));
    // 执行
    if (preparedStatement.executeUpdate() > 0) {
      System.out.println("成功.");
    }

    // ! 查询
    String query = "SELECT * From jdbcstudy.users where name = ?;";
    preparedStatement = connection.prepareStatement(query);
    preparedStatement.setString(1, "weidows");
    resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
      System.out.println("id = " + resultSet.getObject("id"));
      System.out.println("name = " + resultSet.getObject("NAME"));
      System.out.println("password = " + resultSet.getObject("PASSWORD"));
      System.out.println("email = " + resultSet.getObject("email"));
      System.out.println("birthday = " + resultSet.getObject("birthday"));
      System.out.println("===============================================================");
    }

    // 断连
    JDBCUtils.releaseConnection(connection, preparedStatement, resultSet);
  }
}

IDEA+SQL

图形化的,摸索摸索就差不多会了.
在学这个 SQL 课程时,个人开始用的是 sqlyog,后来卸载了,直接用的 idea 内嵌的.

JDBC+事务

创建表+插入数据

create TABLE jdbcstudy.account
(
  id    int primary key auto_increment,
  name  varchar(255),
  money DECIMAL
);

insert into jdbcstudy.account(name, money)
VALUES ('A', 1000),
      ('B', 1000),
      ('C', 1000);

事务

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import twenty_one.jdbc.demo2.utils.JDBCUtils;

public class TestTransaction {
  private static Connection connection = null;
  private static PreparedStatement preparedStatement = null;
  private static ResultSet resultSet = null;

  public static void main(String[] args) throws SQLException {
    try {
      connection = JDBCUtils.getConnection();
      //关闭数据库的自动提交功能,同时开启事务
      connection.setAutoCommit(false);

      String sql = "update account set money = money-500 where id = 1";
      preparedStatement = connection.prepareStatement(sql);
      preparedStatement.executeUpdate();

      String sql2 = "update account set money = money-500 where id = 2";
      preparedStatement = connection.prepareStatement(sql2);
      preparedStatement.executeUpdate();

      //业务完毕，提交事务
      connection.commit();
      System.out.println("事务操作成功");
    } catch (Exception e) {
      //如果失败，默认会回滚,也可以在这里自定义
      connection.rollback();
      e.printStackTrace();
    } finally {
      JDBCUtils.releaseConnection(connection, preparedStatement, resultSet);
    }
  }
}

连接池

概念上与线程池相同,因为频繁创建和断开连接耗费大量资源,利用池化技术来减轻资源损耗.
数据库连接池接口 DataSource ,开源数据源实现类:
- DBCP
- C3P0
- Druid: 阿里巴巴
无论使用什么数据源，本质是不变的，DateSource 接口不会变，方法就不会变

用法区别就是 utils 类中,加载驱动,连接 connection 和读取 properties 配置文件不需要我们自己写了

db.properties

# DBCP的设置文件,属性名不能错
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 & useSSL = true
username=root
password=123456

utils.DbcpUtils

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DbcpUtils {
  private static Properties properties = new Properties();
  private static DataSource dataSource = null;

  static {
    // 加载连接信息
    try {
      properties.load(new FileInputStream("Java/src/main/java/twenty_one/jdbc/demo3/db.properties"));
      dataSource = BasicDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  // 获取连接
  public static Connection getConnection() throws SQLException {
    return dataSource.getConnection();
  }

  // 释放资源
  public static void releaseConnection(Connection connection, Statement statement, ResultSet resultSet)
      throws SQLException {
    if (resultSet != null) {
      resultSet.close();
    }
    if (statement != null) {
      statement.close();
    }
    if (connection != null) {
      connection.close();
    }
  }
}

TestDataSource

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import twenty_one.jdbc.demo3.utils.DbcpUtils;

public class TestDataSource {
  private static Connection connection = null;
  private static PreparedStatement preparedStatement = null;
  private static ResultSet resultSet = null;

  public static void main(String[] args) throws SQLException {
    // 连接
    connection = DbcpUtils.getConnection();

    // ! 插入
    // 问号'?'为占位符
    String insert = "INSERT INTO jdbcstudy.users(NAME, PASSWORD, email, birthday)  VALUES (?,?,?,?);";
    //预编译sql
    preparedStatement = connection.prepareStatement(insert);
    //手动赋值
    preparedStatement.setString(1, "weidows");
    preparedStatement.setInt(2, 123456);
    preparedStatement.setString(3, "utsuko27@qq.com");
    preparedStatement.setDate(4, new Date(new java.util.Date().getTime()));
    // 执行
    if (preparedStatement.executeUpdate() > 0) {
      System.out.println("成功.");
    }

    // ! 查询
    String query = "SELECT * From jdbcstudy.users where name = ?;";
    preparedStatement = connection.prepareStatement(query);
    preparedStatement.setString(1, "weidows");
    resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
      System.out.println("id = " + resultSet.getObject("id"));
      System.out.println("name = " + resultSet.getObject("NAME"));
      System.out.println("password = " + resultSet.getObject("PASSWORD"));
      System.out.println("email = " + resultSet.getObject("email"));
      System.out.println("birthday = " + resultSet.getObject("birthday"));
      System.out.println("===============================================================");
    }

    // 断连
    DbcpUtils.releaseConnection(connection, preparedStatement, resultSet);
  }
}