概述
是java语言操作数据库的api(应用程序编程接口)
JDBC包下载:JDBC
列举java.sql.*下的接口
Connection
:连接,代表java和数据之间的通道,桥梁;Statement
:语句,可以用来执行sql语句;ResultSet
:结果集,代表的是查询的结果;
类
DriverManager:
工具类,获取连接对象;SQLException
:异常对象,是Exception的子类型,属于检查异常;
操作顺序
- 加载驱动(Driver)jdbc的驱动就是一个连接工厂,生产的产品就是连接对象
com.mysql.jdbc.Driver
是Driver的mysql实现类。
把这个类加载并且初始化
Class.forName(“驱动类名”);
jdbc3.0以上的版本都可以省略加载驱动这一步;
2.获取连接对象
DriverManager.getConnection(url,username,password);
//内部调用了Driver对象获取数据库连接;
url的格式:jdbc:mysql://ip地址:端口号/数据库名?参数
例如:
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","westos");
- 创建语句
Statement stmt=conn.createStatement();
- 执行sql
int rows=stmt.executeUpdate();//用来执行sql,返回值代表的是操作(insert、update等)影响的行数
ResultSet rs=stmt.executeQuery();//用来执行select 语句,返回结果集
rs.next(); //取得下一条记录,返回的是boolean值
- 释放资源
先打开的资源后关闭
rs.close();//关闭结果集
stmt.close();//关闭语句
conn.close();//关闭连接
实例:
package demo01;
import java.sql.*;
public class JdbcStudent {
static final String URL="jdbc:mysql://localhost:3306/test";
static final String NAME="root";
static final String PASSWORD="westos";
public static void main(String[] args) {
try(Connection conn=DriverManager.getConnection(URL,NAME,PASSWORD);
)
{
int i2 = insertData(conn, "王祖贤", "1964-8-9", "女");
System.out.println(i2);
} catch (SQLException e) {
e.printStackTrace();
}
}
private static int insertData(Connection conn,String sname,String birthday,String sex) throws SQLException {
try (
Statement stmt = conn.createStatement();
){
String sqlI="insert into student(sname,birthday,sex) values ('"+sname+"','"+birthday+"','"+sex+"')";
return stmt.executeUpdate(sqlI);
}
}
}
PreparedStatement(预编译的语句对象)
减少sql语句拼接复杂度
- 需要预先提供sql语句
PreparedStatement psmt=conn.prepareStatement(String sql);
- 可以在sql中用?占位某个值
insert into student(sname,birthday,sex) values(?,?,?);
- 给?赋值,使用PreparedStatement中一系列以set开头的方法
setString(?的位置,值)
setInt(?的位置,值)
setDate(?的位置,值)
例如:
psmt.setString(1,"李四");
psmt.setString(2,"1998-10-20");
- 执行sql
psmt.executeUpdate();
注意
?只能占位值,而不能占位列名。
例子:
private static int insertPre(Connection conn,String sname,String birthday,String sex) throws SQLException {
try (
PreparedStatement prep = conn.prepareStatement("insert into student(sname,birthday,sex) values(?,?,?)");
){
//给占位?赋值
prep.setString(1,sname);
prep.setString(2,birthday);
prep.setString(3,sex);
//执行
return prep.executeUpdate();
}
}
SQL攻击
public Boolean sqlAtt(String username,String password) {
try (Connection conn = this.getConnection();) {
Statement stmt = conn.createStatement();
String sql = "select * from user where username='" + username + "' and password='" + password + "'";
System.out.println(sql);
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
return true;
} else {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
运行结果:
可以看到虽然没有输入正确的密码,但是因为有or '1=1'
,使得password判断的时候整个为true,所以查询出了信息。
System.out.println(js.sqlAtt("李青","789' or '1=1"));
//select * from user where username='李青' and password='789' or '1=1'
//true
所以应尽量避免使用字符串拼接的方法来编写sql语句;
防范方法:
- 对参数内容做检查,内部不能有sql关键字例如:or
- preparedStatement ,用?占位避免了拼接字符串
获取自增列的主键值
获取刚刚插入的行的主键值
//查询自增列的主键值
public void test(){
try (Connection conn = this.getConnection();){
String sql="insert into student(sid,sname,birthday,sex) values(null,?,?,?)";
//Statement.RETURN_GENERATED_KEYS 表示一个选项,表示要返回自增主键值
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1,"aaa");
ps.setString(2,"1990-9-06");
ps.setString(3,"男");
int i = ps.executeUpdate();
System.out.println("影响行数:"+i);
ResultSet generatedKeys = ps.getGeneratedKeys();
generatedKeys.next();
System.out.println("刚刚新增的主键值:"+generatedKeys.getInt(1));
}catch (SQLException e){
e.printStackTrace();
}
}
封装
将jdbc对数据库的操作封装成一个类,可实现使用通用方法对数据库增删改查。
事务控制
begin; //开始事务
...
commit; //提交事务
rollback; //回滚事务
jdbc默认是让每条sql的执行作为一个独立的事务,每条语句执行前后会默认加上begin和commit。
public class ShiWu {
public static void main(String[] args) {
try(Connection conn = JdbcuUtils.getConnection();){
String sql="delete from student where sid=?";
PreparedStatement psmt = conn.prepareStatement(sql);
psmt.setObject(1,1001);
//begin
psmt.executeUpdate();
//commit 默认一条语句为一个独立的事务
int a=1/0; //制造异常
psmt.setObject(1,1002);
psmt.executeUpdate();
}catch (SQLException e){
e.printStackTrace();
}
}
}
运行多条语句,如果出现异常则前面语句已经执行,异常后的语句没有执行。这样可能造成数据安全问题,所以要让多条语句包含在一个事务内,如果发生异常则rollback回滚。
解决方法
让事务变成手动提交:
Connection.setAutoCommit(false);
接下来事务在执行第一条insert、update、delete时不会自动加上commit,而是由程序员控制在哪条语句之后执行commit。
public static void main(String[] args) {
try(Connection conn = JdbcuUtils.getConnection();){
conn.setAutoCommit(false);
try {
String sql="delete from student where sid=?";
PreparedStatement psmt = conn.prepareStatement(sql);
psmt.setObject(1,1002);
psmt.executeUpdate();
int a=1/0; //制造异常
psmt.setObject(1,1003);
psmt.executeUpdate();
conn.commit();//手动提交事务
}catch (Exception e){
conn.rollback();//如果执行事务是发生异常则回滚
e.printStackTrace();
}
}catch (SQLException e){
e.printStackTrace();
}
}
从运行结果可以看到发生了异常,但是第一条执行成功的语句也回滚(恢复)了。
性能提升
- 批量增删改
向数据库中插入10万条数据
create table big(
id int primary key auto_increment,
name varchar(100)
);
批处理
必须开启mysql的批处理功能:在连接数据库时加上批处理参数
jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
PreparedStatement.addBatch();//加入批处理包
PrepareStatement.executeBatch(); //执行批处理包,发送数据,发完一次包就清空
public class MultiHandle {
public static void main(String[] args) {
try(Connection conn = JdbcuUtils.getConnection();){
String sql="insert into big(id,name) values(null,?)";
PreparedStatement pstm = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 1; i <= 100000; i++) {
pstm.setObject(1,"张"+i);
pstm.addBatch();
//添加一万条提交一次
if(i%10000==0){
pstm.executeBatch();
}
}
System.out.println("运行时间:"+(System.currentTimeMillis()-start));
}catch (SQLException e){
e.printStackTrace();
}
}
}
- 查询性能提升
为了防止数据过多造成java堆内存不够,一次查询指定数量的数据,用完之后,再即从数据库里拿。
mysql需要设置抓取大小限制功能,在连接参数中加入:
jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useCursorFetch=true
还可以给fetchSize一个默认值:在连接参数中加入defaultFetchSize=50
PreparedStatement.setFetchSize(50);
提升查新性能最有效的方法,建立数据库索引。索引是对现有的数据进行排序,在排序的结果中搜索,效率会很高。
索引的数据结构:B+树
//create index 索引名 on 表(列);
//向big表的name列建立一个索引
create index idx_name on big(name);
//查看索引
show create table big\G;
//删除索引
alter table big drop key idx_name;
mysql的主键会自动创建索引,用主键作为查询条件,搜索速度最快。
索引使用的注意事项:
- 使用了空间换了时间,会占用额外存储。
- 索引提升查询性能的同时,影响数据的增删改(在查询频率远高于增删改时使用索引)。
- null值不适合索引,要加索引的列上一般添加not null约束,并给一个默认值。
- 区分度很低的列不适合建索引。列的取值越唯一,区分度越高。
- mysql主键索引(聚簇索引,把这一行的值都存储到叶子节点上)和普通索引(只存储了主键的值),查询普通索引时,如果select的列值不是建立了索引的列,则会搜索两遍,先走普通索引再走主键索引(主键索引叶子节点上包含全部信息,普通索引叶子节点上只有主键和建立了索引的列)这种成为回表。
- 避免回表的方法:不随便用select * ;建立复合索引
create index idx_name on big(name,sex);
(先按name排序,name相同再按sex排序)
- sql语句的执行流程
-
sql发送给数据库服务器;
连接层 -
管理连接,验证连接是否有效(连接池);
服务层 -
查询缓存处理,缓存有已有的数据直接返回;
mysql8.0之后取消了这个功能。 -
sql需要词法分析、语法分析、生成语法解析树–由分析器完成;
mysql中默认没有打开这个功能,需要打开开关,加入连接参数:
useServerPrepStmts=true
cachePrepStmts=true
prepStmtCacheSize=25
-
进行sql的优化处理,选择索引–由优化器完成;
-
调用存储引擎 --执行器
存储引擎层 -
去索引搜索,读取磁盘数据
mysql默认的存储引擎是InnoDB(支持事务、支持外键、聚簇索引(主键顺序排列,在叶子节点存储所有记录))
- 连接池(connection pool)
连接池中可以设置多个连接对象,当用户接连数据库时,使用已经创建的连接对象,用完之后归还给连接池,并且使用完之后连接对象并不会关闭。由于创建连接需要耗费大量资源,这样可以节省资源。
使用Druid连接池接口
javax.sql.DataSource //连接池接口
Connection conn=datasource.getConnection(); //
conn.close(); //并不是真的关闭连接,只是将连接归还给连接池
Druid包下载:Druid
装饰模式 decorate pattern:在原有对象的基础上,对方法进行功能上的增强,或者功能上的改变。原有对象和装饰对象它们实现了共同的接口,或者是继承了共同的父类。可以把两个对象中相当于独立的功能进行组合,比继承的方式更为灵活。
public class JdbcuUtils {
static final String URL = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
static final String NAME = "root";
static final String PASSWORD = "westos";
static final DruidDataSource dataSource=new DruidDataSource();
static {
dataSource.setUrl(URL);
dataSource.setUsername(NAME);
dataSource.setPassword(PASSWORD);
dataSource.setDriverClassName("com.mysql.jdbc.Driver");//可选
dataSource.setInitialSize(5); //初始连接数
dataSource.setMaxActive(10); //最大连接数
dataSource.setMinIdle(5); //最小连接数
//连接空闲的时候,为了不使连接断开,每隔一段时间使连接保活
dataSource.setValidationQuery("select 1"); //一条简单的sql,用来保证连接存活
dataSource.setTestWhileIdle(true);//当空闲的时候检查连接的有效性
dataSource.setTimeBetweenConnectErrorMillis(60*1000); //检查间隔 一分钟
}
//获取数据库连接,池连
public static Connection getConnection2() throws SQLException {
return dataSource.getConnection();
}
//获取数据库连接,直连
public static Connection getConnection() throws SQLException {
System.out.println("数据库已连接");
return DriverManager.getConnection(URL, NAME, PASSWORD);
}
}
单元测试
junit(java单元测试)
格式:
- 必须是public
- 没有返回值
- 没有参数
- 必须在方法上加上@Test注解
jar包下载:有关jar包
每个测试方法都可以作为一个独立的入口,可以运行类上测试来执行类中所有测试方法。
Assert.assertEquals(期望值,实际值);
如果测试通过会显示绿色对勾,否则显示红色叉号。
junit内部使用反射
package demo03;
import org.junit.Assert;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestUtil {
@Test
public void test1(){
System.out.println("test1");
}
@Test
public void test2() throws SQLException {
Connection conn = JdbcuUtils.getConnection();
PreparedStatement psmt = conn.prepareStatement("select * from student where name=?");
psmt.setString(1, "张三");
ResultSet rs = psmt.executeQuery();
rs.next();
int id = rs.getInt("id");
Assert.assertEquals(434, id);
}
}