增删改查
package com.imau.util;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
public class SqlUtil {
private static String tableName="";
private static String sql="";
private static StringBuffer sfield=new StringBuffer();
private static StringBuffer sValue=new StringBuffer();
//填充预处理命令占位符
public static PreparedStatement initPstm(T t,PreparedStatement pstm,SqlType sqlType) {
Field fields[]=t.getClass().getDeclaredFields();
try {
for(int i=1;i<fields.length;i++) {
PropertyDescriptor pd = new PropertyDescriptor(fields[i].getName(),t.getClass());
Method method=pd.getReadMethod();
//添加 修改
if(sqlType==SqlType.Insert) {
//排除主键
if(i!=0) {
pstm.setObject(i, method.invoke(t));
}
}else if(sqlType==SqlType.Update) {
//排除主键
if(i==0) {
pstm.setObject(fields.length, method.invoke(t));
}else {
pstm.setObject(i, method.invoke(t));
}
}else if(sqlType==SqlType.Delete) {//删除
//判断字段是否为主键
if(fields[i].getName().equals("id")) {
pstm.setObject(1, method.invoke(t));
}
}else if(sqlType==SqlType.Query) {
int flag=0;
Object v=method.invoke(t);
if(v!=null ) {
pstm.setObject(i, v);
}
}
}
}catch(Exception e){
e.printStackTrace();
}
return pstm;
}
//返回sql语句 1 2 3 4
public static String getsql(T t,int sqlType) {
tableName= t.getClass().getSimpleName().toLowerCase();
if(sqlType==1) {
sql="insert into “+tableName+” ( ";
Field fields[]=t.getClass().getDeclaredFields();
for(int i=1;i<fields.length;i++) {
if(i!=fields.length-1) {
sfield.append(fields[i].getName()).append(" ,");
sValue.append(" ?, ");
}else {
sfield.append(fields[i].getName());
sValue.append(" ? ");
}
}
sql=sql+sfield.toString()+" ) values ( "+sValue.toString()+" ) ";
}
if(sqlType==2) {
sql="delete from"+ tableName +" where id = ? ";
}
if(sqlType==3) {
sql=" update "+tableName+" set ";
Field fields[]=t.getClass().getDeclaredFields();
for(int i=1;i<fields.length;i++) {
if(i!=fields.length-1) {
sfield.append(fields[i].getName()).append(" =").append("?,");
}else {
sfield.append(fields[i].getName()).append(" =").append("?");
}
}
sql=sql+sfield+" where id = ?";
}
if(sqlType==4) {
try {
int flag=0;
sql="select * from "+tableName+" where ";
Field fields[]=t.getClass().getDeclaredFields();
for(int i=0;i<fields.length;i++) {
PropertyDescriptor pd = new PropertyDescriptor(fields[i].getName(),t.getClass());
Method method=pd.getReadMethod();
Object v=method.invoke(t);
if(v!=null ) {
if(flag!=0) {
sfield.append("and");
sfield.append(fields[i].getName()).append(" = ? ");
}else {
sfield.append(fields[i].getName()).append(" = ? ");
flag=1;
}
}
}
sql=sql+sfield;
}catch(Exception e){
e.printStackTrace();
}
}
return sql;
}
public static <T> String getSql( Class<T> clazz,Object field, String oper,Object arg) {
tableName=clazz.getSimpleName().toLowerCase();
sql="select * from " +tableName;
if(!field.equals("")&&field!=null) {
sql=sql+" where "+field+" "+oper+" ? ";
}
return sql;
}
public static List handlerRs(Class clazz,T t,ResultSet rs) {
ArrayList list=new ArrayList<>();
try {
Field fields[]=clazz.getDeclaredFields();
ResultSetMetaData rsmd=rs.getMetaData();
while(rs.next()) {
T t1=clazz.newInstance();
for(int i=0;i<rsmd.getColumnCount();++i) {
//字段列名->对象属性赋值
String colName=rsmd.getColumnName(i+1);
Object colVau=rs.getObject(colName);
fieldVau(t1,fields,colName,colVau);
}
list.add(t1);
}
return list;
}catch(Exception e){
e.printStackTrace();
}
return null;
}
//处理结果集:映射对象赋值
public static T handlerRs(T t,ResultSet rs) {
try {
Field fields[]=t.getClass().getDeclaredFields();
ResultSetMetaData rsmd=rs.getMetaData();
while(rs.next()) {
for(int i=0;i<rsmd.getColumnCount();++i) {
//字段列名->对象属性赋值
String colName=rsmd.getColumnName(i+1);
Object colVau=rs.getObject(colName);
fieldVau(t,fields,colName,colVau);
}
}
return t;
}catch(Exception e){
e.printStackTrace();
}
return null;
}
public static void fieldVau(T t,Field fields[],String colName,Object colVau) {
try {
for(Field field:fields) {
//找到对应属性
if(field.getName().equals(colName)&&!colName.equals(“id”)) {
PropertyDescriptor pd = new PropertyDescriptor(field.getName(),t.getClass());
Method method=pd.getWriteMethod();
//强制访问方法
//属性赋值
method.invoke(t, colVau);
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
public static String getsql(Class type, Object field, String oper) {
// TODO 自动生成的方法存根
return null;
}
}
package com.imau.util;
public enum SqlType {
Insert,Delete,Update,Query
}