上一篇文章为大家介绍了如何使用反射解析领域模型的属性信息并缓存，本节将介绍如何自动封装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;
}
}
下一节将为大家介绍如何封装不同数据库的分页语句，只需要配置一下即可，应用层无需实现分页逻辑。
网友评论