JDBC
Java database connectivity
是java语言操作数据库的 api (应用程序编程接口)
存放位置:java.sql.*
接口
Connection 连接 代表了java和数据之间的通道,桥梁
Statement 语句 可以用来执行 insert, update, delete , select …
ResultSet 结果集 代表的是查询的结果
类
DriverManager 工具类,获取连接对象
SQLException 异常对象 是 Exception的子类型,属于检查异常
操作步骤顺序
- 加载驱动 (Driver) jdbc的驱动就是一个连接工厂,生成连接对象
Class.forName("驱动类名");
例如:
Class.forName("com.mysql.jdbc.Driver");
jdbc 3.0 以上版本都可以省略加载驱动这一步
- 获取连接对象
DriverManager.getConnection(url, 用户名, 密码); // 返回一个Connection接口的实例
url 的格式 :
jdbc:mysql://ip地址:端口号/数据库名?参数
例如:mysql数据库
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
- 创建语句
Statement stmt = conn.createStatement();
- 执行sql
int rows = stmt.executeUpdate(); // 用来执行 insert , update, delete , DDL , 返回值代表影响行数
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
}
// 迭代器模式
- 释放资源 ( 先打开的资源后关闭 ),可以使用try(资源对象){}来自动关闭资源以及捕获异常
try(Connection conn=DriverManger.getConnection("jdbc:mysql://localhost:3306/test", "root", "root")){
}
catch(SQLException e){
}
PreparedStatement 预编译语句对象
// 1. 需要预先提供sql语句
PreparedStatement psmt = conn.prepareStatement(String sql);
// 2. 可以在sql中用?占位某个值
insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)
// 3. 给?赋值
使用PreparedStatement中一系列以 set开头的方法
setString(?的位置, 值)
setInt(?的位置, 值)
setDate(?的位置, 值)
psmt.setString(1, "李四");
psmt.setString(2, "1999-9-7");
// 4. 执行sql
psmt.executeUpdate();
注意: ?能够占位的只有值, 不能是表名、列名、关键字
SQL注入攻击
防范方法:
- 对参数内存做检查,内部不能有sql关键字例如:or
- 使用PreparedStatement
事务控制
jdbc 默认是让每条sql的执行作为一个独立的事务
让事务变为手动提交:
Connection.setAutoCommit(false);
begin; // 开始事务 start transaction;
Connection.begin();
多条sql语句
commit; // 提交事务, 最终确认,让所有修改生效
Connection.commit();
rollback; // 回滚事务,撤销事务内所有的修改
Connection.rollback();
性能提升
1. 批量增删改
批处理
PreparedStatement.addBatch() 加入批处理包
PreparedStatement.executeBatch() 把批处理包中所有sql,一次性发送给数据库服务器
对于mysql需要通过连接参数,开启批处理功能:
在连接字符串之后添加:
jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useCursorFetch
示例代码:
try(Connection conn = JdbcUtils.getConnection()) {
PreparedStatement psmt = conn.prepareStatement("insert into Students(name) values(?)");
for (int i = 1; i <= 100001; i++) {
psmt.setString(1, "王" +i);
psmt.addBatch();
if(i % 10000 == 0) {
psmt.executeBatch(); // 发送给服务器并清空批处理包
}
}
psmt.executeBatch(); // 收尾的
} catch (SQLException e) {
e.printStackTrace();
}
2. 查询性能提升
如果,返回记录行数:10000可能会超出虚拟机规定的内存,可以设置抓取大小的限制:
PreparedStatement.setFetchSize(50);
mysql 中需要在连接参数中加入: useCursorFetch=true,来开启抓取大小的限制功能
还可以给fetchSize一个默认值: 连接参数中加入: defaultFetchSize=50
提升查询性能最有效的方法,建立数据库索引
索引是对现有的数据进行排序,在排序的结果中搜索,效率会很高
索引的数据结构是:B+树
create index 索引名 on 表(列);
// 向 big 的name 建立一个索引
create index idx_name on big(name);
mysql的主键会自动创建索引,用主键作为查询条件,搜索速度最快
索引使用的注意事项:
- 使用了空间换了时间,会占用额外存储
- 索引提升查询性能的同时,影响数据的增删改( 在这张表的查询远高于增删改时,建立索引有意义 )
- null 值不适合索引,要加索引的列上一般添加一个not null约束, 并给一个默认值
- 区分度低的列不适合建索引
- mysql 主键索引(聚簇索引, 把这一行的值都存储到叶子节点上) 和 普通索引(叶子节点只存储了主键的值和索引列的值)
所以查询普通索引时,会搜索两遍,首先走普通索引,再走主键索引,(称为回表)
避免回表的办法,1. 不用滥用select * , 2. 建立复合(组合)索引
create index idx_name on big(name, sex, age);
先按name排序,name相同的再按sex排序
- 从大量的数据中找到少量数据时,才能充分利用索引
删除索引
语法:
alter table 表名 drop key 索引名
alter table big drop key idx_name;
3. sql语句的执行流程
-
sql 发送给数据库服务器
连接层 -
管理连接,验证连接是否有效(连接池)
服务层 -
查询缓存处理,缓存中已有的数据直接返回, 从mysql 8.0开始,这个功能没有了
select * from student 10 条学生记录放入了查询缓存insert ... update delete
-
sql 需要词法分析、语法分析,生成语法解析树 – 由分析器完成
select * from student where id = 1;
select * from student where id = 2;
select * from student where id = 3;
可以使用下边的语句来减少分析时间
select * from student where id = ?;但是mysql 中默认没有打开这个功能,需要打开开关:
useServerPrepStmts=true
cachePrepStmts=true
prepStmtCacheSize=25 -
进行sql的优化处理,选择索引 – 由优化器
-
调用存储引擎 – 执行器
存储引擎层 -
去索引搜索,读取磁盘数据
mysql 默认的存储引擎是InnoDB( 支持事务,支持外键,聚簇索引按(主键顺序排列,在叶子节点存储所有记录) )
MyISAM( 不支持事务,不支持外键, 不支持灾难恢复 )
Memory( 内存 , hash索引, 只能支持等值比较)
4. 连接池 (connection pool)
apache dbcp 最老牌
c3p0 连接池
alibaba druid (德鲁伊) sql监控
javax.sql.DataSource 连接池接口
Conneciton conn = datasource.getConnection(); 从池中返回连接对象
conn.close(); // 一般都覆盖了关闭方法,将连接归还给连接池,而不是真正关闭
文档地址:
https://github.com/alibaba/druid/wiki/DruidDataSource配置属性列表
- 用DriverManager 获取连接,称为直连
- 用DataSource 获取连接,称为池连