BaseDao的好处就是减少代码量,看起不会冗余。
关键点设计
-
BaseDao如何得知要处理的具体类型呢?
未来要处理的类型是不确定性的
采用泛型设计 -
BaseDao在内部方法处理时,
如何知道要处理的具体类型?
在实际的运行期间,需要把这个类型给明确化采用反射技术 -
BaseDao完成更新方法的抽取 (增删改)
方法设计的四要素:返回类型 方法名,方法参数,方法体
方法体方法名: update
方法参数:变化的东西,SQL+参数值
方法体:共性的结构 -
BaseDao完成查询方法的抽取
方法设计的四要素: 返回类型 方法名,方法参数,方法体
返回类型:集合List,单个对象,数值 要不要整成一个方法?code review
结论:整成三个方法方法名: getBeanList,getBean,getValue方法参数: SQL+参数列表
配置文件
# 驱动名称(连接Mysql)
driverClassName = com.mysql.cj.jdbc.Driver
# 参数?rewriteBatchedStatements=True表示支持批处理机制
url = jdbc:mysql://localhost:3306/goodsdb?useSSL=false
# 用户名,注意这里是按"userName"来读取的
username = root
# 用户密码(自己改)
password = sasa
Util工具类
主要就是获取连接和关闭连接
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
private static final DataSource dataSource;
// 静态代码块,它只在第一次创建对象的时候
// 只执行一次,后面创建对象不会执行
static{
// 配置文件加载类
Properties properties = new Properties();
try {
//配置文件加载,因为他有时相对路径找不到,所以放绝对路径
properties.load(Files.newInputStream(Paths.get("F:\Web\javaWeb2024\src\main\resources\driuid.properties")));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void close(AutoCloseable autoCloseable){
if(autoCloseable != null){
try {
autoCloseable.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
public static void close(Statement statement,Connection connection){
close(statement);
close(connection);
}
public static void close(Statement statement, Connection connection, ResultSet resultSet){
close(statement);
close(connection);
close(resultSet);
}
}
更新(增删改)
/**
* 增删改方法
* @param sql 执行的sql
* @param args sql参数
* @return 影响行数
*/
public int update(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int row = 0;
try {
connection = JDBCUtil.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 {
JDBCUtil.close(preparedStatement, connection);
}
return row;
}
查询单个对象
/**
*查询单个对象
*/
public T getBean(String sql, Object... args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Connection connection = JDBCUtil.getConnection();
T t = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
//获取到ResultSetMetaData元数据,获取到实际查询有几列
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if (resultSet.next()) {
t = targetClass.newInstance();
//将每条数据库记录转换为每个对象
//难点一:要获取到实际处理的类型的属性
//难点二:按需赋值
for (int i = 0; i < columnCount; i++) {
//获取到属性名
String columnLabel = metaData.getColumnLabel(i + 1);
//获取到属性值
Object object = resultSet.getObject(columnLabel);
//得到对象属性给属性赋值
Field field = targetClass.getDeclaredField(
underToCamel(columnLabel));
String type = null;
if (object != null) {
type = object.getClass().getName();
/*
* 时间类型特殊处理
* 获取到mysql时间类型的值,转为其真正类型通过debug发现mysql
* 在java中储存时间是用java.time.LocalDateTime类型存储的
* 而不是java.sql.Timestamp类型或者直接使用java.time.LocalDateTime
*/
if ("java.time.LocalDateTime".equalsIgnoreCase(type)) {
Timestamp timestamp = Timestamp.valueOf(((LocalDateTime) object));
object = timestamp;
}
}
field.setAccessible(true); //私有属性开放权限
field.set(t, object);
}
}
return t;
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(preparedStatement, connection, resultSet);
}
return t;
}
查询集合
/**
* 反射获取到实际处理的对象
* 得到他的属性ORM
* 根据sql查询返回数据列表
* @param sql 执行的sql
* @param args sql条件参数
* @return List
*/
public List<T> getBeanList(String sql, Object... args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Connection connection = JDBCUtil.getConnection();
List<T> list = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//获取到ResultSetMetaData元数据,获取到实际查询有几列
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if (!resultSet.next()) { // 判断是否有结果集,没有返回null
return list;
} else { // next()将游标移至第一行,使用do while不会丢失数据
list = new ArrayList<>();
do {
//将每条数据库记录转换为每个对象
//难点一:要获取到实际处理的类型的属性
//难点二:按需赋值
T t = targetClass.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取到属性名
String columnLabel = metaData.getColumnLabel(i + 1);
//获取到属性值
Object object = resultSet.getObject(columnLabel);
//给属性赋值
Field field = targetClass.getDeclaredField(
underToCamel(columnLabel));
String type = null;
if (object != null) {
type = object.getClass().getName();
/*
* 时间类型特殊处理
* 获取到mysql时间类型的值,转为其真正类型通过debug发现mysql
* 在java中储存时间是用java.time.LocalDateTime类型存储的
* 而不是java.sql.Timestamp类型
* 或者不用转换直接使用LocalDateTime
*/
if ("java.time.LocalDateTime".equalsIgnoreCase(type)) {
Timestamp timestamp = Timestamp.valueOf(((LocalDateTime) object));
object = timestamp;
}
}
field.setAccessible(true); //私有属性开放权限
field.set(t, object);
}
list.add(t);
} while (resultSet.next());
}
return list;
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(preparedStatement, connection, resultSet);
}
return list;
}
查询值(如数据总数)
/**
* 泛型方法返回一个泛型值
* @param sql 执行的sql
* @param args sql参数
* @param <V> 值
* @return 返回一个泛型值
*/
public <V> V getValue(String sql, Object... args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Connection connection = JDBCUtil.getConnection();
V value = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
return (V) resultSet.getObject(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(preparedStatement, connection, resultSet);
}
return value;
}
BaseDao
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 数据层
* 通过继承获取到实际要处理的类型
*/
public abstract class BaseDao<T> {
/**
* 具体要处理的目标类型
*/
private Class<T> targetClass;
public BaseDao() {
//targetClass?
//反射获取到实际处理的类型
//获取当前类的父类
Type genericSuperclass = this.getClass().getGenericSuperclass();
//把泛型中的类型参数化强转
ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
//获取参数化类型的实际类型参数
Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
//将实际类型参数转换为Class类型
targetClass = (Class<T>) actualTypeArguments[0];
}
/**
* 增删改方法
*
* @param sql 执行的sql
* @param args sql参数
* @return 影响行数
*/
public int update(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
int row = 0;
try {
connection = JDBCUtil.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 {
JDBCUtil.close(preparedStatement, connection);
}
return row;
}
/**
* 反射获取到实际处理的对象
* 得到他的属性ORM
* 根据sql查询返回数据列表
*
* @param sql 执行的sql
* @param args sql条件参数
* @return List
*/
public List<T> getBeanList(String sql, Object... args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Connection connection = JDBCUtil.getConnection();
List<T> list = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//获取到ResultSetMetaData元数据,获取到实际查询有几列
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if (!resultSet.next()) { // 判断是否有结果集,没有返回null
return list;
} else { // next()将游标移至第一行,使用do while不会丢失数据
list = new ArrayList<>();
do {
//将每条数据库记录转换为每个对象
//难点一:要获取到实际处理的类型的属性
//难点二:按需赋值
T t = targetClass.newInstance();
for (int i = 0; i < columnCount; i++) {
//获取到属性名
String columnLabel = metaData.getColumnLabel(i + 1);
//获取到属性值
Object object = resultSet.getObject(columnLabel);
//给属性赋值
Field field = targetClass.getDeclaredField(
underToCamel(columnLabel));
String type = null;
if (object != null) {
type = object.getClass().getName();
/*
* 时间类型特殊处理
* 获取到mysql时间类型的值,转为其真正类型通过debug发现mysql
* 在java中储存时间是用java.time.LocalDateTime类型存储的
* 而不是java.sql.Timestamp类型
* 或者不用转换直接使用LocalDateTime
*/
if ("java.time.LocalDateTime".equalsIgnoreCase(type)) {
Timestamp timestamp = Timestamp.valueOf(((LocalDateTime) object));
object = timestamp;
}
}
field.setAccessible(true); //私有属性开放权限
field.set(t, object);
}
list.add(t);
} while (resultSet.next());
}
return list;
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(preparedStatement, connection, resultSet);
}
return list;
}
/**
*查询单个对象
*/
public T getBean(String sql, Object... args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Connection connection = JDBCUtil.getConnection();
T t = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
//获取到ResultSetMetaData元数据,获取到实际查询有几列
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if (resultSet.next()) {
t = targetClass.newInstance();
//将每条数据库记录转换为每个对象
//难点一:要获取到实际处理的类型的属性
//难点二:按需赋值
for (int i = 0; i < columnCount; i++) {
//获取到属性名
String columnLabel = metaData.getColumnLabel(i + 1);
//获取到属性值
Object object = resultSet.getObject(columnLabel);
//得到对象属性给属性赋值
Field field = targetClass.getDeclaredField(
underToCamel(columnLabel));
String type = null;
if (object != null) {
type = object.getClass().getName();
/*
* 时间类型特殊处理
* 获取到mysql时间类型的值,转为其真正类型通过debug发现mysql
* 在java中储存时间是用java.time.LocalDateTime类型存储的
* 而不是java.sql.Timestamp类型或者直接使用java.time.LocalDateTime
*/
if ("java.time.LocalDateTime".equalsIgnoreCase(type)) {
Timestamp timestamp = Timestamp.valueOf(((LocalDateTime) object));
object = timestamp;
}
}
field.setAccessible(true); //私有属性开放权限
field.set(t, object);
}
}
return t;
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(preparedStatement, connection, resultSet);
}
return t;
}
/***
* 泛型方法返回一个泛型值
* @param sql 执行的sql
* @param args sql参数
* @param <V> 值
* @return 返回一个泛型值
*/
public <V> V getValue(String sql, Object... args) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Connection connection = JDBCUtil.getConnection();
V value = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
return (V) resultSet.getObject(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(preparedStatement, connection, resultSet);
}
return value;
}
}
测试
数据库
create table t_user (
id int primary key auto_increment,
name varchar(50) not null unique,
password char(50) not null,
age tinyint,
register_date datetime not null default now()
)
insert into t_user(name,password,age) values('小李',1234,12),
('小黄',1234,12),
('小唐',1234,12)
实体类
import java.util.Date;
public class User {
private Integer id;
private String name;
private String password;
private Integer age;
private Date registerDate;
public User() {
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", password='" + password + '\'' +
", age=" + age +
", registerDate=" + registerDate +
'}';
}
public User(Integer id, String name, String password, Integer age, Date registerDate) {
this.id = id;
this.name = name;
this.password = password;
this.age = age;
this.registerDate = registerDate;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getRegisterDate() {
return registerDate;
}
public void setRegisterDate(Date registerDate) {
this.registerDate = registerDate;
}
}
userDao类
userDao继承BaseDao,在此处可以写SQL语句
import com.example.javaweb.增删改查封装.pojo.User;
import java.util.List;
public class UserDao extends BaseDao<User> {
//新增
public int add(User user) {
String sql = "insert into t_user(name,password,age)value(?,?,?)";
return update(sql, user.getName(),user.getPassword(),user.getAge());
}
//删除
public int del(int id){
String sql = "delete from t_user where id=?";
return update(sql,id);
}
//修改
public int amend(int id,String name){
String sql = "update t_user set name=? where id=?";
return update(sql,name,id);
}
//根据id查询
public User queryByid(int id){
String sql = "select * from t_user where id=?";
return getBean(sql,id);
}
//查询所有信息
public List<User> queryAll(){
String sql = "select * from t_user";
return getBeanList(sql);
}
//查询总数
public int queryCount(){
String sql = "select count(*) from t_user";
Object value = getValue(sql);
return Integer.parseInt(value.toString());
}
}
Main运行
import com.example.javaweb.增删改查封装.dao.UserDao;
import com.example.javaweb.增删改查封装.pojo.User;
import java.util.List;
public class Main{
public static void main(String[] args) {
UserDao userDao = new UserDao();
int up = userDao.amend(1,"隔壁老王");
System.out.println("修改:"+up);
User u = new User();
u.setName("小明");
u.setPassword("123");
u.setAge(18);
System.out.println("新增:"+userDao.add(u));
System.out.println("删除:"+userDao.del(3));
System.out.println("查询对象:"+userDao.queryByid(1));
List<User> users = userDao.queryAll();
for (User user : users) {
System.out.println("查询集合"+user);
}
System.out.println("查询总数:"+userDao.queryCount());
}
}
结果: