Spring JdbcTemplate实现通用的泛型dao三：构建动态sql

Spring JdbcTemplate实现通用的泛型dao一：主功能实现

Spring JdbcTemplate实现通用的泛型dao二：实现自己的名称转换NameHandler 

Spring JdbcTemplate实现通用的泛型dao三：构建动态sql

Spring JdbcTemplate实现通用的泛型dao四：通用自定义转换到JavaBean的RowMapper实现

构建动态sql，其实说白了就是拼装sql语句，在这里我把传入的实体参数，属性有值的拼装进sql，为null的则忽略，要实现这个不用说，肯定要利用Java的反射功能，来看一个具有代表性的insert语句的构建：

/**
 * 构建insert语句
 * 
 * @param entity 实体映射对象
 * @param nameHandler 名称转换处理器
 * @return
 */publicstaticSqlContext buildInsertSql(Object entity,NameHandler nameHandler){Class<?> clazz = entity.getClass();String tableName = nameHandler.getTableName(clazz.getSimpleName());String primaryName = nameHandler.getPrimaryName(clazz.getSimpleName());StringBuilder sql =newStringBuilder("insert into ");List<Object>params=newArrayList<Object>();
    sql.append(tableName);//获取属性信息BeanInfo beanInfo =ClassUtils.getSelfBeanInfo(clazz);PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
    sql.append("(");StringBuilder args =newStringBuilder();
    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);returnnewSqlContext(sql, primaryName,params);}

 

众所周知，Java的反射是性能较低的，也有性能较好的第三方实现如cglib，这里并没有使用。在我的实测中两者差距不大。

但是注意这里并没有使用属性的操作方式，也就是没有使用jdk反射获取属性的getDeclaredFields()方法，而是使用了BeanInfo和PropertyDescriptor，因为后者的运行效率要远远高于前者。

在我的实测中，构建一个拥有12个属性的JavaBean的动态sql，十万次所耗时间为900毫秒左右，完全可以接受。当然，这里对JavaBean的信息进行了缓存，如果不缓存时间将多耗上几个数量级。

下面顺便贴上完整的代码：

/**
 * sql辅助为类
 * 
 * User: liyd
 * Date: 2/13/14
 * Time: 10:03 AM
 */publicclassSqlUtils{/** 日志对象 */privatestaticfinalLogger LOG =LoggerFactory.getLogger(SqlUtils.class);/**
     * 构建insert语句
     *
     * @param entity 实体映射对象
     * @param nameHandler 名称转换处理器
     * @return
     */publicstaticSqlContext buildInsertSql(Object entity,NameHandler nameHandler){Class<?> clazz = entity.getClass();String tableName = nameHandler.getTableName(clazz.getSimpleName());String primaryName = nameHandler.getPrimaryName(clazz.getSimpleName());StringBuilder sql =newStringBuilder("insert into ");List<Object>params=newArrayList<Object>();
        sql.append(tableName);//获取属性信息BeanInfo beanInfo =ClassUtils.getSelfBeanInfo(clazz);PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
        sql.append("(");StringBuilder args =newStringBuilder();
        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);returnnewSqlContext(sql, primaryName,params);}/**
     * 构建更新sql
     * 
     * @param entity
     * @param nameHandler
     * @return
     */publicstaticSqlContext buildUpdateSql(Object entity,NameHandler nameHandler){Class<?> clazz = entity.getClass();StringBuilder sql =newStringBuilder();List<Object>params=newArrayList<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);returnnewSqlContext(sql, primaryName,params);}/**
     * 构建查询条件
     * 
     * @param entity
     * @param nameHandler
     */publicstaticSqlContext buildQueryCondition(Object entity,NameHandler nameHandler){//获取属性信息BeanInfo beanInfo =ClassUtils.getSelfBeanInfo(entity.getClass());//        PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entityClass);PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();StringBuilder condition =newStringBuilder();List<Object>params=newArrayList<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++;}returnnewSqlContext(condition,null,params);}/**
     * 获取属性值
     *
     * @param readMethod
     * @param entity
     * @return
     */privatestaticObject getReadMethodValue(Method readMethod,Object entity){if(readMethod ==null){returnnull;}try{if(!Modifier.isPublic(readMethod.getDeclaringClass().getModifiers())){
                readMethod.setAccessible(true);}return readMethod.invoke(entity);}catch(Exception e){
            LOG.error("获取属性值失败", e);thrownewMincoderException(e);}}}

 

获取BeanInfo时写了一个ClassUtils来实现，里面对Bean信息进行了缓存。因为项目使用spring，本来想使用spring提供的BeanUtils.getPropertyDescriptor()方法的，里面同样拥有缓存，但是该方法会把实体类父类的属性信息也获取出来，而PropertyDescriptor中又没法判断，这将直接导致拼装sql时字段的错误，因为你不知道哪些字段是操作当前表所需要的。没办法，查看jdk本身的Introspector类，发现里面有如下方法定义：

publicstaticBeanInfo getBeanInfo(Class<?> beanClass,Class<?> stopClass)throwsIntrospectionException

 

即可以指定在哪个类停止获取属性，这正是我们需要的，可惜spring没有进行封装，只能自己实现了，参考了spring的实现，使用WeakHashMap来防止内存的溢出，及时清空Introspector本身的缓存：

/**
 * 类辅助
 *
 * User: liyd
 * Date: 2/12/14
 * Time: 10:08 PM
 */publicclassClassUtils{/** 日志对象 */privatestaticfinalLogger               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.
     */privatestaticfinalMap<Class,BeanInfo> classCache =Collections.synchronizedMap(newWeakHashMap<Class,BeanInfo>());/**
     * 获取类本身的BeanInfo，不包含父类属性
     * 
     * @param clazz
     * @return
     */publicstaticBeanInfo 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);thrownewMincoderException(e);}}/**
     * 初始化实例
     * 
     * @param clazz
     * @return
     */publicstaticObject newInstance(Class<?> clazz){try{return clazz.newInstance();}catch(Exception e){
            LOG.error("根据class创建实例失败", e);thrownewMincoderException(e);}}}

另外创建了对象SqlContext来保存构建后的sql和参数信息，定义如下：

 

/**
 * 执行sql的上下文内容
 * 
 * User: liyd
 * Date: 2/13/14
 * Time: 10:40 AM
 */publicclassSqlContext{/** 执行的sql */privateStringBuilder sql;/** 主键名称 */privateString        primaryKey;/** 参数，对应sql中的?号 */privateList<Object>params;publicSqlContext(StringBuilder sql,String primaryKey,List<Object>params){this.sql = sql;this.primaryKey = primaryKey;this.params=params;}//getter setter 略}