人力资源管理案例
JDBC操作所有案例代码:https://gitee.com/pikachu2333/jdbc-study
文章目录
数据库信息
数据库:company
数据表:employee
账号:root
密码:root
数据表结构和内容:
CREATE TABLE employee(
eno INT PRIMARY KEY,
ename VARCHAR(20),
salary FLOAT,
dname VARCHAR(40)
);
INSERT INTO employee(eno,ename,salary,dname) VALUES(3308,'张三',6000,'研发部');
INSERT INTO employee(eno,ename,salary,dname) VALUES(3420,'李四',8700.0,'研发部');
INSERT INTO employee(eno,ename,salary,dname) VALUES(3610,'王五',4550.5,'市场部');
JDBC中Date日期对象的处理
在原有的数据表上增加了hiredate入职日期字段date类型,date和datetime区别是date只保存日期,datetime保存到时间
功能/案例:
-
实现JDBC对数据库的增删改查
-
使用PreparedStatement避免SQL注入的风险
-
事务案例:实现批量增加员工
-
DbUtils封装工具类简化数据库连接和关闭的操作
-
数据分页查询处理: 基于实体类实现分页数据封装
-
对java和数据库Date日期对象处理
-
批处理方式插入大量数据;显著提升效率
-
Druid连接池的配置与使用
-
c3p0连接池的配置与使用
-
Apache Commons DBUtils 和 Druid联合使用演示
JDBC
- Java数据库连接 Java DataBase Connectivity
- JDBC可以让Java通过程序操作关系型数据库
- JDBC基于驱动程序实现与数据库的连接与操作
JDBC驱动程序
平台提供标准,厂商进行实现
JDBC的优点
- 统一的API,提供一致的开发过程
- 易于学习,容易上手,代码结构稳定
- 功能强大,执行效率高,可处理海量数据
JDBC开发流程
- 加载并注册JDBC驱动
- 创建数据库连接
- 创建Statement对象
- 遍历查询结果
- 关闭连接,释放资源
创建数据库选择utf8mb4字符集,它是用4个字符表示的,而utf8是3个字符,更全面的包含了生僻字
JDBC驱动
获取JDBC驱动jar
https://www.mysql.com/cn/products/connector/
选择java语言的JDBC Driver for MySQL (Connector/J)选项下载
进入下载页面,Connector没有提供Windows平台的下载,不过没有关系,因为java是跨平台的,我们选择任何一个平台下载,底层生成的字节码都是一样的,所以这里选择一个Ubuntu Linux平台的,下载第一个最新的
使用解压软件打开压缩的包,里面又包含dat.tar.xz压缩包,一直打开文件夹找到java jar包
mysql-connector-java-8.0.26.jar
将jar包添加到IDEA,添加刚刚的jar包
创建数据库连接代码
String dbDriver = "com.mysql.cj.jdbc.Driver"; //JDBC驱动类
String dbURL = "jdbc:mysql://localhost:3306/company"; //连接字符串
String dbUsername = "root";
String dbPassword = "123456";
// 1. 加载并注册JDBC驱动
Class.forName(dbDriver);
// 2. 创建数据库连接
Connection connection = DriverManager.getConnection(dbURL, dbUsername, dbPassword);
Class.forName作用
- Class.forName用于加载指定的JDBC驱动类
- Class.forName本质是通知JDBC注册这个驱动类
- 驱动由数据库厂商自行开发,连接字符串也不同
数据库 | JDBC驱动类 | 连接字符串 |
---|---|---|
MySQL 5 | com.mysql.jdbc.Driver | jdbc:mysql://主机ip:端口/数据库名 |
MySQL 8 | com.mysql.cj.jdbc.Driver | jdbc:mysql://主机ip:端口/数据库名 |
Oracle | oracle.jdbc.driver.OracleDriver | jdbc:oracle:thin:@主机ip:端口:数据库名 |
SQL Server | com.mircosoft.sqlserver.jdbc.SQLServerDriver | jdbc:mircosoft:sqlserver:主机ip:端口;databasename=数据库名 |
DriverManager
- DriverManager用于注册/管理JDBC程序
- DriverManager.getConnection(连接字符串,用户名,密码)
- 返回值Connection对象,对应数据库的物理网络连接
Connection对象
- Connection对象用于JDBC与数据库的网络通信对象
- java.sql.Connection是一个接口,具体由驱动厂商实现
- 所有数据库的操作都建立在Connection基础上
JDBC程序的本质是一个C/S架构的模式,网络通信基于TCP/IP协议,在这个协议基础之上JDBC提供了一个接口对其进行对应,这个接口就是Connection,而这个接口的具体实现者就是MySQL厂商,在MySQL驱动程序中实现如何进行数据的传输和加密。每一个JDBC的应用程序可以建立多个Connection连接对象。
MySQL连接字符串
- 格式:jdbc:mysql://[主机ip][:端口]/数据库名?参数列表
- 主机ip与端口是可选设置,默认值为127.0.0.1与3306(不推荐使用)
- 参数列表采用url编码,格式:参数1=值1&参数2=值2&…
MySQL连接字符串常用参数
参数名 | 建议参数值 | 说明 |
---|---|---|
useSSL | true(生产) false(开发) | 是否禁用ssl |
useUnicode | true | 使用unicode编码传输数据 |
characterEncoding | UTF-8 | 使用UTF-8编码传输数据 |
serverTimezone | Asia/Shanghai | 使用东8时区时间,UTC+8 |
allowPublicKeyRetrieval | true | 允许从客户端获取公钥加密传输 |
SSL安全套接层,在网络传输过程中,使用非对称加密的方式对数据加密传输,如果开发的时候设置为false,如果在线上安全性更高,而且还要申请相应的SSL证书建议开启。
如果在服务器MySQL数据库默认设置的serverTimezone就是Asia/Shanghai,那么在连接字符串中就可以省略,同理如果allowPublicKeyRetrieval服务器端默认开启,也可以省略掉。
初学者建议都写加深印象
SQL注入攻击
- SQL注入攻击是指利用SQL漏洞越权获取数据的黑客行为
- SQL注入共计根源是未对原始SQL中敏感字符做特殊处理
- 解决方法:放弃Statement改用PreparedStatement处理SQL
原始代码:存在SQL注入风险
//dname值为' or 1=1 or 1='时,所有筛选条件均失效
//SQL: select * from employee where dname = '' or 1=1 or 1=''
//dname = ' or 1=1 or 1=';
String sql = "select * from employee where dname='"+dname+"'"
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
...
}
PreparedStatement
- PreparedStatement预编译是Statement是Statement的子接口
- PreparedStatement对SQL进行参数化,预防SQL注入攻击
- PreparedStatement比Statement执行效率更高
新的代码:利用PreparedStatement预防SQL注入风险
//dname值为' or 1=1 or 1='时,所有筛选条件均失效
//SQL: select * from employee where dname = '\' or 1=1 or 1=\''
String sql = "select * from employee where dname=?"
PreparedStatement pstmt = conn.createStatement(sql);
pstmt.setString(1,dname); //设置SQL参数,参数从1开始
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
...
}
JDBC事务管理方式
- 事务是以一种可靠的、一致的方式,访问和操作数据库的程序单元
- 说人话:要么把事情做完,要么什么都不做,不要做一半
- 事务依赖于数据库实现,MySQL通过事务区作为数据缓冲地带
案例:银行转账
事务的提交操作
- 账户1加操作提交到事务区
- 账户2减操作提交到事务区
- commit提交,事务一次性写入数据表
- 清空事务区
事务的回滚操作
- 数据更新操作提交到事务区
- rollback回滚,取消数据的更新
- 清空事务区
JDBC两种事务模式
自动提交事务模式
- 每一次执行写操作SQL,自动提交事务
- 自动开启提交方法:conn.setAutoCommit(true)
- 自动事务是JDBC默认行为,此模式无法保证多数据一致性
手动提交事务模式
- 指显示调用commit()与rollback()方法
- 手动提交开启方法:conn.setAutoCommit(false)
- 手动提交事务可保证多数据一致性,但必须手动调用提交/回滚方法
连接池
阿里巴巴Druid连接池
https://github.com/alibaba/druid
- Druid是阿里巴巴开源连接池组件,是最好的连接池之一
- Druid对数据库连接进行有效管理与重用,最大化程序执行效率
- 连接池负责创建关联连接,程序只负责取用与归还
druid-config.properties 配置连接池,初始化连接数和最大连接数最好一样,这样用户连接的时候直接调用线程快速连接
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/company?useSSL=false&useUnicode=true&characterEncoding=UTF-8&ServerTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username=root
password=root
initialSize=20
maxActive=20
C3P0连接池
https://www.mchange.com/projects/c3p0/
C3P0连接池在14年之前一些库存的历史项目用的比较多,而14年之后Driver连接池用的多
使用c3p0-config.xm配置
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/company?useSSL=false&useUnicode=true&characterEncoding=UTF-8&ServerTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">10</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
Apache Commons DBUtils
- commons-dbutils是Apache提供的开源JDBC工具库
- 他是对JDBC的简单封装,学习成本极低
- 使用commons-dbutils可以极大简化JDBC编码工作量
http://commons.apache.org/proper/commons-dbutils/
Apache Commons DBUtils 和 Druid联合使用演示
package Sample;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import hrapp.entity.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
/**
* Apache Commons DBUtils 和 Druid联合使用演示
*/
public class DBUtilsSample {
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);
//执行query方法时DBUtils会创建一个连接,执行完毕后自动关闭
List<Employee> employeeList = qr.query("select * from employee limit ?,10", new BeanListHandler<>(Employee.class), new Object[]{10});
for (Employee emp : employeeList) {
System.out.println(emp.getEname());
}
} catch (Exception e) {
e.printStackTrace();
}
}
public 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 employee set salary=salary+1000 where eno=?";
String sql2 = "update employee set salary=salary-500 where eno=?";
QueryRunner qr = new QueryRunner(); //写入时不用传入数据源对象
qr.update(conn, sql1, new Object[]{1010});
qr.update(conn, sql2, new Object[]{1011});
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
try {
if (conn != null && !conn.isClosed()) {
conn.close(); //对连接进行回收(注意并不是关闭)
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void main(String[] args) {
// query();
update();
}
}