锐客网

  1. 首页 > it技术 > >

java|实体bean 根据JPA注解 @table @column 构建sql 语句

1.ClassUtils 辅助类可以传参 实体及属性获取方法名及列名
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.util.WeakHashMap;


import javax.persistence.Column;
import javax.persistence.JoinColumn;
import javax.persistence.Table;


import org.apache.log4j.Logger;






/**
* 类辅助
*
* User: dking
*/
public class ClassUtils {
/** 日志对象 */
private static final Logger LOG = Logger.getLogger(ClassUtils.class);
private Class entity;
/**
* Map keyed by class containing CachedIntrospectionResults.
* Needs to be a WeakHashMap with WeakReferences as values to allow
* for proper garbage collection in case of multiple class loaders.
*/
private static final Map classCache = Collections
.synchronizedMap(new WeakHashMap());
/**
* 获取类本身的BeanInfo，不包含父类属性
*
* @param clazz
* @return
*/
public static BeanInfo getSelfBeanInfo(Class clazz) {
try {
BeanInfo beanInfo;
if (classCache.get(clazz) == null) {
beanInfo = Introspector.getBeanInfo(clazz, clazz.getSuperclass());
classCache.put(clazz, beanInfo);
// Immediately remove class from Introspector cache, to allow for proper
// garbage collection on class loader shutdown - we cache it here anyway,
// in a GC-friendly manner. In contrast to CachedIntrospectionResults,
// Introspector does not use WeakReferences as values of its WeakHashMap!
Class classToFlush = clazz;
do {
Introspector.flushFromCaches(classToFlush);
classToFlush = classToFlush.getSuperclass();
} while (classToFlush != null);
} else {
beanInfo = classCache.get(clazz);
}
return beanInfo;
} catch (IntrospectionException e) {
LOG.error("获取BeanInfo失败", e);
//throw new MincoderException(e);
}
return null;
}
/**
*
获得表名
@param clazz 映射到数据库的po类
@return String
*/
@SuppressWarnings({"unchecked","rawtypes"})
public static String getTableName(Class clazz) {
Table annotation = (Table)clazz.getAnnotation(Table.class);
if(annotation != null){
System.out.println("annotation.name():"+annotation.name());
return annotation.name();
}
return null;
}
/**
*
获得列名
@param clazz 映射到数据库的po类
@param icol 第几列
@return String


*/


@SuppressWarnings({"unchecked","rawtypes"})


public static String getColumnName(Class clazz, String name) {
try{
Method method = clazz.getDeclaredMethod(name, clazz);
// Method method= clazz.getMethod(name, clazz);
// Method method= clazz.getMethod(name, clazz.getClasses());
Column c = method.getAnnotation(Column.class);
return c.name();

}catch(Exception e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
return null;


}
/**
* 获取方法名
* @param clazz
* @param field
* @return
*/
@SuppressWarnings({"rawtypes"})
public static String getMethodName(Class clazz, String field) {
try{
PropertyDescriptor pd = new PropertyDescriptor(field, clazz);
if (pd != null) {
Method getMethod = pd.getReadMethod(); //获得get方法
Column column = getMethod.getAnnotation(Column.class);
// System.out.println(getMethod.getName()+" "+column.name());
return getMethod.getName();
}
}catch(Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 获取@column 注解值
* @param clazz
* @param field
* @return
*/
@SuppressWarnings({"rawtypes"})
public static String getMethodNameColumn(Class clazz, String field) {
try{
PropertyDescriptor pd = new PropertyDescriptor(field, clazz);
if (pd != null) {
Method getMethod = pd.getReadMethod(); //获得get方法
Column column = getMethod.getAnnotation(Column.class);
// System.out.println(getMethod.getName()+" "+column.name());
return column.name();
}
}catch(Exception e) {
e.printStackTrace();
}
return null;
}
/*
*
* Discription : 获取字段名称 并将数据放入map集合中
*
* @param clazz
* @return Map
* @throws
* @author : dking
* @date 2013-9-30 上午9:51:49
*/
public static Map getColumnNames(Class clazz) {
Map map = new HashMap();
Method[] methods = clazz.getMethods();
for (Method method : methods) {
Column c = method.getAnnotation(Column.class); // 获取注视中注解的对象
if (null != c) {
map.put(c.name(),c.name());
} else {
JoinColumn jc = method.getAnnotation(JoinColumn.class); // 获取外键的字段名称
if (null != jc) {
map.put(jc.name(),jc.name());
}
}
}
return map;
}
/**
* 初始化实例
*
* @param clazz
* @return
* @throws IllegalAccessException
* @throws InstantiationException
*/
public static Object newInstance(Class clazz) throws InstantiationException, IllegalAccessException {
return clazz.newInstance();
}
public static void main(String[] args) {
}
}


2. 执行sql的上下文内容，返回执行sql的语句及参数
import java.util.List;


/**
* 执行sql的上下文内容
*
* User: dking
*/
public class SqlContext {
/** 执行的sql */
private StringBuilder sql;
/** 主键名称 */
private String primaryKey;
/** 参数，对应sql中的?号 */
private Listparams;
public SqlContext(StringBuilder sql, String primaryKey, List params) {
this.sql = sql;
this.primaryKey = primaryKey;
this.params = params;
}
//getter setter
public StringBuilder getSql() {
return sql;
}
public void setSql(StringBuilder sql) {
this.sql = sql;
}
public String getPrimaryKey() {
return primaryKey;
}
public void setPrimaryKey(String primaryKey) {
this.primaryKey = primaryKey;
}
public List getParams() {
return params;
}
public void setParams(List params) {
this.params = params;
}

}

【java|实体bean 根据JPA注解 @table @column 构建sql 语句】3.entity 映射成 sql 语句

import java.beans.BeanInfo;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.List;


import org.apache.log4j.Logger;


/**
* entity 映射成 sql 语句
*
* User: dking
*/
public class SqlUtils {
/** 日志对象 */
private static final Logger LOG = Logger.getLogger(SqlUtils.class);
/**
* 构建insert语句
*
* @param entity 实体映射对象
* @return
*/
public static SqlContext buildInsertSql(Object entity) {
Class clazz = entity.getClass();
String tableName = ClassUtils.getTableName(clazz);
String primaryName = ClassUtils.getMethodNameColumn(clazz, "id");
//String primaryName = ClassUtils.getMethodNameColumn(clazz, "getId");
StringBuilder sql = new StringBuilder("insert into ");
List params = new ArrayList();
sql.append(tableName);
//获取属性信息
BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
sql.append("(");
StringBuilder args = new StringBuilder();
args.append("(");
for (PropertyDescriptor pd : pds) {
Object value = https://www.it610.com/article/getReadMethodValue(pd.getReadMethod(), entity);
if (value =https://www.it610.com/article/= null) {
continue;
}
sql.append(ClassUtils.getMethodNameColumn(clazz, pd.getName()));
args.append("?");
params.add(value);
sql.append(",");
args.append(",");
}
sql.deleteCharAt(sql.length() - 1);
args.deleteCharAt(args.length() - 1);
args.append(")");
sql.append(")");
sql.append(" values ");
sql.append(args);
return new SqlContext(sql, primaryName, params);
}
/**
* 构建更新sql
*
* @param entity
* @param nameHandler
* @return
*/
public static SqlContext buildUpdateSql(Object entity) {
Class clazz = entity.getClass();
StringBuilder sql = new StringBuilder();
List params = new ArrayList();
String tableName = ClassUtils.getTableName(clazz);
String primaryName = ClassUtils.getMethodNameColumn(clazz, "id");
//获取属性信息
BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
sql.append("update ");
sql.append(tableName);
sql.append(" set ");
Object primaryValue = https://www.it610.com/article/null;
for (PropertyDescriptor pd : pds) {
Object value = https://www.it610.com/article/getReadMethodValue(pd.getReadMethod(), entity);
if (value =https://www.it610.com/article/= null) {
continue;
}
String columnName = ClassUtils.getMethodNameColumn(clazz, pd.getName());
if (primaryName.equalsIgnoreCase(columnName)) {
primaryValue = https://www.it610.com/article/value;
}
sql.append(columnName);
sql.append(" = ");
sql.append("?");
params.add(value);
sql.append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" where ");
sql.append(primaryName);
sql.append(" = ?");
params.add(primaryValue);
return new SqlContext(sql, primaryName, params);
}
/**
* 构建查询条件
*
* @param entity
* @param nameHandler
*/
public static SqlContext buildQueryCondition(Object entity) {
//获取属性信息
BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(entity.getClass());
//PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entityClass);
PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
StringBuilder condition = new StringBuilder();
List params = new ArrayList();
int count = 0;
for (PropertyDescriptor pd : pds) {
Object value = https://www.it610.com/article/getReadMethodValue(pd.getReadMethod(), entity);
if (value =https://www.it610.com/article/= null) {
continue;
}
if (count > 0) {
condition.append(" and ");
}
condition.append(ClassUtils.getMethodNameColumn(entity.getClass(), pd.getName()));
condition.append(" = ?");
params.add(value);
count++;
}
return new SqlContext(condition, null, params);
}
/**
* 获取属性值
*
* @param readMethod
* @param entity
* @return
*/
private static Object getReadMethodValue(Method readMethod, Object entity) {
if (readMethod == null) {
return null;
}
try {
if (!Modifier.isPublic(readMethod.getDeclaringClass().getModifiers())) {
readMethod.setAccessible(true);
}
return readMethod.invoke(entity);
} catch (Exception e) {
LOG.error("获取属性值失败", e);
try {
throw new Exception(e);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
return null;
}
}

4.测试用例
System.out.println("ssss");
SportNewsEntity dd = new SportNewsEntity();
dd.setId("222");
SqlContext context = SqlUtils.buildInsertSql(dd);
System.out.println("sql"+context.getSql()+"args "+context.getParams().toArray());



@RequestMapping(params = "doAddSql")
@ResponseBody
public AjaxJson doAddSql(OperateSportsAppEntity operateSportsApp, HttpServletRequest request) {
String message = null;
AjaxJson j = new AjaxJson();
message = "经营高危险性体育项目申请书添加成功";
try{
//必须生成uuid
operateSportsApp.setId(UUIDGenerator.generate());
//动态数据源【缓存】 多数据源主键 详情在多数据源管理模块
DynamicDataSourceEntity dynamicSourceEntity = ResourceUtil.dynamicDataSourceMap.get("mysql");
// DynamicDataSourceEntity dynamicSourceEntity = operateSportsAppService.getEntity(DynamicDataSourceEntity.class, "40288a5e5d3067b9015d306b828d0001");
//动态获取数据源 Spring实现动态数据源
JdbcTemplate jdbcTemplate = DynamicDBUtil.getJdbcTemplate(dynamicSourceEntity);
//sql 上下文件 生成sql语句
SqlContext context = SqlUtils.buildInsertSql(operateSportsApp);
//执行入库操作
jdbcTemplate.update(context.getSql().toString(), context.getParams().toArray());
systemService.addLog(message, Globals.Log_Type_INSERT, Globals.Log_Leavel_INFO);
}catch(Exception e){
e.printStackTrace();
message = "经营高危险性体育项目申请书添加失败";
throw new BusinessException(e.getMessage());
}
j.setMsg(message);
return j;
}



    推荐阅读


    上一篇：uva|uva10048

    下一篇：黑猴子的家（手动故障转移HDFS-HA）