结果如下图所示
java类代码分别如下
1
package com.hdx.contractor.util.mybatis;
import com.hdx.contractor.common.user.SecurityUser;
import com.hdx.contractor.common.user.UserDetail;
import com.hdx.contractor.util.query.oConvertUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.binding.MapperMethod.ParamMap;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.Date;
import java.util.Properties;
@Slf4j
@Component
@Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }) })
public class MybatisInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
String sqlId = mappedStatement.getId();
log.debug("------sqlId------" + sqlId);
SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
Object parameter = invocation.getArgs()[1];
log.debug("------sqlCommandType------" + sqlCommandType);
if (parameter == null) {
return invocation.proceed();
}
if (SqlCommandType.INSERT == sqlCommandType) {
UserDetail sysUser = this.getLoginUser();
Field[] fields = oConvertUtils.getAllFields(parameter);
for (Field field : fields) {
log.debug("------field.name------" + field.getName());
try {
if ("createBy".equals(field.getName())) {
field.setAccessible(true);
Object localCreateBy = field.get(parameter);
field.setAccessible(false);
if (localCreateBy == null || "".equals(localCreateBy)) {
if (sysUser != null) {
field.setAccessible(true);
field.set(parameter, sysUser.getUsername());
field.setAccessible(false);
}
}
}
if ("userId".equals(field.getName())) {
field.setAccessible(true);
Object localuserId = field.get(parameter);
field.setAccessible(false);
if (localuserId == null || "".equals(localuserId)) {
if (sysUser != null) {
field.setAccessible(true);
field.set(parameter, sysUser.getId());
field.setAccessible(false);
}
}
}
if ("createTime".equals(field.getName())) {
field.setAccessible(true);
Object localCreateDate = field.get(parameter);
field.setAccessible(false);
if (localCreateDate == null || "".equals(localCreateDate)) {
field.setAccessible(true);
field.set(parameter, new Date());
field.setAccessible(false);
}
}
if ("sysOrgCode".equals(field.getName())) {
field.setAccessible(true);
Object localSysOrgCode = field.get(parameter);
field.setAccessible(false);
if (localSysOrgCode == null || "".equals(localSysOrgCode)) {
if (sysUser != null) {
field.setAccessible(true);
field.setAccessible(false);
}
}
}
} catch (Exception e) {
}
}
}
if (SqlCommandType.UPDATE == sqlCommandType) {
UserDetail sysUser = this.getLoginUser();
Field[] fields = null;
if (parameter instanceof ParamMap) {
ParamMap<?> p = (ParamMap<?>) parameter;
String et = "et";
if (p.containsKey(et)) {
parameter = p.get(et);
} else {
parameter = p.get("param1");
}
if (parameter == null) {
return invocation.proceed();
}
fields = oConvertUtils.getAllFields(parameter);
} else {
fields = oConvertUtils.getAllFields(parameter);
}
for (Field field : fields) {
log.debug("------field.name------" + field.getName());
try {
if ("updateBy".equals(field.getName())) {
if (sysUser != null) {
field.setAccessible(true);
field.set(parameter, sysUser.getUsername());
field.setAccessible(false);
}
}
if ("updateTime".equals(field.getName())) {
field.setAccessible(true);
field.set(parameter, new Date());
field.setAccessible(false);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
private UserDetail getLoginUser() {
UserDetail sysUser = null;
try {
sysUser = SecurityUser.getUser();
} catch (Exception e) {
sysUser = null;
}
return sysUser;
}
}
2
package com.hdx.contractor.util.query;
public interface CommonConstant {
String UNKNOWN = "unknown";
String STRING_NULL = "null";
String DICT_TEXT_SUFFIX = "_dictText";
}
3
package com.hdx.contractor.util.query;
import com.baomidou.mybatisplus.annotation.DbType;
import com.hdx.contractor.util.SpringContextUtils;
import lombok.extern.slf4j.Slf4j;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
@Slf4j
public class CommonUtils {
private static String FILE_NAME_REGEX = "[^A-Za-z\\.\\(\\)\\-（）\\_0-9\\u4e00-\\u9fa5]";
private static String DB_TYPE = "";
private static DbType dbTypeEnum = null;
@Deprecated
public static String getDatabaseType() {
if (oConvertUtils.isNotEmpty(DB_TYPE)) {
return DB_TYPE;
}
DataSource dataSource = SpringContextUtils.getApplicationContext().getBean(DataSource.class);
try {
return getDatabaseTypeByDataSource(dataSource);
} catch (SQLException e) {
log.warn(e.getMessage(), e);
return "";
}
}
private static String getDatabaseTypeByDataSource(DataSource dataSource) throws SQLException {
if ("".equals(DB_TYPE)) {
Connection connection = dataSource.getConnection();
try {
DatabaseMetaData md = connection.getMetaData();
String dbType = md.getDatabaseProductName().toUpperCase();
String sqlserver = "SQL SERVER";
if (dbType.indexOf(DataBaseConstant.DB_TYPE_MYSQL) >= 0) {
DB_TYPE = DataBaseConstant.DB_TYPE_MYSQL;
} else if (dbType.indexOf(DataBaseConstant.DB_TYPE_ORACLE) >= 0 || dbType.indexOf(DataBaseConstant.DB_TYPE_DM) >= 0) {
DB_TYPE = DataBaseConstant.DB_TYPE_ORACLE;
} else if (dbType.indexOf(DataBaseConstant.DB_TYPE_SQLSERVER) >= 0 || dbType.indexOf(sqlserver) >= 0) {
DB_TYPE = DataBaseConstant.DB_TYPE_SQLSERVER;
} else if (dbType.indexOf(DataBaseConstant.DB_TYPE_POSTGRESQL) >= 0) {
DB_TYPE = DataBaseConstant.DB_TYPE_POSTGRESQL;
} else if (dbType.indexOf(DataBaseConstant.DB_TYPE_MARIADB) >= 0) {
DB_TYPE = DataBaseConstant.DB_TYPE_MARIADB;
} else {
log.error("数据库类型:[" + dbType + "]不识别!");
}
} catch (Exception e) {
log.error(e.getMessage(), e);
} finally {
connection.close();
}
}
return DB_TYPE;
}
}
4
package com.hdx.contractor.util.query;
import com.hdx.contractor.util.SpringContextUtils;
import java.util.List;
public class DataAutorUtils {
public static final String MENU_DATA_AUTHOR_RULES = "MENU_DATA_AUTHOR_RULES";
@SuppressWarnings("unchecked")
public static synchronized List<SysPermissionDataRuleModel> loadDataSearchConditon() {
return (List<SysPermissionDataRuleModel>) SpringContextUtils.getHttpServletRequest().getAttribute(MENU_DATA_AUTHOR_RULES);
}
}
5
package com.hdx.contractor.util.query;
public interface DataBaseConstant {
public static final String DB_TYPE_MYSQL = "MYSQL";
public static final String DB_TYPE_ORACLE = "ORACLE";
public static final String DB_TYPE_DM = "DM";
public static final String DB_TYPE_POSTGRESQL = "POSTGRESQL";
public static final String DB_TYPE_SQLSERVER = "SQLSERVER";
public static final String DB_TYPE_MARIADB = "MARIADB";
public static final String DB_TYPE_DB2 = "DB2";
public static final String DB_TYPE_HSQL = "HSQL";
public static final String SYS_ORG_CODE = "sysOrgCode";
public static final String SYS_ORG_CODE_TABLE = "sys_org_code";
public static final String SYS_MULTI_ORG_CODE = "sysMultiOrgCode";
public static final String SYS_MULTI_ORG_CODE_TABLE = "sys_multi_org_code";
public static final String SYS_USER_CODE = "sysUserCode";
public static final String SYS_USER_CODE_TABLE = "sys_user_code";
public static final String SYS_USER_NAME = "sysUserName";
public static final String SYS_USER_NAME_TABLE = "sys_user_name";
public static final String SYS_DATE = "sysDate";
public static final String SYS_DATE_TABLE = "sys_date";
public static final String SYS_TIME = "sysTime";
public static final String SYS_TIME_TABLE = "sys_time";
public static final String SYS_BASE_PATH = "sys_base_path";
public static final String CREATE_BY_TABLE = "create_by";
public static final String CREATE_BY = "createBy";
public static final String CREATE_TIME_TABLE = "create_time";
public static final String CREATE_TIME = "createTime";
public static final String UPDATE_BY_TABLE = "update_by";
public static final String UPDATE_BY = "updateBy";
public static final String UPDATE_TIME = "updateTime";
public static final String UPDATE_TIME_TABLE = "update_time";
public static final String BPM_STATUS = "bpmStatus";
public static final String BPM_STATUS_TABLE = "bpm_status";
String TENANT_ID = "tenantId";
String TENANT_ID_TABLE = "tenant_id";
String SQL_WHERE = "where";
String SQL_ASC = "asc";
String DB_TYPE_SQL_SERVER_BLANK = "sql server";
}
6
package com.hdx.contractor.util.query;
public enum MatchTypeEnum {
AND("AND"),
OR("OR");
private String value;
MatchTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public static MatchTypeEnum getByValue(Object value) {
if (oConvertUtils.isEmpty(value)) {
return null;
}
return getByValue(value.toString());
}
public static MatchTypeEnum getByValue(String value) {
if (oConvertUtils.isEmpty(value)) {
return null;
}
for (MatchTypeEnum val : values()) {
if (val.getValue().toLowerCase().equals(value.toLowerCase())) {
return val;
}
}
return null;
}
}
7
package com.hdx.contractor.util.query;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import javax.servlet.http.HttpServletRequest;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.net.InetAddress;
import java.net.NetworkInterface;
import java.net.SocketException;
import java.net.UnknownHostException;
import java.sql.Date;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class oConvertUtils {
public static boolean isEmpty(Object object) {
if (object == null) {
return (true);
}
if ("".equals(object)) {
return (true);
}
if (CommonConstant.STRING_NULL.equals(object)) {
return (true);
}
return (false);
}
public static boolean isNotEmpty(Object object) {
if (object != null && !"".equals(object) && !object.equals(CommonConstant.STRING_NULL)) {
return (true);
}
return (false);
}
public static String decode(String strIn, String sourceCode, String targetCode) {
String temp = code2code(strIn, sourceCode, targetCode);
return temp;
}
@SuppressWarnings("AlibabaLowerCamelCaseVariableNaming")
public static String StrToUTF(String strIn, String sourceCode, String targetCode) {
strIn = "";
try {
strIn = new String(strIn.getBytes("ISO-8859-1"), "GBK");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return strIn;
}
private static String code2code(String strIn, String sourceCode, String targetCode) {
String strOut = null;
if (strIn == null || "".equals(strIn.trim())) {
return strIn;
}
try {
byte[] b = strIn.getBytes(sourceCode);
for (int i = 0; i < b.length; i++) {
System.out.print(b[i] + " ");
}
strOut = new String(b, targetCode);
} catch (Exception e) {
e.printStackTrace();
return null;
}
return strOut;
}
public static int getInt(String s, int defval) {
if (s == null || s == "") {
return (defval);
}
try {
return (Integer.parseInt(s));
} catch (NumberFormatException e) {
return (defval);
}
}
public static int getInt(String s) {
if (s == null || s == "") {
return 0;
}
try {
return (Integer.parseInt(s));
} catch (NumberFormatException e) {
return 0;
}
}
public static int getInt(String s, Integer df) {
if (s == null || s == "") {
return df;
}
try {
return (Integer.parseInt(s));
} catch (NumberFormatException e) {
return 0;
}
}
public static Integer[] getInts(String[] s) {
if (s == null) {
return null;
}
Integer[] integer = new Integer[s.length];
for (int i = 0; i < s.length; i++) {
integer[i] = Integer.parseInt(s[i]);
}
return integer;
}
public static double getDouble(String s, double defval) {
if (s == null || s == "") {
return (defval);
}
try {
return (Double.parseDouble(s));
} catch (NumberFormatException e) {
return (defval);
}
}
public static double getDou(Double s, double defval) {
if (s == null) {
return (defval);
}
return s;
}
public static int getInt(Object object, int defval) {
if (isEmpty(object)) {
return (defval);
}
try {
return (Integer.parseInt(object.toString()));
} catch (NumberFormatException e) {
return (defval);
}
}
public static Integer getInt(Object object) {
if (isEmpty(object)) {
return null;
}
try {
return (Integer.parseInt(object.toString()));
} catch (NumberFormatException e) {
return null;
}
}
public static int getInt(BigDecimal s, int defval) {
if (s == null) {
return (defval);
}
return s.intValue();
}
public static Integer[] getIntegerArry(String[] object) {
int len = object.length;
Integer[] result = new Integer[len];
try {
for (int i = 0; i < len; i++) {
result[i] = new Integer(object[i].trim());
}
return result;
} catch (NumberFormatException e) {
return null;
}
}
public static String getString(String s) {
return (getString(s, ""));
}
public static String getString(Object object) {
if (isEmpty(object)) {
return "";
}
return (object.toString().trim());
}
public static String getString(int i) {
return (String.valueOf(i));
}
public static String getString(float i) {
return (String.valueOf(i));
}
public static String getString(String s, String defval) {
if (isEmpty(s)) {
return (defval);
}
return (s.trim());
}
public static String getString(Object s, String defval) {
if (isEmpty(s)) {
return (defval);
}
return (s.toString().trim());
}
public static long stringToLong(String str) {
Long test = new Long(0);
try {
test = Long.valueOf(str);
} catch (Exception e) {
}
return test.longValue();
}
public static String getIp() {
String ip = null;
try {
InetAddress address = InetAddress.getLocalHost();
ip = address.getHostAddress();
} catch (UnknownHostException e) {
e.printStackTrace();
}
return ip;
}
private static boolean isBaseDataType(Class clazz) throws Exception {
return (clazz.equals(String.class) || clazz.equals(Integer.class) || clazz.equals(Byte.class) || clazz.equals(Long.class) || clazz.equals(Double.class) || clazz.equals(Float.class) || clazz.equals(Character.class) || clazz.equals(Short.class) || clazz.equals(BigDecimal.class) || clazz.equals(BigInteger.class) || clazz.equals(Boolean.class) || clazz.equals(Date.class) || clazz.isPrimitive());
}
public static String getIpAddrByRequest(HttpServletRequest request) {
String ip = request.getHeader("x-forwarded-for");
if (ip == null || ip.length() == 0 || CommonConstant.UNKNOWN.equalsIgnoreCase(ip)) {
ip = request.getHeader("Proxy-Client-IP");
}
if (ip == null || ip.length() == 0 || CommonConstant.UNKNOWN.equalsIgnoreCase(ip)) {
ip = request.getHeader("WL-Proxy-Client-IP");
}
if (ip == null || ip.length() == 0 || CommonConstant.UNKNOWN.equalsIgnoreCase(ip)) {
ip = request.getRemoteAddr();
}
return ip;
}
public static String getRealIp() throws SocketException {
String localip = null;
String netip = null;
Enumeration<NetworkInterface> netInterfaces = NetworkInterface.getNetworkInterfaces();
InetAddress ip = null;
boolean finded = false;
while (netInterfaces.hasMoreElements() && !finded) {
NetworkInterface ni = netInterfaces.nextElement();
Enumeration<InetAddress> address = ni.getInetAddresses();
while (address.hasMoreElements()) {
ip = address.nextElement();
if (!ip.isSiteLocalAddress() && !ip.isLoopbackAddress() && ip.getHostAddress().indexOf(":") == -1) {
netip = ip.getHostAddress();
finded = true;
break;
} else if (ip.isSiteLocalAddress() && !ip.isLoopbackAddress() && ip.getHostAddress().indexOf(":") == -1) {
localip = ip.getHostAddress();
}
}
}
if (netip != null && !"".equals(netip)) {
return netip;
} else {
return localip;
}
}
public static String replaceBlank(String str) {
String dest = "";
if (str != null) {
String reg = "\\s*|\t|\r|\n";
Pattern p = Pattern.compile(reg);
Matcher m = p.matcher(str);
dest = m.replaceAll("");
}
return dest;
}
public static boolean isIn(String substring, String[] source) {
if (source == null || source.length == 0) {
return false;
}
for (int i = 0; i < source.length; i++) {
String aSource = source[i];
if (aSource.equals(substring)) {
return true;
}
}
return false;
}
public static Map<Object, Object> getHashMap() {
return new HashMap<>(5);
}
public static Map<Object, Object> setToMap(Set<Object> setobj) {
Map<Object, Object> map = getHashMap();
for (Iterator iterator = setobj.iterator(); iterator.hasNext();) {
Map.Entry<Object, Object> entry = (Map.Entry<Object, Object>) iterator.next();
map.put(entry.getKey().toString(), entry.getValue() == null ? "" : entry.getValue().toString().trim());
}
return map;
}
public static boolean isInnerIp(String ipAddress) {
boolean isInnerIp = false;
long ipNum = getIpNum(ipAddress);
long aBegin = getIpNum("10.0.0.0");
long aEnd = getIpNum("10.255.255.255");
long bBegin = getIpNum("172.16.0.0");
long bEnd = getIpNum("172.31.255.255");
long cBegin = getIpNum("192.168.0.0");
long cEnd = getIpNum("192.168.255.255");
String localIp = "127.0.0.1";
isInnerIp = isInner(ipNum, aBegin, aEnd) || isInner(ipNum, bBegin, bEnd) || isInner(ipNum, cBegin, cEnd) || localIp.equals(ipAddress);
return isInnerIp;
}
private static long getIpNum(String ipAddress) {
String[] ip = ipAddress.split("\\.");
long a = Integer.parseInt(ip[0]);
long b = Integer.parseInt(ip[1]);
long c = Integer.parseInt(ip[2]);
long d = Integer.parseInt(ip[3]);
long ipNum = a * 256 * 256 * 256 + b * 256 * 256 + c * 256 + d;
return ipNum;
}
private static boolean isInner(long userIp, long begin, long end) {
return (userIp >= begin) && (userIp <= end);
}
public static String camelName(String name) {
StringBuilder result = new StringBuilder();
if (name == null || name.isEmpty()) {
return "";
} else if (!name.contains(SymbolConstant.UNDERLINE)) {
return name.substring(0, 1).toLowerCase() + name.substring(1).toLowerCase();
}
String[] camels = name.split("_");
for (String camel : camels) {
if (camel.isEmpty()) {
continue;
}
if (result.length() == 0) {
result.append(camel.toLowerCase());
} else {
result.append(camel.substring(0, 1).toUpperCase());
result.append(camel.substring(1).toLowerCase());
}
}
return result.toString();
}
public static String camelNames(String names) {
if(names==null||"".equals(names)){
return null;
}
StringBuffer sf = new StringBuffer();
String[] fs = names.split(",");
for (String field : fs) {
field = camelName(field);
sf.append(field + ",");
}
String result = sf.toString();
return result.substring(0, result.length() - 1);
}
public static String camelNameCapFirst(String name) {
StringBuilder result = new StringBuilder();
if (name == null || name.isEmpty()) {
return "";
} else if (!name.contains(SymbolConstant.UNDERLINE)) {
return name.substring(0, 1).toUpperCase() + name.substring(1).toLowerCase();
}
String[] camels = name.split("_");
for (String camel : camels) {
if (camel.isEmpty()) {
continue;
}
result.append(camel.substring(0, 1).toUpperCase());
result.append(camel.substring(1).toLowerCase());
}
return result.toString();
}
public static String camelToUnderline(String para){
int length = 3;
if(para.length()<length){
return para.toLowerCase();
}
StringBuilder sb=new StringBuilder(para);
int temp=0;
for(int i=2;i<para.length();i++){
if(Character.isUpperCase(para.charAt(i))){
sb.insert(i+temp, "_");
temp+=1;
}
}
return sb.toString().toLowerCase();
}
public static String randomGen(int place) {
String base = "qwertyuioplkjhgfdsazxcvbnmQAZWSXEDCRFVTGBYHNUJMIKLOP0123456789";
StringBuffer sb = new StringBuffer();
Random rd = new Random();
for(int i=0;i<place;i++) {
sb.append(base.charAt(rd.nextInt(base.length())));
}
return sb.toString();
}
public static Field[] getAllFields(Object object) {
Class<?> clazz = object.getClass();
List<Field> fieldList = new ArrayList<>();
while (clazz != null) {
fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
clazz = clazz.getSuperclass();
}
Field[] fields = new Field[fieldList.size()];
fieldList.toArray(fields);
return fields;
}
public static List<Map<String, Object>> toLowerCasePageList(List<Map<String, Object>> list){
List<Map<String, Object>> select = new ArrayList<>();
for (Map<String, Object> row : list) {
Map<String, Object> resultMap = new HashMap<>(5);
Set<String> keySet = row.keySet();
for (String key : keySet) {
String newKey = key.toLowerCase();
resultMap.put(newKey, row.get(key));
}
select.add(resultMap);
}
return select;
}
public static<F,T> List<T> entityListToModelList(List<F> fromList, Class<T> tClass){
if(fromList == null || fromList.isEmpty()){
return null;
}
List<T> tList = new ArrayList<>();
for(F f : fromList){
T t = entityToModel(f, tClass);
tList.add(t);
}
return tList;
}
public static<F,T> T entityToModel(F entity, Class<T> modelClass) {
log.debug("entityToModel : Entity属性的值赋值到Model");
Object model = null;
if (entity == null || modelClass ==null) {
return null;
}
try {
model = modelClass.newInstance();
} catch (InstantiationException e) {
log.error("entityToModel : 实例化异常", e);
} catch (IllegalAccessException e) {
log.error("entityToModel : 安全权限异常", e);
}
BeanUtils.copyProperties(entity, model);
return (T)model;
}
public static boolean listIsEmpty(Collection list) {
return (list == null || list.size() == 0);
}
public static boolean listIsNotEmpty(Collection list) {
return !listIsEmpty(list);
}
}
8
package com.hdx.contractor.util.query;
import java.io.Serializable;
public class QueryCondition implements Serializable {
private static final long serialVersionUID = 4740166316629191651L;
private String field;
private String type;
private String dbType;
private String rule;
private String val;
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getDbType() {
return dbType;
}
public void setDbType(String dbType) {
this.dbType = dbType;
}
public String getRule() {
return rule;
}
public void setRule(String rule) {
this.rule = rule;
}
public String getVal() {
return val;
}
public void setVal(String val) {
this.val = val;
}
@Override
public String toString(){
StringBuffer sb =new StringBuffer();
if(field == null || "".equals(field)){
return "";
}
sb.append(this.field).append(" ").append(this.rule).append(" ").append(this.type).append(" ").append(this.dbType).append(" ").append(this.val);
return sb.toString();
}
}
9
package com.hdx.contractor.util.query;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.util.NumberUtils;
import java.beans.PropertyDescriptor;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@Slf4j
public class QueryGenerator {
public static final String SQL_RULES_COLUMN = "SQL_RULES_COLUMN";
private static final String BEGIN = "_begin";
private static final String END = "_end";
private static final String MULTI = "_MultiString";
private static final String STAR = "*";
private static final String COMMA = ",";
public static final String QUERY_COMMA_ESCAPE = "++";
private static final String NOT_EQUAL = "!";
private static final String QUERY_SEPARATE_KEYWORD = " ";
private static final String SUPER_QUERY_PARAMS = "superQueryParams";
private static final String SUPER_QUERY_MATCH_TYPE = "superQueryMatchType";
public static final String SQL_SQ = "'";
private static final String ORDER_COLUMN = "column";
private static final String ORDER_TYPE = "order";
private static final String ORDER_TYPE_ASC = "ASC";
public static final String LIKE_MYSQL_SPECIAL_STRS = "_,%";
public static final String YYYY_MM_DD = "yyyy-MM-dd";
public static final String TO_DATE = "to_date";
private static final ThreadLocal<SimpleDateFormat> LOCAL = new ThreadLocal<SimpleDateFormat>();
private static SimpleDateFormat getTime() {
SimpleDateFormat time = LOCAL.get();
if (time == null) {
time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
LOCAL.set(time);
}
return time;
}
public static <T> QueryWrapper<T> initQueryWrapper(T searchObj, Map<String, String[]> parameterMap) {
long start = System.currentTimeMillis();
QueryWrapper<T> queryWrapper = new QueryWrapper<T>();
installMplus(queryWrapper, searchObj, parameterMap);
log.debug("---查询条件构造器初始化完成,耗时:" + (System.currentTimeMillis() - start) + "毫秒----");
return queryWrapper;
}
private static void installMplus(QueryWrapper<?> queryWrapper, Object searchObj, Map<String, String[]> parameterMap) {
PropertyDescriptor[] origDescriptors = PropertyUtils.getPropertyDescriptors(searchObj);
Map<String, SysPermissionDataRuleModel> ruleMap = getRuleMap();
for (String c : ruleMap.keySet()) {
if (oConvertUtils.isNotEmpty(c) && c.startsWith(SQL_RULES_COLUMN)) {
queryWrapper.and(i -> i.apply(getSqlRuleValue(ruleMap.get(c).getRuleValue())));
}
}
String name, type, column;
Map<String, String> fieldColumnMap = new HashMap<>(5);
for (int i = 0; i < origDescriptors.length; i++) {
name = origDescriptors[i].getName();
type = origDescriptors[i].getPropertyType().toString();
try {
if (judgedIsUselessField(name) || !PropertyUtils.isReadable(searchObj, name)) {
continue;
}
Object value = PropertyUtils.getSimpleProperty(searchObj, name);
column = getTableFieldName(searchObj.getClass(), name);
if (column == null) {
continue;
}
fieldColumnMap.put(name, column);
if (ruleMap.containsKey(name)) {
addRuleToQueryWrapper(ruleMap.get(name), column, origDescriptors[i].getPropertyType(), queryWrapper);
}
doIntervalQuery(queryWrapper, parameterMap, type, name, column);
if (null != value && value.toString().startsWith(COMMA) && value.toString().endsWith(COMMA)) {
String multiLikeval = value.toString().replace(",,", COMMA);
String[] vals = multiLikeval.substring(1, multiLikeval.length()).split(COMMA);
final String field = oConvertUtils.camelToUnderline(column);
if (vals.length > 1) {
queryWrapper.and(j -> {
log.info("---查询过滤器，Query规则---field:{}, rule:{}, value:{}", field, "like", vals[0]);
j = j.like(field, vals[0]);
for (int k = 1; k < vals.length; k++) {
j = j.or().like(field, vals[k]);
log.info("---查询过滤器，Query规则 .or()---field:{}, rule:{}, value:{}", field, "like", vals[k]);
}
});
} else {
log.info("---查询过滤器，Query规则---field:{}, rule:{}, value:{}", field, "like", vals[0]);
queryWrapper.and(j -> j.like(field, vals[0]));
}
} else {
QueryRuleEnum rule = convert2Rule(value);
value = replaceValue(rule, value);
addEasyQuery(queryWrapper, column, rule, value);
}
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
doMultiFieldsOrder(queryWrapper, parameterMap, fieldColumnMap);
doSuperQuery(queryWrapper, parameterMap, fieldColumnMap);
}
private static void doIntervalQuery(QueryWrapper<?> queryWrapper, Map<String, String[]> parameterMap, String type, String filedName, String columnName) throws ParseException {
String endValue = null, beginValue = null;
if (parameterMap != null && parameterMap.containsKey(filedName + BEGIN)) {
beginValue = parameterMap.get(filedName + BEGIN)[0].trim();
addQueryByRule(queryWrapper, columnName, type, beginValue, QueryRuleEnum.GE);
}
if (parameterMap != null && parameterMap.containsKey(filedName + END)) {
endValue = parameterMap.get(filedName + END)[0].trim();
addQueryByRule(queryWrapper, columnName, type, endValue, QueryRuleEnum.LE);
}
if (parameterMap != null && parameterMap.containsKey(filedName + MULTI)) {
endValue = parameterMap.get(filedName + MULTI)[0].trim();
addQueryByRule(queryWrapper, columnName.replace(MULTI, ""), type, endValue, QueryRuleEnum.IN);
}
}
private static void doMultiFieldsOrder(QueryWrapper<?> queryWrapper, Map<String, String[]> parameterMap, Map<String, String> fieldColumnMap) {
Set<String> allFields = fieldColumnMap.keySet();
String column = null, order = null;
if (parameterMap != null && parameterMap.containsKey(ORDER_COLUMN)) {
column = parameterMap.get(ORDER_COLUMN)[0];
}
if (parameterMap != null && parameterMap.containsKey(ORDER_TYPE)) {
order = parameterMap.get(ORDER_TYPE)[0];
}
log.info("排序规则>>列:" + column + ",排序方式:" + order);
if (DataBaseConstant.CREATE_TIME.equals(column) && !fieldColumnMap.containsKey(DataBaseConstant.CREATE_TIME)) {
column = "id";
log.warn("检测到实体里没有字段createTime，改成采用ID排序！");
}
if (oConvertUtils.isNotEmpty(column) && oConvertUtils.isNotEmpty(order)) {
if (column.endsWith(CommonConstant.DICT_TEXT_SUFFIX)) {
column = column.substring(0, column.lastIndexOf(CommonConstant.DICT_TEXT_SUFFIX));
}
log.debug("当前字段有：" + allFields);
if (!allColumnExist(column, allFields)) {
throw new RuntimeException("请注意，将要排序的列字段不存在：" + column);
}
if (column.contains(",")) {
List<String> columnList = Arrays.asList(column.split(","));
String columnStrNew = columnList.stream().map(c -> fieldColumnMap.get(c)).collect(Collectors.joining(","));
if (oConvertUtils.isNotEmpty(columnStrNew)) {
column = columnStrNew;
}
} else {
column = fieldColumnMap.get(column);
}
SqlInjectionUtil.filterContent(column);
if (order.toUpperCase().indexOf(ORDER_TYPE_ASC) >= 0) {
String columnStr = oConvertUtils.camelToUnderline(column);
String[] columnArray = columnStr.split(",");
queryWrapper.orderByAsc(Arrays.asList(columnArray));
} else {
String columnStr = oConvertUtils.camelToUnderline(column);
String[] columnArray = columnStr.split(",");
queryWrapper.orderByDesc(Arrays.asList(columnArray));
}
}
}
private static boolean allColumnExist(String columnStr, Set<String> allFields) {
boolean exist = true;
if (columnStr.indexOf(COMMA) >= 0) {
String[] arr = columnStr.split(COMMA);
for (String column : arr) {
if (!allFields.contains(column)) {
exist = false;
break;
}
}
} else {
exist = allFields.contains(columnStr);
}
return exist;
}
private static void doSuperQuery(QueryWrapper<?> queryWrapper, Map<String, String[]> parameterMap, Map<String, String> fieldColumnMap) {
if (parameterMap != null && parameterMap.containsKey(SUPER_QUERY_PARAMS)) {
String superQueryParams = parameterMap.get(SUPER_QUERY_PARAMS)[0];
String superQueryMatchType = parameterMap.get(SUPER_QUERY_MATCH_TYPE) != null ? parameterMap.get(SUPER_QUERY_MATCH_TYPE)[0] : MatchTypeEnum.AND.getValue();
MatchTypeEnum matchType = MatchTypeEnum.getByValue(superQueryMatchType);
try {
superQueryParams = URLDecoder.decode(superQueryParams, "UTF-8");
List<QueryCondition> conditions = JSON.parseArray(superQueryParams, QueryCondition.class);
if (conditions == null || conditions.size() == 0) {
return;
}
List<QueryCondition> filterConditions = conditions.stream().filter(
rule -> oConvertUtils.isNotEmpty(rule.getField())
&& oConvertUtils.isNotEmpty(rule.getRule())
&& oConvertUtils.isNotEmpty(rule.getVal())
).collect(Collectors.toList());
if (filterConditions.size() == 0) {
return;
}
log.info("---高级查询参数-->" + filterConditions);
queryWrapper.and(andWrapper -> {
for (int i = 0; i < filterConditions.size(); i++) {
QueryCondition rule = filterConditions.get(i);
if (oConvertUtils.isNotEmpty(rule.getField())
&& oConvertUtils.isNotEmpty(rule.getRule())
&& oConvertUtils.isNotEmpty(rule.getVal())) {
log.debug("SuperQuery ==> " + rule.toString());
Object queryValue = rule.getVal();
if ("date".equals(rule.getType())) {
queryValue = str2Date(rule.getVal(), date_sdf.get());
} else if ("datetime".equals(rule.getType())) {
queryValue = str2Date(rule.getVal(), datetimeFormat.get());
}
String dbType = rule.getDbType();
if (oConvertUtils.isNotEmpty(dbType)) {
try {
String valueStr = String.valueOf(queryValue);
switch (dbType.toLowerCase().trim()) {
case "int":
queryValue = Integer.parseInt(valueStr);
break;
case "bigdecimal":
queryValue = new BigDecimal(valueStr);
break;
case "short":
queryValue = Short.parseShort(valueStr);
break;
case "long":
queryValue = Long.parseLong(valueStr);
break;
case "float":
queryValue = Float.parseFloat(valueStr);
break;
case "double":
queryValue = Double.parseDouble(valueStr);
break;
case "boolean":
queryValue = Boolean.parseBoolean(valueStr);
break;
default:
}
} catch (Exception e) {
log.error("高级查询值转换失败：", e);
}
}
addEasyQuery(andWrapper, fieldColumnMap.get(rule.getField()), QueryRuleEnum.getByValue(rule.getRule()), queryValue);
if (MatchTypeEnum.OR == matchType && i < (filterConditions.size() - 1)) {
andWrapper.or();
}
}
}
});
} catch (UnsupportedEncodingException e) {
log.error("--高级查询参数转码失败：" + superQueryParams, e);
} catch (Exception e) {
log.error("--高级查询拼接失败：" + e.getMessage());
e.printStackTrace();
}
}
}
public static QueryRuleEnum convert2Rule(Object value) {
if (value == null) {
return QueryRuleEnum.EQ;
}
String val = (value + "").toString().trim();
if (val.length() == 0) {
return QueryRuleEnum.EQ;
}
QueryRuleEnum rule = null;
int length2 = 2;
int length3 = 3;
if (rule == null && val.length() >= length3) {
if (QUERY_SEPARATE_KEYWORD.equals(val.substring(length2, length3))) {
rule = QueryRuleEnum.getByValue(val.substring(0, 2));
}
}
if (rule == null && val.length() >= length2) {
if (QUERY_SEPARATE_KEYWORD.equals(val.substring(1, length2))) {
rule = QueryRuleEnum.getByValue(val.substring(0, 1));
}
}
if (rule == null && val.equals(STAR)) {
rule = QueryRuleEnum.EQ;
}
if (rule == null && val.contains(STAR)) {
if (val.startsWith(STAR) && val.endsWith(STAR)) {
rule = QueryRuleEnum.LIKE;
} else if (val.startsWith(STAR)) {
rule = QueryRuleEnum.LEFT_LIKE;
} else if (val.endsWith(STAR)) {
rule = QueryRuleEnum.RIGHT_LIKE;
}
}
if (rule == null && val.contains(COMMA)) {
rule = QueryRuleEnum.IN;
}
if (rule == null && val.startsWith(NOT_EQUAL)) {
rule = QueryRuleEnum.NE;
}
if (rule == null && val.indexOf(QUERY_COMMA_ESCAPE) > 0) {
rule = QueryRuleEnum.EQ_WITH_ADD;
}
if (rule == QueryRuleEnum.IN && val.indexOf(YYYY_MM_DD) >= 0 && val.indexOf(TO_DATE) >= 0) {
rule = QueryRuleEnum.EQ;
}
return rule != null ? rule : QueryRuleEnum.EQ;
}
private static Object replaceValue(QueryRuleEnum rule, Object value) {
if (rule == null) {
return null;
}
if (!(value instanceof String)) {
return value;
}
String val = (value + "").toString().trim();
if (QueryRuleEnum.EQ.getValue().equals(val)) {
return val;
}
if (rule == QueryRuleEnum.LIKE) {
value = val.substring(1, val.length() - 1);
value = specialStrConvert(value.toString());
} else if (rule == QueryRuleEnum.LEFT_LIKE || rule == QueryRuleEnum.NE) {
value = val.substring(1);
value = specialStrConvert(value.toString());
} else if (rule == QueryRuleEnum.RIGHT_LIKE) {
value = val.substring(0, val.length() - 1);
value = specialStrConvert(value.toString());
} else if (rule == QueryRuleEnum.IN) {
value = val.split(",");
} else if (rule == QueryRuleEnum.EQ_WITH_ADD) {
value = val.replaceAll("\\+\\+", COMMA);
} else {
if (val.startsWith(rule.getValue())) {
value = val.replaceFirst(rule.getValue(), "");
} else if (val.startsWith(rule.getCondition() + QUERY_SEPARATE_KEYWORD)) {
value = val.replaceFirst(rule.getCondition() + QUERY_SEPARATE_KEYWORD, "").trim();
}
}
return value;
}
private static void addQueryByRule(QueryWrapper<?> queryWrapper, String name, String type, String value, QueryRuleEnum rule) throws ParseException {
if (oConvertUtils.isNotEmpty(value)) {
if (value.contains(COMMA)) {
Object[] temp = Arrays.stream(value.split(COMMA)).map(v -> {
try {
return QueryGenerator.parseByType(v, type, rule);
} catch (ParseException e) {
e.printStackTrace();
return v;
}
}).toArray();
addEasyQuery(queryWrapper, name, rule, temp);
return;
}
Object temp = QueryGenerator.parseByType(value, type, rule);
addEasyQuery(queryWrapper, name, rule, temp);
}
}
private static Object parseByType(String value, String type, QueryRuleEnum rule) throws ParseException {
Object temp;
switch (type) {
case "class java.lang.Integer":
temp = Integer.parseInt(value);
break;
case "class java.math.BigDecimal":
temp = new BigDecimal(value);
break;
case "class java.lang.Short":
temp = Short.parseShort(value);
break;
case "class java.lang.Long":
temp = Long.parseLong(value);
break;
case "class java.lang.Float":
temp = Float.parseFloat(value);
break;
case "class java.lang.Double":
temp = Double.parseDouble(value);
break;
case "class java.util.Date":
temp = getDateQueryByRule(value, rule);
break;
default:
temp = value;
break;
}
return temp;
}
private static Date getDateQueryByRule(String value, QueryRuleEnum rule) throws ParseException {
Date date = null;
int length = 10;
if (value.length() == length) {
if (rule == QueryRuleEnum.GE) {
date = getTime().parse(value + " 00:00:00");
} else if (rule == QueryRuleEnum.LE) {
date = getTime().parse(value + " 23:59:59");
}
}
if (date == null) {
date = getTime().parse(value);
}
return date;
}
public static void addEasyQuery(QueryWrapper<?> queryWrapper, String name, QueryRuleEnum rule, Object value) {
if (value == null || rule == null || oConvertUtils.isEmpty(value)) {
return;
}
name = oConvertUtils.camelToUnderline(name);
log.info("---查询过滤器，Query规则---field:{}, rule:{}, value:{}", name, rule.getValue(), value);
switch (rule) {
case GT:
queryWrapper.gt(name, value);
break;
case GE:
queryWrapper.ge(name, value);
break;
case LT:
queryWrapper.lt(name, value);
break;
case LE:
queryWrapper.le(name, value);
break;
case EQ:
case EQ_WITH_ADD:
queryWrapper.eq(name, value);
break;
case NE:
queryWrapper.ne(name, value);
break;
case IN:
if (value instanceof String) {
queryWrapper.in(name, (Object[]) value.toString().split(COMMA));
} else if (value instanceof String[]) {
queryWrapper.in(name, (Object[]) value);
}
else if (value.getClass().isArray()) {
queryWrapper.in(name, (Object[]) value);
} else {
queryWrapper.in(name, value);
}
break;
case LIKE:
queryWrapper.like(name, value);
break;
case LEFT_LIKE:
queryWrapper.likeLeft(name, value);
break;
case RIGHT_LIKE:
queryWrapper.likeRight(name, value);
break;
default:
log.info("--查询规则未匹配到---");
break;
}
}
private static boolean judgedIsUselessField(String name) {
return "class".equals(name) || "ids".equals(name)
|| "page".equals(name) || "rows".equals(name)
|| "sort".equals(name) || "order".equals(name);
}
public static Map<String, SysPermissionDataRuleModel> getRuleMap() {
Map<String, SysPermissionDataRuleModel> ruleMap = new HashMap<>(5);
List<SysPermissionDataRuleModel> list = DataAutorUtils.loadDataSearchConditon();
if (list != null && list.size() > 0) {
if (list.get(0) == null) {
return ruleMap;
}
for (SysPermissionDataRuleModel rule : list) {
String column = rule.getRuleColumn();
if (QueryRuleEnum.SQL_RULES.getValue().equals(rule.getRuleConditions())) {
column = SQL_RULES_COLUMN + rule.getId();
}
ruleMap.put(column, rule);
}
}
return ruleMap;
}
private static void addRuleToQueryWrapper(SysPermissionDataRuleModel dataRule, String name, Class propertyType, QueryWrapper<?> queryWrapper) {
QueryRuleEnum rule = QueryRuleEnum.getByValue(dataRule.getRuleConditions());
if (rule.equals(QueryRuleEnum.IN) && !propertyType.equals(String.class)) {
String[] values = dataRule.getRuleValue().split(",");
Object[] objs = new Object[values.length];
for (int i = 0; i < values.length; i++) {
objs[i] = NumberUtils.parseNumber(values[i], propertyType);
}
addEasyQuery(queryWrapper, name, rule, objs);
} else {
if (propertyType.equals(String.class)) {
addEasyQuery(queryWrapper, name, rule, converRuleValue(dataRule.getRuleValue()));
} else if (propertyType.equals(Date.class)) {
String dateStr = converRuleValue(dataRule.getRuleValue());
int length = 10;
if (dateStr.length() == length) {
addEasyQuery(queryWrapper, name, rule, str2Date(dateStr, date_sdf.get()));
} else {
addEasyQuery(queryWrapper, name, rule, str2Date(dateStr, datetimeFormat.get()));
}
} else {
addEasyQuery(queryWrapper, name, rule, NumberUtils.parseNumber(dataRule.getRuleValue(), propertyType));
}
}
}
public static ThreadLocal<SimpleDateFormat> date_sdf = new ThreadLocal<SimpleDateFormat>() {
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat("yyyy-MM-dd");
}
};
public static ThreadLocal<SimpleDateFormat> datetimeFormat = new ThreadLocal<SimpleDateFormat>() {
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
};
public static Date str2Date(String str, SimpleDateFormat sdf) {
if (null == str || "".equals(str)) {
return null;
}
Date date = null;
try {
date = sdf.parse(str);
return date;
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
public static String converRuleValue(String ruleValue) {
String value = datetimeFormat.get().format(Calendar.getInstance().getTime());
return value != null ? value : ruleValue;
}
public static String trimSingleQuote(String ruleValue) {
if (oConvertUtils.isEmpty(ruleValue)) {
return "";
}
if (ruleValue.startsWith(QueryGenerator.SQL_SQ)) {
ruleValue = ruleValue.substring(1);
}
if (ruleValue.endsWith(QueryGenerator.SQL_SQ)) {
ruleValue = ruleValue.substring(0, ruleValue.length() - 1);
}
return ruleValue;
}
public static String getSqlRuleValue(String sqlRule) {
try {
Set<String> varParams = getSqlRuleParams(sqlRule);
for (String var : varParams) {
String tempValue = converRuleValue(var);
sqlRule = sqlRule.replace("#{" + var + "}", tempValue);
}
} catch (Exception e) {
log.error(e.getMessage(), e);
}
return sqlRule;
}
public static Set<String> getSqlRuleParams(String sql) {
if (oConvertUtils.isEmpty(sql)) {
return null;
}
Set<String> varParams = new HashSet<String>();
String regex = "\\#\\{\\w+\\}";
Pattern p = Pattern.compile(regex);
Matcher m = p.matcher(sql);
while (m.find()) {
String var = m.group();
varParams.add(var.substring(var.indexOf("{") + 1, var.indexOf("}")));
}
return varParams;
}
public static String getSingleQueryConditionSql(String field, String alias, Object value, boolean isString) {
return getSingleQueryConditionSql(field, alias, value, isString, null);
}
public static String getSingleQueryConditionSql(String field, String alias, Object value, boolean isString, String dataBaseType) {
if (value == null) {
return "";
}
field = alias + oConvertUtils.camelToUnderline(field);
QueryRuleEnum rule = QueryGenerator.convert2Rule(value);
return getSingleSqlByRule(rule, field, value, isString, dataBaseType);
}
private static String getSingleSqlByRule(QueryRuleEnum rule, String field, Object value, boolean isString, String dataBaseType) {
String res = "";
switch (rule) {
case GT:
res = field + rule.getValue() + getFieldConditionValue(value, isString, dataBaseType);
break;
case GE:
res = field + rule.getValue() + getFieldConditionValue(value, isString, dataBaseType);
break;
case LT:
res = field + rule.getValue() + getFieldConditionValue(value, isString, dataBaseType);
break;
case LE:
res = field + rule.getValue() + getFieldConditionValue(value, isString, dataBaseType);
break;
case EQ:
res = field + rule.getValue() + getFieldConditionValue(value, isString, dataBaseType);
break;
case EQ_WITH_ADD:
res = field + " = " + getFieldConditionValue(value, isString, dataBaseType);
break;
case NE:
res = field + " <> " + getFieldConditionValue(value, isString, dataBaseType);
break;
case IN:
res = field + " in " + getInConditionValue(value, isString);
break;
case LIKE:
res = field + " like " + getLikeConditionValue(value, QueryRuleEnum.LIKE);
break;
case LEFT_LIKE:
res = field + " like " + getLikeConditionValue(value, QueryRuleEnum.LEFT_LIKE);
break;
case RIGHT_LIKE:
res = field + " like " + getLikeConditionValue(value, QueryRuleEnum.RIGHT_LIKE);
break;
default:
res = field + " = " + getFieldConditionValue(value, isString, dataBaseType);
break;
}
return res;
}
private static String getSingleSqlByRule(QueryRuleEnum rule, String field, Object value, boolean isString) {
return getSingleSqlByRule(rule, field, value, isString, null);
}
private static String getFieldConditionValue(Object value, boolean isString, String dataBaseType) {
String str = value.toString().trim();
if (str.startsWith(SymbolConstant.EXCLAMATORY_MARK)) {
str = str.substring(1);
} else if (str.startsWith(QueryRuleEnum.GE.getValue())) {
str = str.substring(2);
} else if (str.startsWith(QueryRuleEnum.LE.getValue())) {
str = str.substring(2);
} else if (str.startsWith(QueryRuleEnum.GT.getValue())) {
str = str.substring(1);
} else if (str.startsWith(QueryRuleEnum.LT.getValue())) {
str = str.substring(1);
} else if (str.indexOf(QUERY_COMMA_ESCAPE) > 0) {
str = str.replaceAll("\\+\\+", COMMA);
}
if (dataBaseType == null) {
dataBaseType = getDbType();
}
if (isString) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(dataBaseType)) {
return " N'" + str + "' ";
} else {
return " '" + str + "' ";
}
} else {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(dataBaseType) && str.endsWith(SymbolConstant.SINGLE_QUOTATION_MARK) && str.startsWith(SymbolConstant.SINGLE_QUOTATION_MARK)) {
return " N" + str;
}
return value.toString();
}
}
private static String getInConditionValue(Object value, boolean isString) {
String[] temp = value.toString().split(",");
if (temp.length == 0) {
return "('')";
}
if (isString) {
List<String> res = new ArrayList<>();
for (String string : temp) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
res.add("N'" + string + "'");
} else {
res.add("'" + string + "'");
}
}
return "(" + String.join(",", res) + ")";
} else {
return "(" + value.toString() + ")";
}
}
private static String getLikeConditionValue(Object value, QueryRuleEnum ruleEnum) {
String str = value.toString().trim();
if (str.startsWith(SymbolConstant.ASTERISK) && str.endsWith(SymbolConstant.ASTERISK)) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
return "N'%" + str.substring(1, str.length() - 1) + "%'";
} else {
return "'%" + str.substring(1, str.length() - 1) + "%'";
}
} else if (str.startsWith(SymbolConstant.ASTERISK)) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
return "N'%" + str.substring(1) + "'";
} else {
return "'%" + str.substring(1) + "'";
}
} else if (str.endsWith(SymbolConstant.ASTERISK)) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
return "N'" + str.substring(0, str.length() - 1) + "%'";
} else {
return "'" + str.substring(0, str.length() - 1) + "%'";
}
} else {
if (str.indexOf(SymbolConstant.PERCENT_SIGN) >= 0) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
if (str.startsWith(SymbolConstant.SINGLE_QUOTATION_MARK) && str.endsWith(SymbolConstant.SINGLE_QUOTATION_MARK)) {
return "N" + str;
} else {
return "N" + "'" + str + "'";
}
} else {
if (str.startsWith(SymbolConstant.SINGLE_QUOTATION_MARK) && str.endsWith(SymbolConstant.SINGLE_QUOTATION_MARK)) {
return str;
} else {
return "'" + str + "'";
}
}
} else {
if (ruleEnum == QueryRuleEnum.LEFT_LIKE) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
return "N'%" + str + "'";
} else {
return "'%" + str + "'";
}
} else if (ruleEnum == QueryRuleEnum.RIGHT_LIKE) {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
return "N'" + str + "%'";
} else {
return "'" + str + "%'";
}
} else {
if (DataBaseConstant.DB_TYPE_SQLSERVER.equals(getDbType())) {
return "N'%" + str + "%'";
} else {
return "'%" + str + "%'";
}
}
}
}
}
@SuppressWarnings({"unchecked", "rawtypes"})
public static String installAuthJdbc(Class<?> clazz) {
StringBuffer sb = new StringBuffer();
Map<String, SysPermissionDataRuleModel> ruleMap = getRuleMap();
PropertyDescriptor[] origDescriptors = PropertyUtils.getPropertyDescriptors(clazz);
String sqlAnd = " and ";
for (String c : ruleMap.keySet()) {
if (oConvertUtils.isNotEmpty(c) && c.startsWith(SQL_RULES_COLUMN)) {
sb.append(sqlAnd + getSqlRuleValue(ruleMap.get(c).getRuleValue()));
}
}
String name, column;
for (int i = 0; i < origDescriptors.length; i++) {
name = origDescriptors[i].getName();
if (judgedIsUselessField(name)) {
continue;
}
if (ruleMap.containsKey(name)) {
column = getTableFieldName(clazz, name);
if (column == null) {
continue;
}
SysPermissionDataRuleModel dataRule = ruleMap.get(name);
QueryRuleEnum rule = QueryRuleEnum.getByValue(dataRule.getRuleConditions());
Class propType = origDescriptors[i].getPropertyType();
boolean isString = propType.equals(String.class);
Object value;
if (isString) {
value = converRuleValue(dataRule.getRuleValue());
} else {
value = NumberUtils.parseNumber(dataRule.getRuleValue(), propType);
}
String filedSql = getSingleSqlByRule(rule, oConvertUtils.camelToUnderline(column), value, isString);
sb.append(sqlAnd + filedSql);
}
}
log.info("query auth sql is:" + sb.toString());
return sb.toString();
}
public static void installAuthMplus(QueryWrapper<?> queryWrapper, Class<?> clazz) {
Map<String, SysPermissionDataRuleModel> ruleMap = getRuleMap();
PropertyDescriptor[] origDescriptors = PropertyUtils.getPropertyDescriptors(clazz);
for (String c : ruleMap.keySet()) {
if (oConvertUtils.isNotEmpty(c) && c.startsWith(SQL_RULES_COLUMN)) {
queryWrapper.and(i -> i.apply(getSqlRuleValue(ruleMap.get(c).getRuleValue())));
}
}
String name, column;
for (int i = 0; i < origDescriptors.length; i++) {
name = origDescriptors[i].getName();
if (judgedIsUselessField(name)) {
continue;
}
column = getTableFieldName(clazz, name);
if (column == null) {
continue;
}
if (ruleMap.containsKey(name)) {
addRuleToQueryWrapper(ruleMap.get(name), column, origDescriptors[i].getPropertyType(), queryWrapper);
}
}
}
public static String convertSystemVariables(String sql) {
return getSqlRuleValue(sql);
}
public static String getAllConfigAuth() {
StringBuffer sb = new StringBuffer();
Map<String, SysPermissionDataRuleModel> ruleMap = getRuleMap();
String sqlAnd = " and ";
for (String c : ruleMap.keySet()) {
SysPermissionDataRuleModel dataRule = ruleMap.get(c);
String ruleValue = dataRule.getRuleValue();
if (oConvertUtils.isEmpty(ruleValue)) {
continue;
}
if (oConvertUtils.isNotEmpty(c) && c.startsWith(SQL_RULES_COLUMN)) {
sb.append(sqlAnd + getSqlRuleValue(ruleValue));
} else {
boolean isString = false;
ruleValue = ruleValue.trim();
if (ruleValue.startsWith("'") && ruleValue.endsWith("'")) {
isString = true;
ruleValue = ruleValue.substring(1, ruleValue.length() - 1);
}
QueryRuleEnum rule = QueryRuleEnum.getByValue(dataRule.getRuleConditions());
String value = converRuleValue(ruleValue);
String filedSql = getSingleSqlByRule(rule, c, value, isString);
sb.append(sqlAnd + filedSql);
}
}
log.info("query auth sql is = " + sb.toString());
return sb.toString();
}
private static String getDbType() {
return CommonUtils.getDatabaseType();
}
private static List<Field> getClassFields(Class<?> clazz) {
List<Field> list = new ArrayList<Field>();
Field[] fields;
do {
fields = clazz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
list.add(fields[i]);
}
clazz = clazz.getSuperclass();
} while (clazz != Object.class && clazz != null);
return list;
}
private static String getTableFieldName(Class<?> clazz, String name) {
try {
Field field = null;
try {
field = clazz.getDeclaredField(name);
} catch (NoSuchFieldException e) {
}
if (field == null) {
List<Field> allFields = getClassFields(clazz);
List<Field> searchFields = allFields.stream().filter(a -> a.getName().equals(name)).collect(Collectors.toList());
if (searchFields != null && searchFields.size() > 0) {
field = searchFields.get(0);
}
}
if (field != null) {
TableField tableField = field.getAnnotation(TableField.class);
if (tableField != null) {
if (tableField.exist() == false) {
return null;
} else {
String column = tableField.value();
if (!"".equals(column)) {
return column;
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return name;
}
private static String specialStrConvert(String value) {
if (DataBaseConstant.DB_TYPE_MYSQL.equals(getDbType()) || DataBaseConstant.DB_TYPE_MARIADB.equals(getDbType())) {
String[] specialStr = QueryGenerator.LIKE_MYSQL_SPECIAL_STRS.split(",");
for (String str : specialStr) {
if (value.indexOf(str) != -1) {
value = value.replace(str, "\\" + str);
}
}
}
return value;
}
}
10
package com.hdx.contractor.util.query;
public enum QueryRuleEnum {
GT(">","gt","大于"),
GE(">=","ge","大于等于"),
LT("<","lt","小于"),
LE("<=","le","小于等于"),
EQ("=","eq","等于"),
NE("!=","ne","不等于"),
IN("IN","in","包含"),
LIKE("LIKE","like","全模糊"),
LEFT_LIKE("LEFT_LIKE","left_like","左模糊"),
RIGHT_LIKE("RIGHT_LIKE","right_like","右模糊"),
EQ_WITH_ADD("EQWITHADD","eq_with_add","带加号等于"),
LIKE_WITH_AND("LIKEWITHAND","like_with_and","多词模糊匹配————暂时未用上"),
SQL_RULES("USE_SQL_RULES","ext","自定义SQL片段");
private String value;
private String condition;
private String msg;
QueryRuleEnum(String value, String condition, String msg){
this.value = value;
this.condition = condition;
this.msg = msg;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public String getCondition() {
return condition;
}
public void setCondition(String condition) {
this.condition = condition;
}
public static QueryRuleEnum getByValue(String value){
if(oConvertUtils.isEmpty(value)) {
return null;
}
for(QueryRuleEnum val :values()){
if (val.getValue().equals(value) || val.getCondition().equals(value)){
return val;
}
}
return null;
}
}
11
package com.hdx.contractor.util.query;
import cn.hutool.crypto.SecureUtil;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class SqlInjectionUtil {
private final static String TABLE_DICT_SIGN_SALT = "20200501";
private final static String XSS_STR = "and |extractvalue|updatexml|exec |insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |;|or |+|user()";
private final static String REGULAR_EXPRE_USER = "user[\\s]*\\([\\s]*\\)";
private final static String SHOW_TABLES = "show\\s+tables";
private final static Pattern SQL_ANNOTATION = Pattern.compile("/\\*.*\\*/");
public static void checkDictTableSign(String dictCode, String sign, HttpServletRequest request) {
String accessToken = request.getHeader("X-Access-Token");
String signStr = dictCode + SqlInjectionUtil.TABLE_DICT_SIGN_SALT + accessToken;
String javaSign = SecureUtil.md5(signStr);
if (!javaSign.equals(sign)) {
log.error("表字典，SQL注入漏洞签名校验失败 ：" + sign + "!=" + javaSign+ ",dictCode=" + dictCode);
throw new RuntimeException("无权限访问！");
}
log.info(" 表字典，SQL注入漏洞签名校验成功！sign=" + sign + ",dictCode=" + dictCode);
}
public static void filterContent(String value) {
filterContent(value, null);
}
public static void filterContent(String value, String customXssString) {
if (value == null || "".equals(value)) {
return;
}
checkSqlAnnotation(value);
value = value.toLowerCase();
String[] xssArr = XSS_STR.split("\\|");
for (int i = 0; i < xssArr.length; i++) {
if (value.indexOf(xssArr[i]) > -1) {
log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
log.error("请注意，值可能存在SQL注入风险!---> {}", value);
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
}
if (customXssString != null) {
String[] xssArr2 = customXssString.split("\\|");
for (int i = 0; i < xssArr2.length; i++) {
if (value.indexOf(xssArr2[i]) > -1) {
log.error("请注意，存在SQL注入关键词---> {}", xssArr2[i]);
log.error("请注意，值可能存在SQL注入风险!---> {}", value);
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
}
}
if(Pattern.matches(SHOW_TABLES, value) || Pattern.matches(REGULAR_EXPRE_USER, value)){
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
return;
}
public static void filterContent(String[] values) {
filterContent(values, null);
}
public static void filterContent(String[] values, String customXssString) {
String[] xssArr = XSS_STR.split("\\|");
for (String value : values) {
if (value == null || "".equals(value)) {
return;
}
checkSqlAnnotation(value);
value = value.toLowerCase();
for (int i = 0; i < xssArr.length; i++) {
if (value.indexOf(xssArr[i]) > -1) {
log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
log.error("请注意，值可能存在SQL注入风险!---> {}", value);
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
}
if (customXssString != null) {
String[] xssArr2 = customXssString.split("\\|");
for (int i = 0; i < xssArr2.length; i++) {
if (value.indexOf(xssArr2[i]) > -1) {
log.error("请注意，存在SQL注入关键词---> {}", xssArr2[i]);
log.error("请注意，值可能存在SQL注入风险!---> {}", value);
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
}
}
if(Pattern.matches(SHOW_TABLES, value) || Pattern.matches(REGULAR_EXPRE_USER, value)){
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
}
return;
}
public static void specialFilterContentForDictSql(String value) {
String specialXssStr = " exec |extractvalue|updatexml| insert | select | delete | update | drop | count | chr | mid | master | truncate | char | declare |;|+|user()";
String[] xssArr = specialXssStr.split("\\|");
if (value == null || "".equals(value)) {
return;
}
checkSqlAnnotation(value);
value = value.toLowerCase();
for (int i = 0; i < xssArr.length; i++) {
if (value.indexOf(xssArr[i]) > -1 || value.startsWith(xssArr[i].trim())) {
log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
log.error("请注意，值可能存在SQL注入风险!---> {}", value);
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
}
if(Pattern.matches(SHOW_TABLES, value) || Pattern.matches(REGULAR_EXPRE_USER, value)){
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
return;
}
public static void specialFilterContentForOnlineReport(String value) {
String specialXssStr = " exec |extractvalue|updatexml| insert | delete | update | drop | chr | mid | master | truncate | char | declare |user()";
String[] xssArr = specialXssStr.split("\\|");
if (value == null || "".equals(value)) {
return;
}
checkSqlAnnotation(value);
value = value.toLowerCase();
for (int i = 0; i < xssArr.length; i++) {
if (value.indexOf(xssArr[i]) > -1 || value.startsWith(xssArr[i].trim())) {
log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
log.error("请注意，值可能存在SQL注入风险!---> {}", value);
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
}
if(Pattern.matches(SHOW_TABLES, value) || Pattern.matches(REGULAR_EXPRE_USER, value)){
throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
}
return;
}
public static boolean isClassField(String field, Class clazz){
Field[] fields = clazz.getDeclaredFields();
for(int i=0;i<fields.length;i++){
String fieldName = fields[i].getName();
String tableColumnName = oConvertUtils.camelToUnderline(fieldName);
if(fieldName.equalsIgnoreCase(field) || tableColumnName.equalsIgnoreCase(field)){
return true;
}
}
return false;
}
public static boolean isClassField(Set<String> fieldSet, Class clazz){
Field[] fields = clazz.getDeclaredFields();
for(String field: fieldSet){
boolean exist = false;
for(int i=0;i<fields.length;i++){
String fieldName = fields[i].getName();
String tableColumnName = oConvertUtils.camelToUnderline(fieldName);
if(fieldName.equalsIgnoreCase(field) || tableColumnName.equalsIgnoreCase(field)){
exist = true;
break;
}
}
if(!exist){
return false;
}
}
return true;
}
public static void checkSqlAnnotation(String str){
Matcher matcher = SQL_ANNOTATION.matcher(str);
if(matcher.find()){
String error = "请注意，值可能存在SQL注入风险---> \\*.*\\";
log.error(error);
throw new RuntimeException(error);
}
}
}
12
package com.hdx.contractor.util.query;
public class SymbolConstant {
public static final String SPOT = ".";
public static final String DOUBLE_BACKSLASH = "\\";
public static final String COLON = ":";
public static final String COMMA = ",";
public static final String LEFT_CURLY_BRACKET = "{";
public static final String RIGHT_CURLY_BRACKET = "}";
public static final String WELL_NUMBER = "#";
public static final String SINGLE_SLASH = "/";
public static final String DOUBLE_SLASH = "//";
public static final String EXCLAMATORY_MARK = "!";
public static final String UNDERLINE = "_";
public static final String SINGLE_QUOTATION_MARK = "'";
public static final String ASTERISK = "*";
public static final String PERCENT_SIGN = "%";
public static final String DOLLAR = "$";
public static final String AND = "&";
public static final String equals = "=";
public static final String SPOT_SINGLE_SLASH = "../";
public static final String SPOT_DOUBLE_BACKSLASH = "..\\";
public static final String SYS_VAR_PREFIX = "#{";
public static final String DOUBLE_LEFT_CURLY_BRACKET = "{{";
public static final String SQUARE_BRACKETS_LEFT = "[";
public static final String SQUARE_BRACKETS_RIGHT = "]";
}
13
package com.hdx.contractor.util.query;
import java.util.Date;
public class SysPermissionDataRuleModel {
private String id;
private String permissionId;
private String ruleName;
private String ruleColumn;
private String ruleConditions;
private String ruleValue;
private Date createTime;
private String createBy;
private Date updateTime;
private String updateBy;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPermissionId() {
return permissionId;
}
public void setPermissionId(String permissionId) {
this.permissionId = permissionId;
}
public String getRuleName() {
return ruleName;
}
public void setRuleName(String ruleName) {
this.ruleName = ruleName;
}
public String getRuleColumn() {
return ruleColumn;
}
public void setRuleColumn(String ruleColumn) {
this.ruleColumn = ruleColumn;
}
public String getRuleConditions() {
return ruleConditions;
}
public void setRuleConditions(String ruleConditions) {
this.ruleConditions = ruleConditions;
}
public String getRuleValue() {
return ruleValue;
}
public void setRuleValue(String ruleValue) {
this.ruleValue = ruleValue;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getCreateBy() {
return createBy;
}
public void setCreateBy(String createBy) {
this.createBy = createBy;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public String getUpdateBy() {
return updateBy;
}
public void setUpdateBy(String updateBy) {
this.updateBy = updateBy;
}
}
14
package com.hdx.contractor.util;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
@Component
public class SpringContextUtils implements ApplicationContextAware {
public static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext)
throws BeansException {
SpringContextUtils.applicationContext = applicationContext;
}
public static Object getBean(String name) {
return applicationContext.getBean(name);
}
public static <T> T getBean(Class<T> requiredType) {
return applicationContext.getBean(requiredType);
}
public static <T> T getBean(String name, Class<T> requiredType) {
return applicationContext.getBean(name, requiredType);
}
public static boolean containsBean(String name) {
return applicationContext.containsBean(name);
}
public static boolean isSingleton(String name) {
return applicationContext.isSingleton(name);
}
public static Class<? extends Object> getType(String name) {
return applicationContext.getType(name);
}
public static HttpServletRequest getHttpServletRequest() {
return ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
}
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
}
扫一扫
5483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
