cn.easyutil.project.base.sqlExecuter.SqlBuilder Maven / Gradle / Ivy

Go to download
package cn.easyutil.project.base.sqlExecuter;

import cn.easyutil.project.base.exception.CommonException;
import cn.easyutil.project.base.jdbc.QueryType;
import cn.easyutil.project.base.sqlExecuter.annotation.TableIgnore;
import cn.easyutil.project.base.sqlExecuter.annotation.TableName;
import cn.easyutil.project.base.sqlExecuter.annotation.TableField;
import cn.easyutil.project.base.sqlExecuter.annotation.TableId;
import cn.easyutil.util.javaUtil.ObjectUtil;
import cn.easyutil.util.javaUtil.StringUtil;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

/**
 * 通过注解生成sql
 */
public class SqlBuilder {

	/**
	 * 由传入的对象生成insert sql语句
	 * @return sql	数据库语句
	 * @throws Exception
	 */
	public static String buildInsertSql(Object parameterObject) throws Exception {
		if (null == parameterObject) {
            throw new RuntimeException("补充insert语句时发现传递参数为null");
        }

        boolean isSqlExcuter = false;
        SQLExecuter executer = null;
        Map fieldMap = null;
        Object bean = parameterObject;
        if(parameterObject instanceof SQLExecuter){
        	isSqlExcuter = true;
        	executer = (SQLExecuter) parameterObject;
        	bean = executer.getBean();
        	fieldMap = returnFieldMap(bean);
        }else{
        	fieldMap = returnFieldMap(parameterObject);
        }

        Class clazz = bean.getClass();

        String tableName = getTableName(clazz);

        StringBuffer tableSql = new StringBuffer();
        StringBuffer valueSql = new StringBuffer();

        tableSql.append("insert into ").append(tableName).append("(");
        valueSql.append("values(");

        boolean allFieldNull = true;
        // 根据字段注解和属性值联合生成sql语句
        for (String fieldName : fieldMap.keySet()) {
			FieldInfo info = fieldMap.get(fieldName);
            // 由于要根据字段对象值是否为空来判断是否将字段加入到sql语句中，因此DTO对象的属性不能是简单类型，反而必须是封装类型
            if (!ObjectUtil.isBaseObject(info.value)) {
                continue;
            }
            //主键不允许自己插入
            if(getTableIdJavaName(clazz).equals(fieldName)){
            	continue;
            }
            allFieldNull = false;
            Class type = ObjectUtil.getField(clazz, fieldName).getType();
            String jdbcType = parseTypeToJDBCType(type);
            String str = jdbcType != null ? ",jdbcType=" + jdbcType : "";
            String paramName = fieldName;
            if(isSqlExcuter){
            	paramName = "mybatisParams."+fieldName;
            	executer.getMybatisParams().put(fieldName, info.value);
            }
            tableSql.append("`" + getJDBCField(clazz, fieldName) + "`").append(",");
            valueSql.append("#{").append(paramName).append(str)
                    .append("},");
        }
        if (allFieldNull) {
            throw new RuntimeException("参数中所有基本类型的属性都为null");
        }
        tableSql.delete(tableSql.lastIndexOf(","), tableSql.lastIndexOf(",") + 1);
        valueSql.delete(valueSql.lastIndexOf(","), valueSql.lastIndexOf(",") + 1);
        if(isSqlExcuter){
        	return tableSql.append(") ").append(valueSql).append(")").append(" where 1=1 ").append(executer.getMybatisSql()).toString();
        }
        return tableSql.append(") ").append(valueSql).append(")").toString();
	}
	
	/**
	 * 将基本类型转换为jdbcType
	 * @param type
	 * @return
	 */
	private static String parseTypeToJDBCType(Class type) {
		if(type.isAssignableFrom(Integer.class)) {
			return "INTEGER";
		} if(type.isAssignableFrom(Long.class)) {
			return "INTEGER";
		} if(type.isAssignableFrom(Double.class)) {
			return "DOUBLE";
		} if(type.isAssignableFrom(Float.class)) {
			return "DOUBLE";
		}  if(type.isAssignableFrom(String.class)) {
			return "VARCHAR";
		} 
		return null; 
	}

	/**
	 * 由传入的对象生成update sql语句
	 * @return sql	数据库语句
	 * @throws Exception
	 */
	public static String buildUpdateSql(Object parameterObject) throws Exception {
		if (null == parameterObject) {
            throw new CommonException("补充update语句时发现传递参数为null");
        }
        boolean isSqlExcuter = false;
        SQLExecuter executer = null;
        Map fieldMap = null;
        Object bean = parameterObject;
        if(parameterObject instanceof SQLExecuter){
        	isSqlExcuter = true;
        	executer = (SQLExecuter) parameterObject;
        	bean = executer.getBean();
        	fieldMap = returnFieldMap(bean);
        }else{
        	fieldMap = returnFieldMap(parameterObject);
        }

        Class clazz = bean.getClass();

        String tableName = getTableName(clazz);

        StringBuffer tableSql = new StringBuffer();
        StringBuffer whereSql = new StringBuffer(" where 1=1 ");

        tableSql.append("update ").append(tableName).append(" set ");

        // 根据字段注解和属性值联合生成sql语句
        for (String fieldName : fieldMap.keySet()) {
			FieldInfo info = fieldMap.get(fieldName);
            // 由于要根据字段对象值是否为空来判断是否将字段加入到sql语句中，因此DTO对象的属性不能是简单类型，反而必须是封装类型
            if (!ObjectUtil.isBaseObject(info.value)) {
                continue;
            }
            if (fieldName.equals(getTableIdName(clazz))) {
                continue;
            }
            Class type = ObjectUtil.getField(clazz, fieldName).getType();
            String jdbcType = parseTypeToJDBCType(type);
            String str = jdbcType != null ? ",jdbcType=" + jdbcType : "";
            String paramName = fieldName;
            if(isSqlExcuter){
            	paramName = "mybatisParams."+fieldName;
            	executer.getMybatisParams().put(fieldName, info.value);
            }
            tableSql.append("`"+getJDBCField(clazz, fieldName)+"` ")
					.append(QueryType.equals.oper)
                    .append(" #{")
                    .append(paramName).append(str)
                    .append("},");
            
        }
        if(isSqlExcuter && !executer.getIncrMap().isEmpty()){
        	Set> set = executer.getIncrMap().entrySet();
        	for (Entry s : set) {
        		String flag = "+";
        		Object value = s.getValue();
        		if(new BigDecimal(value.toString()).intValue() <0 ){
        			flag = "";
        		}
        		tableSql.append(s.getKey())
                .append("=")
                .append(s.getKey()+flag+value)
                .append(",");
			}
        }
        if(isSqlExcuter && !executer.getNullValFields().isEmpty()){
            List nullValFields = executer.getNullValFields();
            for(String field : nullValFields){
                tableSql.append(" `"+field+"`=null, ");
            }
        }
        if(isSqlExcuter && executer.getMybatisSql().toString().equals("") && ObjectUtil.getAttributeValue(bean, getTableIdJavaName(clazz)) == null){
        	throw new CommonException("修改操作中，条件不能为空");
        }
        if (!isSqlExcuter && ObjectUtil.getAttributeValue(parameterObject, getTableIdJavaName(clazz)) == null) {
            throw new CommonException("修改操作中，主键不能为空");
        }
        tableSql.delete(tableSql.lastIndexOf(","), tableSql.lastIndexOf(",") + 1);
        String tableIdName = getTableIdName(clazz);
        String tableIdJavaName = getTableIdJavaName(clazz);
        if(isSqlExcuter){
        	whereSql.append(executer.getMybatisSql());
        	if(ObjectUtil.getAttributeValue(bean, tableIdJavaName) != null){
        		whereSql.append(" and "+tableIdName+"=#{mybatisParams."+tableIdJavaName+",jdbcType=INTEGER} ");
            	executer.getMybatisParams().put(tableIdJavaName, ObjectUtil.getAttributeValue(bean, tableIdName));
        	}
        }else if(ObjectUtil.getAttributeValue(parameterObject, tableIdJavaName) != null){
        	whereSql.append(" and "+tableIdName+"="+fieldMap.get(tableIdJavaName));
        }
        return tableSql.append(whereSql).toString();
	}

	/**
	 * 由传入的对象生成delete sql语句
	 * @return sql	数据库语句
	 * @throws Exception
	 */
	public static String buildDeleteSql(Object parameterObject) throws Exception {
		if (null == parameterObject) {
            throw new RuntimeException("补充delete语句时发现传递参数为null");
        }
        boolean isSqlExcuter = false;
        SQLExecuter executer = null;
        Map fieldMap = null;
        Object bean = parameterObject;
        if(parameterObject instanceof SQLExecuter){
        	isSqlExcuter = true;
        	executer = (SQLExecuter) parameterObject;
        	bean = executer.getBean();
        	fieldMap = returnFieldMap(bean);
        }else{
        	fieldMap = returnFieldMap(parameterObject);
        }

        Class clazz = bean.getClass();

        String tableName = getTableName(clazz);

        StringBuffer sql = new StringBuffer();
        sql.append("delete from ").append(tableName);

        StringBuffer whereSql = new StringBuffer();
        whereSql.append(" where 1=1 ");

        boolean allFieldNull = true;
        for (String fieldName : fieldMap.keySet()) {
			FieldInfo info = fieldMap.get(fieldName);
            // 由于要根据字段对象值是否为空来判断是否将字段加入到sql语句中，因此DTO对象的属性不能是简单类型，反而必须是封装类型
            if (!ObjectUtil.isBaseObject(info.value)) {
                continue;
            }
            allFieldNull = false;
            Class type = ObjectUtil.getField(clazz, fieldName).getType();
            String jdbcType = parseTypeToJDBCType(type);
            String str = jdbcType != null ? ",jdbcType=" + jdbcType : "";
            String paramName = fieldName;
            String jdbcField = getJDBCField(clazz, fieldName);
            if(isSqlExcuter){
            	paramName = "mybatisParams."+fieldName;
            	executer.getMybatisParams().put(fieldName, info.value);
            }

            whereSql.append(" and ").append("`"+jdbcField+"` ").append(QueryType.equals.oper).append(" #{")
            .append(paramName).append(str)
            .append("}");

        }
        if(isSqlExcuter){
        	whereSql.append(executer.getMybatisSql());
        	if (allFieldNull && executer.getMybatisSql().equals("")) {
        		throw new CommonException("参数中所有基本类型的属性都为null，不允许删除全表数据");
        	}
        }else if(allFieldNull){
        	throw new CommonException("参数中所有基本类型的属性都为null，不允许删除全表数据");
        }
        return sql.append(whereSql).toString();
	}

	/**
	 * 由传入的对象生成query sql语句
	 * 
	 * @return sql
	 * @throws Exception
	 */
	public static String buildSelectSql(Object parameterObject) throws Exception {

		SQLExecuter executer = null;
		boolean isSqlExcuter = false;
		boolean isparseExcuter = false;
		Map fieldMap = null;
		Object bean = parameterObject;
		if (parameterObject instanceof SQLExecuter) {
			executer = (SQLExecuter) parameterObject;
			bean = executer.getBean();
			isSqlExcuter = true;
			fieldMap = returnFieldMap(bean);
		} else {
			fieldMap = returnFieldMap(bean);
		}
		Class clazz = bean.getClass();

		String tableName = getTableName(clazz);

		StringBuffer selectSql = new StringBuffer();
		if (isSqlExcuter && !isparseExcuter) {
			if(executer.getReturnParam().toUpperCase().contains("COUNT")){
				ObjectUtil.setAttribute(executer, "returnParam", new StringBuffer());
			}
			selectSql.append(
					"select " + (executer.getReturnParam().length() == 0 ? " * " : executer.getReturnParam()) + "from ")
					.append(tableName);
		} else {
			selectSql.append("select * from ").append(tableName);
		}

		StringBuffer whereSql = new StringBuffer();
		// whereSql.append(" where deleted = 0 ");
		whereSql.append(" where 1=1 ");
		for (String fieldName : fieldMap.keySet()) {
			FieldInfo info = fieldMap.get(fieldName);
			// 由于要根据字段对象值是否为空来判断是否将字段加入到sql语句中，因此DTO对象的属性不能是简单类型，反而必须是封装类型
			if (!ObjectUtil.isBaseObject(info.value)) {
				continue;
			}
			
			Class type = ObjectUtil.getField(clazz,fieldName).getType();
			String jdbcType = parseTypeToJDBCType(type);
			String str = jdbcType != null ? ",jdbcType=" + jdbcType : "";
			String paramName = fieldName;
			String jdbcField = getJDBCField(clazz, fieldName);
			if (isSqlExcuter && !isparseExcuter) {
				paramName = "mybatisParams." + fieldName;
				executer.getMybatisParams().put(fieldName, processQueryValue(info.queryType, info.value));
			}
			whereSql.append(" and ").append("`" + jdbcField + "` ").append(info.queryType.oper).append(" #{")
					.append(paramName).append(str).append("}");
		}
		if (isSqlExcuter) {
			return selectSql.append(whereSql).append(executer.getMybatisSql()).toString();
		}
		return selectSql.append(whereSql).toString();
	}

	public static String buildGetSql(Object object) throws Exception {
		if (null == object) {
			throw new RuntimeException("补充get语句时发现传递参数为null");
		}

		String sql = buildSelectSql(object);
		if (sql != null) {
			sql = sql + " limit 1";
		}

		return sql;
	}
	
	/**
     * 返回对应的 实体类的 key -> value
     *
     * @param object
     * @return
     */
    public static Map returnFieldMap(Object object) {
        Map fieldMap = new HashMap<>();
        Map ignores = ObjectUtil.getFieldAnnotation(object.getClass(),TableIgnore.class);
        Map tempMap = ObjectUtil.getNotNullAttributes(object);
        for(String key:ignores.keySet()){
        	tempMap.remove(key);
        }
//        tempMap.forEach((k, v) -> fieldMap.put(StringUtil.conversionMapUnderscore(k), v));
        // 去除log字段 lombok 使用@Log4j @Slf4j 等会注入log变量
        if (fieldMap.containsKey("log")) {
            fieldMap.remove("log");
        }
        Map result = new HashMap<>();
		Set keys = tempMap.keySet();
		keys.forEach(key -> {
			FieldInfo info = new SqlBuilder().new FieldInfo();
			info.fieldName = key;
			info.value = tempMap.get(key);
			info.queryType = QueryType.equals;
			Map fieldAnnotation = ObjectUtil.getFieldAnnotation(object.getClass(), key, TableField.class);
			if(fieldAnnotation!=null && fieldAnnotation.get(key)!=null){
				TableField tableField = fieldAnnotation.get(key);
				info.queryType = tableField.queryType();
			}
			result.put(key, info);
		});

		return result;
    }
    
    /**
     * 根据java字段获取数据库的字段
     * @param clazz
     * @param fieldName
     * @return
     */
    public static String getJDBCField(Class clazz,String fieldName){
    	String jdbcField;
    	if(fieldName.contains("_")){
    		jdbcField = fieldName;
    	}else{
    		jdbcField = StringUtil.conversionMapUnderscore(fieldName);
    	}
    	Map fieldAnnotation = ObjectUtil.getFieldAnnotation(clazz, fieldName, TableField.class);
    	if(fieldAnnotation != null && !fieldAnnotation.isEmpty()){
			TableField tableField = fieldAnnotation.get(fieldName);
			if(tableField!=null && !StringUtil.isEmpty(tableField.value())){
    			jdbcField = tableField.value();
    		}
    	}
    	return jdbcField;
    }
    
    public static String getTableIdJavaName(Class clazz){
    	String name = "id";
    	Map fieldAnnotation = ObjectUtil.getFieldAnnotation(clazz,TableId.class);
    	if(fieldAnnotation==null || fieldAnnotation.isEmpty()){
    		return name;
    	}
    	name = fieldAnnotation.entrySet().iterator().next().getKey();
    	return name;
    }
    /**
     * 获取自增主键的数据库字段
     * @param clazz
     * @return
     */
    public static String getTableIdName(Class clazz){
    	String tableId = "id";
    	Map fieldAnnotation = ObjectUtil.getFieldAnnotation(clazz,TableId.class);
    	if(fieldAnnotation!=null && !fieldAnnotation.isEmpty()){
    		 Entry next = fieldAnnotation.entrySet().iterator().next();
    		 String key = next.getKey();
    		 Map tableField = ObjectUtil.getFieldAnnotation(clazz, key, TableField.class);
    		 if(tableField!=null && !tableField.isEmpty()){
    			 String value = tableField.entrySet().iterator().next().getValue().value();
    			 if(!StringUtil.isEmpty(value)){
    				 tableId = value;
    			 }
    		 }
    	}
    	return tableId;
    }
    /**
     * 获取表名
     * @param 
     *
     * @param clazz
     * @return
     */
    public static  String getTableName(Class clazz) {

        String tableName = clazz.getSimpleName();

        TableName table = (TableName) clazz.getAnnotation(TableName.class);
        if (table != null && !StringUtil.isEmpty(table.value())) {
        	tableName =table.value();
            tableName = "`" + tableName + "`";
        } else if (clazz.getSimpleName().contains("Bean") && tableName.equals(clazz.getSimpleName())) {
            tableName = "`" + StringUtil.conversionMapUnderscore(clazz.getSimpleName().substring(0, clazz.getSimpleName().indexOf("Bean"))) + "`";
        }

        return tableName;
    }


	private static Object processQueryValue(QueryType queryType,Object value){
		if(queryType == QueryType.leftLike){
			return value.toString()+"%";
		}
		if(queryType == QueryType.allLike){
			return "%"+value.toString()+"%";
		}
		return value;
	}
	/**
	 * 获取字段详细信息
	 */
	class FieldInfo{
    	String fieldName;
    	QueryType queryType;
    	Object value;
	}
}