1.DBUtil类
package com.cdsxt.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;
import com.mysql.jdbc.PreparedStatement;
public class DBUtil {
private static String mysqlDriver;
private static String url;
private static String user;
private static String password;
static{
try {
InputStream is=DBUtil.class.getResourceAsStream("/db.properties");
Properties ps=new Properties();
ps.load(is);
mysqlDriver=ps.getProperty("mysqlDriver");
url=ps.getProperty("url");
user=ps.getProperty("user");
password=ps.getProperty("password");
Class.forName(mysqlDriver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取数据库连接
public static Connection getConn(){
Connection conn=null;
try {
conn= DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
System.out.println("获取数据库链接异常");
}
return conn;
}
//关闭资源
public static void close(Object...objs){
if(objs!=null&&objs.length>0){
for(int i=0;i<objs.length;i++){
try {
if(objs[i] instanceof Connection){
((Connection)objs[i]).close();
}else if(objs[i] instanceof PreparedStatement){
((PreparedStatement)objs[i]).close();
}else if(objs[i] instanceof ResultSet){
((ResultSet)objs[i]).close();
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("关闭资源 异常");
}
}
}
}
}
2.封装代码;
package com.cdsxt.base;
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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import com.cdsxt.po.Person;
import com.cdsxt.util.DBUtil;
//基于泛型类
public class BaseDao3<T> {
Class<T> clazz;
public BaseDao3(){
Type type=this.getClass().getGenericSuperclass();
Type[] types=((ParameterizedType)type).getActualTypeArguments();
clazz=(Class<T>)types[0];
}
//查询表带参数
public List<T> queryList(T t){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String sql=getQuerySql(t);
List<T> list=new ArrayList<T>();
try {
conn=DBUtil.getConn();
ps=conn.prepareStatement(sql);
Field[] fields=clazz.getDeclaredFields();
int c=1;
for(int i=0;i<fields.length;i++){
fields[i].setAccessible(true);
//填坑
if(fields[i].get(t)!=null){
String fieldName=fields[i].getName();
Method method=clazz.getMethod(getGetter(fieldName));
Object obj=method.invoke(t);
ps.setObject(c, obj);
fields[i].setAccessible(false);
c++;
}
}
rs=ps.executeQuery();
ResultSetMetaData metaData=rs.getMetaData();
int count=metaData.getColumnCount();
while(rs.next()){
T obj=(T)clazz.newInstance();
for(int i=0;i<count;i++){
String fieldName=metaData.getColumnName(i+1);
Field field=clazz.getDeclaredField(fieldName);
Method method=clazz.getMethod(getSetter(fieldName), field.getType());
method.invoke(obj, rs.getObject(i+1));
}
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtil.close(rs,ps,conn);
}
return list;
}
//查询单个po
public T queryPo(int id){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
T t=null;
String sql="select * from "+clazz.getSimpleName()+" where id=?";
try {
t=(T)clazz.newInstance();
conn=DBUtil.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1, id);
rs=ps.executeQuery();
ResultSetMetaData metaData =rs.getMetaData();
int count=metaData.getColumnCount();
while(rs.next()){
for(int i=0;i<count;i++){
String fieldName=metaData.getColumnName(i+1);
Field filed =clazz.getDeclaredField(fieldName);
Method method=clazz.getMethod(getSetter(fieldName), filed.getType());
method.invoke(t, rs.getObject(i+1));
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtil.close(rs,ps,conn);
}
return t;
}
//获取符合条件的po 个数
public int queryCount(T t){
return queryList(t).size();
}
//添加po
public void insertPo(T t){
Connection conn=null;
PreparedStatement ps=null;
String sql=getInsertSql(t);
try {
conn=DBUtil.getConn();
ps=conn.prepareStatement(sql);
Field[] fields=clazz.getDeclaredFields();
int c=1;
//填坑
for(int i=0;i<fields.length;i++){
fields[i].setAccessible(true);
//填坑
if(fields[i].get(t)!=null){
String fieldName=fields[i].getName();
Method method=clazz.getMethod(getGetter(fieldName));
Object obj=method.invoke(t);
ps.setObject(c, obj);
fields[i].setAccessible(false);
c++;
}
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtil.close(ps,conn);
}
}
//修改po
public void updatePo(T t,int id){
Connection conn=null;
PreparedStatement ps=null;
String sql=getUpdateSql(t, id);
try {
conn=DBUtil.getConn();
ps=conn.prepareStatement(sql);
Field[] fields=clazz.getDeclaredFields();
int c=1;
//填坑
for(int i=0;i<fields.length;i++){
fields[i].setAccessible(true);
//填坑
if(fields[i].get(t)!=null){
String fieldName=fields[i].getName();
Method method=clazz.getMethod(getGetter(fieldName));
Object obj=method.invoke(t);
ps.setObject(c, obj);
fields[i].setAccessible(false);
c++;
}
}
ps.setInt(c, id);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtil.close(ps,conn);
}
}
//删除
public void deletePo(int id){
Connection conn=null;
PreparedStatement ps=null;
String sql="delete from "+clazz.getSimpleName()+" where id=?";
System.out.println(sql);
try {
conn=DBUtil.getConn();
ps=conn.prepareStatement(sql);
ps.setInt(1, id);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtil.close(ps,conn);
}
}
//获取查询的sql语句
public String getQuerySql(T t){
String sql="select * from "+clazz.getSimpleName()+" where 1=1 ";
try {
Field[] fields=clazz.getDeclaredFields();
for(int i=0;i<fields.length;i++){
fields[i].setAccessible(true);
if(fields[i].get(t)!=null){
sql+= " and " +fields[i].getName()+"=?";
}
}
sql=sql.substring(0,sql.length());
} catch (Exception e) {
e.printStackTrace();
}
return sql;
}
//获取添加的sql语句
public String getInsertSql(T t){
String sql="insert into "+clazz.getSimpleName()+"(";
String param="";
Field[] fields=clazz.getDeclaredFields();
try {
for(int i=0;i<fields.length;i++){
fields[i].setAccessible(true);
if(fields[i].get(t)!=null){
sql+=fields[i].getName()+",";
param+="?,";
}
}
sql=sql.substring(0,sql.length()-1)+") values("+param;
sql=sql.substring(0,sql.length()-1)+")";
} catch (Exception e) {
e.printStackTrace();
}
return sql;
}
//获取修改的sql语句
public String getUpdateSql(T t,int id){
String sql="update "+clazz.getSimpleName()+" set ";
Field[] fields=clazz.getDeclaredFields();
try {
for(int i=0;i<fields.length;i++){
fields[i].setAccessible(true);
if(fields[i].get(t)!=null){
sql+=fields[i].getName()+"=?,";
}
}
} catch (Exception e) {
e.printStackTrace();
}
sql=sql.substring(0,sql.length()-1)+" where id =?";
return sql;
}
//获取set方法的方法名
public static String getSetter(String fieldName){
//传入属性名 拼接set方法
return "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
}
//获取get方法的方法名
public static String getGetter(String fieldName){
//传入属性名 拼接set方法
return "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
}
}