前言
公司的测试和运维不太懂sql，每次将sql文件交给他们到测试或者生产数据库中执行sql的时候，他们都是全部执行，不看是否执行成功。因此，就会导致有些insert语句没有执行成功，等到功能验证的时候就会出问题，到时候又得是开发一步步寻找bug，最后找到数据库中发现数据缺失……
因此，我写了一个接口，将sql文件中的insert语句进行扫描，转换成select语句进行查询，如果查询得到就说明insert语句执行成功，查询不到说明执行失败。
注意：
项目开发的时候，很少会对数据进行删除，所以很少会运用到delete语句。而update语句的话，我们小组规定，为了能够成功将数据存入到数据库中，会将update语句转化为insert语句，如下：
比如我想把id=1，name=张三的数据，将他的age改为19。
insert into student (id,name,age) values (1,'张三',19) on duplicate key update age = 19;
上面这条语句的意思是将（id=1，name=张三，age=19）的数据插入到student表中，如果有字段冲突（主键冲突或者唯一索引冲突），就会将原来的数据的age修改为19。
假如我数据库中已经有（1，‘张三’，18）的数据，当我执行上面的sql时，由于id主键的冲突，就会无法进行数据的插入，反而是将原来的数据的age=18改为19。
思路
总共分六步：
1、扫描文件，获得insert语句；
2、扫描insert语句，获取表名和字段；
3、扫描insert语句，获取字段值；
4、根据2、3步获取的表名、字段和字段值，组合成一个map；
5、根据上一步得到的map，拼接key和value，得到select语句，并执行；
6、若查询成功，则insert语句执行成功；若查询失败，则说明insert语句执行失败。
此外，由于我们要自己拼接select语句，就不能像使用mybatis一样将sql写在xml文件中，这样子无法动态的拼接sql。因此，我们使用spring的JdbcTemple来查询数据库。
在yml写入数据库连接信息。
spring:
application:
name: test-module
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/production_center?serverTimezone=UTC
username: root
password: 123456
代码中直接注入JdbcTemplate。
@Autowired
private JdbcTemplate jdbcTemplate;
代码
从文件中扫描得到insert的sql语句
/**
* 从文件中扫描得到insert的sql语句
* @return
*/
public List<String> getSql(MultipartFile file) {
try {
InputStream stream = file.getInputStream();
// 字节流转换成字符流
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(stream));
StringBuilder str = new StringBuilder();
String line = null;
List<String> stringList = new ArrayList<>();
//按行读取
while ((line = bufferedReader.readLine()) != null) {
// 获取insert语句
if (line.startsWith("INSERT") || line.startsWith("insert")) {
while (line != null) {
if (line.endsWith(";")) {
str.append(line);
stringList.add(str.toString());
str.delete(0, str.length());
break;
}
if (line == null) {
continue;
}
str.append(line);
line = bufferedReader.readLine();
}
}
}
bufferedReader.close();
return stringList;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
获取insert语句中的key值
/**
* 获取insert语句中的key值
* @param str
* @return
*/
public List<String> getKeys(String str) {
ArrayList<String> key = new ArrayList<>();
String regex = "`(\\w+)`(?!\\.)";
Pattern compile = Pattern.compile(regex);
Matcher matcher = compile.matcher(str);
while (matcher.find()) {
key.add(matcher.group(1).toString()); //group(1):表示第一个（）里的内容
}
// 如果表名和字段名没有反单引号，采取下列措施
if (CollectionUtils.isEmpty(key)) {
Pattern compileTable = Pattern.compile("(to|TO) (\\w+)");
Matcher matcherTable = compileTable.matcher(str);
if (matcherTable.find()) {
key.add(matcherTable.group(2).toString());
}
Pattern compile1 = Pattern.compile("(\\()(.+)(\\))( ?)(V|v)");
Matcher matcher1 = compile1.matcher(str);
while (matcher1.find()) {
String s = matcher1.group(2).toString();
Arrays.stream(s.replace(" ", "").split(",")).forEach(item -> key.add(item));
}
}
return key;
}
获取insert语句中的value值
/**
* 获取insert语句中的value值
* @param str
* @return
*/
public List<String> getValues(String str) {
//获得字段值
String string = "";
if ("".equals(string)) {
String regex2 = "(values ?|VALUES ?)(\\()(.+)(\\))";
Pattern compile2 = Pattern.compile(regex2);
Matcher matcher2 = compile2.matcher(str);
while (matcher2.find()) {
string = matcher2.group(3).toString();
}
}
List<String> list = new ArrayList<>();
log.info("str的长度={}",string.length());
String concatStr = "";
for (int i = 0; i < string.length(); i++) {
char c = string.charAt(i);
// 针对括号里的内容进行获取
if(string.charAt(i) == '(') {
int num = 0;
for (int j = i; j < string.length(); j++) {
if (string.charAt(j) == '(') {
num++;
}
if (string.charAt(j) == ')') {
concatStr += string.charAt(j);
num --;
if (num == 0) {
i = j+1;
break;
}
continue;
}
concatStr += string.charAt(j);
}
}
// 针对单引号里的内容进行获取
if (c == '\'' || c == '\"') {
if (string.charAt(i+1) == '\'' || string.charAt(i+1) == '\"') {
i++;
continue;
}
for (int j = i+1; j < string.length(); j++) {
char charAt = string.charAt(j);
if (charAt == '\'' || charAt == '\"') {
i = j+1;
break;
}
concatStr += charAt;
}
}
if(i >= string.length()){
break;
}
if (i == string.length()-1) {
if (string.charAt(i) == '\'' || c == '\"') {
break;
}
else {
concatStr += c;
break;
}
}
if (string.charAt(i) == ',') {
list.add(concatStr);
concatStr = "";
continue;
}
if (c == ' '){
continue;
}
concatStr += string.charAt(i);
}
list.add(concatStr);
return list;
}
得到一个key为字段value为参数值的map
/**
* 得到一个key为字段value为参数值的map
*
* @param sql
* @return
*/
public HashMap<String, Object> getInsertMap(String sql) {
//获得字段
List<String> key = getKeys(sql);
log.info("key = {}", JSON.toJSONString(key));
//获得字段值
List<String> value = getValues(sql);
log.info("value={}", JSON.toJSONString(value));
//组合成一个map
HashMap<String, Object> map = new HashMap<>();
try {
for (int i = 0; i < value.size(); i++) {
if (i == 0) {
// 将表名放入map中
map.put("table", key.get(i));
continue;
}
map.put(key.get(i + 1), value.get(i));
}
} catch (Exception e) {
e.printStackTrace();
}
// 去除公式生成的time或其它随机生成的id等信息
map.remove("param_id");
map.remove("create_time");
map.remove("modify_time");
ArrayList<String> delete = new ArrayList<>();
// 去除字段值为NULL和null还有为空的字段
for (String strKey : map.keySet()) {
if (map.get(strKey).equals("NULL") || map.get(strKey).equals("") || map.get(strKey).equals("null")) {
delete.add(strKey);
}
}
//删除值为空或为Null或为随机数的字段和值
delete.stream().forEach(item -> map.remove(item));
log.info("map={}", JSON.toJSONString(map));
return map;
}
拼接成select语句
/**
* 拼接sql语句
*
* @param map
* @return
*/
public Map<String, Object> splitSql(HashMap<String, Object> map) {
String table = (String) map.get("table");
map.remove("table");
StringBuilder builder = new StringBuilder();
for (String key : map.keySet()) {
builder.append(key + " = " + "'" + map.get(key) + "'" + " and ");
}
// 去除最后的”and“
builder.delete(builder.length() - 4, builder.length());
String sql = "select * from " + table + " where " + builder;
log.info("拼接的sql为 = {}", sql);
try {
// 执行sql
Map<String, Object> queryForMap = jdbcTemplate.queryForMap(sql);
log.info("通过jdbcTemplate得到map={}", JSON.toJSONString(queryForMap));
return queryForMap;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
在接口中进行校验
/**
* 扫描文件，将insert语句转化为select语句，查询数据库进行验证
* @param sqlFile
* @return
*/
@RequestMapping("/transformToSelect")
public List<String> transformToSelect(@RequestPart("sqlFile") MultipartFile sqlFile) {
List<String> list = new ArrayList<>();
HashMap<Object, Object> map = new HashMap<>();
//从文件中获取insert语句
List<String> sqls = upLoadService.getSql(sqlFile);
for (int i = 0; i < sqls.size(); i++) {
// 获得字段和值的map
HashMap<String, Object> insertMap = upLoadService.getInsertMap(sqls.get(i));
// 得到拼接好的select语句并查询
Map<String, Object> sqlMap = upLoadService.splitSql(insertMap);
if (MapUtil.isEmpty(sqlMap)) {
log.info("出现问题的sql={}",sqls.get(i));
list.add(sqls.get(i));
continue;
}
}
return list;
}
改进
以上，我们使用的是spring的JdbcTemplate进行数据库的查询，虽然能够动态的查询数据库，但假如我一份sql文件是查询数据库A，但另一份sql文件查询的是数据库B，这样的话我换份文件查询，还得到yml中再修改一下数据库的连接信息，然后重新构建项目，比较麻烦。
因此，如果能够动态的连接不同的数据库，在导入不同sql文件之前，我只需要填写不同的数据库连接信息，就能切换到相应的数据库连接了。
解决方法
@RestController
@Slf4j
public class CzfDataSource {
public JdbcTemplate jdbcTemplate;
@RequestMapping("/jdbcTemplate")
public void connectDataSource(String url, String driverClassName, String userName, String password) {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setUrl(url);
dataSource.setDriverClassName(driverClassName);
dataSource.setUsername(userName);
dataSource.setPassword(password);
jdbcTemplate = new JdbcTemplate(dataSource);
}
}
调用这个接口，我们就能将项目中使用的jdbcTemplate转成我们填写的那个数据库连接了。
当然，代码中也要相应的进行更改。之前导入的jdbcTemplate要换成我们重新编写的jdbcTemplate。
// @Autowired
// private JdbcTemplate jdbcTemplate;
@Autowired
private CzfDataSource dataSource;
然后代码中使用到的jdbcTemplate改成如下：
//Map<String, Object> queryForMap = jdbcTemplate.queryForMap(sql);
Map<String, Object> queryForMap = dataSource.jdbcTemplate.queryForMap(sql);
3600
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
