需求分析
后管页面的功能落实到数据上，其实也无非是对数据的增删改查，对应到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当中尽量不要使用${}拼接参数
