-
DAO:Data Access Object访问数据信息的类和接口,包括了对数据的CRUD(Create、Retrival、Update、Delete),而不包含任何业务相关的信息。
-
在所有的dao中会有很多重复性的工作,我们可以封装一个父类来完成此类重复工作,我们称之为BaseDAO。
一个比较全面的BaseDao
package com.xinzhi.dao.impl;
import com.xinzhi.dao.IBaseDao;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* @author zn
* @date 2020/4/4
**/
public class BaseDaoImpl<T> implements IBaseDao<T> {
private static DataSource DATA_SOURCE = null;
static {
InputStream in = UserDaoImpl.class.getClassLoader().getResourceAsStream("config/jdbc.config");
Properties properties = new Properties();
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
HikariConfig hikariConfig = new HikariConfig(properties);
BaseDaoImpl.DATA_SOURCE = new HikariDataSource(hikariConfig);
}
public Connection getConnection() {
if (BaseDaoImpl.DATA_SOURCE != null) {
try {
return BaseDaoImpl.DATA_SOURCE.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
public void closeAll(Statement statement, ResultSet resultSet) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//简单的通用保存,通过可变参数赋值
public void save(String sql, Object... params) {
PreparedStatement statement = null;
try {
Connection conn = getConnection();
statement = conn.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
statement.setObject(i, params[i]);
}
statement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(statement, null);
}
}
/**
* 高级部分
* 有要求 数据库的名字和类名必须一样
* 每个字段和属性的名字也要一样
* 有规矩好办事,重在体会思想
* 搞明白还能这么干就行了
* 思路:
* 因为规定了数据库名称和类名形同,字段也相同
* 所有可以通过反射获取类名和字段名拼接一个字符串
*
* @return
*/
public List<T> findAll(Class clazz) {
//拼一个sql select id,username,password from user
//其中id,username,password可变但是他是类的字段啊
//user可变但是他是类名啊,反射登场了
List<T> list = new ArrayList<>();
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
//利用反射拼出一个select语句
Field[] fields = clazz.getDeclaredFields();
StringBuilder fieldStr = new StringBuilder();
fieldStr.append("select ");
for (Field field : fields) {
fieldStr.append(field.getName().toLowerCase()).append(",");
}
fieldStr.deleteCharAt(fieldStr.length() - 1);
fieldStr.append(" from ");
fieldStr.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1));
Connection conn = getConnection();
statement = conn.prepareStatement(fieldStr.toString());
resultSet = statement.executeQuery();
while (resultSet.next()) {
Object obj = clazz.newInstance();
for (Field field : fields) {
Object value = resultSet.getObject(field.getName());
field.setAccessible(true);
field.set(obj, value);
}
list.add((T) obj);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(statement, resultSet);
}
return list;
}
public void save(Object obj) {
Class clazz = obj.getClass();
Field[] fields = clazz.getDeclaredFields();
//拼接出一个insert语句
StringBuilder sql = new StringBuilder("insert into ");
sql.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1))
.append(" (");
for (Field field : fields) {
sql.append(field.getName().toLowerCase()).append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(") values (");
for (Field field : fields) {
sql.append("?,");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(")");
System.out.println(sql);
PreparedStatement statement = null;
try {
Connection conn = getConnection();
statement = conn.prepareStatement(sql.toString());
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
statement.setObject(i + 1, fields[i].get(obj));
}
statement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(statement, null);
}
}
@Override
public void update(Object obj, String fieldName, Object fieldValue) {
PreparedStatement statement = null;
try {
Class clazz = obj.getClass();
//拼接出一个update语句
StringBuilder sql = new StringBuilder("update " + clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1)
+ " set ");
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
sql.append(field.getName()).append("=").append("?").append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" where ").append(fieldName).append("=?");
System.out.println(sql);
Connection conn = getConnection();
statement = conn.prepareStatement(sql.toString());
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
statement.setObject(i + 1, fields[i].get(obj));
}
statement.setObject(fields.length + 1, fieldValue);
statement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(statement, null);
}
}
@Override
public void delete(Class clazz, String fieldName, Object fieldValue) {
//拼接一个delete语句
String sql = "delete from " + clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1)
+ " where " + fieldName + "=?";
System.out.println(sql);
PreparedStatement statement = null;
try {
Connection conn = getConnection();
statement = conn.prepareStatement(sql);
statement.setObject(1, fieldValue);
statement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(statement, null);
}
}
@Override
public T findOne(Class clazz, String fieldName, Object fieldValue) {
T t = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
Field[] fields = clazz.getDeclaredFields();
//拼接一个语句
StringBuilder sql = new StringBuilder();
sql.append("select ");
for (Field field : fields) {
sql.append(field.getName().toLowerCase()).append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" from ");
sql.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1))
.append(" where " + fieldName + "=?");
System.out.println(sql.toString());
Connection conn = getConnection();
statement = conn.prepareStatement(sql.toString());
statement.setObject(1,fieldValue);
resultSet = statement.executeQuery();
while (resultSet.next()) {
Object obj = clazz.newInstance();
for (Field field : fields) {
Object value = resultSet.getObject(field.getName());
field.setAccessible(true);
field.set(obj, value);
}
t = (T) obj;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(statement, resultSet);
}
return t;
}
}
自己写的带注释版本的
package com.sdu.reflect;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BaseDaoImpl<T> implements IBaseDao<T> {
private static DataSource DATA_SOURCE = null;
/*使用的是 Hikari(光)数据库连接池
static {
InputStream in = UserDaoImpl.class.getClassLoader().getResourceAsStream("config/jdbc.config");
Properties properties = new Properties();
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
HikariConfig hikariConfig = new HikariConfig(properties);
BaseDaoImpl.DATA_SOURCE = new HikariDataSource(hikariConfig);
}
*/
@Override
public Connection getConnection() {
if (BaseDaoImpl.DATA_SOURCE != null) {
try {
return BaseDaoImpl.DATA_SOURCE.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public void closeAll(Statement statement, ResultSet resultSet) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void save(Object obj) {
Class clzz = obj.getClass();
Connection conn = null;
PreparedStatement statement = null;
Field [] fields = clzz.getDeclaredFields();
StringBuilder sb = new StringBuilder();
sb.append("insert into ").append(clzz.getName().toLowerCase().substring(clzz.getName().lastIndexOf(".")+1));
sb.append(" (");
for (Field field:fields
) {
sb.append(field.getName().toLowerCase()).append(",");
}
sb.deleteCharAt(sb.length()-1);
sb.append(") values (");
System.out.println(sb.toString());
try {
conn = getConnection();
statement = conn.prepareStatement(sb.toString());
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
statement.setObject(i+1, fields[i].get(obj));
}
statement.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
closeAll(statement,null);
}
}
@Override
public List<T> findAll(Class clazz) {
List<T> list = new ArrayList<>();
PreparedStatement statement = null;
ResultSet resultSet = null;
Field [] fields = clazz.getFields();
StringBuilder fieldBuilder = new StringBuilder();
fieldBuilder.append("select ");
for(Field field: fields){
fieldBuilder.append(field.getName().toLowerCase()).append(",");
}
fieldBuilder.deleteCharAt(fieldBuilder.length()-1);
fieldBuilder.append(" from ");
fieldBuilder.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1));
Connection conn = getConnection();
try {
statement = conn.prepareStatement(fieldBuilder.toString());
resultSet = statement.executeQuery();
while (resultSet.next()){
Object obj = clazz.newInstance();
for (Field field: fields){
Object value = resultSet.getObject(field.getName());
field.setAccessible(true);
field.set(obj,value);
}
list.add((T)obj);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
closeAll(statement,resultSet);
}
return list;
}
@Override
public void update(Object obj, String fieldName, Object fieldValue) {
//其实是用传进来的obj中的对象的值来更新数据的值,fieldName是条件名,fieldValue是条件值
ResultSet resultSet = null;
PreparedStatement ps = null;
Class clzz = obj.getClass();
Field[] fields = clzz.getDeclaredFields();
StringBuilder sb = new StringBuilder("update " + clzz.getName().toLowerCase().substring(clzz.getName().lastIndexOf(".")+1) + " set ");
for (Field field:fields
) {
field.setAccessible(true);
sb.append(field.getName()).append("=").append("?").append(",");
}
sb.deleteCharAt(sb.length()-1);
sb.append(" where ").append(fieldName).append("=?");
System.out.println(sb.toString());
Connection conn = getConnection();
try {
ps = conn.prepareStatement(fields.toString());
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
ps.setObject(i+1,fields[i].get(obj));
}
ps.setObject(fields.length + 1, fieldValue);
ps.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}finally {
closeAll(ps,null);
}
}
@Override
public void delete(Class clazz, String fieldName, Object fieldValue) {
String sql = "delete from " + clazz.getName().toString().toLowerCase().substring(clazz.getName().toString().lastIndexOf(".") + 1)
+" where " + fieldName +"=?";
PreparedStatement ps = null;
System.out.println(sql);
Connection conn = getConnection();
try {
ps = conn.prepareStatement(sql);
ps.setObject(1,fieldValue);
ps.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
closeAll(ps,null);
}
}
@Override
public T findOne(Class clazz, String fieldName, Object fieldValue) {
PreparedStatement ps = null;
Connection con = getConnection();
ResultSet rs = null;
T t = null;
Field [] fields = clazz.getDeclaredFields();
StringBuilder sql = new StringBuilder();
sql.append("select ");
for (int i = 0; i < fields.length; i++) {
sql.append(fields[i].toString().toLowerCase()).append(",");
}
sql.deleteCharAt(sql.length()-1);
sql.append(" from ");
sql.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".")+1));
sql.append(" where ").append(fieldName+"=?");
System.out.println(sql.toString());
try {
ps = con.prepareStatement(sql.toString());
ps.setObject(1,fieldValue);
rs = ps.executeQuery();
while(rs.next()){
Object obj = clazz.newInstance();
for (Field field:fields
) {
Object value = rs.getObject(field.getName());
field.setAccessible(true);
field.set(obj,value);
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}finally {
closeAll(ps,rs);
}
return t;
}
}