真理并无尽头

常用API

HeJin大约 5 分钟数据库技术JDBC

在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：

  • Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
  • PreparedStatement：语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
  • CallableStatement：用于执行 SQL 存储过程。

Statement更新

@Test
public void test5() throws SQLException, ClassNotFoundException, IOException {
    // 1.定义要素
    Properties properties = new Properties();
    properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
    String driverName = properties.getProperty("driverName");
    String url = properties.getProperty("url");
    String username = properties.getProperty("username");
    String password = properties.getProperty("password");

    // 2.加载驱动,实例化驱动。并注册驱动
    // 仅仅需要jvm加载一下
    Class.forName(driverName);

    // 4.获取连接
    Connection connection = DriverManager.getConnection(url, username, password);

    // 5.创建
    Statement statement = connection.createStatement();
    String sql = "insert into person (name, age) values ('ww', 40)";
    int affectRows = statement.executeUpdate(sql);
    System.out.println(affectRows);
}

1

Process finished with exit code 0

Statement查询

Java与SQL数据类型转换表

Java类型SQL类型
booleanBIT
byteTINYINT
shortSMALLINT
intINTEGER
longBIGINT
StringCHAR,VARCHAR...
byteBINARY
java.sql.Date（日期，没有时间）DATE
java.sql.Time（只有时间）TIME
java.sql.TimestampTIMESTAMP
@Test
public void test5() throws SQLException, ClassNotFoundException, IOException {
    // 1.定义要素
    Properties properties = new Properties();
    properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
    String driverName = properties.getProperty("driverName");
    String url = properties.getProperty("url");
    String username = properties.getProperty("username");
    String password = properties.getProperty("password");

    // 2.加载驱动,实例化驱动。并注册驱动
    // 仅仅需要jvm加载一下
    Class.forName(driverName);

    // 4.获取连接
    Connection connection = DriverManager.getConnection(url, username, password);

    // 5.创建
    Statement statement = connection.createStatement();

    String sql = "select id, name, age from person";
    ResultSet resultSet = statement.executeQuery(sql);
    List<User> userList = new ArrayList<>();
    while (resultSet.next()){
        int id = resultSet.getInt(1);
        String name = resultSet.getString(2);
        int age = resultSet.getInt(3);

        User user = new User();
        user.setId(id);
        user.setName(name);
        user.setAge(age);
        userList.add(user);
    }

    System.out.println(userList);
}

结果：

[User{id=1, name='zs', age=26}, User{id=5, name='ww', age=40}]

Process finished with exit code 0

SQL注入

简单模拟一下登录：输入正确的用户名，根据用户名查询信息。正常来说，输入的用户名数据库中没有就查不出信息。

public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
    // 输入用户名和密码
    Scanner scanner = new Scanner(System.in);
    System.out.println("请输入用户名: ");
    String username = scanner.nextLine();

    // 登录
    String sql = "select id, name, age from person where name = '" + username + "'" ;
    Connection connection = DbUtils.getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()){
        User user = new User();
        user.setId(resultSet.getInt(1));
        user.setName(resultSet.getString(2));
        user.setAge(resultSet.getInt(3));

        System.out.println(user);
    }
}

请输入用户名: 
zs
User{id=1, name='zs', age=26}

Process finished with exit code 0

随便输入：

请输入用户名: 
122

Process finished with exit code 0

但是如果我输入下面的，就会查询出数据库的全部数据。数据就泄露了。

xxx' or 1 = '1

请输入用户名: 
xxx' or 1 = '1
sql: select id, name, age from person where name = 'xxx' or 1 = '1'
User{id=1, name='zs', age=26}
User{id=5, name='ww', age=40}

Process finished with exit code 0

这时，where后面拼接了一个永远为true的条件，就会查询整张表。相当于or之前的条件全部失效了。

如果是进行用户名和密码的校验，将会出现用户名和密码都是错的情况，但是进入了系统，这就是非常不安全的了。数据就全部泄露了。这就是SQL注入。SQL注入有很多办法，这是最简单的一种，我们要避免SQL注入。Statement是无法避免的。

PreparedStatement的使用

mysql预编译

  • 通常我们发送一条SQL语句给MySQL服务器时，MySQL服务器每次都需要对这条SQL语句进行校验、解析等操作。
  • 但是有很多情况下，我们的【一条SQL语句】可能需要反复的执行，每次执行可能仅仅是传递的参数不同而已，类似于这样的SQL语句如果每次都需要进行校验、解析等操作，未免太过于浪费性能了，因此产生了SQL语句的预编译。
  • 所谓【预编译】就是将一些灵活的参数值以占位符?的形式给代替掉，我们把参数值给抽取出来，把SQL语句进行模板化。让MySQL服务器执行相同的SQL语句时，不需要在校验、解析SQL语句上面花费重复的时间。
-- 预编译sql
PREPARE st from 'SELECT * FROM person WHERE id = ?';

SET @id = 1;
EXECUTE st USING @id;

SET @id = 5;
EXECUTE st USING @id;

PreparedStatement

默认使用PreparedStatement是【不能执行预编译】的，这需要在url中给出useServerPrepStmts=true参数（MySQL Server 4.1之前的版本是不支持预编译的，而Connector/J在5.0.5以后的版本，默认是没有开启预编译功能的。

useServerPrepStmts=true&cachePrepStmts=true

为了查看效果，我们打开mysql的通用查询日志：

show VARIABLES like '%general_log%';
SET GLOBAL general_log=1;

ublic static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
    // 输入用户名和密码
    Scanner scanner = new Scanner(System.in);
    System.out.println("请输入用户名: ");
    String username = scanner.nextLine();

    // 登录
    String sql = "select id, name, age from person where name = ?" ;
    System.out.println("sql: " + sql);
    // xxx' or 1 = '1
    Connection connection = DbUtils.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, username);
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()){
        User user = new User();
        user.setId(resultSet.getInt(1));
        user.setName(resultSet.getString(2));
        user.setAge(resultSet.getInt(3));

        System.out.println(user);
    }
}

输入：

请输入用户名: 
xxx' or 1 = '1
sql: select id, name, age from person where name = ?

Process finished with exit code 0

MySQL服务端没有开启预编译：

2022-12-02T07:00:21.021462Z	   77 Query	SET character_set_results = NULL
2022-12-02T07:00:21.021769Z	   77 Query	SET autocommit=1
2022-12-02T07:00:21.036760Z	   77 Query	select id, name, age from person where name = 'xxx'' or 1 = ''1'

开启预编译：

2022-12-02T07:04:09.662687Z	   78 Query	SET character_set_results = NULL
2022-12-02T07:04:09.663076Z	   78 Query	SET autocommit=1
2022-12-02T07:04:09.680767Z	   78 Prepare	select id, name, age from person where name = ?
2022-12-02T07:04:09.685223Z	   78 Execute	select id, name, age from person where name = 'xxx\' or 1 = \'1'
Copyright © 2023 HeJin