简述
封装了jdbc的一些对数据库的基本操作，不过当了为了应付课设，写的太着急了，有很多地方都可以优化，有兴趣的可以优化一下，复制就可以使用。主要原理就是反射和泛型，有兴趣的可以看看代码，想直接用的复制就可以用，能帮你节省掉写基础增删改查的时间
注意事项
tableMap的存储的是数据库的表和实体类的对应关系，修改成你自己
示例
public DaoTemplate() {
//将T的类型返回给entityClass 方便在方法中的使用
Type type = this.getClass().getGenericSuperclass();
if(type instanceof ParameterizedType){
ParameterizedType parameterizedType = (ParameterizedType) type;
Type[] types = parameterizedType.getActualTypeArguments();
entityClass = (Class<T>) types[0];
}
// 存储T类型和数据库中表的映射关系
tableMap.put("class mycode.entity.StudentEntity", "student");
tableMap.put("class mycode.entity.HomewordEntity", "homeword");
tableMap.put("class mycode.entity.ProblemsInHomeworkEntity", "problems_in_homework");
tableMap.put("class mycode.entity.StudentWorkAssociationEntity", "student_work_association");
tableMap.put("class mycode.entity.TeacherEntity", "teacher");
tableMap.put("class mycode.entity.TeachingClassEntity", "teaching_class");
tableMap.put("class mycode.entity.StudentClassEntity", "student_class");
}
jdbc配置文件
c3p0.user=root
c3p0.password=123456
c3p0.driverClass=com.mysql.cj.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/homework?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
c3p0.initPoolSize=5
c3p0.maxPoolSize=10
代码
Dao模板
package mycode.dao;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import mycode.util.FormatUtil;
import mycode.util.JdbcPollUtils;
public abstract class DaoTemplate<T> {
/*
* tablemap用于存储T类型和数据库中表的映射关系，需要自行配置
*/
private Map<String, String> tableMap = new HashMap<>();
protected Connection con=JdbcPollUtils.getConnnection();
private Class<T> entityClass;
public Class<T> getEntityClass() {
return entityClass;
}
public void setEntityClass(Class<T> entityClass) {
this.entityClass = entityClass;
}
public DaoTemplate() {
//将T的类型返回给entityClass 方便在方法中的使用
Type type = this.getClass().getGenericSuperclass();
if(type instanceof ParameterizedType){
ParameterizedType parameterizedType = (ParameterizedType) type;
Type[] types = parameterizedType.getActualTypeArguments();
entityClass = (Class<T>) types[0];
}
// 存储T类型和数据库中表的映射关系
tableMap.put("class mycode.entity.StudentEntity", "student");
tableMap.put("class mycode.entity.HomewordEntity", "homeword");
tableMap.put("class mycode.entity.ProblemsInHomeworkEntity", "problems_in_homework");
tableMap.put("class mycode.entity.StudentWorkAssociationEntity", "student_work_association");
tableMap.put("class mycode.entity.TeacherEntity", "teacher");
tableMap.put("class mycode.entity.TeachingClassEntity", "teaching_class");
tableMap.put("class mycode.entity.StudentClassEntity", "student_class");
}
public Map<String, String> getTableMap() {
return tableMap;
}
/*
* insert的模板
*/
protected void insertTemplate(PreparedStatement pre, Field[] fields, T o) {
try {
int i = 1;
Class entityClass = o.getClass();
for (Field field : fields) {
// 若属性类型为String执行
if (field.getGenericType().toString().equals("class java.lang.String")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(field.getName())); // 通过拼接的方式获取某个属性的get方法
String val = (String) m.invoke(o); // 通过反射机制执行该get方法
if (val != null) {
pre.setString(i, val);
} else {
pre.setString(i, null);
}
}
// 若属性类型为Integer执行
if (field.getGenericType().toString().equals("class java.lang.Integer")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(field.getName())); // 通过拼接的方式获取某个属性的get方法
Integer val = (Integer) m.invoke(o); // 通过反射机制执行该get方法
if (val != null) {
pre.setInt(i, val);
} else {
pre.setInt(i, 0);
}
}
// 若属性类型为Date类型时执行
if (field.getGenericType().toString().equals("class java.sql.Date")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(field.getName())); // 通过拼接的方式获取某个属性的get方法
Date val = (Date) m.invoke(o); // 通过反射机制执行该get方法
if (val != null) {
pre.setDate(i, val);
} else {
pre.setDate(i, null);
}
}
if (field.getGenericType().toString().equals("class java.lang.Double")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(field.getName())); // 通过拼接的方式获取某个属性的get方法
Double val = (Double) m.invoke(o); // 通过反射机制执行该get方法
if (val != null) {
pre.setDouble(i, val);
} else {
pre.setDouble(i, 0);
}
}
i++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* update的模板
*/
protected void updateTemplate(PreparedStatement pre, Field[] fields, T o) {
try {
int i = 1;
Class entityClass = o.getClass();
for (int j = 1; j < fields.length; j++) {
if (fields[j].getGenericType().toString().equals("class java.lang.String")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(fields[j].getName()));
String val = (String) m.invoke(o);
if (val != null) {
pre.setString(i, val);
i++;
}
}
if (fields[j].getGenericType().toString().equals("class java.lang.Integer")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(fields[j].getName()));
Integer val = (Integer) m.invoke(o);
if (val != null) {
pre.setInt(i, val);
i++;
}
}
if (fields[j].getGenericType().toString().equals("class java.sql.Date")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(fields[j].getName()));
Date val = (Date) m.invoke(o);
if (val != null) {
pre.setDate(i, val);
i++;
}
}
if (fields[j].getGenericType().toString().equals("class java.lang.Double")) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(fields[j].getName()));
Double val = (Double) m.invoke(o);
if (val != null) {
pre.setDouble(i, val);
i++;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void setTableMap(Map<String, String> tableMap) {
this.tableMap = tableMap;
}
private static String getMethodName(String fildeName) throws Exception {
byte[] items = fildeName.getBytes();
items[0] = (byte) ((char) items[0] - 'a' + 'A');
return new String(items);
}
public int insert(T o) {
int a=0;
try {
Class<?> entityClass = o.getClass();
Field[] fields = entityClass.getDeclaredFields();
String sql = "insert into ";
sql += tableMap.get(entityClass.toString());
sql += " values(";
for (int i = 0; i < fields.length; i++) {
sql += "?";
if (i != fields.length - 1)
sql += ",";
}
sql += ")";
PreparedStatement pre = null;
if(con==null)
{
con=JdbcPollUtils.getConnnection();
}
pre = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
insertTemplate(pre, fields, o);
pre.execute();
ResultSet rs = pre.getGeneratedKeys();
if(rs.next()) a = rs.getInt(1);
JdbcPollUtils.close(null, pre, null);
} catch (Exception e) {
e.printStackTrace();
}
return a;
}
public void insert(List<T> list) {
list.forEach(item -> {
this.insert(item);
});
}
public void deleteById(int id) {
try {
if(con==null)
{
con=JdbcPollUtils.getConnnection();
}
String sql = "delete from ";
sql += tableMap.get(entityClass.toString());
sql += " where id=" + id;
PreparedStatement pre = con.prepareStatement(sql);
pre.execute();
JdbcPollUtils.close(null, pre, con);
} catch (Exception e) {
e.printStackTrace();
}
}
public void update(T o) {
try {
Class<?> entityClass = o.getClass();
Field[] fields = entityClass.getDeclaredFields();
/*
* 进行拼接sql语句
*/
String sql = "update ";
sql += tableMap.get(entityClass.toString());
sql += " set ";
for (int i = 1; i < fields.length; i++) {
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(fields[i].getName()));
Object val = (Object) m.invoke(o);
if (val != null) {
sql += FormatUtil.toDatabase(fields[i].getName()) + "=?";
if (i != fields.length - 1)
sql += ",";
}
}
if (sql.charAt(sql.length() - 1) == ',') {
sql = sql.substring(0, sql.length() - 1);
}
Method m;
m = (Method) entityClass.getMethod("get" + getMethodName(fields[0].getName()));
Integer val = (Integer) m.invoke(o);
sql += " where " + fields[0].getName() + "=" + val;
if(con==null)
{
con=JdbcPollUtils.getConnnection();
}
PreparedStatement pre = con.prepareStatement(sql);
updateTemplate(pre, fields, o);
pre.executeUpdate();
JdbcPollUtils.close(null, pre, con);
} catch (Exception e) {
e.printStackTrace();
}
}
protected T getOneByConditions(PreparedStatement pre)
{
T o=null;
try {
Field[] fields = entityClass.getDeclaredFields();
ResultSet r=pre.executeQuery();
if(r.next())
{
o=(T) entityClass.newInstance();
int i=1;
for (Field field : fields) {
Method m;
m = (Method) entityClass.getMethod("set" + getMethodName(field.getName()),field.getType());
String p[]=field.getType().toString().split("[.]");
String mname=null;
if(p[p.length-1].equals("Integer"))
{
mname="Int";
}
else
{
mname=getMethodName2(p[p.length-1]);
}
Method mr=r.getClass().getMethod("get"+mname, int.class);
m.invoke(o, mr.invoke(r,i));
i++;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return o;
}
protected List getListByConditions(PreparedStatement pre)
{
T o=null;
List all=new LinkedList();
try {
Field[] fields = entityClass.getDeclaredFields();
ResultSet r=pre.executeQuery();
while(r.next())
{
o=(T) entityClass.newInstance();
int i=1;
for (Field field : fields) {
Method m;
m = (Method) entityClass.getMethod("set" + getMethodName(field.getName()),field.getType());
String p[]=field.getType().toString().split("[.]");
String mname=null;
if(p[p.length-1].equals("Integer"))
{
mname="Int";
}
else
{
mname=getMethodName2(p[p.length-1]);
}
Method mr=r.getClass().getMethod("get"+mname, int.class);
m.invoke(o, mr.invoke(r,i));
i++;
}
all.add(o);
}
JdbcPollUtils.close(r, pre, con);
} catch (Exception e) {
e.printStackTrace();
}
return all;
}
public T getOne(int id) {
T o=null;
try {
Field[] fields = entityClass.getDeclaredFields();
String sql = "select * from " + tableMap.get(entityClass.toString()) + " where " + fields[0].getName() + "="
+ id;
if(con==null)
{
con=JdbcPollUtils.getConnnection();
}
PreparedStatement pre = con.prepareStatement(sql);
ResultSet r=pre.executeQuery();
if(r.next())
{
o=(T) entityClass.newInstance();
int i=1;
for (Field field : fields) {
Method m;
m = (Method) entityClass.getMethod("set" + getMethodName(field.getName()),field.getType());
String p[]=field.getType().toString().split("[.]");
String mname=null;
if(p[p.length-1].equals("Integer"))
{
mname="Int";
}
else
{
mname=getMethodName2(p[p.length-1]);
}
Method mr=r.getClass().getMethod("get"+mname, int.class);
m.invoke(o, mr.invoke(r,i));
i++;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return o;
}
private String getMethodName2(String name) {
String result="";
if(name!=null)
{
result+=name.toUpperCase().charAt(0);
for(int i=1;i<name.length();i++)
{
result+=name.charAt(i);
}
}
return result;
}
public List<T> getAll(){
T o=null;
List all=null;
try {
all=new LinkedList<>();
if(con==null)
{
con=JdbcPollUtils.getConnnection();
}
Field[] fields = entityClass.getDeclaredFields();
String sql = "select * from " + tableMap.get(entityClass.toString());
PreparedStatement pre = con.prepareStatement(sql);
ResultSet r=pre.executeQuery();
while(r.next())
{
o=(T) entityClass.newInstance();
int i=1;
for (Field field : fields) {
Method m;
m = (Method) entityClass.getMethod("set" + getMethodName(field.getName()),field.getType());
String p[]=field.getType().toString().split("[.]");
String mname=null;
if(p[p.length-1].equals("Integer"))
{
mname="Int";
}
else
{
mname=getMethodName2(p[p.length-1]);
}
Method mr=r.getClass().getMethod("get"+mname, int.class);
m.invoke(o, mr.invoke(r,i));
i++;
}
all.add(o);
}
JdbcPollUtils.close(r, pre, con);
} catch (Exception e) {
e.printStackTrace();
}
return all;
}
}
c3p0.properties
c3p0.user=root
c3p0.password=123456
c3p0.driverClass=com.mysql.cj.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/homework?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
c3p0.initPoolSize=5
c3p0.maxPoolSize=10
JdbcPollUtils
package mycode.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JdbcPollUtils {
private static DataSource dataSource = new ComboPooledDataSource();
private static Connection con = null;
public static Connection getConnnection() {
try {
return dataSource.getConnection();
}
catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet r, Statement statement, Connection con) {
try {
if (r != null) {
r.close();
}
if (statement != null) {
statement.close();
}
if (con != null) {
con.close();
}
} catch (Exception e) {
}
}
}
代码写的篇幅很长，但其实很多都是重复的，有兴趣的可以优化一下。
使用样例
public class StudentClassDao extends DaoTemplate<StudentClassEntity> {
public List<StudentClassEntity> getByCid(int cid) throws SQLException{
String sql="select * from student_class where class_id=?";
if(con==null)
{
con=JdbcPollUtils.getConnnection();
}
PreparedStatement pre=con.prepareStatement(sql);
pre.setInt(1, cid);
return getListByConditions(pre);
}
}
379
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
