JAVA生成SQL语句

需求背景

经常在网上看人吐槽产品经理的奇葩需求，我是乐呵呵地当笑话看，当这种需求落到自己头上之后，才能稍微体会到当事人的无奈心情。
我所在的项目是某手机银行的后台，提供给手机调用的后台接口，比如营销banner、APP上菜单数据、布局数据的下发等，同时也提供相应的后管页面供配置数据。但是业务最近提出，因为运维权限收紧，操作后管的次数受到限制，同时为了避免在后管操作失误导致的数据配置出错，要求所有后管的配置操作都通过提交SQL上线。比如新增了一条菜单、隐藏了首页的某个组件等操作，原本是可以通过后管页面进行的操作现在都要我们通过提交SQL语句来进行。
明明是自己怕麻烦、怕担责，却甩锅给运维，说不让用后管。忍住把水杯甩他脸上的冲动，我也只能一边心里骂娘一边微笑着答应下来，唉，外包就是这样，活是自己干，但是在决策上却说不上话。

需求分析

后管页面的功能落实到数据上，其实也无非是对数据的增删改查，对应到SQL就是CRUD的操作。再落实到业务提出的具体功能上，比如新增菜单就是执行insert操作，修改布局数据就是执行update操作。想到这里，思路就很明显了——在后管页面上增加“导出为insert语句”和“导出为update语句”的功能。
以菜单功能为例，在后管中菜单的列表页上增加导出SQL按钮，鼠标放到哪一行数据上，哪一行就弹出导出为insert语句和导出为update语句的气泡按钮，点击按钮之后调用接口，后台从数据库查询这条数据，然后据此生成insert或update语句。

代码示例

下面以user表为例，演示生成insert语句的代码

数据准备

创建user表，并插入3条数据

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email_address` varchar(100) DEFAULT NULL COMMENT '电子邮箱地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `user`(`id`, `name`, `age`, `email_address`) VALUES (1, 'tom', 10, 'tom.cat@qq.com');
INSERT INTO `user`(`id`, `name`, `age`, `email_address`) VALUES (2, 'jerry', 20, 'jerry.mouse@163.com');
INSERT INTO `user`(`id`, `name`, `age`, `email_address`) VALUES (3, 'Jessica', 15, 'jessica1999@gmail.com');

JAVA代码

由下层往上，依次创建SqlExportMapper 、SqlExportService 、SqlExportController
项目由SpringBoot搭建，持久层框架为MyBatis

@Mapper
public interface SqlExportMapper {
    @Select("SELECT * FROM ${table} WHERE ${where}")
    List<LinkedHashMap<String, Object>> select(@Param("table") String table, @Param("where") String where);
}

@Service
public class SqlExportService {
    @Autowired
    private SqlExportMapper sqlExportMapper;

    public List<LinkedHashMap<String, Object>> select(String table, String where) {
        return sqlExportMapper.select(table, where);
    }

    public String assembleUpdateSQL(Map<String, Object> record, String table, String where, List<String> columnsToUpdate) {
        StringBuilder sb = new StringBuilder("update ").append(table).append(" set ");
        int i = 1;
        for (String column : columnsToUpdate) {
            Object value = record.get(column);
            if (value == null) {
                sb.append(column).append("=").append("null");
            } else if (value instanceof Number) {
                sb.append(column).append("=").append(value);
            } else {
                sb.append(column).append("='").append(value.toString()).append("'");
            }
            if (i < columnsToUpdate.size()) {
                sb.append(", ");
                i++;
            }
        }
        sb.append(" where ").append(where).append(";");
        return sb.toString();
    }

    public String assembleInsertSQL(Map<String, Object> record, String table) {
        StringBuilder sb = new StringBuilder("insert into ").append(table).append(" ");
        sb.append(assembleColumnNames(record)).append("values ").append(assembleColumnValues(record)).append(";");
        return sb.toString();
    }

    private String assembleColumnNames(Map<String, Object> record) {
        StringBuilder sb = new StringBuilder("(");
        int i = 1;
        for (String key : record.keySet()) {
            sb.append(key);
            if (i < record.size()) {
                sb.append(", ");
                i++;
            }
        }
        sb.append(")");
        return sb.toString();
    }

    private String assembleColumnValues(Map<String, Object> record) {
        StringBuilder sb = new StringBuilder("(");
        int i = 1;
        for (Map.Entry<String, Object> entry : record.entrySet()) {
            Object value = entry.getValue();
            if (value == null) {
                sb.append("null");
            } else if (value instanceof Number) {
                sb.append(value);
            } else {
                sb.append("'").append(value.toString()).append("'");
            }
            if (i < record.size()) {
                sb.append(", ");
                i++;
            }
        }
        sb.append(")");
        return sb.toString();
    }
}

@RestController
public class SqlExportController {
    @Autowired
    private SqlExportService sqlExportService;

    @RequestMapping("/generateInsert")
    public String generateInsert(String table, String where) {
        List<LinkedHashMap<String, Object>> records = sqlExportService.select(table, where);
        StringBuilder returnSql = new StringBuilder();
        for (Map<String, Object> record : records) {
            String sql = sqlExportService.assembleInsertSQL(record, table);
            returnSql.append(sql).append("\r\n");
        }
        return returnSql.toString();
    }

    @RequestMapping("/generateUpdate")
    public String generateUpdate(String table, String where,
                                 @RequestParam("columnsToUpdate") List<String> columnsToUpdate) {
        List<LinkedHashMap<String, Object>> records = sqlExportService.select(table, where);
        StringBuilder returnSql = new StringBuilder();
        for (Map<String, Object> record : records) {
            String sql = sqlExportService.assembleUpdateSQL(record, table, where, columnsToUpdate);
            returnSql.append(sql).append("\r\n");
        }
        return returnSql.toString();
    }
}

执行效果

生成insert语句

1、http://localhost:8080/generateInsert?table=user&where=id = 1

2、http://localhost:8080/generateInsert?table=user&where=id in (1,2)

生成update语句

1、http://localhost:8080/generateUpdate?table=user&where=id = 1&columnsToUpdate=name,email_address

2、http://localhost:8080/generateUpdate?table=user&where=id =3&columnsToUpdate=name

代码注意点

Mapper中的select方法需要可以从任意表以任意条件查询数据，所以table和where都是使用${}包围起来的，而不是常用的#{}。
在MyBatis中，#{}表示预编译参数，框架先会生成预编译语句（PreparedStatement），把参数的位置放上问号（?），在执行SQL时，再将问号替换为实际传入的参数。预编译语句一次编译、多次执行，省去了解析优化等繁琐过程，此外预编译语句能防止sql 注入（就是在问号的左右加上了单引号）。${}则是表示字符串拼接，参数传入的是什么，就参数拼接到对应的位置，这就导致SQL每次都需要编译，同时有sql注入的风险。因此在绝大多数情况下，我们都是使用#{}的。

但是在我们的需求中，表名和where条件都是需要动态拼接的，如果使用#{}，SQL写成下面这样子

@Select("SELECT * FROM #{table} WHERE #{where}")
List<LinkedHashMap<String, Object>> select(@Param("table") String table, @Param("where") String where);

预编译的SQL语句就成了下面这样

SELECT * FROM ？ WHERE ？

假设传入的table=“user”，where=“id = 2”，那么实际执行的SQL就是

SELECT * FROM 'user' WHERE 'id=2'

上面最终执行的SQL语句中，由于"user"和"id=2"都被单引号包起来了，导致SQL的语法都错了，最终执行报错。因此在这个需求里，table和where都只能使用${}包裹起来。
除此以外，我们可以使用${}的原因还有这是面向后管页面的接口，只对内网开放，不用担心SQL注入的问题。注意如果是对外网开放的接口，MyBatis的SQL当中尽量不要使用${}拼接参数。