Java反射 JavaBean對象自動生成插入,更新,刪除,查詢sql語句操作

//生成刪除id為1的語句
User user = new User();
user.setId(1);
String deleteSql = getDeleteSql("user", User.class, user);

//生成查詢id為1的語句
User user = new User();
user.setId(1);
String selectSql = getSelectSql("user", User.class, user);

package com.hims.util;
 
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.hims.bean.User;
import java.lang.reflect.Field;
 
public class ProduceSql {
 //String insertSql = getInsertSql("user", User.class, user);
 
 /**
 * 生成插入語句
 * @param tablename 表明
 * @param t 有數(shù)據(jù)的實體
 * @param <T> 數(shù)據(jù)實體類型 如 User
 */
 public static <T> String getInsertSql(String tablename, T t) throws IllegalArgumentException {
 //insert into table_name (column_name1,column_name2, ...) values (value1,value2, ...)
 boolean flag = false;
 String sql = "";
 Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);
 StringBuffer topHalf = new StringBuffer("insert into "+tablename+" (");
 StringBuffer afterAalf = new StringBuffer("values (");
 for (Field field : fields) {
  if ("ID".equals(field.getName()) || "id".equals(field.getName())){
  continue; //id 自動生成無需手動插入
  }
  topHalf.append(field.getName() + ",");
  if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
  afterAalf.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
  flag = true;
  } else {
  afterAalf.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
  flag = true;
  }
 }
 if (!flag) {
  throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty.You must provide an object with at least one attribute.");
 }
 topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
 afterAalf = new StringBuffer(StrUtil.removeSuffix(afterAalf.toString(), ","));
 topHalf.append(") ");
 afterAalf.append(") ");
 sql = topHalf.toString() + afterAalf.toString();
 return sql;
 }
 
 /**
 * 生成更新語句
 * 必須含有id
 * 數(shù)據(jù)實體中 null 與 空字段不參與更新
 * @param tablename 數(shù)據(jù)庫中的表明
 * @param t 有數(shù)據(jù)的實體
 * @param <T> 數(shù)據(jù)實體類型,如 User
 */
 public static <T> String getUpdateSql(String tablename, T t) throws IllegalArgumentException {
 //UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where ID=xxx
 //or
 //UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where id=xxx
 boolean flag = false;
 String sql = "";
 String id = ""; //保存id列名：ID or id
 Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);
 sql = "update "+tablename+" set ";
 for (Field field : fields) {
  StringBuffer tmp = new StringBuffer();
  if ("ID".equals(field.getName()) || "id".equals(field.getName())){
  id = field.getName();
  continue;//更新的時候無需set id=xxx
  }
  if (ReflectUtil.getFieldValue(t, field.getName()) != null && (String)ReflectUtil.getFieldValue(t, field.getName()) != "") {
  tmp.append( field.getName() + "=");
  if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
   tmp.append( "'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
   flag = true;
  } else {
   tmp.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
   flag = true;
  }
  sql += tmp;
  }
 }
 if (!flag) {
  throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty except for ID.You must provide an object with at least one attribute exclude ID.");
 }
 sql = StrUtil.removeSuffix(sql, ",") + " where " + id + "='" + ReflectUtil.getFieldValue(t, id)+"'";
 return sql;
 }
 
 /**
 * 生成刪除語句
 * 根據(jù) user 中第一個不為空的字段刪除,應該盡量使用 id,提供至少一個非空屬性
 * @param tablename 表明
 * @param t 有數(shù)據(jù)的實體
 * @param <T> 數(shù)據(jù)實體類型 如 User
 */
 public static <T> String getDeleteSql(String tablename, T t) throws IllegalArgumentException {
 //delete from table_name where column_name = value
 return getSelectOrDeleteSql(tablename, t, "delete");
 }
 
 /**
 * 生成查詢語句
 * 根據(jù) user 中第一個不為空的字段查詢
 * @param tablename 表名
 * @param t 有數(shù)據(jù)的實體
 * @param <T> 數(shù)據(jù)實體類型 如 User
 */
 public static <T> String getSelectSql(String tablename, T t) throws IllegalArgumentException {
 //delete from table_name where column_name = value
 return getSelectOrDeleteSql(tablename, t, "select *");
 }
 
 /**
 * 根據(jù) operation 生成一個如：operation from table_name where column_name = value 的sql語句
 * @param tablename
 * @param t
 * @param operation "select *" or "delete"
 * @param <T>
 * @return
 * @throws IllegalArgumentException
 */
 private static <T> String getSelectOrDeleteSql(String tablename, T t, String operation) throws IllegalArgumentException {
 //operation from table_name where column_name = value
 boolean flag = false;
 String sql = "";
 Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);
 StringBuffer topHalf = new StringBuffer(operation + " from " + tablename + " where ");
 for (Field field : fields) {
  if ("ID".equals(field.getName()) || "id".equals(field.getName())) {
  if (ReflectUtil.getFieldValue(t, field.getName()) != null && (int)ReflectUtil.getFieldValue(t, field.getName()) != 0) {
   //id 不為空
   topHalf.append(field.getName() + " = " + ReflectUtil.getFieldValue(t, field.getName()));
   flag = true;
   break;
  }
  }
  else {
  if (ReflectUtil.getFieldValue(t, field.getName()) != null && (String)ReflectUtil.getFieldValue(t, field.getName()) != "") {
   topHalf.append(field.getName() + " = '" + ReflectUtil.getFieldValue(t, field.getName()) + "'");
   flag = true;
   break;
  }
  }
 }
 if (!flag) {
  throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty.You must provide an object with at least one attribute.");
 }
 sql = topHalf.toString();
 return sql;
 }
} 

package com.pdt.util;
 
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List; 
 
public class BeanUtil {
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		System.out.println(getBeanFilesList("com.pdt.bean.Dictionary"));
		
		System.out.println(genCreateTableSql("com.pdt.bean.Dictionary"));
		
		System.out.println(genInsertSql("com.pdt.bean.Dictionary"));
	}
	
	public static String getBeanName(String bean){
		try {
			Class clz = Class.forName(bean);
			String clzStr = clz.toString();
			//得到類名
			String beanName = clzStr.substring(clzStr.lastIndexOf(".")+1).toLowerCase();
			return beanName;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return "";
		}
	}
	
	public static List<String> getBeanPropertyList(String bean){
		try {
			Class clz = Class.forName(bean);
			Field[] strs = clz.getDeclaredFields();
			List<String> propertyList = new ArrayList<String>();
			for (int i = 0; i < strs.length; i++) {
				String protype = strs[i].getType().toString();
				propertyList.add(protype.substring(protype.lastIndexOf(".")+1)+"`"+strs[i].getName());
			}
			return propertyList;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static String getBeanFilesList(String bean){
		try {
			Class clz = Class.forName(bean);
			Field[] strs = clz.getDeclaredFields();
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < strs.length; i++) {
				String protype = strs[i].getType().toString();
				if (!strs[i].getName().equals("tableName")&&!strs[i].getType().equals("List")) {
				 sb.append(strs[i].getName()+",");
				}
			}
			sb.deleteCharAt(sb.toString().lastIndexOf(","));
			return sb.toString();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 生成建表語句
	 * @param bean
	 * @return
	 */
	public static String genCreateTableSql(String bean){
		List<String> beanPropertyList = getBeanPropertyList(bean);
		StringBuffer sb = new StringBuffer("create table wnk_pdt_"+getBeanName(bean)+"(\n");
		for (String string : beanPropertyList) {
			String[] propertys = string.split("`");
			if (!propertys[1].equals("tableName")&&!propertys[1].equals("param")&&!propertys[0].equals("List")) {
				if (propertys[1].equals("id")) {
					sb.append(" id bigint primary key auto_increment,\n");
				} else {
					if (propertys[0].equals("int")) {
						sb.append(" " + propertys[1] + " int default 0 comment '',\n");
					} else if (propertys[0].equals("String")) {
						sb.append(" " + propertys[1] + " varchar(2000) default '' comment '',\n");
					} else if (propertys[0].equals("double")) {
						sb.append(" " + propertys[1] + " double(10,2) default 0.0 comment '',\n");
					} else if (propertys[0].equals("Date")) {
						sb.append(" " + propertys[1] + " datetime comment '',\n");
					}
				}
			}
		}
		sb.append(")");
		sb.deleteCharAt(sb.lastIndexOf(","));
		return sb.toString();
	}
	
	/**
	 * 生成查詢語句
	 * @param bean
	 * @return
	 */
	public static String genSelectAllSql(String bean){
		String filesList = getBeanFilesList(bean);
		return "select \n "+filesList+" \n from \n wnk_pdt_"+getBeanName(bean)+"";
	}
	
	/**
	 * 生成插入語句
	 * @param bean
	 * @return
	 */
	public static String genInsertSql(String bean){
		String filesList = getBeanFilesList(bean);
		int fl = DataUtil.getCountSonStr(filesList,",")+1;
		String wenhao = "";
		for (int i = 0; i < fl; i++) {
			if(i==fl-1){
				wenhao = wenhao+"&#63;";
			}else{
				wenhao = wenhao+"&#63;,";
			}
		}
		return "insert into wnk_pdt_"+getBeanName(bean)+"("+filesList+") values("+wenhao+")";
	}
}