最近在写一个公司内部项目,由于觉得配置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 stub
SysNetProxyCfg 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=0
DELETE FROM sysNetProxyCfg WHERE id=1
INSERT 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 sysNetProxyCfg
UPDATE sysNetProxyCfg SET createDate='2012-05-04 14:45:35.0',name='netProxyCfgName',type='netProxyCfgType',proxyHostIp='000.000.000.000',proxyPort=0
UPDATE sysNetProxyCfg SET createDate='2012-05-04 14:45:35.0',name='netProxyCfgName',type='netProxyCfgType',proxyHostIp='000.000.000.000',proxyPort=0 WHERE id=1
UPDATE sysNetProxyCfg SET modifyDate=NOW(),createDate=NOW() WHERE id=1