JdbcTemplate工具类DbCriteria

package pers.vclee.fastweb.mapper;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Component;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.alibaba.fastjson.serializer.SerializerFeature.DisableCircularReferenceDetect;
import static com.alibaba.fastjson.serializer.SerializerFeature.WriteDateUseDateFormat;

/**
 * Author: sir.li
 * email:  lesli2@qq.com
 * Date:   2020/8/6
 */
@Component
public class DbCriteria implements ApplicationContextAware {

    private static final Logger logger = LoggerFactory.getLogger(DbCriteria.class);
    private final static List<String> list = Arrays.asList("String", "Integer", "Long", "Double", "BigDecimal", "BigInteger");

    private static JdbcTemplate jdbcTemplate;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
    }

    public static <T> List<T> queryList(String preSql, Class<T> clazz, Object... params) {
        if (list.contains(clazz.getSimpleName())) {
            return jdbcTemplate.queryForList(preSql, params, clazz);
        }
        return jdbcTemplate.query(preSql, new BeanPropertyRowMapper<>(clazz), params);
    }

    public static List<Map<String, Object>> queryList(String preSql, Object... params) {
        return jdbcTemplate.queryForList(preSql, params);
    }

    public static <T> T queryObject(String preSql, Class<T> clazz, Object... params) {
        try {
            if(list.contains(clazz.getSimpleName())) {
                return jdbcTemplate.queryForObject(preSql, params, clazz);
            }
            return jdbcTemplate.queryForObject(preSql, params, new BeanPropertyRowMapper<>(clazz));
        } catch (IncorrectResultSizeDataAccessException e) {
//            logger.error("查询单个数据sql错误,原因为：{}", e.getMessage(),e);
            return null;
        }
    }

    public static <T> T queryById(Object onlyKey,String tableName,Class<T> clazz) {
        return queryObject("select * from " + tableName + " where id=" + preValue(onlyKey), clazz);
    }

    public static JSONObject queryForJsonObject(String preSql, Object... params) {
        try {
            return JSONObject.parseObject(JSONObject.toJSONString(jdbcTemplate.queryForMap(preSql, params)));
        } catch (IncorrectResultSizeDataAccessException e) {
//            logger.error("查询单个数据sql错误,原因为：{}", e.getMessage(), e);
            return null;
        }
    }

    public static int update(String preSql, Object... params) {
        return jdbcTemplate.update(preSql, params);
    }

    public static int updateById(Object obj,String tableName) {
        JSONObject json = JSONObject.parseObject(JSON.toJSONString(obj, DisableCircularReferenceDetect,WriteDateUseDateFormat));
        StringBuilder preSql = new StringBuilder("update " + tableName + " set ");
        json.forEach((key, value) -> {
            if (!key.equals("id")) {
                preSql.append(camelToUnderline(key)).append("=")
                        .append(preValue(value)).append(",");
            }
        });
        preSql.deleteCharAt(preSql.length() - 1);
        preSql.append(" where id =").append(preValue(json.get("id"))).append(";");
        return jdbcTemplate.update(preSql.toString());
    }

    /**
     * 非自增插入
     * @param object 对象字段
     * @param tableName 表名称
     * @return 插入数量
     */
    public static int insertNx(Object object, String tableName){
        return jdbcTemplate.update(createSingleTableInsertSqlByEntity(object,tableName));
    }

    /**
     * 自增插入(自动设置插入对象的主键)
     * @param object 对象字段
     * @param tableName 表名称
     * @return 插入数量
     */
    public static int insert(Object object, String tableName){
        try {
            String preSql = createSingleTableInsertSqlByEntity(object,tableName);
            GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
            int addNum = jdbcTemplate.update(connection -> connection.prepareStatement(preSql, PreparedStatement.RETURN_GENERATED_KEYS), keyHolder);
            Class<?> clazz = object.getClass();
            Method setMethod = clazz.getMethod("setId", clazz);
            setMethod.invoke(object, Objects.requireNonNull(keyHolder.getKey()).intValue());
            return addNum;
        }catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e){
            logger.error("pojo对象反射Id字段失败:"+e.getMessage());
            return 0;
        }
    }

    public static int insert(String preSql, Object... params) {
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(preSql, PreparedStatement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                ps.setObject(i + 1, param);
            }
            return ps;
        }, keyHolder);
        return Objects.requireNonNull(keyHolder.getKey()).intValue();
    }


    public static int delete(String preSql, Object... params) {
        return jdbcTemplate.update(preSql, params);
    }

    /**
     * 根据对象生成单表插入sql语句(对象字段可以直接插入表)
     *
     * @param obj       表字段对象
     * @param tableName 插入表名
     * @return preSql 预检查sql
     */
    private static String createSingleTableInsertSqlByEntity(Object obj, String tableName) {
        JSONObject json = JSONObject.parseObject(JSON.toJSONString(obj, DisableCircularReferenceDetect,WriteDateUseDateFormat));
        StringBuilder preSql = new StringBuilder("insert into " + tableName);
        StringBuilder keys = new StringBuilder(" (");
        StringBuilder values = new StringBuilder("(");
        json.forEach((key, value) -> {
            keys.append(camelToUnderline(key)).append(",");
            values.append(preValue(value)).append(",");
        });
        keys.deleteCharAt(keys.length() - 1);
        values.deleteCharAt(values.length() - 1);
        keys.append(") values ");
        values.append(");");
        preSql.append(keys.toString()).append(values.toString());
        return preSql.toString();
    }

    private static Object preValue(Object value) {
        if (value instanceof String) {
            return "'" + value + "'";
        } else if (value instanceof Number) {
            return value;
        } else if(value instanceof Date){
            return Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value));
        }else {
            return "'" + JSONObject.toJSONString(value) + "'";
        }
    }

    private static String camelToUnderline(String filedName) {
        int len = filedName.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = filedName.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append("_");
                sb.append(Character.toLowerCase(c));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    private static String getTableName(Object obj) {
        Type type = obj.getClass().getGenericSuperclass();
        String typeName = type.getTypeName();
        String preName = typeName.substring(typeName.lastIndexOf(".") + 1);
        String first = preName.substring(0, 1);
        return camelToUnderline(preName.replaceFirst(first, first.toLowerCase()));
    }
}

使用说明

1. 需要引入spring-jdbc
2. 需要引入alibaba-fastjson