一、JDBC概述
(一)、基本介绍
package com.hspedu.jdbc.myjdbc;
//我们规定的jdbc接口(方法)
public interface JdbcInterface {
//连接
public Object getConnection();
//crud
public void curd();
//关闭连接
public void close();
}
package com.hspedu.jdbc.myjdbc;
//mysql数据库实现了jdbc接口
public class MysqlJdbcImpl implements JdbcInterface{
@Override
public Object getConnection() {
System.out.println("得到mysql的连接");
return null;
}
@Override
public void curd() {
System.out.println("完成MySQL的增删改查");
}
@Override
public void close() {
System.out.println("关闭mysql的连接");
}
}
package com.hspedu.jdbc.myjdbc;
public class TestJDBC {
public static void main(String[] args) {
//完成mysql的操作
JdbcInterface jdbcInterface = new MysqlJdbcImpl();
jdbcInterface.getConnection();//通过接口来调用实现类【动态绑定】
jdbcInterface.curd();
jdbcInterface.close();
}
}
(二)、JDBC带来的好处
(三)JDBC API
二、JDBC快速入门
(一)、JDBC程序编写步骤
(二)JDBC第一个程序
CREATE TABLE actor (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT '',
sex CHAR(1) NOT NULL DEFAULT '女',
borndate DATETIME,
phone VARCHAR(12)
);
SELECT * FROM actor
package com.hspedu.jdbc;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
//这是第一个JDBC程序,完成简单的操作
public class Jdbc01 {
public static void main(String[] args) throws SQLException {
//前置工作:在项目系创建一个文件及比如libs
//将mysql.jar 拷贝到该目录下,点击add to project ..加入到项目中
//1.注册驱动
//(1) jdbc:mysql://规定好表示协议,通过jdbc的方式连接mysql
//(2) localhost 主机,可以是ip地址
//(3) 3306 表示mysql监听的端口
//(4) hsp_db02 连接到mysql dbms 的哪个数据库
//(5) mysql的连接本质就是socket连接
Driver driver = new Driver();//创建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/hsp_db02";
//将 用户名和密码放入到Properties 对象
Properties properties = new Properties();
//说明 user 和password 是规定好,后面的值根据实际情况写
properties.setProperty("user","root");//用户
properties.setProperty("password", "hsp");//密码
//获取连接
Connection connect = driver.connect(url, properties);
//3.执行sql
String sql = "insert into actor values(null, '刘德华','男','1970-10-11','110')";
//String sql = "update actor set name = '周星驰' where id = 1";
//String sql = "delete form actor where id = 1";
//statement 用于执行静态SQL语句并返回其生成的结果的对象
Statement statement = connect.createStatement();
int rows = statement.executeUpdate(sql);//如果是dml语句,返回的是影响的行数
System.out.println(rows > 0 ? "成功" : "失败");
//4.关闭连接资源
statement.close();
connect.close();
}
}
三、获取数据库连接5种方式
package com.hspedu.jdbc;
import com.mysql.jdbc.Driver;
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.SQLException;
import java.util.Properties;
//分析java连接mysql的5种方式
public class JdbcConn {
@Test
//方式一
public void connect01() throws SQLException {
Driver driver = new Driver();
String url = "jdbc:mysql://localhost:3306/hsp_db02";
//将 用户名和密码放入到Properties 对象
Properties properties = new Properties();
//说明 user 和password 是规定好,后面的值根据实际情况写
properties.setProperty("user","root");//用户
properties.setProperty("password", "hsp");//密码
//获取连接
Connection connect = driver.connect(url, properties);
System.out.println("方式1=" + connect);//方式1=com.mysql.jdbc.JDBC4Connection@68fb2c38
}
//方式二
@Test
public 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/hsp_db02";
//将 用户名和密码放入到Properties 对象
Properties properties = new Properties();
//说明 user 和password 是规定好,后面的值根据实际情况写
properties.setProperty("user","root");//用户
properties.setProperty("password", "hsp");//密码
Connection connect = driver.connect(url, properties);
System.out.println("方式2=" + connect);//方式2=com.mysql.jdbc.JDBC4Connection@68fb2c38
}
//方式3 使用DriverManager 替代 Diver 进行统一管理
@Test
public void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
//使用反射机制Driver
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver =(Driver) aClass.newInstance();
//创建url 和 password
String url = "jdbc:mysql://localhost:3306/hsp_db02";
String user = "root";
String password = "hsp";
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("方式3=" + connection);//方式3=com.mysql.jdbc.JDBC4Connection@567d299b
}
//方式4:使用Class.forName 自动完成组成驱动,简化代码
//这种方式推荐使用
@Test
public void connect04() throws ClassNotFoundException, SQLException {
//使用反射加载了Driver类
//在加载Driver类时,完成注册
/*
源码:1.静态代码块,在类加载时,会执行一次
2.DriverManager.registerDriver(new Driver());
3.因此注册driver的工作已经完成
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
*/
Class.forName("com.mysql.jdbc.Driver");
//创建url 和 password
String url = "jdbc:mysql://localhost:3306/hsp_db02";
String user = "root";
String password = "hsp";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("第4种方式 " + connection);//第4种方式 com.mysql.jdbc.JDBC4Connection@68fb2c38
}
@Test
//方式5,在方式4的基础上改进,增加配置文件,让连接mysql更加灵活
public void connect05() throws IOException, ClassNotFoundException, SQLException {
//通过Properties对象获取配置文件的信息
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("方式5=" + connection);//方式5=com.mysql.jdbc.JDBC4Connection@68fb2c38
}
}
(一)课堂练习
(二)ResultSet【结果集】
package com.hspedu.jdbc.resultSet_;
import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;
//演示select 语句返回 ResultSet ,并取出结果
@SuppressWarnings({"all"})
public class ResultSet_ {
public static void main(String[] args) throws Exception {
//通过Properties对象获取配置文件的信息
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 id, name, sex, borndate from actor";
//执行给定的SQL语句,该语句返回单个ResultSet对象
/*
+----+-----------+-----+---------------------+-------+
| id | NAME | sex | borndate | phone |
+----+-----------+-----+---------------------+-------+
| 1 | 刘德华 | 男 | 1970-10-11 00:00:00 | 110 |
| 2 | 刘德华 | 男 | 1970-12-12 00:00:00 | 110 |
| 3 | jack | 男 | 1970-11-11 00:00:00 | 110 |
+----+-----------+-----+---------------------+-------+
*/
/*
resultSet对象的结构
*/
ResultSet resultSet = statement.executeQuery(sql);//指向表头
//5.使用while取出数据
while (resultSet.next()) { //让光标向后移,如果没有更多行,则返回false
int id = resultSet.getInt(1);//获取该行的第一列
int id1 = 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);
}
//6.关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
1 刘德华 男 1970-10-11
2 刘德华 男 1970-12-12
3 jack 男 1970-11-11
这里是引用
四、Statement
(一)基本介绍
-- 演示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
-- 输入用户名 为 1' or
-- 输入密码 为 or '1' = '1'
SELECT *
FROM admin
WHERE NAME = '1' OR' and pwd = 'OR '1' = '1'
package com.hspedu.jdbc.statement_;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
//演示statement 的注入问题
@SuppressWarnings({"all"})
public class Statement_ {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
//让用户输入管理员名和密码
System.out.print("请输入管理员的名字:");
//next():当接收到空格或者’(单引号)就是表示结束
//如果希望看到SQL注入,这里需要nextLine()
String admin_name = scanner.nextLine();
System.out.print("请输入管理员的密码:");
String admin_pwd = scanner.nextLine();
//通过Properties对象获取配置文件的信息
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 name, pwd from admin where name = '" +
admin_name + "' and pwd = ' "+ admin_pwd + "'" ;
ResultSet resultSet = statement.executeQuery(sql);
if(resultSet.next()) { //如果查询到一条记录,则说明该管理存在
System.out.println("恭喜, 登录成功");
} else {
System.out.println("对不起,登录失败");
}
//关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
五、PreparedStatement
(一)基本介绍
(二)预处理好处
package com.hspedu.jdbc.preparedstatement_;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
//演示PreparedStatement
@SuppressWarnings({"all"})
public class PreparedStatement_ {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
//让用户输入管理员名和密码
System.out.print("请输入管理员的名字:");
//next():当接收到空格或者’(单引号)就是表示结束
//如果希望看到SQL注入,这里需要nextLine()
String admin_name = scanner.nextLine();
System.out.print("请输入管理员的密码:");
String admin_pwd = scanner.nextLine();
//通过Properties对象获取配置文件的信息
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.得到PreparedStatement
//3.1.组织Sql, SQL 语句的?相当于占位符
String sql = "select name , pwd from admin where name = ? and pwd = ?";
//3.2preparedStatement 对象实现了 PreparedStatement 接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3.3 给? 赋值占位符
preparedStatement.setString(1, admin_name);
preparedStatement.setString(2, admin_pwd);
//4.执行select 语句使用 executeQuery
//如果执行的是dml(update, insert, delete) executeUpdte()
//这里执行executeQuery,不要写sql
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next()) { //如果查询到一条记录,则说明该管理存在
System.out.println("恭喜, 登录成功");
} else {
System.out.println("对不起,登录失败");
}
//关闭连接
resultSet.close();
preparedStatement.close();
connection.close();
}
}
执行DML
package com.hspedu.jdbc.preparedstatement_;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import java.util.Scanner;
//演示PreparedStatement DML
@SuppressWarnings({"all"})
public class PreparedStatementDML_ {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
//让用户输入管理员名和密码
System.out.print("请输入管理员的名字:");
//next():当接收到空格或者’(单引号)就是表示结束
//如果希望看到SQL注入,这里需要nextLine()
String admin_name = scanner.nextLine();
//System.out.print("请输入管理员的密码:");
//String admin_pwd = scanner.nextLine();
//通过Properties对象获取配置文件的信息
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.得到PreparedStatement
//3.1.组织Sql, SQL 语句的?相当于占位符
//添加记录
//String sql = "insert into admin values(?, ?)";
//String sql = "update admin set pwd = ? where name = ?";
String sql = "delete from admin where name = ?";
//3.2preparedStatement 对象实现了 PreparedStatement 接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3.3 给? 赋值占位符
//preparedStatement.setString(1, admin_pwd);
preparedStatement.setString(1, admin_name);
// preparedStatement.setString(2, admin_name);
//4.执行dml 语句使用 executeUpdate
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "执行成功" : "执行失败");
//关闭连接
preparedStatement.close();
connection.close();
}
}
六、jdbc API
七、封装JDBCUtils
package com.hspedu.jdbc.Utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
//这是一个工具类,完成mysql的连接个关闭资源
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) {
//在实际开发中,我们可以这样处理
//1.将编译异常转成运行异常
//2.这时调用者可以选择捕获异常,也可以默认处理该异常
throw new RuntimeException(e);
}
}
//连接数据库,返回Connection
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
//1.将编译异常转成运行异常
//2.这时调用者可以选择捕获异常,也可以默认处理该异常
throw new RuntimeException(e);
}
}
//关闭相关资源
//1.ResultSet 结果集
//2.Statement 或者PreparedStatement
//3.Connection
//4.如果需要关闭资源,就传入对象,否则传入null
public static void cloce(ResultSet set, Statement statement, Connection connection) {
//判断是否为空
try {
if(set != null) {
set.close();
}
if(statement != null) {
statement.close();
}
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
//将编译异常转成运行异常抛出
throw new RuntimeException(e);
}
}
}
package com.hspedu.jdbc.Utils;
import jdk.nashorn.internal.scripts.JD;
import org.junit.Test;
import java.sql.*;
//该类演示如何使用JDBCUtils工具类,完成dml 和select
public class JDBCUtils_Use {
public static void main(String[] args) {
}
@Test
public void testSelect() {
//1.得到连接
Connection connection = JDBCUtils.getConnection();
//2.组织一个sql
String sql = "select * from actor";
PreparedStatement preparedStatement = null;
ResultSet set = null;
//3.创建PreparedStatement 对象
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//执行
set = preparedStatement.executeQuery();
//遍历该结果集
while (set.next()) {
int id = set.getInt("id");
String name = set.getString("name");
String sex = set.getString("sex");
Date borndate = set.getDate("borndate");
String phone = set.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.cloce(set, preparedStatement, connection);
}
}
@Test
public void testDML() {//insert, update, delete
//1.得到连接
Connection connection = JDBCUtils.getConnection();
//2.组织一个sql
String sql = "update actor set name = ? where id = ?";
PreparedStatement preparedStatement = null;
//3.创建PreparedStatement 对象
try {
preparedStatement = connection.prepareStatement(sql);
//给占位符赋值
preparedStatement.setString(1, "周星驰");
preparedStatement.setInt(2,4);
//执行
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.cloce(null, preparedStatement, connection);
}
}
}
八、事务
(一)基本介绍
(二)应用案例
模拟经典的转账业务
package com.hspedu.jdbc.transaction_;
import com.hspedu.jdbc.Utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 演示jdbc中如何使用事务
*/
public class Transaction_ {
@Test
public void noTransaction() {
//操作转账的业务
//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";
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();//在默认情况下,connection是默认自动提交
//将 connection 设置为不自动提交
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
//int i = 1/0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//这里提交事务
connection.commit();
} catch (SQLException e) {
//在这里我们可以进行回滚,即撤销执行的sql
//默认回滚到事务开始的状态
System.out.println("执行发生了异常,撤销执行的sql");
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.cloce(null, preparedStatement,connection);
}
//
}
}
CREATE TABLE ACCOUNT(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT '',
balance DOUBLE NOT NULL DEFAULT 0) CHARACTER SET utf8;
INSERT INTO ACCOUNT VALUES(NULL, '马云', 3000);
INSERT INTO ACCOUNT VALUES(NULL, '马化腾', 10000);
SELECT * FROM ACCOUNT;
九、批处理
(一)基本介绍
package com.hspedu.jdbc.batch_;
import com.hspedu.jdbc.Utils.JDBCUtils;
import jdk.nashorn.internal.scripts.JD;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 演示java的批处理
*/
public class Batch_ {
//传统方法,添加5000条数据到admin2
@Test
public void noBath() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values (null, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "jack" + i);
preparedStatement.setString(2, "666");
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("传统的方式 耗时=" + (end - start));//传统的方式 耗时=2315
//关闭连接
JDBCUtils.cloce(null, preparedStatement, connection);
}
//使用批量方式添加数据
@Test
public void batch() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into admin2 values (null, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "jack" + i);
preparedStatement.setString(2, "666");
//将sql 语句加到批处理包中
preparedStatement.addBatch();
//当有1000条记录是,在批量执行
if((i + 1) % 1000 == 0) { //满1000条sql
preparedStatement.executeBatch();
//清空一把
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("批量的方式 耗时=" + (end - start));//批量的方式 耗时=55
//关闭连接
JDBCUtils.cloce(null, preparedStatement, connection);
}
}
CREATE TABLE admin2(
id INT PRIMARY KEY AUTO_INCREMENT,
udername VARCHAR(32) NOT NULL,
PASSWORD VARCHAR(32) NOT NULL
);
SELECT COUNT(*) FROM admin2
DROP TABLE admin2
十、数据库连接池
(一)5k次连接数据库问题
(二)传统获取Connection问题分析
package com.hspedu.jdbc.datasource;
import com.hspedu.jdbc.Utils.JDBCUtils;
import jdk.nashorn.internal.scripts.JD;
import org.junit.Test;
import java.sql.Connection;
public class ConQuestion {
//代码 连接mysql 5000次
@Test
public void testCOn() {
for (int i = 0; i < 5000; i++) {
//使用传统的jdbc方式,得到连接
Connection connection = JDBCUtils.getConnection();
//做一些工作,比如得到PreparedStatement,发送sql
}
}
}
package com.hspedu.jdbc.datasource;
import com.hspedu.jdbc.Utils.JDBCUtils;
import jdk.nashorn.internal.scripts.JD;
import org.junit.Test;
import java.sql.Connection;
public class ConQuestion {
//代码 连接mysql 5000次
@Test
public void testCOn() {
//看看连接-关闭 connection 会耗用多久
long start = System.currentTimeMillis();
System.out.println("开始连接");
for (int i = 0; i < 5000; i++) {
//使用传统的jdbc方式,得到连接
Connection connection = JDBCUtils.getConnection();
//做一些工作,比如得到PreparedStatement,发送sql
//关闭
JDBCUtils.cloce(null, null, connection);
}
long end = System.currentTimeMillis();
System.out.println("传统方式5000次 耗时=" + (end - start));//传统方式5000次 耗时=5242
}
}
(三)数据库连接池基本介绍
(四)数据库连接池种类
(五)C3P0应用案例
c3p0方式一
package com.hspedu.jdbc.datasource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;
/**
* 演示c3p0的使用
*/
public class C3p0_ {
//方式1:相关参数,在程序中指定user, url, password等
@Test
public void testC3P0_01() 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);
//最大连接数
comboPooledDataSource.setMaxPoolSize(50);
//测试连接池的效率,测试对mysql 5000次操作
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();//这个方法就是从 DataSource 接口实现的
//System.out.println("连接ok");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("c3p0 5000次连接mysql 耗时=" + (end - start) );//c3p0 5000次连接mysql 耗时=276
}
}
package com.hspedu.jdbc.datasource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
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的使用
*/
public class C3p0_ {
//方式1:相关参数,在程序中指定user, url, password等
@Test
public void testC3P0_01() 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);
//最大连接数
comboPooledDataSource.setMaxPoolSize(50);
//测试连接池的效率,测试对mysql 5000次操作
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();//这个方法就是从 DataSource 接口实现的
//System.out.println("连接ok");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("c3p0 5000次连接mysql 耗时=" + (end - start) );//c3p0 5000次连接mysql 耗时=276
}
//第二种方式 使用配置文件模板来完成
//1.将c3p0 提供的c3p0.config.xml 拷贝到 crc目录下
//2.该文件指定了连接数据库和连接池的相关参数
@Test
public void testC3P0_02() throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hsp_db02");
//测试5000次连接mysql
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
//System.out.println("连接0k");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("c3p0的第二种方式 耗时=" + (end - start));//c3p0的第二种方式 耗时=252
}
}
c3p0-config.xml
<c3p0-config>
<!-- 数据源名称代表连接池 -->
<default-config name="hsp_edu">
<!--加载驱动-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!--数据库url-->
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/hsp_db02</property>
<!--数据库用户名-->
<property name="user">root</property>
<!--数据库密码-->
<property name="password">hsp</property>
<!-- 每次增长的连接数 -->
<property name="acquireIncrement">5</property>
<!-- 初始化的连接数 -->
<property name="initialPoolSize">10</property>
<!-- 最小连接数 -->
<property name="minPoolSize">5</property>
<!--最大连接数-->
<property name="maxPoolSize">50</property>
<!-- 可连接的最多的命令对象数 -->
<property name="maxStatements">5</property>
<!-- 每个连接对象可以连接的最大的命令对象数 -->
<property name="maxStatementsPerConnection">2</property>
</default-config>
</c3p0-config>
(六)Druid(德鲁伊)应用实例
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/hsp_db02?rewriteBatchedStatements=true
username=root
password=hsp
#initial connection Size
initialSize=10
#min idle connection size
minIdle=5
#max active connection size
maxActive=20
#max wait time (5000 mil seconds)
maxWait=5000
package com.hspedu.jdbc.datasource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
/**
* 测试druid的使用
*/
public class Druid_ {
@Test
public void testDruid() throws Exception {
//1.加入Druid jar包
//2.加入 配置文件 druid.properties, 将该文件拷贝项目的src目录
//3.创建Properties对象,读取配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//4.创建一个指定参数的数据库连接池,Druid连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = dataSource.getConnection();
//System.out.println("连接成功");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("druid连接池 操作5000 耗时=" + (end - start));//druid连接池 操作5000 耗时=297
}
}
package com.hspedu.jdbc.datasource;
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;
/**
* 基于druid数据库连接池的工具类
*/
public class JDBCUtilsByDruid {
private static DataSource ds;
//在静态代码块完成ds 初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//编写getConnection方法
public static Connection getConnection() throws SQLException {
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 (SQLException e) {
throw new RuntimeException(e);
}
}
}
package com.hspedu.jdbc.datasource;
import com.hspedu.jdbc.Utils.JDBCUtils;
import org.junit.Test;
import java.sql.*;
public class JDBCUtilsByDruid_USE {
@Test
public void testSelect() {
System.out.println("使用druid 方式完成");
//1.得到连接
Connection connection = JDBCUtils.getConnection();
//2.组织一个sql
String sql = "select * from actor";
PreparedStatement preparedStatement = null;
ResultSet set = null;
//3.创建PreparedStatement 对象
try {
connection = JDBCUtilsByDruid.getConnection();
System.out.println(connection.getClass());//运行类型 class com.alibaba.druid.pool.DruidPooledConnection
preparedStatement = connection.prepareStatement(sql);
//执行
set = preparedStatement.executeQuery();
//遍历该结果集
while (set.next()) {
int id = set.getInt("id");
String name = set.getString("name");
String sex = set.getString("sex");
Date borndate = set.getDate("borndate");
String phone = set.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtilsByDruid.close(set, preparedStatement, connection);
}
}
}
十一、Apache-DBUtils
(一)先分析一个问题
(二)基本介绍
(三)应用案例
package com.hspedu.jdbc.datasource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class DBUtils_USE {
//使用Apache-DBUtils 工具类 + druid 完成对表的crud操作
@Test
public void testQueryMany() throws SQLException { //返回结果是多行的情况
//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 >= ?";
//5.(1) query 方法就是执行SQL语句,得到ResultSet --封装到--》ArrayList集合中
//(2)返回集合
//(3)connection:连接
//(4)sql:执行的sql语句
//(5)new BeanListHandler<>(Actor.class):再将resultset->Actor对象-》封装到ArrayList
//底层使用反射机制去获取Actor类的属性
//(6)1就是给sql语句中的?赋值,可以有多个值,因为是可变参数 1,2,3,4,) Object... params
//(7)底层得到的resultset ,会在query关闭,关闭PreparedStatment
List<Actor> list = (List<Actor>) queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 4);
System.out.println("输出集合的信息");
for (Actor actor: list) {
System.out.print(actor);
}
//释放资源
JDBCUtilsByDruid.close(null,null, connection);
}
}
//演示 Apache-dbutils + durid 完成 返回的结果是单行记录(单个对象)
@Test
public void testQuerySingle() throws SQLException {
//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 = ?";
//返回的是单行记录,使用的Hander 是BeanHandler
Actor actor = queryRunner.query(connection,sql, new BeanHandler<>(Actor.class), 4);
System.out.println(actor);
JDBCUtilsByDruid.close(null,null,connection);
}
@Test
//演示apache-dbutils + druid 完成查询结果是单行单列-返回的就是object
public void testScalar() throws SQLException {
//1.得到 连接(druid)
Connection connection = JDBCUtilsByDruid.getConnection();
QueryRunner queryRunner = new QueryRunner();
//4.就可以执行相关的方法,返回单个对象
String sql = "select * from actor where id = ?";
Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 4);
System.out.println(obj);
JDBCUtilsByDruid.close(null,null,connection);
}
}
@Test
//演示apache-dbutils + druid 完成dml(update, insert, delete)
public void testDML() throws SQLException {
Connection connection = JDBCUtilsByDruid.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "update actor set name= ? where id = ?";
//(1)执行dml操作是queryRunner.update()
//(2)返回的值是受影响的行数
int affectedRow = queryRunner.update(connection, sql, "张三丰", 4);
System.out.println(affectedRow >0 ? "执行成功" :"执行没有影响到表");
JDBCUtilsByDruid.close(null,null,connection);
}
(四)表和JavaBean的类型映射关系
十二、DAO和增删改查通用方法-BasicAao
(一)先分析一个问题
(二)基本说明
(三)BasicDAO应用示例
package com.hspedu.dao_.dao;
import com.hspedu.jdbc.datasource.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方法,针对任意的表
Connection connection = null;
//开发通用的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 (SQLException e) {
throw new RuntimeException(e);//将编译异常-》运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//返回对个对象(即查询的结果是多行)
/**
*
* @param sql sql,语句,可以有
* @param clazz 闯入一个类的Class对象 比如Actor.class
* @param parameters 传入 ? 的具体值,可以是多个
* @return 根据ACtor.class 返回对应的ArrayList 集合
*/
public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
} catch (SQLException 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();
return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
} catch (SQLException 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();
return qr.query(connection,sql,new ScalarHandler(),parameters);
} catch (SQLException e) {
throw new RuntimeException(e);//将编译异常-》运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
}
package com.hspedu.dao_.test;
import com.hspedu.dao_.dao.ActorDAO;
import com.hspedu.dao_.domain.Actor;
import jdk.nashorn.internal.ir.CallNode;
import org.junit.Test;
import java.util.List;
public class TestDAO {
@Test
//测试ActorDAO对actor表crud操作
public void testActorDAO() {
ActorDAO actorDAO = new ActorDAO();
//1.查询
List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1);
System.out.println("====查询结果===");
for (Actor actor : actors) {
System.out.println(actor);
}
//2.查询单行记录
Actor actor = actorDAO.querySingle("select * from actor where id >= ?", Actor.class, 1);
System.out.println("===查询单行记录");
System.out.println(actor);
//3.查询单行单列
Object o = actorDAO.queryScalar("select name from actor id = ?", 6);
System.out.println("===查询单行单列值===");
System.out.println(o);
//4.dml操作insert ,update, delete
int update = actorDAO.update("insert into actor values(null,?,?,?,?)", "张无忌", "男", "2009-11-11", "3414");
System.out.println(update > 0 ? "执行成功" : "执行没有影响的行数");
}
}