连接数据库
使用原始方式:
public void test1() throws SQLException {
String url = "jdbc:mysql://localhost:3306/ssh_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
String username = "root";
String password = "root";
Driver driver = new com.mysql.cj.jdbc.Driver();
Properties info = new Properties();
// info.setProperty("username",username); 远古连接方式用户名的key是user,而不是username
info.setProperty("user", username);
info.setProperty("password", password);
Connection connect = driver.connect(url, info);
System.out.println(connect);
}
读取配置类的方式:
public void test4() throws IOException, ClassNotFoundException, SQLException {
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");//默认类路径下
Properties info = new Properties();
info.load(is);
String url = info.getProperty("url");
String user= info.getProperty("user");
String password = info.getProperty("password");
String driverClass = info.getProperty("driverClass");
Class.forName(driverClass);//实例化driver,注册驱动
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
不需要显示的注册驱动,因为在Driver类的静态代码块中已经帮我们注册实现好了。
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver()); //实例化并注册驱动
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
PreparedStatement和Statement的区别
- PreparedStatement是Statement的子接口,它是一条预编译过的SQL语句。
- PreparedStatement对象通过prepareStatement() 方法获取,Statement对象是通过createStatement()方法获取。
- PreparedStatement较Statement效率更高,因为预编译过,可以被重复拿来使用。
- PreparedStatement可以防止SQL注入问题,而Statement不行。
- PreparedStatement通过 ? 占位符进对SQL语句进行占位。而Statement需要进行拼串,可读性和维护性较差。
使用Statement演示SQL注入演示
假设根据字符串s查询部门表的id和名称,此时可以看到SQL为了拼接上变量id和name,需要进行复杂的拼串,如果拼串较多,那么可读性将会极差,维护起来也十分不方便。
并且可以在SQL语句后面拼接上 OR 1 = 1,那么这是一个恒成立的语句,并且#后面的语句会被注释掉,故OR前面的条件不成立,后面的条件成立,就会将所有的信息查出来,就会造成数据库表信息的泄漏。
public void test2() {
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
List<Dept> list = new ArrayList<>();
conn = JdbcUtils.getConnection();
statement = conn.createStatement();
int id = 1;
String name = "产品部门";
String sql = "SELECT id,NAME FROM dept WHERE id = '" + id + "' OR 1 = 1 # AND NAME = '" + name + "'";
rs = statement.executeQuery(sql);
while (rs.next()) {
Dept dept = new Dept();
dept.setId(rs.getInt("id"));
dept.setName(rs.getString("name"));
list.add(dept);
}
System.out.println(list);
} catch (SQLException se) {
se.printStackTrace();
} finally {
try {
JdbcUtils.close(conn, statement, rs);
} catch (SQLException se) {
se.printStackTrace();
}
}
}
使用PreparedStatement可以防止SQL注入
对上面的代码进行改造,使用PreparedStatement来执行,此时SQL语句已经被预编译,无法SQL注入,执行代码将会出错。
public void test3() {
Connection conn = null;
String sql = "SELECT id,NAME FROM dept WHERE id = ? OR 1 = 1 # AND NAME = ?"; // 会对SQL语句进行预编译,执行报错
PreparedStatement ps = null;
ResultSet rs = null;
try {
List<Dept> list = new ArrayList<>();
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
int id = 1;
String name = "产品部门";
ps.setObject(1, id);
ps.setObject(2, name);
rs = ps.executeQuery();
while (rs.next()) {
Dept dept = new Dept();
dept.setId(rs.getInt("id"));
dept.setName(rs.getString("name"));
list.add(dept);
}
System.out.println(list);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
JdbcUtils.close(conn, ps, rs);
} catch (SQLException se) {
se.printStackTrace();
}
}
}
数据库事务
-
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。(增删改操作)
-
事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改,整个事务**回滚(rollback)**到最初状态。
当一个连接对象被创建时,默认情况下是自动提交事务:每次执行一个 SQL 语句时,如果执行成功,就会向数据库自动提交,而不能回滚。
如何使用JDBC进行事务操作呢?
- 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
- 在所有的 SQL 语句都成功执行后,调用 commit(); 方法提交事务
- 在出现异常时,调用 rollback(); 方法回滚事务
事务操作演示:
id为2的用户给id为1的用户转账100
public void test4() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn.setAutoCommit(false); // 取消事务自动提交
conn = JdbcUtils.getConnection();
String sql = "UPDATE balance SET balance = balance + 100 WHERE id = 1 "; // 加100
ps = conn.prepareStatement(sql);
ps.executeUpdate();
System.out.println(1/0); //模拟出现异常
sql = "UPDATE balance SET balance = balance - 100 WHERE id = 2 "; // 减100
ps = conn.prepareStatement(sql);
ps.executeUpdate();
conn.commit(); //没有异常就提交
} catch (SQLException se) {
try {
conn.rollback(); // 事务回滚
} catch (SQLException throwables) {
throwables.printStackTrace();
}
se.printStackTrace();
} finally {
try {
JdbcUtils.close(conn, ps, null);
} catch (SQLException se) {
se.printStackTrace();
}
}
}
上述代码,如果没有统一提交事务,那么当行第一条sql语句能够正常执行,中途出现异常,比如断网断电,导致第二条sql语句没有执行。此时用户1的钱多了,但是用户2的钱却没有减少。
所以取消事务自动提交,没有异常统一提交,出现异常则回滚到最初的状态。
事务的ACID属性
-
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 -
一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 -
隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。 -
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
数据库的并发问题
同时运行的事务,想对数据库表中的同一数据进行操作时,如果没有采取必要的隔离机制,就会导致各种并发问题。
- 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
- 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
- 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。
脏读就是一个事务修改了某个数据,但是还没提交,结果另一个事务跑过来,读到了修改后的数据,然后第一个事务又回滚了,那么这样第二个事务读到的数据就是脏数据,临时切勿无效。
不可重复读,就是一个事务读取某个表中的数据,此时第二个事务跑过来,对该数据进行了更改,如果没有提交,则第一个事务是读取不到的,这就避免了脏读。但是如果第二个事务对数据进行了更改,并且提交了,那么第一个事务再次读取该数据时,就发现与上一次读取不同。不可重复读是指一个事务在自己事务内重复读取数据,发现读取的数据不同。不可重复读针对的是update操作。
幻读,就是一个事务读取了表中的数据,此时第二个事务跑过来,对数据进行了添加操作,此时第一个事务再去查询,发现怎么多了一些数据,仿佛出现幻觉,这就是幻读。
不可重复读和幻读是可以接受的,就比如刷新淘宝库存,发现和上次看的库存不一样,但是很正常,但是脏读一定要解决,因为脏读是读到未提交的数据,随时可能会回滚,是临时的。
四种隔离级别
数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。.
隔离级别 | 描述 |
---|---|
READ UNCOMMITTED(读未提交数据) | 允许事务读取未被其他事务提交的变更,脏读,不可重复读,幻读的问题都会出现 |
READ COMMITED(读已提交数据) | 只允许事务读取被其他事务提交的变更,可以避免脏读,但是不可重复读、幻读仍会出现 |
REPEATABLE READ(可重复读) | 确保事务可以从一个字段读出相同的值,在这个事务期间,禁止其它事务对此字段进行更新操作,可以避免脏读、不可重复读,但是幻读问题仍然存在。 |
SERIALIZABLE(串行化) | 确保事务可以从一个表中读取相同的行,在这个事务持续期间,进制其它事务对该表进行增删改操作,所有问题都可以避免,但是性能极其低下。 |
数据库连接池
传统开发中的问题
普通的JDBC数据库连接使用 DriverManager 来获取,每次向数据库建立连接的时候都要将 Connection 加载到内存中,再验证用户名和密码(得花费0.05s~1s的时间)。需要数据库连接的时候,就向数据库要求一个,执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线,频繁的进行数据库连接操作将占用很多的系统资源,严重的甚至会造成服务器的崩溃。
对于每一次数据库连接,使用完后都得断开。否则,如果程序出现异常而未能关闭,将会导致数据库系统中的内存泄漏,最终将导致重启数据库。如果连接没有正常关闭,就会导致内存泄漏。
数据库连接池基本思想
数据库连接池的基本思想:就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接,当需要建立数据库连接时,只需从“缓冲池”中取出一个,使用完毕之后再放回去。数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。
数据库连接池技术的优点
- 资源重用
- 更快的系统反应速度
- 新的资源分配手段
- 统一的连接管理,避免数据库连接泄漏
Druid(德鲁伊)数据库连接池
Druid是阿里巴巴开源平台上一个数据库连接池实现,它结合了C3P0、DBCP、Proxool等DB池的优点,同时加入了日志监控,可以很好的监控DB池连接和SQL的执行情况,可以说是针对监控而生的DB连接池,可以说是目前最好的连接池之一。
测试演示:
package com.atguigu.druid;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class TestDruid {
public static void main(String[] args) throws Exception {
InputStream in = ConnectionTest.class.getClassLoader().getResourceAsStream("druid.properties");
Properties info = new Properties();
info.load(in);
DataSource dataSource = DruidDataSourceFactory.createDataSource(info);// 通过工厂创建数据库连接池
Connection connection = dataSource.getConnection(); // 获取池内的链接
System.out.println(connection);
}
}
其中,配置文件为:【druid.properties】
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.cj.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall
抽取工具类:
package jdbc.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static DataSource dataSource = null;
static {
try {
Properties info = new Properties();
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
info.load(in);
dataSource = DruidDataSourceFactory.createDataSource(info);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void close(Connection conn, Statement statement, ResultSet rs) throws SQLException {
if (conn != null) {
conn.close();
}
if (statement != null) {
statement.close();
}
if (rs != null) {
rs.close();
}
}
}
详细配置参数:
配置 | 缺省 | 说明 |
---|---|---|
name | 配置这个属性的意义在于,如果存在多个数据源,监控的时候可以通过名字来区分开来。 如果没有配置,将会生成一个名字,格式是:”DataSource-” + System.identityHashCode(this) | |
url | 连接数据库的url,不同数据库不一样。例如:mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto | |
username | 连接数据库的用户名 | |
password | 连接数据库的密码。如果你不希望密码直接写在配置文件中,可以使用ConfigFilter。详细看这里:https://github.com/alibaba/druid/wiki/使用ConfigFilter | |
driverClassName | 根据url自动识别 这一项可配可不配,如果不配置druid会根据url自动识别dbType,然后选择相应的driverClassName(建议配置下) | |
initialSize | 0 | 初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时 |
maxActive | 8 | 最大连接池数量 |
maxIdle | 8 | 已经不再使用,配置了也没效果 |
minIdle | 最小连接池数量 | |
maxWait | 获取连接时最大等待时间,单位毫秒。配置了maxWait之后,缺省启用公平锁,并发效率会有所下降,如果需要可以通过配置useUnfairLock属性为true使用非公平锁。 | |
poolPreparedStatements | false | 是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。 |
maxOpenPreparedStatements | -1 | 要启用PSCache,必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true。在Druid中,不会存在Oracle下PSCache占用内存过多的问题,可以把这个数值配置大一些,比如说100 |
validationQuery | 用来检测连接是否有效的sql,要求是一个查询语句。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会其作用。 | |
testOnBorrow | true | 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 |
testOnReturn | false | 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能 |
testWhileIdle | false | 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 |
timeBetweenEvictionRunsMillis | 有两个含义: 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据,详细看testWhileIdle属性的说明 | |
numTestsPerEvictionRun | 不再使用,一个DruidDataSource只支持一个EvictionRun | |
minEvictableIdleTimeMillis | ||
connectionInitSqls | 物理连接初始化的时候执行的sql | |
exceptionSorter | 根据dbType自动识别 当数据库抛出一些不可恢复的异常时,抛弃连接 | |
filters | 属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有: 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall | |
proxyFilters | 类型是List,如果同时配置了filters和proxyFilters,是组合关系,并非替换关系 |