Spring JdbcTemplate 辅助类

在日常开发中,我们经常需要访问数据库,从而不可避免需要去跟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 "";
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值