只使用一个查询和列别名的嵌套1:N实体的SpringJdbc RowMapper

Question

我有许多查询，有许多选择字段和一些嵌套实体。这是嵌套实体结构的简化版本：

public class OuterEntity{
    private String name1;
    private String name2;
    private MiddleEntity middle;
    //... get/set..
 }

public class MiddleEntity{
    private String surname1;
    private String surname2;
    private InnerEntity inner;
    //... get/set..
}

public class InnerEntity{
    private String nickname1;
    private String nickname2;
    //... get/set..
}

所有实体都有1:N关系，所以我可以编写单个长查询来获取所有数据。我希望避免多次查询来分别获得每个单个实体。

select 
    o.name1
    o.name2
    m.surname1
    m.surname2
    i.nickname1
    i.nickname2
from outertable o
join middletable m on m.id=o.middle
join innertable i on i.id=m.inner

select 
    o.name1 as name1
    o.name2 as name1
    m.surname1 as middle_surname1
    m.surname2 as middle_surname2
    i.nickname1 as middle_inner_nickname1
    i.nickname2 as middle_inner_nickname2
from outertable o
join middletable m on m.id=o.middle
join innertable i on i.id=m.inner

Answer 1

我现在什么也没找到，我试图基于BeanPropertyRowMapper Soruce编写一个自定义BeanWrapper。

import java.beans.PropertyDescriptor;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.NotWritablePropertyException;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.JdbcUtils;
/**
 * @author tobia.scapin
 * 
 * BeanRowMapper for nesting beans of 1:n entity this uses query aliases to build entity nesting.
 * Field names should be exactly the same of bean property, respect cases and do not use underscore for field names
 * "id" columnname/alias is used to check if a nested entity should be null.
 * 
 * example:
 *      select
 *          a.p1 as property1
 *          b.id as entityname_id    //<-- if this is values is null, the entity will be null
 *          b.p1 as entityname_property1
 *          b.p2 as entityname_property2
 *          c.id as entityname_subentity_id     //<-- if this is values is null, the subentity will be null
 *          c.p1 as entityname_subentity_property1
 *      from a,b,c
 *
 * @param <T>
 */
public class NestedBeanAliasRowMapper<T> implements RowMapper<T> {

    private static final String NESTING_SEPARATOR = "_";
    private static final String NULLIZER_FIELD = "id";

    @SuppressWarnings("rawtypes")
    private final static List<Class> TYPES;
    static{
        TYPES=Arrays.asList(new Class[]{ int.class, boolean.class, byte.class, short.class, long.class, double.class, float.class, Boolean.class, Integer.class, Byte.class, Short.class, Long.class, BigDecimal.class, Double.class, Float.class, String.class, Date.class});
    }

    private Class<T> mappedClass;
    private Map<String, PropertyDescriptor> mappedFields;
    private Map<String, PropertyDescriptor> mappedBeans;
    @SuppressWarnings("rawtypes")
    private Map<Class,NestedBeanAliasRowMapper> mappersCache=new HashMap<Class,NestedBeanAliasRowMapper>();
    private Map<String,BeanProp> beanproperties=null;

    public NestedBeanAliasRowMapper(Class<T> mappedClass) {
        initialize(mappedClass);
    }

    /**
     * Initialize the mapping metadata for the given class.
     * @param mappedClass the mapped class
     */
    protected void initialize(Class<T> mappedClass) {
        this.mappedClass = mappedClass;
        mappersCache.put(mappedClass, this);
        this.mappedFields = new HashMap<String, PropertyDescriptor>();
        this.mappedBeans = new HashMap<String, PropertyDescriptor>();
        PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(mappedClass);
        for (PropertyDescriptor pd : pds) {
            if (pd.getWriteMethod() != null) {
                if(TYPES.contains(pd.getPropertyType()))                    
                    this.mappedFields.put(pd.getName(), pd);
                else
                    this.mappedBeans.put(pd.getName(), pd);
            }
        }
    }

    @Override
    public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        List<Integer> cols=new ArrayList<Integer>();
        for (int index = 1; index <= columnCount; index++)
            cols.add(index);
        return mapRow(rs, rowNumber, cols, "", true);
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public T mapRow(ResultSet rs, int rowNumber, List<Integer> cols, String aliasPrefix, boolean root) throws SQLException {
        T mappedObject = BeanUtils.instantiate(this.mappedClass);
        BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
        ResultSetMetaData rsmd = rs.getMetaData();
        if(rowNumber==0) beanproperties=new HashMap<String,BeanProp>();
        for (int index : cols) {
            String column = JdbcUtils.lookupColumnName(rsmd, index);
            if(aliasPrefix!=null && column.length()>aliasPrefix.length() && column.substring(0, aliasPrefix.length()).equals(aliasPrefix))
                column=column.substring(aliasPrefix.length()); //remove the prefix from column-name
            PropertyDescriptor pd = this.mappedFields.get(column);
            if (pd != null) {
                try {
                    Object value = getColumnValue(rs, index, pd);
                    if(!root && NULLIZER_FIELD.equals(column) && value==null)
                        return null;
                    bw.setPropertyValue(pd.getName(), value);
                }
                catch (NotWritablePropertyException ex) {
                    throw new DataRetrievalFailureException("Unable to map column '" + column + "' to property '" + pd.getName() + "'", ex);
                }
            }else if(rowNumber==0 && column.contains(NESTING_SEPARATOR)){
                String[] arr=column.split(NESTING_SEPARATOR);
                column=arr[0];
                PropertyDescriptor bpd = this.mappedBeans.get(column);
                if(bpd!=null){
                    BeanProp beanprop=beanproperties.get(column);
                    if(beanprop==null){
                        beanprop=new BeanProp();
                        beanprop.setClazz(bpd.getPropertyType());
                        beanproperties.put(column, beanprop);
                    }
                    beanprop.addIndex(index);
                }
            }
        }
        if(!beanproperties.isEmpty()) for (String beanname : beanproperties.keySet()) {
            BeanProp beanprop=beanproperties.get(beanname);     
            NestedBeanAliasRowMapper mapper=mappersCache.get(beanprop.getClazz());
            if(mapper==null){
                mapper=new NestedBeanAliasRowMapper<>(beanprop.getClazz());
                mappersCache.put(beanprop.getClazz(), mapper);
            }
            Object value = mapper.mapRow(rs, rowNumber, beanprop.getIndexes(), aliasPrefix+beanname+NESTING_SEPARATOR, false);
            bw.setPropertyValue(beanname, value);           
        }
        return mappedObject;
    }

    protected Object getColumnValue(ResultSet rs, int index, PropertyDescriptor pd) throws SQLException {
        return JdbcUtils.getResultSetValue(rs, index, pd.getPropertyType());
    }

    public static <T> BeanPropertyRowMapper<T> newInstance(Class<T> mappedClass) {
        return new BeanPropertyRowMapper<T>(mappedClass);
    }

    @SuppressWarnings("rawtypes")
    private class BeanProp{
        private Class clazz;
        private List<Integer> indexes=new ArrayList<Integer>(); 

        public Class getClazz() {
            return clazz;
        }
        public void setClazz(Class clazz) {
            this.clazz = clazz;
        }
        public List<Integer> getIndexes() {
            return indexes;
        }
        public void addIndex(Integer index) {
            this.indexes.add(index);
        }


    }

}