利用 spring jdbcTemplate + Java Persistence API @Table @Column 实现对象和数据库的映射

具体实现思路

第一步 建立数据库和实体的映射

package laughing.my.entity;

import lombok.Data;

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

/**
 * @author laughing
 * @create 2019-08-01 19:04:36
 * @desc 菜单功能表映射（1对多）
 **/
@Data
@Table(name = "sys_menu_func")
public class MenuFuncRelEntity extends BaseEntity {
    /**
     * 菜单id
     */
    @Column(name = "menu_id")
    private String menuId;

    /**
     * 功能Code
     */
    @Column(name = "func_code")
    private String funcCode;

    /**
     * 权限
     */
    @Column(name = "func_action")
    private String funcAction;
}

第二步：使用反射机制把字段和具体对象的属性映射即识别@Column

package laughing.my.dao.util;

import laughing.my.entity.MenuEntity;
import lombok.Data;
import org.springframework.beans.BeanUtils;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

/**
 * @author laughing
 * @create 2019-08-25 16:02:36
 * @desc
 **/
@Data
public class EntityTableRowMapper {
    /**
     * id的字段名称
     */
//    private String idName = null;

    /**
     * table对应的class
     */
    private Class tableClass = null;
    /**
     * 对应的数据库名称
     */
    private String tableName = null;

    /**
     * 表中所有的字段
     */
    private Set<String> columnNames = null;

    /**
     * 表中所有的字段对应的属性名称
     */
    private Set<String> fieldNames = null;

    /**
     * 属性名称和数据库字段名的映射
     * K: 属性名
     * V：表字段名称
     */
    private Map<String, String> fieldNameColumnMapper = null;

    /**
     * 数据库字段名和属性名称的映射
     * K：表字段名称
     * V: 属性名
     */
    private Map<String, String> columnFieldNameMapper = null;
    /**
     * 数据库字段名和class属性的映射
     * K：表字段名称
     * V：class属性
     */
    private Map<String, Field> columnFieldMapper = null;

    private Map<String, PropertyDescriptor> mappedFields = null;

    public static EntityTableRowMapper toEntityTableRowMapper(Class clazz) {
        Class clz = clazz;
        String tableName = EntityUtils.getTableName(clz);
        if (tableName == null) {
            return null;
        }
        EntityTableRowMapper entityTableRowMapper = new EntityTableRowMapper();
        Map<String, Field> columnFieldMap = EntityUtils.columnFieldMap(clz);
        Map<String, Field> superColumnFieldMap = EntityUtils.columnFieldMap(clz.getSuperclass());
        columnFieldMap.putAll(superColumnFieldMap);
        int size = columnFieldMap.size();
        Map<String, String> fieldNameColumnMapper = new HashMap<>(size);
        Map<String, String> columnFieldNameMapper = new HashMap<>(size);
        Set<String> columnNames = new HashSet<>(size);
        Set<String> fieldNames = new HashSet<>(size);
        entityTableRowMapper.setTableClass(clz);
        entityTableRowMapper.setTableName(EntityUtils.getTableName(clz));
        initPropertyDescriptor(clazz, entityTableRowMapper);
//        mapper.setIdName(EntityUtils.idColumnName(clz));
        entityTableRowMapper.setColumnFieldMapper(columnFieldMap);
        for (Map.Entry<String, Field> entry : columnFieldMap.entrySet()) {
            String columnName = entry.getKey();
            Field field = entry.getValue();
            String fieldName = field.getName();
            fieldNameColumnMapper.put(fieldName, columnName);
            columnFieldNameMapper.put(columnName,fieldName);
            fieldNames.add(fieldName);
            columnNames.add(columnName);
        }
        entityTableRowMapper.setColumnNames(columnNames);
        entityTableRowMapper.setFieldNameColumnMapper(fieldNameColumnMapper);
        entityTableRowMapper.setFieldNames(fieldNames);
        entityTableRowMapper.setColumnFieldNameMapper(columnFieldNameMapper);
        return entityTableRowMapper;
    }

    /**
     * PropertyDescriptor
     *
     * @param mappedClass
     * @param entityTableRowMapper
     */
    protected static void initPropertyDescriptor(Class mappedClass, EntityTableRowMapper entityTableRowMapper) {
        Map<String, PropertyDescriptor> mappedFields = new HashMap();
        PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(mappedClass);
        for (PropertyDescriptor pd : pds) {
            if (pd.getWriteMethod() != null) {
                mappedFields.put(pd.getName(), pd);
                String underscoredName = pd.getName();
                if (pd.getName().equals(underscoredName)) {
                    mappedFields.put(underscoredName, pd);
                }
            }
        }
        entityTableRowMapper.setMappedFields(mappedFields);
    }



    public static void main(String[] args) {
//        EntityTableRowMapper entityTableRowMapper = new EntityTableRowMapper();
        MenuEntity entity = new MenuEntity();
        EntityTableRowMapper entityTableRowMapper = EntityTableRowMapper.toEntityTableRowMapper(entity.getClass());
        System.out.println("a");
    }
}

第三步 自定义RowMapper

package laughing.my.dao.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.*;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;

import java.beans.PropertyDescriptor;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * 对象映射
 *
 * @param <T>
 */
@Slf4j
public class MyBeanPropertyRowMapper<T> implements RowMapper<T> {

    /**
     * The class we are mapping to
     */
    private Class<T> mappedClass;


    /**
     * column mapper  entity field
     */
    private EntityTableRowMapper entityTableRowMapper;


    public MyBeanPropertyRowMapper(Class<T> mappedClass) {
        this.mappedClass = mappedClass;
        initialize(mappedClass);
    }


    /**
     * Initialize the mapping metadata for the given class.
     *
     * @param mappedClass the mapped class.
     */
    protected void initialize(Class<T> mappedClass) {
        entityTableRowMapper = EntityMapperFactory.getEntityTableRowMapper(mappedClass);
    }


    /**
     * Extract the values for all columns in the current row.
     * <p>Utilizes public setters and result set metadata.
     *
     * @see ResultSetMetaData
     */
    @Override
    public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
        // 创建实例
        T mappedObject = BeanUtils.instantiate(this.mappedClass);
        // Spring委托BeanWrapper完成Bean属性的填充工作
        BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
        initBeanWrapper(bw);
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        // 字段和实体的映射
        for (int index = 1; index <= columnCount; index++) {
            // 字段名称
            String column = JdbcUtils.lookupColumnName(rsmd, index);
            // 映射entity 属性
            String entityField = this.entityTableRowMapper.getColumnFieldNameMapper().get(column);
            // 获取PropertyDescriptor
            // PropertyDescriptor类：(属性描述器)
            PropertyDescriptor pd = this.entityTableRowMapper.getMappedFields().get(entityField);
            if (pd != null) {
                try {
                    Object value = getColumnValue(rs, index, pd);
                    log.debug("Mapping column '{}' to property '{}' of type {}", column, pd.getName(), pd.getPropertyType());
                    try {
                        // 设置值
                        bw.setPropertyValue(pd.getName(), value);
                    } catch (TypeMismatchException e) {
                        if (value == null) {
                            log.debug("Intercepted TypeMismatchException for row " + rowNumber +
                                    " and column '" + column + "' with value " + value +
                                    " when setting property '" + pd.getName() + "' of type " + pd.getPropertyType() +
                                    " on object: " + mappedObject);
                        } else {
                            throw e;
                        }
                    }
                } catch (NotWritablePropertyException ex) {
                    throw new DataRetrievalFailureException("Unable to map column " + column + " to property " + pd.getName(), ex);
                }
            }
        }
        return mappedObject;
    }

    /**
     * Initialize the given BeanWrapper to be used for row mapping.
     * To be called for each row.
     * <p>The default implementation is empty. Can be overridden in subclasses.
     *
     * @param bw the BeanWrapper to initialize
     */
    protected void initBeanWrapper(BeanWrapper bw) {
    }

    /**
     * Retrieve a JDBC object value for the specified column.
     * <p>The default implementation calls
     * {@link JdbcUtils#getResultSetValue(ResultSet, int, Class)}.
     * Subclasses may override this to check specific value types upfront,
     * or to post-process values return from <code>getResultSetValue</code>.
     *
     * @param rs    is the ResultSet holding the data
     * @param index is the column index
     * @param pd    the bean property that each result object is expected to match
     *              (or <code>null</code> if none specified)
     * @return the Object value
     * @throws SQLException in case of extraction failure
     * @see JdbcUtils#getResultSetValue(ResultSet, int, Class)
     */
    protected Object getColumnValue(ResultSet rs, int index, PropertyDescriptor pd) throws SQLException {
        return JdbcUtils.getResultSetValue(rs, index, pd.getPropertyType());
    }


}

 

import lombok.extern.slf4j.Slf4j;

import javax.persistence.Column;
import javax.persistence.Table;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;

/**
 * @author laughing
 * @create 2019-08-25 16:17:21
 * @desc
 **/
@Slf4j
public class EntityUtils {


    /**
     * 实体类映射Map
     *
     * @param clazz
     * @return
     */
    public static Map columnFieldMap(Class clazz) {
        Map<String, Field> map = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
        }
        for (int i = 0; i < fields.length; i++) {
            try {
                Field field = clazz.getDeclaredField(fields[i].getName());
                Column column = field.getAnnotation(Column.class);
                if (column != null) {
                    map.put(column.name(), field);
                }
            } catch (NoSuchFieldException e) {
                log.error("columnFieldMap error : {}", e);
            }
        }
        return map;
    }

    /**
     * 获取 table 名 （@Table 注解）
     *
     * @param clazz entity 对象
     * @return
     */
    public static String getTableName(Class<?> clazz) {
        Table annotation = clazz.getAnnotation(Table.class);
        if (annotation == null) {
            return null;
        }
        String name = annotation.name();
        return name;
    }

    /**
     * @param obj
     * @param field
     * @return
     */
    public static Object getValue(Object obj, Field field) {
        Object value = null;
        try {
            // 通过属性获取对象的属性
            //Field field = obj.getClass().getDeclaredField(propName);
            // 对象的属性的访问权限设置为可访问
            field.setAccessible(true);
            // 获取属性的对应的值
            value = field.get(obj);
        } catch (Exception e) {
            log.error("getFieldVal error : {}", e);
        }

        return value;
    }

}

最后使用

   public List<MenuEntity> findMenuList() {
        String sql = "select * from sys_menu  order by parent_menu_id , order_no";
        return jdbcTemplate.query(sql.toString(), new MyBeanPropertyRowMapper<>(
                MenuEntity.class));
    }