批处理.德鲁伊.Apache"/>
JDBC和数据库连接池: SQL注入.预处理.批处理.德鲁伊.Apache
JDBC 概述
基本介绍
模拟JDBC
public class TestJdbc {public static void main(String[] args) {JdbcInterface jdbcInterface = new MysqlJdbcImpl();//完成对mysql的操作jdbcInterface.getConnection();jdbcInterface.crud();jdbcInterface.close();}
}
//java 厂商规定的jdbc接口
public interface JdbcInterface {//连接public Object getConnection();//CRUDpublic void crud();//关闭连接public void close();
}
//mysql数据库实现了jdbc接口(mysql厂商开发)
public class MysqlJdbcImpl implements JdbcInterface {@Overridepublic Object getConnection() {System.out.println("得到mysql的连接");return null;}@Overridepublic void crud() {System.out.println("完成mysql增删改查");}@Overridepublic void close() {System.out.println("关闭mysql的连接");}
}
JDBC 带来的好处
- JDBC是java提供一套用于数据库操作的接口API,java程序员只需要面向这套接口编程即可.不同的数据库厂商,需要针对这套接口,提供不同实现.
- 如果java直接访问数据库:可行,但不建议,因为这意味着java应用程序没有更好的可移植性.
JDBC API
JDBC 快速入门
JDBC程序编写步骤:(先执行前置工作步骤)
- 注册驱动–加载Driver类(该类在驱动包下的com.mysql.jdbc.Driver路径)
- 获取连接–得到Connection
- 执行增删改查–发送SQL给mysql执行
- 释放资源-- 关闭相关连接
前置工作:
- 在项目下创建一个文件夹比如 libs
- 将mysql-connector-java-5.1.37-bin.jar拷贝到该目录下,点击 add to project…加入到项目中
- 该jar包中即为mysql数据库针对java接口做的实现
//jdbc程序,完成简单操作
public class Jdbc01 {public static void main(String[] args) throws SQLException {//前置工作： 在项目下创建一个文件夹比如 libs// 将 mysql.jar 拷贝到该目录下，点击 add to project ..加入到项目中//1. 注册驱动Driver driver = new Driver();//com.mysql.jdbc.Driver//2.得到连接//(1) jdbc:mysql:// 规定好表示协议，通过jdbc的方式连接mysql//(2) localhost 主机，可以是ip地址//(3) 3306 表示mysql监听的端口//(4) hsp_db02 连接到mysql dbms 的哪个数据库//(5) mysql的连接本质就是前面学过的socket连接String url = "jdbc:mysql://localhost:3306/db02";//将 用户名和密码放入到Properties 对象Properties properties = new Properties();properties.setProperty("user", "root");properties.setProperty("password", "root");Connection connect = driver.connect(url, properties);//3.执行sqlString sql = "insert into actor values(null,'刘德华','男','1970-11-11','110')";sql = "update actor set name='周星驰' where id=1";sql = "delete from actor where id=1";//statement 用于执行静态SQL语句并返回其生成的结果的对象Statement statement = connect.createStatement();int rows = statement.executeUpdate(sql);//执行sql语句, 如果是 dml语句，返回的就是影响行数System.out.println(rows > 0 ? "成功" : "失败");//4.关闭连接资源statement.close();connect.close();}
}
获取数据库连接(5种方式)
- 方式1: 注册驱动(创建Driver对象)–>将user,password存入配置文件(properties)–>使用方法driver.connect(url,properties)得到连接
- 方式2: 注册驱动(通过反射动态加载Driver类–>newInstance()-创建Driver对象)–>将user,password存入配置文件(properties)–>使用方法driver.connect(url,properties)得到连接
- 方式3: 通过反射创建对象–>通过DriverManager静态方法注册驱动–>通过DriverManager静态方法得到连接
- 方式4: 使用反射加载Driver–>类加载执行静态代码块完成注册–>通过DriverManager静态方法得到连接
- 方式5: 增加配置文件–>从配置文件读取到url,user,password,driver参数–>执行方式4步骤
总结:
无论属于5种方式的哪一种,最终都是通过DriverManager静态方法注册驱动,具体表现为两种途径:
- 间接方式:直接创建或通过反射创建或加载Driver类的对象,从而加载Driver类,执行其类中的静态代码块[如下图],调用 DriverManager 静态方法注册驱动
- 直接方式:不创建Driver对象,自动调用驱动包下的类名通过class.forName()去注册
提示:
- mysql驱动5.1.6可以无需Class.forName(“com.mysql.jdbc.Driver”);
- 从jdk1.5以后使用了jdbc4,不再需要显示调用class.forName()注册驱动而是自动调用驱动jar包下META-INF\services\java.sql.Driver文本中的类名称去注册
- 建议还是写上Class.forName(“com.mysql.jdbc.Driver”),更加明确
//java连接mysql的5种方式
public class JdbcConn {//方式1@Testpublic void connect01() throws SQLException {Driver driver = new Driver();//创建driver对象//连接到ip+数据库String url = "jdbc:mysql://localhost:3306/db02";//存入mysql用户名密码Properties properties = new Properties();properties.setProperty("user", "root");properties.setProperty("password", "root");//得到连接Connection connect = driver.connect(url, properties);System.out.println(connect);}//方式2@Testpublic void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {//通过反射动态加载Driver类,更灵活Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");Driver driver = (Driver) aClass.newInstance();String url = "jdbc:mysql://localhost:3306/db02";Properties properties = new Properties();properties.setProperty("user", "root");properties.setProperty("password", "root");Connection connect = driver.connect(url, properties);System.out.println(connect);}//方式3 使用DriverManager 替代 driver 进行统一管理@Testpublic void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");Driver driver = (Driver) aClass.newInstance();String url = "jdbc:mysql://localhost:3306/db02";String password = "root";String user = "root";//通过DriverManager静态方法注册驱动DriverManager.registerDriver(driver);//通过DriverManager静态方法得到连接Connection connection = DriverManager.getConnection(url, user, password);System.out.println(connection);}//方式4 Driver类加载时自动注册驱动,简化,推荐使用@Testpublic void connect04() throws ClassNotFoundException, SQLException {//使用反射加载Driver,提示:jdk1.5开始使用jdbc4以后,无需加载,自动调用Class.forName("com.mysql.jdbc.Driver");//类加载执行静态代码块完成注册/*static {try {DriverManager.registerDriver(new Driver());} catch (SQLException var1) {throw new RuntimeException("Can't register driver!");}}*/String url = "jdbc:mysql://localhost:3306/db02";String password = "root";String user = "root";Connection connection = DriverManager.getConnection(url, user, password);System.out.println("connection = " + connection);}//方式5 增加配置文件,更加灵活@Testpublic void connect05() throws IOException, ClassNotFoundException, SQLException {//通过Properties对象获取配置文件信息Properties properties = new Properties();properties.load(new FileReader("src\\mysql.properties"));String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");Class<?> aClass = Class.forName(driver);//建议写上Connection connection = DriverManager.getConnection(url, user, password);System.out.println("connection = " + connection);}
}
ResultSet结果集
基本介绍
debug查看resultSet内部结构
由图看出:
- resultSet底层维护了ArrayList集合存入了多行记录
- 每行记录由byte类型二维数组维护
- 该行的每列(标量)存放在byte[]中
代码实现:
//演示select语句返回结果集,并取出结果
public class ResultSet_ {public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {//通过Properties对象获取配置文件的信息Properties properties = new Properties();properties.load(new FileReader("chapter25\\src\\mysql.properties"));String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");//1. 注册驱动Class.forName(driver);//2. 得到连接Connection connection = DriverManager.getConnection(url, user, password);//3. 得到StatementStatement statement = connection.createStatement();//4. 组织SqLString sql = "select id,name,sex,borndate,phone from actor";//debug得到resultSet类型:com.mysql.jdbc.JDBC42ResultSet@6e0e048a//该对象里有rowData-->rows-->elementDate存放每行记录里的数据//执行给定的SQL语句，该语句返回单个 ResultSet对象ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {// 让光标向后移动，如果没有更多行，则返回falseint id = resultSet.getInt(1);//获取该行的第1列
// int id = resultSet.getInt("id");通过列名来获取值, 推荐String name = resultSet.getString(2);String sex = resultSet.getString(3);Date date = resultSet.getDate(4);System.out.println(id+"\t"+name+"\t"+sex+"\t"+date);}//关闭连接resultSet.close();statement.close();connection.close();}
}
Statement
基本介绍
mysql中演示sql注入
create table admin(`name` varchar(32)not null unique,`pwd` varchar(32) not null default '')character set utf8;insert into admin values('tom','123');
-- 查找某个管理是否存在
select * from admin where `name`='tom' AND `pwd`='123';
-- sql注入:tom as 1' or , 123 as or '1'='1select * from admin where `name`='1' or' AND `pwd`='or '1'='1';
说明:
当查询时可在输入过滤条件的参数时使用万能用户名(1’ or)和密码( or ‘1’='1)即可
jdbc中演示SQL注入
//演示statement的注入问题
public class Statement_ {public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {Scanner sc = new Scanner(System.in);System.out.println("请输入用户名:");//1' orString name = sc.nextLine();//next(): 当接收到 空格或者 '就是表示结束,只能用nextLineSystem.out.println("请输入密码:");// or '1' = '1String pwd = sc.nextLine();Properties properties = new Properties();properties.load(new FileReader("chapter25\\src\\mysql.properties"));String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");String url = properties.getProperty("url");Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, password);Statement statement = connection.createStatement();String sql = "select name,pwd from admin where name='"+name+"' and pwd='"+pwd+"'";ResultSet resultSet = statement.executeQuery(sql);if (resultSet.next()) { //如果查询到一条记录，则说明该管理存在System.out.println("恭喜,登录成功");} else {System.out.println("抱歉,登录失败");}resultSet.close();statement.close();connection.close();}
}
PreparedStatement
基本介绍
预处理好处
- 不再使用+拼接sql语句,减少语法错误
- 有效的解决了sql注入问题
- 大大减少了编译次数,效率较高
查询应用案例:
//演示PreparedStatement使用:有效防止statement注入
public class PreparedStatement_ {public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {Scanner sc = new Scanner(System.in);System.out.println("请输入用户名:");//1' orString name = sc.nextLine();//next(): 当接收到 空格或者 '就是表示结束,只能用nextLineSystem.out.println("请输入密码:");// or '1' = '1String pwd = sc.nextLine();Properties properties = new Properties();properties.load(new FileReader("chapter25\\src\\mysql.properties"));String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");String url = properties.getProperty("url");Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, password);//先组织字段值为?的sql语句-->构造PreparedStatement对象时使用String sql = "select name,pwd from admin where name=? and pwd=?";PreparedStatement preparedStatement = connection.prepareStatement(sql);//调用方法给?赋值preparedStatement.setString(1, name);preparedStatement.setString(2, pwd);//执行select语句时再不可填入sql作为参数ResultSet resultSet = preparedStatement.executeQuery();if (resultSet.next()) { //如果查询到一条记录，则说明该管理存在System.out.println("恭喜,登录成功");} else {System.out.println("抱歉,登录失败");}resultSet.close();preparedStatement.close();connection.close();}
}
DML案例:
//演示预处理方式使用dml语句@SuppressWarnings({"all"})
public class PreparedStatementDML_ {public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {Scanner sc = new Scanner(System.in);System.out.println("请输入用户名:");//1' orString name = sc.nextLine();//next(): 当接收到 空格或者 '就是表示结束,只能用nextLineSystem.out.println("请输入密码:");// or '1' = '1String pwd = sc.nextLine();Properties properties = new Properties();properties.load(new FileReader("chapter25\\src\\mysql.properties"));String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");String url = properties.getProperty("url");Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, password);//先组织字段值为?的sql语句-->构造PreparedStatement对象时使用String sql = "select name,pwd from admin where name=? and pwd=?";sql = "insert into admin values(?,?)";sql = "update admin set pwd=? where name=?";//注意两个字段赋值的顺序变化sql = "delete from admin where name =?";PreparedStatement preparedStatement = connection.prepareStatement(sql);//调用方法给?赋值preparedStatement.setString(1, name);preparedStatement.setString(2, pwd);//执行DML语句int update = preparedStatement.executeUpdate();System.out.println(update>0?"执行成功":"受影响的行数为0");preparedStatement.close();connection.close();}
}
JDBC相关API小结
总结:以上接口都是在导入的mysql-connector-java-5.1.37-bin.jar中提供实现,即mysql提供.
封装 JDBCUtils
在jdbc操作中,获得连接和释放资源是经常用到的,可以将其封装到JDBC连接的工具类:JDBCUtils
作用:完成 mysql的连接和关闭资源
代码实现:
//工具类，完成 mysql的连接和关闭资源(因为连接和关闭只执行一次,使用静态成员)
public class JDBCUtils {private static String url;private static String user;private static String pwd;private static String driver;//初始化读取属性值static {Properties properties = new Properties();try {properties.load(new FileReader("src\\mysql.properties"));url = properties.getProperty("url");user = properties.getProperty("user");pwd = properties.getProperty("password");driver = properties.getProperty("driver");} catch (IOException e) {//在实际开发中，我们可以这样处理//1. 将编译异常转成 运行异常//2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.throw new RuntimeException(e);}}//得到连接public static Connection getConnect() {try {return DriverManager.getConnection(url, user, pwd);} catch (Exception e) {throw new RuntimeException(e);}}//关闭相关资源(如果没有该对象传入null)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);}}}
- 使用JDBCUtils工具类，完成CRUD
//演示使用JDBCUtils工具类，完成dml 和 select
public class JDBCUtils_Use {@Testpublic void testSelect() throws SQLException {//使用trycatch捕捉的话注意待关闭的资源的引用(变量)的作用域//使用工具类静态方法得到连接Connection connect = JDBCUtils.getConnect();System.out.println(connect.getClass());//(Collection在mysql中的实现类)class com.mysql.jdbc.JDBC4Connection//sqlString sql = "select id,name,sex,borndate,phone from actor where id=?";PreparedStatement preparedStatement = connect.prepareStatement(sql);//?赋值preparedStatement.setInt(1,2);//查询(1条),获得结果集ResultSet resultSet = preparedStatement.executeQuery();System.out.println(resultSet.getClass());//指针下移resultSet.next();//处理结果集int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");Date borndate = resultSet.getDate("borndate");String phone = resultSet.getString("phone");System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);//关闭资源JDBCUtils.close(resultSet, preparedStatement, connect);}@Testpublic void testDML() {Connection connection = null;PreparedStatement preparedStatement = null;try {connection = JDBCUtils.getConnect();String sql = "update actor set name='周星驰' where id=?";preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, 2);preparedStatement.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.close(null,preparedStatement,connection);}}
}
事务
基本介绍
应用实例
模拟经典的转账业务
- 不使用事务可能出现的问题:默认情况下,connection是默认自动提交,导致执行1条sql语句后如果出现异常,由于try catch的特性,后面的sql语句没有被执行,导致转账出错
- 使用事务保持一致性,全部执行或全部回退,不会发生上述情况
代码实现:
//jdbc 中使用事务
public class Transaction_ {@Testpublic void noTransaction() {Connection connection = null;PreparedStatement preparedStatement = null;try {connection = JDBCUtils.getConnect();String sql = "update account set balance=balance-100 where id=1";preparedStatement = connection.prepareStatement(sql);preparedStatement.executeUpdate();//该方法无参默认执行由连接获得preparedstatement的方法中的sqlint i=1/0;//转账失败,化腾血亏100块String sql2 = "update account set balance=balance+100 where id=2";preparedStatement.executeUpdate(sql2);//该方法优先执行自己的sql2} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.close(null,preparedStatement,connection);}}@Testpublic void transaction() {Connection connection = null;PreparedStatement preparedStatement = null;try {//在默认情况下，connection是默认自动提交connection = JDBCUtils.getConnect();//开启事务connection.setAutoCommit(false);String sql = "update account set balance=balance-100 where id=1";preparedStatement = connection.prepareStatement(sql);preparedStatement.executeUpdate();//该方法无参默认执行由连接获得preparedstatement的方法中的sqlint i=1/0;//转账失败,终止交易,化腾不亏即血赚String sql2 = "update account set balance=balance+100 where id=2";preparedStatement.executeUpdate(sql2);//该方法优先执行自己的sql2//提交事务connectionmit();} catch (Exception e) {//这里我们可以进行回滚，即撤销执行的SQL//默认回滚到事务开始的状态.System.out.println("执行发生了异常，撤销执行的sql");try {connection.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();} finally {JDBCUtils.close(null,preparedStatement,connection);}}
}
批处理
基本介绍
应用实例
准备工作:创建mysql中表格
create table admin2(id int primary key auto_increment,username varchar(32) not null,`password` varchar(32) not null);
jdbc代码实现:
//jdbc 的批处理
//总结:执行多条SQL语句:
//方式1:每次执行(executeUpdate)完sql,
//-->只需更新sql语句内容(sql赋新值或者setString等方法改sql参数)
//-->再执行(executeUpdate)改变的sql
//方式2:向集合中加入(addBatch(Str/no paramters))改后的sql,批量执行public class Batch_ {@SuppressWarnings({"all"})@Testpublic void noBatch() throws SQLException {Connection connect = JDBCUtils.getConnect();String sql = "insert into admin2 values(null ,?,?)";PreparedStatement preparedStatement = connect.prepareStatement(sql);long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {preparedStatement.setString(1, "jack[" + i + "]");preparedStatement.setString(2, "123");preparedStatement.executeUpdate();}long end = System.currentTimeMillis();System.out.println("不使用批处理耗时:" + (end - start));//6358msJDBCUtils.close(null, preparedStatement, connect);}@Test//别忘了改配置文件的url参数?rewriteBatchedStatements=truepublic void batch() throws SQLException {Connection connect = JDBCUtils.getConnect();String sql = "insert into admin2 values(null ,?,?)";PreparedStatement preparedStatement = connect.prepareStatement(sql);long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {preparedStatement.setString(1, "jack[" + i + "]");preparedStatement.setString(2, "123");//将sql 语句加入到批处理包中 -> 看源码/*//1. //第一就创建 ArrayList - elementData => Object[]//2. elementData => Object[] 就会存放我们预处理的sql语句//3. 当elementData满后,就按照1.5扩容//4. 当添加到指定的值后，就executeBatch//5. 批量处理会减少我们发送sql语句的网络开销，而且减少编译次数，因此效率提高public void addBatch() throws SQLException {synchronized(this.checkClosed().getConnectionMutex()) {if (this.batchedArgs == null) {this.batchedArgs = new ArrayList();}for(int i = 0; i < this.parameterValues.length; ++i) {this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);}this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));}}*/preparedStatement.addBatch();//preparedStatement中的SQL语句放入 集合if ((i + 1) % 1000 == 0) {preparedStatement.executeBatch();preparedStatement.clearBatch();}}long end = System.currentTimeMillis();System.out.println("使用批处理耗时:" + (end - start));//142msJDBCUtils.close(null, preparedStatement, connect);}
}
数据库连接池
问题引出
5k次连接数据库问题:
- 编写程序完成连接MySQL5000次的操作
- 看看有什么问题,耗时又是多久.==>数据库连接池
//测试传统方式连接的速度
public class ConQuestion {@Testpublic void testCon() {System.out.println("开始连接...");long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {Connection connect = JDBCUtils.getConnect();//如果不关闭连接:Too many connectionsJDBCUtils.close(null,null,connect);}long end = System.currentTimeMillis();System.out.println("传统方式连5000次耗时:"+(end-start));//13197ms}
}
问题分析
数据库连接池种类
说明:使用了第三方数据库连接池后,连接池,连接等接口以及接口对应的方法都改为由第三方提供实现(jar驱动包中)
C3P0应用实例
前置工作:
- 将c3p0-0.9.1.2.jar拷贝到项目下指定的目录(libs)下,点击 add to project…加入到项目中,包中提供数据库连接池接口的实现–>数据库连接池接口的实现类型变成com.mchange.v2.c3p0.ComboPooledDataSource–>Connection接口的实现类型变成com.mchange.v2.c3p0.impl.NewProxyConnection
- 将c3p0-config.xml拷贝到项目目录(src)下,该文件指定了连接数据库和连接池的相关参数(url,user,password,driver驱动路径)
//演示使用c3p0
public class C3P0_ {//方式1： 相关参数，在程序中指定user, url , password等@Testpublic void testC3P0_01() throws Exception {//1. 创建一个数据源对象ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();//com.mchange.v2.c3p0.ComboPooledDataSource//2. 通过配置文件mysql.properties 获取相关连接的信息Properties properties = new Properties();properties.load(new FileReader("src\\mysql.properties"));String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");//给数据源 comboPooledDataSource 设置相关的参数//注意：连接管理是由 comboPooledDataSource 来管理comboPooledDataSource.setDriverClass(driver);comboPooledDataSource.setJdbcUrl(url);comboPooledDataSource.setUser(user);comboPooledDataSource.setPassword(password);//设置初始化连接数comboPooledDataSource.setInitialPoolSize(10);//最大连接数comboPooledDataSource.setMaxPoolSize(50);long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {Connection connection = comboPooledDataSource.getConnection();connection.close();}long end = System.currentTimeMillis();System.out.println("c3p0连接5000次耗时:"+(end-start));//1119}//第二种方式 使用配置文件模板来完成//1. 将c3p0 提供的 c3p0.config.xml 拷贝到 src目录下//2. 该文件指定了连接数据库和连接池的相关参数@Testpublic void testc3p0_02() throws SQLException {//提示:该方式的构造器参数不能为空,且与配置文件的named-config name保持一致ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {Connection connection = comboPooledDataSource.getConnection();connection.close();}long end = System.currentTimeMillis();System.out.println("c3p0连接5000次耗时:"+(end-start));//1018}
}
Druid(德鲁伊)应用实例
前置工作:
- 将druid-1.1.10.jar拷贝到项目下指定的目录(libs)下,点击 add to project…加入到项目中,包中提供数据库连接池接口的实现–>数据库连接池接口的实现类型变成com.alibaba.druid.pool.DruidDataSource–>Connection接口的实现类型变成com.alibaba.druid.pool.DruidPooledConnection
- 将druid.properties拷贝到项目目录(src)下,该文件指定了连接数据库和连接池的相关参数(url,user,password,driver驱动路径)
使用:
//Druid数据库连接管理的使用
public class Druid_ {@Testpublic void testDruid() throws Exception {Properties properties = new Properties();properties.load(new FileReader("src\\druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {Connection connection = dataSource.getConnection();connection.close();}long end = System.currentTimeMillis();System.out.println("druid连接5000次耗时:"+(end-start));//741}
}
Druid工具类
说明:将 JDBCUtils 工具类改成Druid (德鲁伊)实现
功能:创建一个数据源对象–>通过配置文件druid.properties 获取相关连接的信息–>得到连接Connection(引用)–>关闭连接(将连接引用的对象放回连接池)
//基于druid数据库连接池的工具类
public class JDBCUtilsByDruid {private static DataSource dataSource;static {Properties properties = new Properties();try {properties.load(new FileReader("src\\druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() {Connection connection=null;try {connection=dataSource.getConnection();} catch (SQLException e) {e.printStackTrace();}return 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是接口,因第三方不同,Collection实现类不同//这里关闭机制由原来的mysql里Collection实现类提供//改为由Druid里的实现类提供关闭机制//两者区别:后者把引用断掉,collection对象放回连接池//前者:直接断开JDBC数据库连接connection.close();}} catch (SQLException e) {throw new RuntimeException(e);}}
}
使用Druid工具类
public class JDBCUtilsByDruid_USE {@Testpublic void testSelect() throws SQLException {//使用trycatch捕捉的话注意待关闭的资源的引用(变量)的作用域//使用工具类静态方法得到连接Connection connect = JDBCUtilsByDruid.getConnection();System.out.println(connect.getClass());//(Collection在Druid中的实现类)class com.alibaba.druid.pool.DruidPooledConnection//sqlString sql = "select id,name,sex,borndate,phone from actor where id=?";PreparedStatement preparedStatement = connect.prepareStatement(sql);//?赋值preparedStatement.setInt(1,2);//查询(1条),获得结果集ResultSet resultSet = preparedStatement.executeQuery();//如果在操作结果集之前就关闭连接即关闭结果集 ,会异常//-->Operation not allowed after ResultSet closed//-->因此在关闭之前就要将读取结果封装到集合中//指针下移resultSet.next();//处理结果集int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");Date borndate = resultSet.getDate("borndate");String phone = resultSet.getString("phone");System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);//关闭资源JDBCUtilsByDruid.close(resultSet, preparedStatement, connect);}
}
Apache-DBUtils
分析问题
- 关闭connection后,resultSet结果集无法使用
- resultSet不利于数据的管理
自行尝试解决
手动封装记录:创建集合–>将循环读取resultSet中的记录并保存在事先创建的集合中
@Test
public void testSelectToArrayList() throws SQLException {ArrayList<Actor> list = new ArrayList<>();Connection connection = JDBCUtilsByDruid.getConnection();String sql = "select * from actor where id>?";PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1,0);ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");Date borndate = resultSet.getDate("borndate");String phone = resultSet.getString("phone");list.add(new Actor(id, name, sex, borndate, phone));}JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);System.out.println(list);
}
工具介绍
功能:隐式编译预处理sql语句–>执行SQL语句后,隐式处理CRUD结果(封装查询记录),直接返回存放记录的集合/对象–>隐式关闭结果集–>隐式关闭preparedstatement
提示:使用DBUtils同时使用德鲁伊工具类时,不需显示关闭结果集和preparedstatement
前置工作:
将commons-dbutils-1.3.jar拷贝到项目下指定的目录(libs)下,点击 add to project…加入到项目中
应用实例:
//使用 apache-DBUtils 工具类 + druid 完成对表的 crud 操作
public class DBUtils_USE {@Test//返回结果是多行的情况public void selectManyTest() throws SQLException {Connection connection = JDBCUtilsByDruid.getConnection();//2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project//3. 创建 QueryRunnerQueryRunner queryRunner = new QueryRunner();//org.apachemons.dbutilsString sql = "select * from actor where id>?";//(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/*** 分析 queryRunner.query方法:* public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {* PreparedStatement stmt = null;//定义PreparedStatement* ResultSet rs = null;//接收返回的 ResultSet* Object result = null;//返回ArrayList** try {* stmt = this.prepareStatement(conn, sql);//创建PreparedStatement* this.fillStatement(stmt, params);//对sql 进行 ? 赋值* rs = this.wrap(stmt.executeQuery());//执行sql,返回resultset* result = rsh.handle(rs);//返回的resultset --> arrayList[result] [使用到反射，对传入class对象处理]* } catch (SQLException var33) {* this.rethrow(var33, sql, params);* } finally {* try {* this.close(rs);//关闭resultset* } finally {* this.close((Statement)stmt);//关闭preparedstatement对象* }* }** return result;* }*/List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 0);System.out.println(list);JDBCUtilsByDruid.close(null,null,connection);}@Test//返回结果是单行的情况public void selectSingle() throws SQLException {Connection connection = JDBCUtilsByDruid.getConnection();QueryRunner queryRunner = new QueryRunner();String sql = "select * from actor where id=?";// 因为我们返回的单行记录<--->单个对象 , 使用的Hander 是 BeanHandlerActor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 3);System.out.println(actor);JDBCUtilsByDruid.close(null,null,connection);}@Test//返回结果是标量(单行单列)的情况public void selectsaclar() throws SQLException {Connection connection = JDBCUtilsByDruid.getConnection();QueryRunner queryRunner = new QueryRunner();String sql = "select name from actor where id=?";//返回单行单列 , 返回的就是Object,使用的handler 就是 ScalarHandlerObject o = queryRunner.query(connection, sql, new ScalarHandler(),3);System.out.println(o);JDBCUtilsByDruid.close(null,null,connection);}//演示 apache-dbutils + druid 完成 dml (update, insert ,delete)@Testpublic void dml() throws SQLException {Connection connection = JDBCUtilsByDruid.getConnection();String sql = "update actor set name=? where id=?";sql = "insert into actor values(null,?,?,?,?)";sql = "delete from actor where id=?";QueryRunner queryRunner = new QueryRunner();//(1) 执行dml 操作是 queryRunner.update()//(2) 返回的值是受影响的行数 (affected: 受影响)// queryRunner.update(connection, sql, "刘德华", 2);
// queryRunner.update(connection, sql, "黎明", "男","1971-11-11","114");int affectedRow = queryRunner.update(connection, sql, 2);System.out.println(affectedRow>0?"执行成功":"执行没有影响到表");JDBCUtilsByDruid.close(null,null,connection);}
}
补充:表和 JavaBean 的类型映射关系
DAO
问题
说明
BasicDAO 应用实例
代码实现:
domain层:
package com.hspedu.dao_.domain;import java.util.Date;
//javaBean/domain
public class Actor {private Integer id;private String name;private String sex;private Date borndate;private String phone;public Actor() {}public Actor(Integer id, String name, String sex, Date borndate, String phone) {this.id = id;this.name = name;this.sex = sex;this.borndate = borndate;this.phone = phone;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Date getBorndate() {return borndate;}public void setBorndate(Date borndate) {this.borndate = borndate;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}@Overridepublic String toString() {return "\nActor{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", borndate=" + borndate +", phone='" + phone + '\'' +'}';}
}
utils层
package com.hspedu.dao_.utils;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;/*** @ClassName* @Description* @Author zxk* @DateTime 2022-02-19-21:24* @Version*///基于druid数据库连接池的工具类
public class JDBCUtilsByDruid {private static DataSource dataSource;static {Properties properties = new Properties();try {properties.load(new FileReader("src\\druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection() {Connection connection=null;try {connection=dataSource.getConnection();} catch (SQLException e) {e.printStackTrace();}return 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是接口,因第三方不同,Collection实现类不同//这里关闭机制由原来的mysql里Collection实现类提供//改为由Druid里的实现类提供关闭机制//两者区别:后者把引用断掉,collection对象放回连接池//前者:直接断开JDBC数据库连接connection.close();}} catch (SQLException e) {throw new RuntimeException(e);}}
}
dao层
package com.hspedu.dao_.dao;import com.hspedu.dao_.utils.JDBCUtilsByDruid;
import org.apachemons.dbutils.QueryRunner;
import org.apachemons.dbutils.handlers.BeanHandler;
import org.apachemons.dbutils.handlers.BeanListHandler;
import org.apachemons.dbutils.handlers.ScalarHandler;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
//开发BasicDAO , 是其他DAO的父类
public class BasicDAO<T>{private QueryRunner qr = new QueryRunner();//apache-DB工具类private Connection connection ;//开发通用的dml方法, 针对任意的表public int update(String sql, Object... parameters) {try {connection = JDBCUtilsByDruid.getConnection();return qr.update(connection, sql, parameters);} catch (SQLException e) {//将编译异常->运行异常 ,抛出,解决了:该方法接收不到返回语句的编译问题(作用域)throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}}//返回多个对象(即查询的结果是多行), 针对任意表/**** @param sql sql 语句，可以有 ?* @param clazz clazz 传入一个类的Class对象 比如 Actor.class* @param parameters parameters 传入 ? 的具体的值，可以是多个* @return 根据Actor.class 返回对应的 ArrayList 集合*/public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {try {connection = JDBCUtilsByDruid.getConnection();return qr.query(connection, sql, new BeanListHandler<>(clazz), parameters);} catch (Exception e) {throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null, null, connection);}}//查询单行结果 的通用方法public T querySingle(String sql, Class<T> clazz, Object... parameters) {try {connection = JDBCUtilsByDruid.getConnection();return qr.query(connection,sql, new BeanHandler<>(clazz), parameters);} catch (SQLException e) {throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}}//查询单行单列的方法,即返回单值的方法public Object queryScalar(String sql, Object... parameters) {try {connection = JDBCUtilsByDruid.getConnection();return qr.query(connection, sql, new ScalarHandler(), parameters);} catch (Exception e) {throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}}
}
package com.hspedu.dao_.dao;import com.hspedu.dao_.domain.Actor;public class ActorDAO extends BasicDAO<Actor> {//1. 就有 BasicDAO 的方法//2. 根据业务需求，可以编写特有的方法.
}
view层(测试)
package com.hspedu.dao_.test;import com.hspedu.dao_.dao.ActorDAO;
import com.hspedu.dao_.domain.Actor;
import org.junit.jupiter.api.Test;import java.util.List;public class TestDAO {@Testpublic void testActorDAO() {ActorDAO actorDAO = new ActorDAO();//4. dml操作 insert ,update, deleteint update = actorDAO.update("update actor set name='周星驰' where id=?", 3);System.out.println(update>0?"执行成功":"没有受影响的行数");//1. 查询多行记录List<Actor> actors = actorDAO.queryMulti("select * from actor where id>?", Actor.class, 0);for (Actor actor : actors) {System.out.println(actor);}//2. 查询单行记录Actor actor = actorDAO.querySingle("select * from actor where id=?", Actor.class, 4);System.out.println(actor);//3. 查询单行单列Object o = actorDAO.queryScalar("select * from actor where id=?", 4);System.out.println(o);}
}
更多推荐
JDBC和数据库连接池: SQL注入.预处理.批处理.德鲁伊.Apache
发布评论