导数据sql片段
根据创建表语句,生成以注释为列名的查询语句
import cn.hutool.core.util.ReUtil;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
public class SqlTest {
@Test
public void t() {
String sql = "CREATE TABLE `t_payment_plan` (\n" +
" `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',\n" +
" `tenant_id` int NOT NULL COMMENT '企业ID',\n" +
" PRIMARY KEY (`id`) USING BTREE\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='付款计划表';";
Pattern columnPtn = Pattern.compile("`(.*)`");
Pattern commentPtn = Pattern.compile("COMMENT ('.*')");
List<String> list = Arrays.stream(sql.split("\n")).collect(Collectors.toList());
String tableName = ReUtil.get(columnPtn, list.get(0), 1);
// 掐头去尾
list.remove(0);
list.remove(list.size() - 1);
List<String> sqlFragment = new ArrayList<>();
for (String s : list) {
if (s.trim().startsWith("`")) {
String col = ReUtil.get(columnPtn, s, 1);
String comment = ReUtil.get(commentPtn, s, 1);
sqlFragment.add(col + " AS " + comment);
}
}
String fullSql = "SELECT \n" + String.join(",\n", sqlFragment) + "\nFROM " + tableName;
System.out.println(fullSql);
}
}