如何通过Java代码操作数据库？

痛点：如果项目中使用MySQL数据库，怎么通过Java代码操作数据库？
MySQL数据库应该MySQL的开发人员最了解其底层实现，应该提供一套Java接口给我们去使用，同样如果使用的是Oracle的数据库，Oracle的公司应该提供一套Java接口给我们去使用。如果使用的是DB2数据库，DB2公司的开发人员应该提供一套Java接口给我们使用。但是有一个问题是，如果项目一开始使用的是MySQL想改成Oracle的数据库，就会造成所有使用MySQL接口的地方换成Oracle的，为了解决这种问题，Java官方推出了JDBC。
JDBC - 图1

JDBC: Java Database Connectivity
JDBC只是定义了一套接口，具体实现有各大数据库厂商实现。属于JavaSE的一部分。
以后Java开发人员只需使用JDK自带的JDBC去操作数据库就可以了。JDBC会判断使用的是哪个数据库的实现进行调用。

MySQL

下载MySQL的官方实现jar包，也叫驱动包。所谓驱动包就是对JDBC的实现。
MySQL下载地址
Snipaste_2021-08-27_11-23-10.png

JDBC使用步骤

a) 注册Driver到DriverManager

  1. DriverManager.registerDriver(new Driver());

为了简单起见并且对MySQL的Driver没有依赖，我们通常这样注册Driver到DriverManager

  1.  Class.forName("com.mysql.cj.jdbc.Driver");

这一步JDBC就知道了底层数据库用的是MySQL还是其他的数据库，从Java6开始这一步可以省略，只需将下载的MySQL的jar包放在类路径里面(add as library)，DriverManager就会自动检测并加载驱动程序。

b) 利用JDBC里面的DriverManager创建数据库连接

  1.  Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/customer_test","root","root");

因为使用的是MySQL数据库，MySQL规定连接时使用的URL格式为

  1. jdbc:mysql://ip地址:端口号/数据库名称

上面为规定固定写法

c) 利用Connection对象创建Statement语句对象

  1. Statement statement = connection.createStatement();

d) 利用Statement语句对象执行SQL

  1. statement.execute("UPDATE `customer_test`.`user` SET `age` = 9 WHERE `id` = 1");

e) 关闭资源

  1. statement.close();
  2. connection.close();

上面使用步骤使用的都是JDBC的代码，与底层数据库实现没有依赖。如果以后更换其他数据库，只需改动如下

  1. Class.forName("com.mysql.cj.jdbc.Driver");
  2. DriverManager.getConnection("jdbc:mysql://localhost:3306/customer_test","root","root");

只需改动注册的字符串、链接时的URL、用户名、密码就可以了。其他都不需要改动。而且以后肯定是要把这些变动的字符串放在配置文件里面，这样一句代码也不用改变。

Statement的常用API

  1. ResultSet resultSet = statement.executeQuery("SELECT *from user");

返回查询到的结果集。

  1. while (resultSet.next()){
  3.     String name =  resultSet.getString("name");
  4.     int age =  resultSet.getInt("age");
  6. }

当第一次调用resultSet.next()时，会指向第一条数据，这个遍历结果使用的是迭代器模式。
如果指向的这一行有数据就会返回true，否则返回false。
resultSet.getString() 传入数据库表里面的字段名称(如果查询的时候指定了字段的别名，使用别名也可以)。并将表里面的数据转为String类型。
resultSet.getInt() 将表里面的数据变为int类型。获取到表里面的数据后希望转为什么类型就调用相应的getxxx()。

  • statement.executeUpdate()

用于执行插入、删除、修改等

Statement不安全问题

典型的案例就是登录时SQL注入。
当用户进行账号登录时，输入账号、密码发送到后端后，后端需要在数据库里面查询有没有该用户，并且验证输入的账号密码是否正确。

  1. String userName = "李付发";
  2. String password = "456";
  3. String sql = "SELECT *FROM user WHERE name = '" + userName+"' AND password = '" +password+"'";
  4. System.out.println(sql);
  5. //        String sql = "SELECT *FROM user WHERE name = '李付发' AND password = '456'";
  6. ResultSet resultSet = statement.executeQuery(sql);
  7. if (resultSet.next()){ //查询出来的数据要么一条要么没有，有表示登录成功
  8.     System.out.println("登录成功");
  9. }else {
  10.     System.out.println("登录失败");
  11. }

如果数据库里面有该用户就会登录成功，这样并没有什么问题，但是如果用户登录时输入的用户名和密码是如下内容，就会显示登录成功，然而数据库里面并没有该记录。

  1. String userName = "";
  2. String password = " ' OR '1' = '1";
  3. String sql = "SELECT *FROM user WHERE name = '" + userName+"' AND password = '" +password+"'";
  4. System.out.println(sql);
  5. ResultSet resultSet = statement.executeQuery(sql);
  6. if (resultSet.next()){ //查询出来的数据要么一条要么没有，有表示登录成功
  7.     System.out.println("登录成功");
  8. }else {
  9.     System.out.println("登录失败");
  10. }

上面的SQL语句的查询是

  1. SELECT *FROM user WHERE name = '' AND password = ' ' OR '1' = '1'

无论用户名输入的是什么都会显示登录成功。

为了解决上面的问题，在开发中通常使用PreparedStatement

  1. String userName = "";
  2. String password = " ' OR '1' = '1";
  3. //使用?作为占位
  4. String sql = "SELECT *FROM user WHERE name = ? AND password = ? ";
  6. PreparedStatement statement = connection.prepareStatement(sql);
  7. //设置SQL问号的占位参数
  8. statement.setString(1,userName); //占位符从1开始，设置第一个？的值
  9. statement.setString(2,password); //设置第2个？的值
  11. ResultSet resultSet = statement.executeQuery();
  12. if (resultSet.next()){ //查询出来的数据要么一条要么没有，有表示登录成功
  13.     System.out.println("登录成功");
  14. }else {
  15.     System.out.println("登录失败");
  16. }

为什么这种方式能防止SQL注入？
会先对SQL进行特殊符号转义，比如：

  1. SELECT *FROM user WHERE name = '' AND password = ' ' OR '1' = '1'

进行转义为

  1. SELECT *FROM user WHERE name = '' AND password = ' \' OR \'1\' = \'1'

转义后的OR以及OR后面的内容就变为password的值了。

关于PreparedStatement

PreparedStatement继承自Statement，属于JDBC的一部分。
PreparedStatement优点：

  • 效率高
  • 防止SQL注入
  • 支持批量处理

项目添加很多文件后报资源找不到，可以重新启动一下Tomcat

也可以这样干
Snipaste_2021-08-28_08-39-18.png
Snipaste_2021-08-28_08-39-52.png

Snipaste_2021-08-28_08-40-25.png

DAO

我们不能直接将访问数据库代码直接放到Servlet里面进行处理，访问数据库代码应该封装起来。这一个菜鸟教程写的的足够简单、易于理解
对于很多共同的代码应该封装起来，比如数据库的链接以及关闭，对于个性化的代码留在DIO里面(比如SQL语句以及设置SQL的参数、bean模型数据)。

配置文件

对于经常修改的值，建议放在配置文件中，不要写死在Java代码中。
Java中常见的配置文件

  • properties: 适合量小、比较简单的数据配置

  • xml : 比较灵活、适合量大、比较复杂的配置

    properties使用

    在src下新建properties文件(最好要在resources下新建哈，让类加载器知道文件在哪)
    关于加载资源路径的讨论
    Snipaste_2021-08-30_10-38-05.png
    会弹出如下框，输入文件名即可
    Snipaste_2021-08-30_08-56-38.png
    properties文件用于存储键值对的

  • key和value的分割符，使用=或者:分割，建议分隔符两边不要留空格

  • 、!是单行注释

  • 可以使用\反斜线连接多行内容
    1. #这是用户名
    2. userName=lff
    3. !这是密码
    4. password=123
    5. #反斜线\用于连接多行，类似C语音的宏定义
    6. url=http://localhost?location=上海\
    7. goods=123
    解析、获取、处理上面DB.properties文件
    1. InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("DB.properties");
    2. //sdk提供的处理类，简化io处理操作
    3. Properties properties = new Properties();
    4. try {
    5.   properties.load(inputStream);
    6.   String userName = properties.getProperty("userName");
    7.   String password = properties.getProperty("password");
    8.   String url = properties.getProperty("url");
    9.   System.out.println(userName + "__" + password + "__" +url);
    10. } catch (IOException e) {
    11.   e.printStackTrace();
    12. }

数据库连接池

这里简要的说明了为什么需要连接池以及相关库的使用方式
基本原理
a) 在初始化时，创建一定数量的数据库连接对象存放在内存中
b) 当访问数据库时，从连接池中取出已经创建好的空闲链接,如果没有空闲的链接才会创建新的
c) 使用完毕后，将链接放入连接池中（并不是关闭链接），以供下一个请求使用
d) 当链接的空闲时间超过设置的最大空闲时间时就会释放掉该链接

druid的使用

GitHub地址
JDBC连接池有一个标准的接口javax.sql.DataSource，这个类位于Java标准库中，但仅仅是接口。要使用JDBC连接池，我们必须选择一个JDBC连接池的实现，这里选择阿里开源的druid。

maven配置

  1. <dependency>
  2.         <groupId>com.alibaba</groupId>
  3.         <artifactId>druid</artifactId>
  4.         <version>${druid-version}</version>
  5. </dependency>

jar包下载地址

druid.properties配置

  1. driverClassName=com.mysql.jdbc.Driver
  2. url=jdbc:mysql://127.0.0.1:3306/xr2
  3. username=root
  4. password=root
  5. initialSize=5
  6. maxActive=10
  7. maxWait=5000

使用方式

  1. try (InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("druid.properties")) {
  2.     Properties properties = new Properties();
  3.     properties.load(inputStream);
  4.     DataSource ds = DruidDataSourceFactory.createDataSource(properties);
  5.     //ds.getConnection(); 使用这种方式获取链接对象
  7. } catch (Exception e) {
  8.     e.printStackTrace();
  9. }

Spring JDBC

W3C链接
Spring提供了一个核心类JdbcTemplate，这个类可以无缝衔接连接池，只需在创建JdbcTemplate时，把上面的DataSource注册到里面就可以了。JdbcTemplate有一个构造函数如下

  1. public JdbcTemplate(DataSource dataSource)

通过上面的构造函数创建JdbcTemplate对象后。可以很方便的使用该对象里面的如下功能进行数据操作：

  • execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
  • update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
  • query方法及queryForXXX方法：用于执行查询相关语句；
  • call方法：用于执行存储过程、函数相关语句。