JDBC和数据库连接池

JDBC 概述基本介绍原理示意图代码示例JdbcInterface 模拟Java公司提供给其它数据库厂商的接口，供给调用TestJdbc 模拟一个类来实现数据库的调用和相关操作MysqlJdbcimpl 模拟Mysql厂商使用JavaOracleJdbcimpl 模拟Oracle厂商使用JavaJDBC带来的好处JDBC APIJDBC入门JDBC程序编写步骤JDBC的第一个程序获取数据库连接的5种方式方式1方式2方式3方式4方式5课后作业ResultSet[结果集]基本介绍应用实例Statement基本介绍SQL注入小案例(sqlyog操作)SQL注入小案例(IDEA演示)PreparedStatement预处理的好处查询queryPreparedStatement DML语句插入 Insert更新Update删除Delete课堂练习JDBC API封装JDBCUtils 关闭连接得到连接说明工具类DML使用类DQL使用类事务基本介绍应用实例模拟转账业务没有事务控制出现数据不一致问题事务控制后消除数据不一致问题批处理基本介绍数据库连接池5000次连接数据库问题传统方式获取Connection问题分析数据库连接池的基本介绍数据库连接池类型C3P0应用实例Druid(德鲁伊)应用实例将JDBCUtils更改成Druid实现Apache—DBUtils分析一个问题用自己的土方法模拟DBUtilsDBUtils基本介绍应用实例演示 apache-dbutils + druid 完成 返回的结果是单行记录(单个对象)演示 apache-dbutils + druid 返回的结果是单行单列(Object)演示修改Update演示插入Insert演示删除Delete表和 JavaBean 的类型映射关系DAO 和增删改查通用方法-BasicDao分析一个问题基本说明BasicDAOActorDaoActorTestDao课后练习BasicDaoGoodsDaoGoodsGoodsTest

JDBC 概述

基本介绍

JDBC为访问不同的数据库提供了统一的接口，为使用者屏蔽了细节问题。Java程序员使用JDBC，可以连接任何提供了JDBC驱动的数据库系统，从而完成对数据库的各种操作JDBC的原理

原理示意图

代码示例

JdbcInterface 模拟Java公司提供给其它数据库厂商的接口，供给调用

package com.jdbc;/*** @InterfaceName: JdbcInterface* @Description: Java公司提供给其它数据库厂商的接口，供给调用* @Author: wty* @Date: /11/6*/public interface JdbcInterface {/*** 连接数据库** @return java.lang.Object* @Param No such property: code for class: Script1* @Date /11/6 11:59* @Author wty**/public Object getConnection();/*** 进行CRUD** @return void* @Param No such property: code for class: Script1* @Date /11/6 11:59* @Author wty**/public void crud();/*** 关闭连接** @return void* @Param No such property: code for class: Script1* @Date /11/6 11:59* @Author wty**/public void close();}

TestJdbc 模拟一个类来实现数据库的调用和相关操作

package com.jdbc;/*** 模拟一个类来实现数据库的调用和相关操作** @author wty* @date /11/6 12:02*/public class TestJdbc {public static void main(String[] args) {// 完成对mysql的操作JdbcInterface jdbcInterface = new MysqlJdbcimpl();// jdbcInterface = new OracleJdbcimpl();// 获取连接 : 动态绑定jdbcInterface.getConnection();// 增删改查jdbcInterface.crud();// 关闭连接jdbcInterface.close();}}

MysqlJdbcimpl 模拟Mysql厂商使用Java

package com.jdbc;/*** 模拟Mysql厂商使用Java** @author wty* @date /11/6 12:00*/public class MysqlJdbcimpl implements JdbcInterface {@Overridepublic Object getConnection() {System.out.println("Mysql数据库连接到Java");return null;}@Overridepublic void crud() {System.out.println("Mysql增删改查");}@Overridepublic void close() {System.out.println("Mysql关闭连接");}}

OracleJdbcimpl 模拟Oracle厂商使用Java

package com.jdbc;/*** 模拟Oracle厂商使用Java** @author wty* @date /11/6 12:05*/public class OracleJdbcimpl implements JdbcInterface {@Overridepublic Object getConnection() {System.out.println("Oracle数据库连接到Java");return null;}@Overridepublic void crud() {System.out.println("Oracle增删改查");}@Overridepublic void close() {System.out.println("Oracle关闭连接");}}

JDBC带来的好处

JDBC API

JDBC入门

JDBC程序编写步骤

注册驱动 - 加载Driver类获取连接 - 得到Connection执行增删改查 - 发送SQL给mysql执行释放资源 - 关闭相关连接

JDBC的第一个程序

package com.jdbc.myjdbc;import com.mysql.jdbc.Driver;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;/*** @author wty* @date /11/6 14:48*/public class Jdbc01 {public static void main(String[] args) throws SQLException {// 前置工作，在项目下创建文件夹，把mysql.jar拷贝，并点击 Add as Library// 1.注册驱动 创建Driver对象Driver driver = new Driver();// 2.获取连接// (1)jdbc 协议// (2)localhost 表示主机或者IP地址// (3)3306表示mysql监听的端口// (4)db_02 表示哪个数据库// (5)mysql的连接的本质是socket连接String url = "jdbc:mysql://localhost:3306/db_02";// (6)将用户名和密码放入到PropertiesProperties properties = new Properties();// 用户名properties.setProperty("user", "root");// 密码properties.setProperty("password", "hsp");Connection connect = driver.connect(url, properties);// 3. 执行增删改查String sqlStr = "insert into actor values(null,'jack','男','1996-01-13','13713456612')";// 发送和执行sql语句Statement statement = connect.createStatement();// DML语句:受影响的行数int rows = statement.executeUpdate(sqlStr);System.out.println(rows > 0 ? "成功" : "失败");// 4. 释放资源statement.close();connect.close();}}

获取数据库连接的5种方式

方式1

/*** 方式1** @return void* @Param No such property: code for class: Script1* @Date /11/6 15:36* @Author wty**/@Testpublic void connection1() throws SQLException {String url = "jdbc:mysql://localhost:3306/db_02";Properties properties = new Properties();properties.setProperty("user", "root");properties.setProperty("password", "hsp");Driver driver = new Driver();Connection connect = driver.connect(url, properties);System.out.println(connect);// 输出: com.mysql.jdbc.JDBC4Connection@2d363fb3connect.close();}

方式2

/*** 使用反射加载Driver类** @return void* @Param No such property: code for class: Script1* @Date /11/6 15:41* @Author wty**/@Testpublic void connection2() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {// 动态加载更加灵活减少依赖:运行时加载需要的类，如果运行时不用该类，即使不存在该类，也不报错，降低了依赖。Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");Object o = aClass.newInstance();// 向下转型Driver driver = (Driver) o;// 剩下步骤一样String url = "jdbc:mysql://localhost:3306/db_02";Properties properties = new Properties();properties.setProperty("user", "root");properties.setProperty("password", "hsp");Connection connect2 = driver.connect(url, properties);System.out.println(connect2);// 输出: com.mysql.jdbc.JDBC4Connection@2d363fb3connect2.close();}

方式3

/**** 使用DriverManager替换Driver** @Param No such property: code for class: Script1* @return void* @Date /11/6 15:53* @Author wty**/@Testpublic void connection3() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");// 直接向下转型Driver driver = (Driver) aClass.newInstance();// 创建url 和 user 和 passwordString url = "jdbc:mysql://localhost:3306/db_02";String user = "root";String password = "hsp";// 注册Driver驱动,用DriverManager统一管理DriverManager.registerDriver(driver);Connection connection3 = DriverManager.getConnection(url, user, password);System.out.println(connection3);// com.mysql.jdbc.JDBC4Connection@7d6f77ccconnection3.close();}

方式4

/*** 推荐使用!!!!* 使用Class.forName自动完成注册驱动** @return void* @Param No such property: code for class: Script1* @Date /11/6 16:04* @Author wty**/@Testpublic void connection4() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {// 使用反射加载Driver类// 加载Driver类的时候会自动注册Class.forName("com.mysql.jdbc.Driver");// 上面这句话也可以去掉，因为类java.sql.Driver中已经加载/*** 源码分析** public class Driver extends NonRegisteringDriver implements java.sql.Driver {*public Driver() throws SQLException {*}*静态代码块:类加载会执行一次*static {* try {* // 这里已经注册了* DriverManager.registerDriver(new Driver());* } catch (SQLException var1) {* throw new RuntimeException("Can't register driver!");* }*}* }*/// 创建url 和 PropertiesString url = "jdbc:mysql://localhost:3306/db_02";Properties properties = new Properties();properties.setProperty("user", "root");properties.setProperty("password", "hsp");Connection connection4 = DriverManager.getConnection(url, properties);System.out.println(connection4);// com.mysql.jdbc.JDBC4Connection@2d363fb3connection4.close();}

方式5

配置文件

user=rootpassword=hspurl=jdbc:mysql://localhost:3306/db_02driver=com.mysql.jdbc.Driver

连接数据库

/*** 使用配置文件连接数据库** @return void* @Param No such property: code for class: Script1* @Date /11/6 16:14* @Author wty**/@Testpublic void connection5() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, IOException {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");Class.forName(driver);Connection connection5 = DriverManager.getConnection(url, user, password);System.out.println(connection5);// com.mysql.jdbc.JDBC4Connection@2d363fb3connection5.close();}

课后作业

加载类

package com.jdbc.HomeWork;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;/*** @author wty* @date /11/6 16:36*/public class HomeWork01 {public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {Properties properties = new Properties();properties.load(new FileInputStream("src/com/jdbc/HomeWork/res.properties"));String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, password);Statement statement = connection.createStatement();// String sqlStr = "insert into actor values" +//"(2,'tom','男','1996-01-01','13779669189')," +//"(3,'tony','男','1997-02-01','17779669189')," +//"(4,'Anna','女','1998-08-01','13179669189'),(5,'Linda','女','1999-09-01','13279669189');";// 修改id = 1的记录为我的名字//String sqlStr = "update actor set name = 'hsp' where id = 1;";// 删除id = 3的记录String sqlStr = "delete from actor where id = 3;";int rows = statement.executeUpdate(sqlStr);System.out.println(rows > 0 ? "成功" : "失败");statement.close();connection.close();}}

配置文件

url=jdbc:mysql://localhost:3306/db_02user=rootpassword=hspdriver=com.mysql.jdbc.Driver

ResultSet[结果集]

基本介绍

应用实例

package com.jdbc.resultset;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.*;import java.util.Properties;/*** 演示select 语句并返回ResultSet,并取出结果** @author wty* @date /11/6 16:58*/public class ResultSetExercise {public static void main(String[] args) 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");// 1. 注册驱动Class.forName(driver);// 2. 得到连接Connection connection = DriverManager.getConnection(url, user, password);// 3.得到StatementStatement statement = connection.createStatement();// 4. 组织sqlString sqlStr = "select id,name,sex,borndate,phone from actor;";// 5.返回结果集(类似于一张表),并用while取出ResultSet resultSet = statement.executeQuery(sqlStr);/*** +----+-------+-----+---------------------+-------------+* | id | name | sex | borndate | phone |* +----+-------+-----+---------------------+-------------+* | 1 | hsp | 男 | 1996-01-13 00:00:00 | 13713456612 |* | 2 | tom | 男 | 1996-01-01 00:00:00 | 13779669189 |* | 4 | Anna | 女 | 1998-08-01 00:00:00 | 13179669189 |* | 5 | Linda | 女 | 1999-09-01 00:00:00 | 13279669189 |* +----+-------+-----+---------------------+-------------+* 4 rows in set (0.00 sec)*/// resultSet.next()让光标向后移动，如果后面没有了，就是falsewhile (resultSet.next()) {// 获取第一列数据int id = resultSet.getInt(1);String name = resultSet.getString(2);String sex = resultSet.getString(3);Date borndate = resultSet.getDate(4);String phone = resultSet.getString(5);System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone + "\t");}// 6.关闭连接resultSet.close();statement.close();// com.mysql.jdbc.JDBC4Connection@2d363fb3connection.close();}}

Statement

基本介绍

SQL注入小案例(sqlyog操作)

CREATE TABLE admin (-- 管理员表NAME VARCHAR (32) NOT NULL UNIQUE,pwd VARCHAR (32) NOT NULL DEFAULT '') CHARACTER SET utf8 ;INSERT INTO adminVALUES('tom','123');SELECT * FROM admin aWHERE a.`NAME` = 'tom'AND a.`pwd` = '123';-- SQL注入-- 用户名 1' or-- 密码 or '1' = '1SELECT * FROM admin aWHERE a.`NAME` = '1' OR'AND a.`pwd` = 'OR '1' = '1';

SQL注入小案例(IDEA演示)

package com.jdbc.statement;import org.junit.Test;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.*;import java.util.Properties;import java.util.Scanner;/*** 演示sql注入** @author wty* @date /11/6 19:15*/public class StatementExercise {@Testpublic void statementExerciseTest() throws IOException, ClassNotFoundException, SQLException {System.out.println("请输入用户名");Scanner scanner = new Scanner(System.in);// nextLine允许单引号和空格String inUser = scanner.nextLine();System.out.println("请输入密码");String inPwd = scanner.nextLine();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");// 1. 注册驱动Class.forName(driver);// 2. 得到连接Connection connection = DriverManager.getConnection(url, user, password);// 3.得到StatementStatement statement = connection.createStatement();// 4.查询String sqlStr = "select name,pwd from admin where name = '" + inUser + "' and pwd = '" + inPwd + "'";ResultSet resultSet = statement.executeQuery(sqlStr);if (resultSet.next()) {// 如果查询到记录，说明用户存在System.out.println("恭喜登录成功!");} else {System.out.println("登录失败，没有该用户!");}// 关闭连接resultSet.close();statement.close();connection.close();}}

PreparedStatement

预处理的好处

查询query

package com.jdbc.preparedstatement;import org.junit.Test;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.*;import java.util.Properties;import java.util.Scanner;/*** PreparedStatement演示控制sql注入** @author wty* @date /11/6 19:35*/public class PrepareperedStatementExercise {@Testpublic void prepareperedStatementExerciseTest() throws IOException, ClassNotFoundException, SQLException {System.out.println("请输入用户名");Scanner scanner = new Scanner(System.in);// nextLine允许单引号和空格String inUser = scanner.nextLine();System.out.println("请输入密码");String inPwd = scanner.nextLine();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");// 1. 注册驱动Class.forName(driver);// 2. 得到连接Connection connection = DriverManager.getConnection(url, user, password);// 3.构建查询语句 , ? 相当于占位符String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";// 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);// 给问号赋值preparedStatement.setString(1, inUser);preparedStatement.setString(2, inPwd);ResultSet resultSet = preparedStatement.executeQuery();if (resultSet.next()) {// 如果查询到记录，说明用户存在System.out.println("恭喜登录成功!");} else {System.out.println("登录失败，没有该用户!");}// 关闭连接resultSet.close();preparedStatement.close();connection.close();}}

PreparedStatement DML语句

插入 Insert

package com.jdbc.preparedstatement;import org.junit.Test;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.*;import java.util.Properties;import java.util.Scanner;/*** PreparedStatement演示处理DML语句--插入** @author wty* @date /11/6 19:52*/public class PrepareperedStatementExercise02 {@Testpublic void PrepareperedStatementExercise02Test() throws SQLException, ClassNotFoundException, IOException {System.out.println("请输入要添加的用户名");Scanner scanner = new Scanner(System.in);// nextLine允许单引号和空格String inUser = scanner.nextLine();System.out.println("请输入要添加的密码");String inPwd = scanner.nextLine();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");// 1. 注册驱动Class.forName(driver);// 2. 得到连接Connection connection = DriverManager.getConnection(url, user, password);// 3.构建查询语句， ? 相当于占位符//String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";// 3.添加记录String sqlStr = "insert into admin values (?,?)";// 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);// 给问号赋值preparedStatement.setString(1, inUser);preparedStatement.setString(2, inPwd);int rows = preparedStatement.executeUpdate();System.out.println(rows > 0 ? "成功" : "失败");// 关闭连接preparedStatement.close();connection.close();}}

更新Update

package com.jdbc.preparedstatement;import org.junit.Test;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Properties;import java.util.Scanner;/*** PreparedStatement演示处理DML语句--更新* * @author wty* @date /11/6 20:00*/public class PrepareperedStatementExercise03 {@Testpublic void PrepareperedStatementExercise03Test() throws SQLException, ClassNotFoundException, IOException {System.out.println("请输入修改后的用户名");Scanner scanner = new Scanner(System.in);// nextLine允许单引号和空格String inUser = scanner.nextLine();System.out.println("请输入修改后的密码");String inPwd = scanner.nextLine();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");// 1. 注册驱动Class.forName(driver);// 2. 得到连接Connection connection = DriverManager.getConnection(url, user, password);// 3.构建查询语句， ? 相当于占位符//String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";// 3.添加记录String sqlStr = "update admin set name = ?,pwd = ? where name = 'admin'";// 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);// 给问号赋值preparedStatement.setString(1, inUser);preparedStatement.setString(2, inPwd);int rows = preparedStatement.executeUpdate();System.out.println(rows > 0 ? "成功" : "失败");// 关闭连接preparedStatement.close();connection.close();}}

删除Delete

package com.jdbc.preparedstatement;import org.junit.Test;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Properties;import java.util.Scanner;/*** PreparedStatement演示处理DML语句--删除** @author wty* @date /11/6 20:00*/public class PrepareperedStatementExercise04 {@Testpublic void PrepareperedStatementExercise04Test() throws SQLException, ClassNotFoundException, IOException {System.out.println("请输入需要删除的用户名");Scanner scanner = new Scanner(System.in);// nextLine允许单引号和空格String inUser = scanner.nextLine();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");// 1. 注册驱动Class.forName(driver);// 2. 得到连接Connection connection = DriverManager.getConnection(url, user, password);// 3.构建查询语句， ? 相当于占位符//String sqlStr = "select name,pwd from admin where name = ? and pwd = ?";// 3.添加记录String sqlStr = "delete from admin where name = ?";// 4.得到PreparedStatement,preparedStatement 是实现PreparedStatement接口的对象PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);// 给问号赋值preparedStatement.setString(1, inUser);int rows = preparedStatement.executeUpdate();System.out.println(rows > 0 ? "成功" : "失败");// 关闭连接preparedStatement.close();connection.close();}}

课堂练习

package com.jdbc.preparedstatement;import org.junit.Test;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.*;import java.util.Properties;import java.util.Scanner;/*** @author wty* @date /11/6 22:36*/public class HomeWork {@Testpublic void homeWorkTest() throws IOException, ClassNotFoundException, SQLException {// System.out.println("请输入需要的用户名");// Scanner scanner = new Scanner(System.in);// String inUser = scanner.nextLine();// System.out.println("请输入新的密码");// scanner = new Scanner(System.in);// String inPsd = scanner.nextLine();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");Class.forName(driver);Connection connection = DriverManager.getConnection(url, user, password);//String sqlStr = "insert into admin values (?,?)";String sqlStr = "select name, pwd from admin";PreparedStatement preparedStatement = connection.prepareStatement(sqlStr);//preparedStatement.setString(1, inUser);//preparedStatement.setString(2, inPsd);ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {String name = resultSet.getString(1);String pwd = resultSet.getString(2);System.out.println(name + "\t" + pwd + "\t");}preparedStatement.close();connection.close();}}

JDBC API

封装JDBCUtils 关闭连接得到连接

说明

工具类

package com.jdbc.utils;import java.io.FileInputStream;import java.io.IOException;import java.sql.*;import java.util.Properties;/*** JDBC工具包:完成mysql的连接和关闭资源** @author wty* @date /11/6 23:14*/public class JDBCUtils {// 因为只需要一份，所以做成静态的/*** 用户名*/private static String user;/*** 密码*/private static String password;/*** url*/private static String url;/*** driver*/private static String driver;// 在静态代码块初始化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");Class.forName(driver);} catch (IOException | ClassNotFoundException e) {// 在实际开发中，这样处理// 1.将编译异常转换成运行异常// 2.调用者可以选择捕获异常，或者默认处理throw new RuntimeException(e);}}/**** 连接数据库,返回Connection** @Param No such property: code for class: Script1* @return java.sql.Connection* @Date /11/6 23:30* @Author wty**/public static Connection getConnection() {try {return DriverManager.getConnection(url, user, password);} catch (SQLException e) {// 在实际开发中，这样处理// 1.将编译异常转换成运行异常// 2.调用者可以选择捕获异常，或者默认处理throw new RuntimeException(e);}}/**** 关闭相关资源** @Param * @param resultSet* @param statement* @param connection* @return void* @Date /11/6 23:38* @Author wty**/public static void close(ResultSet resultSet, Statement statement, Connection connection) {// 1. ResultSet 结果集// 2. Statement PreparedStatement// 3. Connection// 4. 如果需要关闭资源，就传入资源，否则就传入空try {if (resultSet != null) {resultSet.close();}if (statement != null) {statement.close();}if (connection != null) {connection.close();}} catch (SQLException e) {throw new RuntimeException(e);}}}

DML使用类

package com.jdbc.utils;import org.junit.Test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;/*** 该类演示如何使用JDBCUtils工具类** @author wty* @date /11/6 23:42*/public class JDBCUtilsUse {@Testpublic void testDML() {// 1. 得到连接Connection connection = null;// 2.组织一个sqlString sqlStr = "update admin set name = ? where name = ?";// 3.创建一个PreparedStatementPreparedStatement preparedStatement = null;try {connection = JDBCUtils.getConnection();preparedStatement = connection.prepareStatement(sqlStr);preparedStatement.setString(1, "周星驰");preparedStatement.setString(2, "king");preparedStatement.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {// 关闭资源JDBCUtils.close(null, preparedStatement, connection);}}}

DQL使用类

package com.jdbc.utils;import org.junit.Test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/*** @author wty* @date /11/7 9:28*/public class JDBCUtilsUsebf {@Testpublic void JDBCUtilsUsebfTest() {// 连接Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {connection = JDBCUtilsbf.getConnection();String sqlStr = "select name,pwd from admin";preparedStatement = connection.prepareStatement(sqlStr);resultSet = preparedStatement.executeQuery();while (resultSet.next()) {String name = resultSet.getString("name");String pwd = resultSet.getString("pwd");System.out.println(name + "\t" + pwd + "\t");}} catch (SQLException e) {e.printStackTrace();}// 关闭JDBCUtilsbf.close(connection, preparedStatement, resultSet);}}

事务

基本介绍

应用实例

模拟转账业务

没有事务控制出现数据不一致问题

执行代码前

没有事务控制代码如下:

package com.jdbc.transaction;import com.jdbc.utils.JDBCUtils;import org.junit.Test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;/*** 通过转账案例，模拟JDBC中的事务** @author wty* @date /11/7 9:55*/public class TransactionExercise {/*** 没有事务的情况** @return void* @Param * @param* @Date /11/7 9:56* @Author wty**/@Testpublic void noTransaction() {// 操作转账的业务// 1. 得到连接Connection connection = null;// 2.组织一个sqlString sqlStr = "update account set balance = balance - 100 where id = ?";String sqlStr2 = "update account set balance = balance + 100 where id = ?";// 3.创建一个PreparedStatementPreparedStatement preparedStatement = null;try {// connection在默认情况下是自动提交的connection = JDBCUtils.getConnection();preparedStatement = connection.prepareStatement(sqlStr);preparedStatement.setInt(1, 1);preparedStatement.executeUpdate();// 人为抛出异常int i = 1 / 0;preparedStatement = connection.prepareStatement(sqlStr2);preparedStatement.setInt(1, 2);preparedStatement.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {// 关闭资源JDBCUtils.close(null, preparedStatement, connection);}}}

执行代码抛出异常

数据不一致问题出现

马化腾的金额没有增加

事务控制后消除数据不一致问题

package com.jdbc.transaction;import com.jdbc.utils.JDBCUtils;import org.junit.Test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;/*** 通过转账案例，模拟JDBC中的事务** @author wty* @date /11/7 9:55*/public class TransactionExercise {/*** 有事务的情况** @return void* @Param * @param* @Date /11/7 9:56* @Author wty**/@Testpublic void haveTransaction() {// 操作转账的业务// 1. 得到连接Connection connection = null;// 2.组织一个sqlString sqlStr = "update account set balance = balance - 100 where id = ?";String sqlStr2 = "update account set balance = balance + 100 where id = ?";// 3.创建一个PreparedStatementPreparedStatement preparedStatement = null;try {connection = JDBCUtils.getConnection();// 连接后，设置为不自动提交，(回滚到这里)connection.setAutoCommit(false);preparedStatement = connection.prepareStatement(sqlStr);preparedStatement.setInt(1, 1);preparedStatement.executeUpdate();// 人为抛出异常//int i = 1 / 0;preparedStatement = connection.prepareStatement(sqlStr2);preparedStatement.setInt(1, 2);preparedStatement.executeUpdate();mit();System.out.println("执行成功，提交事务!");} catch (SQLException e) {try {System.out.println("执行发生错误，回滚事务!");// 默认回滚到事务开始的状态connection.rollback();} catch (SQLException ex) {ex.printStackTrace();}e.printStackTrace();} finally {// 关闭资源JDBCUtils.close(null, preparedStatement, connection);}}}

正常情况下马云金额减少，马化腾金额增加。

抛出异常情况下马云和马化腾的金额保持不变。

批处理

基本介绍

package com.jdbc.batch;import com.jdbc.utils.JDBCUtils;import org.junit.Test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;/*** @author wty* @date /11/7 10:31*/public class BatchExercise {/*** 普通方法逐条处理** @return void* @Param * @param* @Date /11/7 10:31* @Author wty**/@Testpublic void noBatch() {// 获取连接Connection connection = null;PreparedStatement preparedStatement = null;try {// 执行sqlconnection = JDBCUtils.getConnection();// 设置不自动提交事务connection.setAutoCommit(false);String sqlStr = "insert into admin2 values (null,?,?)";preparedStatement = connection.prepareStatement(sqlStr);System.out.println("开始执行");long begin = System.currentTimeMillis();for (int i = 1; i <= 5000; i++) {preparedStatement.setString(1, "tom" + i);preparedStatement.setString(2, "123");preparedStatement.executeUpdate();}long end = System.currentTimeMillis();System.out.println("总时长: " + (end - begin));/*** 开始执行* 总时长: 603*/mit();} catch (SQLException e) {try {connection.rollback();} catch (SQLException ex) {ex.printStackTrace();}e.printStackTrace();}// 关闭连接JDBCUtils.close(null, preparedStatement, connection);}/*** 批处理** @return void* @Param * @param* @Date /11/7 10:31* @Author wty**/@Testpublic void haveBatch() {// 获取连接Connection connection = null;PreparedStatement preparedStatement = null;try {// 执行sqlconnection = JDBCUtils.getConnection();// 设置不自动提交事务connection.setAutoCommit(false);String sqlStr = "insert into admin2 values (null,?,?)";preparedStatement = connection.prepareStatement(sqlStr);System.out.println("开始执行");long begin = System.currentTimeMillis();for (int i = 1; i <= 5000; i++) {preparedStatement.setString(1, "tom" + i);preparedStatement.setString(2, "123");// 将sql语句加入Batch 批处理包preparedStatement.addBatch();/**源码分析* 1. 第一次创建ArrayList -对象数组 protected List<Object> batchedArgs;* 2. elementData 即 Object[] 就会存放sql语句* 3. 当elementData满后，就按照1.5倍扩容(ArrayList扩容规则)* 4. 当添加到指定的值后，就executeBatch* 5. 批处理会减少我们发送sql语句的网络开销，而且减少编译次数，因此效率提高**public void addBatch() throws SQLException {* synchronized(this.checkClosed().getConnectionMutex()) {* if (this.batchedArgs == null) {* // 这里看底层是 ArrayList* this.batchedArgs = new ArrayList();* }** this.batchedArgs.add(new ServerPreparedStatement.BatchedBindValues(this.parameterBindings));* }*}*/// 当有1000条数据的时候执行一次if (i % 1000 == 0) {preparedStatement.executeBatch();// 清空一次preparedStatement.clearBatch();}}long end = System.currentTimeMillis();System.out.println("批处理总时长: " + (end - begin));/*** 开始执行* 批处理总时长: 101*/mit();} catch (SQLException e) {try {connection.rollback();} catch (SQLException ex) {ex.printStackTrace();}e.printStackTrace();}// 关闭连接JDBCUtils.close(null, preparedStatement, connection);}}

数据库连接池

5000次连接数据库问题

package com.jdbc.datasource;import com.jdbc.utils.JDBCUtils;import org.junit.Test;import java.sql.Connection;import java.sql.SQLException;/*** @author wty* @date /11/7 11:26*/public class ConQuestion {/*** 传统方式连接数据库5000次** @return* @Param * @param null* @Date /11/7 11:26* @Author wty**/@Testpublic void ConQuestionTest() throws SQLException {long begin = System.currentTimeMillis();for (int i = 1; i <= 5000; i++) {// 获取连接Connection connection = JDBCUtils.getConnection();// 不关闭连接/*** 抛出异常** java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from* server: "Too many connections"*/JDBCUtils.close(null, null, connection);}long end = System.currentTimeMillis();System.out.println("共用时:" + (end - begin));/*** 共用时:10439*/}}

传统方式获取Connection问题分析

数据库连接池的基本介绍

数据库连接池类型

C3P0应用实例

package com.jdbc.datasource;import com.mchange.boPooledDataSource;import org.junit.Test;import java.beans.PropertyVetoException;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;/*** 演示C3P0的使用** @author wty* @date /11/7 14:19*/public class C3P0Use {/*** C3P0的使用方式1:相关参数，在程序中指定user,url,password** @return void* @Param * @param* @Date /11/7 14:20* @Author wty**/@Testpublic void C3P0Use01() throws IOException, PropertyVetoException, SQLException {// 1.创建一个数据源对象ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();// 2.通过配置文件获取相关信息Properties properties = new Properties();properties.load(new FileInputStream("src/mysql.properties"));String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");String driver = properties.getProperty("driver");// 3. 给数据源设置相关的参数// 连接管理由数据源对象掌控comboPooledDataSource.setDriverClass(driver);comboPooledDataSource.setJdbcUrl(url);comboPooledDataSource.setUser(user);comboPooledDataSource.setPassword(password);// 设置数据源的连接数(初始化连接数)comboPooledDataSource.setInitialPoolSize(10);// 最大连接数：超过了就进入等待队列中comboPooledDataSource.setMaxPoolSize(50);// 测试调用5000次的效率long begin = System.currentTimeMillis();for (int i = 1; i <= 5000; i++) {// 核心方法，从DataSource接口实现Connection connection = comboPooledDataSource.getConnection();//System.out.println("连接成功");connection.close();}long end = System.currentTimeMillis();System.out.println("共用时:" + (end - begin));/*** 共用时:654*/}/*** C3P0的使用方式2:* <p>* 1.将c3p0提供的配置文件c3p0-config.xml拷贝到src目录下* 2.该文件指定了连接池和数据库的相关参数** @return void* @Param * @param* @Date /11/7 14:20* @Author wty**/@Testpublic void C3P0Use02() throws IOException, PropertyVetoException, SQLException {// 1.创建一个数据源对象(把数据源名称写入括号内)ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("wty");// 测试调用5000次的效率long begin = System.currentTimeMillis();for (int i = 1; i <= 5000; i++) {// 核心方法，从DataSource接口实现Connection connection = comboPooledDataSource.getConnection();//System.out.println("连接成功");connection.close();}long end = System.currentTimeMillis();System.out.println("共用时:" + (end - begin));/*** 共用时:657*/}}

Druid(德鲁伊)应用实例

package com.jdbc.datasource;import com.alibaba.druid.pool.DruidDataSourceFactory;import jdk.nashorn.internal.ir.CallNode;import org.junit.Test;import javax.sql.DataSource;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.util.Properties;/*** Druid德鲁伊的使用** @author wty* @date /11/7 15:04*/public class DruidUse {/*** Druid的使用:** @return void* @Param * @param* @Date /11/7 15:07* @Author wty**/@Testpublic void DruidUseTest() throws Exception {//1.加入 Druid jar 包//2.加入 配置文件 druid.properties//3.创建Properties对象用来获取信息Properties properties = new Properties();properties.load(new FileInputStream("src/druid.properties"));// 4.创建一个指定参数的数据库连接池,Druid连接池DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);long begin = System.currentTimeMillis();for (int i = 1; i <= 5000; i++) {Connection connection = dataSource.getConnection();//System.out.println("连接成功");connection.close();}long end = System.currentTimeMillis();System.out.println("共用时:" + (end - begin));/*** 共用时:592*/}}

将JDBCUtils更改成Druid实现

DruidUtils

package com.HomeWork.utils;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;import java.io.FileInputStream;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;/*** @author wty* @date /11/7 15:30*/public class DruidUtils {private static DataSource dataSource = null;static {Properties properties = new Properties();try {properties.load(new FileInputStream("src/druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {throw new RuntimeException(e);}}public static Connection getConnection() {Connection connection = null;try {connection = dataSource.getConnection();} catch (SQLException e) {throw new RuntimeException(e);}return connection;}/*** 数据库连接池技术中：close方法不是真正断掉连接，而是把使用的Connection对象放回连接池** @param statement* @param connection* @return void* @Param * @param resultSet* @Date /11/7 15:43* @Author wty**/public static void close(ResultSet resultSet, Statement statement, Connection connection) {try {if (null != resultSet) {resultSet.close();}if (null != statement) {statement.close();}if (null != connection) {connection.close();}} catch (SQLException e) {throw new RuntimeException(e);}}}

测试类:

package com.jdbc.utils;import org.junit.Test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/*** DruidUtils的使用** @author wty* @date /11/7 15:47*/public class DruidUtilsUse {/*** DruidUtils的使用--DML语句：批量插入** @return void* @Param * @param* @Date /11/7 16:19* @Author wty**/@Testpublic void DruidUtilsUseTest01() {// 连接Connection connection = null;PreparedStatement preparedStatement = null;try {connection = DruidUtils.getConnection();connection.setAutoCommit(false);String sqlStr = "insert into admin2 values (null,?,?)";long begin = System.currentTimeMillis();// 这里一定要放在for循环外面preparedStatement = connection.prepareStatement(sqlStr);for (int i = 1; i <= 5000; i++) {preparedStatement.setString(1, "tom" + i);preparedStatement.setString(2, "123");preparedStatement.addBatch();if (i % 1000 == 0) {preparedStatement.executeBatch();preparedStatement.clearBatch();}}long end = System.currentTimeMillis();System.out.println("共用时:" + (end - begin));System.out.println("connection的运行类型是；" + connection.getClass());mit();} catch (SQLException e) {try {connection.rollback();} catch (SQLException ex) {ex.printStackTrace();}e.printStackTrace();}// 断开连接池DruidUtils.close(null, preparedStatement, connection);}/*** DruidUtils的使用--DQL语句** @return void* @Param * @param* @Date /11/7 16:19* @Author wty**/@Testpublic void DruidUtilsUseTest02() {// 连接Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {connection = DruidUtils.getConnection();String sqlStr = "select id,username,password from admin2";// 这里一定要放在for循环外面preparedStatement = connection.prepareStatement(sqlStr);resultSet = preparedStatement.executeQuery();while (resultSet.next()) {int id = resultSet.getInt("id");String username = resultSet.getString("username");String password = resultSet.getString("password");System.out.println(id + "\t" + username + "\t" + password + "\t");}System.out.println("connection的运行类型是；" + connection.getClass());} catch (SQLException e) {e.printStackTrace();}// 断开连接池DruidUtils.close(resultSet, preparedStatement, connection);}}

运行结果:表明Druid中，close()方法的运行类型是com.alibaba.druid.pool.DruidPooledConnection，并非是直接断开数据库连接，而是把使用的Connection对象放回连接池

Apache—DBUtils

分析一个问题

用自己的土方法模拟DBUtils

Actor类

package com.jdbc.dbutils;import java.util.Date;/*** Actor类 和表db_02.actor每个字段相对应** @author wty* @date /11/7 18:42*/public class Actor {private Integer id;private String name;private String sex;private String phone;private Date borndate;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 String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public Date getBorndate() {return borndate;}public void setBorndate(Date borndate) {this.borndate = borndate;}public Actor(Integer id, String name, String sex, Date borndate, String phone) {this.id = id;this.name = name;this.sex = sex;this.phone = phone;this.borndate = borndate;}/**** 一定要给一个无参构造器用于反射** @Param * @param* @return* @Date /11/7 18:57* @Author wty**/public Actor() {}@Overridepublic String toString() {return "Actor{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", phone='" + phone + '\'' +", borndate=" + borndate +'}';}}

土办法模拟的类

package com.jdbc.dbutils;import com.jdbc.utils.DruidUtils;import org.junit.Test;import java.io.FileInputStream;import java.io.IOException;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.Date;import java.util.Properties;/*** 土方法实现底层DBUtils工具类** @author wty* @date /11/7 18:41*/public class DBUtilsExercise {@Testpublic ArrayList DBUtilsExerciseTest() {// 连接Connection connection = null;// 组装sqlProperties properties = new Properties();ResultSet resultSet = null;PreparedStatement preparedStatement = null;// 把resultSet1保存到集合里ArrayList<Actor> actors = new ArrayList<>();try {connection = DruidUtils.getConnection();properties.load(new FileInputStream("src/druid.properties"));String sqlStr = "select id,name,sex,borndate,phone from actor";preparedStatement = connection.prepareStatement(sqlStr);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");// 把resultSet集合元素放入到actors中actors.add(new Actor(id, name, sex, borndate, phone));}for (Actor actor : actors) {System.out.println(actor);}} catch (IOException | SQLException e) {e.printStackTrace();} finally {// 关闭连接DruidUtils.close(resultSet, preparedStatement, connection);}// 因为ArrayList 和connection无关联，所以该集合可以复用return actors;}}

DBUtils基本介绍

应用实例

package com.jdbc.dbutils;import com.jdbc.utils.DruidUtils;import mons.dbutils.DbUtils;import mons.dbutils.QueryRunner;import mons.dbutils.handlers.BeanListHandler;import org.junit.Test;import java.sql.Connection;import java.sql.SQLException;import java.util.List;/*** DBUtils工具类的使用--CRUD** @author wty* @date /11/7 19:11*/public class DBUtilsUse {@SuppressWarnings({"all"})@Testpublic void DBUtilsUseTest() throws SQLException {// 1. 得到连接Connection connection = DruidUtils.getConnection();// 2. 使用DBUtils 类和接口，先引入DBUtils相关的jar包，加入project// 3. 创建QueryRunnerQueryRunner queryRunner = new QueryRunner();String sqlStr = "select id,name,sex,borndate,phone from actor where id <= ?";// 注意:sql也可以查询部分列// 老韩解读// (1)query 方法就是执行sql语句并返回得到ResultSet,并且封装到ArrayList中// (2)返回集合// (3)connection 连接// (4)sqlStr sql语句// (5)new BeanListHandler<>(Actor.class) 在将ResultSet取出到Actor对象里，封装到ArrayList中// (6)4是sql语句中的?赋值 可以有多个// (7)queryRunner.query()方法底层会关闭结果集resultSet、PreparedStatementList<Actor> list = queryRunner.query(connection, sqlStr, new BeanListHandler<>(Actor.class), 4);// 底层使用反射机制 获取Actor类中的属性,然后进行封装/*** // 底层源码*public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {* PreparedStatement stmt = null;* // ResultSet查询sql的结果集* ResultSet rs = null;* // 返回的ArrayList* Object result = null;** try {* stmt = this.prepareStatement(conn, sql);* // sql语句中？的填充赋值* this.fillStatement(stmt, params);* // 执行sql,获取查询结果放到ResultSet* rs = this.wrap(stmt.executeQuery());* // 从Actor中获取对象封装到Arraylist中，用到反射* result = rsh.handle(rs);* } catch (SQLException var33) {* this.rethrow(var33, sql, params);* } finally {* try {* // 这里关闭了结果集resultSet* this.close(rs);* } finally {* // 这里关闭了结果集statement* this.close((Statement)stmt);* }* }** return result;*}*/for (Actor actor : list) {System.out.println(actor);}// 释放资源,关闭对象DruidUtils.close(null, null, connection);}}

演示 apache-dbutils + druid 完成 返回的结果是单行记录(单个对象)

/*** 演示 apache-dbutils + druid 完成 返回的结果是单行多列记录(单个对象)** @author wty* @date /11/7 20:00*/public class DBUtilsUseSingleObject {@Testpublic void DBUtilsUseSingleObjectTest01() {// 获取连接Connection connection = DruidUtils.getConnection();// 组装sqlString sqlStr = "select * from actor where id = ?";QueryRunner queryRunner = new QueryRunner();try {// 因为返回的单个对象，就是单条记录，就不是集合了，用BeanHandlerActor actor = queryRunner.query(connection, sqlStr, new BeanHandler<>(Actor.class), 1);System.out.println(actor);} catch (SQLException e) {e.printStackTrace();} finally {// 关闭连接DruidUtils.close(null, null, connection);}}

演示 apache-dbutils + druid 返回的结果是单行单列(Object)

/*** 演示 apache-dbutils + druid 完成 返回的结果是单行单列** @author wty* @date /11/7 20:00*/@Testpublic void DBUtilsUseSingleObjectTestScalar() {// 获取连接Connection connection = DruidUtils.getConnection();// 组装sqlString sqlStr = "select name from actor where id = ? ";QueryRunner queryRunner = new QueryRunner();try {// 因为返回的单个对象，就是单行单列记录，就不是集合了，用BeanHandlerObject query = queryRunner.query(connection, sqlStr, new ScalarHandler(), 1);System.out.println(query);} catch (SQLException e) {e.printStackTrace();} finally {// 关闭连接DruidUtils.close(null, null, connection);}}

演示修改Update

/*** 演示 apache-dbutils + druid 完成 Update语句** @author wty* @date /11/7 20:00*/@Testpublic void DBUtilsUseSingleObjectTestUpdate() {// 获取连接Connection connection = DruidUtils.getConnection();// 组装sql 完成 修改String sqlStr = "update actor set name = ? where id = ?";QueryRunner queryRunner = new QueryRunner();try {// 返回的值是受影响的行数// queryRunner.update()可以用于增删改DML语句int rows = queryRunner.update(connection, sqlStr, "tommy", 2);System.out.println(rows > 0 ? "成功" : "没有影响");} catch (SQLException e) {e.printStackTrace();} finally {// 关闭连接DruidUtils.close(null, null, connection);}}

演示插入Insert

/*** 演示 apache-dbutils + druid 完成 insert语句** @author wty* @date /11/7 20:00*/@Testpublic void DBUtilsUseSingleObjectTestInsert() {// 获取连接Connection connection = DruidUtils.getConnection();// 组装sql 完成 修改String sqlStr = "insert into actor values (null,?,?,?,?)";QueryRunner queryRunner = new QueryRunner();try {int rows = queryRunner.update(connection, sqlStr, "Lily", "女", "1998-03-04", "17312113421");System.out.println(rows > 0 ? "成功" : "未删除");} catch (SQLException e) {e.printStackTrace();} finally {// 关闭连接DruidUtils.close(null, null, connection);}}

演示删除Delete

/*** 演示 apache-dbutils + druid 完成 Delete语句** @author wty* @date /11/7 20:00*/@Testpublic void DBUtilsUseSingleObjectTestDelete() {// 获取连接Connection connection = DruidUtils.getConnection();// 组装sql 完成 修改String sqlStr = "delete from actor where id = ?";QueryRunner queryRunner = new QueryRunner();try {int rows = queryRunner.update(connection, sqlStr, 4);System.out.println(rows > 0 ? "成功" : "未删除");} catch (SQLException e) {e.printStackTrace();} finally {// 关闭连接DruidUtils.close(null, null, connection);}}

表和 JavaBean 的类型映射关系

DAO 和增删改查通用方法-BasicDao

分析一个问题

基本说明

BasicDAO

package com.dao.dao;import com.dao.utils.DruidUtils;import mons.dbutils.QueryRunner;import mons.dbutils.handlers.BeanHandler;import mons.dbutils.handlers.BeanListHandler;import mons.dbutils.handlers.ScalarHandler;import java.sql.Connection;import java.sql.SQLException;import java.util.List;/*** 开发 BasicDAO 是其他DAO的父类* <T>泛型指定具体类型** @author wty* @date /11/7 23:37*/@SuppressWarnings({"all"})public class BasicDAO<T> {private QueryRunner queryRunner = new QueryRunner();/*** 开发通用的DML方法** @param params 这种写法可以参考源码 queryRunner.query()* @param sqlStr* @return int* @Date /11/7 23:43* @Author wty**/public int dml(String sqlStr, Object... params) {Connection connection = null;int affectedrows = 0;try {connection = DruidUtils.getConnection();affectedrows = queryRunner.update(connection, sqlStr, params);} catch (SQLException e) {throw new RuntimeException(e);} finally {DruidUtils.close(null, null, connection);}return affectedrows;}/*** 返回多个对象(即查询的结果是多行)，针对任意表** @return java.util.List<T> 返回对应的ArrayList 集合* @param: sqlStr sql语句可以有?* @param: clazz 传入一个类的Class对象，比如 Actor.class* @param: params 传入?具体的值，可以是多个* @date /11/8 0:04* @author wty**/public List<T> queryMulti(String sqlStr, Class<T> clazz, Object... params) {Connection connection = null;List<T> list = null;try {connection = DruidUtils.getConnection();list = queryRunner.query(connection, sqlStr, new BeanListHandler<T>(clazz), params);} catch (Exception e) {e.printStackTrace();} finally {DruidUtils.close(null, null, connection);}return list;}/*** 返回单行多列数据** @return T* @param: sqlStr* @param: clazz* @param: params* @date /11/8 0:19* @author wty**/public T querySingle(String sqlStr, Class<T> clazz, Object... params) {Connection connection = null;T t = null;try {connection = DruidUtils.getConnection();t = queryRunner.query(connection, sqlStr, new BeanHandler<T>(clazz), params);} catch (Exception e) {e.printStackTrace();} finally {DruidUtils.close(null, null, connection);}return t;}/*** 返回单行单列** @return T* @param: sqlStr* @param: clazz* @param: params* @date /11/8 0:22* @author wty**/public Object queryScalar(String sqlStr, Object... params) {Connection connection = null;Object o = null;try {connection = DruidUtils.getConnection();o = queryRunner.query(connection, sqlStr, new ScalarHandler(), params);} catch (Exception e) {e.printStackTrace();} finally {DruidUtils.close(null, null, connection);}return o;}}

ActorDao

package com.dao.dao;import com.dao.domain.Actor;/*** @author wty* @date /11/8 0:27*/public class ActorDao extends BasicDAO<Actor> {// 1. 就有BasicDAO所有的方法// 2. 根据业务需求可以编写特有的方法}

Actor

package com.dao.domain;import java.util.Date;/*** Actor类 和表db_02.actor每个字段相对应** @author wty* @date /11/7 18:42*/public class Actor {private Integer id;private String name;private String sex;private String phone;private Date borndate;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 String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public Date getBorndate() {return borndate;}public void setBorndate(Date borndate) {this.borndate = borndate;}public Actor(Integer id, String name, String sex, Date borndate, String phone) {this.id = id;this.name = name;this.sex = sex;this.phone = phone;this.borndate = borndate;}/**** 一定要给一个无参构造器用于反射** @Param * @param* @return* @Date /11/7 18:57* @Author wty**/public Actor() {}@Overridepublic String toString() {return "Actor{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", phone='" + phone + '\'' +", borndate=" + borndate +'}';}}

TestDao

package com.dao.test;import com.dao.dao.ActorDao;import com.dao.domain.Actor;import org.junit.Test;import java.util.List;/*** 测试ActorDao对Actor表的CRUD操作** @author wty* @date /11/8 0:29*/public class TestDao {@Testpublic void testDao() {ActorDao actorDao = new ActorDao();// 1.测试查询语句 多行记录String sqlStr = "SELECT * FROM actor where id >= ?";List<Actor> actors = actorDao.queryMulti(sqlStr, Actor.class, 1);System.out.println("多行多列查询结果：");for (Actor actor : actors) {System.out.println(actor);}// 2.测试查询语句 单行记录sqlStr = "SELECT * FROM actor where id = ?";Actor actor = actorDao.querySingle(sqlStr, Actor.class, 1);System.out.println("单行多列查询结果是:");System.out.println(actor);// 3.测试查询语句 单行单列sqlStr = "select name from actor where id = ?";Object o = actorDao.queryScalar(sqlStr, 5);System.out.println("单行单列查询结果是:");System.out.println(o);// 4.测试DML语句// 修改sqlStr = "update actor set name = ? where id = ?";int affectedrows = actorDao.dml(sqlStr, "Lili", 6);System.out.println(affectedrows > 0 ? "修改成功" : "没有受影响的行");// 新增sqlStr = "insert into actor values(null,?,?,?,?)";int dml_insert = actorDao.dml(sqlStr, "sam", "男", "1991-03-04", "13167541122");System.out.println(dml_insert > 0 ? "添加成功" : "没有受影响的行");// 删除sqlStr = "delete from actor where id = ?";int dml_delete = actorDao.dml(sqlStr, 2);System.out.println(dml_delete > 0 ? "删除成功" : "没有受影响的行");}}

课后练习

BasicDao

package com.HomeWork.dao;import com.HomeWork.utils.DruidUtils;import mons.dbutils.QueryRunner;import mons.dbutils.handlers.BeanHandler;import mons.dbutils.handlers.BeanListHandler;import mons.dbutils.handlers.ScalarHandler;import java.sql.Connection;import java.util.List;/*** @author wty* @date /11/8 9:55*/public class BasicDao<T> {/*** dml语句** @param* @return int* @param: sqlStr* @param: params* @date /11/8 9:59* @author wty**/public int dml(String sqlStr, Object... params) {Connection connection = null;QueryRunner queryRunner = new QueryRunner();int affectedrows = 0;try {// 连接connection = DruidUtils.getConnection();// 组装sqlaffectedrows = queryRunner.update(connection, sqlStr, params);} catch (Exception e) {throw new RuntimeException(e);} finally {DruidUtils.close(null, null, connection);}return affectedrows;}/*** 多行多列查询** @param* @return java.util.List<T>* @param: sqlStr* @param: params* @date /11/8 10:01* @author wty**/public List<T> mutilResult(String sqlStr, Class<T> clazz, Object... params) {Connection connection = null;QueryRunner queryRunner = new QueryRunner();List<T> list = null;try {// 连接connection = DruidUtils.getConnection();// 组装sqllist = queryRunner.query(connection, sqlStr, new BeanListHandler<>(clazz), params);} catch (Exception e) {throw new RuntimeException(e);} finally {DruidUtils.close(null, null, connection);}return list;}/*** 返回单行多列** @param* @return T* @param: sqlStr* @param: clazz* @param: params* @date /11/8 10:04* @author wty**/public T SingleResult(String sqlStr, Class<T> clazz, Object... params) {Connection connection = null;QueryRunner queryRunner = new QueryRunner();T t = null;try {// 连接connection = DruidUtils.getConnection();// 组装sqlt = queryRunner.query(connection, sqlStr, new BeanHandler<>(clazz), params);} catch (Exception e) {throw new RuntimeException(e);} finally {DruidUtils.close(null, null, connection);}return t;}/*** 返回单行单列** @param* @return java.lang.Object* @param: sqlStr* @param: clazz* @param: params* @date /11/8 10:06* @author wty**/public Object SalarResult(String sqlStr, Object... params) {Connection connection = null;QueryRunner queryRunner = new QueryRunner();Object o = null;try {// 连接connection = DruidUtils.getConnection();// 组装sqlo = queryRunner.query(connection, sqlStr, new ScalarHandler(), params);} catch (Exception e) {throw new RuntimeException(e);} finally {DruidUtils.close(null, null, connection);}return o;}}

GoodsDao

package com.HomeWork.dao;import com.HomeWork.domain.Goods;import com.dao.dao.BasicDAO;import org.junit.Test;import java.util.List;/*** @author wty* @date /11/8 10:07*/public class GoodsDao extends BasicDAO<Goods> {}

Goods

package com.HomeWork.domain;/*** @author wty* @date /11/8 9:53*/public class Goods {private Integer id;private String goods_name;private Double price;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getGoods_name() {return goods_name;}public void setGoods_name(String goods_name) {this.goods_name = goods_name;}public Double getPrice() {return price;}public void setPrice(Double price) {this.price = price;}@Overridepublic String toString() {return "Goods{" +"id=" + id +", goods_name='" + goods_name + '\'' +", price=" + price +'}';}public Goods(Integer id, String goods_name, Double price) {this.id = id;this.goods_name = goods_name;this.price = price;}public Goods() {}}

GoodsTest

package com.HomeWork.test;import com.HomeWork.dao.GoodsDao;import com.HomeWork.domain.Goods;import org.junit.Test;import java.util.List;/*** @author wty* @date /11/8 10:27*/public class GoodsTest {@Testpublic void testInsert() {GoodsDao goodsDao = new GoodsDao();String sqlStr = "insert into goods values (?,?,?)";int rows = goodsDao.dml(sqlStr, 1006, "喜之郎", 32.99);System.out.println(rows > 0 ? "插入成功" : "没有收影响的结果集");}@Testpublic void testUpdate() {GoodsDao goodsDao = new GoodsDao();String sqlStr = "update goods set price = ? where id = ?";int rows = goodsDao.dml(sqlStr, 3.5, 1004);System.out.println(rows > 0 ? "更新成功" : "没有收影响的结果集");}@Testpublic void testDelete() {GoodsDao goodsDao = new GoodsDao();String sqlStr = "delete from goods where id = ?";int rows = goodsDao.dml(sqlStr, 1005);System.out.println(rows > 0 ? "删除成功" : "没有收影响的结果集");}@Testpublic void multiSelect() {GoodsDao goodsDao = new GoodsDao();String sqlStr = "select id,goods_name,price from goods";List<Goods> list = goodsDao.queryMulti(sqlStr, Goods.class);for (Goods goods : list) {System.out.println(goods);}}@Testpublic void SingleSelect() {GoodsDao goodsDao = new GoodsDao();String sqlStr = "select id,goods_name,price from goods where id = ?";Goods goods = goodsDao.querySingle(sqlStr, Goods.class, 1001);System.out.println(goods);}@Testpublic void SalarSelect() {GoodsDao goodsDao = new GoodsDao();String sqlStr = "select goods_name from goods where id = ?";Object o = goodsDao.queryScalar(sqlStr, 1001);System.out.println(o);}}