本文介绍了如何对 MyBatisPlus 的 AutoSqlInjector 进行改造，以解决字段关键字问题。通过创建自定义文件并修改 selectColumns 方法，实现了对字段和别名的处理。同时，提供了大小写转换方法和处理 SQL 条件别名的辅助方法，以确保在不同数据库中兼容性。此外，还展示了如何从 YAML 配置获取当前使用的数据库类型。
接上文，因为是对已有系统进行改造，所以会存在关键字的问题，但是如果修改字段工作量缺失太大，但是用到mybatisplus自带的方法又无法避免，想到去修改源码文件来实现，下面上内容
mybatisplus的sql注入器为AutoSqlInjector文件位于com.baomidou.mybatisplus.mapper中，那我们在项目根目录下建立相同路径的文件
然后对其生成sql的方法进行改造：
injectInsertOneSql、sqlSelectColumns、sqlSelectObjsColumns 主要是针对其字段和别名，
protected String sqlSelectObjsColumns(TableInfo table) {
StringBuilder columns = new StringBuilder();
/*
* 普通查询
*/
columns.append("<choose><when test=\"ew != null and ew.sqlSelect != null\">${ew.sqlSelect}</when><otherwise>");
// 主键处理
if (StringUtils.isNotEmpty(table.getKeyProperty())) {
if (table.isKeyRelated()) {
columns.append(table.getKeyColumn()).append(" AS ").append(sqlWordConvert(table.getKeyProperty()));
} else {
columns.append(sqlWordConvert(table.getKeyProperty()));
}
} else {
// 表字段处理
List<TableFieldInfo> fieldList = table.getFieldList();
if (CollectionUtils.isNotEmpty(fieldList)) {
TableFieldInfo fieldInfo = fieldList.get(0);
// 匹配转换内容
String wordConvert = sqlWordConvert(fieldInfo.getProperty());
if (fieldInfo.getColumn().equals(wordConvert)) {
columns.append(Tools.convertKeyWordForDb(wordConvert,true,InitService.dialectType));
} else {
// 字段属性不一致
columns.append(Tools.convertKeyWordForDb(fieldInfo.getColumn(),true,InitService.dialectType));
columns.append(" AS ").append(Tools.convertKeyWordForDb(wordConvert,false,InitService.dialectType));
}
}
}
columns.append("</otherwise></choose>");
return columns.toString();
}protected String sqlSelectColumns(TableInfo table, boolean entityWrapper) {
StringBuilder columns = new StringBuilder();
if (null != table.getResultMap()) {
/*
* 存在 resultMap 映射返回
*/
if (entityWrapper) {
columns.append("<choose><when test=\"ew != null and ew.sqlSelect != null\">${ew.sqlSelect}</when><otherwise>");
}
columns.append("*");
if (entityWrapper) {
columns.append("</otherwise></choose>");
}
} else {
/*
* 普通查询
*/
if (entityWrapper) {
columns.append("<choose><when test=\"ew != null and ew.sqlSelect != null\">${ew.sqlSelect}</when><otherwise>");
}
List<TableFieldInfo> fieldList = table.getFieldList();
int size = 0;
if (null != fieldList) {
size = fieldList.size();
}
// 主键处理
if (StringUtils.isNotEmpty(table.getKeyProperty())) {
if (table.isKeyRelated()) {
columns.append(table.getKeyColumn()).append(" AS ").append(sqlWordConvert(table.getKeyProperty()));
} else {
columns.append(sqlWordConvert(table.getKeyProperty()));
}
if (size >= 1) {
// 判断其余字段是否存在
columns.append(",");
}
}
if (size >= 1) {
// 字段处理
int i = 0;
Iterator<TableFieldInfo> iterator = fieldList.iterator();
while (iterator.hasNext()) {
TableFieldInfo fieldInfo = iterator.next();
// 匹配转换内容
String wordConvert = sqlWordConvert(fieldInfo.getProperty());
if (fieldInfo.getColumn().equals(wordConvert)) {
columns.append(Tools.convertKeyWordForDb(wordConvert,true,InitService.dialectType));
} else {
// 字段属性不一致
columns.append(Tools.convertKeyWordForDb(fieldInfo.getColumn(),true,InitService.dialectType));
columns.append(" AS ").append(Tools.convertKeyWordForDb(wordConvert,false,InitService.dialectType));
}
if (i + 1 < size) {
columns.append(",");
}
i++;
}
}
if (entityWrapper) {
columns.append("</otherwise></choose>");
}
}
/*
* 返回所有查询字段内容
*/
return columns.toString();
}protected void injectInsertOneSql(boolean selective, Class<?> mapperClass, Class<?> modelClass, TableInfo table) {
/*
* INSERT INTO table <trim prefix="(" suffix=")" suffixOverrides=",">
* <if test="xx != null">xx,</if> </trim> <trim prefix="values ("
* suffix=")" suffixOverrides=","> <if test="xx != null">#{xx},</if>
* </trim>
*/
KeyGenerator keyGenerator = new NoKeyGenerator();
StringBuilder fieldBuilder = new StringBuilder();
StringBuilder placeholderBuilder = new StringBuilder();
SqlMethod sqlMethod = selective ? SqlMethod.INSERT_ONE : SqlMethod.INSERT_ONE_ALL_COLUMN;
fieldBuilder.append("\n<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">\n");
placeholderBuilder.append("\n<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">\n");
String keyProperty = null;
String keyColumn = null;
// 表包含主键处理逻辑,如果不包含主键当普通字段处理
if (StringUtils.isNotEmpty(table.getKeyProperty())) {
if (table.getIdType() == IdType.AUTO) {
/** 自增主键 */
keyGenerator = new Jdbc3KeyGenerator();
keyProperty = table.getKeyProperty();
keyColumn = table.getKeyColumn();
} else {
if (null != table.getKeySequence()) {
keyGenerator = TableInfoHelper.genKeyGenerator(table, builderAssistant, sqlMethod.getMethod(), languageDriver);
keyProperty = table.getKeyProperty();
keyColumn = table.getKeyColumn();
fieldBuilder.append(table.getKeyColumn()).append(",");
placeholderBuilder.append("#{").append(table.getKeyProperty()).append("},");
} else {
/** 用户输入自定义ID */
fieldBuilder.append(Tools.convertKeyWordForDb(table.getKeyColumn(),true, InitService.dialectType)).append(",");
// 正常自定义主键策略
placeholderBuilder.append("#{").append(table.getKeyProperty()).append("},");
}
}
}
// 是否 IF 标签判断
boolean ifTag;
List<TableFieldInfo> fieldList = table.getFieldList();
for (TableFieldInfo fieldInfo : fieldList) {
// 在FieldIgnore,INSERT_UPDATE,INSERT 时设置为false
ifTag = !(FieldFill.INSERT == fieldInfo.getFieldFill()
|| FieldFill.INSERT_UPDATE == fieldInfo.getFieldFill());
if (selective && ifTag) {
fieldBuilder.append(convertIfTagIgnored(fieldInfo, false));
fieldBuilder.append(Tools.convertKeyWordForDb(fieldInfo.getColumn(),true,InitService.dialectType)).append(",");
fieldBuilder.append(convertIfTagIgnored(fieldInfo, true));
placeholderBuilder.append(convertIfTagIgnored(fieldInfo, false));
placeholderBuilder.append("#{").append(fieldInfo.getEl()).append("},");
placeholderBuilder.append(convertIfTagIgnored(fieldInfo, true));
} else {
fieldBuilder.append(Tools.convertKeyWordForDb(fieldInfo.getColumn(),true,InitService.dialectType)).append(",");
placeholderBuilder.append("#{").append(fieldInfo.getEl()).append("},");
}
}
fieldBuilder.append("\n</trim>");
placeholderBuilder.append("\n</trim>");
String sql = String.format(sqlMethod.getSql(), table.getTableName(), fieldBuilder.toString(),
placeholderBuilder.toString());
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
this.addInsertMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource, keyGenerator, keyProperty,
keyColumn);
}其中 convertKeyWordForDb 为大小写转换方法
/**
* 将条件转换为对应数据库带引号
* @param str 字符串
* @param upper 是否转换大写，true为转换，false为不转换，针对oracle
* @param dialectType 目前使用的数据库，mysql，oracle
* @return
*/
public static String convertKeyWordForDb(String str,boolean upper,String dialectType) {
List<String> keyList = (Arrays.asList(keyWord.split(",")));
if(keyList.contains(str.toUpperCase())){
if("mysql".equals(dialectType)){
str = "`"+str+"`";
}else if("oracle".equals(dialectType)){
if(upper){
str = "\""+str.toUpperCase()+"\"";
}else{
str = "\""+str+"\"";
}
}
}
return str;
}InitService.dialectType 为当前使用的数据库，通过获取Yml里面配置获取
创建InitService作为初始化启动类，获取yml配置信息
public class InitService implements ApplicationContextInitializer {
public static String dialectType="";
@Override
public void initialize(ConfigurableApplicationContext arg0) {
dialectType = getDialectType();
}
/**
* 获取目前使用的数据库
* @return
*/
static String getDialectType(){
//获取配置文件yml里面自己数据的配置信息
.....
return dialectType;
}
}配置此类在项目最初加载
SpringApplication application = new SpringApplication(Application.class);
application.addInitializers(new InitService());
application.run(args);如果为war包可能需要如下配置src/main/resources下建立META-INF/spring.factories文件，配置对应文件路径
org.springframework.context.ApplicationContextInitializer=\文件路径没有改正的为如果使用的为mybatisplus的selectMaps方法获取在转换上需要自行转换，下面提供几个我使用的方法作为参考
/**
* 包括空格判断
* @param input
* @return
*/
public static boolean containSpace(String input){
return Pattern.compile("\\s+").matcher(input).find();
}
static final String keyWord = "OR,DECIMAL,CREATE,FROM,PUBLIC,UNION,NOWAIT,RAW,TO,PCTFREE,VALUES,DEFAULT,GRANT,WITH,TABLE,ALTER," +
"SELECT,VARCHAR,ANY,|,-,GROUP,IDENTIFIED,/,^,NULL,CONNECT,VIEW,DISTINCT,SET,BY,ORDER,MINUS,PRIOR,ASC,VARCHAR2," +
"ALL,+,DROP,AND,LOCK,INTERSECT,HAVING,ON,UPDATE,BETWEEN,EXISTS,:,INTEGER,INSERT,FOR,CHAR,SMALLINT,=,MODE,REVOKE," +
"ELSE,>,IN,RENAME,TRIGGER,NUMBER,SYNONYM,.,CLUSTER,START,SHARE,OF,OPTION,INTO,COMPRESS,WHERE,*,CHECK,THEN,AS," +
"[,UNIQUE,],@,,,LONG,SIZE,(,DELETE,NOT,),DESC,DATE,RESOURCE,FLOAT,IS,LIKE,EXCLUSIVE,&,!,NOCOMPRESS,INDEX,LEVEL";
/**
* 将条件转换为对应数据库带引号
* @param str 字符串
* @return
*/
public static String convertForDb(String str) {
if("mysql".equals(InitService.dialectType){
str = "`"+str+"`";
}else if("oracle".equals(InitService.dialectType){
str = "\""+str.toUpperCase()+"\"";
}
return str;
}
/**
* 将setSqlSelect sql语句中查询条件别名进行转换为带双引号
* @param sql 查询条件
* @return
*/
public static String changeSqlParamAalias(String sql) {
//定义返回串
StringBuffer sbf = new StringBuffer();
//进行数据分割
String items [] = sql.split(",");
for(int i = 0 ;i<items.length;i++){
if(i!=0){
sbf.append(" , ");
}
final String str = items[i];
if(str.toLowerCase().contains("as")){
String [] array = str.trim().split("\\s+");
sbf.append(array[0]).append(" AS ").append("\""+array[2]+"\"");
}else if(containSpace(str.trim())){
String [] array = str.trim().split("\\s+");
sbf.append(array[0]).append(" AS ").append("\""+array[1]+"\"");
}else{
sbf.append(str).append(" AS ").append("\""+str.trim()+"\"");
}
}
return sbf.toString();
}
/**
* 获取实体类 @TableField @TableId 的注释名称和列的拼接查询sql
* @param clazz 实体类
* @return
*/
public static String getSelectSqlColumn(Class<?> clazz) {
List<String> keyList = (Arrays.asList(keyWord.split(",")));
//用于存储字段和中文值的集合
List<LinkedHashMap<String,String>> fieldList = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
LinkedHashMap<String, String> map = new LinkedHashMap<>();
//获取普通列
if (field.isAnnotationPresent(TableField.class)) {
TableField declaredAnnotation = field.getDeclaredAnnotation(TableField.class);
String column = declaredAnnotation.value();
if(StringUtils.isNotBlank(column)){
if(keyList.contains(column.toUpperCase())){
map.put("column", convertForDb(column));
}else{
map.put("column", column);
}
map.put("fieldNames", field.getName());
}
}
//获取ID列
if (field.isAnnotationPresent(TableId.class)) {
TableId declaredAnnotation = field.getDeclaredAnnotation(TableId.class);
String column = declaredAnnotation.value();
if(StringUtils.isNotBlank(column)){
map.put("fieldNames", field.getName());
map.put("column", column);
}
}
//如果为空则不增加
if(MapUtils.isNotEmpty(map)){
fieldList.add(map);
}
}
//定义返回串
StringBuffer sbf = new StringBuffer();
//循环进行拼接
for(LinkedHashMap<String,String> hashMap : fieldList){
if(sbf.length()>0){
sbf.append(" , ");
}
sbf.append(hashMap.get("column"));
sbf.append(" AS ");
sbf.append("\"").append(hashMap.get("fieldNames")).append("\"");
}
return sbf.toString();
}上述为我在兼容转换所用到的方法，希望给大家带来处理方式，如果有更好的方式欢迎大家评论留言。
扫一扫
1261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
