JDBC基础 + JDBC实现水果库存系统案例
JDBC
- JDBC : sun发布的 一个 java程序和数据库之间通信的 规范（接口）
- 各大数据库厂商去实现JDBC规范（实现类），这些实现类打成压缩包，就是所谓的jar包
JavaWe b学习框架：
1 JDBC常规操作：
1)JDBC连接MySQL数据库
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//目标：和数据库建立连接
public class Demo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.添加jar
//2.加载驱动
//org.gjt.mm.mysql.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//3.通过驱动管理器获取连接对象
//3-1.准备URL
String url = "jdbc:mysql://localhost:3306/fruitdb";
//3-2.准备用户名
String user = "root";
//3-3.准备密码
String pwd = "123456";
Connection conn = DriverManager.getConnection(url,user,pwd);
System.out.println("conn = " + conn);
}
}
/*
1. JDBC : sun发布的 一个 java程序和数据库之间通信的 规范（接口）
2. 各大数据库厂商去实现JDBC规范（实现类），这些实现类打成压缩包，就是所谓的jar包
3. 常见错误：
1. ClassNotFoundException，添加依赖库
2. 中文乱码
*/
2)JDBC对数据库进行增、删、改
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Demo02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.通过驱动管理器获取连接对象
//url 表示 和 数据库通信的地址
//如果url中需要带参数，则需要使用?进行连接
//如果需要带多个参数，则从第二个参数开始使用&连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useSSL=false&useUnicode=true&characterEncoding=utf-8","root","qian123150");
//3.编写SQL语句
//id , fname , price , fcount , remark
String sql = "insert into t_fruit values(0,?,?,?,?)";
//4.创建预处理命令对象
PreparedStatement psmt = conn.prepareStatement(sql);
//5.填充参数
psmt.setString(1,"草莓");
psmt.setInt(2,10);
psmt.setInt(3,30);
psmt.setString(4,"草莓很好吃");
//6.执行更新(增删改），返回影响行数
int count = psmt.executeUpdate();
System.out.println(count > 0 ? "添加成功！" : "添加失败！");
//7.释放资源（关闭连接 , 先关闭psmt，后关闭conn）
psmt.close();
conn.close();
}
}
修改和删除：
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
//JDBC - 修改和删除
public class Demo03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Fruit fruit = new Fruit(33,"猕猴桃","猕猴桃是水果之王");
Class.forName("org.gjt.mm.mysql.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
String sql = "update t_fruit set fname = ? , remark = ? where fid = ? " ;
PreparedStatement psmt = conn.prepareStatement(sql);
psmt.setString(1,fruit.getFname());
psmt.setString(2,fruit.getRemark());
psmt.setInt(3,fruit.getFid());
int count = psmt.executeUpdate();
System.out.println(count > 0 ? "修改成功！" : "修改失败！");
psmt.close();
conn.close();
}
}
//JDBC - 修改和删除
public class Demo04 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.gjt.mm.mysql.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
String sql = "delete from t_fruit where fid = ? " ;
PreparedStatement psmt = conn.prepareStatement(sql);
psmt.setInt(1,6);
int count = psmt.executeUpdate();
System.out.println(count > 0 ? "删除成功！" : "删除失败！");
psmt.close();
conn.close();
}
}
3)JDBC对数据库进行查询操作
查询所有的库存：
package com.atguigu.jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
//JDBC - 查询所有的库存
public class Demo05 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.gjt.mm.mysql.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
//3.编写SQL语句
String sql = "select * from t_fruit" ;
//4.创建预处理命令对象
PreparedStatement psmt = conn.prepareStatement(sql);
//5.执行查询，返回结果集
ResultSet rs = psmt.executeQuery();
//6.解析结果集
List<Fruit> fruitList = new ArrayList<>();
while(rs.next()){
//1表示读取当前行的第一列的数据
//getInt ， 因为这一列是int类型，所以使用getInt
//getInt(结果集的列名)
//int fid = rs.getInt("fid");
int fid = rs.getInt(1);
String fname = rs.getString("fname");
int price = rs.getInt(3);
int fcount = rs.getInt(4);
String remark = rs.getString(5);
Fruit fruit = new Fruit(fid , fname , price , fcount , remark );
fruitList.add(fruit);
}
//7.释放资源
rs.close();
psmt.close();
conn.close();
fruitList.forEach(System.out::println);
}
}
查询指定fid的库存记录：
package com.atguigu.jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
//JDBC - 查询指定fid的库存记录
public class Demo06 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.gjt.mm.mysql.Driver");
//url -> jdbc:mysql://ip:port/dbname?参数列表
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
//3.编写SQL语句
String sql = "select * from t_fruit where fid = ? " ;
//4.创建预处理命令对象
PreparedStatement psmt = conn.prepareStatement(sql);
//5.填充参数
psmt.setInt(1,33);
//6.执行查询，返回结果集
ResultSet rs = psmt.executeQuery();
//6.解析结果集
if(rs.next()){
int fid = rs.getInt(1);
String fname = rs.getString("fname");
int price = rs.getInt(3);
int fcount = rs.getInt(4);
String remark = rs.getString(5);
Fruit fruit = new Fruit(fid , fname , price , fcount , remark );
System.out.println(fruit);
}
//7.释放资源
rs.close();
psmt.close();
conn.close();
}
}
查询库存总数：
package com.atguigu.jdbc;
import java.sql.*;
//JDBC - 查询库存记录
public class Demo07 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.gjt.mm.mysql.Driver");
//url -> jdbc:mysql://ip:port/dbname?参数列表
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
//3.编写SQL语句
String sql = "select count(*) from t_fruit" ;
//4.创建预处理命令对象
PreparedStatement psmt = conn.prepareStatement(sql);
//5.执行查询，返回结果集
ResultSet rs = psmt.executeQuery();
//6.解析结果集
if(rs.next()){
int count = rs.getInt(1);
System.out.println("总记录条数："+count);
}
//7.释放资源
rs.close();
psmt.close();
conn.close();
}
}
4) 获取自增列的值
- 创建psmt的时候，设置第二个参数
Statement.RETURN_GENERATED_KEYS - 执行完更新之后，再去获取结果集，结果集中就包含了自增列的值
// 1
psmt=conn.preparedStatement(sql,Statement.RETURN_GENERATED_KEYS);
// 2
psmt.executeUpdate();
Resultset rs = psmt.getGeneratedKeys();
Long id=rs.getLong(1);
5) 批处理
- 如果要执行批处理任务，URL中需要添加一个参数：
rewriteBatchedStatements=true - psmt.addBatch();
- psmt.executeBatch();
- 清空批处理：clearBatch();
优点：
效率高
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Demo01Batch {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
//批处理操作一： 如果要执行批处理任务，URL中需要添加一个参数：rewriteBatchedStatements=true
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true","root","123456");
String sql = "insert into t_fruit values(0,?,?,?,?)";
PreparedStatement psmt = conn.prepareStatement(sql);
for(int i = 0 ; i<10 ; i++){
psmt.setString(1,"榴莲"+i);
psmt.setInt(2,15);
psmt.setInt(3,100);
psmt.setString(4,"榴莲是一种神奇的水果");
//批处理操作二：psmt.addBatch()
psmt.addBatch();
if(i%1000==0){ //如果任务较多，可以分批次执行，每次执行完，清空任务队列
psmt.executeBatch();
psmt.clearBatch();
}
}
//批处理操作三
int[] count = psmt.executeBatch();
for (int i = 0; i < count.length; i++) {
System.out.println(count[i]);
}
psmt.close();
conn.close();
}
}
2 项目实战 - 水果库存系统
1）DAO的概念和角色（设计理念）：
DAO-称之为数据访问对象，其中的方法都是单精度方法。
什么叫单精度，单精度指的是这个方法的粒度不能再分了，已经非常细了（因此也称之为细粒度）
水果库存系统初级实现：
Client类实现：
public class Client {
public static void main(String[] args) {
Menu m = new Menu() ;
boolean flag = true ;
while(flag){
//调用显示主菜单的方法
int slt = m.showMainMenu();
switch(slt){
case 1:
//显示库存列表
m.showFruitList();
break;
case 2:
m.addFruit();
break;
case 3:
m.showFruitInfo();
break;
case 4:
m.delFruit();
break;
case 5:
flag=m.exit();
break;
default:
System.out.println("你不按套路出牌！");
break;
}
}
System.out.println("谢谢使用！再见！");
}
}
Fruit类实现：
public class Fruit {
private Integer fid ;
private String fname ;
private Integer price ;
private Integer fcount ;
private String remark ;
public Fruit(){}
public Fruit(Integer fid, String fname, Integer price, Integer fcount, String remark) {
this.fid = fid;
this.fname = fname;
this.price = price;
this.fcount = fcount;
this.remark = remark;
}
public Integer getFid() {
return fid;
}
public void setFid(Integer fid) {
this.fid = fid;
}
public String getFname() {
return fname;
}
public void setFname(String fname) {
this.fname = fname;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Integer getFcount() {
return fcount;
}
public void setFcount(Integer fcount) {
this.fcount = fcount;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return fid + "\t\t" + fname + "\t\t" + price +"\t\t" + fcount +"\t\t" + remark ;
}
}
Menu类实现：
import java.util.List;
import java.util.Scanner;
//菜单类
public class Menu {
Scanner input = new Scanner(System.in);
FruitDAO fruitDAO = new FruitDAOImpl();
//显示主菜单
public int showMainMenu(){
System.out.println("=================欢迎使用水果库存系统=====================");
System.out.println("1.查看水果库存列表");
System.out.println("2.添加水果库存信息");
System.out.println("3.查看特定水果库存信息");
System.out.println("4.水果下架");
System.out.println("5.退出");
System.out.println("======================================================");
System.out.print("请选择：");
int slt = input.nextInt();
return slt ;
}
//查看水果库存列表
public void showFruitList(){
List<Fruit> fruitList = fruitDAO.getFruitList();
System.out.println("------------------------------------------------------");
System.out.println("编号\t\t名称\t\t单价\t\t库存\t\t备注");
if(fruitList==null || fruitList.size()<=0){
System.out.println("对不起，库存为空！");
}else{
for (int i = 0; i < fruitList.size(); i++) {
Fruit fruit = fruitList.get(i);
System.out.println(fruit);
}
}
System.out.println("------------------------------------------------------");
}
//添加水果库存信息 -- 业务方法：添加库存记录
public void addFruit(){
System.out.print("请输入水果名称：");
String fname = input.next() ;
Fruit fruit = fruitDAO.getFruitByFname(fname);
if(fruit==null){ //说明库存中没有这个名称的水果 - 添加
System.out.print("请输入水果单价：");
int price = input.nextInt() ;
System.out.print("请输入水果库存量：");
int fcount = input.nextInt() ;
System.out.print("请输入水果备注：");
String remark = input.next() ;
//封装成一个新的fruit对象
fruit = new Fruit(0,fname , price , fcount , remark ) ;
//调用DAO的添加方法
fruitDAO.addFruit(fruit);
}else{ // 说明库存中有这个名称的水果 - 修改
System.out.print("请输入追加的库存量：");
int fcount = input.nextInt() ;
fruit.setFcount(fruit.getFcount()+fcount);
//调用DAO的修改方法
fruitDAO.updateFruit(fruit);
}
System.out.println("添加成功！");
}
//查看指定水果库存信息
public void showFruitInfo(){
System.out.print("请输入水果名称：");
String fname = input.next() ;
Fruit fruit = fruitDAO.getFruitByFname(fname);
if(fruit==null){
System.out.println("对不起，没有找到指定的水果库存记录！");
}else{
System.out.println("------------------------------------------------------");
System.out.println("编号\t\t名称\t\t单价\t\t库存\t\t备注");
System.out.println(fruit);
System.out.println("------------------------------------------------------");
}
}
//水果下架
public void delFruit(){
System.out.print("请输入水果名称：");
String fname = input.next() ;
Fruit fruit = fruitDAO.getFruitByFname(fname);
if(fruit==null){
System.out.println("对不起，没有找到需要下架的水果信息！");
}else{
System.out.print("是否确认下架？(Y/N)");
String slt = input.next() ;
if("y".equalsIgnoreCase(slt)){
fruitDAO.delFruit(fname);
System.out.println("下架成功！");
}
}
}
//退出
public boolean exit(){
System.out.print("是否确认退出？(Y/N)");
String slt = input.next();
return !"Y".equalsIgnoreCase(slt);
}
}
FruitDAO接口：
import java.util.List;
public interface FruitDAO {
//查询库存列表
List<Fruit> getFruitList();
//新增库存
boolean addFruit(Fruit fruit);
//修改库存
boolean updateFruit(Fruit fruit);
//根据名称查询特定库存
Fruit getFruitByFname(String fname);
//删除特定库存记录
boolean delFruit(String fname);
}
FruitDAOImpl接口类实现：
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class FruitDAOImpl implements FruitDAO {
Connection conn;
PreparedStatement psmt;
ResultSet rs;
final String DRIVER = "com.mysql.cj.jdbc.Driver";
final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
final String USER = "root";
final String PWD = "123456";
@Override
public List<Fruit> getFruitList() {
List<Fruit> fruitList = new ArrayList<>();
try {
//1 加载驱动
Class.forName(DRIVER);
//2 通过驱动管理器获取连接对象
conn = DriverManager.getConnection(URL, USER ,PWD);
//3 编写SQL语言
String sql = "select * from t_fruit";
//4 创建预处理命令
psmt = conn.prepareStatement(sql);
//5 执行查询
rs = psmt.executeQuery();
//6 解析结果集
while(rs.next()){
int fid = rs.getInt(1);
String fname = rs.getString(2);
int price = rs.getInt(3);
int fcount = rs.getInt(4);
String remark = rs.getString(5);
Fruit fruit = new Fruit(fid,fname,price,fcount,remark);
fruitList.add(fruit);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try {
if(rs != null) {
rs.close();
}
if(psmt != null){
psmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return fruitList;
}
@Override
public boolean addFruit(Fruit fruit) {
try {
//1 加载驱动
Class.forName(DRIVER);
//2 通过驱动管理器获取连接对象
conn = DriverManager.getConnection(URL, USER ,PWD);
//3 编写SQL语言
String sql = "insert into t_fruit values(0,?,?,?,?)";
//4 创建预处理命令
psmt = conn.prepareStatement(sql);
psmt.setString(1,fruit.getFname());
psmt.setInt(2,fruit.getPrice());
psmt.setInt(3,fruit.getFcount());
psmt.setString(4,fruit.getRemark());
//5 执行更新操作 并返回
return psmt.executeUpdate() > 0;
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try {
if(psmt != null){
psmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return false;
}
@Override
public boolean updateFruit(Fruit fruit) {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PWD);
String sql = "update t_fruit set fcount = ? where fid = ?";
psmt = conn.prepareStatement(sql);
psmt.setInt(1,fruit.getFcount());
psmt.setInt(2,fruit.getFid());
return psmt.executeUpdate() > 0;
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try {
if(psmt != null){
psmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return false;
}
@Override
public Fruit getFruitByFname(String fname) {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PWD);
String sql = "select * from t_fruit where fname like ?";
// String sql = "select * from t_fruit where fname = ?";
psmt = conn.prepareStatement(sql);
psmt.setString(1,fname);
rs = psmt.executeQuery();
if(rs.next()){
int fid = rs.getInt(1);
int price = rs.getInt(3);
int fcount = rs.getInt(4);
String remark = rs.getString(5);
return new Fruit(fid, fname, price, fcount, remark);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try {
if(rs != null) {
rs.close();
}
if(psmt != null){
psmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
@Override
public boolean delFruit(String fname) {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PWD);
String sql = "delete from t_fruit where fname like ?";
psmt = conn.prepareStatement(sql);
psmt.setString(1,fname);
return psmt.executeUpdate() > 0;
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
try {
if(psmt != null){
psmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return false;
}
}
2）接口类抽取获取连接
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class FruitDAOImpl implements FruitDAO {
Connection conn;
PreparedStatement psmt;
ResultSet rs;
final String DRIVER = "com.mysql.cj.jdbc.Driver";
final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
final String USER = "root";
final String PWD = "123456";
private Connection getConn(){
try {
//1 加载驱动
Class.forName(DRIVER);
//2 通过驱动管理器获取连接对象
return DriverManager.getConnection(URL, USER ,PWD);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
return null;
}
private void close(ResultSet rs, PreparedStatement psmt, Connection conn){
try {
if(rs != null) {
rs.close();
}
if(psmt != null){
psmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public List<Fruit> getFruitList() {
List<Fruit> fruitList = new ArrayList<>();
try {
conn = getConn();
//3 编写SQL语言
String sql = "select * from t_fruit";
//4 创建预处理命令
psmt = conn.prepareStatement(sql);
//5 执行查询
rs = psmt.executeQuery();
//6 解析结果集
while(rs.next()){
int fid = rs.getInt(1);
String fname = rs.getString(2);
int price = rs.getInt(3);
int fcount = rs.getInt(4);
String remark = rs.getString(5);
Fruit fruit = new Fruit(fid,fname,price,fcount,remark);
fruitList.add(fruit);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(rs, psmt,conn);
}
return fruitList;
}
@Override
public boolean addFruit(Fruit fruit) {
try {
conn = getConn();
//3 编写SQL语言
String sql = "insert into t_fruit values(0,?,?,?,?)";
//4 创建预处理命令
psmt = conn.prepareStatement(sql);
psmt.setString(1,fruit.getFname());
psmt.setInt(2,fruit.getPrice());
psmt.setInt(3,fruit.getFcount());
psmt.setString(4,fruit.getRemark());
//5 执行更新操作 并返回
return psmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(rs, psmt,conn);
}
return false;
}
@Override
public boolean updateFruit(Fruit fruit) {
try {
conn = getConn();
String sql = "update t_fruit set fcount = ? where fid = ?";
psmt = conn.prepareStatement(sql);
psmt.setInt(1,fruit.getFcount());
psmt.setInt(2,fruit.getFid());
return psmt.executeUpdate() > 0;
} catch ( SQLException e) {
e.printStackTrace();
}finally {
close(rs, psmt,conn);
}
return false;
}
@Override
public Fruit getFruitByFname(String fname) {
try {
conn = getConn();
String sql = "select * from t_fruit where fname like ?";
// String sql = "select * from t_fruit where fname = ?";
psmt = conn.prepareStatement(sql);
psmt.setString(1,fname);
rs = psmt.executeQuery();
if(rs.next()){
int fid = rs.getInt(1);
int price = rs.getInt(3);
int fcount = rs.getInt(4);
String remark = rs.getString(5);
return new Fruit(fid, fname, price, fcount, remark);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(rs, psmt,conn);
}
return null;
}
@Override
public boolean delFruit(String fname) {
try {
conn = getConn();
String sql = "delete from t_fruit where fname like ?";
psmt = conn.prepareStatement(sql);
psmt.setString(1,fname);
return psmt.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(rs, psmt,conn);
}
return false;
}
}
3）BaseDAO抽取增删改 *查
抽象类BaseDAO实现：
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
//抽象类不能实例化对象，只能用来继承
//final 关键字修饰的类，不能用于继承
//final 关键字修饰的方法，不能被子类覆盖/重写
public abstract class BaseDAO<T> {
protected String DRIVER = "com.mysql.cj.jdbc.Driver";
protected final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
protected final String USER = "root";
protected final String PWD = "123456";
protected Connection conn;
protected PreparedStatement psmt;
protected ResultSet rs;
//T的Class 对象
private Class entityClass;
public BaseDAO() {
//getClass()获取Class对象，当前我们执行的是new FruitDAOImpl();创建的是FruitDAO的实例
//那么子类构造方法内部会首先调用父类（BaseDAO）的午餐构造方法
//因此此处的getClass() 会被执行，但是getClass获取的是FruitDAOImpl的class
//所以getGenericSuperclass()获取到的是BaseDAO的Class
Type genericType = getClass().getGenericSuperclass();
//ParameterizedType 参数化类型
Type[] actualTypeArguments = ((ParameterizedType) genericType).getActualTypeArguments();
//获取到的<T>中的真实的类型
Type actualType = actualTypeArguments[0];
System.out.println(actualType.getTypeName());//获取到全列名
try {
entityClass = Class.forName(actualType.getTypeName());
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
protected Connection getConn() {
try {
//1 加载驱动
Class.forName(DRIVER);
//2 通过驱动管理器获取连接对象
return DriverManager.getConnection(URL, USER, PWD);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
return null;
}
protected void close(ResultSet rs, PreparedStatement psmt, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (psmt != null) {
psmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private void setParams(PreparedStatement psmt, Object... params) {
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
try {
psmt.setObject(i + 1, params[i]);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
//执行更新返回影响行数, insert操作返回自增值
protected int executeUpdate(String sql, Object... params) {
boolean insertFlag = false;
insertFlag = sql.trim().toUpperCase().startsWith("INSERT");
try {
conn = getConn();
if(insertFlag){
psmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
}else{
//4 创建预处理命令
psmt = conn.prepareStatement(sql);
}
//5 执行更新
setParams(psmt, params);
int count = psmt.executeUpdate();
rs = psmt.getGeneratedKeys();
if(rs.next()){
return ((Long)rs.getLong(1)).intValue();
}
return count;
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, psmt, conn);
}
return 0;
}
//通过反射技术给obj对象的property属性威propertyValue值
private void setValue(Object obj, String property, Object propertyValue) {
Class clazz = obj.getClass();
try {
Field field = clazz.getDeclaredField(property);
if (field != null) {
field.setAccessible(true);
field.set(obj, propertyValue);
}
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
}
}
// 执行查询，返回List
protected List<T> executeQuery(String sql, Object... params) {
List<T> list = new ArrayList<>();
try {
conn = getConn();
psmt = conn.prepareStatement(sql);
setParams(psmt, params);
rs = psmt.executeQuery();
//通过rs可以获取结果集的元数据
//元数据：描过结果集数据的数据，简单讲，就是这个结果集有哪些列，什么类型等等
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//解析
while (rs.next()) {
T entity = (T) entityClass.newInstance();
for (int i = 0; i < columnCount; i++) {
String columnName = rsmd.getColumnName(i + 1);
Object columnValue = rs.getObject(i + 1);
setValue(entity, columnName, columnValue);
}
list.add(entity);
}
} catch (SQLException | InstantiationException | IllegalAccessException throwables) {
throwables.printStackTrace();
} finally {
close(rs, psmt, conn);
}
return list;
}
//执行单个查询，返回单个实体对象
protected T load(String sql, Object... params) {
try {
conn = getConn();
psmt = conn.prepareStatement(sql);
setParams(psmt, params);
rs = psmt.executeQuery();
//通过rs可以获取结果集的元数据
//元数据：描过结果集数据的数据，简单讲，就是这个结果集有哪些列，什么类型等等
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//解析
if (rs.next()) {
T entity = (T) entityClass.newInstance();
for (int i = 0; i < columnCount; i++) {
String columnName = rsmd.getColumnName(i + 1);
Object columnValue = rs.getObject(i + 1);
setValue(entity, columnName, columnValue);
}
return entity;
}
} catch (SQLException | InstantiationException | IllegalAccessException throwables) {
throwables.printStackTrace();
} finally {
close(rs, psmt, conn);
}
return null;
}
//执行复杂查询，返回例如统计结果
protected Object[] executeComplexQuery(String sql, Object... params){
try {
conn = getConn();
psmt = conn.prepareStatement(sql);
setParams(psmt, params);
rs = psmt.executeQuery();
//通过rs可以获取结果集的元数据
//元数据：描过结果集数据的数据，简单讲，就是这个结果集有哪些列，什么类型等等
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
Object[] columnValueArr = new Object[columnCount];
//解析
if (rs.next()) {
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
columnValueArr[i] = columnValue;
}
return columnValueArr;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(rs, psmt, conn);
}
return null;
}
}
FruitDAOImpl接口类优化：
import java.util.ArrayList;
import java.util.List;
public class FruitDAOImpl extends BaseDAO<Fruit> implements FruitDAO {
@Override
public List<Fruit> getFruitList() {
List<Fruit> fruitList = new ArrayList<>();
return super.executeQuery("select * from t_fruit");
}
@Override
public boolean addFruit(Fruit fruit) {
String sql = "insert into t_fruit values(0,?,?,?,?)";
return super.executeUpdate(sql,fruit.getFname(),fruit.getPrice(),fruit.getFcount(),fruit.getRemark()) > 0;
}
@Override
public boolean updateFruit(Fruit fruit) {
String sql = "update t_fruit set fcount = ? where fid = ?";
return super.executeUpdate(sql,fruit.getFid()) > 0;
}
@Override
public Fruit getFruitByFname(String fname) {
return super.load("select * from t_fruit where fname like ?",fname);
}
@Override
public boolean delFruit(String fname) {
String sql = "delete from t_fruit where fname like ?";
return super.executeUpdate(sql,fname) > 0;
}
}
3 数据源连接池
连接池优点：
- 响应时间更快
- 连接对象的利用率更高
阿里的德鲁伊连接池技术
加入jar包：druid-1.1.12.jar
步骤：
- 建立一个数据连接池
- 设置连接池的参数
- 获取连接
import com.alibaba.druid.pool.DruidDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
//验证连接池中的connection可以重复使用
public class Demo02Druid {
public static void main(String[] args) throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8");
dataSource.setUsername("root");
dataSource.setPassword("123456");
//证明两点：
//1. 被close的连接对象并没有真正关闭，而是将状态重新设置为空闲状态，然后放回池子，这样下次获取连接对象，这个对象会被重复使用
//2. 没有close的连接对象会被一直占用，那么下次继续获取连接对象，是不会获取到这个对象的（hashcode没有重复，只出现一次）
for(int i = 0 ; i<5 ; i++){
Connection conn1 = dataSource.getConnection();
Connection conn2 = dataSource.getConnection();
System.out.println(conn1);
System.out.println(conn2);
if(i%3==0){
conn1.close();
conn2.close();
}
}
}
}
含连接池参数的代码：
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
//验证连接池的各项参数：初始化大小、最大激活数量、最大等待时间
public class Demo03Druid {
public static void main(String[] args) throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setInitialSize(2);
dataSource.setMaxActive(5);
dataSource.setMaxWait(5000);
for(int i = 0 ; i<10 ; i++){
Connection conn1 = dataSource.getConnection();
System.out.println(i+"-------->"+conn1);
}
}
}
读取外部配置文件的连接池设计：
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.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
//读取外部的配置文件设置连接池
public class Demo05Druid {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
InputStream is = Demo05Druid.class.getClassLoader().getResourceAsStream("jdbc2.properties");
properties.load(is);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
for(int i = 0 ; i<10 ; i++){
Connection conn1 = dataSource.getConnection();
System.out.println(i+"-------->"+conn1);
}
}
}
/*
Statement - 存在注入式漏洞,一般情况下，不使用
PreparedStatement - 预处理命令对象
CallableStatement - 执行存储过程的
*/
jbdc.properties配置文件
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8
username=root
password=123456
initialSize=2
maxActive=5
maxWait=5000
资料来源：尚硅谷
936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
