JDBC操作数据库
JDBC操作数据库
1、检查MySQL版本和JDBC驱动版本
首先检查当前MySQL数据库版本
直接去官方网站上查看与之匹配的驱动
所以这里准备的驱动是8.0开头的即可。
2、工程搭建
参考：https://blog.csdn.net/k1507157/article/details/122367217
对应代码：
public class JdbcExample {
// 直接去lib/META-INF/services下面去拷贝即可
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// 数据库的用户名与密码
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// 三大资源类型！注意请勿重复创建即可。后续会专门使用
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println("实例化Statement对象，即执行SQL语句的对象...");
stmt = conn.createStatement();
String sql = "SELECT * FROM student";
// 将结果集对象保存到了ResultSet对象中来。这个对象也是一个资源类型
rs = stmt.executeQuery(sql);
// 展开结果集数据库
while (rs.next()) {
// 通过字段检索
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 姓名: " + name);
System.out.print(", 年龄: " + age);
System.out.print(", 生日: " + birthday);
System.out.println();
}
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
显示结果：
3、SQL注入
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
演示一下，首先看对应的SQL：
String sql = "SELECT * FROM student where name = 'lig' and age = 22";
加入说这里模仿的是登录，输入用户名密码登录，上面的方式是可以的。
但是总是有坏人写了下面的一条SQL：
String sql = "SELECT * FROM student where name = 'lig' and age = 22 or 1=1";
看一下对应的代码和结果：
public class SqlInjectExample {
// 直接去lib/META-INF/services下面去拷贝即可
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// 数据库的用户名与密码
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// 三大资源类型！注意请勿重复创建即可。后续会专门使用
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println("实例化Statement对象，即执行SQL语句的对象...");
stmt = conn.createStatement();
String sql = "SELECT * FROM student where name = 'lig' and age = 22 or 1=1";
// 将结果集对象保存到了ResultSet对象中来。这个对象也是一个资源类型
rs = stmt.executeQuery(sql);
// 展开结果集数据库
while (rs.next()) {
// 通过字段检索
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 姓名: " + name);
System.out.print(", 年龄: " + age);
System.out.print(", 生日: " + birthday);
System.out.println();
}
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
结果显示：
通过这种恶意的方式，对SQL来进行操作，也能够达到这样的查询所有的效果。那么如何来避免这种情况呢？
答案是修改执行SQL语句的执行器对象。
将Statement修改成其子类PreparedStatement ，通过名字也可以看到有一个预处理的概念。
所谓的预处理首先对SQL进行编译，编译之后得到的SQL中的参数用'?'来占用，在执行的时候才会将对应的参数值代替对应的？
下面来看一下：
public class SqlInjectResultExample {
// 直接去lib/META-INF/services下面去拷贝即可
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// 数据库的用户名与密码
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// 三大资源类型！注意请勿重复创建即可。后续会专门使用
Connection conn = null;
// 获取得到预处理对象
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println("实例化Statement对象，即执行SQL语句的对象...");
String sql = "SELECT * FROM student where name = ? and age = ? ";
stmt = conn.prepareStatement(sql);
stmt.setString(1,"lig");
stmt.setInt(2,22);
// 将结果集对象保存到了ResultSet对象中来。这个对象也是一个资源类型
rs = stmt.executeQuery();
// 展开结果集数据库
while (rs.next()) {
// 通过字段检索
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 姓名: " + name);
System.out.print(", 年龄: " + age);
System.out.print(", 生日: " + birthday);
System.out.println();
}
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
从这里可以看到获取得到预处理对象之后，预处理和原来的普通执行SQL的对象有着不同。
stmt = conn.createStatement();
String sql = "SELECT * FROM student where name = 'lig' and age = 22 or 1=1";
// 将结果集对象保存到了ResultSet对象中来。这个对象也是一个资源类型
rs = stmt.executeQuery(sql);
============================================================================================================
stmt = conn.prepareStatement(sql);
stmt.setString(1,"lig");
stmt.setInt(2,22);
// 将结果集对象保存到了ResultSet对象中来。这个对象也是一个资源类型
rs = stmt.executeQuery();
那么通过这种预处理对象，再想要来进行SQL注入是不可能的了。即使是伪造也伪造不出来对应的值。
那么改变一下对应的SQL来看一下：
String sql = "SELECT * FROM student where age = ? and name = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1,22);
stmt.setString(2,"lig or 1=1");
但是这里对于数据库的执行来说，没有执行成功。
没有获取得到对应的值。所以无法伪造成功。
4、JDBC事务
类似MySQL的事务处理，我们之前可以通过MySQL的命令行来演示对应的事务控制；同样的，java代码也给我们提供了对应的API来进行操作事务。
所谓的事务：即要么一次性做完，要么什么都不做。要保证着的是事务操作的完整性。
msyql命令行操作方式：
start transaction;
commit;
// rollback;
在java中也提供了对事务的操作。默认是自动提交
每执行一次写操作，自动提交事务
自动提交开启方法：conn.setAutoCommit(true)
自动事务是JDBC的默认行为，此模式无法保证数据一致性
所以通常我们都会将这里的自动提交设置为false
conn.setAutoCommit(true)
将自动提交设置为fasle之后，在进行操作的时候需要手动提交事务或者是回滚事务。
conn.commit();
// conn.rollback();
通过案例来进行演示操作：
public class InsertExample {
// 直接去lib/META-INF/services下面去拷贝即可
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// 数据库的用户名与密码
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// 三大资源类型！注意请勿重复创建即可。后续会专门使用
Connection conn = null;
// 获取得到预处理对象
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println("实例化Statement对象，即执行SQL语句的对象...");
String sql = "insert into student (name,age,birthday) values (?,?,?)";
stmt = conn.prepareStatement(sql);
Student student = new Student();
student.setAge(26);
student.setBirthday(new Date(System.currentTimeMillis()));
student.setName("limj");
stmt.setString(1,student.getName());
stmt.setInt(2,student.getAge());
stmt.setDate(3,student.getBirthday());
conn.setAutoCommit(false);
int i = stmt.executeUpdate();
System.out.println("向数据库中插入了"+i+"条记录");
conn.commit();
System.out.println("执行数据库操作成功");
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
try {
// 数据进行回滚
conn.rollback();
System.out.println("执行数据库操作失败...... 回滚事务");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
看一下对应的结果
查看一下数据库中的记录，可以看到增加了一条数据。
将事务设置成手动关闭之后，如果java代码执行成功，那么会自动提交；如果代码执行失败，那么需要将其回滚代码。
还有一点需要关注的是：
事务到底是什么时候开始的？在MySQL高版本中使用的是Innodb执行引擎，在这个执行引擎下，事务的开启时机是在第一次执行SQL的开始的。这个具体可以参考我的博客中的springboot事务的。
那么这里再手动的制造一下问题所在：
public class InsertExample {
// 直接去lib/META-INF/services下面去拷贝即可
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// 数据库的用户名与密码
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// 三大资源类型！注意请勿重复创建即可。后续会专门使用
Connection conn = null;
// 获取得到预处理对象
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println("实例化Statement对象，即执行SQL语句的对象...");
String sql = "insert into student (name,age,birthday) values (?,?,?)";
stmt = conn.prepareStatement(sql);
Student student = new Student();
student.setAge(26);
student.setBirthday(new Date(System.currentTimeMillis()));
student.setName("exception");
stmt.setString(1, student.getName());
stmt.setInt(2, student.getAge());
stmt.setDate(3, student.getBirthday());
conn.setAutoCommit(false);
int kk = 1/0;
int i = stmt.executeUpdate();
System.out.println("向数据库中插入了" + i + "条记录");
conn.commit();
System.out.println("执行数据库操作成功");
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
try {
// 数据进行回滚
conn.rollback();
System.out.println("执行数据库操作失败...... 回滚事务");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
在事务开启之后还未执行插入数据库操作前，添加一行代码，手动制造异常：
int kk = 1/0;
查看控制台异常，然后去数据库中查看，发现没有对应的添加记录。
事务操作是通过日志来进行体现的。下次专门开一个专题来讲解一下。
5、批处理操作
数据量特别大的时候，比如收增删改的时候，我们要是一个一个的对其进行操作的话，显得效率十分低下，那么JDBC原生API也给我们来提供了对应的操作方式。
下面通过案例来进行演示：分别通过不实用批处理和使用批处理的方式插入一万条数据的执行时长。
5.1、不使用批处理
public class NoUserPInsertExample {
// 直接去lib/META-INF/services下面去拷贝即可
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
// 数据库的用户名与密码
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// 三大资源类型！注意请勿重复创建即可。后续会专门使用
Connection conn = null;
// 获取得到预处理对象
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println("实例化Statement对象，即执行SQL语句的对象...");
String sql = "insert into student (name,age,birthday) values (?,?,?)";
stmt = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 3000; i++) {
stmt.setString(1, "lig");
stmt.setInt(2, 22);
stmt.setDate(3, new Date(System.currentTimeMillis()));
stmt.executeUpdate();
}
long timeDifference = System.currentTimeMillis() - start;
System.out.println("执行时长差为：" + timeDifference); // 29737 ms
System.out.println("执行数据库操作成功");
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
try {
// 数据进行回滚
conn.rollback();
System.out.println("执行数据库操作失败...... 回滚事务");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
不使用批处理，那么类似下面这种SQL语句一条一条的进行插入：
insert into student (name,age,birthday) values (?,?,?)
那么插入3000条数据就是3000条语句。这种效率很慢
5.2、使用批处理
mysql现在对批量操作进行了关闭，如果想要使用批处理，那么需要在url后面添加一下
&rewriteBatchedStatements=true
执行起来的速度贼快。不加的话，也能执行，但并不是真正的批处理，很慢
public class UserPInsertExample {
// 直接去lib/META-INF/services下面去拷贝即可
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&rewriteBatchedStatements=true";
// 数据库的用户名与密码
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
// 三大资源类型！注意请勿重复创建即可。后续会专门使用
Connection conn = null;
// 获取得到预处理对象
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 执行查询
System.out.println("实例化Statement对象，即执行SQL语句的对象...");
String sql = "insert into student (name,age,birthday) values (?,?,?)";
stmt = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 30000; i++) {
stmt.setString(1, "lig");
stmt.setInt(2, 22);
stmt.setDate(3, new Date(System.currentTimeMillis()));
stmt.addBatch(); // 将参数设置到values()当中来
}
stmt.executeBatch();
long timeDifference = System.currentTimeMillis()-start;
System.out.println("执行时长差为："+timeDifference); // 9165 ms
System.out.println("执行数据库操作成功");
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
try {
// 数据进行回滚
conn.rollback();
System.out.println("执行数据库操作失败...... 回滚事务");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
使用了批处理，就相当于使用了:
insert into student (name,age,birthday) values (?,?,?),(?,?,?),(?,?,?)
类似上面这种。
5、数据源
对于数据库操作来说，java去连接数据库这是属于进程之间的连接，需要消耗一定的资源。当然自己测试的时候体现不出来这一点，但是实际生产中，如果连接数达到一定的峰值了，不断的创建新的连接和关闭数据库连接都是需要消耗计算机的系统资源的。
我们更多的是希望当前的机器为当前的服务来给提供资源。所以需要对数据库连接进行反复利用，也就是一种池化思想。那么我们也可以自己来手写一个连接池，但是感觉没有那个必要这么来操作，不妨来使用一下阿里巴巴的数据库连接池。
这里放一个链接：https://blog.csdn.net/yunnysunny/article/details/8657095
需要的自己来配置即可
从理论中来，到实践中去，最终回归理论
