1. 什么是 JDBC(Java Database Connectivity)?
Java数据库连接,用于执行SQL语句的javaAPI,可方便实现多种数据库的统一操作
2. JDBC 操作数据库的基本步骤
- 加载数据库驱动
- 获取数据库连接
- 创建执行 SQL 语句的对象
- 执行 SQL 语句得到结果
- 关闭数据库连接
//导入 mysql-connector-java-xxx.jar 驱动包
//1.使用反射加载驱动,DriverManager 会自动注册驱动并返回给调用的程序
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取数据库连接,所需配置信息写入 .properties 文件中
Connection conn = DriverManager.getConnection(url, user, password);
//3.创建 PreparedStatement 对象
/*PreparedStatement 对象可以预编译SQL语句,使用占位符,防止SQL注入*/
String sql = "select name,password from table where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
//填充占位符,第一个参数表示第几个占位符,第二个参数表示值是多少
ps.setObject(1, args[0]);
//4.执行SQL语句
/* 只有当不确定sql语句是什么功能时候才应该使用execute()方法
增、删、改,应该用 executeUpdate() 方法
查,应该用 executeQuery() 方法 */
ResultSet rs = ps.executeQuery();
//5.关闭连接,用 try-catch 捕获异常
rs.close();
ps.close();
conn.close();
3. 批处理
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
String sql = "insert into table(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
/* PreparedStatement批处理
* 优点:
* SQL语句预编译了
* 对于同一种类型的SQL语句,不用编写很多条
* 缺点:
* 不能发送不同类型的SQL语句 */
for(int i = 1; i <= 20005; i++){
ps.setObject(1, "name_" + i);
//添加到批处理
ps.addBatch();
if(i % 500 == 0){
//执行批处理
ps.executeBatch();
ps.clearBatch();
}
}
//剩余5个不满足条件了,但已经添加到批处理中,在for循环外单独再执行批处理
ps.executeBatch();
ps.clearBatch();
ps.close();
conn.close();
4. 事务
事务的 ACID 属性:
- 原子性(Atomicity):要么全部执行,要不全部不执行
- 一致性(Consistency):执行结果必须使数据库从一个状态转变到另一个状态
- 隔离性(Isolation):不同的事务并发操纵相同的数据时, 各事务互不干扰
- 持久性(Durability):一个事务成功执行,数据库数据对应的改变是永久性的
数据库 4 种隔离级别:
- READ_UNCOMMITTED:什么都不解决
- READ_COMMITTED:解决脏读
- REPEATABLE_READ(MySQL默认):解决脏读、不可重复读
- SERIALIZABLE:解决脏读、不可重复读、幻读
脏读:一个事务读取到另外一个事务修改后未提交的数据
不可重复读:一个事务多次读取同一数据却返回不同的结果(原数据被另一个事务update了)
幻读:一个事务多次相同的sql查询返回不同的记录(原表被另一个事务insert了新的行)
try{
//关闭Mysql的事务自动提交,开始一个事务
connection.setAutoCommit(false);
//A账户减去500块
String sql = "UPDATE a SET money=money-500 ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
//设置一个错误,使在转账过程中出现问题
int a = 3 / 0;
//B账户得到500块
String sql2 = "UPDATE b SET money=money+500";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//如果程序能执行到这里,没有抛出异常,我们就手动提交事务
connection.commit();
//恢复事务的自动提交
connection.setAutoCommit(true);
} catch (SQLException e) {
try {
//如果出现了异常,就会进到这里来,我们就把事务回滚
connection.rollback();
//恢复事务的自动提交
connection.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
5. 元数据
元数据,本质是定义数据的数据,可以描述数据库、表、列的定义信息
- DataBaseMetaData:数据库元数据
- ParameterMetaData:参数元数据
- ResultSetMetaData:结果集元数据
//省略至获取到ResultSet结果
//获取结果集
ResultSetMetaData rsmd = rs.getMetaData();
//获取列数
int columnCount = rsmd.getColumnCount();
//打印列名
for(int i = 0, i <= columnCount, i++){
String columnLable = rsmd.getColumnLable(i + 1);
System.out.println(columnLable);
}
6. 简化 JDBC
增、删、改
//编写通用的 增删改 操作
public void update(String sql,Object ... args){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCutils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
ps.setObject(i + 1, args[i]);
}
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCutils.closeResource(conn, ps);
}
}
查
public <T> T query(Connection conn, Class<T> clazz, String sql, Object ... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
//获取连接
conn = JDBCutils.getConnection();
//预编译sql语句
ps = conn.prepareStatement(sql);
//填充占位符
for(int i = 0; i < args.length; i++){
ps.setObject(i + 1, args[i]);
}
//执行查询
rs = ps.executeQuery();
//获取结果集
ResultSetMetaData rsmd = rs.getMetaData();
//获取列数
int columnCount = rsmd.getColumnCount();
if(rs.next()){
//使用泛型创建不同类型的实例对象
T t = clazz.newInstance();
for(int i = 0; i < columnCount; i++){
//获取列值
Object value = rs.getObject(i + 1);
//获取列名
String columnLabel = rsmd.getColumnLabel(i + 1);
//反射动态修改对象中属性值
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t, value);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCutils.closeResource(null, ps, rs);
}
return null;
}
7. 数据库连接池
- DBCP:tomcat 自带,有BUG,淘汰
- C3P0:速度慢,稳定性好,开源
- Druid:最常用,阿里提供
使用 druid 连接池创建数据库连接
public void getConnection() throws Exception{
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
DataSource dataSource = DruidDataSourceFactory.createDataSource(pros);
Connection conn = dataSource.getConnection();
}
8. DbUtils 框架(commons-dbutils-xxx.jar)
dbutils 是对 JDBC 简单的封装,极大简化 jdbc 编码的工作量
QueryRunner类
该类简化了SQL查询,配合ResultSetHandler使用,可以完成大部分的数据库操作,重载了许多的查询,更新,批处理方法。大大减少了代码量
ResultSetHandler接口
该接口规范了对ResultSet的操作,要对结果集进行什么操作,传入ResultSetHandler接口的实现类即可。
使用 DbUtils 的 CRUD 操作
/*
* 使用DbUtils框架对数据库的CRUD
* 批处理
*
* */
public class Test {
@org.junit.Test
public void add() throws SQLException {
//创建出QueryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "INSERT INTO student (id,name) VALUES(?,?)";
//我们发现query()方法有的需要传入Connection对象,有的不需要传入
//区别:你传入Connection对象是需要你来销毁该Connection,你不传入,由程序帮你把Connection放回到连接池中
queryRunner.update(sql, new Object[]{"100", "zhongfucheng"});
}
@org.junit.Test
public void query()throws SQLException {
//创建出QueryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT * FROM student";
List list = (List) queryRunner.query(sql, new BeanListHandler(Student.class));
System.out.println(list.size());
}
@org.junit.Test
public void delete() throws SQLException {
//创建出QueryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "DELETE FROM student WHERE id='100'";
queryRunner.update(sql);
}
@org.junit.Test
public void update() throws SQLException {
//创建出QueryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "UPDATE student SET name=? WHERE id=?";
queryRunner.update(sql, new Object[]{"zhongfuchengaaa", 1});
}
@org.junit.Test
public void batch() throws SQLException {
//创建出QueryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "INSERT INTO student (name,id) VALUES(?,?)";
Object[][] objects = new Object[10][];
for (int i = 0; i < 10; i++) {
objects[i] = new Object[]{"aaa", i + 300};
}
queryRunner.batch(sql, objects);
}
}
9. 使用 JDBC 实现分页
Mysql 分页语法
SELECT *
FROM table
LIMIT 5,5; --排除前5条,从第6条开始,读取5条记录
//每页显示3条数据
int lineSize = 3;
//总记录数
int totalRecord = getTotalRecord();
//假设用户指定的是第2页
int currentPage = 2;
//一共有多少页
int pageCount = getPageCount(totalRecord, lineSize);
//使用什么数据库进行分页,记得要在JdbcUtils中改配置
List<Person> list = getPageData2(currentPage, lineSize);
for (Person person : list) {
System.out.println(person);
}
//使用JDBC连接Mysql数据库实现分页
public static List<Person> getPageData(int currentPage, int lineSize) throws SQLException {
//从哪个位置开始取数据
int start = (currentPage - 1) * lineSize;
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT name,address FROM person LIMIT ?,?";
List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{start, lineSize});
return persons;
}
//使用JDBC连接Oracle数据库实现分页
public static List<Person> getPageData2(int currentPage, int lineSize) throws SQLException {
//从哪个位置开始取数据
int start = (currentPage - 1) * lineSize;
//读取前N条数据
int end = currentPage * lineSize;
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT " +
" name, " +
" address " +
"FROM ( " +
" SELECT " +
" name, " +
" address , " +
" ROWNUM rn " +
" FROM person " +
" WHERE ROWNUM <= ? " +
")temp WHERE temp.rn>?";
List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{end, start});
return persons;
}
public static int getPageCount(int totalRecord, int lineSize) {
//简单算法
//return (totalRecord - 1) / lineSize + 1;
//此算法比较好理解,把数据代代进去就知道了。
return totalRecord % lineSize == 0 ? (totalRecord / lineSize) : (totalRecord / lineSize) + 1;
}
public static int getTotalRecord() throws SQLException {
//使用DbUtils框架查询数据库表中有多少条数据
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT COUNT(*) FROM person";
Object o = queryRunner.query(sql, new ScalarHandler());
String ss = o.toString();
int s = Integer.parseInt(ss);
return s;
}