一、快速入门
（1）准备工作：
下载 mysql-connector-java-8.0.30.jar包，项目下新建libs目录将其放入右键找到add as library 结束如下图便成功了
（2）测试用例：
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) throws Exception {
//1.注册驱动
Driver driver = new Driver(); //注意导入的包名
//2.得到连接
//(1)jdbc:mysql:// 规定好的表示协议，通过jdbc的方式连接数据库mysql
//(2)localhost //表示主机的id地址
//(3)3306端口
//(4)表示链接那个数据库
String url = "jdbc:mysql://localhost:3306/jdbc";
//将 用户名与密码放入到propeties 对象
Properties properties = new Properties();
// user password 是固定的key值
properties.setProperty("user", "root"); //用户
properties.setProperty("password", "root"); //密码
Connection connect = driver.connect(url, properties);
//3.执行sql
String sql = "insert into actor values(null,'刘德华','男','1970-11-11','110')";
//statement 用于执行静态sql语句 并返回结果对象
Statement statement = connect.createStatement();
int rows = statement.executeUpdate(sql); //如果是dml返回的是印象行数
System.out.println(rows > 0 ? "成功" : "失败");
//4.关闭资源
statement.close();
connect.close();
}
}二、连接方式
// import com.mysql.cj.jdbc.Driver;
@Test //方式一
public void connect1() throws Exception {
Driver driver = new Driver(); //注意导入的包名
String url = "jdbc:mysql://localhost:3306/jdbc";
Properties properties = new Properties();
properties.setProperty("user", "root"); //用户
properties.setProperty("password", "root"); //密码
Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
@Test //方式二：使用反射
public void connect2() throws Exception {
Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver =(Driver)aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/jdbc";
Properties properties = new Properties();
properties.setProperty("user", "root"); //用户
properties.setProperty("password", "root"); //密码
Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
@Test //方式三：使用DriverManager 代替Driver 进行统一管理
public void connect3() throws Exception {
Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver =(Driver)aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "root";
DriverManager.registerDriver(driver); //注册Driver驱动
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
@Test //方式四：使用DriverManager 代替Driver 进行统一管理
public void connect4() throws Exception {
// Class.forName("com.mysql.cj.jdbc.Driver"); //这句话可以不写但建议写上，因为它会自动加载
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
@Test //方式五：通过properites获取配置信息(推荐使用)
/**
* 在src下新建 mysql.properties文件里面写入：
* url=jdbc:mysql://localhost:3306/jdbc
* user=root
* password=root
* driver=com.mysql.cj.jdbc.Driver
*/
public void connect5() throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("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);
System.out.println(connection);
}三、ResultSet 底层
（1）使用ResultSet查询数据：
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
//1.注册驱动
Class.forName(driver); //可以不写建议写上
//2.得到链接
Connection connection = DriverManager.getConnection(url, user, password);
//3.得到statement
Statement statement = connection.createStatement();
//4.组织sql
String sql = "select * from actor";
ResultSet resultSet = statement.executeQuery(sql);
System.out.println(resultSet);
//5.使用while取出数据
/**
* +----+--------+-----+------------+-------+
* | id | name | sex | date | phone |
* +----+--------+-----+------------+-------+
* | 1 | 刘德华 | 男 | 1970-11-11 | 110 |
* | 2 | 刘德华 | 男 | 1970-11-11 | 110 |
*/
while (resultSet.next()) {//让光标向后移动，如果没有更多行，则返回false
int id = resultSet.getInt(1);//获取该行的第一列
String name = resultSet.getString(2);//获取该行的第二列
String sex = resultSet.getString(3);//获取该行的第三列
String date = resultSet.getString(4);//获取该行的第四列
String phone = resultSet.getString(5);//获取该行的第五列
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date + "\t" + phone);
}
//6关闭连接
connection.close();
statement.close();
}
}四、sql注入（了解即可）不能用：恶意攻击 Statement一般不用，一般使用他们子类
五、预处理
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
//1.注册驱动
Class.forName(driver); //可以不写建议写上
//2.得到链接
Connection connection = DriverManager.getConnection(url, user, password);
//3.得到statement
Statement statement = connection.createStatement();
//4.组织sql
String sql = "select * from actor where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"1"); //预处理把 第一个问号改成 1
ResultSet resultSet = preparedStatement.executeQuery();
//5.使用while取出数据
/**
* +----+--------+-----+------------+-------+
* | id | name | sex | date | phone |
* +----+--------+-----+------------+-------+
* | 1 | 刘德华 | 男 | 1970-11-11 | 110 |
* | 2 | 刘德华 | 男 | 1970-11-11 | 110 |
*/
while (resultSet.next()) {//让光标向后移动，如果没有更多行，则返回false
int id = resultSet.getInt(1);//获取该行的第一列
String name = resultSet.getString(2);//获取该行的第二列
String sex = resultSet.getString(3);//获取该行的第三列
String date = resultSet.getString(4);//获取该行的第四列
String phone = resultSet.getString(5);//获取该行的第五列
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date + "\t" + phone);
}
//6关闭连接
connection.close();
statement.close();
}
}六、预处理DML
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
//1.注册驱动
Class.forName(driver); //可以不写建议写上
//2.得到链接
Connection connection = DriverManager.getConnection(url, user, password);
//3.1 添加记录
// String sql = "insert into actor values(?,?,?,?,?)";
//3.2 修改记录
// String sql = "update actor set name = ? where id = 2";
//3.3 删除记录
String sql = "delete from actor where id = 2";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
/* 添加一条记录
preparedStatement.setString(1, null); //预处理把 第一个问号改成 null
preparedStatement.setString(2, "jack"); //预处理把 第二个问号改成 null
preparedStatement.setString(3, "女"); //预处理把 第三个问号改成 null
preparedStatement.setString(4, "2001-2-1"); //预处理把 第四个问号改成 null
preparedStatement.setString(5, "120"); //预处理把 第五个问号改成 null*/
/*修改一条记录
preparedStatement.setString(1, "kangkang"); //预处理把 第一个问号改成 null
*/
//4.组织sql
int i = preparedStatement.executeUpdate();
System.out.println(i > 0 ? "成功" : "失败");
//6关闭连接
preparedStatement.close();
connection.close();
}
}七、封装与使用JDBCUtils工具类
（1）封装：
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
//完成练级与关闭数据库
public class JDBCUtils {
//定义相关属性(4个),因为只需要一份，因此我们做出static
private static String user; //用户名
private static String password; //密码
private static String url; //url
private static String driver; //驱动名
//在static代码块去初始化
static {
try {
Properties properties = new Properties();
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) {
//在实际开发中，我们可以这样处理
//将编译异常，转为运行异常 调用者可以处理，也可以默认
throw new RuntimeException(e);
}
}
//连接
public static Connection getConnection() {
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//关闭相关资源
public static void close(ResultSet set, Statement statement,Connection connection){
//判断是否为空
try {
if (set != null){
set.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
（2）使用dml：
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) {
//1.得到连接
Connection connection = null;
//2.组织一个sql
String sql = "insert into actor values(?,?,?,?,?)";
//3.创建P
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,null); //第1个问号
preparedStatement.setString(2,"周星驰"); //第2个问号
preparedStatement.setString(3,"男"); //第3个问号
preparedStatement.setString(4,"2022-2-20"); //第4个问号
preparedStatement.setString(5,"12345"); //第5个问号
//4.执行
int update = preparedStatement.executeUpdate();
System.out.println(update>0?"成功":"失败");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
}（3）使用工具类查询：
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) {
//1.得到连接
Connection connection = null;
//2.组织一个sql
String sql = "select * from actor";
//3.创建P
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//4.执行
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt(1);//获取该行的第一列
String name = resultSet.getString(2);//获取该行的第二列
String sex = resultSet.getString(3);//获取该行的第三列
String date = resultSet.getString(4);//获取该行的第四列
String phone = resultSet.getString(5);//获取该行的第五列
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date + "\t" + phone);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
}八、事务
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) {
//1.得到连接
Connection connection = null;
//2.组织一个sql
String sql = "update account set balance = balance - 100 where id = 1";
String sql2 = "update account set balance = balance + 100 where id = 2";
//3.创建P
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection(); //默认情况下connection是自动提交
//将connectio设置为不自动提交
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql);
//4.执行
int i = preparedStatement.executeUpdate(); //执行第一条
int a = 1 / 0; //抛出运行异常
preparedStatement = connection.prepareStatement(sql2);
int count = preparedStatement.executeUpdate(); //执行第二条
connection.commit();
} catch (SQLException throwables) {
System.out.println("发生了异常执行回滚执行的sql");
//在这里进行回滚
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
}九、批处理
大大提高运行速度，配置文件加上一句
url=jdbc:mysql://localhost:3306/jdbc?rewriteBatchedStatements=true
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) {
//1.得到连接
Connection connection = null;
//2.组织一个sql
String sql = "insert into actor values(?,?,?,?,?)";
//3.创建P
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, null); //第1个问号
preparedStatement.setString(2, "周星驰"); //第2个问号
preparedStatement.setString(3, "男"); //第3个问号
preparedStatement.setString(4, "2022-2-20"); //第4个问号
preparedStatement.setString(5, "12345"); //第5个问号
//将sql 语句加入到批处理包 -> 看源码
preparedStatement.addBatch();
//当有1000条时批量执行
if ((i + 1) % 1000 == 0) {
preparedStatement.executeBatch();
//清空一把
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println(end - end);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
}十、C3P0数据连接池：
（1）方式一
下载c3p0jar包，放到libs里面 注意版本
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) throws Exception {
//1.创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//2.通过配置文件mysql.properties 获取相关连接信息
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");
//给数据源 comboPooledDataSource 设置相关的信息
//注意我们的连接是由comboPooledDataSource来管理
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
//设置初始化连接数
comboPooledDataSource.setInitialPoolSize(10);
//最大连接数
long start = System.currentTimeMillis();
comboPooledDataSource.setMaxPoolSize(50);
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection(); //这个方法是从datasource继承的
connection.close();
}
System.out.println("时间消耗：" + (System.currentTimeMillis() - start));
}
}（2）方式二：使用模块文件来完成
1.在src下新建c3p0-config.xml文件注意不要打错了
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 数据源名称代表连接池-->
<named-config name="hsp_edu">
<!-- 配置数据库驱动 -->
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<!-- 配置数据库链接地址 -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
<!-- 配置数据库用户名 -->
<property name="user">root</property>
<!-- 配置数据库密码 -->
<property name="password">root</property>
<!-- 每次增长的连接数 -->
<property name="acquireIncrement">5</property>
<!-- 初始化连接数 -->
<property name="initialPoolSize">10</property>
<!-- 最小连接数 -->
<property name="minPoolSize">5</property>
<!--最大连接数 -->
<property name="maxPoolSize">50</property>
<!--可连接命令最多的命令对象。Default:0 -->
<property name="maxStatements">5</property>
<!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>2.使用
@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) throws Exception {
//1.将c3p0文件考备到 c3p0-config.xml 考备到 src目录下
//2.该文件指定了数据库和连接池的相关参数
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hsp_edu");
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
}
}十一、德鲁伊连接池(推荐新项目)
（1）使用：
1.下载jar包 Central Repository: com/alibaba/druid/1.0.10
2.书写配置文件 在src目录下新建一个druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=root
initialSize=10
minIdle=5
maxActive=50
maxWait=5000@SuppressWarnings({"all"}) //取消文件警告
public class Hello {
public static void main(String[] args) throws Exception {
//1. 加入Druid jar包
//2. 加入配置文件 druid.properties
//3.创建Propertie对象,读取文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//4. 创建一个指定的参数的数据库连接池,德鲁伊的连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
for (int i = 0; i < 5000; i++) {
Connection connection = dataSource.getConnection();
connection.close();
}
System.out.println("完毕");
}
}十二、德鲁伊连接池封装工具类
public class JDBCUtilsByDruid {
private static DataSource ds;
//在static代码块种完成 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 {
return ds.getConnection();
}
//关闭连接，老师再次强调：在数据库连接技术种 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 (Exception e) {
throw new RuntimeException(e);
}
}
}十三、Apache--DBUtils
（1）完成查询操作：
JDBCUtilsByDruid文件：
package com.baidu.Hello;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtilsByDruid {
private static DataSource ds;
//在static代码块种完成 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 {
return ds.getConnection();
}
//关闭连接，老师再次强调：在数据库连接技术种 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 (Exception e) {
throw new RuntimeException(e);
}
}
}
druid.properties文件在上面：
Actor.class:
package com.test;
public class Actor {
private int id;
private String name;
private String sex;
private String date;
private String phone;
public Actor() {
}
public Actor(int id, String name, String sex, String date, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.date = date;
this.phone = phone;
}
public int getId() {
return id;
}
public void setId(int 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 getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", date='" + date + '\'' +
", phone='" + phone + '\'' +
'}';
}
}
Test测试类：
public class Test1 {
//使用apche-DBUtils 工具类 + druid 完成对表的curd操作
@Test //返回结果是多行的情况
public void testQueryMany() throws Exception {
// 1.得到 连接(druid)
Connection connection = JDBCUtilsByDruid.getConnection();
// 2.使用 DBUtils类和接口，先引入DBUtils相关的jar,加入到本地Project
// 3.创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 4.就可以执行相关方法，返回ArrayList结果集
String sql = "select * from actor where id > ?";
// 4.1 query方法就是执行sql语句，得到resultSet ---封装到-->ArrayList 集合种
// 4.2 返回集合
// 4.3 connection 连接
// 4.4 new BeanListHandler<>(Actor.class) 底层使用反射封装Actor对象放到ArrayList种
// 4.5 这个后面的 0 就是给sql语句的问号复制可变参数，可以很多个
//注意下面Actor类的类名需要表的字段一样，类型一样，列名一样，需要在不同的文件内，并且类需要用public修饰
List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 0);
for (Actor actor : list) {
System.out.println(actor);
}
// 5.关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
@Test //返回结果是多行的情况
public void testQuerySingle() throws Exception {
// 1.得到 连接(druid)
Connection connection = JDBCUtilsByDruid.getConnection();
// 2.使用 DBUtils类和接口，先引入DBUtils相关的jar,加入到本地Project
// 3.创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 4.就可以执行相关方法，返回单个对象
String sql = "select * from actor where id > ?";
Actor query = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1000);
System.out.println(query);
//关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
@Test //返回结果是单行单列的情况
public void testQueryScalar() throws Exception {
// 1.得到 连接(druid)
Connection connection = JDBCUtilsByDruid.getConnection();
// 2.使用 DBUtils类和接口，先引入DBUtils相关的jar,加入到本地Project
// 3.创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 4.就可以执行相关方法，返回单个对象
String sql = "select name from actor where id = ?";
Object query = queryRunner.query(connection, sql, new ScalarHandler(), 30016);
System.out.println(query);
//关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
}十四、BasicDAO 最重要的
1.准备文件夹：
新建好几个文件夹：
2.BasicDAO：
package com.dao_.dao;
import com.baidu.Hello.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.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();
//开发一个通用的dml方法，针对任意表,
public int update(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = qr.update(connection, sql, parameters);
return update;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询多行 返回多个对象，针对任意表
public List<T> queryMultiply(String sql,Class<T> clazz,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
List<T> query = qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
return query;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询单行，返回单个对象，针对任意表
public T querySingle(String sql,Class<T> clazz,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
T query = qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
return query;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询单行单个，返回单个对象，针对任意表
public Object queryScalar(String sql,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
Object query = qr.query(connection, sql, new ScalarHandler(), parameters);
return query;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
}
3.Actor：
package com.dao_.dao;
import com.baidu.Hello.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.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();
//开发一个通用的dml方法，针对任意表,
public int update(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = qr.update(connection, sql, parameters);
return update;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询多行 返回多个对象，针对任意表
public List<T> queryMultiply(String sql,Class<T> clazz,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
List<T> query = qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
return query;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询单行，返回单个对象，针对任意表
public T querySingle(String sql,Class<T> clazz,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
T query = qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
return query;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询单行单个，返回单个对象，针对任意表
public Object queryScalar(String sql,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
Object query = qr.query(connection, sql, new ScalarHandler(), parameters);
return query;
} catch (Exception e) {
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
}
4.ActorDAO：
package com.dao_.dao;
import com.dao_.domain.Actor;
public class ActorDAO extends BasicDAO<Actor>{
//1.就有BasicDAO 的方法
//2.根据业务需求，可以编写特有的方法
}
5.TestDAO:
package com.dao_.test;
import com.dao_.dao.ActorDAO;
import com.dao_.domain.Actor;
import org.junit.jupiter.api.Test;
import java.util.List;
public class TestDAO {
@Test //测试ActorDAO 对actor表crud操作
public void testActorDAO(){
ActorDAO actorDAO = new ActorDAO();
//1.查询
List<Actor> actors = actorDAO.queryMultiply("select * from actor where id >= ?", Actor.class, 0);
System.out.println("====查询结果====");
for (Actor actor: actors) {
System.out.println(actor);
}
//2.查询单行记录
Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 30050);
System.out.println("查询单行结果为："+actor);
//3. 查询单行单例
Object o = actorDAO.queryScalar("select name from actor where id = ?", 30050);
System.out.println("查询的姓名为："+o);
//4.dml操作 insert update delete
actorDAO.update("insert into actor values(null,'张三','13','123','123')");
actorDAO.update("delete from actor where id = ?", 30050);
actorDAO.update("update actor set name = '成龙' where id = ?", 30051);
}
}
十五、案例书写连携DAO：
3476
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
