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

只喝水的蚊子

浏览: 33497 次
性别:
来自: 上海

最近访客更多访客>>

uule

xionghu

ifang

蕃薯耀

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

博客分类：

java

最近在写一个公司内部项目，由于觉得配置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

分享到：

quartz时间格式的解释 | quartz的持久化

2014-08-11 13:33
浏览 1003
评论(0)
分类:编程语言
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

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

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

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

评论

发表评论

相关推荐

comet4j java服务端推送消息到web页面实例

web.xml里<filter-mapping>中的<dispatcher>作用

去除JSP中生成html多余空行的方法

Jaxb笔记

Servlet3.0-使用注解定义Servlet

EL表达式-自定义函数

Eclipse使用技巧 - 自定义JavaDoc注解和代码模版，提升开发效率和规范性

最近访客更多访客>>