构建动态sql,其实说白了就是拼装sql语句,在这里我把传入的实体参数,属性有值的拼装进sql,为null的则忽略,要实现这个不用说,肯定要利用Java的反射功能,来看一个具有代表性的insert语句的构建:
[java] view plain copy
- /**
- * 构建insert语句
- *
- * @param entity 实体映射对象
- * @param nameHandler 名称转换处理器
- * @return
- */
- public static SqlContext buildInsertSql(Object entity, NameHandler nameHandler) {
- Class<?> clazz = entity.getClass();
- String tableName = nameHandler.getTableName(clazz.getSimpleName());
- String primaryName = nameHandler.getPrimaryName(clazz.getSimpleName());
- StringBuilder sql = new StringBuilder("insert into ");
- List<Object> params = new ArrayList<Object>();
- 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 = getReadMethodValue(pd.getReadMethod(), entity);
- if (value == null) {
- continue;
- }
- sql.append(nameHandler.getColumnName(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);
- }
众所周知,Java的反射是性能较低的,也有性能较好的第三方实现如cglib,这里并没有使用。在我的实测中两者差距不大。
但是注意这里并没有使用属性的操作方式,也就是没有使用jdk反射获取属性的getDeclaredFields()方法,而是使用了BeanInfo和PropertyDescriptor,因为后者的运行效率要远远高于前者。
在我的实测中,构建一个拥有12个属性的JavaBean的动态sql,十万次所耗时间为900毫秒左右,完全可以接受。当然,这里对JavaBean的信息进行了缓存,如果不缓存时间将多耗上几个数量级。
下面顺便贴上完整的代码:
[java] view plain copy
- /**
- * sql辅助为类
- *
- * User: liyd
- * Date: 2/13/14
- * Time: 10:03 AM
- */
- public class SqlUtils {
- /** 日志对象 */
- private static final Logger LOG = LoggerFactory.getLogger(SqlUtils.class);
- /**
- * 构建insert语句
- *
- * @param entity 实体映射对象
- * @param nameHandler 名称转换处理器
- * @return
- */
- public static SqlContext buildInsertSql(Object entity, NameHandler nameHandler) {
- Class<?> clazz = entity.getClass();
- String tableName = nameHandler.getTableName(clazz.getSimpleName());
- String primaryName = nameHandler.getPrimaryName(clazz.getSimpleName());
- StringBuilder sql = new StringBuilder("insert into ");
- List<Object> params = new ArrayList<Object>();
- 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 = getReadMethodValue(pd.getReadMethod(), entity);
- if (value == null) {
- continue;
- }
- sql.append(nameHandler.getColumnName(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, NameHandler nameHandler) {
- Class<?> clazz = entity.getClass();
- StringBuilder sql = new StringBuilder();
- List<Object> params = new ArrayList<Object>();
- String tableName = nameHandler.getTableName(clazz.getSimpleName());
- String primaryName = nameHandler.getPrimaryName(clazz.getSimpleName());
- //获取属性信息
- BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
- PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
- sql.append("update ");
- sql.append(tableName);
- sql.append(" set ");
- Object primaryValue = null;
- for (PropertyDescriptor pd : pds) {
- Object value = getReadMethodValue(pd.getReadMethod(), entity);
- if (value == null) {
- continue;
- }
- String columnName = nameHandler.getColumnName(pd.getName());
- if (primaryName.equalsIgnoreCase(columnName)) {
- primaryValue = 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, NameHandler nameHandler) {
- //获取属性信息
- BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(entity.getClass());
- // PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entityClass);
- PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
- StringBuilder condition = new StringBuilder();
- List<Object> params = new ArrayList<Object>();
- int count = 0;
- for (PropertyDescriptor pd : pds) {
- Object value = getReadMethodValue(pd.getReadMethod(), entity);
- if (value == null) {
- continue;
- }
- if (count > 0) {
- condition.append(" and ");
- }
- condition.append(nameHandler.getColumnName(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);
- throw new MincoderException(e);
- }
- }
- }
获取BeanInfo时写了一个ClassUtils来实现,里面对Bean信息进行了缓存。因为项目使用spring,本来想使用spring提供的BeanUtils.getPropertyDescriptor()方法的,里面同样拥有缓存,但是该方法会把实体类父类的属性信息也获取出来,而PropertyDescriptor中又没法判断,这将直接导致拼装sql时字段的错误,因为你不知道哪些字段是操作当前表所需要的。没办法,查看jdk本身的Introspector类,发现里面有如下方法定义:
[java] view plain copy
- public static BeanInfo getBeanInfo(Class<?> beanClass, Class<?> stopClass) throws IntrospectionException
即可以指定在哪个类停止获取属性,这正是我们需要的,可惜spring没有进行封装,只能自己实现了,参考了spring的实现,使用WeakHashMap来防止内存的溢出,及时清空Introspector本身的缓存:
- /**
- * 类辅助
- *
- * User: liyd
- * Date: 2/12/14
- * Time: 10:08 PM
- */
- public class ClassUtils {
- /** 日志对象 */
- private static final Logger LOG = LoggerFactory
- .getLogger(ClassUtils.class);
- /**
- * 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<Class, BeanInfo> classCache = Collections
- .synchronizedMap(new WeakHashMap<Class, BeanInfo>());
- /**
- * 获取类本身的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);
- }
- }
- /**
- * 初始化实例
- *
- * @param clazz
- * @return
- */
- public static Object newInstance(Class<?> clazz) {
- try {
- return clazz.newInstance();
- } catch (Exception e) {
- LOG.error("根据class创建实例失败", e);
- throw new MincoderException(e);
- }
- }
- }
- 另外创建了对象SqlContext来保存构建后的sql和参数信息,定义如下:
- /**
- * 执行sql的上下文内容
- *
- * User: liyd
- * Date: 2/13/14
- * Time: 10:40 AM
- */
- public class SqlContext {
- /** 执行的sql */
- private StringBuilder sql;
- /** 主键名称 */
- private String primaryKey;
- /** 参数,对应sql中的?号 */
- private List<Object> params;
- public SqlContext(StringBuilder sql, String primaryKey, List<Object> params) {
- this.sql = sql;
- this.primaryKey = primaryKey;
- this.params = params;
- }
- //getter setter 略
- }