反射+注释，根据实体类对象生成SQL语句工具类

最近在写一个公司内部项目，由于觉得配置Hibernate过于繁琐，索性使用了spring的jdbc，可是又要写很多的sql语句，为了偷偷懒，于是就写个能通过实体类对象生成SQL语句的工具类。

目前只在MySql数据库上实验通过，其他数据库未测试。

本工具类还有很多不足之处，不过好在可以满足自己一些简单的日常使用。

上代码了。

字段类型：

package net.tjnwdseip.util;public enum FieldType {STRING,NUMBER,DATE}

字段注释：

package net.tjnwdseip.util;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Documented@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.FIELD)public @interface FieldAnnotation {String fieldName();FieldType fieldType();boolean pk();}

表名注释：

package net.tjnwdseip.util;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Documented@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.TYPE)public @interface TableAnnotation {String tableName();}

SQL语句生成工具类：

package net.tjnwdseip.util;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;/** *  * @ClassName: CreateSqlTools * @Description: TODO(根据实体类对象生成SQL语句) * @author LiYang * @date 2012-5-4 下午10:07:03 *  */public class CreateSqlTools {/** *  * @Title: getTableName * @Description: TODO(获取表名) * @param @param obj * @param @return 设定文件 * @return String 返回类型 * @throws */private static String getTableName(Object obj) {String tableName = null;if (obj.getClass().isAnnotationPresent(TableAnnotation.class)) {tableName = obj.getClass().getAnnotation(TableAnnotation.class).tableName();}return tableName;}/** *  * @Title: getAnnoFieldList * @Description: TODO(获取所有有注释的字段,支持多重继承) * @param @param obj * @param @return 设定文件 * @return List<Field> 返回类型 * @throws */@SuppressWarnings("rawtypes")private static List<Field> getAnnoFieldList(Object obj) {List<Field> list = new ArrayList<Field>();Class superClass = obj.getClass().getSuperclass();while (true) {if (superClass != null) {Field[] superFields = superClass.getDeclaredFields();if (superFields != null && superFields.length > 0) {for (Field field : superFields) {if (field.isAnnotationPresent(FieldAnnotation.class)) {list.add(field);}}}superClass = superClass.getSuperclass();} else {break;}}Field[] objFields = obj.getClass().getDeclaredFields();if (objFields != null && objFields.length > 0) {for (Field field : objFields) {if (field.isAnnotationPresent(FieldAnnotation.class)) {list.add(field);}}}return list;}/** *  * @Title: getFieldValue * @Description: TODO(获取字段的值,支持多重继承) * @param @param obj * @param @param field * @param @return 设定文件 * @return String 返回类型 * @throws */@SuppressWarnings({ "rawtypes" })private static String getFieldValue(Object obj, Field field) {String value = null;String name = field.getName();String methodName = "get" + name.substring(0, 1).toUpperCase()+ name.substring(1);Method method = null;Object methodValue = null;try {method = obj.getClass().getMethod(methodName);} catch (NoSuchMethodException | SecurityException e1) {// TODO Auto-generated catch block}if (method != null) {try {methodValue = method.invoke(obj);} catch (IllegalAccessException | IllegalArgumentException| InvocationTargetException e) {// TODO Auto-generated catch block}if (methodValue != null) {value = methodValue.toString();} else {Class objSuperClass = obj.getClass().getSuperclass();while (true) {if (objSuperClass != null) {try {methodValue = method.invoke(objSuperClass);} catch (IllegalAccessException| IllegalArgumentException| InvocationTargetException e) {// TODO Auto-generated catch block}if (methodValue != null) {value = methodValue.toString();break;} else {objSuperClass = objSuperClass.getSuperclass();}} else {break;}}}}return value;}/** *  * @Title: getInsertSql * @Description: TODO(根据实体类对象字段的值生成INSERT SQL语句,可选固定参数) * @param @param obj * @param @param fixedParams *        固定参数(如该参数与实体类中有相同的字段,则忽略实体类中的对应字段,HashMap<String *        ,String>,key=指定字段名,value=对应字段的值) * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getInsertSql(Object obj,HashMap<String, String> fixedParams) {String insertSql = null;String tableName = getTableName(obj);if (tableName != null) {StringBuffer sqlStr = new StringBuffer("INSERT INTO ");StringBuffer valueStr = new StringBuffer(" VALUES (");List<Field> annoFieldList = getAnnoFieldList(obj);if (annoFieldList != null && annoFieldList.size() > 0) {sqlStr.append(tableName + " (");if (fixedParams != null && fixedParams.size() > 0) {Iterator<String> keyNames = fixedParams.keySet().iterator();while (keyNames.hasNext()) {String keyName = (String) keyNames.next();sqlStr.append(keyName + ",");valueStr.append(fixedParams.get(keyName) + ",");}}for (Field field : annoFieldList) {FieldAnnotation anno = field.getAnnotation(FieldAnnotation.class);if (!anno.pk()) {Object fieldValue = getFieldValue(obj, field);if (fieldValue != null) {if (fixedParams != null && fixedParams.size() > 0) {Iterator<String> keyNames = fixedParams.keySet().iterator();boolean nextFieldFlag = false;while (keyNames.hasNext()) {String keyName = (String) keyNames.next();if (anno.fieldName().equals(keyName)) {nextFieldFlag = true;break;}}if (nextFieldFlag) {break;}}sqlStr.append(anno.fieldName() + ",");switch (anno.fieldType()) {case NUMBER:valueStr.append(fieldValue + ",");break;default:valueStr.append("'" + fieldValue + "',");break;}}}}insertSql = sqlStr.toString().substring(0, sqlStr.length() - 1)+ ")"+ valueStr.toString().substring(0,valueStr.length() - 1) + ")";}}return insertSql;}/** *  * @Title: getInsertSql * @Description: TODO(根据实体类对象字段的值生成INSERT SQL语句) * @param @param obj * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getInsertSql(Object obj) {return getInsertSql(obj, null);}/** *  * @Title: getUpdateSql * @Description: TODO(根据实体类对象字段的值生成UPDATE SQL语句,可选更新条件为主键,可选固定更新参数) * @param @param obj * @param @param reqPk 是否指定更新条件为主键(true=是,false=否) * @param @param fixedParams *        固定参数(如该参数与实体类中有相同的字段,则忽略实体类中的对应字段,HashMap<String *        ,String>,key=指定字段名,value=对应字段的值) * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getUpdateSql(Object obj, boolean reqPk,HashMap<String, String> fixedParams) {String updateSql = null;String tableName = getTableName(obj);if (tableName != null) {List<Field> annoFieldList = getAnnoFieldList(obj);if (annoFieldList != null && annoFieldList.size() > 0) {StringBuffer sqlStr = new StringBuffer("UPDATE " + tableName);StringBuffer valueStr = new StringBuffer(" SET ");String whereStr = " WHERE ";if (fixedParams != null && fixedParams.size() > 0) {Iterator<String> keyNames = fixedParams.keySet().iterator();while (keyNames.hasNext()) {String keyName = (String) keyNames.next();valueStr.append(keyName + "="+ fixedParams.get(keyName) + ",");}}for (Field field : annoFieldList) {String fieldValue = getFieldValue(obj, field);if (fieldValue != null) {FieldAnnotation anno = field.getAnnotation(FieldAnnotation.class);if (!anno.pk()) {if (fixedParams != null && fixedParams.size() > 0) {boolean nextFieldFlag = false;Iterator<String> keyNames = fixedParams.keySet().iterator();while (keyNames.hasNext()) {String keyName = (String) keyNames.next();if (anno.fieldName().equals(keyName)) {nextFieldFlag = true;break;}}if (nextFieldFlag) {break;}}valueStr.append(anno.fieldName() + "=");switch (anno.fieldType()) {case NUMBER:valueStr.append(fieldValue + ",");break;default:valueStr.append("'" + fieldValue + "',");break;}} else {if (reqPk) {whereStr += anno.fieldName() + "=" + fieldValue;}}}}updateSql = sqlStr.toString()+ valueStr.toString().substring(0,valueStr.length() - 1)+ (reqPk ? whereStr : "");}}return updateSql;}/** *  * @Title: getUpdateSql * @Description: TODO(根据实体类对象字段的值生成UPDATE SQL语句,无条件) * @param @param obj * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getUpdateSql(Object obj) {return getUpdateSql(obj, false, null);}/** *  * @Title: getUpdateSql * @Description: TODO(根据实体类对象字段的值生成UPDATE SQL语句,可选更新条件为主键) * @param @param obj * @param @param reqPk 是否指定更新条件为主键(true=是,false=否) * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getUpdateSql(Object obj, boolean reqPk) {return getUpdateSql(obj, reqPk, null);}/** *  * @Title: getDeleteSql * @Description: TODO(根据实体类对象字段的值生成有条件的DELETE *               SQL语句，可选主键为删除条件或使用各个字段的值为条件，多个条件用AND连接) * @param @param obj * @param @param reqPk 是否指定更新条件为主键(true=是,false=否) * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getDeleteSql(Object obj, boolean reqPk) {String deleteSql = null;String tableName = getTableName(obj);if (tableName != null) {StringBuffer delSqlBuffer = new StringBuffer("DELETE FROM ");List<Field> annoFieldList = getAnnoFieldList(obj);if (annoFieldList != null && annoFieldList.size() > 0) {delSqlBuffer.append(tableName + " WHERE ");for (Field field : annoFieldList) {if (reqPk) {FieldAnnotation anno = field.getAnnotation(FieldAnnotation.class);if (anno.pk()) {String fieldValue = getFieldValue(obj, field);delSqlBuffer.append(anno.fieldName() + "=");switch (anno.fieldType()) {case NUMBER:delSqlBuffer.append(fieldValue);break;default:delSqlBuffer.append("'" + fieldValue + "'");break;}break;}} else {String fieldValue = getFieldValue(obj, field);if (fieldValue != null) {FieldAnnotation anno = field.getAnnotation(FieldAnnotation.class);delSqlBuffer.append(anno.fieldName() + "=");switch (anno.fieldType()) {case NUMBER:delSqlBuffer.append(fieldValue + " AND ");break;default:delSqlBuffer.append("'" + fieldValue + "' AND ");break;}}}}if (reqPk) {deleteSql = delSqlBuffer.toString();} else {deleteSql = delSqlBuffer.toString().substring(0,delSqlBuffer.length() - 5);}}}return deleteSql;}/** *  * @Title: getDeleteSql * @Description: TODO(根据实体类对象字段的值生成有条件的DELETE SQL语句，使用各个字段的值为条件，多个条件用AND连接) * @param @param obj * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getDeleteSql(Object obj) {return getDeleteSql(obj, false);}/** *  * @Title: getSelectAllSql * @Description: TODO(根据实体类对象字段的值生成SELECT SQL语句,无查询条件) * @param @param obj * @param @return 设定文件 * @return String 返回类型 * @throws */public static String getSelectAllSql(Object obj) {String selectSql = null;String tableName = getTableName(obj);if (tableName != null) {StringBuffer selectBuffer = new StringBuffer("SELECT ");List<Field> annoFieldList = getAnnoFieldList(obj);if (annoFieldList != null && annoFieldList.size() > 0) {for (Field field : annoFieldList) {FieldAnnotation anno = field.getAnnotation(FieldAnnotation.class);selectBuffer.append(anno.fieldName() + ",");}selectSql = selectBuffer.toString().substring(0,selectBuffer.length() - 1)+ " FROM " + tableName;}}return selectSql;}}

实体类注释写法：

package net.tjnwdseip.entity;import java.sql.Timestamp;import net.tjnwdseip.util.FieldAnnotation;import net.tjnwdseip.util.FieldType;public class BaseEntity {@FieldAnnotation(fieldName="id",fieldType=FieldType.NUMBER,pk=true)private Integer id;@FieldAnnotation(fieldName="createDate",fieldType=FieldType.DATE, pk = false)private Timestamp createDate;@FieldAnnotation(fieldName="modifyDate",fieldType=FieldType.DATE, pk = false)private Timestamp modifyDate;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Timestamp getCreateDate() {return createDate;}public void setCreateDate(Timestamp createDate) {this.createDate = createDate;}public Timestamp getModifyDate() {return modifyDate;}public void setModifyDate(Timestamp modifyDate) {this.modifyDate = modifyDate;}public BaseEntity(Integer id, Timestamp createDate, Timestamp modifyDate) {super();this.id = id;this.createDate = createDate;this.modifyDate = modifyDate;}public BaseEntity() {super();}}

package net.tjnwdseip.entity;import java.sql.Timestamp;import net.tjnwdseip.util.FieldAnnotation;import net.tjnwdseip.util.FieldType;import net.tjnwdseip.util.TableAnnotation;/** *  * @ClassName: SysNetProxyCfg  * @Description: TODO(网络代理设置)  * @author LiYang  * @date 2012-5-2 下午4:13:08  * */@TableAnnotation(tableName="sysNetProxyCfg")public class SysNetProxyCfg extends BaseEntity {@FieldAnnotation(fieldName = "name", fieldType = FieldType.STRING, pk = false)private String name;@FieldAnnotation(fieldName = "type", fieldType = FieldType.STRING, pk = false)private String type;@FieldAnnotation(fieldName = "proxyHostIp", fieldType = FieldType.STRING, pk = false)private String proxyHostIp;@FieldAnnotation(fieldName = "proxyPort", fieldType = FieldType.NUMBER, pk = false)private Integer proxyPort;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String getProxyHostIp() {return proxyHostIp;}public void setProxyHostIp(String proxyHostIp) {this.proxyHostIp = proxyHostIp;}public Integer getProxyPort() {return proxyPort;}public void setProxyPort(Integer proxyPort) {this.proxyPort = proxyPort;}public SysNetProxyCfg(Integer id, Timestamp createDate,Timestamp modifyDate, String name, String type, String proxyHostIp,Integer proxyPort) {super(id, createDate, modifyDate);this.name = name;this.type = type;this.proxyHostIp = proxyHostIp;this.proxyPort = proxyPort;}public SysNetProxyCfg() {super();}}

测试类：

package net.tjnwdseip.demo;import java.sql.Timestamp;import java.util.HashMap;import net.tjnwdseip.entity.SysNetProxyCfg;import net.tjnwdseip.util.CreateSqlTools;public class DemoTest {public static void main(String[] args) {// TODO Auto-generated method stubSysNetProxyCfg netProxyCfg = new SysNetProxyCfg(1, Timestamp.valueOf("2012-05-04 14:45:35"), null, "netProxyCfgName", "netProxyCfgType", "000.000.000.000", 0);HashMap<String, String> fixedParams=new HashMap<String,String>();fixedParams.put("createDate", "NOW()");fixedParams.put("modifyDate", "NOW()");System.out.println(CreateSqlTools.getDeleteSql(netProxyCfg));System.out.println(CreateSqlTools.getDeleteSql(netProxyCfg, true));System.out.println(CreateSqlTools.getInsertSql(netProxyCfg));System.out.println(CreateSqlTools.getInsertSql(netProxyCfg, fixedParams));System.out.println(CreateSqlTools.getSelectAllSql(netProxyCfg));System.out.println(CreateSqlTools.getUpdateSql(netProxyCfg));System.out.println(CreateSqlTools.getUpdateSql(netProxyCfg, true));System.out.println(CreateSqlTools.getUpdateSql(netProxyCfg, true, fixedParams));}}

测试结果：

DELETE FROM sysNetProxyCfg WHERE id=1 AND createDate='2012-05-04 14:45:35.0' AND name='netProxyCfgName' AND type='netProxyCfgType' AND proxyHostIp='000.000.000.000' AND proxyPort=0DELETE FROM sysNetProxyCfg WHERE id=1INSERT INTO sysNetProxyCfg (createDate,name,type,proxyHostIp,proxyPort) VALUES ('2012-05-04 14:45:35.0','netProxyCfgName','netProxyCfgType','000.000.000.000',0)INSERT INTO sysNetProxyCfg (modifyDate,createDate) VALUES (NOW(),NOW())SELECT id,createDate,modifyDate,name,type,proxyHostIp,proxyPort FROM sysNetProxyCfgUPDATE sysNetProxyCfg SET createDate='2012-05-04 14:45:35.0',name='netProxyCfgName',type='netProxyCfgType',proxyHostIp='000.000.000.000',proxyPort=0UPDATE sysNetProxyCfg SET createDate='2012-05-04 14:45:35.0',name='netProxyCfgName',type='netProxyCfgType',proxyHostIp='000.000.000.000',proxyPort=0 WHERE id=1UPDATE sysNetProxyCfg SET modifyDate=NOW(),createDate=NOW() WHERE id=1