SpringBoot+JdbcTemplate通用CRUD

package com.common.service;
 
import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
 
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
 
import com.common.annotation.CustomTableName;
 
public abstract class BaseService<Entity>{
	protected  static Logger logger;
    /** 具体操作的实体类对象 */
    private Class<Entity>       entityClass;
    /** 名称加工处理器 */
    private NameHandler    nameHandler;
    /** 数据库表名字*/
    protected final String entityTableName;
    
    /** spring jdbcTemplate 对象 */
    @Autowired
    protected JdbcTemplate jdbcTemplate;
    public BaseService() {
        logger = LoggerFactory.getLogger(this.getClass());
        //获取到当前类的类型
        Type parentType = this.getClass().getGenericSuperclass();
        System.out.println("parentType:"+parentType);
        // 转成参数类型接口
        ParameterizedType paramterType = (ParameterizedType) parentType;
        System.out.println("paramterType:"+paramterType);
        // 得到泛型类型
        Type[] types = paramterType.getActualTypeArguments();
        System.out.println("types:"+types);
        // 得到传入泛型的类
        entityClass = (Class<Entity>) types[0];
        CustomTableName customTableName = entityClass.getAnnotation(CustomTableName.class);
        if(customTableName != null && StringUtils.isNotEmpty(customTableName.value())){
            entityTableName = customTableName.value();
        }else{
            entityTableName = entityClass.getSimpleName().toLowerCase();
        }
        
        System.out.println("entityTableName:"+entityTableName);
    }
    /**
     * 获取实际运行时的名称处理器
     *
     * @return
     */
    private NameHandler getActualNameHandler() {
        if (nameHandler == null) {
            synchronized (this) {
                if (nameHandler == null) {
                    nameHandler = this.getNameHandler();
                }
            }
        }
        return nameHandler;
    }
    /**
     * 得到名称处理器，子类覆盖此方法实现自己的名称转换处理器
     *
     * @return
     */
    protected NameHandler getNameHandler() {
        return new DefaultNameHandler();
    }
    /**
     * 插入一条记录
     *
     * @param entity
     */
    public Long insert(Entity entity) {
        final SqlContext sqlContext = SqlUtils.buildInsertSql(entity, this.getActualNameHandler());
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sqlContext.getSql().toString(),
                    new String[] { sqlContext.getPrimaryKey() });
                int index = 0;
                for (Object param : sqlContext.getParams()) {
                    index++;
                    ps.setObject(index, param);
                }
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }
    /**
     * 更新记录
     *
     * @param entity
     */
    public void update(Entity entity) {
        SqlContext sqlContext = SqlUtils.buildUpdateSql(entity, this.getActualNameHandler());
        jdbcTemplate.update(sqlContext.getSql().toString(), sqlContext.getParams().toArray());
    }
    /**
     * 删除记录
     *
     * @param id
     */
    public int delete(Serializable id) {
        //String tableName = this.getActualNameHandler().getTableName(entityClass.getSimpleName());
        String primaryName = this.getNameHandler().getPrimaryName(entityClass.getSimpleName());
        String sql = "DELETE FROM " + entityTableName + " WHERE " + primaryName + " = ?";
        return jdbcTemplate.update(sql, id);
    }
    /**
     * 删除所有记录
     */
    public void deleteAll() {
        String sql = " TRUNCATE TABLE " + entityTableName;
        jdbcTemplate.execute(sql);
    }
    /**
     * 得到记录
     *
     * @param id
     * @return
     */
    public Entity getEntityById(Integer id){
        if(id == null || id <= 0){
            logger.error("根据ID查询数据异常,id:{}",id);
            return null;
        }
        String sql = "SELECT * FROM "+entityTableName+" WHERE id = ?";
        Entity entity = jdbcTemplate.queryForObject(sql, entityClass, new Object[]{id});
        return entity;
    }
 
    /**
     * 查询分页列表
     *
     * @param entity
     * @return
     */
 
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Entity> queryPageLists(Entity entity,PageModel pegeModel){
        String  sql = "select top "+pegeModel.getPageSize()+"* from (select row_number()over(order by "+pegeModel.getCloumn()+" "+pegeModel.getFlag()+")rownumber,* from "+entityTableName+")  a1  where  rownumber >"+pegeModel.getPageSize()+"*"+(pegeModel.getPageNum()-1)+"";
        StringBuilder querySql = new StringBuilder(sql);
        //不调用queryCount方法，条件共同组装一次，减少反射获取的次数
        SqlContext sqlContext = SqlUtils.buildQueryCondition(entity, this.getActualNameHandler());
        if (sqlContext.getSql().length() > 0) {
            querySql.append(" and ");
            querySql.append(sqlContext.getSql());
        }
        List<Entity> entityLists = jdbcTemplate.query(querySql.toString(),sqlContext.getParams().toArray(),new BeanPropertyRowMapper(entityClass));
        return entityLists;
    }
    
    /**
     * 查询所有的信息
     * @param entity
     * @return
     */
    public List<Entity> queryLists(Entity entity){
        String  sql = "select * from ";
        StringBuilder querySql = new StringBuilder(sql);
        querySql.append(entityTableName);
        //不调用queryCount方法，条件共同组装一次，减少反射获取的次数
        SqlContext sqlContext = SqlUtils.buildQueryCondition(entity, this.getActualNameHandler());
        if (sqlContext.getSql().length() > 0) {
            querySql.append(" where ");
            querySql.append(sqlContext.getSql());
        }
        List<Entity> entityLists = jdbcTemplate.query(querySql.toString(),sqlContext.getParams().toArray(), new BeanPropertyRowMapper(entityClass));
        return entityLists;
    }
    /**
     * 查询总条数
     * @param entity
     * @return
     */
    public Integer queryContents(Entity entity){
        String  sql = "select count(*) from "+entityTableName+"";
        StringBuilder querySql = new StringBuilder(sql);
        //不调用queryCount方法，条件共同组装一次，减少反射获取的次数
        SqlContext sqlContext = SqlUtils.buildQueryCondition(entity, this.getActualNameHandler());
        if (sqlContext.getSql().length() > 0) {
            querySql.append(" where ");
            querySql.append(sqlContext.getSql());
        }
        Integer count = jdbcTemplate.queryForObject(querySql.toString(),sqlContext.getParams().toArray(),Integer.class);
        return count;
    }
    
 
}
package com.common.service;
 
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
/**
 * 类辅助
 *
 * User: liyd
 * Date: 2/12/14
 * Time: 10:08 PM
 */
public class ClassUtils {
 
    /** 日志对象 */
    private static final Logger  LOG  = LoggerFactory.getLogger(ClassUtils.class);
 
    /**
     * Map keyed by class containing CachedIntrospectionResults.
     * Needs to be a WeakHashMap with WeakReferences as values to allow
     * for proper garbage collection in case of multiple class loaders.
     */
    private static final Map<Class, BeanInfo> classCache = Collections.synchronizedMap(new WeakHashMap<Class, BeanInfo>());
 
    /**
     * 获取类本身的BeanInfo，不包含父类属性
     * 
     * @param clazz
     * @return
     */
    public static BeanInfo getSelfBeanInfo(Class<?> clazz) {
        try {
            BeanInfo beanInfo;
            if (classCache.get(clazz) == null) {
                beanInfo = Introspector.getBeanInfo(clazz, clazz.getSuperclass());
                classCache.put(clazz, beanInfo);
                // Immediately remove class from Introspector cache, to allow for proper
                // garbage collection on class loader shutdown - we cache it here anyway,
                // in a GC-friendly manner. In contrast to CachedIntrospectionResults,
                // Introspector does not use WeakReferences as values of its WeakHashMap!
                Class classToFlush = clazz;
                do {
                    Introspector.flushFromCaches(classToFlush);
                    classToFlush = classToFlush.getSuperclass();
                } while (classToFlush != null);
            } else {
                beanInfo = classCache.get(clazz);
            }
            return beanInfo;
        } catch (IntrospectionException e) {
            LOG.error("获取BeanInfo失败", e);
           // throw new MincoderException(e);
        }
		return null;
    }
 
    /**
     * 初始化实例
     * 
     * @param clazz
     * @return
     */
    public static Object newInstance(Class<?> clazz) {
        try {
            return clazz.newInstance();
        } catch (Exception e) {
            LOG.error("根据class创建实例失败", e);
            //throw new MincoderException(e);
        }
		return null;
    }
}
 
 
package com.common.service;
/**
 * 默认名称处理handler
 * 
 * User: liyd
 * Date: 2/12/14
 * Time: 4:51 PM
 */
public class DefaultNameHandler implements NameHandler {
	 /** 字段前缀 */
    //private static final String PREFIX     = "_";
    /** 主键后缀 */
    private static final String ID= "id";
    /**
     * 根据实体名获取表名
     *
     * @param entityName
     * @return
     */
    @Override
    public String getTableName(String entityName) {
        //Java属性的骆驼命名法转换回数据库下划线“_”分隔的格式
       return NameUtils.getUnderlineName(entityName);
    }
    /**
     * 根据表名获取主键名
     *
     * @param entityName
     * @return
     */
    @Override
    public String getPrimaryName(String entityName) {
        //String underlineName = NameUtils.getUnderlineName(entityName);
        //正如前面说到的，数据库列名统一以“_”开始，主键以表名加上“_id” 如user表主键即“_user_id”
        return ID;
    }
    /**
     * 根据属性名获取列名
     *
     * @param fieldName
     * @return
     */
    @Override
    public String getColumnName(String fieldName) {
        String underlineName = NameUtils.getUnderlineName(fieldName);
        //数据库列名统一以“_”开始
        return underlineName;
    }
}
 

package com.common.service;
/**
 * 名称处理接口
 * 
 * User: liyd
 * Date: 2/12/14
 * Time: 4:51 PM
 */
public interface NameHandler {
    /**
     * 根据实体名获取表名
     *
     * @param entityName
     * @return
     */
    public String getTableName(String entityName);
    /**
     * 根据表名获取主键名
     * 
     * @param entityName
     * @return
     */
    public String getPrimaryName(String entityName);
    /**
     * 根据属性名获取列名
     *
     * @param fieldName
     * @return
     */
    public String getColumnName(String fieldName);
}
 

package com.common.service;
 
public class NameUtils {
 
	public static String getUnderlineName(String entityName) {
		return entityName.toLowerCase();
	}
 
}
public class PageModel {
	
	/**每页大小*/
	private Integer pageSize;
	/**第几页*/
	private Integer pageNum;
	/**根据什么字段排序*/
	private Object  cloumn;
	/**desc   还是  asc*/
	private String flag;
    //get set 方法省去
}
public class SqlContext {
 
    /** 执行的sql */
    private StringBuilder sql;
 
    /** 主键名称 */
    private String   primaryKey;
 
    /** 参数，对应sql中的?号 */
    private List<Object>  params;
    //此处省略全参构造和get/set方法
}
package com.common.service;
 
import java.beans.BeanInfo;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import com.walmart.common.annotation.CustomTableName;
 
/**
 * sql辅助为类
 * 
 * User: liyd
 * Date: 2/13/14
 * Time: 10:03 AM
 */
public class SqlUtils {
 
    /** 日志对象 */
    private static final Logger LOG = LoggerFactory.getLogger(SqlUtils.class);
    
    /**
     * 构建insert语句
     *
     * @param entity 实体映射对象
     * @param nameHandler 名称转换处理器
     * @return
     */
    public static SqlContext buildInsertSql(Object entity, NameHandler nameHandler) {
        Class<?> clazz = entity.getClass();
        String tableName = nameHandler.getTableName(clazz.getSimpleName());//获取表名
        String primaryName = nameHandler.getPrimaryName(clazz.getSimpleName());//获取主键名
        StringBuilder sql = new StringBuilder("insert into ");
        List<Object> params = new ArrayList<Object>();
        sql.append(tableName);
 
        //获取属性信息
        BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
        PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
        sql.append("(");
        StringBuilder args = new StringBuilder();
        args.append("(");
        for (PropertyDescriptor pd : pds) {
            Object value = getReadMethodValue(pd.getReadMethod(), entity);
            if (value == null) {
                continue;
            }
            sql.append(nameHandler.getColumnName(pd.getName()));//获取列名
            args.append("?");
            params.add(value);
            sql.append(",");
            args.append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        args.deleteCharAt(args.length() - 1);
        args.append(")");
        sql.append(")");
        sql.append(" values ");
        sql.append(args);
        return new SqlContext(sql, primaryName, params);
    }
 
    /**
     * 构建更新sql
     * 
     * @param entity
     * @param nameHandler
     * @return
     */
    public static SqlContext buildUpdateSql(Object entity, NameHandler nameHandler) {
        Class<?> clazz = entity.getClass();
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<Object>();
        String tableName = nameHandler.getTableName(clazz.getSimpleName());
        String primaryName = nameHandler.getPrimaryName(clazz.getSimpleName());
        //获取属性信息
        BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(clazz);
        PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
 
        sql.append("update ");
        sql.append(tableName);
        sql.append(" set ");
        Object primaryValue = null;
        for (PropertyDescriptor pd : pds) {
            Object value = getReadMethodValue(pd.getReadMethod(), entity);
            if (value == null) {
                continue;
            }
            String columnName = nameHandler.getColumnName(pd.getName());
            if (primaryName.equalsIgnoreCase(columnName)) {
                primaryValue = value;
            }
            sql.append(columnName);
            sql.append(" = ");
            sql.append("?");
            params.add(value);
            sql.append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where ");
        sql.append(primaryName);
        sql.append(" = ?");
        params.add(primaryValue);
        return new SqlContext(sql, primaryName, params);
    }
 
    /**
     * 构建查询条件
     * 
     * @param entity
     * @param nameHandler
     */
    public static SqlContext buildQueryCondition(Object entity, NameHandler nameHandler) {
        //获取属性信息
        BeanInfo beanInfo = ClassUtils.getSelfBeanInfo(entity.getClass());
        //PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entityClass);
        PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
        StringBuilder condition = new StringBuilder();
        List<Object> params = new ArrayList<Object>();
        int count = 0;
        for (PropertyDescriptor pd : pds) {
            Object value = getReadMethodValue(pd.getReadMethod(), entity);
            if (value == null || value=="") {
                continue;
            }
            if (count > 0) {
                condition.append(" and ");
            }
            condition.append(nameHandler.getColumnName(pd.getName()));
            condition.append(" = ?");
            params.add(value);
            count++;
        }
        return new SqlContext(condition, null, params);
    }
 
    /**
     * 获取属性值
     *
     * @param readMethod
     * @param entity
     * @return
     */
    private static Object getReadMethodValue(Method readMethod, Object entity) {
        if (readMethod == null) {
            return null;
        }
        try {
            if (!Modifier.isPublic(readMethod.getDeclaringClass().getModifiers())) {
                readMethod.setAccessible(true);
            }
            return readMethod.invoke(entity);
        } catch (Exception e) {
            LOG.error("获取属性值失败", e);
            //throw new MincoderException(e);
        }
		return entity;
    }
}