Hibernate自定义查询封装实现数据映射自由

Hibernate的自定义查询代码如下：

	@Override
    public List<RsAttr> findbynv(String attr_name, String attr_value) {
            String sql = "select * from Atable a where a.aId = ? and a.aField < ?";
            SQLQuery sqlQuery = getSession().createSQLQuery(sql);// SQLQuery是hibernate用于支持原生sql的接口类
            sqlQuery.setParameter(0, attr_name);
            sqlQuery.setParameter(1, attr_value);
            sqlQuery.addEntity(RsAttr.class);
            List<RsAttr> RsAttrList1 = (List<RsAttr>)sqlQuery.list();
            return RsAttrList1;
    }

Hibernate虽然有自定义查询但只适用于将数据映射到对应的@Entity标注类中，而无法映射那些自定义的字段集，所以才有了如下代码来弥补进行这方面的功能：

创建数据映射核心工具类

package com.fnl.util;

import org.apache.commons.beanutils.BeanUtilsBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.util.Iterator;
import java.util.Map;

/**
 * @Author Wuxb
 * @Date 2022/1/22 15:46
 * @Description
 */
public class BeanUtilsPlus {
    final static Logger log= LoggerFactory.getLogger(BeanUtilsPlus.class);
	//将map集合转为对应的bean
    public static <A>A convert2Object(Class<A> clazz,Map<String,Object> map) throws IntrospectionException, IllegalAccessException, InstantiationException {
        BeanInfo bi = Introspector.getBeanInfo(clazz);

        A obj = clazz.newInstance();

        PropertyDescriptor[] pds = bi.getPropertyDescriptors();

        String pName;
        for(PropertyDescriptor pd:pds){
            //可设置不区分大小写和下划线
            Column col = pd.getReadMethod().getAnnotation(Column.class);
            if(col!=null){
                pName=col.name();
            }else {
                pName = pd.getName().toUpperCase();//都转为大写，去除字段的大小写区分
            }
            if(map.containsKey(pName)){
                try {
                    //执行bean的写方法
                    pd.getWriteMethod().invoke(obj, map.get(pName));
                } catch (Exception ex) {
                    log.error(ex.getMessage());
                }
            }
        }

        return obj;
    }
}

在dao层调用Hibernate自定义查询来进行封装

/**
 * @Author Wuxb
 * @Date 2022/1/22 15:46
 * @Description
 */
public class GenericDaoImp extends HibernateDaoSupport {
    
	//引用Hibernate自定义查询代码
	public List<Map<String, Object>> findList(String sql, List<Object> param) {
		Query q = this.getHibernateTemplate().getSessionFactory().getCurrentSession()
				.createSQLQuery(sql);
		if (param != null && param.size() > 0) {
			for (int i = 0; i < param.size(); i++) {
				q.setParameter(i, param.get(i));
			}
		}
		List<Map<String, Object>> list = q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		return list;
	}
    
	//可以自适应任何bean对象只要字段名相同即可自动填充，不区分大小写
	@Override
    public <A> List<A> getListToClass(Class<A> annotationClass, String sql, List<Object> param) throws IllegalAccessException, IntrospectionException, InstantiationException {
       ArrayList<A> list = new ArrayList<>();
       List<Map<String, Object>> page1 = this.findList(sql, param);
       for (Map<String, Object> map:page1) {
          list.add(BeanUtilsPlus.convert2Object(annotationClass,map));
       }
       return list;
    }

}

创建表A和表B关联查询语句对应的映射数据bean

public class ABTableVO {
    //a表对应的字段
    private Long aId;
    private String aField;
    //b表对应的字段
    private Long bId;
    private String bField;
    
    public Long getaId() {
        return aId;
    }

    public void setaId(Long aId) {
        this.aId = aId;
    }

    public String getaField() {
        return aField;
    }

    public void setaField(String aField) {
        this.aField = aField;
    }

    public Long getbId() {
        return bId;
    }

    public void setbId(Long bId) {
        this.bId = bId;
    }

    public String getbField() {
        return bField;
    }

    public void setbField(String bField) {
        this.bField = bField;
    }
}

下面是封装后自定义查询的使用方式

@Repository("aTableDao")
public class ATableDaoImpl extends GenericDaoImp{
    
    @Override
    public Result<List<ABTableVO>> getDate(BTable query) throws IllegalAccessException, IntrospectionException, InstantiationException {
        StringBuffer sql = new StringBuffer();
        sql.append(" select * ");
        sql.append(" from table_a a ");
        sql.append(" left join table_b b on a.aId=b.bField where 1=1 ");
        List<Object> querys = new ArrayList<>();
        if(StringHelper.isNotEmpty(query.getFid())){
            sql.append(" and a.aId=? ");
            querys.add(query.getFid());
        }
        if(StringHelper.isNotEmpty(query.getbId())){
            sql.append(" and b.bId=? ");
            querys.add(query.getbId());
        }
        //不区分大小写
        List<ABTableVO> list = this.getPageToClass(ABTableVO.class, sql.toString(), querys);

        return Result.success(list);
    }
}