1.整合增删改查集合类

import org.springframework.beans.factory.annotation.Autowired;
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 org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class MysqlMake {

    private  String myTable;
    private  String myPrefix="ly_";
    private  String mySqlStr="";
    private  String myFiled="*";
    
    @Autowired
    JdbcTemplate jdbcTemplate;

    public MysqlMake table(String table){
        myTable = myPrefix+table;
        return this;
    }
    public MysqlMake field(String field){
        myFiled = field;
        return this;
    }
    // where 条件判断
    public MysqlMake where(String where){
        if(where != null){
            int intIndex = mySqlStr.indexOf("where");
            if(intIndex == - 1){
                mySqlStr = mySqlStr + " where "+where;
            }else{
                mySqlStr = mySqlStr + " "+where;
            }

        }

        return this;
    }
    public MysqlMake where(String key,String value){
        if (key != null && value != null) {
              int intIndex = mySqlStr.indexOf("where");
              if (intIndex == -1) {
                mySqlStr = mySqlStr + " where " + key + "=\"" + value + "\" ";
              } else {
                mySqlStr = mySqlStr + " and " + key + "=\"" + value + "\" ";
              }
        }
        return this;
    }
    public MysqlMake where(String key,String eq,String value){
        if (key != null && eq != null && value != null) {
          int intIndex = mySqlStr.indexOf("where");
          if (intIndex == -1) {
            mySqlStr = mySqlStr + " where " + key + eq + "\"" + value + "\" ";
          } else {
            mySqlStr = mySqlStr + " and " + key + eq + "\"" + value + "\" ";
          }
        }
        return this;
    }
    // Where IN in查询
    public MysqlMake whereIn(String key,String value){
        if(key != null && value != null){
            int intIndex = mySqlStr.indexOf("where");
            if (intIndex == -1) {
                mySqlStr = mySqlStr + " where " + key + " in ("+value+")";
            }else{
                mySqlStr = mySqlStr + " and " + key + " in ("+value+")";
            }
        }
        return this;
    }
    // whereBetween Between查询
    public MysqlMake whereBetween(String key,String start,String end){
        if (key != null && start != null && end != null) {
            int intIndex = mySqlStr.indexOf("where");
            if (intIndex == -1) {
                mySqlStr = mySqlStr + " where " + key + " between " + start + " and " + end;
            }else{
                mySqlStr = mySqlStr + " and " + key + " between " + start + " and " + end;
            }
        }
        return this;
    }
    // 链表查询 左链表
    public MysqlMake leftJoin(String table,String str){
        if(table != null && str != null ){
            mySqlStr = mySqlStr + " left join "+myPrefix+table+" on "+str;
        }
        return this;
    }
    // 排序
    public MysqlMake orderBy(String order,String sort){
        mySqlStr = mySqlStr + " order by "+order+" "+sort+" ";
        return this;
    }

    //查询数量
    public String count(){
        String sql = "select count(" + myFiled + ") from " + myTable + mySqlStr;
        cleanInit();
        return sql;
    }
    // 分页查询总条数
    public Integer pageTotalCount(){
        String sql2 = "select count(*) from " + myTable + mySqlStr;
        try {
            // 查询总数
            int total= jdbcTemplate.queryForObject(sql2,new Object[]{},Integer.class);
            return total;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
    // 分页查询
    public HashMap<String, Object> page(Integer pageNum , Integer pageSize){
        // 总条数
        Integer total = pageTotalCount();
        System.out.println(total);
        // 构造查询语句
        Integer startNum = pageNum>0 ? (pageNum - 1) : 0;
        Integer pageStart = startNum * pageSize;//提取分页开始索引
        String sql = "select "+ myFiled + " from "+ myTable + mySqlStr + " limit "+pageStart+","+pageSize+" ";
        System.out.println(sql);
        // 置空
        cleanInit();
        try {
            HashMap<String, Object> result = new HashMap<String, Object>();
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            if(list != null && list.isEmpty() == false){
                // 总页数
                Integer pages = (int) Math.ceil(total/pageSize);
                result.put("data",list);
                result.put("pages",pages);
                result.put("last_page",pages);
                result.put("total",total);
                System.out.println(result);
                return result;
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    // 列表查询
    public List<Map<String, Object>> select(){
        String sql =  "select " + myFiled + " from " + myTable + mySqlStr;
        cleanInit();
        try {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            if(list != null && !list.isEmpty()){
                return list;
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    // 单条查询
    public Map<String, Object> find(){
        // sql语句
        String sql =  "select " + myFiled + " from " + myTable + mySqlStr+ " limit 0,1 ";
        System.out.println(sql);
        cleanInit();
        try {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            if(list != null && !list.isEmpty()){
                Map<String, Object> info = list.get(0);
                return info;
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    // 单条单字段查询
    public String findOne(){
        // sql语句
        String sql =  "select " + myFiled + " from " + myTable + mySqlStr+ " limit 0,1 ";
        cleanInit();
        try {
            String info = jdbcTemplate.queryForObject(sql,new Object[]{},String.class);
            return info;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    // 插入数据
    public Integer insert(Map<String, Object> dataMap){
        if(dataMap != null && !dataMap.isEmpty()){
            // 拼接插入参数
            String keyStr = "";
            String valueStr = "";
            for(Map.Entry<String, Object> entry : dataMap.entrySet()){
                String mapKey = entry.getKey();
                String mapValue = entry.getValue().toString();
                keyStr = keyStr + mapKey + ",";
                valueStr = valueStr + "\"" + mapValue + "\",";
            }
            // 删除拼接字符串最后的逗号
            keyStr = keyStr.substring(0,keyStr.length() - 1);
            valueStr = valueStr.substring(0,valueStr.length() - 1);
            // 插入sql 语句
            String sql =  "insert " + myTable + " ("+keyStr+") values ("+valueStr+")";
            cleanInit();
            try {
                // 插入数据后 获取自增ID
                KeyHolder keyHolder = new GeneratedKeyHolder();
                PreparedStatementCreator preparedStatementCreator = con -> {
                    PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    return ps;
                };
                jdbcTemplate.update(preparedStatementCreator,keyHolder);
                // 获取自增ID
                return keyHolder.getKey().intValue();
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
        return 0;
    }
    // 删除数据
    public Integer delete(){
        // 更新sql语句
        String sql =  "delete from " + myTable + mySqlStr;
        cleanInit();
        try {
            // 可以成功更新数据库
            Integer info = jdbcTemplate.update(sql);
            return info;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
    // 更新数据 单个字段更新
    public Integer updateOne(String key,String value){
        // 更新sql语句
        String sql =  "update " + myTable + " SET " + key + "="+ value + mySqlStr;
        cleanInit();
        try {
            // 可以成功更新数据库
            Integer info = jdbcTemplate.update(sql);
            return info;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    // 更新数据 整条数据更新
    public Integer update(Map<String, String> dataMap){
        if(dataMap !=null && !dataMap.isEmpty()){
            // 拼接更新参数
            String sqlStr = null;
            for(Map.Entry<String, String> entry : dataMap.entrySet()){
                String mapKey = entry.getKey();
                String mapValue = entry.getValue();
                sqlStr = sqlStr + mapKey + "=\"" + mapValue + "\",";
            }
            // 更新sql语句
            String sql =  "update " + myTable + " SET " + sqlStr + mySqlStr;
            cleanInit();
            try {
                // 执行
                Integer info = jdbcTemplate.update(sql);
                return info;
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
        return 0;
    }
    // 字段数据自增
    public Integer increment(String key,String value){
        // 更新sql语句
        String sql =  "update " + myTable + " SET "+key+"="+key+"+"+value+mySqlStr;
        System.out.println(sql);
        cleanInit();
        try {
            // 执行
            Integer info = jdbcTemplate.update(sql);
            return info;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
    // 字段数据自减
    public Integer decrement(String key,String value){
        // 更新sql语句
        String sql =  "update " + myTable + " SET "+key+"="+key+"-"+value+mySqlStr;
        cleanInit();
        try {
            // 执行
            Integer info = jdbcTemplate.update(sql);
            return info;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
    // 置空还原数据
    public Boolean cleanInit(){
        myTable = "";
        myFiled = "*";
        mySqlStr = "";
        return true;
    }
}

2.使用

// 引入
@Resource
MysqlMake mysqlMake;

// 查询单个字段
String info = mysqlMake.table("account").field("money").where("account_id" , "1").findOne();

// 单条数据查询
Map<String, Object> info = mysqlMake.table("account").where("account_id" , "1").find();

// 分页查询
Map<String, Object> list  = mysqlMake.table("account_money_change as a")
        .field("a.money,a.created_at,n.nfc_sn,n.type")
        .leftJoin("account as ac", "ac.id=a.account_id")
        .leftJoin("account as at", "at.id=a.to_account_id")
        .leftJoin("user_nfc as n", "n.id=at.account_id")
        .where("a.consume_type","13")
        .whereIn("a.account_id",type)
        .whereBetween("a.created_at",startTime,endTime)
        .orderBy("a.id","desc")
        .page(pageNum,pageSize);
if(list !=null){
    // 修改数据
    List<HashMap<String, Object>> lista = (List<HashMap<String, Object>>) list.get("data");
    for (Map<String, Object> info : lista) {
        String newTime = DateUtil.timeStamp2Date(info.get("created_at").toString(), "yyyy-MM-dd");
        info.put("created_at",newTime);
    }
}

// 数据写入
String nowTime = DateUtil.timeStamp();
// 构造写入参数
Map<String, Object> info = new HashMap<String, Object>();
info.put("consume_type",consume_type);
info.put("name",getMoneyChangeName(consume_type.toString()));
info.put("account_id",account_id);
info.put("money",-money);
info.put("to_account_id",to_account_id);
info.put("order_id",order_id);
info.put("created_at",nowTime);
info.put("updated_at",nowTime);
// 写入数据 并获取ID
Integer account_change_id = mysqlMake.table("account_money_change").insert(info);


// 数据更新
// 出账后金额减少
mysqlMake.table("account").where("id",account_id.toString()).decrement("money",money.toString());
// 出账后更新出账后的金额
mysqlMake.table("account_money_change").where("id",account_change_id.toString()).updateOne("money_after",money_after.toString());