MySQL - JDBC&数据库连接池
1 JDBC
1.1 概念
Java Data Base Connectivity
执行SQL的Java API
Java访问多种数据库的统一规范(接口)
数据库厂商实现接口并提供驱动jar包
1.1.1 JDBC项目准备
新建项目并导入JDBC相关jar包
1.1.2 JDBC连接步骤
1.1.2.1 注册驱动
Class.forName("com.mysql.jdbc.Driver");
原理:
JDBC3开始可不用注册驱动直接使用,该步骤可以省略
1.1.2.2 获取连接
connection接口表示连接对象,具体实现类由数据库厂商实现
Driver Manager的getConnection方法可以获得连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8","root","123456");
该Connection对象有com.mysql.jdbc提供
1.1.2.3 获取语句执行对象
statement类,语句对象,发送SQL语句给服务器,执行静态SQL并返回结果
通过Connection连接对象的createStatement方法获取
statement常用方法:
int executeUpdate(String sql); DML,DDL语句; 返回值代表受影响的行数
ResultSet executeQuery(String sql); DQL语句; 返回ResultSet结果集对象
1.1.2.4 处理结果集对象(DQL)
DQL语句得到的是ResultSet结果集对象
ResultSet接口封装了查询的结果
接口方法:
boolean next(); 如果下一行还有记录返回true,没有记录返回false
xxx getXxx (String or int); 重载方法; 通过列名返回,参数String,返回不同的类型; 通过列号返回,参数是int,从1开始,返回不同类型
Statement statement = connection.createStatement();
String sql = "SELECT * FROM jdbc_user";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
int id = resultSet.getInt("id");
String username = resultSet.getString(2);
String password = resultSet.getString("PASSWORD");
Date birthday = resultSet.getDate(4);
System.out.println(id+" "+username+" "+password+" "+birthday);
}
1.1.2.5 释放资源
先开起后关闭
resultset(仅DQL查询时使用) -> statement - > connection
resultSet.close();
connection.close();
statement.close();
1.2 JDBC工具类(Utils)
1.2.1 编写JDBCUtils工具类
将连接信息定义成字符串常量
编写静态代码块注册驱动
编写静态方法获取连接对象
编写关闭资源的方法(重载对于DQL和非DQL)
1.2.1.1 将连接信息定义成字符串常量
public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/bd4?characterEncoding=UTF-8";
public static final String USERNAME = "root";
public static final String PASSWORD = "123456";
1.2.1.2 编写静态代码块注册驱动
static {
try {
Class.forName(DRIVERNAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
1.2.1.3 编写静态方法获取连接对象
public static Connection getConnection(){
try {
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
return connection;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
1.2.1.4 编写关闭资源的方法(重载对于DQL和非DQL)
public static void Close(Connection con, Statement statement){
if (con!=null && statement!= null){
try {
statement.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void Close(Connection con, Statement statement, ResultSet resultSet){
if (con!= null && statement!=null && resultSet!=null){
try {
resultSet.close();
statement.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
1.3 SQL注入问题
使用OR,前面用户名密码错误也可以拿到数据因为or1真为真
select * from jdbc_user where username = 'tom' and PASSWORD = '123' or '1' = '1';
代码演示:SQL语句为
String sql = "select * from jdbc_user where username = '" + username + "' and PASSWORD = '" + password + "'";
当用户输入password为:
abc' or '1' = '1
就会产生SQL注入
1.3.1 如何解决SQL注入?
不能让用户输入的密码和SQL语句进行简单的字符串拼接
输入时使用Scanner.next而不是nextLine接收,这样无法拼接空格
拿到数据后判断SQL返回结果的用户名是否与用户输入的用户名相同
1.3.1.1 预处理对象防止SQL注入
PreparedStatement是Statement接口的子接口,继承父接口所有方法
预编译的SQL语句:
执行效率更高
更安全,可防止SQL注入
? 占位符来设置参数
具体流程:
编写SQL,对于参数使用?占位符
通过Connection对象的preparestatement(sql) 有参方法拿到预处理对象
调用预处理对象的setXXX(int 占位符位置,XXX 要设置的值)给参数赋值
调用预处理对象的execute方法执行SQL查询
示例:
Connection connection = JDBCUtils.getConnection();
String sql = "select * from jdbc_user where username = ? and PASSWORD = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"tom");
preparedStatement.setString(2,"123456");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("username")+" "+resultSet.getString("PASSWORD"));
}
JDBCUtils.Close(connection,preparedStatement,resultSet);
}
1.3.1.2 statement和preparedstatement
当执行SQL时,比如插入数据(insert into jdbc_user values .... )
statement对象每执行一条SQL语句就会先发给数据库先编译再执行
插入多少条数据数据库就需要编译多少次
执行静态SQL
preparedstatement对象执行SQL语句数据库只需要预编译一次
然后将预编译的SQL保存起来(缓存)
插入多条数据时只需设置参数即可
可包含动态参数"?"
1.4 事务API
Connection方法实现事务
方法
说明
void setAutoCommit(boolean autocommit)
是否自动提交,true自动提交
void commit()
提交事务
viod rollback()
回滚事务
示例:模拟转账操作
获取connection连接对象
开启事务(关闭事务自动提交)
获取preparedstatement对象
设置参数,正常则手动提交
出现异常则回滚事务
关闭资源
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql = "update account set money = ? where NAME = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setDouble(1,500.0);
preparedStatement.setString(2,"tom");
preparedStatement.executeUpdate();
preparedStatement.setDouble(1,1500.0);
preparedStatement.setString(2,"jack");
preparedStatement.executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.Close(connection,preparedStatement);
}
}
2 数据库连接池
2.1 连接池介绍
管理数据库连接,可重复使用链接
关闭连接不代表销毁connection,只是进行了归还
如何使用连接池:
Java官方为连接池提供了公共的接口java.sql.DataSource
厂商实现该接口用户即可方便用户切换不同连接池
常用连接池:
DBCP
C3P0
Druid
2.2 常用连接池
2.2.1 DBCP连接池
2.2.1.1 概念:
开源
属于Apache
tomcat内置连接池
使用方法:
导入相关Jar包
2.2.1.2 DBCP工具类
定义常量保存数据库连接相关信息(Drivername,url,username,password)
创建连接池对象BasicDataSource,由DBCP提供的实现类
编写静态代码块对连接池对象进行配置
编写方法从连接池对象种获取连接
释放资源
代码:
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBCPUtils {
public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8";
public static final String USERNAME = "root";
public static final String PASSWORD = "123456";
public static BasicDataSource dataSource = new BasicDataSource();
static {
dataSource.setDriverClassName(DRIVERNAME);
dataSource.setUrl(URL);
dataSource.setUsername(USERNAME);
dataSource.setPassword(PASSWORD);
}
public static Connection getConnection(){
try {
Connection connection = dataSource.getConnection();
return connection;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void close(Connection con, Statement statement){
if (con!=null && statement != null){
try {
statement.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection con, Statement statement, ResultSet resultSet){
if (con!=null && statement != null && resultSet!= null){
try {
resultSet.close();
statement.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试类:
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DBCPUtils.getConnection();
String sql = "select * from jdbc_user where username = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"tom");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString("username")+" "+resultSet.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBCPUtils.close(connection,preparedStatement,resultSet);
}
常见配置项:
属性
描述
driverClassName
数据库驱动名称(mysql时 - com.mysql.jdbc.Driver)
url
数据库地址
username
用户名
password
密码
maxActive
最大连接数量(默认8)
maxIdle
最大空闲连接(默认8)
minIdle
最小空闲连接(默认0)
intialSize
初始化连接(默认0)
2.2.2 C3P0连接池
2.2.2.1 概念
开源
Hibernate,Spring框架使用
使用方式:
可配置xml文件存储c3p0相关配置
c3p0-config.xml
3
60
100
10
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8
root
123456
10
30
100
10
2.2.2.2 c3p0工具类
不需要定义常量保存数据库连接相关信息(因为配置xml文件)
创建连接池对象ComboPooledDataSource,由c3p0提供的实现类
new ComboPooledDataSource() 无参构造使用的是xml中的默认配置
new ComboPooledDataSource(configName) 有参构造使用的是xml中的指定配置
编写方法从连接池对象中拿到connection连接对象
释放资源
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class C3P0Utils {
public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");
public static Connection getConnection(){
try {
Connection connection = dataSource.getConnection();
return connection;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void close(Connection con, Statement statement){
try {
if (con!= null && statement!= null){
statement.close();
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection con, Statement statement, ResultSet resultSet){
try {
if (con!= null && statement!= null && resultSet!=null){
resultSet.close();
statement.close();
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
C3P0测试类:
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = C3P0Utils.getConnection();
statement = connection.createStatement();
String sql = "select * from jdbc_user";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getInt(1)+" "+resultSet.getString(2)+" "+ resultSet.getDate(4));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
C3P0Utils.close(connection,statement,resultSet);
}
2.2.3 Druid连接池
2.2.3.1 概念
使用方式:
配置文件:
properties文件形式
名称任意,文件路径任意,建议放到统一的resource资源目录下
druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000
2.2.3.2 Druid工具类
不需要定义常量来保存数据库连接相关信息(properties文件)
获取数据库连接池对象,通过工厂来获取DruidDataSourceFactory的createDataSource方法
createDataSource(Properties p)参数传入properties属性集对象
properties属性集对象通过load方法传入从字节流获取的对应properties文件 - getResourceAsStream()方法
通过从工厂中拿到的连接池对象创建connection对象
释放资源
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DruidUtils {
public static DataSource dataSource;
static {
try {
Properties p = new Properties();
InputStream resourceAsStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
p.load(resourceAsStream);
dataSource = DruidDataSourceFactory.createDataSource(p);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
Connection connection = dataSource.getConnection();
return connection;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void close (Connection connection, Statement statement){
if (connection != null && statement != null){
try {
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close (Connection connection, Statement statement, ResultSet resultSet){
if (connection != null && statement != null && resultSet!= null){
try {
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
示例:查询工资3000到5000的员工姓名
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = DruidUtils.getConnection();
statement = connection.createStatement();
String sql = "select ename from employee where salary between 3000 and 5000";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("ename"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DruidUtils.close(connection,statement,resultSet);
}
2.2.4 DBUtils工具类
2.2.4.1 基础概念
解决JDBC代码冗余问题
APache对JDBC进行简单封装的开源工具
使用方式:
核心功能:
QueryRunner
SQL语句操作的API
ResulSetHandler接口
DQL后自定义封装结果集
DbUtils类
封装了关闭资源和事务处理的方法
2.2.4.2 表和类的关系
JavaBean类对应一张表
实现序列化接口 Serializable
提供私有字段 private 类型 变量名 - 对应表中字段
get和set方法
无参构造
2.2.4.3 QueryRunner 核心类
构造方法
手动模式:QueryRunner()
自动模式(传入数据库连接池对象):QueryRunner(DataSource ds) 提供数据源(连接池),DbUtils自动维护连接
//自定义Druid工具类中需要加入获取连接池对象的方法
public static DataSource getDataSource(){
return dataSource;
}
//手动
QueryRunner queryRunner = new QueryRunner();
//自动
QueryRunner queryRunner1 = new QueryRunner(DruidUtils.getDataSource());
常用方法
update(Connection con, String sql, Object ... params) 表数据的增删改操作
query(Connection con, String sql, ResultSetHandlerresultset, Object ... params) 表数据的查询操作
update:
创建QueryRunner实例对象(手动模式调用update方法时需要传入connection对象,自动模式调用update方法时不需要传入connection对象)
编写SQL语句,使用? 占位符
设置占位符参数
调用update方法
释放资源
示例:插入一条数据
try {
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into employee values(?,?,?,?,?,?)";
Object[] params = {null,"刘翔",35,"男",9000,"1997-09-18"};
Connection connection = DruidUtils.getConnection();
queryRunner.update(connection,sql,params);
DbUtils.closeQuietly(connection);
} catch (SQLException e) {
e.printStackTrace();
}
示例:修改一条数据
try {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "update employee set salary = ? where ename = ?";
Object[] params = {5000, "刘翔"};
queryRunner.update(sql,params);
} catch (SQLException e) {
e.printStackTrace();
}
示例:删除一条数据
try {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql = "delete from employee where eid = ?";
queryRunner.update(sql,11);
} catch (SQLException e) {
e.printStackTrace();
}
2.2.4.4 ResulSetHandler (接口)
对查询出来的ResultSet结果集进行处理
常用实现类:
ResultSetHandler 实现类
描述
ArrayHandler
将结果集中的第一条记录封装到一个Object[]数组中,数组中的每一个元素就是这 条记录中的每一个字段的值
ArrayListHandler
将结果集中的每一条记录都封装到一个Object[]数组中,将这些数组在封装到List集 合中
BeanHandler
将结果集中第一条记录封装到一个指定的javaBean中.
BeanListHandler
将结果集中每一条记录封装到指定的javaBean中,再将这些javaBean在封装到List 集合中
ColumnListHandle
将结果集中指定的列的字段值,封装到一个List集合中
KeyedHandler
将结果集中每一条记录封装到Map,在将这个map集合做为另一个 Map的value,另一个Map集合的key是指定的字段的值
MapHandler
将结果集中第一条记录封装到了Map集合中,key就是字段名称, value就是字段值
MapListHandler
将结果集中每一条记录封装到了Map集合中,key就是字段名称, value就是字段值,在将这些Map封装到List集合中
ScalarHandler
它是用于封装单个数据。例如 select count(*) from 表操作
3个不同map的区别
MapListHandler (查询结果为多条记录):每一个Map对应表中的一条数据,Map里的key为字段名,value为值,然后用一个List将所有Map封装起来对应表。 遍历数据需要先遍历List的每一个Map拿到每一条数据,再通过EntrySet拿到每一个Map的所有key:value对
try {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql= "select * from employee";
List> query = queryRunner.query(sql, new MapListHandler());
for (Map map : query) {
Set> entries = map.entrySet();
for (Map.Entry entry : entries) {
System.out.print(entry.getKey()+":"+entry.getValue()+" ");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
结果:
eid:1 ename:李清照 age:22 sex:女 salary:4000.0 empdate:2018-11-12
eid:2 ename:林黛玉 age:20 sex:女 salary:5000.0 empdate:2019-03-14
eid:3 ename:杜甫 age:40 sex:男 salary:6000.0 empdate:2020-01-01
eid:4 ename:李白 age:25 sex:男 salary:3000.0 empdate:2017-10-01
eid:9 ename:zzc age:18 sex:男 salary:3500.0 empdate:2019-09-16
eid:10 ename:abc age:18 sex:男 salary:3600.0 empdate:2019-09-16
MapHandler(查询结果为1条记录):Map对应一条查询到的语句,Map里的key为字段名,value为值,遍历结果遍历entrySet
try {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql= "select * from employee where ename = ?";
Map result = queryRunner.query(sql, new MapHandler(), "李白");
for (Map.Entry entry : result.entrySet()) {
System.out.print(entry.getKey()+":"+entry.getValue()+" ");
}
} catch (SQLException e) {
e.printStackTrace();
}
KeyedHandler:每一条记录对应一个Map,不同之处在于对于表该实现类用Map和每一条记录的指定字段值来对应
try {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql= "select ename,age from employee";
Map> query = queryRunner.query(sql, new KeyedHandler<>());
for (Map.Entry> entry : query.entrySet()) {
System.out.print(entry.getKey()+": ");
Set> entries = entry.getValue().entrySet();
for (Map.Entry objectEntry : entries) {
System.out.print(objectEntry.getKey()+":"+objectEntry.getValue()+" ");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
使用Bean对象:
根据表的对应字段创建Bean对象
import java.io.Serializable;
import java.util.Date;
public class Employee implements Serializable {
/*
eidint(11) NOT NULL
enamevarchar(20) NULL
ageint(11) NULL
sexvarchar(6) NULL
salarydouble NULL
empdatedate NULL
*/
private static final long serialVersionUID = 2927021897478991450L;
private int eid;
private String ename;
private int age;
private String sex;
private double salary;
private Date empdate;
public Employee() {
}
public int getEid() {
return eid;
}
public void setEid(int eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Date getEmpdate() {
return empdate;
}
public void setEmpdate(Date empdate) {
this.empdate = empdate;
}
@Override
public String toString() {
return "Employee{" +
"eid=" + eid +
", ename='" + ename + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", salary=" + salary +
", empdate=" + empdate +
'}';
}
}
示例:
try {
QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
String sql= "select * from employee";
List query = queryRunner.query(sql, new BeanListHandler(Employee.class));
for (Employee employee : query) {
System.out.println(employee.toString());
}
} catch (SQLException e) {
e.printStackTrace();
}
2.2.4.5 批处理
一次操作执行多条SQL语句
批处理的实现:
Statement和PreparedStatement对象都支持批处理
PreparedStatement:
void addBatch() - 将指定SQL添加到次Statement对象中
int[] executeBatch() - 提交一批命令到数据库中,返回数组为受影响行数
mysql 批处理默认关闭,需要参数拼接在url后打开
rewriteBatchedStatements=true
例如: url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8&rewriteBatchedStatements=true
3 MySQL元数据
除了表之外的数据都是元数据
查询结果信息: UPDATE 或 DELETE语句 受影响的记录数
数据库和数据表的信息: 包含了数据库及数据表的结构信息
MySQL服务器信息: 包含了数据库服务器的当前状态,版本号等
Mysql获取元数据常用命令:
-- 元数据相关的命令介绍
-- 1.查看服务器当前状态
show status;
-- 2.查看MySQl的版本信息
select version();
-- 3.查询表中的详细信息
show columns from table_name;
-- 4.显示数据表的详细索引信息
show index from table_name;
-- 5.列出所有数据库
show databases:
-- 6.显示当前数据库的所有表
show tables :
-- 7.获取当前的数据库名
select database():
JDBC获取元数据
元数据类:
DatabaseMetaData - 描述数据库的元数据对象
ResultSetMetaData - 描述结果集的元数据对象
获取元数据对象:调用 getMetaData ()方法
connection 连接对象, 调用 getMetaData () 方法,获取的是DatabaseMetaData 数据库元数据对象
PrepareStatement 预处理对象调用 getMetaData () , 获取的是ResultSetMetaData , 结果集元数据对象
DatabaseMetaData 常用方法:
getURL() : 获取数据库的URL
getUserName(): 获取当前数据库的用户名
getDatabaseProductName(): 获取数据库的产品名称
getDatabaseProductVersion(): 获取数据的版本号
getDriverName(): 返回驱动程序的名称
isReadOnly(): 判断数据库是否只允许只读 true 代表只读
ResultSetMetaData的常用方法:
getColumnCount() : 当前结果集共有多少列
getColumnName(int i) : 获取指定列号的列名, 参数是整数 从1开始
getColumnTypeName(int i): 获取指定列号列的类型, 参数是整数 从1开始