JDBC基础
JDBC基础
1.使用步骤
JDBC是标准,不同数据库厂商用不同数据库的驱动程序来实现
优点:
- 统一API,提供一致开发过程
- 易学习,上手
- 功能强大,效率高,可处理海量数据
1.1 流程
- 加载并注册JDBC驱动;
- 创建数据库连接;
- 创建Statement对象;
- 遍历查询结果;
- 关闭连接,释放资源。
笔者在官网下载的文件中无法解压得到jar包,所以我直接在maven仓库中下载得到,链接 ,然后将其移动到项目的lib目录,并在ProjectStructure配置该依赖。
1.2 数据库连接代码及其说明
Class.forName:用于加载指定的JDBC驱动类,本质是通知JDBC注册这个驱动类,驱动由数据库厂商自行开发,此外不同数据库的连接字符串也不同,见下图:
DriverManager
-
DriverManager用于注册/管理JDBC驱动程序
-
DriverManager.getConnection(连接字符串,用户名,密码)
-
返回Connection对象,对应数据库的物理网络连接,java.sql.Connection是一个接口,具体由驱动厂商实现,所有database操作基于此连接
2.数据库查询方法
首先在MySQL数据库中创建imooc数据库,创建一个示例的部门员工表,以供测试。
package com.imooc.jdbc.sample;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* 标准JDBC操作五步骤
*/
public class StandardJDBCSample {
public static void main(String[] args) throws ClassNotFoundException {
//1.加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.创建数据库连接
Connection conn = null;
try {
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:5001/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai",
"root", "sbt123"
);
//3.创建Statement对象
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from emp where dname='研发部'");
//4.遍历查询结果
while (rs.next()) {
Integer eno = rs.getInt("eno");
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
}
} catch(Exception e){
e.printStackTrace();
} finally {
try {
if (conn != null && conn.isClosed() == false)
//5.关闭连接,释放资源
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
BTW:对创建和关闭进行方法封装
package com.imooc.jdbc.common;
import java.sql.*;
public class DbUtils {
/**
* 创建新的数据库连接
* @return 新的Connection对象
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//1.加载注册
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:5001/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai",
"root", "sbt123");
return conn;
}
/**
* 关闭连接,释放资源
* @param rs 结果集对象
* @param stmt Statement对象
* @param conn Connection对象
*/
public static void closeConnection(ResultSet rs, Statement stmt, Connection conn){
//5.conn.close()
try {
if(rs != null) {
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try{
if(stmt != null) {
stmt.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null && !conn.isClosed())
//5.关闭连接,释放资源
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3.SQL注入攻击以及应对
3.1 SQL注入攻击
利用SQL漏洞越权获取数据的黑客行为,原因是未对原始SQL中的敏感字符做特殊处理
3.2 应对:使用PreparedStatement处理SQL
PreparedStatement是 Statement子接口,通过对SQL进行参数化,预防SQL注入攻击,且比后者执行效率更高。
4. JDBC 写操作
Insert,Update,Delete都是如同下面一样,使用executeUpdate(),只是返回值不同,Insert成功返回值时1,Update返回修改的记录数,同时写操作是没有ResultSet返回对象的。
5.JDBC事务管理
事务依赖于数据库实现,MySQL通过事务区作为数据缓冲地带来实现,即实际操作是首先在事务区中运行,只有commit之后,才会一次性将所有在事务区的操作写入到真正的数据表中,之后清空事务区。回滚操作同理。
JDBC提供两种事务模式:自动提交,和手动提交
package com.imooc.jdbc.sample;
import com.imooc.jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* JDBC中的事务机制
*/
public class TransactionSample {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
//关闭默认的自动提交
conn.setAutoCommit(false);
String sql = "insert into emp (eno,ename,salary,dname) values(?,?,?,?)";
for (int i = 2001; i < 3001; i++) {
//测试有无事务机制的区别
// if (i == 2003) {
// throw new RuntimeException("插入失败");
// }
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, i);
pstmt.setString(2, "员工" + i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.executeUpdate();
}
//手动提交
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null && !conn.isClosed()) {
//回滚
conn.rollback();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
DbUtils.closeConnection(null, pstmt, conn);
}
}
}
6.基于实体类实现分页数据封装
将MySQL的查询的每一行数据 用一个实体类JavaBean来保存,然后用集合对象来保存所有数据
Employee.java
package com.imooc.jdbc.hrapp.entity;
import java.util.Date;
/**
* 员工实体类
*/
public class Employee {
public Employee() {
}
private Integer eno;
private String ename;
private Float salary;
private String dname;
private Date hiredate;
public Integer getEno() {
return eno;
}
public void setEno(Integer eno) {
this.eno = eno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Float getSalary() {
return salary;
}
public void setSalary(Float salary) {
this.salary = salary;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
}
PaginationCommand.java
package com.imooc.jdbc.hrapp.command;
import com.imooc.jdbc.common.DbUtils;
import com.imooc.jdbc.hrapp.entity.Employee;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class PaginationCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入页号:");
int page = in.nextInt();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Employee> list = new ArrayList<>();
try {
conn = DbUtils.getConnection();
String sql = "select * from emp limit ?,10";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,(page -1)*10);
rs = pstmt.executeQuery();
while(rs.next()){
Integer eno = rs.getInt("eno");
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
Date hiredate = rs.getDate("hiredate"); //Java.sql.date
Employee emp = new Employee();
emp.setEno(eno);
emp.setEname(ename);
emp.setSalary(salary);
emp.setDname(dname);
emp.setHiredate(hiredate);
list.add(emp);
}
System.out.println(list.size());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
DbUtils.closeConnection(rs,pstmt,conn);
}
}
}
7.JDBC中的Date日期对象的处理
-
在MySQL数据库中,表示日期的数据类型是 Date/Datetime;
-
在实体类定义日期属性用的类是
Java.util.Date
; -
在JDBC中的
rs.getDate()
返回的是java.sql.Date
,继承自Java.util.Date
。
package com.imooc.jdbc.hrapp.command;
import com.imooc.jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class InsertCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工编号: ");
int eno = in.nextInt();
System.out.println("请输入员工姓名: ");
String ename = in.next();
System.out.println("请输入员工薪资: ");
float salary = in.nextFloat();
System.out.println("请输入隶属部门: ");
String dname = in.next();
System.out.println("请输入入职日期: ");
String strHiredate = in.next();
//String到java.sql.Date分两步:
//1.String→java.util.Date
java.util.Date udHiredate = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
udHiredate = sdf.parse(strHiredate);
} catch (ParseException e) {
e.printStackTrace();
}
//2.java.util.Date转为java.sql.Date
long time = udHiredate.getTime();// 获取自1970年到现在的毫秒数
java.sql.Date sdHiredate = new java.sql.Date(time);
Connection conn =null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
try {
conn = DbUtils.getConnection();
String sql = "insert into emp(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,eno);
pstmt.setString(2,ename);
pstmt.setFloat(3,salary);
pstmt.setString(4,dname);
pstmt.setDate(5, sdHiredate); //java.sql.Date
int cnt = pstmt.executeUpdate(); //cnt=1 所有修改操作都是Update
System.out.println("cnt: " + cnt);
System.out.println(ename + "员工入职手续已办理");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DbUtils.closeConnection(null, pstmt, conn);
}
}
}
JDBC批处理
Batch:重复执行相同sql语句多次,比如 Insert 1000条数据,使用批处理节省时间和资源。批处理创建一次 PreparedStatement对象,执行许多次语句,而普通的是创建多个PreparedStatement对象,每个对象执行一次任务。
package com.imooc.jdbc.sample;
import com.imooc.jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
/**
* JDBC中的事务机制
*/
public class BatchSample {
//标准方式插入若干数据
private static void tc1(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
long startTime = new Date().getTime();
conn = DbUtils.getConnection();
//默认自动提交
conn.setAutoCommit(false);//关闭自动提交
String sql = "insert into emp (eno,ename,salary,dname) values(?,?,?,?)";
for (int i = 100000; i < 200000; i++) {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, i);
pstmt.setString(2, "员工" + i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.executeUpdate();
}
conn.commit();//手动提交
long endTime = new Date().getTime();
System.out.println("tc1()执行时长:" + (endTime - startTime));
} catch(Exception e){
e.printStackTrace();
try {
if(conn != null && !conn.isClosed()){
conn.rollback(); //回滚
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
// 批处理插入数据
private static void tc2(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
long startTime = new Date().getTime();
conn = DbUtils.getConnection();
//默认自动提交
conn.setAutoCommit(false);//关闭自动提交
String sql = "insert into emp (eno,ename,salary,dname) values(?,?,?,?)";
pstmt = conn.prepareStatement(sql);
for (int i = 200000 ; i <300000; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "员工" + i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.addBatch(); //将参数加如批处理任务
//pstmt.executeUpdate();
}
pstmt.executeBatch();
conn.commit();//手动提交
long endTime = new Date().getTime();
System.out.println("tc2()执行时长:" + (endTime - startTime));
} catch(Exception e){
e.printStackTrace();
try {
if(conn != null && !conn.isClosed()){
conn.rollback(); //回滚
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
public static void main(String[] args) {
tc1();
tc2();
// tc1()执行时长:13554
// tc2()执行时长:8276
}
}
8.连接池的使用
连接池(连接仓库):数据库连接的管理者,分配者。每一次Java应用的中JDBC程序要使用的数据库,不是通过自己创建,而是从连接池中获取,用完之后不释放资源,而是再还给连接池,由其分配给其他程序使用,即:
连接池负责创建管理连接,程序只负责取用与归还
8.1 Druid连接池
具体配置参考忽一刀
重要的就是配置文件 druid-config.properties
里面的两个配置信息:
initialSize=10
maxActive=20
就是一开始程序启动的时候,就创建10个连接的连接池,后面根据需要最多可以保持20个连接,多了就只能等待了,因此设置最大数量必须要慎重(最忙碌的时候有多少人使用)。
8.2 C3PO连接池
略
9.Apache Commons DBUtils
更加便捷易用的组件,节省代码编写
package com.imooc.jdbc.sample;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.imooc.jdbc.hrapp.entity.Employee;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
/**
* Apache DBUtils + Druid联合使用演示
*/
public class DbUtilsSample {
/**
* query
*/
private static void query() {
Properties properties = new Properties();
String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
try {
propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
properties.load(new FileInputStream(propertyFile));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
QueryRunner qr = new QueryRunner(dataSource);
List<Employee> list = qr.query("select * from emp limit ?,10", new BeanListHandler<>(Employee.class),
new Object[]{10});
for (Employee emp : list) {
System.out.println(emp.getEname());
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Update
private static void update() {
Properties properties = new Properties();
String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
Connection conn = null;
try {
propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
properties.load(new FileInputStream(propertyFile));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
conn = dataSource.getConnection();
conn.setAutoCommit(false);
String sql1 = "update emp set salary=salary+1000 where eno=?";
String sql2 = "update emp set salary=salary-1000 where eno=?";
QueryRunner qr = new QueryRunner();
qr.update(conn,sql1,new Object[]{1000});
qr.update(conn,sql2,new Object[]{1001});
conn.commit();
} catch (Exception e) {
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void main(String[] args) {
query();
update();
}
}