上一篇文章為大家介紹瞭如何使用反射解析領域模型的屬性資訊並快取，本節將介紹如何自動封裝JDBC的結果集ResultSet到實體物件中，這裡正好使用到快取的領域模型。
我們先理一下思路：
- 怎樣正確的呼叫如rs.getString("name")、rs.getLong("size")得到想要的資料，這些資料如何正確的呼叫實體的set方法設值，這裡rs中獲取的值必須是實體中屬性的型別。
- PreparedStatement如何獲取實體屬性值設定到對應SQL引數中，如insert into tableName(id, name) values(?, ?)。我們知道PreparedStatement替換佔位符設值的方式為：ps.setInt(i, parameter)，ps.setString(i, parameter)等
- 如果解決了以上兩個問題，封裝ResultSet結果集到實體物件中可謂輕而易舉。
我們採用為每個Java屬性(對應資料庫表欄位型別)的提供一個對映器
全域性提供一個對映器的註冊服務，需要獲取對應的對映器的時候直接從註冊服務中獲取即可。
對映器介面定義如下：
package com.applet.sql.type;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Created by Jackie Liu on 2017/9/25.
*/
public interface TypeHandler<T> {
void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException;
T getResult(ResultSet rs, String columnName) throws SQLException;
T getResult(ResultSet rs, int columnIndex) throws SQLException;
T getResult(CallableStatement cs, int columnIndex) throws SQLException;
Class<T> getClazz();
}
複製程式碼使用模板方法為對映器提供公共的行為，變化的部分讓子類實現：
package com.applet.sql.type;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Created by Jackie Liu on 2017/9/25.
*/
public abstract class BaseTypeHandler<T> implements TypeHandler<T> {
@Override
public void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
if (parameter == null) {
if (jdbcType == null) {
throw new RuntimeException("JDBC requires that the JdbcType must be specified for all nullable parameters.");
}
try {
ps.setNull(i, jdbcType.TYPE_CODE);
} catch (SQLException e) {
throw new RuntimeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . " +
"Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. " +
"Cause: " + e, e);
}
} else {
try {
setNonNullParameter(ps, i, parameter, jdbcType);
} catch (Exception e) {
throw new RuntimeException("Error setting non null for parameter #" + i + " with JdbcType " + jdbcType + " . " +
"Try setting a different JdbcType for this parameter or a different configuration property. " +
"Cause: " + e, e);
}
}
}
@Override
public T getResult(ResultSet rs, String columnName) throws SQLException {
T result;
try {
result = getNullableResult(rs, columnName);
} catch (Exception e) {
throw new RuntimeException("Error attempting to get column '" + columnName + "' from result set. Cause: " + e, e);
}
if (rs.wasNull()) {
return null;
} else {
return result;
}
}
@Override
public T getResult(ResultSet rs, int columnIndex) throws SQLException {
T result;
try {
result = getNullableResult(rs, columnIndex);
} catch (Exception e) {
throw new RuntimeException("Error attempting to get column #" + columnIndex+ " from result set. Cause: " + e, e);
}
if (rs.wasNull()) {
return null;
} else {
return result;
}
}
@Override
public T getResult(CallableStatement cs, int columnIndex) throws SQLException {
T result;
try {
result = getNullableResult(cs, columnIndex);
} catch (Exception e) {
throw new RuntimeException("Error attempting to get column #" + columnIndex+ " from callable statement. Cause: " + e, e);
}
if (cs.wasNull()) {
return null;
} else {
return result;
}
}
public abstract void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException;
public abstract T getNullableResult(ResultSet rs, String columnName) throws SQLException;
public abstract T getNullableResult(ResultSet rs, int columnIndex) throws SQLException;
public abstract T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException;
}
複製程式碼每個子類實現方式如下(這裡提供一個，其他的各位親們可自己擴充套件或者給我留言)：
package com.applet.sql.type;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Created by Jackie Liu on 2017/9/25.
*/
public class IntegerTypeHandler extends BaseTypeHandler<Integer> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Integer parameter, JdbcType jdbcType)
throws SQLException {
ps.setInt(i, parameter);
}
@Override
public Integer getNullableResult(ResultSet rs, String columnName)
throws SQLException {
return rs.getInt(columnName);
}
@Override
public Integer getNullableResult(ResultSet rs, int columnIndex)
throws SQLException {
return rs.getInt(columnIndex);
}
@Override
public Integer getNullableResult(CallableStatement cs, int columnIndex)
throws SQLException {
return cs.getInt(columnIndex);
}
@Override
public Class<Integer> getClazz() {
return Integer.class;
}
}
複製程式碼全域性的註冊服務如下：
package com.applet.sql.type;
import com.alibaba.druid.support.logging.Resources;
import com.applet.sql.record.JSONB;
import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.Constructor;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
/**
* Created by Jackie Liu on 2017/9/25.
*/
@SuppressWarnings({"ALL", "AlibabaClassMustHaveAuthor"})
public class TypeHandlerRegistry {
// EnumMap，儲存JDBC內部提供的列舉JdbcType型別和對應的TypeHandler
private final Map<JdbcType, TypeHandler<?>> JDBC_TYPE_HANDLER_MAP = new EnumMap<JdbcType, TypeHandler<?>>(JdbcType.class);
// Type：javaType的Class型別(Type是Class的介面)，value是一個Map集合（比如String，可能對應資料庫的clob、char、varchar等，所以是一對多關係）
private final Map<Type, Map<JdbcType, TypeHandler<?>>> TYPE_HANDLER_MAP = new ConcurrentHashMap<Type, Map<JdbcType, TypeHandler<?>>>();
// 處理Object型別（執行時，會嘗試進行向下型別轉換找到合適的TypeHandler，如果依然失敗，最後選擇ObjectTypeHandler）
private final TypeHandler<Object> UNKNOWN_TYPE_HANDLER = new UnknownTypeHandler(this);
// 所有的TypeHandler. Key：TypeHandler的Class型別，value：TypeHandler例項（都是singleton）
private final Map<Class<?>, TypeHandler<?>> ALL_TYPE_HANDLERS_MAP = new HashMap<Class<?>, TypeHandler<?>>();
private static final Map<JdbcType, TypeHandler<?>> NULL_TYPE_HANDLER_MAP = new HashMap<JdbcType, TypeHandler<?>>();
private Class<? extends TypeHandler> defaultEnumTypeHandler = EnumTypeHandler.class;
public TypeHandlerRegistry() {
register(Boolean.class, new BooleanTypeHandler());
register(boolean.class, new BooleanTypeHandler());
register(JdbcType.BOOLEAN, new BooleanTypeHandler());
register(JdbcType.BIT, new BooleanTypeHandler());
register(Byte.class, new ByteTypeHandler());
register(byte.class, new ByteTypeHandler());
register(JdbcType.TINYINT, new ByteTypeHandler());
register(Short.class, new ShortTypeHandler());
register(short.class, new ShortTypeHandler());
register(JdbcType.SMALLINT, new ShortTypeHandler());
register(Integer.class, new IntegerTypeHandler());
register(int.class, new IntegerTypeHandler());
register(JdbcType.INTEGER, new IntegerTypeHandler());
register(Long.class, new LongTypeHandler());
register(long.class, new LongTypeHandler());
register(Float.class, new FloatTypeHandler());
register(float.class, new FloatTypeHandler());
register(JdbcType.FLOAT, new FloatTypeHandler());
register(Double.class, new DoubleTypeHandler());
register(double.class, new DoubleTypeHandler());
register(JdbcType.DOUBLE, new DoubleTypeHandler());
register(Reader.class, new ClobReaderTypeHandler());
register(String.class, new StringTypeHandler());
register(String.class, JdbcType.CHAR, new StringTypeHandler());
register(String.class, JdbcType.CLOB, new ClobTypeHandler());
register(String.class, JdbcType.VARCHAR, new StringTypeHandler());
register(String.class, JdbcType.LONGVARCHAR, new ClobTypeHandler());
//register(String.class, JdbcType.NVARCHAR, new NStringTypeHandler());
//register(String.class, JdbcType.NCHAR, new NStringTypeHandler());
//register(String.class, JdbcType.NCLOB, new NClobTypeHandler());
register(JdbcType.CHAR, new StringTypeHandler());
register(JdbcType.VARCHAR, new StringTypeHandler());
register(JdbcType.CLOB, new ClobTypeHandler());
register(JdbcType.LONGVARCHAR, new ClobTypeHandler());
//register(JdbcType.NVARCHAR, new NStringTypeHandler());
//register(JdbcType.NCHAR, new NStringTypeHandler());
//register(JdbcType.NCLOB, new NClobTypeHandler());
register(Object.class, JdbcType.ARRAY, new ArrayTypeHandler());
register(JdbcType.ARRAY, new ArrayTypeHandler());
register(BigInteger.class, new BigIntegerTypeHandler());
register(JdbcType.BIGINT, new LongTypeHandler());
register(BigDecimal.class, new BigDecimalTypeHandler());
register(JdbcType.REAL, new BigDecimalTypeHandler());
register(JdbcType.DECIMAL, new BigDecimalTypeHandler());
register(JdbcType.NUMERIC, new BigDecimalTypeHandler());
register(InputStream.class, new BlobInputStreamTypeHandler());
register(Byte[].class, new ByteObjectArrayTypeHandler());
register(Byte[].class, JdbcType.BLOB, new BlobByteObjectArrayTypeHandler());
register(Byte[].class, JdbcType.LONGVARBINARY, new BlobByteObjectArrayTypeHandler());
register(byte[].class, new ByteArrayTypeHandler());
register(byte[].class, JdbcType.BLOB, new BlobTypeHandler());
register(byte[].class, JdbcType.LONGVARBINARY, new BlobTypeHandler());
register(JdbcType.LONGVARBINARY, new BlobTypeHandler());
register(JdbcType.BLOB, new BlobTypeHandler());
register(Object.class, UNKNOWN_TYPE_HANDLER);
register(Object.class, JdbcType.OTHER, UNKNOWN_TYPE_HANDLER);
//register(JdbcType.OTHER, UNKNOWN_TYPE_HANDLER);
register(Date.class, new DateTypeHandler());
register(Date.class, JdbcType.DATE, new DateOnlyTypeHandler());
//register(Date.class, JdbcType.TIME, new TimeOnlyTypeHandler());
register(JdbcType.TIMESTAMP, new DateTypeHandler());
register(JdbcType.DATE, new DateOnlyTypeHandler());
//register(JdbcType.TIME, new TimeOnlyTypeHandler());
//register(java.sql.Date.class, new SqlDateTypeHandler());
//register(java.sql.Time.class, new SqlTimeTypeHandler());
//register(java.sql.Timestamp.class, new SqlTimestampTypeHandler());
// mybatis-typehandlers-jsr310
/*if (Jdk.dateAndTimeApiExists) {
Java8TypeHandlersRegistrar.registerDateAndTimeHandlers(this);
}*/
// issue #273
register(Character.class, new CharacterTypeHandler());
register(char.class, new CharacterTypeHandler());
register(JSONB.class, new JSONBTypeHandler());
}
public void setDefaultEnumTypeHandler(Class<? extends TypeHandler> typeHandler) {
this.defaultEnumTypeHandler = typeHandler;
}
public boolean hasTypeHandler(Class<?> javaType) {
return hasTypeHandler(javaType, null);
}
public boolean hasTypeHandler(TypeReference<?> javaTypeReference) {
return hasTypeHandler(javaTypeReference, null);
}
public boolean hasTypeHandler(Class<?> javaType, JdbcType jdbcType) {
return javaType != null && getTypeHandler((Type) javaType, jdbcType) != null;
}
public boolean hasTypeHandler(TypeReference<?> javaTypeReference, JdbcType jdbcType) {
return javaTypeReference != null && getTypeHandler(javaTypeReference, jdbcType) != null;
}
public TypeHandler<?> getMappingTypeHandler(Class<? extends TypeHandler<?>> handlerType) {
return ALL_TYPE_HANDLERS_MAP.get(handlerType);
}
public <T> TypeHandler<T> getTypeHandler(Class<T> type) {
return getTypeHandler((Type) type, null);
}
public <T> TypeHandler<T> getTypeHandler(TypeReference<T> javaTypeReference) {
return getTypeHandler(javaTypeReference, null);
}
public TypeHandler<?> getTypeHandler(JdbcType jdbcType) {
return JDBC_TYPE_HANDLER_MAP.get(jdbcType);
}
public <T> TypeHandler<T> getTypeHandler(Class<T> type, JdbcType jdbcType) {
return getTypeHandler((Type) type, jdbcType);
}
public <T> TypeHandler<T> getTypeHandler(TypeReference<T> javaTypeReference, JdbcType jdbcType) {
return getTypeHandler(javaTypeReference.getRawType(), jdbcType);
}
@SuppressWarnings("unchecked")
private <T> TypeHandler<T> getTypeHandler(Type type, JdbcType jdbcType) {
Map<JdbcType, TypeHandler<?>> jdbcHandlerMap = getJdbcHandlerMap(type);
TypeHandler<?> handler = null;
if (jdbcHandlerMap != null) {
handler = jdbcHandlerMap.get(jdbcType);
if (handler == null) {
handler = jdbcHandlerMap.get(null);
}
if (handler == null) {
// #591
handler = pickSoleHandler(jdbcHandlerMap);
}
}
// type drives generics here
return (TypeHandler<T>) handler;
}
@SuppressWarnings({ "rawtypes", "unchecked" })
private Map<JdbcType, TypeHandler<?>> getJdbcHandlerMap(Type type) {
Map<JdbcType, TypeHandler<?>> jdbcHandlerMap = TYPE_HANDLER_MAP.get(type);
if (NULL_TYPE_HANDLER_MAP.equals(jdbcHandlerMap)) {
return null;
}
if (jdbcHandlerMap == null && type instanceof Class) {
Class<?> clazz = (Class<?>) type;
if (clazz.isEnum()) {
jdbcHandlerMap = getJdbcHandlerMapForEnumInterfaces(clazz, clazz);
if (jdbcHandlerMap == null) {
register(clazz, getInstance(clazz, defaultEnumTypeHandler));
return TYPE_HANDLER_MAP.get(clazz);
}
} else {
jdbcHandlerMap = getJdbcHandlerMapForSuperclass(clazz);
}
}
TYPE_HANDLER_MAP.put(type, jdbcHandlerMap == null ? NULL_TYPE_HANDLER_MAP : jdbcHandlerMap);
return jdbcHandlerMap;
}
private Map<JdbcType, TypeHandler<?>> getJdbcHandlerMapForEnumInterfaces(Class<?> clazz, Class<?> enumClazz) {
for (Class<?> iface : clazz.getInterfaces()) {
Map<JdbcType, TypeHandler<?>> jdbcHandlerMap = TYPE_HANDLER_MAP.get(iface);
if (jdbcHandlerMap == null) {
jdbcHandlerMap = getJdbcHandlerMapForEnumInterfaces(iface, enumClazz);
}
if (jdbcHandlerMap != null) {
// Found a type handler regsiterd to a super interface
HashMap<JdbcType, TypeHandler<?>> newMap = new HashMap<JdbcType, TypeHandler<?>>();
for (Map.Entry<JdbcType, TypeHandler<?>> entry : jdbcHandlerMap.entrySet()) {
// Create a type handler instance with enum type as a constructor arg
newMap.put(entry.getKey(), getInstance(enumClazz, entry.getValue().getClass()));
}
return newMap;
}
}
return null;
}
private Map<JdbcType, TypeHandler<?>> getJdbcHandlerMapForSuperclass(Class<?> clazz) {
Class<?> superclass = clazz.getSuperclass();
if (superclass == null || Object.class.equals(superclass)) {
return null;
}
Map<JdbcType, TypeHandler<?>> jdbcHandlerMap = TYPE_HANDLER_MAP.get(superclass);
if (jdbcHandlerMap != null) {
return jdbcHandlerMap;
} else {
return getJdbcHandlerMapForSuperclass(superclass);
}
}
private TypeHandler<?> pickSoleHandler(Map<JdbcType, TypeHandler<?>> jdbcHandlerMap) {
TypeHandler<?> soleHandler = null;
for (TypeHandler<?> handler : jdbcHandlerMap.values()) {
if (soleHandler == null) {
soleHandler = handler;
} else if (!handler.getClass().equals(soleHandler.getClass())) {
// More than one type handlers registered.
return null;
}
}
return soleHandler;
}
public TypeHandler<Object> getUnknownTypeHandler() {
return UNKNOWN_TYPE_HANDLER;
}
public void register(JdbcType jdbcType, TypeHandler<?> handler) {
JDBC_TYPE_HANDLER_MAP.put(jdbcType, handler);
}
// java type + handler
public <T> void register(Class<T> javaType, TypeHandler<? extends T> typeHandler) {
register((Type) javaType, typeHandler);
}
private <T> void register(Type javaType, TypeHandler<? extends T> typeHandler) {
register(javaType, null, typeHandler);
}
public <T> void register(TypeReference<T> javaTypeReference, TypeHandler<? extends T> handler) {
register(javaTypeReference.getRawType(), handler);
}
// java type + jdbc type + handler
public <T> void register(Class<T> type, JdbcType jdbcType, TypeHandler<? extends T> handler) {
register((Type) type, jdbcType, handler);
}
private void register(Type javaType, JdbcType jdbcType, TypeHandler<?> handler) {
if (javaType != null) {
Map<JdbcType, TypeHandler<?>> map = TYPE_HANDLER_MAP.get(javaType);
if (map == null) {
map = new HashMap<JdbcType, TypeHandler<?>>();
TYPE_HANDLER_MAP.put(javaType, map);
}
map.put(jdbcType, handler);
}
ALL_TYPE_HANDLERS_MAP.put(handler.getClass(), handler);
}
// java type + handler type
public void register(String javaTypeClassName, String typeHandlerClassName) throws ClassNotFoundException {
register(Resources.classForName(javaTypeClassName), Resources.classForName(typeHandlerClassName));
}
public void register(Class<?> javaTypeClass, Class<?> typeHandlerClass) {
register(javaTypeClass, getInstance(javaTypeClass, typeHandlerClass));
}
// java type + jdbc type + handler type
public void register(Class<?> javaTypeClass, JdbcType jdbcType, Class<?> typeHandlerClass) {
register(javaTypeClass, jdbcType, getInstance(javaTypeClass, typeHandlerClass));
}
// Construct a handler (used also from Builders)
@SuppressWarnings("unchecked")
public <T> TypeHandler<T> getInstance(Class<?> javaTypeClass, Class<?> typeHandlerClass) {
if (javaTypeClass != null) {
try {
Constructor<?> c = typeHandlerClass.getConstructor(Class.class);
return (TypeHandler<T>) c.newInstance(javaTypeClass);
} catch (NoSuchMethodException ignored) {
// ignored
} catch (Exception e) {
throw new RuntimeException("Failed invoking constructor for handler " + typeHandlerClass, e);
}
}
try {
Constructor<?> c = typeHandlerClass.getConstructor();
return (TypeHandler<T>) c.newInstance();
} catch (Exception e) {
throw new RuntimeException("Unable to find a usable constructor for " + typeHandlerClass, e);
}
}
/**
* @since 3.2.2
*/
public Collection<TypeHandler<?>> getTypeHandlers() {
return Collections.unmodifiableCollection(ALL_TYPE_HANDLERS_MAP.values());
}
}
複製程式碼有了這些我們就可以輕鬆封裝結果集到物件了，預設的jdbcTemplate rowMapper如下：
package com.applet.sql.mapper;
import com.applet.sql.builder.SelectBuilder;
import com.applet.sql.record.DomainModelContext;
import com.applet.sql.record.DomainModelAnalysis;
import com.applet.sql.record.TableColumn;
import com.applet.sql.type.JdbcType;
import com.applet.sql.type.TypeHandler;
import com.applet.sql.type.TypeHandlerRegistry;
import com.applet.utils.SpringContextHelper;
import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.ReflectionUtils;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/**
* 預設的ORM方式
*
* @param <T>
* @author Jackie Liu
*/
public class DefaultRowMapper<T> implements RowMapper<T> {
private DomainModelAnalysis domainModelAnalysis;
public DefaultRowMapper(DomainModelAnalysis domainModelAnalysis) {
this.domainModelAnalysis = domainModelAnalysis;
}
public DefaultRowMapper(Class<T> cls) {
DomainModelContext commonModelContext = SpringContextHelper.getBean(DomainModelContext.class);
domainModelAnalysis = commonModelContext.getDomainModelAnalysis(cls);
}
@SuppressWarnings({"unchecked", "rawtypes"})
@Override
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
int count = metaData.getColumnCount();
T t = (T) BeanUtils.instantiate(domainModelAnalysis.getClazz());
for (int i = 1; i <= count; i++) {
String colName = metaData.getColumnLabel(i).toUpperCase();
TableColumn tableColumn = domainModelAnalysis.getTableColumnByColumnName(colName);
if (tableColumn == null) {
extendColumnRow(colName, t, metaData, i, rs);
continue;
}
int columnType = metaData.getColumnType(i);
TypeHandler typeHandler = getTypeHandler(columnType, tableColumn.getJavaType());
ReflectionUtils.invokeMethod(tableColumn.getFieldSetMethod(), t, typeHandler.getResult(rs, i));
}
return t;
}
private boolean extendColumnRow(String colName, T t, ResultSetMetaData metaData, int index, ResultSet rs) throws SQLException {
SelectBuilder.Column column = SelectBuilder.getExtendColumn(colName);
if (column == null) {
return false;
}
Object manyToOne = ReflectionUtils.invokeMethod(column.getGetModelMethod(), t);
if (manyToOne == null) {
manyToOne = BeanUtils.instantiate(column.getManyToOneClass());
ReflectionUtils.invokeMethod(column.getSetModelMethod(), t, manyToOne);
}
TableColumn tableColumn = column.getManyToOneTableColumn();
int columnType = metaData.getColumnType(index);
TypeHandler typeHandler = getTypeHandler(columnType, tableColumn.getJavaType());
ReflectionUtils.invokeMethod(tableColumn.getFieldSetMethod(), manyToOne, typeHandler.getResult(rs, index));
return true;
}
private TypeHandler getTypeHandler(int columnType, Class<?> javaType) {
TypeHandlerRegistry typeHandlerRegistry = DomainModelContext.getTypeHandlerRegistry();
TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(JdbcType.forCode(columnType));
if (typeHandler != null && javaType.getName().equals(typeHandler.getClazz().getName())) {
return typeHandler;
}
typeHandler = typeHandlerRegistry.getTypeHandler(javaType);
return typeHandler;
}
}
複製程式碼下一節將為大家介紹如何封裝不同資料庫的分頁語句，只需要配置一下即可，應用層無需實現分頁邏輯。