在日常开发中,我们经常需要访问数据库,从而不可避免需要去跟ResultSet打交道。Spring JdbcTemplate 对原生的JDBC API进行了包装,让程序员更专注于业务逻辑编码,例如查询数据库操作:
public User queryUserById(long id) {
return jdbcTemplate.queryForObject("SELECT * FROM tb_employee WHERE id=?", new Object[]{id}, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int i) throws SQLException {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
return user;
}
});
}
我们只需要编写自己的RowMapper将ResultSet 转换为JavaBean即可。
但是这样还是不够简洁,特别是当JavaBean的属性特别多时,实现RowMapper会让人很烦躁。于是乎自己造了一个轮子,它自动将ResultSet 转换为Array、Map、JavaBean等对象。
简化Spring JdbcTemplate
1、IConverter 接口
package com.bytebeats.toolkit.orm;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public interface IConverter {
Object[] toArray(ResultSet rs) throws SQLException;
Map<String, Object> toMap(ResultSet rs) throws SQLException;
<T> T toBean(ResultSet rs, Class<T> type) throws SQLException;
<T> List<T> toBeanList(ResultSet rs, Class<T> type) throws SQLException;
}
IConverter 接口定义了将ResultSet 转换为Object[] 、Map、JavaBean、List<JavaBean>行为。
接下来,使用Spring JdbcTemplate 查询数据库就是一件很惬意的事情了。
2、查询数据库
1、User类
package com.bytebeats.toolkit.samples.model;
import com.bytebeats.toolkit.orm.Column;
import java.util.Date;
public class User {
private long id;
private String name;
private int age;
private double amount;
@Column("create_time")
private Date createTime;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public double getAmount() {
return amount;
}
public void setAmount(double amount) {
this.amount = amount;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
2、IUserDao
package com.bytebeats.toolkit.samples.db.dao;
import com.bytebeats.toolkit.samples.model.User;
import java.util.List;
public interface IUserDao {
User queryUserById(long id);
List<User> queryUsers();
int insert(User user);
}
3、UserDaoImpl类
package com.bytebeats.toolkit.samples.db.dao.impl;
import com.bytebeats.toolkit.orm.IConverter;
import com.bytebeats.toolkit.samples.db.dao.IUserDao;
import com.bytebeats.toolkit.samples.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Repository("userDao")
public class UserDaoImpl implements IUserDao {
@Resource(name = "pkJdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Autowired
private IConverter converter;
public User queryUserById(long id) {
return jdbcTemplate.queryForObject("SELECT * FROM tb_employee WHERE id=?", new Object[]{id}, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int i) throws SQLException {
return converter.toBean(rs, User.class);
}
});
}
public List<User> queryUsers() {
return jdbcTemplate.query("SELECT * FROM tb_employee", new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int i) throws SQLException {
return converter.toBean(rs, User.class);
}
});
}
public int insert(User user) {
return 0;
}
}
一行代码搞定ResultSet 到JavaBean的转换,是不是很easy。
实现原理
原理:
注解+反射技术
通过反射获取到JavaBean的属性列表,通过ResultSetMetaData 获取数据库表的column列表,然后根据列名找到与之相对的属性(名称相同&类型一致)并赋值。JavaBean的属性名可能会与列名不一致,可以通过Column注解指定其对应的列名。
不卖关子了,直接上代码,Talk is cheap, show me the code.
BasicRowConverter
package com.bytebeats.toolkit.orm;
import com.bytebeats.toolkit.annotation.ThreadSafe;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@ThreadSafe
public class BasicRowConverter implements IConverter {
private final BeanConverter converter = new BeanConverter();
@Override
public Object[] toArray(ResultSet rs) throws SQLException {
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for (int i = 0; i < cols; i++) {
result[i] = rs.getObject(i + 1);
}
return result;
}
@Override
public Map<String, Object> toMap(ResultSet rs) throws SQLException {
Map<String, Object> result = new HashMap<String, Object>();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
for (int i = 1; i <= cols; i++) {
String columnName = rsmd.getColumnLabel(i);
if (null == columnName || 0 == columnName.length()) {
columnName = rsmd.getColumnName(i);
}
result.put(columnName, rs.getObject(i));
}
return result;
}
@Override
public <T> T toBean(ResultSet rs, Class<T> type) throws SQLException {
return this.converter.toBean(rs, type);
}
@Override
public <T> List<T> toBeanList(ResultSet rs, Class<T> type) throws SQLException {
return this.converter.toBeanList(rs, type);
}
}
BeanConverter类
package com.bytebeats.toolkit.orm;
import com.bytebeats.toolkit.model.ClassInfo;
import com.bytebeats.toolkit.annotation.ThreadSafe;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.LoadingCache;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;
import java.util.concurrent.ExecutionException;
@ThreadSafe
public class BeanConverter {
protected static final int PROPERTY_NOT_FOUND = -1;
private static final Map<Class<?>, Object> primitiveDefaults = new HashMap<Class<?>, Object>();
private final LoadingCache<Class<?>, ClassInfo> columnToPropertyCache = CacheBuilder.newBuilder()
.maximumSize(200)
.build(new CacheLoader<Class<?>, ClassInfo>() {
@Override
public ClassInfo load(Class<?> cls) throws Exception {
ClassInfo classInfo = new ClassInfo();
classInfo.setColumnToPropertyOverrides(getColumnToProperty(cls));
classInfo.setProps(getPropertyDescriptors(cls));
return classInfo;
}
});
static {
primitiveDefaults.put(Integer.TYPE, Integer.valueOf(0));
primitiveDefaults.put(Short.TYPE, Short.valueOf((short) 0));
primitiveDefaults.put(Byte.TYPE, Byte.valueOf((byte) 0));
primitiveDefaults.put(Float.TYPE, Float.valueOf(0f));
primitiveDefaults.put(Double.TYPE, Double.valueOf(0d));
primitiveDefaults.put(Long.TYPE, Long.valueOf(0L));
primitiveDefaults.put(Boolean.TYPE, Boolean.FALSE);
primitiveDefaults.put(Character.TYPE, Character.valueOf((char) 0));
}
public BeanConverter(){
}
public <T> T toBean(ResultSet rs, Class<T> type) throws SQLException {
ClassInfo classInfo = null;
try {
classInfo = columnToPropertyCache.get(type);
} catch (ExecutionException e) {
throw new IllegalArgumentException("", e);
}
ResultSetMetaData rsmd = rs.getMetaData();
int[] columnToProperty = this.mapColumnsToProperties(rsmd, classInfo.getProps(), classInfo.getColumnToPropertyOverrides());
return createBean(rs, type, classInfo.getProps(), columnToProperty);
}
public <T> List<T> toBeanList(ResultSet rs, Class<T> type) throws SQLException {
List<T> results = new ArrayList<T>();
if (!rs.next()) {
return results;
}
ClassInfo classInfo = null;
try {
classInfo = columnToPropertyCache.get(type);
} catch (ExecutionException e) {
throw new IllegalArgumentException("", e);
}
ResultSetMetaData rsmd = rs.getMetaData();
int[] columnToProperty = this.mapColumnsToProperties(rsmd, classInfo.getProps(), classInfo.getColumnToPropertyOverrides());
do {
results.add(this.createBean(rs, type, classInfo.getProps(), columnToProperty));
} while (rs.next());
return results;
}
private <T> T createBean(ResultSet rs, Class<T> type,
PropertyDescriptor[] props, int[] columnToProperty)
throws SQLException {
T bean = this.newInstance(type);
for (int i = 1; i < columnToProperty.length; i++) {
if (columnToProperty[i] == PROPERTY_NOT_FOUND) {
continue;
}
PropertyDescriptor prop = props[columnToProperty[i]];
Class<?> propType = prop.getPropertyType();
Object value = null;
if(propType != null) {
value = this.processColumn(rs, i, propType);
if (value == null && propType.isPrimitive()) {
value = primitiveDefaults.get(propType);
}
this.setValueToBean(bean, prop, value);
}
}
return bean;
}
private void setValueToBean(Object target, PropertyDescriptor prop, Object value)
throws SQLException {
Method setter = prop.getWriteMethod();
if (setter == null) {
return;
}
Class<?>[] params = setter.getParameterTypes();
try {
// convert types for some popular ones
if (value instanceof java.util.Date) {
final String targetType = params[0].getName();
if ("java.sql.Date".equals(targetType)) {
value = new java.sql.Date(((java.util.Date) value).getTime());
} else
if ("java.sql.Time".equals(targetType)) {
value = new java.sql.Time(((java.util.Date) value).getTime());
} else
if ("java.sql.Timestamp".equals(targetType)) {
Timestamp tsValue = (Timestamp) value;
int nanos = tsValue.getNanos();
value = new java.sql.Timestamp(tsValue.getTime());
((Timestamp) value).setNanos(nanos);
}
}
// Don't call setter if the value object isn't the right type
if (this.isCompatibleType(value, params[0])) {
setter.invoke(target, new Object[]{value});
} else {
throw new SQLException(
"Cannot set " + prop.getName() + ": incompatible types, cannot convert "
+ value.getClass().getName() + " to " + params[0].getName());
}
} catch (IllegalArgumentException e) {
throw new SQLException(
"Cannot set " + prop.getName() + ": " + e.getMessage());
} catch (IllegalAccessException e) {
throw new SQLException(
"Cannot set " + prop.getName() + ": " + e.getMessage());
} catch (InvocationTargetException e) {
throw new SQLException(
"Cannot set " + prop.getName() + ": " + e.getMessage());
}
}
private boolean isCompatibleType(Object value, Class<?> type) {
// Do object check first, then primitives
if (value == null || type.isInstance(value)) {
return true;
} else if (type.equals(Integer.TYPE) && value instanceof Integer) {
return true;
} else if (type.equals(Long.TYPE) && value instanceof Long) {
return true;
} else if (type.equals(Double.TYPE) && value instanceof Double) {
return true;
} else if (type.equals(Float.TYPE) && value instanceof Float) {
return true;
} else if (type.equals(Short.TYPE) && value instanceof Short) {
return true;
} else if (type.equals(Byte.TYPE) && value instanceof Byte) {
return true;
} else if (type.equals(Character.TYPE) && value instanceof Character) {
return true;
} else if (type.equals(Boolean.TYPE) && value instanceof Boolean) {
return true;
}
return false;
}
protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
PropertyDescriptor[] props,
Map<String, String> columnToPropertyOverrides) throws SQLException {
int cols = rsmd.getColumnCount();
int[] columnToProperty = new int[cols + 1];
Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
for (int col = 1; col <= cols; col++) {
String columnName = rsmd.getColumnLabel(col);
if (null == columnName || 0 == columnName.length()) {
columnName = rsmd.getColumnName(col);
}
String propertyName = columnToPropertyOverrides.get(columnName);
if (propertyName == null) {
propertyName = columnName;
}
for (int i = 0; i < props.length; i++) {
if (propertyName.equalsIgnoreCase(props[i].getName())) {
columnToProperty[col] = i;
break;
}
}
}
return columnToProperty;
}
private PropertyDescriptor[] getPropertyDescriptors(Class<?> c)
throws SQLException {
BeanInfo beanInfo = null;
try {
beanInfo = Introspector.getBeanInfo(c);
} catch (IntrospectionException e) {
throw new SQLException(
"Bean introspection failed: " + e.getMessage());
}
return beanInfo.getPropertyDescriptors();
}
protected <T> T newInstance(Class<T> c) throws SQLException {
try {
return c.newInstance();
} catch (InstantiationException e) {
throw new SQLException(
"Cannot create " + c.getName() + ": " + e.getMessage());
} catch (IllegalAccessException e) {
throw new SQLException(
"Cannot create " + c.getName() + ": " + e.getMessage());
}
}
protected Object processColumn(ResultSet rs, int index, Class<?> propType)
throws SQLException {
if ( !propType.isPrimitive() && rs.getObject(index) == null ) {
return null;
}
if (propType.equals(String.class)) {
return rs.getString(index);
} else if (
propType.equals(Integer.TYPE) || propType.equals(Integer.class)) {
return Integer.valueOf(rs.getInt(index));
} else if (
propType.equals(Boolean.TYPE) || propType.equals(Boolean.class)) {
return Boolean.valueOf(rs.getBoolean(index));
} else if (propType.equals(Long.TYPE) || propType.equals(Long.class)) {
return Long.valueOf(rs.getLong(index));
} else if (
propType.equals(Double.TYPE) || propType.equals(Double.class)) {
return Double.valueOf(rs.getDouble(index));
} else if (
propType.equals(Float.TYPE) || propType.equals(Float.class)) {
return Float.valueOf(rs.getFloat(index));
} else if (
propType.equals(Short.TYPE) || propType.equals(Short.class)) {
return Short.valueOf(rs.getShort(index));
} else if (propType.equals(Byte.TYPE) || propType.equals(Byte.class)) {
return Byte.valueOf(rs.getByte(index));
} else if (propType.equals(Timestamp.class)) {
return rs.getTimestamp(index);
} else {
return rs.getObject(index);
}
}
private Map<String,String> getColumnToProperty(Class<?> type) {
Map<String, String> columnToPropertyMap = new HashMap<String, String>();
Field[] fields = type.getDeclaredFields();
for (Field field : fields){
String columnName = null;
if(field.isAnnotationPresent(Column.class)){
Column col = field.getAnnotation(Column.class);
if(col!=null){
columnName = col.value();
}
}
if(columnName==null || "".equals(columnName)){
columnName = field.getName();
}
columnToPropertyMap.put(columnName, field.getName());
}
return columnToPropertyMap;
}
}
ClassInfo 类
package com.bytebeats.toolkit.model;
import java.beans.PropertyDescriptor;
import java.util.Map;
public class ClassInfo {
private Map<String, String> columnToPropertyOverrides;
private PropertyDescriptor[] props;
public Map<String, String> getColumnToPropertyOverrides() {
return columnToPropertyOverrides;
}
public void setColumnToPropertyOverrides(Map<String, String> columnToPropertyOverrides) {
this.columnToPropertyOverrides = columnToPropertyOverrides;
}
public PropertyDescriptor[] getProps() {
return props;
}
public void setProps(PropertyDescriptor[] props) {
this.props = props;
}
}
Column 注解
package com.bytebeats.toolkit.orm;
import java.lang.annotation.*;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Documented
public @interface Column {
String value() default "";
}