基于jdbcTemplate 自动sql 生成工具

package com.lining.omni.service;

import com.lining.omni.entity.DcTableEntity;
import com.lining.omni.util.FormatTypeUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author: chenyuanjie
 * @Date: 2022.1.19
 * @Description:
 */
@Service
@Slf4j
public class DongChaService implements InitializingBean {

    private JdbcTemplate jdbcTemplate = null;

    // 因为在service层所以依赖这个的参数都需要添加这个配置文件 所以这个配置文件先写死
    @Override
    public void afterPropertiesSet() throws Exception {
        Properties properties = new Properties();
        // 使用ClassLoader加载properties配置文件生成对应的输入流
        InputStream in = DongChaService.class.getClassLoader().getResourceAsStream("config/dcData.properties");
        // 使用properties对象加载输入流
        properties.load(in);
        //获取key对应的value值
        String url = properties.getProperty("dcdata.url");
        String username = properties.getProperty("dcdata.username");
        String password = properties.getProperty("dcdata.password");
        String driverClassName = properties.getProperty("dcdata.driverClassName");

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        jdbcTemplate = new JdbcTemplate(dataSource);
    }




    public boolean delete(String table, Map<String, Object> map) {
        if (ObjectUtils.isEmpty(table) || ObjectUtils.isEmpty(map)){
            return false;
        }
        List<DcTableEntity> query = getDcTableEntities(table);
        StringBuffer stringBuffer = new StringBuffer();
        Boolean flag = false;
        stringBuffer.append("delete from ").append(table).append(" where ");
        flag = getaBoolean(map, query, stringBuffer, flag);
        if (flag){
            stringBuffer.append(" 1=1 ");
            String deleteSql = stringBuffer.toString();
            log.info(" deleteSql:{} "+deleteSql);
            jdbcTemplate.update(deleteSql);
            return true;
        }else {
            return false;
        }
    }

    private Boolean getaBoolean(Map<String, Object> map, List<DcTableEntity> query, StringBuffer stringBuffer, Boolean flag) {
        for (Map.Entry<String,Object> entry:map.entrySet()){
            for (DcTableEntity dcTableEntity:query){
                if (dcTableEntity.getColumnName().equals(entry.getKey())){
                    switch (FormatTypeUtils.formatType(dcTableEntity.getDataType())){
                        case "String":
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String value = entry.getValue().toString();
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '"+value+"' and ");
                                flag = true;
                            }else {
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '' and ");
                                flag = true;
                            }
                            break;
                        case "Integer":
                            int value = Integer.parseInt("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(dcTableEntity.getColumnName()+" = '"+value+"' and ");
                            flag = true;
                            break;
                        case "Double":
                            double doubleValue = Double.parseDouble("".equals(entry.getValue().toString()) ? "0.00" : entry.getValue().toString());
                            stringBuffer.append(dcTableEntity.getColumnName()+" = '"+doubleValue+"' and ");
                            flag = true;
                            break;
                        case "Long":
                            Long longValue = Long.parseLong("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(dcTableEntity.getColumnName()+" = '"+longValue+"' and ");
                            flag = true;
                            break;
                        default:
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String valueDef = entry.getValue().toString();
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '"+valueDef+"' and ");
                                flag = true;
                            }else{
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '' and ");
                            }
                            break;
                    }
                }
            }
        }
        return flag;
    }

    private List<DcTableEntity> getDcTableEntities(String table) {
        // 查询表结构
        String sql = "select * from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and TABLE_NAME= '"+table+"'";
        return jdbcTemplate.query(sql, new RowMapper<DcTableEntity>() {
            @Override
            public DcTableEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                DcTableEntity dcTableEntity = new DcTableEntity();
                // 字段名
                dcTableEntity.setColumnName(resultSet.getString("COLUMN_NAME"));
                // 字段类型
                dcTableEntity.setDataType(resultSet.getString("DATA_TYPE"));
                // 是否主键
                dcTableEntity.setColumnKey(resultSet.getString("COLUMN_KEY"));
                return dcTableEntity;
            }
        });
    }

    public boolean insert(String table, Map<String, Object> map) {
        if (ObjectUtils.isEmpty(table) || ObjectUtils.isEmpty(map)){
            return false;
        }
        List<DcTableEntity> query = getDcTableEntities(table);
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("insert into "+table+" (");
        for (Map.Entry<String,Object> entry:map.entrySet()){
            stringBuffer.append(entry.getKey()).append(",");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(" ) ").append("  value ( ");
        for (Map.Entry<String,Object> entry:map.entrySet()) {
            for (DcTableEntity dcTableEntity : query) {
                if (dcTableEntity.getColumnName().equals(entry.getKey())) {
                    switch (FormatTypeUtils.formatType(dcTableEntity.getDataType())){
                        case "String":
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String value = entry.getValue().toString();
                                stringBuffer.append("'");
                                stringBuffer.append(value);
                                stringBuffer.append("', ");
                            }else{
                                stringBuffer.append("'");
                                stringBuffer.append("', ");
                            }
                            break;
                        case "Integer":
                            int valueInteger = Integer.parseInt("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(valueInteger);
                            stringBuffer.append(", ");
                            break;
                        case "Double":
                            double valueD = Double.parseDouble("".equals(entry.getValue().toString()) ? "0.00" : entry.getValue().toString());
                            stringBuffer.append(valueD);
                            stringBuffer.append(", ");
                            break;
                        case "Long":
                            Long valueL = Long.parseLong("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(valueL);
                            stringBuffer.append(", ");
                            break;
                        default:
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String valueS = entry.getValue().toString();
                                stringBuffer.append("'");
                                stringBuffer.append(valueS);
                                stringBuffer.append("', ");
                            }else{
                                if (entry.getKey().equals("exec_time")){
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    String format = simpleDateFormat.format(new Date());
                                    stringBuffer.append("'");
                                    stringBuffer.append(format);
                                    stringBuffer.append("', ");
                                }else {
                                    stringBuffer.append("'");
                                    stringBuffer.append("', ");
                                }
                            }
                            break;
                    }
                }
            }
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-2);
        stringBuffer.append(" ) ");
        String insertSql = stringBuffer.toString();
        log.info("insertSql :{}",insertSql);
        jdbcTemplate.update(insertSql);
        return true;
    }


    public List<Map<String, Object>> select(List<String> columnNames, String table, Map<String, Object> map) {
        if (ObjectUtils.isEmpty(table) || ObjectUtils.isEmpty(map)){
            return new ArrayList();
        }
        List<DcTableEntity> query = getDcTableEntities(table);
        StringBuffer stringBuffer = new StringBuffer();
        String str = "";
        for (String name : columnNames){
            str = str+name +",";
        }
        String substring = str.substring(0, str.length() - 1);
        stringBuffer.append("select "+substring+" from ").append(table).append(" where ");
        Boolean flag = false;
        flag = getaBoolean(map, query, stringBuffer, flag);
        if (flag){
            stringBuffer.append(" 1=1 ");
            String selectSql = stringBuffer.toString();
            log.info(" selectSql:{} ",selectSql);
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectSql);
            return maps;
        }else {
            return new ArrayList();
        }
    }

    public boolean update(String table, Map<String, Object> map) {
        if (ObjectUtils.isEmpty(table) || ObjectUtils.isEmpty(map)){
            return false;
        }
        List<DcTableEntity> query = getDcTableEntities(table);
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" update "+table+" set ");
        for (Map.Entry<String,Object> entry:map.entrySet()) {
            for (DcTableEntity dcTableEntity : query) {
                if (dcTableEntity.getColumnName().equals(entry.getKey())) {
                    switch (FormatTypeUtils.formatType(dcTableEntity.getDataType())){
                        case "String":
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String value = entry.getValue().toString();
                                stringBuffer.append(entry.getKey()).append(" = ").append("'").append(value).append("'").append(",");
                            }else{
                                stringBuffer.append(entry.getKey()).append(" = ").append("''").append(",");
                            }
                            break;
                        case "Integer":
                            int valuei = Integer.parseInt("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(entry.getKey()).append(" = ").append(valuei).append(",");
                            break;
                        case "Double":
                            double valued = Double.parseDouble("".equals(entry.getValue().toString()) ? "0.00" : entry.getValue().toString());
                            stringBuffer.append(entry.getKey()).append(" = ").append(valued).append(",");
                            break;
                        case "Long":
                            Long valueL = Long.parseLong("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(entry.getKey()).append(" = ").append(valueL).append(",");
                            break;
                        default:
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String valueV = entry.getValue().toString();
                                stringBuffer.append(entry.getKey()).append(" = ").append("'").append(valueV).append("'").append(",");
                            }else {
                                if (entry.getKey().equals("exec_time")){
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    String format = simpleDateFormat.format(new Date());
                                    stringBuffer.append(entry.getKey()).append(" = ").append("'"+format+"'").append(",");
                                }else {
                                    stringBuffer.append(entry.getKey()).append(" = ").append("''").append(",");
                                }
                            }
                            break;
                    }
                }
            }
        }
        stringBuffer.deleteCharAt(stringBuffer.length() - 1);
        stringBuffer.append(" where  ");
        Boolean flag = false;
        for (Map.Entry<String,Object> entry:map.entrySet()) {
            for (DcTableEntity dcTableEntity : query) {
                if (dcTableEntity.getColumnName().equals(entry.getKey()) && "PRI".equalsIgnoreCase(dcTableEntity.getColumnKey())) {
                    switch (FormatTypeUtils.formatType(dcTableEntity.getDataType())){
                        case "String":
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String value = entry.getValue().toString();
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '"+value+"' and ");
                                flag = true;
                            }else {
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '' and ");
                                flag = true;
                            }
                            break;
                        case "Integer":
                            int value = Integer.parseInt("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(dcTableEntity.getColumnName()+" = '"+value+"' and ");
                            flag = true;
                            break;
                        case "Double":
                            double valueDouble = Double.parseDouble("".equals(entry.getValue().toString()) ? "0.00" : entry.getValue().toString());
                            stringBuffer.append(dcTableEntity.getColumnName()+" = '"+valueDouble+"' and ");
                            flag = true;
                            break;
                        case "Long":
                            Long valueLong = Long.parseLong("".equals(entry.getValue().toString()) ? "0" : entry.getValue().toString());
                            stringBuffer.append(dcTableEntity.getColumnName()+" = '"+valueLong+"' and ");
                            flag = true;
                            break;
                        default:
                            if (!ObjectUtils.isEmpty(entry.getValue())){
                                String valueDef = entry.getValue().toString();
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '"+valueDef+"' and ");
                                flag = true;
                            }else {
                                stringBuffer.append(dcTableEntity.getColumnName()+" = '' and ");
                                flag = true;
                            }
                            break;
                    }
                }
            }
        }
        if (flag){
            stringBuffer.append(" 1=1 ");
            String updateSql = stringBuffer.toString();
            log.info("updateSql:{}",updateSql);
            jdbcTemplate.update(updateSql);
            return true;
        }else {
            return false;
        }
    }


}

package com.lining.omni.entity;

import lombok.Data;

@Data
public class DcTableEntity {
    private String tableCatalog;
    private String tableSchema;
    private String tableName;
    private String columnName;
    private Long ordinalPosition;
    private String columnDefault;
    private Boolean isNullanle;
    private String dataType;
    private Long characterMaximumLength;
    private Long characterOctetLength;
    private Long numericPrecision;
    private Long numericScale;
    private String datetimePrecisopm;
    private String characterSetName;
    private String collationName;
    private String columnType;
    private String columnKey;
    private String extra;
    private String privileges;
    private String columnComment;
}

package com.lining.omni.util;

public class FormatTypeUtils {
    // 格式转换
    public static String formatType(String type){
        if (type != null) {
            if (type.startsWith("varchar") || type.startsWith("text")) {
                return "String";
            } else if (type.startsWith("tinyint")) {
                return "int";
            } else if (type.startsWith("int") || type.startsWith("smallint")) {
                return "Integer";
            } else if (type.startsWith("bigint")) {
                return "Long";
            } else if (type.startsWith("decimal")) {
                return "Double";
            } else if (type.startsWith("date") || type.startsWith("datetime")) {
                return "Date";
            } else if (type.equalsIgnoreCase("VARCHAR2") || type.equalsIgnoreCase("char")) {
                return "String";
            } else if (type.equalsIgnoreCase("TIMESTAMP(6)") || type.equalsIgnoreCase("timestamp")) {
                return "Timestamp";
            } else if (type.equalsIgnoreCase("XMLTYPE")) {
                return "String";
            } else {
                return "nontype";
            }
        }else{
            return "";
        }
    }
}

可以生成单表的增删改查 ，多表生成可以自己基于这个自己研究。

调用方式：列出一个吧 messageMap 基本上是要和字段名称对应的 可以自己封装方法变成适用驼峰格式。

 if(flag){
            dongChaService.update(TABLE_NAME,messageMap);
        }else {
            dongChaService.insert(TABLE_NAME,messageMap);
        }