韩顺平JDBC学习总结
1. 为什么要用JDBC
JDBC是Java Database Connectivity,是一套操作数据库的接口规范
Java不可能具体地去操作每一种数据库,因此引入JDBC,让数据库厂商去实现JDBC接口
Java程序员只需要面向JDBC接口编程即可
2. JDBC连接数据库的几种方式
方式1:
静态加载,依赖第三方Driver
//连接方式1
public static void conn01() throws SQLException {
Driver driver = new Driver();
String url = "jdbc:mysql://localhost:3306/db01";
Properties properties = new Properties();
properties.setProperty("user","wood");
properties.setProperty("password","123");
Connection connect = driver.connect(url,properties);
System.out.println(connect);
//执行sql语句
String sql = "insert into actor values(null,'wang', '女', '1990-11-13','1321330')";
Statement statement = connect.createStatement();
//返回sql语句影响的数据库表的行的数量
int rows = statement.executeUpdate(sql);
System.out.println(rows>0?"成功":"失败");
//关闭连接
statement.close();
connect.close();
}
方法2:
利用反射实行动态加载,更加灵活减少依赖
//方法2:利用反射加载Driver类,动态加载,更加灵活,减少依赖性
public static void conn02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
Class aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/db01";
Properties properties = new Properties();
properties.setProperty("user","wood");
properties.setProperty("password","123");
Connection connect = driver.connect(url,properties);
System.out.println("方式2,反射: "+connect);
String sql = "insert into actor values(null,'gong', '男', '2010-11-13','1351330')";
Statement statement = connect.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows>0?"成功":"失败");
statement.close();
connect.close();
}
方法3:使用DriverManager替换Driver进行统一管理
DriverManager类中存在一静态代码块,自动执行DriverManager.registerDriver(new Driver());
因此无需手动注册
public static void conn03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
//jdk1.5后,Class.forName可以省略,注册会自动完成
Class aClass = Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db01";
String user ="wood";
String password = "123";
Connection connection = DriverManager.getConnection(url,user,password);
System.out.println("方式3,DriverManager: "+connection);
String sql = "insert into actor values(null,'gu', '女', '1995-11-13','1623330')";
Statement statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows>0?"成功":"失败");
statement.close();
connection.close();
}
方法4:
使用配置文件来获取数据库的连接信息
public static void conn04() throws IOException, ClassNotFoundException, SQLException {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
/*mysql.properties文件格式:
*user=wood
*password=123
*url=jdbc:mysql://localhost:3306/db01
*driver=com.mysql.jdbc.Driver
*/
//Class.forName()建议写,但可以省略
Class.forName(driver);
Connection connection = DriverManager.getConnection(url,user,password);
System.out.println("方法4:使用配置文件链接数据库"+connection);
String sql = "insert into actor values(null,'li', '男', '1991-11-13','1226399')";
Statement statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows>0?"成功":"失败");
statement.close();
connection.close();
}
3. 结果集ResultSet
查询数据库,将结果放在一个结果集对象中
ResultSet是一个接口,JDBC42ResultSet才是实现接口的实例对象
其中rowData用于存放查询数据
查询结果的每一行用ArrayList存放在rows中
String sql = "Select * from actor";
ResultSet resultSet =statement.executeQuery(sql);
while(resultSet.next()){
int id = resultSet.getInt("id");
String name =resultSet.getString("name");
String sex = resultSet.getString("sex");
Date date =resultSet.getDate("borndate");
String phone =resultSet.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date+ "\t" + phone);
}
注:查询的列名应当和while循环中的列名一一对应
若返回的查询中有date这一列,但while循环中没有会出现报错
getString()也可以用索引来获得对应的列(index=1为第一列)
当查询结束后,我们需要将resultSet关闭,查询的结果会丢失。
我们后续若想继续使用该结果集就要重新查询,这很不方便。
在后续DAO的介绍中,我们会为每个表创建一个JavaBean来解决复用这个问题。
4. PreparedStatement(预处理)
预处理的优点:
- Statement语句存在SQL注入的风险,PreparedStatement将解决这一问题
- 不再使用+拼接sql语句,减少语法错误
- 大大减少了编译的次数,提高了效率
使用方法:
//admin和pass由Scanner从控制台获取
//"?"为占位符,留给后面PreparedStatement输入
String sql = "select name, password from users where name =? and password =?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,admin);
preparedStatement.setString(2,pass);
//这里无需再传入sql语句,preparedStatement已经在上面和sql绑定了
//若再传入sql,则执行的是最原始的sql语句
ResultSet resultSet =preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
DML操作使用preparedStatement.executeUpdate()。
该方法会返回一个int值,代表该次操作影响的数据库表的行数。
5. JDBC API总结
6. JDBCUtils
JDBCUtils: JDBC Utilities,即JDBC工具类
我们在使用JDBC操控数据库时,经常会进行重复的操作,如连接和关闭。
把这些操作封装到JDBCUtils类中可以使我们的代码更加简洁,使用JDBC也更加方便
封装JDBCUtils:
public class JDBCUtils {
//定义相关的属性(4个)
//因为只需要一份,因此用static修饰
private static String user;
private static String password;
private static String url;
private static String driver;
//在static 代码块获得配置文件信息
static{
Properties properties = new Properties();
try {
//加载之前用过的配置文件
properties.load(new FileInputStream("src\\mysql.properties"));
//读取相关属性
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
driver = properties.getProperty("driver");
} catch (IOException e) {
//在实际开发中,可以这样处理
//1. 将编译异常转成运行异常
//2. 调用这可以选择捕获该异常,也可以选择默认处理(报出编译异常)
throw new RuntimeException(e);
//e.printStackTrace();
}
}
//连接数据库,返回Connection
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
}catch (SQLException e){
throw new RuntimeException(e);
}
}
//关闭相关资源
/*
* 1. ResultSet 结果集
* 2. Statement或PreparedStatement
* 3. Connection
*/
public static void close(ResultSet set, Statement statement, Connection connection){
//判断是否为NULL
try {
if(set !=null){
set.close();
}
if(statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
} catch (SQLException e) {
//转为运行时异常抛出
throw new RuntimeException(e);
}
}
}
使用JDBCUtils
public class JDBCUtilsTest {
public static void main(String[] args) {
//这些变量可能需要通过JDBCUtils来关闭
//因此定义在外部
Connection connection =null;
PreparedStatement preparedStatement =null;
String sql = "select id,name from users";
ResultSet resultSet =null;
try {
connection = JDBCUtils.getConnection();
System.out.println("获得连接:"+connection.getClass());
preparedStatement =connection.prepareStatement(sql);
//这里sql语句已经完整,所以不用填充,直接使用
resultSet = preparedStatement.executeQuery();
System.out.println("id"+ "\t"+ "name");
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
System.out.println(id + "\t" + name);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(resultSet,preparedStatement,connection);
}
/*运行结果
* id name
* 1 wang
* 2 zhang
*/
}
}
如果执行DML操作,不需要使用resultSet,close()函数的resultSet部分传入null即可
7. 事物
在JDBC程序中创建Connection对象时,默认会开启自动提交事务。
即执行一个sql语句,就提交一个,无法回滚。
当我们需要在JDBC中让多个SQL语句作为一个整体进行执行(转账),需要使用setAutoCommit(false)。
该方法会取消自动提交事务这一设定。
在确定所有SQL语句执行成功后,使用commit()手动提交事务。
当SQL语句中的某个部分操作失败或出现异常时,使用rollback()回滚事务。
public class TestTransaction {
public static void main(String[] args) {
Connection connection =null;
PreparedStatement preparedStatement =null;
String sql1 ="UPDATE bank set deposit = deposit - 00 where id = 1";
String sql2 ="UPDATE bank set deposit = deposit + 100 where id = 2";
try {
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
//执行第一条sql1
preparedStatement =connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
//制造异常
int i = 1/0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//无异常发生,则提交
connection.commit();
//检测到异常
} catch (Exception e) {
System.out.println("sql执行未完成,数据回滚");
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
//未使用resultSet,传入null即可
JDBCUtils.close(null,preparedStatement,connection);
}
}
}
如若未发生异常,则会正常执行sql1和sql2。
8. 批处理
批处理即批量处理sql语句
批处理通常和PreparedStatement一起使用,既减少编译次数又减少网络开销,提高效率
JDBC批处理方法:
addBatch():将sql语句添加到Batch
executeBatch():批量执行Batch内的语句
clearBatch():清空批处理包的语句
注:
要在JDBC中使用批处理,必须在配置文件的url中传入rewriteBatchedStatements=true这一参数,否则无法使用批处理
url=jdbc:mysql://localhost:3306/db01?rewriteBatchedStatements=true
public class TestBatch {
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into Batch values(null,?)";
PreparedStatement preparedStatement =connection.prepareStatement(sql);
for (int i = 0; i < 500; i++) {
preparedStatement.setString(1,"batch"+i);
//添加到批处理
preparedStatement.addBatch();
//每攒够100条sql命令,执行一次
if((i+1)%100==0){
//执行Batch内的语句
preparedStatement.executeBatch();
//提交后清空batch内的sql语句
preparedStatement.clearBatch();
}
}
//关闭连接
JDBCUtils.close(null,preparedStatement,connection);
System.out.println("添加成功");
}
}
9. 数据库连接池
当使用DriverManager来获取连接时,每次和数据库建立连接的时候都要将Connection加载到内存中,再验证IPD地址,用户名和密码(0.05s~1s)。若频繁的进行数据库连接,会占用很多系统资源,容易造成数据库崩溃
且每一次数据库连接使用完后都需要断开连接。如果程序出现异常而未能关闭,将导致数据库内存泄露,最终将导致重启数据库。
传统获取连接的方式,不能控制创建的连接数量。且当连接过多时,也可能会导致内存泄露,数据库崩溃
因此使用数据库连接池技术来解决问题。
数据库连接池原理:
- 预先在连接池中放入一定数量的连接,当需要接力数据库连接时,只需从连接池中取出一个,使用完毕后放回
- 数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。
- 当应用程序向连接池请求的连接数超过最大连接量时,这些请求将被加入到等待队列
- 若等待时间过长,则放弃该次连接,重新申请。
数据库连接池的种类:
- JDBC的数据库连接池使用javax.sql.DataSource来表示,DataSource是一个接口,该接口通常由第三方提供
- C3P0数据库连接池,速度相对较慢,稳定性不错。
- DBCP数据库连接池,速度比C3P0快名单不稳定。
- Proxool数据库连接池,有监控连接池状态的功能,稳定性不如C3P0。
- BoneCP数据库连接池速度快。
- Druid集各家有点于一身。
Druid数据库
配置文件:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db01?rewriteBatchedStatements=true
username=wood
password=123
#initial connection size 设定初始的连接个数
initialSize=10
#min idle connection size至少维持多少空闲连接数
minIdle=5
#max active connection size 连接池最多连接数(主要参数)
maxActive=10
#max wait time 在等待队列中的最长时间 ms
maxWait=5000
DruidUtils实现
public class JDBCDruidUtils {
private static DataSource ds;
//在静态代码块完成 ds初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//编写getConnection方法
public static Connection getConnection() throws SQLException, SQLException {
return ds.getConnection();
}
//关闭连接, 在数据库连接池技术中,close() 不是真的断掉连接(用java多态去理解这个close())
//而是把使用的Connection对象放回连接池
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
try{
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
使用Druid连接数据库
public class TestDruid {
public static void main(String[] args) throws Exception {
//这些变量可能需要通过JDBCUtils来关闭
//因此定义在外部
Connection connection =null;
PreparedStatement preparedStatement =null;
String sql = "select id,name from users";
ResultSet resultSet =null;
System.out.println("使用Druid连接数据库");
try {
connection = JDBCDruidUtils.getConnection();
preparedStatement =connection.prepareStatement(sql);
//这里sql语句已经完整,所以不用填充,直接使用
resultSet = preparedStatement.executeQuery();
System.out.println("id"+ "\t"+ "name");
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
System.out.println(id + "\t" + name);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCDruidUtils.close(resultSet,preparedStatement,connection);
}
}
}
10. Apache—DBUtils
在前面使用ResultSet时就提过关闭连接后结果集将无法使用
解决思路:
编写一个类,与数据库返回结果集相映射,通过一个类对象对应一条数据库查询记录的方式,通过ArrayList存储,以实现数据库查询结果的相对持久化的记录。
JavaBean实现
public class users {
private Integer id;
private String name;
private String password;
public users() { //一定要给一个无参构造器[反射需要]
}
public users(Integer id, String name, String password) {
this.id = id;
this.name = name;
this.password = password;
}
@Override
public String toString() {
return "\r\n"+ "users{" +
"id=" + id +
", name='" + name + '\'' +
", password='" + password + '\'' +
'}'+"\r\n";
}
}
main方法
public class TestJavaBean {
public static void main(String[] args) {
ArrayList list =testSelectToArrayList();
System.out.println("list 集合数据: "+list.toString());
}
public static ArrayList<users> testSelectToArrayList() {
Connection connection =null;
PreparedStatement preparedStatement =null;
ResultSet set =null;
String sql = "select * from users";
ArrayList<users> list = new ArrayList<>();//创建ArrayList对象,存放users对象
//3. 创建PreparedStatement 对象
try {
connection = JDBCDruidUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//执行, 得到结果集
set = preparedStatement.executeQuery();
//遍历该结果集
while (set.next()) {
int id = set.getInt("id");
String name = set.getString("name");//getName()
String password = set.getString("password");//getSex()
//把得到的resultset 的记录,封装到 users对象,放入到list集合
list.add(new users(id, name, password)); // 将查询记录,一条接一条放入list中
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCDruidUtils.close(set, preparedStatement, connection);
}
//因为ArrayList 和 connection 没有任何关联,所以该集合可以复用.
return list;
}
}
使用DBUtils:
-
commons -dbutils是Apache组织提供的一个开源JDBC工具类库,它是对JDBC的封装,使用dbutils能极大简化jdbc编码的工作量。
-
QueryRunner类:该类封装了SQL的执行,是线程安全的;可以实现增、删、改、查、批处
-
ResultSetHandler接口:该接口用于处理java.sql.ResultSet,将数据按要求转换为另一种形式
-
ArrayHandler:把结果集中的第一行数据转成对象数组。
-
ArrayListHandler:把结果集中的每一行数据都转成一 个数组,再存放到List中。
-
BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
-
BeanListHandler:将结果集中的每一行数据都封装到一 个对应的JavaBean实例中,存放到List里。
-
ColumnListHandler:将结果集中某列的数据存放到List中。
-
KeyedHandler(name):将结果集中的每行数据都封装到Map里,再把这些map再存到一个map里,其key为指定的key。
-
MapHandler:将结果集中的第一行数据封装到一 个Map里,key是列名,value就是对应的值。
-
MapListHandler:将结果集中的每一行数据都封装到一个Map里, 然后再存放到List。
使用DBUtils
public class TestDBUtils {
public static void main(String[] args) throws SQLException {
//1. 得到 连接 (druid)
Connection connection = JDBCDruidUtils.getConnection();
//2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project
//3. 创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
//4. 就可以执行相关的方法,返回ArrayList 结果集
//String sql = "select * from actor where id >= ?";
// 注意: sql 语句也可以查询部分列
String sql = "select id, name from users";
// 老韩解读
//(1) query 方法就是执行sql 语句,得到resultset ---封装到 --> ArrayList 集合中
//(2) 返回集合
//(3) connection: 连接
//(4) sql : 执行的sql语句
//(5) new BeanListHandler<>(Actor.class): 在将resultset -> Actor 对象 -> 封装到 ArrayList
// 底层使用反射机制 去获取 Actor 类的属性,然后进行封装
//(6) 1 就是给 sql 语句中的? 赋值,可以有多个值,因为是可变参数Object... params
//(7) 底层得到的resultset ,会在query 关闭, 关闭PreparedStatment
List<users> list =
queryRunner.query(connection, sql, new BeanListHandler<>(users.class));
System.out.println("输出集合的信息");
for (users user : list) {
System.out.print(user.toString());
}
//释放资源
JDBCDruidUtils.close(null, null, connection);
}
}
注:
在使用QueryRunner时,JavaBean中必须给每个变量提供set方法
否则只能读到数据有多少行,但具体数据都将为null
11. BasicDao
DAO: Data Access Object 数据访问对象
DAO用于和数据库交互,完成对数据库的crud操作。
在BasicDao的基础上,实现一张表对应一个Dao,更好的完成功能。
如果涉及多表查询的操作,可以用map来解决,也可以设计一个处理多表的Javabean来处理
为什么要用DAO:
- 我们之前的使用的sql语句都是固定的,不能通过参数传入,通用性不好
- select操作的返回值类型不能固定,需要使用泛型
- 当项目很大时,数据库的表也会很多,不可能单靠一个Java类完成
DAO示意图:
DAO的简单设计:
- utils 工具类
- Javabean
- BasicDAO
- XxxDAO,定义一些独有方法,也可以为空
- 测试类
utils工具类和Javabean的实现
和之前用的JDBCDruidUtils以及users类一样,直接导入过来即可
BasicDAO实现
public class BasicDAO<T> {//泛型制定具体类型
private QueryRunner qr = new QueryRunner();
// 开发通用的DML方法,针对任意表
// query方法会关闭preparedStatement和结果集
// JDBCDruidUtils则会关闭connection
// 所以用户只需要输入sql语句和参数即可,非常方便
public int update (String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCDruidUtils.getConnection();
int update = qr.update(connection, sql, parameters);
return update;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCDruidUtils.close(null, null, connection); // 关闭连接
}
}
//返回多个对象(即查询的结果是多行), 针对任意表
/**
*
* @param sql sql 语句,可以有 ?
* @param clazz 传入一个类的Class对象 比如 users.class
* @param parameters 传入 ? 的具体的值,可以是多个
* @return 根据users.class 返回对应的 ArrayList 集合
*/
public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
Connection connection = null;
try {
connection = JDBCDruidUtils.getConnection();
// 返回某类型的对象List数组列表
return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCDruidUtils.close(null, null, connection); // 关闭连接
}
}
// 查询单行结果的通用方法
// BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
// 返回的是一个Object类
public T querySingle(String sql, Class<T> clazz, Object... parameters) {
Connection connection = null;
try {
connection = JDBCDruidUtils.getConnection();
return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCDruidUtils.close(null, null, connection); // 关闭连接
}
}
// 查询单行单列的方法,即返回单值的方法
// 这里的Object是单行单列的某元素类型(int、string ....)
// 如查询某行的姓名项,此时就是String类型,这里用Object类
public Object queryScalar(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCDruidUtils.getConnection();
return qr.query(connection, sql, new ScalarHandler(), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCDruidUtils.close(null, null, connection); // 关闭连接
}
}
}
usersDAO:
public class usersDAO extends BasicDAO{
//可以写usersDAO特有的操作
}
测试DAO:
public class TestUsersDAO {
public static void main(String[] args) {
usersDAO usersDAO1 = new usersDAO();
//1. 多行查询
List<users> user =
usersDAO1.queryMulti("select * from users where id >= ?", users.class, 1);
System.out.println("===查询多行结果===");
for (users use: user) {
System.out.println(use);
}
//2. 查询单行记录
Object user1 =
usersDAO1.querySingle("select * from users where id = ?", users.class, 1);
System.out.println("====查询单行结果====");
System.out.println(user1);
//3. 查询单行单列
Object user3 = usersDAO1.queryScalar("select name from users where id = ?", 1);
System.out.println("====查询单行单列值===");
System.out.println(user3);
System.out.println("======测试dml======");
//4. dml操作
int update = usersDAO1.update("insert into users values(null, ?, ?)", "qi", "456");
System.out.println(update > 0 ? "执行成功" : "执行没有影响表");
}
}