JDBC
程序员文章站
2022-05-04 09:55:35
...
JDBC
一、引言
JavaWeb学习思维导图
1.1 如何操作数据
使用客户端工具访问数据库，需要手工建立链接，输入用户名和密码登录，编写SQL语句，点击执行，查看操作结果（结果集或受影响行数）。
1.2 实际开发中，会采用客户端操作数据库吗？
在实际开发过程中，当用户的数据发生改变时，不可能通过客户端操作执行SQL语句，因为操作量过大！无法保证效率和正确性
二、JDBC（Java DataBase Connectivity）
2.1 什么是JDBC？
JDBC（Java DataBase Connectivity） Java连接数据库，可以使用Java语言连接数据库完成CRUD操作
2.2 JDBC核心思想
Java中定义了访问数据库的接口，可以为多种关系型数据库提供统一的访问方式。
由数据库厂商提供驱动实现类(Driver数据库驱动)
2.2.1 MySQL数据库驱动
- mysql-connector-java-5.1.X 适用于5.X版本
- mysql-connector-java-8.0.X 适用于8.X版本
2.2.2 JDBC API
JDBC 是由多个接口和类进行功能实现
| 类型 | 全限定名 | 简介 |
|---|---|---|
| class | java.sql.DriverManager | 管理多个数据库驱动类，提供了获取数据库连接的方法 |
| interface | java.sql.Connection | 代表一个数据库连接(当Connection不是NULL时，表示已连接一个数据库) |
| interface | java.sql.Statement | 发送SQL语句到数据库的工具 |
| interface | java.sql.ResultSet | 保存SQL查询语句的结果数据(结果集) |
| class | java.sql.SQLException | 处理数据库应用程序时所发生的异常 |
2.3 环境搭建
- 在项目下新建 lib 文件夹，用于存放 jar 文件
- 将MySQL驱动文件mysql-connector-java-5.1.25-bin.jar 复制到项目的lib文件夹中
- 选中lib文件夹 右键选择 add as library，点击OK
三、JDBC开发步骤【重点】
3.1 注册驱动
使用Class.forName(“com.mysql.jdbc.Driver”);手动加载字节码文件到JVM中
Class.forName("com.mysql.jdbc.Driver");
3.2 连接数据库
通过DriverManager.getConnection(url,user,password);获得数据库连接对象
URL:jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8
user:root
password:123456
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8","root","123456");
3.3 获取发送SQL的对象
通过Connection对象获得Statement对象，用于对数据库进行通用访问的
Statement statement = connection.createStatement();
3.4 执行SQL语句
编写SQL语句，并执行，接收执行后的结果
int result = statement.executeUpdate("INSERT INTO grade(GradeName) VALUES ('NZ2008');“);
3.5 处理结果
接收并处理操作结果
if(result>0){
System.out.println("执行成功！");
}
3.6 释放资源
遵循的是先开后关的原则，释放过程中用到的所有资源对象
statement.close();
connection.close();
3.7 综合案例
综合核心六步：实现增、删、改
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//将驱动字节码文件加载到JVM中
//2.连接数据库
String url = "jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8";//数据库连接地址
String user = "root";//用户名
String password ="123456";//密码
Connection connection = DriverManager.getConnection(url,user,password);
//3.获取发送SQL语句的对象 Statement
Statement statement = connection.createStatement();
//4.编写SQL语句,并执行SQL语句
// String sql = "INSERT INTO grade(GradeName) VALUES ('NZ2008');";
// String sql = "delete from grade where GradeName='NZ2008'";
String sql = "update grade set GradeName = 'NZ2008' WHERE GradeId='6' ";
int result = statement.executeUpdate(sql);
//5.处理结果
if(result>0){
System.out.println("执行成功！");
}else{
System.out.println("执行失败");
}
//6.释放资源 先开后管
statement.close();
connection.close();
}
}
四、ResultSet（结果集）
在执行查询SQL后，存放查询的结果集数据
4.1 接收结果集
ResultSet resultSet = statement.executeQuery(select GradeId, GradeName from grade);
4.2 遍历ResultSet中的数据
ResultSet以表（Table）结构进行临时结果的存储，需要通过JDBC API将其中的数据进行依次获取
- 数据行指针：初始行位置在第一行数据前，没调用一次boolean next()方法，ResultSet中指针向下移动一行，结果为true，表示当前行有数据
- rs.getXxx(“列名”); 根据列名获得数据
- rs.getXxx(整数下标);代表根据列的编号顺序获得！从1开始
boolean next() throws SQLException;//判断rs结果集中下一行是否有数据
4.2.1 遍历方法
int getInt(int columnIndex)throws SQLException;//获得当前行的第N列的int值
int getInt(String columnLable)throws SQLException;//获得当前行columnLable列的int值
4.3 综合案例
对grade表中所有数据进行遍历
4.3.1 根据列的名称获取
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestDql {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2,获得连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8","root","123456");
//3.获得执行SQL的对象
Statement statement = connection.createStatement();
//4.编写SQL语句
String sql = "select GradeId, GradeName from grade";
ResultSet resultSet = statement.executeQuery(sql);
//5.处理结果（结果集）
while(resultSet.next()){//判断结果集是否有下一行
//1.根据列名获取当前每一列的数据
Integer GradeId = resultSet.getInt("GradeId");
String gradeName = resultSet.getString("GradeName");
System.out.println(GradeId+"\t"+gradeName);
}
//6.释放资源
resultSet.close();
statement.close();
connection.close();
}
}
4.3.2 根据列的下标获取
//5.处理结果（结果集）
while(resultSet.next()){//判断结果集是否有下一行
//1.根据列名获取当前每一列的数据
Integer GradeId = resultSet.getInt(1);
String GradeName = resultSet.getString(2);
System.out.println(GradeId+"\t"+GradeName);
}
五、常见错误
- java.lang.ClassNotFoundException 找不到类(类名书写错误，没有导入jar包)
- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException与SQL语句相关的错误（表名、列民书写错误、约束错误、插入的值是String类型但是没加单引号）建议：在客户端工具中测试sql语句后，再粘贴到代码中来
- com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘NZ2008’ for key ‘GradeName’ 原因：主键值已存在，要改插入的主键值，或者插入了列约束UNIQUE重复值
- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorExceptin:Unknown column “password” in
- 可能输入的值的类型不对，确定插入元素时，对应的值的类型的是否正确。
六、综合案例
6.1 创建表
创建一张用户表 User
id 主键、自动增长
username 字符串类型 非空
password 字符串类型 非空
phone 字符串类型
插入两条测试语句
create table user(
userId int primary key auto_increment,
username varchar(20) not null,
password varchar(18) not null,
address varchar(100),
phone varchar(11)
)charset=utf8;
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('张三','123','北京昌平沙河','13812345678');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五','5678','北京海淀','13812345141');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('赵六','123','北京朝阳','13812340987');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('田七','123','北京大兴','13812345687');
6.2 实现登录
- 通过控制台，用户输入用户名和密码
- 用户输入的用户名和密码作为参数，编写查询SQL语句
- 如果查询到用户，则用户存在，提示登录成功，反之，提示失败
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class TestLogin {
public static void main(String[] args) throws Exception{
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2,获得连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8","root","123456");
//3.获得执行SQL的对象
Statement statement = connection.createStatement();
//4.编写SQL语句
String sql = "select * from user where username = '" +username+"' and password ='"+password+"'";
//String sql = "select * from user where username = 'xxx' or 1=1 ;# 'and password ='123456';
ResultSet resultSet = statement.executeQuery(sql);
//5.处理结果（结果集）
if(resultSet.next()){//通过参数，查找了一行数据 提示用户登录成功
System.out.println("登录成功！");
}else{
System.out.println("登录失败");
}
//6.释放资源
resultSet.close();
statement.close();
connection.close();
}
}
七、SQL注入问题
7.1 什么是SQL注入
当用户输入的数据中有SQL关键字或语法时，并且参与了SQL语句的编译，导致SQL语句编译后条件结果为true,一直得到正确的结果，称为SQL注入
7.2 如何避免SQL注入
由于编写的SQL语句，是在用户输入数据后，整合后再编译成SQL语句，所以为了避免SQL注入的问题，使SQL语句在用户输入数据前，SQL语句已经完成编译，成为了完整的SQL语句，再进行填充数据
八、PreparedStatement【 重点】
PreparedStatement接口继承了Statement接口，执行SQL语句的方法没有区别！
8.1 PreparedStatement的应用
作用: 1.预编译SQL语句，效率高！
2.安全，避免SQL注入
3.可以动态的填充数据，执行多个同构的SQL语句
public class TestLogin {
public static void main(String[] args) throws Exception{
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2,获得连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8","root","123456");
//3.获得执行SQL的对象
String sql = "select * from user where username = ? and password =?" ;
PreparedStatement preparedStatement = connection.prepareStatement(sql);//预编译SQL语句
//为占位符下标赋值
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
//4.执行SQL语句
ResultSet resultSet = preparedStatement.executeQuery();
//5.处理结果（结果集）
if(resultSet.next()){//通过参数，查找了一行数据 提示用户登录成功
System.out.println("登录成功！");
}else{
System.out.println("登录失败");
}
//6.释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}
8.1.1 参数标记
//1.预编译SQL语句
PreparedStatement preparedStatement = connection.prepareStatement(sql);
8.1.2 动态参数绑定
pstmt.setXxx(下标,值)；参数下标是从1开始，为指定占位符下标绑定值
//2.为展位符下标赋值
pstmt.setString(1,username);
pstmt.setString(2,password);
九、综合案例
9.1 创建数据库、表
数据库Account
- 创建一张表 Account，有以下列
- cardId:字符串，主键
- password：字符串，非空
- username：字符串，非空
- balance：小数，非空
- phone：字符串，非空
CREATE DATABASE Account CHARACTER SET utf8;
USE Account;
CREATE TABLE t_account(
cardId VARCHAR(20) PRIMARY KEY,
PASSWORD VARCHAR(20) NOT NULL,
username VARCHAR(10) NOT NULL,
balance DOUBLE NOT NULL,
phone VARCHAR(11)
)CHARSET = utf8;
9.2 创建项目通过JDBC实现功能
创建AccountSystem类，完成下列功能
- 开户：控制台输入所有的账号信息，使用PreparedStatement添加至t_account表
- 存款：控制台输入卡号、密码、存储金额进行进行修改
- 取款：输入卡号、密码、取款金额
- 转账：输入卡号、密码、对方卡号、转账金额进行修改
- 修改密码：控制台进行输入卡号、密码、再输入新密码进行修改
- 注销：控制台输入卡号、密码、删除对应的账户信息
import java.sql.*;
import java.util.Scanner;
/**
* - 开户：控制台输入所有的账号信息，使用PreparedStatement添加至t_account表
* - 存款：控制台输入卡号、密码、存储金额进行进行修改
* - 取款：输入卡号、密码、取款金额
* - 转账：输入卡号、密码、对方卡号、转账金额进行修改
* - 修改密码：控制台进行输入卡号、密码、再输入新密码进行修改
* - 注销：控制台输入卡号、密码、删除对应的账户信息
*/
public class AccountSystem {
Scanner scanner = new Scanner(System.in);
private static Connection connection = null;
PreparedStatement preparedStatement=null ;
ResultSet resultSet =null ;
static {
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8", "root", "123456");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
//开户
public void register() {
System.out.println("请输入卡号:");
String cardId = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
System.out.println("请输入用户名:");
String username = scanner.next();
System.out.println("请输入存款金额:");
double balance = scanner.nextDouble();
System.out.println("请输入预留手机号:");
String phone = scanner.next();
try {
//3.创建PreparedStatement
String sql = "insert into t_account(cardId,password,username,balance,phone)value(?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
//4.为占位符赋值
preparedStatement.setString(1, cardId);
preparedStatement.setString(2, password);
preparedStatement.setString(3, username);
preparedStatement.setDouble(4, balance);
preparedStatement.setString(5, phone);
//5.执行SQL语句
int i = preparedStatement.executeUpdate();
//6.处理结果
if (i > 0) {
System.out.println("开户成功！");
} else {
System.out.println("开户失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally { //6.释放资源
try {
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//存款
public void saveMoney() {
System.out.println("请输入卡号:");
String cardId = this.scanner.next();
System.out.println("请输入密码:");
String password = this.scanner.next();
System.out.println("请输入存款金额:");
double money = this.scanner.nextDouble();
if (money > 0) {
//存款操作
String sql = "update t_account set balance = balance + ? where cardId=? and password=?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setDouble(1, money);
preparedStatement.setString(2, cardId);
preparedStatement.setString(3, password);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("存款成功！");
} else {
System.out.println("存款失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} else {
System.out.println("输入的金额不正确");
}
}
//取款
public void takeMoney() {
System.out.println("请输入卡号:");
String cardId = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
System.out.println("请输入取款金额:");
double money = scanner.nextDouble();
if (money > 0) {
String sql = "select balance from t_account where cardId=? and password= ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, cardId);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
double balance = resultSet.getDouble(1);
if (money <= balance) {
//取款操作
String sql2 = "update t_account set balance = balance - ? where cardId=? and password= ?";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.setDouble(1, money);
preparedStatement.setString(2, cardId);
preparedStatement.setString(3, password);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("取款成功！");
}
} else {
System.out.println("您的余额不足！");
}
} else {
System.out.println("请核实用户名或密码");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (this.resultSet != null) {
this.resultSet.close();
}
if (this.preparedStatement != null) {
this.preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
} else {
System.out.println("您的金额不正确");
}
}
//转账
public void transfer(){
System.out.println("请输入您的卡号:");
String cardId = scanner.next();
System.out.println("请输入您的密码:");
String password = scanner.next();
System.out.println("请输入转账金额:");
double money = scanner.nextDouble();
if (money > 0) {
//查询用户自身
String sql = "select balance from t_account where cardId = ? and password = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, cardId);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
double balance = resultSet.getDouble("balance");
if (money <= balance) {
System.out.println("请输入对方卡号");
String toCardId = scanner.next();
String ss = "select * from t_account where cardId=?";
preparedStatement = connection.prepareStatement(ss);
preparedStatement.setString(1, toCardId);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
String sql2 = "update t_account set balance = balance - ? where cardId = ?";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.setDouble(1, money);
preparedStatement.setString(2, cardId);
preparedStatement.executeUpdate();
String sql3 = "update t_account set balance = balance + ? where cardId = ?";
preparedStatement = connection.prepareStatement(sql3);
preparedStatement.setDouble(1, money);
preparedStatement.setString(2, toCardId);
preparedStatement.executeUpdate();
}else{
System.out.println("对方账户不存在");
}
}
} else {
System.out.println("卡号或密码错误");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if ( this.preparedStatement != null) {
this.preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
} else {
System.out.println("转账金额输入错误！");
}
}
//修改密码
public void updatePwd() {
System.out.println("请输入卡号:");
String cardId = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
String sql = "select * form t_account where cardId= ? and password = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, cardId);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println("请输入新密码");
String newPwd = scanner.next();
String sql2 = "update t_account set password = ? where cardId= ?";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.setString(1, newPwd);
preparedStatement.setString(2, cardId);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
} else {
System.out.println("请核对卡号或密码！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (this.resultSet != null) {
this.resultSet.close();
}
if (this.preparedStatement != null) {
this.preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//注销
public void destroy() {
System.out.println("请输入卡号:");
String cardId = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
System.out.println("确定要注销账户吗？");
String answer = scanner.next();
if(answer.equals("y")){
String sql = "delete from t_account where cardId= ? and password = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,cardId);
preparedStatement.setString(2,password);
int i = preparedStatement.executeUpdate();
if(i>0){
System.out.println("注销成功");
}else{
System.out.println("注销失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}else{
System.out.println("退出注销！");
}
}
//只服务关闭
public void closeConnecttion() {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
十、封装工具栏
10.1 重用性方案
- 封装了获取连接、释放资源的两个方法
- 提供 public static Connection getConnection()方法
- 提供public static void closeALL(Connection connection, Statement statement, ResultSet resultSet)
import java.sql.*;
/**
* 数据库工具类
* 1.提供连接-->Connection
* 2.提供统一资源关闭
* 可重用性方案
*/
public class DBUtils {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//硬编码
//获得连接
public static Connection getConnection(){
Connection connection=null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8","root","123456");
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//释放资源
public static void closeALL(Connection connection, Statement statement, ResultSet resultSet){
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
}
10.2 跨平台方案
定义private static final Properties properties = new Properties();配置文件集合
定义static{
//首次使用工具类、触发类加载
InputStream is =DBUtils.class.getResourceAsStream("/db.properties");//复用本类自带流，读取配置文件
properties.load(is);//将is流中的配置文件信息，加载到集合中
Class.forName(properties.getProperty(“driver”));
}
在getConnection方法中，应用properties.getProperty(“url”), properties.getProperty(“username”), properties.getProperty(“password”)
10.2.1 实现
在src目录下新建db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8
username=root
password=123456
DBUtils代码实现
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
*数据库工具类
* 1.提供连接-->Connection
* 2.释放资源
* 可跨平台方案
*/
public class DBUtils {
private static final Properties properties = new Properties();
static {
try {
//适用类自身自带的流
InputStream is =DBUtils.class.getResourceAsStream("/db.properties");
properties.load(is);//通过流，将配置信息的内容分割成键值对
Class.forName(properties.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection connection=null;
try {
connection = DriverManager.getConnection( properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//释放资源
public static void closeALL(Connection connection, Statement statement, ResultSet resultSet){
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
}
十一、ORM
ORM(Object Relational Mapping)
从数据库查询到的结果集[ResultSet]在进行遍历时，逐行遍历，取出的都是零散的数据，在实际应用开发中，我们需要将零散的数据进行封装整理
CREATE TABLE `user`(
id int primary key,
username varchar(20) not null,
password varchar(20) not null,
sex char(2),
email varchar(50) not null,
address varchar(20) not null
)CHARSET = utf8;
11.1 ORM实体(entity):零散数据的载体
- 一行数据中，多个零散的数据进行整理
- 通过entity的规则对表中的数据进行对象的封装
- 表名=类名；列民=属性名；提供各个属性的get、set方法
- 提供无参构造方法、（视情况添加有参构造）
11.1 ORM应用
/**
* id INT PRIMARY KEY,
* username VARCHAR(20) NOT NULL,
* PASSWORD VARCHAR(20) NOT NULL,
* sex CHAR(2),
* email VARCHAR(50) NOT NULL,
* address VARCHAR(20) NOT NULL
*/
public class User {
private int id;
private String username;
private String password;
private String sex;
private String email;
private String address;
public User(){}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", sex='" + sex + '\'' +
", email='" + email + '\'' +
", address='" + address + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public User(int id, String username, String password, String sex, String email, String address) {
this.id = id;
this.username = username;
this.password = password;
this.sex = sex;
this.email = email;
this.address = address;
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class OrmSelect {
public static void main(String[] args) {
Connection connection = DBUtils.getConnection();
String sql = "select id,username,PASSWORD,sex,email,address from `user`;";
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
System.out.println(preparedStatement);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {//拿到每一行数据、
//拿到每一列的数据
User user = new User();
int id = resultSet.getInt("id");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String sex = resultSet.getString("sex");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
//将一行中零散的数据，封装在一个User对象里。
user.setId(id);
user.setUsername(username);
user.setPassword(password);
user.setSex(sex);
user.setEmail(email);
user.setAddress(address);
System.out.println(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeALL(connection, preparedStatement, resultSet);
}
}
}
十二、DAO(Data Access Object)
数据访问对象
- 将所有对同一张表的操作都封装在一个XXXDaoImpl对象中。
- 根据增删改查的不同功能，实现具体的方法(insert,update,delete,select,selectAll)
- 经验：对于任何一张表中的数据进行操作时，无非就是增、删、改、查。应将对于一张表的所有操作统一封装在一个数据访问对象中。重用
import com.qf.t2.DBUtils;
import com.qf.t2.User;
import java.net.ConnectException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
*对数据库中User表的一系列操作。
* 只做对数据库访问的操作
* 复用！对同一张表的操作 实现复用
*/
public class UserDaoImpl {
private Connection connection = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
//增 int id,int username,String password,sex,email,address
public int insert(User user){
try {
connection = DBUtils.getConnection();
String sql = "insert into user(id,username,password,sex,email,address)values(?,?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,user.getId());
preparedStatement.setString(2,user.getUsername());
preparedStatement.setString(3,user.getPassword());
preparedStatement.setString(4,user.getSex());
preparedStatement.setString(5,user.getEmail());
preparedStatement.setString(6,user.getAddress());
int i = preparedStatement.executeUpdate();
return i;//将操作结果返回给调用者
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils.closeALL(connection,preparedStatement,null);
}
return 0;
}
//删
public int delete(int id){
connection = DBUtils.getConnection();
String sql = "delete from user where id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
return preparedStatement.executeUpdate() ;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeALL(connection,preparedStatement,resultSet);
}
return 0 ;
}
//改
public int update(User user){
connection = DBUtils.getConnection();
String sql = "update user username=?,password=?,sex=?,email=?,address=? where id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,user.getUsername());
preparedStatement.setString(2,user.getPassword());
preparedStatement.setString(3,user.getSex());
preparedStatement.setString(4,user.getEmail());
preparedStatement.setString(5,user.getAddress());
preparedStatement.setInt(6,user.getId());
return preparedStatement.executeUpdate() ;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeALL(connection,preparedStatement,resultSet);
}
return 0;
}
//查单个
public User select(int id){
connection = DBUtils.getConnection();
String sql = "select id,username,password,sex,email,address from user where id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id1 = resultSet.getInt("id");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String sex = resultSet.getString("sex");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
User user = new User(id1,username,password,sex,email,address);
return user;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeALL(connection,preparedStatement,resultSet);
}
return null;
}
//查多个
public List<User> selectAll(){
connection = DBUtils.getConnection();
String sql = "select id,username,password,sex,email,address from user";
List<User> userList = new ArrayList<>();
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String sex = resultSet.getString("sex");
String email = resultSet.getString("email");
String address = resultSet.getString("address");
User user = new User(id,username,password,sex,email,address);
userList.add(user);//每封装一个对象封装在集合中
}
return userList;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeALL(connection,preparedStatement,resultSet);
}
return null;
}
}
import com.qf.t2.User;
import java.util.List;
public class TestDao {
public static void main(String[] args) {
//调用新增功能
UserDaoImpl udi =new UserDaoImpl();
User user = new User(2,"kunyan2","123456","女","aaa@qq.com","wuhan");
// int result = udi.insert(user);
// if (result>0){
// System.out.println("新增成功");
// }else{
// System.out.println("新增失败");
// }
// int result = udi.delete(2);
// System.out.println(result);
// User user2 = new User(2,"kunyan2","123","女","aaa@qq.com","wuhan2");
// int result = udi.update(user2);
// System.out.println(result);
// User stu = udi.select(1);
// System.out.println(stu);
List<User> userList = udi.selectAll();
userList.forEach(System.out::println);
}
}
十三、日期类型
- java.util.Date
- Java语言常规应用层面的日期类型。可以通过字符串创建对应的时间对象
- 无法直接通过JDBC插入数据库
- java.sql.Date
- 不可以通过字符串创建对应的时间对象。只能通过毫秒值创建对象(1970年1月1日至今的毫秒值)
- 可以直接通过JDBC插入数据库
//1.java.util.Date
System.out.println(new java.util.Date());
// System.out.println("请输入入职日期：");
// String date = Scanner.next();
//自定义一个日期
String str = "1999-06-24";
//日期的转换 字符串转换为java.util.Date
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//将日期字符串转换成util.Date类型
java.util.Date parse = sdf.parse(str);
13.1 日期格式化工具
SimpleDateFormat 日期格式化
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//按照指定格式转换成util.Date类型
java.util.Date date = sdf.parse("2000-01-01");
13.2 日期工具类 DateUtil
package com.qf.day43.t4;
import java.text.ParseException;
import java.text.SimpleDateFormat;
/**
* 日期转换
* 字符串转UtilDate
* 字符串转SqlDate
* utilDate转成Sqldate
*/
public class DateUtils {
private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//字符串转Util
public static java.util.Date strToUtilDate(String str) {
try {
return simpleDateFormat.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
//字符串转sql
// public static java.sql.Date strToSqlDate(String str){
// SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
// try {
// java.util.Date date = simpleDateFormat.parse(str);
// return new java.sql.Date(date.getTime());
// } catch (ParseException e) {
// e.printStackTrace();
// }
// return null;
// }
//util转sql
public static java.sql.Date utilToSql(java.util.Date date){
return new java.sql.Date(date.getTime());
}
}
13.2.1 测试
package com.qf.day43.t4;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class TestDatetimes {
public static void main(String[] args) throws ParseException {
//1.java.util.Date 当前系统时间
// System.out.println(new java.util.Date());
//
// //自定义一个时间
// String str = "1999-09-09";
// //日期转换 字符串转为 java.util.Date
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// //将日期字符串转换成 util.Date类型
// java.util.Date utilDate = sdf.parse(str);
// System.out.println(utilDate);
//
// //sql.Date 需要毫秒值，来构建一个日期
// java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
// System.out.println(sqlDate);
//
//
// java.util.Date date = DateUtils.strToUtilDate("2002-3-18");
// System.out.println(date);
//
// java.sql.Date date2 = DateUtils.utilToSql(date);
// System.out.println(date2);
System.out.println(new java.util.Date());
System.out.println(new java.sql.Date(new java.util.Date().getTime()));
}
}
作业：user\userinfo
| 列名 | 类型 | 说明 |
|---|---|---|
| user_id | 整数、主键 | 用户编号 |
| user_name | 字符串，唯一，非空 | 用户名称 |
| user_pwd | 字符串，非空 | 用户密码 |
| user_borndate | DATE | 出生日期 |
| user_email | 字符串，非空 | 邮箱 |
| user_address | 字符串 | 地址 |
注意：采用DAO+Entity完成
com.qf.xxx.entity
User
com.qf.xxx.dao
UserDaoImpl
完成五个方法、增、删、改、查、查所有
CREATE DATABASE userinfo CHARACTER SET utf8;
USER userinfo;
CREATE TABLE userinfo(
user_id INT PRIMARY KEY,
user_name VARCHAR(20) UNIQUE NOT NULL,
user_pwd VARCHAR(20) NOT NULL,
user_borndate DATE,
user_email VARCHAR(30) NOT NULL,
user_address VARCHAR(20)
)CHARSET = utf8;
SELECT * FROM userinfo;
import java.text.ParseException;
import java.text.SimpleDateFormat;
public class DateUtils {
private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
public static java.util.Date strToUtil(String str){
try {
return simpleDateFormat.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
public static java.sql.Date utilToSql(java.util.Date date){
return new java.sql.Date(date.getTime());
}
}
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DBUtils {
private static final Properties PROPERTIES = new Properties();
static {
InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
try {
PROPERTIES.load(is);
Class.forName(PROPERTIES.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//释放资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.util.Date;
public class Userinfo {
private int user_id;
private String user_name;
private String user_pwd;
private Date user_borndate;
private String user_email;
private String user_address;
public Userinfo() {
}
public Userinfo(int user_id, String user_name, String user_pwd, Date user_borndate, String user_email, String user_address) {
this.user_id = user_id;
this.user_name = user_name;
this.user_pwd = user_pwd;
this.user_borndate = user_borndate;
this.user_email = user_email;
this.user_address = user_address;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getUser_pwd() {
return user_pwd;
}
public void setUser_pwd(String user_pwd) {
this.user_pwd = user_pwd;
}
public Date getUser_borndate() {
return user_borndate;
}
public void setUser_borndate(Date user_borndate) {
this.user_borndate = user_borndate;
}
public String getUser_email() {
return user_email;
}
public void setUser_email(String user_email) {
this.user_email = user_email;
}
public String getUser_address() {
return user_address;
}
public void setUser_address(String user_address) {
this.user_address = user_address;
}
@Override
public String toString() {
return "Userinfo{" +
"user_id=" + user_id +
", user_name='" + user_name + '\'' +
", user_pwd='" + user_pwd + '\'' +
", user_borndate=" + user_borndate +
", user_email='" + user_email + '\'' +
", user_address='" + user_address + '\'' +
'}';
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserinfoDaoImpl {
private Connection connection = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
public int insert(Userinfo userinfo) {
connection = DBUtils.getConnection();
String sql = "insert into userinfo (user_id,user_name,user_pwd,user_borndate,user_email,user_address)values(?,?,?,?,?,?)";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,userinfo.getUser_id());
preparedStatement.setString(2,userinfo.getUser_name());
preparedStatement.setString(3,userinfo.getUser_pwd());
preparedStatement.setDate(4,DateUtils.utilToSql(userinfo.getUser_borndate()));
preparedStatement.setString(5,userinfo.getUser_email());
preparedStatement.setString(6,userinfo.getUser_address());
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return 0;
}
public int update(Userinfo userinfo) {
connection = DBUtils.getConnection();
String sql = "update userinfo set user_name = ?,user_pwd = ?,user_borndate = ?,user_email = ?,user_address = ? where user_id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,userinfo.getUser_name());
preparedStatement.setString(2,userinfo.getUser_pwd());
preparedStatement.setDate(3,DateUtils.utilToSql(userinfo.getUser_borndate()));
preparedStatement.setString(4,userinfo.getUser_email());
preparedStatement.setString(5,userinfo.getUser_address());
preparedStatement.setInt(6,userinfo.getUser_id());
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return 0;
}
public int delete(int user_id) {
connection = DBUtils.getConnection();
String sql = "delete from userinfo where user_id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,user_id);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return 0;
}
public Userinfo select(String user_name) {
connection = DBUtils.getConnection();
String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from userinfo where user_name = ? ";
Userinfo userinfo = null;
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,user_name);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
userinfo = new Userinfo(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDate(4),resultSet.getString(5),resultSet.getString(6));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return userinfo;
}
public List<Userinfo> selectAll() {
connection = DBUtils.getConnection();
String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from userinfo ";
Userinfo userinfo = null;
List<Userinfo> userinfos = new ArrayList<>();
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
userinfo = new Userinfo(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDate(4),resultSet.getString(5),resultSet.getString(6));
userinfos.add(userinfo);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
return userinfos;
}
}
import java.util.List;
public class TestUserinfo {
public static void main(String[] args) {
//新增
UserinfoDaoImpl userinfoDao = new UserinfoDaoImpl();
// //Userinfo u1 = new Userinfo(1,"kunyan","123456",DateUtils.strToUtil("1999-03-03"),"aaa@qq.com","武汉");
// Userinfo u2 = new Userinfo(2,"kunyan1","123456",DateUtils.strToUtil("1999-03-03"),"aaa@qq.com","武汉");
// int result = userinfoDao.insert(u2);
// if(result>0){
// System.out.println("新增成功");
// }else{
// System.out.println("新增失败");
// }
//修改
// Userinfo u1 = new Userinfo(1,"kunyan","123456",DateUtils.strToUtil("1999-03-06"),"aaa@qq.com","武汉");
// userinfoDao.update(u1);
//删除
userinfoDao.delete(2);
//查所有
List<Userinfo> userinfos = userinfoDao.selectAll();
userinfos.stream().forEach(System.out::println);
//查单个
Userinfo kunyan = userinfoDao.select("kunyan");
System.out.println(kunyan);
}
}
十四、连接池
每次连接数据库，都会获得一个连接对象。每次创建一个连接对象，都是一个较大的资源，如果在连接量较大的场景下，会极大的浪费资源，容易内存溢出。
14.1 自定义连接池
Java中提供了一个接口DataSource，通过实现该接口，可以创建连接池。
import javax.sql.DataSource;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;
/**
* 数据库连接池
*/
public class MyDbPool implements DataSource {
//存储连接对象的集合
private static List<Connection> connections = new ArrayList<>();
//类加载时，就往集合中存放指定数量的连接对象
static {
InputStream is = MyDbPool.class.getResourceAsStream("/db.properties");
Properties properties = new Properties();
try {
properties.load(is);
Class.forName(properties.getProperty("driver"));
//通过循环创建五个连接对象放进集合中
for(int i = 1;i<=5;i++){
Connection connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
connections.add(connection);
}
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public Connection getConnection() throws SQLException {
//获得连接
System.out.println("池中有："+connections.size());
Connection connection = null;
if (connections.size()>0) {
connection = connections.remove(0);
}
return connection;
}
//将连接放回池中
public void release(Connection connection){
connections.add(connection);
System.out.println("放回一个"+connection);
}
@Override
public Connection getConnection(String s, String s1) throws SQLException {
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter printWriter) throws SQLException {
}
@Override
public void setLoginTimeout(int i) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
@Override
public <T> T unwrap(Class<T> aClass) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> aClass) throws SQLException {
return false;
}
}
import java.sql.Connection;
import java.sql.SQLException;
public class TestDBPool {
public static void main(String[] args) throws SQLException {
Connection connection = null;
MyDbPool myDbPool = new MyDbPool();
for(int i =1;i<10;i++){
connection = myDbPool.getConnection();
System.out.println(connection);
myDbPool.release(connection);
}
}
}
14.2 Druid（德鲁伊）
Druid是目前比较流行高性能的，分布式列存储
一、亚秒级查询
二、实时数据注入
三、可扩展的PB级存储
四、多环境部署
五、丰富的社区
14.2.1 Druid配置
创建database properties配置文件
引入druid-1.1.5 jar
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/userinfo?useUnicode=true&characterEncoding=utf8 username=root password=123456 #初始化连接 initialSize=10 #最大连接数量 maxActive=30 #最小空闲连接 minIdle=5 #超时等待时间以毫秒为单位 maxWait=5000
14.2.2 database.properties 文件配置
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
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 DBPoolUtils {
private static DruidDataSource ds;
static{
Properties properties = new Properties();
InputStream is = DBPoolUtils.class.getResourceAsStream("/database.properties");
try {
properties.load(is);
//使用德鲁伊工厂创建连接池
ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.Connection;
public class TestDruid {
public static void main(String[] args) throws Exception {
for(int i = 0 ; i <30;i++){
Connection connection = DBPoolUtils.getConnection();
System.out.println(connection);
connection.close();//不是释放资源关闭，而是放回连接池
// DBPoolUtils.closeAll(connection,null,null
// );
//此时Connection存放的是DruidPooledConnection实现类
//调用的close不是connection关闭的，而是归还连接池的close
}
}
}
注意：连接池中获得的Connection是DruidPooledConnection实现类，调用的close（）方法不是关闭数据库，而是将资源放回池中
十五、Service（Biz/Business）
15.1 业务
概念：用户要完成的一个业务功能，是由一个或多个的DAO调用组成
软件、程序提供一个功能都能叫做业务
15.2 业务层的实现
/**
*Userinfo的业务层逻辑层对象
*/
public class UserinfoServiceImpl {
/**
*用户的注册功能（业务）
*/
public String register(Userinfo userinfo){ //1.接收参数 前端传过来的值
UserinfoDaoImpl userinfoDao = new UserinfoDaoImpl();
//2.调用数据访问层对象的查询方法
Userinfo check = userinfoDao.select(userinfo.getUser_name());
if(check!=null){//用户存在
return "用户已存在！";
}
//3.调用数据访问层对象的新增方法
int result = userinfoDao.insert(userinfo);
//4.将操作结果返回给调用者
if(result>0){
return "注册成功！";
}else{
return "注册失败！";
}
}
/**
*登录功能业务
*/
public Userinfo login(String user_name,String user_pwd){//收参
UserinfoDaoImpl userinfoDao = new UserinfoDaoImpl();
//2.调用数据访问层对象的查询方法
Userinfo userinfo = userinfoDao.select(user_name);
//3.接收结果，处理结果
if(userinfo!=null){//用户存在
//检查查询到的用户密码和输入的密码是否一致
if(userinfo.getUser_pwd().equals(user_pwd)){
return userinfo;
}
}
//4.响应给调用者结果
return null;
}
}
15.3 复用
- DAO数据访问操作复用
- 业务功能的复用 //不同的终端访问
15.4 转账案例
代码参考day44 account项目
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DBUtils {
private static final Properties PROPERTIES = new Properties();
private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
static {
InputStream is = DBUtils.class.getResourceAsStream("/db1.properties");
try {
PROPERTIES.load(is);
Class.forName(PROPERTIES.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
//在ThreadLocal里取
Connection connection = THREAD_LOCAL.get();
try {
//没有，新建
if(connection==null) {
connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
THREAD_LOCAL.set(connection);
}
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//释放资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
THREAD_LOCAL.remove();//关闭连接后移除线程中绑定的连接对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public class T_Account {
private String cardId;
private String password;
private String username;
private double balance;
private String phone;
@Override
public String toString() {
return "T_Account{" +
"cardId='" + cardId + '\'' +
", password='" + password + '\'' +
", username='" + username + '\'' +
", balance=" + balance +
", phone='" + phone + '\'' +
'}';
}
public String getCardId() {
return cardId;
}
public void setCardId(String cardId) {
this.cardId = cardId;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public T_Account(String cardId, String password, String username, double balance, String phone) {
this.cardId = cardId;
this.password = password;
this.username = username;
this.balance = balance;
this.phone = phone;
}
}
import java.sql.Connection;
import java.sql.SQLException;
public class T_AccountServiceImpl {
/**
* 转账业务
* @param fromNo 扣钱卡号
* @param pwd 转账卡号密码
* @param toNo 收钱卡号
* @param money 转账金额
*/
public String transfer(String fromNo,String pwd,String toNo,double money) {
String result = "转账失败";
//2.组织业务功能
T_AcoountDaoImpl accountDao = new T_AcoountDaoImpl();
//拿一个连接
Connection connection = null;
try {
//建立了一个数据库连接
connection = DBUtils.getConnection();
//开启事务!并且关闭事务的自动提交
connection.setAutoCommit(false);
//2.1 验证fromNo是否存在
T_Account fromAcc = accountDao.select(fromNo);
if (fromAcc == null) {
throw new RuntimeException("----卡号不存在----");
}
//2.2 验证fromNo的密码是否正确
if (fromAcc.getPassword().equals(pwd)) {
throw new RuntimeException("----密码错误----");
}
//2.3 验证余额是否充足
if (fromAcc.getBalance() < money) {
throw new RuntimeException("----余额不足----");
}
//2.4 验证toNo是否存在
T_Account toAcc = accountDao.select(toNo);
if (toAcc == null) {
throw new RuntimeException("----对方卡号不存在----");
}
//2.5 减少fromNo的余额
//修改自己的金额，将余额-转账金额替换原有的属性
fromAcc.setBalance(fromAcc.getBalance() - money);
accountDao.update(fromAcc);
//出现异常！导致程序终止
int i = 10/0;
//2.6 增加toNo的余额
toAcc.setBalance(toAcc.getBalance() + money);
accountDao.update(toAcc);
result = "转账成功";
//执行到这里，没有异常，则提交事务
connection.commit();
}catch(Exception e){
e.printStackTrace();
try {
//出现异常回滚
System.out.println("出现了异常！回滚整个事务！");
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally{
DBUtils.closeAll(connection,null,null);
}
return result;
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class T_AcoountDaoImpl {
private Connection connection = null;
//为当前线程绑定一个Connection连接，从头越到尾
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
public int insert(T_Account account){
return 0 ;
}
public int delete(String cardId){
return 0;
}
//修改操作！复用性更强
public int update(T_Account account){
connection = DBUtils.getConnection();
String sql = "update t_account set password=?,username=?,balance = ?,phone = ? where cardId=?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,account.getPassword());
preparedStatement.setString(2,account.getUsername());
preparedStatement.setDouble(3,account.getBalance());
preparedStatement.setString(4,account.getPhone());
preparedStatement.setString(5,account.getCardId());
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null,preparedStatement,resultSet);
}
return 0;
}
public T_Account select(String cardId){
connection = DBUtils.getConnection();
String sql = "SELECT cardId,password,username,balance,phone from t_account where cardId=?";
T_Account account = null;
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,cardId);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
account = new T_Account(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDouble(4),resultSet.getString(5));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null,preparedStatement,resultSet);
}
return account;
}
}
public class TestTransfer {
public static void main(String[] args) {
T_AccountServiceImpl t_accountService = new T_AccountServiceImpl();
String result = t_accountService.transfer("6002","123456","6001",100);
System.out.println(result);
}
}
15.5 解决转账事务问题
1.传递Connection：如果将Service获得的Connection对象，传递给DAO各个方法。可以。//BadSmell臭味
定义接口是为了更容易更换实现，而将Connection参数定义在接口方法中，就会污染当前接口，而无法复用。JDBC-Connection。MyBatis使用SqlSession
2.单例：当前项目只能有一个客户端连接
十六、ThreadLocal
线程工具类：在整个线程中，一直到释放资源，用的是同一个Connection连接对象。
16.1 ThreadLocal
在整个线程（单条执行路径中）所持有的Map中，存储一个键（threadlocal）值（connection）对
线程（thread）对象中只有一个ThreadLocalMap类型的对象（ThreadLocals ），threadLocals中保持了以ThreadLocal对象为Key，set进去的值为Value
每个线程均可绑定多个ThreadLocal，一个线程中可存储多个ThreadLocal
16.1.1 ThreadLocal代码
import java.sql.Connection;
public class Test {
public static void main(String[] args) {
//绑定到线程中！
ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();//0x1122333
//Thread
//获得当前线程对象--->t.threadLocals集合为空-->create-->table[entry]-->key=0x1122333 value=connection
threadLocal.set(null);
//获得当前线程对象-->getMap-->t.threadLocals-->getEntry(0x1122333)-->entry-->entry.value
Connection connection = threadLocal.get();
//每个线程都可以绑定多个ThreadLocal
ThreadLocal<Integer> threadLocal1 = new ThreadLocal<Integer>();
threadLocal1.set(123);
Integer i = threadLocal1.get();
System.out.println(i);
}
}
16.1.2 ThreadLocal的安全
import java.sql.Connection;
public class TestThread {
public static void main(String[] args) {
Thread t1 = new Thread(new Runnable() {
@Override
public void run() {
Connection connection = DBUtils.getConnection();
System.out.println(connection);
try {
Thread.sleep(5000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
});
Thread t2 = new Thread(new Runnable() {
@Override
public void run() {
Connection connection = DBUtils.getConnection();
System.out.println(connection);
try {
Thread.sleep(5000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
});
t1.start();
t2.start();
}
}
16.2 ThreadLocal事务控制优化
将业务层的多步事务操作，封装在DBUtils工具类里，实现复用
16.2.1 DBUtils封装事务的控制
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DBUtils {
private static final Properties PROPERTIES = new Properties();
private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
static {
InputStream is = DBUtils.class.getResourceAsStream("/db1.properties");
try {
PROPERTIES.load(is);
Class.forName(PROPERTIES.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
//在ThreadLocal里取
Connection connection = THREAD_LOCAL.get();
try {
//没有，新建
if(connection==null) {
connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
THREAD_LOCAL.set(connection);
}
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//开启事务
public static void begin(){
Connection connection = getConnection();
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
//提交事务
public static void commit(){
Connection connection = getConnection();
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,null,null);
}
}
//回滚事务
public static void rollback(){
Connection connection = getConnection();
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,null,null);
}
}
//释放资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
THREAD_LOCAL.remove();//关闭连接后移除线程中绑定的连接对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
十七、三层架构设计
- 表示层：
- 命名：xxxView
- 职责：收集用户的数据和需求，展示数据
- 业务逻辑层：
- 命名：xxxServiceImpl
- 职责：数据的加工处理，调用Dao组合完成业务实现、控制事务
- 数据访问层：
- 命名：xxxDaoImpl
- 职责：向业务层提供数据，将业务层加工处理的数据同步给数据库
十八、工具类型的封装及普适性泛型工具
18.1 封装DML方法 、DQL方法
import com.qf.day45.t3.advanced.RowMapper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DaoUtils<T> {
/**
* 公共处理增、删、改的方法
* sql语句，参数列表
* @param sql 执行的sql语句
* @param args 参数列表，为占位符赋值
* @return
*/
public int commonsUpdate(String sql,Object... args){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for(int i =0;i<args.length;i++){
preparedStatement.setObject(i+1,args[i]);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null,preparedStatement,null);
}
return 0;
}
/**
* 公共查询方法（可查询单个对象，也可查询多个对象）
* @param sql
* @param args
* @return
*/
//工具不知道查的是什么 调用者知道
//封装对象，对象赋值，调用者知道
public List<T> commonSelect(String sql, RowMapper<T> rowMapper, Object... args){
List<T> elements = new ArrayList<T>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
if(args!=null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
//根据查询结果完成orm，如何完成对象的创建及赋值?
T t = rowMapper.getRow(resultSet);//回调-->调用者提供的一个封装方法ORM
elements.add(t);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(null,preparedStatement,null);
}
return elements;
}
}
十九、Apache的DbUtils使用
Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能简化JDBC应用的程序的开发，同时不会影响程序的性能
19.1 DbUtils简介
- DbUtils是Java编程中数据库操作实用小工具，小巧，简单，实用
- 对于数据表的查询操作，可以把结果转化为List、Array、Set等集合，便于操作
- 对于数据表的DML操作，也变得简单，只需要写SQL语句
19.1.1 DbUtils主要包含
- ResultSetHandler接口:转换类型接口
- BeanHandler类:实现类，把一条记录转换成对象
- BeanListHandler类：实现类，把多条记录转换成List集合
- ScalarHandler类：实现类，适合获取一行一列的数据。
- QueryRunner：执行SQL语句的类
- 增、删、改：update
- 查询：query()
19.2 DbUtils的使用步骤
- 导入jar包
- mysql连接驱动jar包
- druid-1.1.5.jar
- database.properties配置文件
- commons -dbutils-1.7.jar
19.2.1 代码实现
DBUtils工具类
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* 连接池工具类
*/
public class DBUtils {
private static DruidDataSource dataSource;
static{
Properties properties = new Properties();
InputStream is = DBUtils.class.getResourceAsStream("/database.properties");
try {
properties.load(is);
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//返回一个数据源
public static DataSource getDataSource(){
return dataSource;
}
}
UserDaoImpl 数据访问对象
import com.project.dao.UserDao;
import com.project.entity.User;
import com.project.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
public class UserDaoImpl implements UserDao {
//1.创建QueryRunner对象，并传递一个数据源对象
private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
@Override
public int insert(User user) {
Object[] params={user.getId(),user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress()};
try {
return queryRunner.update("insert into user(id,username,password,sex,email,address)values(?,?,?,?,?,?)",params);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int update(User user) {
Object[] params={user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress(),user.getId()};
try {
return queryRunner.update("update user set username=?,password=?,sex=?,email=?,address=? where id =?",params);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int delete(int id) {
try {
return queryRunner.update("delete from user where id =?",id);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public User select(int id) {
try {
//把查询的记录封装成指定对象
// user = new User(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
return queryRunner.query("select * from user where id=?",new BeanHandler<User>(User.class),id);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public List<User> selectAll() {
try {
return queryRunner.query("select * from user",new BeanListHandler<User>(User.class));
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
atabase.properties");
try {
properties.load(is);
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//返回一个数据源
public static DataSource getDataSource(){
return dataSource;
}
}
##### UserDaoImpl 数据访问对象
```java
import com.project.dao.UserDao;
import com.project.entity.User;
import com.project.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
public class UserDaoImpl implements UserDao {
//1.创建QueryRunner对象，并传递一个数据源对象
private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
@Override
public int insert(User user) {
Object[] params={user.getId(),user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress()};
try {
return queryRunner.update("insert into user(id,username,password,sex,email,address)values(?,?,?,?,?,?)",params);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int update(User user) {
Object[] params={user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress(),user.getId()};
try {
return queryRunner.update("update user set username=?,password=?,sex=?,email=?,address=? where id =?",params);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int delete(int id) {
try {
return queryRunner.update("delete from user where id =?",id);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public User select(int id) {
try {
//把查询的记录封装成指定对象
// user = new User(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
return queryRunner.query("select * from user where id=?",new BeanHandler<User>(User.class),id);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public List<User> selectAll() {
try {
return queryRunner.query("select * from user",new BeanListHandler<User>(User.class));
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
上一篇： Spring Boot上传文件
下一篇： SSM快速整合实现