jdbcTemplate查询Oracle数据转换为实体对象

Tags: /超级猛料/Database.数据库相关/Oracle/ Date Created:

JdbcTemplate可以进行灵活的SQL查询，查询的结果可能包含多个join的表，我们可以用JPA来查询并自动组装数据，但是如果事比较复杂的查询，那么JPA无法支持，此时我们可以用JdbcTemplate查询复杂的SQL语句，并返回多个表的数据，但是当我们用实体类转换的时候，会发现，必须要求字段名顺序和名称、类型完全一致才能正确的转换为目标对象。

但实际的情况是，我们经常定义了每个表的数据实体类，例如：

@Data
@Entity
@Table(name = "Table_A")
public class TableA {
  @Column(name="A_Field1")
  private String field1;

  @Column(name="A_Field2")
  private String field2;

  @Column(name="A_Field3")
  private String field3;
  // ...
}

@Data
@Entity
@Table(name = "Table_B")
public class TableA {
  @Column(name="B_Field1")
  private String field1;

  @Column(name="B_Field2")
  private String field2;

  @Column(name="B_Field3")
  private String field3;
  // ...
}

很显然，对于以下查询，我们可以定义一个大而全的类：

@Data
@Entity
public class FullTable {
  @Column(name="A_Field1")
  private String field1;

  @Column(name="A_Field2")
  private String field2;

  @Column(name="A_Field3")
  private String field3;
  //...

  @Column(name="B_Field1")
  private String field1;

  @Column(name="B_Field2")
  private String field2;

  @Column(name="B_Field3")
  private String field3;
  // ...
}

String sql = "select * from table_a a, table_b b where a.field1 = b.field1 and b_field2 = :field2"
MapSqlParameterSource params = new MapSqlParamterSource().addValue("field2", "Hello world");
return jdbcTemplate.queryForList(sql, params, FullTable.class);

但是上述的方法，要求FullTable中的字段顺序和sql查询的结果中的字段顺序即类型完全一致，这显然不方便。

我们可以用下面的方法来实现通用的字段转换映射：

import jakarta.persistence.Column;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.jdbc.core.RowMapper;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

/**
 * AnnotatedRowMapper
 * RowMapper support @Column annotation <br/>
 * 同名字段或屬性，優先取第一個<br/>
 * 自動轉換查詢結果為實體對象，支持 @Column 注解，如果 @Column注解不存在，則按以下規則優先級匹配字段名稱 <br/>
 * <ul>
 *     <li>Java字段名稱 == 數據庫字段名相同</li>
 *     <li>Java字段名稱 == 與數據庫字段名去掉第一個下劃綫前的前綴</li>
 *     <li>Java字段名稱 == 與數據庫字段名去掉第一個下劃綫前的前綴，且轉爲駝峰命名法</li>
 * </ul>
 *
 * @param <T> Target Object Class Type
 */
public class AnnotatedRowMapper<T> implements RowMapper<T> {
    private Class<T> mappedClass;
    private Map<String, String> mapField;

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

    /**
     * Init field name --> database column name mapping
     */
    private void initFieldMapping(ResultSetMetaData metaData, BeanWrapperImpl wrapper) throws SQLException {
        mapField = new HashMap<>();
        Set<String> columns = new HashSet<>();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            columns.add(metaData.getColumnName(i));
        }

        // Search Order:
        // field name == column name, ignore case
        // field name == XXX_column name without XXX_ prefix
        // field name == XXX_column name without XXX_ prefix and camel case style
        Field[] fields = mappedClass.getDeclaredFields();
        for (Field field : fields) {
            Column columnAnnotation = field.getAnnotation(Column.class);
            String name = field.getName();
            if (columnAnnotation != null) {
                mapField.put(name, columnAnnotation.name().toUpperCase());
            } else {
                columns.stream().filter(str -> name.equalsIgnoreCase(str)
                                || name.equalsIgnoreCase(toCamelCase(str))
                                || name.equalsIgnoreCase(toCamelCase(str.substring(str.indexOf("_")))))
                        .findFirst().ifPresent(value -> mapField.putIfAbsent(name, value));
            }
        }

        PropertyDescriptor[] properties = wrapper.getPropertyDescriptors();
        for (PropertyDescriptor property : properties) {
            String name = property.getName();
            if (mapField.containsKey(name)) continue;

            columns.stream().filter(str -> name.equalsIgnoreCase(str)
                            || name.equalsIgnoreCase(toCamelCase(str))
                            || name.equalsIgnoreCase(toCamelCase(str.substring(str.indexOf("_")))))
                    .findFirst().ifPresent(value -> mapField.putIfAbsent(name, value));
        }
    }

    private static String toCamelCase(String str) {
        return StringUtils.uncapitalize(StringUtils.remove(StringUtils.capitalize(str.toLowerCase()), "_").replaceAll("_([a-z])", "$1"));
    }

    @SneakyThrows
    @Override
    public T mapRow(ResultSet rs, int rowNum) {
        T instance = mappedClass.getDeclaredConstructor().newInstance();
        BeanWrapperImpl wrapper = new BeanWrapperImpl(instance);
        if (mapField == null) {
            initFieldMapping(rs.getMetaData(), wrapper);
        }

        for (String key : mapField.keySet()) {
            String column = mapField.get(key);
            if (column != null) {
                Object value = convertValue(rs.getObject(column), wrapper.getPropertyType(key));
                if (value != null) {
                    wrapper.setPropertyValue(key, value);
                }
            }
        }
        return instance;
    }
    private Object convertValue(Object value, Class<?> targetType) throws SQLException {
        if (value == null) return null;

        if (targetType.isInstance(value)) {
            return value;
        }

        // Primitive type conversion
        if (targetType == Integer.class || targetType == int.class) {
            return ((Number) value).intValue();
        } else if (targetType == Long.class || targetType == long.class) {
            return ((Number) value).longValue();
        } else if (targetType == String.class) {
            return value.toString();
        } else if (targetType == Date.class) {
            return ((oracle.sql.TIMESTAMP) value).dateValue();
        }

        return value;
    }
}

然后我们可以用 Delegate 来拉平所有子对象的属性，形成一个完整的数据结构：

@Data
public class FullTable {
    @Delegate
    @JsonIgnore
    TableA tableA = new TableA();
    
    @Delegate
    @JsonIgnore
    TableB tableB = new TableB();
}
jdbcTemplate.query(sql, params, new AnnotatedRowMapper<>(FullTable.class));

上述方法，有个缺点，就是不能有同名字段，当然你可以根据实际需要修改上述代码符合项目的中的数据库的要求。

你也可以用下面的工具类来转换实体：

import jakarta.persistence.Column;
import lombok.SneakyThrows;
import org.springframework.beans.BeanWrapperImpl;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.*;

public class MapToEntity {
    /**
     * Return field -> column name mapping of @Column annotation
     *
     * @param target Table Entity class definition
     * @return Map of (filed name, column name)
     */
    public static <T> Map<String, String> getFieldMap(Class<T> target) {
        Map<String, String> result = new HashMap<>();
        Field[] fields = target.getDeclaredFields();
        for (Field field : fields) {
            Column columnAnnotation = field.getAnnotation(Column.class);
            String name = field.getName();
            if (columnAnnotation != null) {
                result.put(name, columnAnnotation.name().toUpperCase());
            }
        }
        return result;
    }

    /**
     * Convert JdbcTemplate query result to entity object
     * The target class should be an @Entity @Table class with @Column annotation, for example:
     * <pre>{@code
     * @Data
     * @Entity
     * @Table(name = "TableA")
     * public class TableA {
     *     @Column(name = "a_name")
     *     private String name;
     *     // .....
     * }
     * }</pre>
     */
    @SneakyThrows
    public static <T> T toObject(Map<String, Object> item, Class<T> target) {
        Map<String, String> mapField = getFieldMap(target);

        T instance = target.getDeclaredConstructor().newInstance();
        BeanWrapperImpl wrapper = new BeanWrapperImpl(instance);
        for (String key : mapField.keySet()) {
            String column = mapField.get(key);
            if (column != null) {
                Object value = convertValue(item.get(column), wrapper.getPropertyType(key));
                if (value != null) {
                    wrapper.setPropertyValue(key, value);
                }
            }
        }
        return instance;
    }

    /**
     * Convert JdbcTemplate query result list to entity object list<br/>
     * See {@link #toObject(Map, Class)}
     */
    @SneakyThrows
    public static <T> List<T> toObjects(List<Map<String, Object>> list, Class<T> target) {
        List<T> result = new ArrayList<>(list.size());
        Map<String, String> map = getFieldMap(target);
        list.forEach(item -> result.add(toObject(item, target, map)));
        return result;
    }

    /**
     * Convert JdbcTemplate query result to entity object<br/>
     * The target class should be an @Entity @Table class with @Column annotation
     * See {@link #toObjects(List, Class)}
     */
    @SneakyThrows
    public static <T> T toObject(Map<String, Object> item, Class<T> mappedClass, Map<String, String> mapField) {
        T instance = mappedClass.getDeclaredConstructor().newInstance();
        BeanWrapperImpl wrapper = new BeanWrapperImpl(instance);
        for (String key : mapField.keySet()) {
            String column = mapField.get(key);
            if (column != null) {
                Object value = convertValue(item.get(column), wrapper.getPropertyType(key));
                if (value != null) {
                    wrapper.setPropertyValue(key, value);
                }
            }
        }
        return instance;
    }

    /**
     * Convert Oracle Object To Java Object
     */
    private static Object convertValue(Object value, Class<?> targetType) throws SQLException {
        if (value == null) return null;

        if (targetType.isInstance(value)) {
            return value;
        }

        // Primitive type conversion
        if (targetType == Integer.class || targetType == int.class) {
            return ((Number) value).intValue();
        } else if (targetType == Long.class || targetType == long.class) {
            return ((Number) value).longValue();
        } else if (targetType == String.class) {
            return value.toString();
        } else if (targetType == Date.class) {
            return ((oracle.sql.TIMESTAMP) value).dateValue();
        }

        return value;
    }
}

使用方法：

Map<String, String> mapA = MapToEntity.getFieldMap(TableA.class);
Map<String, String> mapB = MapToEntity.getFieldMap(TableB.class);
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, params);
List<FullTable> result = new ArrayList<>(list.size());
list.forEach(map -> {
  FullTable full = new FullTable();
  full.setTableA(MapToEntity.toObject(map, TableA.class, mapA));
  full.setTableB(MapToEntity.toObject(map, TableB.class, mapB));
});
return result;