java中select查询结果和insert导出语句实现

1. 模仿select语句,前端输入条件，返回前端查询结果

//mybatis Mapper接口
	/**
	 * 模仿 select * from tableName where id=? group by id  来获取
	 * @param tableName
	 * @param fields
	 * @param condition
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	@Select("select ${fields} from ${tableName} where ${condition}")
	public List<Map<?, ?>> findTableDataList(@Param("tableName")String tableName, @Param("fields")String fields,
										@Param("condition")String condition, @Param("pageNum")Integer pageNum, @Param("pageSize")Integer pageSize);

	@Data
	public class FieldEntity{
		 @NotBlank(message = "fields不能为空")
		private String fields;
		 @NotBlank(message = "tableName不能为空")
		private String tableName;
		 @NotBlank(message = "condition不能为空")
		private String condition;
	}

	@Slf4j
	@Validated
	@RestController
	@RequestMapping("/sql")
	public class TableDataController{
		@Autowired
	    private TableDataService tableDataService ;
		@PostMapping("/findTableDataList")
	    public List<Map<?, ?>> findTableDataList(@RequestBody  @Validated FieldEntity fieldEntity) {
			return tableDataService .findTableDataList(fieldEntity);
		}
	}

2. 导出insert语句封装

Controller

/**
 * SqlController
 *
 * @author liuxb
 * @date 2022/3/31 17:19
 */
@Slf4j
@Validated
@RestController
@RequestMapping("/sql")
public class SqlController {
    @Autowired
    private RewardEmpMapper rewardEmpMapper;

    /**
     * 生成insert语句，返回前端字符串，前端可以自行保存到文件，模拟下载
     * @return
     */
    @PostMapping("/insertSql")
    public String insertSql() {
        List<RewardEmp> rewardEmpList = rewardEmpMapper.findAll(new RewardEmp());
        List<RewardEmpSql> rewardEmpSqlList = rewardEmpList.stream().map(x -> {
            RewardEmpSql rewardEmpSql = new RewardEmpSql();
            BeanUtils.copyProperties(x, rewardEmpSql);
            return rewardEmpSql;
        }).collect(Collectors.toList());


        StringBuilder sb = new StringBuilder();
        for (RewardEmpSql rewardEmpSql : rewardEmpSqlList) {
            sb.append(SqlUtil.getInsertSql("luck_reward_emp", rewardEmpSql)).append("\n");
        }
        return sb.toString();
    }
}

Mapper接口

@Select("select * from luck_reward_emp")
List<RewardEmp> findAll(RewardEmp rewardEmp);

实体类

import lombok.Data;

import java.util.Date;

/**
 * 中奖员工
 *
 * @author liuxb
 * @date 2022/02/12 18:42
 */
@Data
public class RewardEmpSql {
    /**
     * id
     */
    private Integer id;
    /**
     * 活动id
     */
    private String activityId;
    /**
     * 奖品名
     */
    private String rewardName;
    /**
     * 奖品等级
     */
    private Integer rewardLevel;
    /**
     * 员工名
     */
    private String empName;
    /**
     * 员工编号
     */
    private String sapId;
    /**
     * 创建时间
     */
    private Date createTime;
    /**
     * 修改时间
     */
    private Date updateTime;
}

sql

CREATE TABLE `luck_reward_emp` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `activity_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '活动id',
  `reward_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '奖品名',
  `reward_level` int DEFAULT NULL COMMENT '奖品等级',
  `emp_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '员工名',
  `sap_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '员工编号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='中奖员工表';

SqlUtil

package com.yl.file.util;

import com.cmbchina.base.exception.BizException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.FastDateFormat;

import java.lang.reflect.Field;
import java.util.Objects;

/**
 * SqlUtil
 *
 * @author liuxb
 * @date 2022/3/31 16:53
 */
@Slf4j
public class SqlUtil {
    private static final String insert_label = "INSERT INTO %s(";
    private static final String values_label = "VALUES(";
    private static final String end_label = ");";

    public static String getInsertSql(String tableName, Object object){
        StringBuilder sb = new StringBuilder();
        sb.append(String.format(insert_label, tableName));
        Class<?> aClass = object.getClass();

        Field[] fields = aClass.getDeclaredFields();
        try {
            for (int i = 0; i < fields.length; i++) {
                sb.append(humpToLine(fields[i].getName())).append(",");
            }
            sb.deleteCharAt(sb.length()-1).append(") ").append(values_label);

            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                String simpleName = fields[i].getType().getSimpleName();
                log.info("simpleName:{}", simpleName);
                switch (simpleName){
                    case "int":
                    case "Integer":
                    case "long":
                    case "Long":
                    case "double":
                    case "Double":
                    case "float":
                    case "Float":
                        sb.append(fields[i].get(object)).append(",");
                        break;
                    case "Date":
                        sb.append(Objects.isNull(fields[i].get(object)) ? null : "'" + FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss").format(fields[i].get(object)) + "'")
                                .append(",");
                        break;
                    default:
                        sb.append(Objects.isNull(fields[i].get(object)) ? null : "'" + fields[i].get(object) + "'").append(",");
                        break;
                }

            }
            sb.deleteCharAt(sb.length()-1);
        } catch (IllegalAccessException e) {
            throw new BizException("生成sql异常", e);
        }
        sb.append(end_label);
        return sb.toString();
    }
    /**
     * 小驼峰命名转下划线命名
     *
     * @param str 小驼峰命名的变量
     * @return
     */
    public static String humpToLine(String str) {
        return str.replaceAll("[A-Z]", "_$0").toLowerCase();
    }
}