com.fhs.pagex.service.PageXAutoSqlService Maven / Gradle / Ivy
The newest version!
package com.fhs.pagex.service;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.fhs.common.spring.SpringContextUtil;
import com.fhs.common.utils.CheckUtils;
import com.fhs.common.utils.ConverterUtils;
import com.fhs.common.utils.StringUtil;
import com.fhs.core.exception.ParamException;
import com.fhs.pagex.bean.DefaultPageXBean;
import com.fhs.pagex.dao.DefaultPageXDAO;
import com.fhs.pagex.dto.PagexAddDTO;
import com.fhs.pagex.dto.PagexListSettDTO;
import com.mybatis.jpa.cache.JpaTools;
import com.mybatis.jpa.common.ColumnNameUtil;
import com.mybatis.jpa.meta.PersistentMeta;
import com.mybatis.jpa.statement.MybatisStatementAdapter;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Service;
import javax.script.ScriptException;
import java.lang.reflect.Field;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* 自动生成sql服务
*
* @ProjectName: framework_v2_idea2
* @Package: com.fhs.pagex.service
* @ClassName: PageXAutoSqlService
* @Author: JackWang
* @CreateDate: 2018/12/11 0011 21:14
* @UpdateUser: JackWang
* @UpdateDate: 2018/12/11 0011 21:14
* @Version: 1.0
*/
@Service
public class PageXAutoSqlService {
/**
*
*/
public void autoSql(String js) throws NoSuchMethodException, ScriptException {
PagexAddDTO pagexAddDTO = new PagexAddDTO(js);
String namespace = ConverterUtils.toString(pagexAddDTO.getModelConfig().get("namespace"));
String insertSql = autoInsert(pagexAddDTO);
parseSql("insertPageX", namespace, "insert", insertSql);
String deleteSql = autoDel(pagexAddDTO);
parseSql("delPageX", namespace, "delete", deleteSql);
deleteSql = autoDelForFkey(pagexAddDTO);
parseSql("delFkeyPageX", namespace, "delete", deleteSql);
String updateSql = autoUpdateSql(pagexAddDTO);
parseSql("updatePageX", namespace, "update", updateSql);
String findSql = autoFind(pagexAddDTO);
parseSql("findBeanPageX", namespace, "select", findSql);
PagexListSettDTO pagexListSettDTO = new PagexListSettDTO(js);
String findPageSql = autoFindPage(pagexListSettDTO);
parseSql("findPageX", namespace, "select", findPageSql);
String findPageCountSql = autoFindPageCount(pagexListSettDTO);
parseSql("findPageCountPageX", namespace, "select", findPageCountSql);
}
/**
* 已经存在的sqlid
*/
private Set existSqlIdSet = new HashSet<>();
private Map mappedStatements;
/**
* 将sql 添加到mybatis中
*
* @param sqlName sql的名字
* @param nameSpace namespace
* @param type 类型
* @param scriptSql sql
*/
private synchronized void parseSql(String sqlName, String nameSpace, String type, String scriptSql) {
if (mappedStatements == null) {
Configuration configuration = SpringContextUtil.getBeanByName(SqlSession.class).getConfiguration();
try {
Field mappedStatementsField = Configuration.class.getDeclaredField("mappedStatements");
mappedStatementsField.setAccessible(true);
mappedStatements = (Map) mappedStatementsField.get(configuration);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
String sqlId = nameSpace + "_" + sqlName;
//吧之前的缓存清除掉
mappedStatements.remove(DefaultPageXDAO.class.getName() + "." + sqlId);
/*if(existSqlIdSet.contains(sqlId))
{
throw new ParamException("sql已经存在:" + nameSpace + "." + type);
}*/
MybatisStatementAdapter adapter = JpaTools.statementAdapterMap.get(DefaultPageXBean.class.getName());
// 方法名
adapter.setMethodName(sqlId);
existSqlIdSet.add(sqlId);
adapter.setParameterTypeClass(Object.class);
PersistentMeta persistentMeta = JpaTools.persistentMetaMap.get(DefaultPageXDAO.class.getName());
SqlCommandType sqlCommandType = null;
String fromPreSql = null;
switch (type) {
case "select":
sqlCommandType = SqlCommandType.SELECT;
// adapter.setResultMapId(ResultMapConstants.DEFAULT_NAMESPACE + "." + persistentMeta.getEntityName());
// 返回值类型
adapter.setResultType(Map.class);
break;
case "delete":
sqlCommandType = SqlCommandType.DELETE;
adapter.setResultType(Integer.class);
break;
case "update":
sqlCommandType = SqlCommandType.UPDATE;
adapter.setResultType(Integer.class);
break;
case "insert":
sqlCommandType = SqlCommandType.INSERT;
adapter.setResultType(Integer.class);
break;
default:
throw new ParamException("不支持此类型");
}
// sqlScript
adapter.setSqlScript(scriptSql);
adapter.setSqlCommandType(sqlCommandType);
adapter.setResultMapId(null);
// 主键策略
adapter.setKeyGenerator(new NoKeyGenerator());
adapter.setKeyProperty(null);
adapter.setKeyColumn(null);
try {
//如果一个sql已经存在是正常现象
adapter.parseStatement();
} catch (IllegalArgumentException e) {
}
}
/**
* 自动生成insert sql
*
* @param pagexAddDTO pagexAddDTO
* @return insert 的sql
*/
public String autoInsert(PagexAddDTO pagexAddDTO) {
Map modelConfig = pagexAddDTO.getModelConfig();
StringBuilder sqlBuilder = new StringBuilder("INSERT INTO " + modelConfig.get("table") + " (`");
sqlBuilder.append(modelConfig.get("pkey") + "`");
List> fields = pagexAddDTO.getFormFieldSett();
Set fieldNameSet = new HashSet<>();
StringBuilder valueFieldBuilder = new StringBuilder("");
for (Map field : fields) {
if (CheckUtils.isNotEmpty(field.get("name"))) {
String[] names = ConverterUtils.toString(field.get("name")).split(",");
for (String name : names) {
fieldNameSet.add(name);
sqlBuilder.append(",`" + name + "`");
valueFieldBuilder.append(",#{" + ColumnNameUtil.underlineToCamel(name) + "}");
}
}
}
String groupCodeSql = "";
String groupCodeValSql = "";
if (ConverterUtils.toBoolean(pagexAddDTO.getModelConfig().get("isMultiTenant"))) {
groupCodeSql = ",group_code";
groupCodeValSql = ",#{groupCode}";
}
sqlBuilder.append(",`create_time`,`create_user`,`update_time`,`update_user` " + groupCodeSql + ") VALUES ("
+ ("uuid".equals(modelConfig.get("type")) ? "#{pkey}" : "null"));
sqlBuilder.append(valueFieldBuilder);
if (sqlBuilder.length() != 0) {
sqlBuilder.append(",");
}
sqlBuilder.append("DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'),#{createUser},DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'),#{updateUser}" + groupCodeValSql + ")");
return sqlBuilder.toString();
}
/**
* 自动生成删除的sql
*
* @param pagexAddDTO pagexAddDTO
* @return 根据id删除数的sql
*/
public String autoDel(PagexAddDTO pagexAddDTO) {
Map modelConfig = pagexAddDTO.getModelConfig();
StringBuilder sqlBuilder = new StringBuilder(" DELETE FROM " + modelConfig.get("table") + " WHERE ");
sqlBuilder.append(modelConfig.get("pkey") + " = #{id}");
return sqlBuilder.toString();
}
/**
* 自动生成删除的sql-用于一对多
*
* @param pagexAddDTO pagexAddDTO
* @return 根据id删除数的sql
*/
public String autoDelForFkey(PagexAddDTO pagexAddDTO) {
Map modelConfig = pagexAddDTO.getModelConfig();
StringBuilder sqlBuilder = new StringBuilder(" DELETE FROM " + modelConfig.get("table") + " WHERE ");
sqlBuilder.append(modelConfig.get("fkey") + " = #{fkey}");
return sqlBuilder.toString();
}
/**
* 自动生成查询单个的sql
*
* @param pagexAddDTO pagexAddDTO
* @return 根据id查询单个sql
*/
public String autoFind(PagexAddDTO pagexAddDTO) {
Map modelConfig = pagexAddDTO.getModelConfig();
StringBuilder sqlBuilder = new StringBuilder(" SELECT " + modelConfig.get("pkey") + " AS " + modelConfig.get("pkeyCamel"));
List> fields = pagexAddDTO.getFormFieldSett();
for (Map field : fields) {
if (CheckUtils.isNotEmpty(field.get("name"))) {
String[] names = ConverterUtils.toString(field.get("name")).split(",");
for (String name : names) {
sqlBuilder.append("," + name + " AS " + ColumnNameUtil.underlineToCamel(name));
}
}
}
sqlBuilder.append(",create_time createTime,create_user createUser,update_time updateTime,update_user updateUser FROM " + modelConfig.get("table") + " WHERE " + modelConfig.get("pkey") + "=#{id}");
return sqlBuilder.toString();
}
/**
* 自动生成查询列表的sql
*
* @param pagexListSettDTO pagexListSettDTO
* @return 生成根据条件查询列表sql
*/
public String autoFindPage(PagexListSettDTO pagexListSettDTO) {
Map modelConfig = pagexListSettDTO.getModelConfig();
StringBuilder sqlBuilder = new StringBuilder("";
sqlBuilder.append(limit);
return sqlBuilder.toString();
}
/**
* 自动生成查询列表总数的sql
*
* @param pagexListSettDTO pagexListSettDTO
* @return 查询列表总数的sql
*/
public String autoFindPageCount(PagexListSettDTO pagexListSettDTO) {
Map modelConfig = pagexListSettDTO.getModelConfig();
StringBuilder sqlBuilder = new StringBuilder("");
return sqlBuilder.toString();
}
/**
* 自动生成列表和查总数sql的过滤条件sql
*
* @param pagexListSettDTO pagexListSettDTO
* @return 生成列表和查总数sql的过滤条件sql
*/
private String autoPagerWhere(PagexListSettDTO pagexListSettDTO) {
StringBuilder sqlBuilder = new StringBuilder("");
List> fields = pagexListSettDTO.getFilters();
String fieldName = null;
String operator = null;
String camelName = null;
Set hasWhereFields = new HashSet<>();
for (Map field : fields) {
fieldName = ConverterUtils.toString(field.get("name"));
camelName = ColumnNameUtil.underlineToCamel(fieldName);
hasWhereFields.add(fieldName);
//代表是between
if (ConverterUtils.toBoolean(field.get("isBT"))) {
//数据要小于等于max
sqlBuilder.append(" ");
sqlBuilder.append(" AND " + fieldName + " #{" + camelName + "Max}");
sqlBuilder.append(" ");
//数据要大于等于min
sqlBuilder.append(" ");
sqlBuilder.append(" AND " + fieldName + " =]]> #{" + camelName + "Min}");
sqlBuilder.append(" ");
}
sqlBuilder.append(" ");
if (!field.containsKey("filterType")) {
operator = "=";
} else if (ConverterUtils.toString(field.get("filterType")).contains("like")) {
sqlBuilder.append(" AND " + fieldName + " LIKE concat('%',#{" + camelName + "},'%')");
sqlBuilder.append(" ");
continue;
} else {
operator = ConverterUtils.toString(field.get("filterType"));
}
sqlBuilder.append(" AND " + fieldName + " #{" + camelName + "}");
sqlBuilder.append("");
}
//如果需要做数据权限校验
if (pagexListSettDTO.getModelConfig().containsKey("dp")) {
JSONObject dpSett = JSON.parseObject(ConverterUtils.toString(pagexListSettDTO.getModelConfig().get("dp")));
dpSett.keySet().forEach(key -> {
//获取这个字段要根据哪个数据权限来做过滤
String dpGroup = dpSett.getString(key);
sqlBuilder.append(" ");
sqlBuilder.append(" ");
sqlBuilder.append(" AND " + key + " IN (${dataPermissin." + dpGroup + "}) ");
sqlBuilder.append(" ");
sqlBuilder.append(" ");
});
}
//处理扩展参数
if (pagexListSettDTO.getModelConfig().containsKey("extendsParam")) {
String[] extendsFilters = StringUtil.toString(pagexListSettDTO.getModelConfig().get("extendsParam")).split("&");
for (String extendsFilter : extendsFilters) {
String clomun = extendsFilter.split("=")[0];
hasWhereFields.add(clomun);
sqlBuilder.append(" ");
sqlBuilder.append(" AND " + clomun + "= #{" + clomun + "}");
sqlBuilder.append(" ");
}
}
PagexAddDTO addDTO = PagexDataService.SIGNEL.getPagexAddDTOFromCache(pagexListSettDTO.getModelConfig().get("namespace").toString());
List> formFieldSett = addDTO.getFormFieldSett();
for (Map filed : formFieldSett) {
fieldName = ConverterUtils.toString(filed.get("name"));
camelName = ConverterUtils.toString(filed.get("camelName"));
if (hasWhereFields.contains(fieldName) || CheckUtils.isNullOrEmpty(fieldName) || CheckUtils.isNullOrEmpty(camelName)) {
continue;
}
if ("group_code".equals(fieldName)) {
continue;
}
sqlBuilder.append(" ");
sqlBuilder.append(" AND " + fieldName + " #{" + camelName + "}");
sqlBuilder.append(" ");
}
//是否为多租户模式
if (ConverterUtils.toBoolean(addDTO.getModelConfig().get("isMultiTenant"))) {
sqlBuilder.append(" ");
sqlBuilder.append(" AND group_code #{groupCode}");
sqlBuilder.append(" ");
}
sqlBuilder.append(" ");
return sqlBuilder.toString();
}
/**
* 拼接更新sql
*
* @param pagexAddDTO pagexAddDTO
* @return 更新sql
*/
public String autoUpdateSql(PagexAddDTO pagexAddDTO) {
Map modelConfig = pagexAddDTO.getModelConfig();
// columns
StringBuilder sets = new StringBuilder();
List> fields = pagexAddDTO.getFormFieldSett();
sets.append(" ");
String camelName = null;
for (Map field : fields) {
if (CheckUtils.isNotEmpty(field.get("name"))) {
if (CheckUtils.isNotEmpty(field.get("name"))) {
String[] names = ConverterUtils.toString(field.get("name")).split(",");
for (String fieldName : names) {
camelName = ColumnNameUtil.underlineToCamel(fieldName);
sets.append(" ");
// columnName = #{ }
sets.append(fieldName).append(" = ").append("#{" + camelName + "}")
.append(" , ");
sets.append(" ");
}
}
}
}
sets.append(" ");
return "";
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy