1. JDBC技术
项目地址:https://gitee.com/ghzhouwei/springboot-data-example
1.1 数据库驱动
在我们的Java应用程序中不可避免的要和数据库打交道,通常使用较多的数据库是关系型数据库,比如Oracle数据库、MySQL数据库以及SQLServer数据库。针对不同的数据库各个数据库厂商都有自己的数据库驱动,Java程序员想要连接数据库必须学会如何使用这些数据库驱动的API。
那么问题就来了,如果我要连接Oracle数据库就需要会使用Oracle数据库的驱动,要想连接MySQL数据库就需要学会使用MySQL数据库的驱动。都是连接关系型数据库却要学习各个关系型数据库厂商的数据库驱动API,这要无疑增加了Java程序员的学习成本。因此基于这样的考虑,sun公司推出了JDBC规范,用来统一关系型数据库的连接方式,以后Java程序员只需要学习JDBC的API就可以轻松连接各种关系型数据库。
1.2 JDBC规范
- 规范:所谓的规范就是一个标准,它是对一类相似事物的定义准则,就类似于Java中的接口。比如手机生产规范,手机的生产必须提供打电话、听音乐和发短信的功能。至于各个手机厂商如何实现这个标准这不是我关心的事,华为怎么实现小米怎么实现不是我关心的事,只要满足手机的标准你生产的手机就是合格的。
- 实例:实例就是针对某个标准而产生的具体产品。比如小米按照手机规范实现了小米手机,华为按照手机标准实现了华为手机,小米手机和华为手机就是手机规范的实例。
JDBC(Java DataBase Connectivity)即Java数据库连接,它是Java语言针对数据库连接的一种规范,各个数据库厂商的数据库驱动就是对JDBC规范的一种实现,他们都满足这种标准。以前我们要连接不同的关系型数据库需要学会每个数据库厂商不同数据库驱动的API来操作数据库,有了JDBC以后我们不必去关心各个数据库驱动的不同API,而只需要学会JDBC的API就可以轻松的在各个关系型数据库中来回切换。当我们要替换数据库的时候只需要替换数据库的驱动,不需要对程序做过大的修改,这就是规范的好处。
1.3 JDBC步骤
既然JDBC是Java语言对关系型数据库连接的一种规范,那么它一定有着自己连接数据库的步骤,只要按照这个步骤就可以很轻松的用Java语言操作数据库,JDBC连接数据库的步骤如下:
第一步:注册驱动
第二步:获取连接
第三步:增删改查
第四步:关闭资源
- 注册驱动:关系型数据库多种多样,因此它们的数据库驱动也多种多样,我们必须告诉JDBC该使用什么样的驱动连接什么样的关系型数据库。
- 获取连接:告诉了JDBC我们以什么驱动连接什么样的关系型数据库以后,我们就可以通过驱动管理器得到一个数据库的连接,所有的数据库操作都是建立在这个连接上的。
- 增删改查:这部分就是我们业务最核心的部分,一切的努力都是为了从数据库得到我们想要的数据,此时SQL的功底是必须的。
- 关闭资源:像Oracle数据库、MySQL数据库和SQLServer数据库这些都是一种服务,与服务建立连接都是需要消耗资源的,既然增删改查都做完了我们就得释放资源,这一步是必不可少的。
2. JDBC-API
JDBC规范中提供了很多API,比如DriverManager、Connection、Statement、PrepareStatemen、ResultSet等。
- DriverManager:驱动管理器,通过这个类的静态方法getConnection我们可以获取到一个数据库的连接。
- Connection:数据库连接,它是操作数据库的基石,得到了连接才说明我们的java程序与数据库服务取得了联系。
- Statement:数据库操作对象,通过它来执行sql语句。这个类有两个比较重要的方法,executeUpdate和executeQuery,前者用于执行数据库的增删改等写操作,后者用于执行查等读操作。
- PreparedStatement:数据库操作对象,类似于Statement,它提供参数占位符用于组装sql语句,能解决sql注入问题,推荐使用这个类而不是使用Statement,它的灵活性符合Java程序员的操作方式。
- ResultSet:结果集对象,把它比作一张二维表不为过,通过它来解析查询到的记录。
3. JDBC案例
这里采用JDBC规范通过MySQL数据库驱动连接MySQL数据库实现对用户表的增删改查操作。
3.1 数据库建表
- 创建用户表,包含用户编号、用户账号、用户密码、用户性别和用户生日五个字段,并添加6条用户数据。
# 创建用户表
CREATE TABLE IF NOT EXISTS `user`
(
`pk_id` INT AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`sex` CHAR(1),
`birthday` DATETIME,
CONSTRAINT `pk_user` PRIMARY KEY(`pk_id`)
);
# 添加用户数据
INSERT INTO
`user`(`username`, `password`, `sex`, `birthday`)
VALUES
('刘备', '01234', '男', '2020-11-15 10:34:12'),
('关羽', '12345', '男', '2020-11-24 10:34:12'),
('张飞', '23456', '男', '2020-12-08 10:34:12'),
('赵云', '34567', '男', '2020-12-18 10:34:12'),
('大乔', '45678', '女', '2020-11-01 10:34:12'),
('小乔', '56789', '女', '2020-12-01 10:34:12');
3.2 构建项目
- 基于IntelliJ IDEA构建一个Maven的普通Java项目。
3.3 导入依赖
- 这里我们需要连接mysql数据库因此导入mysql驱动,外加一个Junit依赖用于做单元测试。
<!-- 依赖管理 -->
<dependencies>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<!-- junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
</dependencies>
3.4 创建实体
- 建立一个名为UserDO的java类,添加用户编号、用户账号、用户密码、用户性别和用户生日五个属性,提供对应属性的Getter和Setter方法以及toString方法。
package com.intest.jdbc.domain;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @ClassName: UserDO
* @Description: 用户实体类
* @Author: ZhouWei
* @Date: 2020-11-21 - 15:42
*/
public class UserDO implements Serializable
{
/**
* 用户编号
*/
private Integer id;
/**
* 用户账号
*/
private String userName;
/**
* 用户密码
*/
private String passWord;
/**
* 用户性别
*/
private String sex;
/**
* 用户生日
*/
private Date birthday;
/**
* Getter和Setter方法
*/
public Integer getId()
{
return id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getUserName()
{
return userName;
}
public void setUserName(String userName)
{
this.userName = userName;
}
public String getPassWord()
{
return passWord;
}
public void setPassWord(String passWord)
{
this.passWord = passWord;
}
public String getSex()
{
return sex;
}
public void setSex(String sex)
{
this.sex = sex;
}
public Date getBirthday()
{
return birthday;
}
public void setBirthday(Date birthday)
{
this.birthday = birthday;
}
/**
* toString方法
* @return
*/
@Override
public String toString()
{
return "UserDO{" +
"id=" + id +
", userName='" + userName + '\'' +
", passWord='" + passWord + '\'' +
", sex='" + sex + '\'' +
", birthday=" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(birthday) +
'}';
}
}
3.5 连接测试
- 编写测试类来看看JDBC连接数据库的步骤,在增删改查步骤中进行连接对象的地址打印,针对不同的业务只需要修改此处。
package com.intest.jdbc;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @ClassName: JDBCTest
* @Description: JDBC测试类
* @Author: ZhouWei
* @Date: 2020-11-21 - 15:52
*/
public class JDBCTest
{
/**
* 连接测试
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void connectionTest() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:打印连接对象地址
System.out.println("connection = " + connection);
// 4.关闭资源
connection.close();
}
}
3.6 添加数据
- 向数据库的用户表添加一条数据,只需要修改第三步增删改查步骤,定义好需要执行的sql语句,通过连接对象获取数据库操作对象,然后通过数据库操作对象执行sql语句并得到操作结果,对操作结果进行打印。
- 值得注意的是这里引入了数据库的操作对象statement,这也属于资源需要关闭。Java中资源关闭的规则,先获取的后关,后获取的先关。statement资源是在connection资源的基础上获取的,因此要先关闭statement资源在关闭connection资源。这就好比我们关门一样,我们优先关闭最里面的门,最后才关闭最外面的门。
/**
* 添加数据
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void insertTest() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:添加数据
// 定义sql语句
String sql = "insert into user(pk_id, username, password, sex, birthday) values(7, '周瑜', 'abcdef', '男', '2002-08-12 10:45:50')";
// 获取数据库操作对象
Statement statement = connection.createStatement();
// 执行sql语句并得到结果
int count = statement.executeUpdate(sql);
// 打印结果
System.out.println("count = " + count);
// 4.关闭资源
statement.close();
connection.close();
}
- java程序添加数据得到添加记录的个数
- 用户周瑜已经被添加到数据库用户表中
3.7 删除数据
- 从数据库用户表中删除一条记录,定义好删除数据的sql语句,通过连接对象得到操作对象,然后执行sql语句得到sql语句执行结果进行打印。
/**
* 删除数据
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void deleteTest() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:删除数据
// 定义sql语句
String sql = "delete from user where pk_id = 7";
// 获取数据库操作对象
Statement statement = connection.createStatement();
// 执行sql语句并获得结果
int count = statement.executeUpdate(sql);
// 打印结果
System.out.println("count = " + count);
// 4.关闭资源
statement.close();
connection.close();
}
- Java程序得到删除记录的个数
- 用户周瑜的信息已经被删除
3.8 修改数据
- 在数据库用户表中修改一条记录,将用户编号为6的大乔修改为孙尚香。
/**
* 修改数据
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void updateTest() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:修改数据
// 定义sql语句
String sql = "update user set username = '孙尚香' where pk_id = 6";
// 获取数据库操作对象
Statement statement = connection.createStatement();
// 执行sql语句并获得结果
int count = statement.executeUpdate(sql);
// 打印结果
System.out.println("count = " + count);
// 4.关闭资源
statement.close();
connection.close();
}
- Java程序得到修改记录的个数
- 大乔修改为孙尚香
3.9 查询数据
- 从数据库用户表中查询所有用户记录进行打印。
- 这里引入一个新的对象resultSet,它是执行查询语句所获取的结果集,可以把它看作是一个二维表,这是资源需要关闭。
/**
* 查询数据
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void queryTest() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:查询数据
// 定义sql语句
String sql = "select pk_id, username, password, sex, birthday from user";
// 获取数据库操作对象
Statement statement = connection.createStatement();
// 执行sql语句并获得结果
ResultSet resultSet = statement.executeQuery(sql);
// 遍历结果集
while (resultSet.next())
{
// 提取数据
Integer id = resultSet.getInt("pk_id");
String userName = resultSet.getString("username");
String passWord = resultSet.getString("password");
String sex = resultSet.getString("sex");
Date birthday = resultSet.getDate("birthday");
// 组装实体
UserDO user = new UserDO();
user.setId(id);
user.setUserName(userName);
user.setPassWord(passWord);
user.setSex(sex);
user.setBirthday(birthday);
// 打印实体
System.out.println("user = " + user);
}
// 4.关闭资源
resultSet.close();
statement.close();
connection.close();
}
- Java程序打印结果
4. SQL注入
4.1 SQL注入问题
- sql注入问题一般发生在java程序需要执行的sql语句的部分参数由外部用户输入,而用户输入的参数拼接到sql中改变了原本sql的语义,导致执行结果与预期结果不一致,这是程序上的BUG。
下面以模拟用户登录来解释SQL注入问题的产生,具体流程如下:
(1)用户输入登录账号和登录密码。
(2)根据账号和密码两个参数去拼接sql语句,在数据库中进行查询。
(3)结果判断。如果有数据则用户输入的登录账号和密码正确,即登录成功。如果没有数据则用户输入的登录账号和密码不正确,即登录失败。
/**
* SQL注入
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void test01() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:查询数据
// 模拟用户输入的登录账号与密码
String userName = "刘备";
String passWord = "01234";
// 定义sql语句
String sql = "select pk_id, username, password, sex, birthday from user where username = '" + userName +"' and password = '" + passWord + "'";
// 打印sql语句
System.out.println("sql = " + sql);
// 获取数据库操作对象
Statement statement = connection.createStatement();
// 执行sql语句并得到结果集
ResultSet resultSet = statement.executeQuery(sql);
// 是否有数据
boolean flag = resultSet.next();
// 判断
if (flag)
{
System.out.println("登录成功");
}
else
{
System.out.println("登录失败");
}
// 4.关闭资源
resultSet.close();
statement.close();
connection.close();
}
- 数据库用户表数据
定义用户登录信息为userName = “刘备” passWord = “01234”,程序执行为登录成功,打印sql如下:
select pk_id, username, password, sex, birthday from user where username = ‘刘备’ and password = ‘01234’
定义用户登录信息为userName = “刘备” passWord = “012”,程序执行为登录失败,因为密码不对,打印sql如下:
select pk_id, username, password, sex, birthday from user where username = ‘刘备’ and password = ‘012’
定义用户登录信息为userName = “xxx” passWord = “sss’ or ‘1’ = '1”,程序执行为登录成功,打印sql如下:
select pk_id, username, password, sex, birthday from user where username = ‘xxx’ and password = ‘sss’ or ‘1’ = ‘1’
注意第三次的登录测试,数据库用户表中是没有用户账号为xxx用户密码为sss’ or ‘1’ = '1这个用户的,但依旧是登录成功。很显然用户输入的密码有诡异,他给我们的sql语句中的where条件添加了一个 or ‘1’ = '1’这个永远为true的BUG,导致账号无论输入什么都是能查询到数据的。这就是所谓的SQL注入问题,它使我们的SQL语义发生了改变。
4.2 解决注入问题
针对sql的注入问题,JDBC的API提供了相应的解决办法。Statement已经不再适合我们的需求,java提供了PreparedStatement来解决sql注入。PreparedStatement采用 ? 进行占位,采用sql预编译的方式来解决sql的注入,而且在多次执行相同sql语句时它的执行效率要优于Statement。
接下来通PreparedStatement来了解一下如何通过占位符来操作数据库。
/**
* 占位符
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void test02() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:查询数据
// 定义sql语句,采用占位符
String sql = "select pk_id, username, password, sex, birthday from user where pk_id = ?";
// 获取数据库操作对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 设置占位符的值
preparedStatement.setInt(1, 1);
// 执行sql语句并获取结果集
ResultSet resultSet = preparedStatement.executeQuery();
// 遍历结果集
while (resultSet.next())
{
// 提取数据
Integer id = resultSet.getInt("pk_id");
String userName = resultSet.getString("username");
String passWord = resultSet.getString("password");
String sex = resultSet.getString("sex");
Date birthday = resultSet.getDate("birthday");
// 组装实体
UserDO user = new UserDO();
user.setId(id);
user.setUserName(userName);
user.setPassWord(passWord);
user.setSex(sex);
user.setBirthday(birthday);
// 打印实体
System.out.println("user = " + user);
}
// 4.关闭资源
resultSet.close();
preparedStatement.close();
connection.close();
}
将上面模拟登录的程序中Statement替换为PreparedStatement解决SQL注入。
/**
* 解决SQL注入
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void test03() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:查询数据
// 模拟用户输入的登录账号与密码
String userName = "xxx";
String passWord = "sss' or '1' = '1";
// 定义sql语句
String sql = "select pk_id, username, password, sex, birthday from user where username = ? and password = ?";
// 获取数据库操作对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 设置参数值
preparedStatement.setString(1, userName);
preparedStatement.setString(2, passWord);
// 执行sql语句并得到结果集
ResultSet resultSet = preparedStatement.executeQuery();
// 是否有数据
boolean flag = resultSet.next();
// 判断
if (flag)
{
System.out.println("登录成功");
}
else
{
System.out.println("登录失败");
}
// 4.关闭资源
resultSet.close();
preparedStatement.close();
connection.close();
}
此时我们再用userName = “xxx” passWord = "sss’ or ‘1’ = '1"的用户信息登录会发现登录失败,SQL注入的问题得到了解决。
5. 元数据
元数据就是对一张张二维表的描述,我们通过JDBC来获取到数据库表的元数据,解剖数据表得到列的个数、列的类型以及列的字段。这点类似于java中的反射,可以获取到一个类的信息。目前有些逆向工程可以根据数据库的表生成对应的Java实体类,这些都需要借助于元数据来进行。
@Test
public void metaTest() throws ClassNotFoundException, SQLException
{
// 定义相关参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/zw_test";
String username = "root";
String password = "root";
// 1.注册驱动
Class.forName(driverName);
// 2.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 3.增删改查:获取元数据
// 定义sql语句
String sql = "select * from user";
// 获取数据库操作对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 获取元数据集
ResultSetMetaData metaData = preparedStatement.getMetaData();
// 获取列数
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++)
{
// 获取列类型
String columnTypeName = metaData.getColumnTypeName(i);
System.out.println("columnTypeName = " + columnTypeName);
// 获取列字段
String columnLabel = metaData.getColumnLabel(i);
System.out.println("columnLabel = " + columnLabel);
}
// 4.关闭资源
preparedStatement.close();
connection.close();
}