利用反射跟自定義注解拼接實體對象的查詢SQL

　前言

　　項目中雖然有ORM映射框架來幫我們拼寫SQL，簡化開發過程，降低開發難度。但難免會出現需要自己拼寫SQL的情況，這里分享一個利用反射跟自定義注解拼接實體對象的查詢SQL的方法。

　　代碼

　　自定義注解：

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Like {

}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Between {

    /**
     * 最小值的實體屬性名
     */
    String min();

    /**
     * 最大值的實體屬性名
     */
    String max();
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface In {

    /**
     * in的具體集合的屬性名
     */
    String values();
}

　　實體對象：

@Data
@Entity
@Table(name="RES_LOG")
public class ResLog {
    @Id
    private String logId;
    private String resourceType;
    private String resourceId;
    @Like //開啟模糊查詢
    private String resourceName;
    private String resourceCode;
    @In(values="operationTypeList")//in查詢
    private String operationType;
    @Between(min="operationTimeStart", max="operationTimeEnd")//開啟區間查詢
    private Date operationTime;
    private String operatorId;
    private String operator;

    @Transient
    private Date operationTimeStart;
    @Transient
    private Date operationTimeEnd;
    @Transient
    private List<String> operationTypeList;

}

　　拼接SQL方法：

/**
 * 自動拼接原生SQL的“and”查詢條件,支持自定義注解：@Like @Between @In
 *
 * @param entity           實體對象
 * @param sql              待拼接SQL
 * @param ignoreProperties 忽略屬性
 */
public static void appendQueryColumns(Object entity, StringBuilder sql, String... ignoreProperties) {

    try {
        //忽略屬性
        List<String> ignoreList1=Arrays.asList(ignoreProperties);
        //默認忽略分頁參數
        List<String> ignoreList2=Arrays.asList("class", "pageable", "page", "rows", "sidx", "sord");
   
        //反射獲取Class的屬性（Field表示類中的成員變量）
        for (Field field : entity.getClass().getDeclaredFields()) {
            //獲取授權
            field.setAccessible(true);
            //屬性名稱
            String fieldName=field.getName();
            //屬性的值
            Object fieldValue=field.get(entity);
            //檢查Transient注解，是否忽略拼接
            if (!field.isAnnotationPresent(Transient.class)) {
                String column=new PropertyNamingStrategy.SnakeCaseStrategy().translate(fieldName).toLowerCase();
                //值是否為空
                if (!StringUtils.isEmpty(fieldValue)) {
                    //映射關系：對象屬性(駝峰)->數據庫字段(下劃線)
                    if (!ignoreList1.contains(fieldName) && !ignoreList2.contains(fieldName)) {
                        //開啟模糊查詢
                        if (field.isAnnotationPresent(Like.class)) {
                            sql.append(" and " + column + " like '%" + escapeSql(fieldValue) + "%'");
                        }
                        //開啟等值查詢
                        else {
                            sql.append(" and " + column + "='" + escapeSql(fieldValue) + "'");
                        }
                    }
                } else {
                    //開啟區間查詢
                    if (field.isAnnotationPresent(Between.class)) {
                        //獲取最小值
                        Field minField=entity.getClass().getDeclaredField(field.getAnnotation(Between.class).min());
                        minField.setAccessible(true);
                        Object minVal=minField.get(entity);
                        //獲取最大值
                        Field maxField=entity.getClass().getDeclaredField(field.getAnnotation(Between.class).max());
                        maxField.setAccessible(true);
                        Object maxVal=maxField.get(entity);
                        //開啟區間查詢
                        if (field.getType().getName().equals("java.util.Date")) {
                            if (!StringUtils.isEmpty(minVal)) {
                                sql.append(" and " + column + " > to_date( '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) minVal) + "','yyyy-mm-dd hh24:mi:ss')");
                            }
                            if (!StringUtils.isEmpty(maxVal)) {
                                sql.append(" and " + column + " < to_date( '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) maxVal) + "','yyyy-mm-dd hh24:mi:ss')");
                            }
                        }
                    }
                    
                    //開啟in查詢
                    if (field.isAnnotationPresent(In.class)) {
                        //獲取要in的值
                        Field values=entity.getClass().getDeclaredField(field.getAnnotation(In.class).values());
                        values.setAccessible(true);
                        List<String> valuesList=(List<String>) values.get(entity);
                        if (valuesList !=null && valuesList.size() > 0) {
                            String inValues="";
                            for (String value : valuesList) {
                                inValues=inValues + "'" + value + "'";
                            }
                            sql.append(" and " + column + " in (" + escapeSql(inValues) + ")");
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

　　2019-10-24補充：注意！我們這屬于動態拼寫SQL，需要進行轉義防范SQL注入！

/**
     * sql轉義
     */
    public static String escapeSql(String str) {
        if (str==null) {
            return null;
        }
        StringBuilder sb=new StringBuilder();
        for (int i=0; i < str.length(); i++) {
            char src=str.charAt(i);
            switch (src) {
                case '\'':
                    sb.append("''");// hibernate轉義多個單引號必須用兩個單引號
                    break;
                case '\"':
                case '\\':
                    sb.append('\\');
                default:
                    sb.append(src);
                    break;
            }
        }
        return sb.toString();
    }

　　測試與效果

public static void main(String[] args) {
    ResLog resLog=new ResLog();
    resLog.setLogId("id1");//等值查詢
    resLog.setResourceName("name1");//like查詢
    resLog.setOperationTimeStart(new Date());//日期區間查詢
    resLog.setOperationTimeEnd(new Date());
    ArrayList<String> list=new ArrayList<>();
    list.add("type1");
    list.add("type2");
    resLog.setOperationTypeList(list);//in查詢
    //在外面拼寫select * from 是為了多表聯查時的情況
    StringBuilder sql=new StringBuilder("select * from res_log where '1'='1'");
    appendQueryColumns(resLog,sql);
    System.out.println(sql.toString());
}

　　拼接結果：

select *
  from res_log
 where '1'='1'
   and log_id='id1'
   and resource_name like '%name1%'
   and operation_type in ('type1''type2')
   and operation_time >
       to_date('2018-10-08 15:00:40', 'yyyy-mm-dd hh24:mi:ss')
   and operation_time <
       to_date('2018-10-08 15:00:40', 'yyyy-mm-dd hh24:mi:ss')

　　后記

　　甚至我們可以直接獲取實體對象對應的表名，直接在方法里面拼出 select * from ，這樣就不需要在外面拼接這一句

//獲取實體對象對應的表名
String TableName=entity.getClass().getAnnotation(Table.class).name();
System.out.println(TableName);

　　為了優化SQL，一般我們不建議select * from，而是需要查詢那些字段就拼出那些字段，例如：select log_id from

　　但是如果數據表有一百個字段呢？一個個手動拼接就太傻了，因此寫了一個自動拼接字段的方法，支持配置忽略拼接的字段

/**
     *
     * @param entity 實體對象
     * @param ignoreProperties 動態參數  忽略拼接的字段
     * @return sql
     */
    public static StringBuilder appendFields(Object entity, String... ignoreProperties) {
        StringBuilder sql=new StringBuilder();
        List<String> ignoreList=Arrays.asList(ignoreProperties);
        try {
            sql.append("select ");

            for (Field field : entity.getClass().getDeclaredFields()) {
                //獲取授權
                field.setAccessible(true);
                String fieldName=field.getName();//屬性名稱
                Object fieldValue=field.get(entity);//屬性的值
                //非臨時字段、非忽略字段
                if (!field.isAnnotationPresent(Transient.class) && !ignoreList.contains(fieldName)) {
                    //拼接查詢字段  駝峰屬性轉下劃線
                    sql.append(new PropertyNamingStrategy.SnakeCaseStrategy().translate(fieldName).toLowerCase()).append(" ").append(",");
                }
            }
            //處理逗號（刪除最后一個字符）
            sql.deleteCharAt(sql.length() - 1);

            String tableName=entity.getClass().getAnnotation(Table.class).name();
            sql.append("from ").append(tableName).append(" where '1'='1' ");
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return sql;
    }

　　接著上面的main測試

public static void main(String[] args) {
    ResLog resLog=new ResLog();
    resLog.setLogId("id1");//等值查詢
    resLog.setResourceName("name1");//like查詢
    resLog.setOperationTimeStart(new Date());//日期區間查詢
    resLog.setOperationTimeEnd(new Date());
    ArrayList<String> list=new ArrayList<>();
    list.add("type1");
    list.add("type2");
    resLog.setOperationTypeList(list);//in查詢
    //動態拼接查詢字段
    StringBuilder sql=appendFields(resLog,"remark","operator");
    appendQueryColumns(resLog,sql);
    System.out.println(sql.toString());
}

　　結果

select log_id,
       resource_type,
       resource_id,
       resource_name,
       resource_code,
       operation_type,
       operation_time,
       operator_id
  from RES_LOG
 where '1'='1'
   and log_id='id1'
   and resource_name like '%name1%'
   and operation_type in ('type1''type2')
   and operation_time >
       to_date('2018-12-13 10:34:33', 'yyyy-MM-dd hh24:mi:ss')
   and operation_time <
       to_date('2018-12-13 10:34:33', 'yyyy-MM-dd hh24:mi:ss')

　　更新

　　2020-10-10更新

　　在之前的操作中，我們直接在與數據表映射的entity實體類中使用自定義注解，實體類負責與數據表進行映射，具有共有屬性，不應該被業務污染，而實體類對應Vo類負責接參、傳參等傳輸數據的責任，不同的業務沖突時可以創建多個Vo類來解決，正合適我們使用自定義注解來拼接SQL，因此，改成在Vo類中使用我們的自定義注解

　　首先，SqlUtil類需要進行一些調整，主要是對appendQueryColumns方法的調整，同時新增了一個拼寫全部SQL的聚合方法joinSqlByEntityAndVo

package cn.huanzi.qch.baseadmin.util;

import cn.huanzi.qch.baseadmin.annotation.Between;
import cn.huanzi.qch.baseadmin.annotation.In;
import cn.huanzi.qch.baseadmin.annotation.Like;
import cn.huanzi.qch.baseadmin.common.pojo.PageCondition;
import com.fasterxml.jackson.databind.PropertyNamingStrategy;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.annotation.Transient;
import org.springframework.util.StringUtils;

import javax.persistence.Table;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 拼接SQL工具類
 * 詳情請閱讀博客：https://www.cnblogs.com/huanzi-qch/p/9754846.html
 */
@Slf4j
public class SqlUtil {

    /**
     * 日期轉換格式
     */
    private static SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 數據庫驅動類，用于判斷數據庫類型
     * MySQL：com.mysql.cj.jdbc.driver（默認）
     * postgresql：org.postgresql.driver
     * Oracle：oracle.jdbc.oracledriver
     */
    @Value("${string.datasource.driver-class-name:com.mysql.cj.jdbc.Driver}")
    private static String sqlType;

    /**
     * 根據實體、Vo直接拼接全部SQL
     * @param entityClass 實體類
     * @param entityVo    繼承了PageCondition分頁條件的Vo類
     * @return sql
     */
    public static StringBuilder joinSqlByEntityAndVo(Class<?> entityClass,Object entityVo){
        //select 所有字段 from table
        StringBuilder sql=SqlUtil.appendFields(entityClass);

        //拼接查詢字段
        SqlUtil.appendQueryColumns(entityClass,entityVo,sql);

        //拼接排序字段
        SqlUtil.orderByColumn((PageCondition)entityVo,sql);

        return sql;
    }

    /**
     * 自動拼接原生SQL的“and”查詢條件,
     * 支持自定義注解，注解改成打在vo類中，不應該破壞公用的entity實體映射類：@Like @Between @In
     *
     * @param entityClass      實體類
     * @param entityVo         繼承了PageCondition分頁條件的Vo類
     * @param sql              待拼接SQL
     * @param ignoreProperties 忽略屬性
     */
    public static void appendQueryColumns(Class<?> entityClass, Object entityVo, StringBuilder sql, String... ignoreProperties) {
        try {

            List<String> ignoreList1=Arrays.asList(ignoreProperties);
            //默認忽略分頁參數
            List<String> ignoreList2=Arrays.asList("class", "pageable", "page", "rows", "sidx", "sord");

            //反射獲取Class的屬性（Field表示類中的成員變量）
            Class<?> entityVoClass=entityVo.getClass();

            //可以直接傳進來，也可以根據entityVoClass來創建entityClass，如果選擇動態拼接，對命名規則有一定要求
//            Class<?> entityClass=Class.forName(entityVoClass.getName().replaceFirst("Vo",""));

            for (Field field : entityVoClass.getDeclaredFields()) {
                //獲取授權
                field.setAccessible(true);
                //屬性名稱
                String fieldName=field.getName();
                //屬性的值
                Object fieldValue=field.get(entityVo);

                //檢查entity中是否也存在該字段，如果沒有，直接跳過
                try {
                    entityClass.getDeclaredField(fieldName);
                }catch (NoSuchFieldException e){
                    log.debug("entity中沒有這個字段，拼接查詢SQL直接跳過：" + e.getMessage());
                    continue;
                }

                String column=SqlUtil.translate(fieldName);

                //值是否為空
                if (!StringUtils.isEmpty(fieldValue)) {
                    //映射關系：對象屬性(駝峰)->數據庫字段(下劃線)
                    if (!ignoreList1.contains(fieldName) && !ignoreList2.contains(fieldName)) {
                        //開啟模糊查詢
                        if (field.isAnnotationPresent(Like.class)) {
                            sql.append(" and ").append(column).append(" like '%").append(SqlUtil.escapeSql((String) fieldValue)).append("%'");
                        }
                        //開啟等值查詢
                        else {
                            sql.append(" and ").append(column).append("='").append(SqlUtil.escapeSql((String) fieldValue)).append("'");
                        }
                    }
                } else {
                    //開啟區間查詢
                    if (field.isAnnotationPresent(Between.class)) {
                        //獲取最小值
                        Field minField=entityVoClass.getDeclaredField(field.getAnnotation(Between.class).min());
                        minField.setAccessible(true);
                        Object minVal=minField.get(entityVo);
                        //獲取最大值
                        Field maxField=entityVoClass.getDeclaredField(field.getAnnotation(Between.class).max());
                        maxField.setAccessible(true);
                        Object maxVal=maxField.get(entityVo);
                        //開啟區間查詢，需要使用對應的函數
                        if (field.getType().getName().equals("java.util.Date")) {
                            //MySQL
                            if(sqlType.toLowerCase().contains("com.mysql.cj.jdbc.driver")){
                                if (!StringUtils.isEmpty(minVal)) {
                                    sql.append(" and ").append(column).append(" > str_to_date( '").append(simpleDateFormat.format((Date) minVal)).append("','%Y-%m-%d %H:%i:%s')");
                                }
                                if (!StringUtils.isEmpty(maxVal)) {
                                    sql.append(" and ").append(column).append(" < str_to_date( '").append(simpleDateFormat.format((Date) maxVal)).append("','%Y-%m-%d %H:%i:%s')");
                                }
                            }
                            //postgresql
                            if(sqlType.toLowerCase().contains("org.postgresql.driver")){
                                if (!StringUtils.isEmpty(minVal)) {
                                    sql.append(" and ").append(column).append(" > cast('").append(simpleDateFormat.format((Date) minVal)).append("' as timestamp)");
                                }
                                if (!StringUtils.isEmpty(maxVal)) {
                                    sql.append(" and ").append(column).append(" < cast('").append(simpleDateFormat.format((Date) maxVal)).append("' as timestamp)");
                                }
                            }
                            //Oracle
                            if(sqlType.toLowerCase().contains("oracle.jdbc.oracledriver")){
                                if (!StringUtils.isEmpty(minVal)) {
                                    sql.append(" and ").append(column).append(" > to_date( '").append(simpleDateFormat.format((Date) minVal)).append("','yyyy-mm-dd hh24:mi:ss')");
                                }
                                if (!StringUtils.isEmpty(maxVal)) {
                                    sql.append(" and ").append(column).append(" < to_date( '").append(simpleDateFormat.format((Date) maxVal)).append("','yyyy-mm-dd hh24:mi:ss')");
                                }
                            }
                        }
                    }

                    //開啟in查詢
                    if (field.isAnnotationPresent(In.class)) {
                        //獲取要in的值
                        Field values=entityVoClass.getDeclaredField(field.getAnnotation(In.class).values());
                        values.setAccessible(true);
                        List<String> valuesList=(List<String>) values.get(entityVo);
                        if (valuesList !=null && valuesList.size() > 0) {
                            StringBuilder inValues=new StringBuilder();
                            for (int i=0; i < valuesList.size(); i++) {
                                inValues.append("'").append(SqlUtil.escapeSql(valuesList.get(i))).append("'");
                                if(i < valuesList.size()-1){
                                    inValues.append(",");
                                }
                            }

                            sql.append(" and ").append(column).append(" in (").append(inValues).append(")");
                        }
                    }
                }
            }
        } catch (Exception e) {
            //輸出到日志文件中
            log.error(ErrorUtil.errorInfoToString(e));
        }
    }

    /**
     *
     * @param entityClass 自動拼接實體類
     * @param ignoreProperties 動態參數  忽略拼接的字段
     * @return sql
     */
    public static StringBuilder appendFields(Class<?> entityClass, String... ignoreProperties) {
        StringBuilder sql=new StringBuilder();
        List<String> ignoreList=Arrays.asList(ignoreProperties);
        sql.append("select ");

        for (Field field : entityClass.getDeclaredFields()) {
            //獲取授權
            field.setAccessible(true);
            String fieldName=field.getName();//屬性名稱

            //非臨時字段、非忽略字段
            if (!field.isAnnotationPresent(Transient.class) && !ignoreList.contains(fieldName)) {
                //拼接查詢字段  駝峰屬性轉下劃線
                sql.append(SqlUtil.translate(fieldName)).append(" ").append(",");
            }
        }
        //處理逗號（刪除最后一個字符）
        sql.deleteCharAt(sql.length() - 1);

        String tableName=entityClass.getAnnotation(Table.class).name();
        sql.append("from ").append(tableName).append(" where '1'='1' ");
        return sql;
    }

    /**
     * 拼接排序SQL
     *
     * @param pageCondition 繼承了PageCondition分頁條件的Vo類
     * @param sql    待拼接的SQL
     */
    public static void orderByColumn(PageCondition pageCondition, StringBuilder sql) {
        String sidx=pageCondition.getSidx();
        String sord=pageCondition.getSord();

        if (!StringUtils.isEmpty(sidx)) {
            //1.獲取Bean
            BeanWrapper srcBean=new BeanWrapperImpl(pageCondition);
            //2.獲取Bean的屬性描述
            PropertyDescriptor[] pds=srcBean.getPropertyDescriptors();
            //3.獲取符合的排序字段名
            for (PropertyDescriptor p : pds) {
                String propertyName=p.getName();
                if (sidx.equals(propertyName)) {
                    sql.append(" order by ").append(translate(sidx)).append("desc".equalsIgnoreCase(sord) ? " desc" : " asc");
                }
            }
        }
    }

    /**
     * 實體屬性轉表字段，駝峰屬性轉下劃線，并全部轉小寫
     */
    private static String translate(String fieldName){
        return new PropertyNamingStrategy.SnakeCaseStrategy().translate(fieldName).toLowerCase();
    }

    /**
     * sql轉義
     * 動態拼寫SQL，需要進行轉義防范SQL注入！
     */
    private static String escapeSql(String str) {
        if (str==null) {
            return null;
        }
        StringBuilder sb=new StringBuilder();
        for (int i=0; i < str.length(); i++) {
            char src=str.charAt(i);
            switch (src) {
                case '\'':
                    sb.append("''");// hibernate轉義多個單引號必須用兩個單引號
                    break;
                case '\"':
                case '\\':
                    sb.append('\\');
                default:
                    sb.append(src);
                    break;
            }
        }
        return sb.toString();
    }
}

　　如何使用？比如我現在有entity映射類SysUser.java，以及對應的Vo類SysUserVo.java

　　entity，負責與數據表進行映射，具有共有屬性，不應該被業務污染

@Entity
@Table(name="sys_user")
@Data
public class SysUser implements Serializable {
    @Id
    private String userId;//用戶id

    private String loginName;//登錄名

    private String userName;//用戶名稱

    private String password;//登錄密碼

    private String valid;//軟刪除標識，Y/N

    private String limitedIp;//限制允許登錄的IP集合

    private Date expiredTime;//賬號失效時間，超過時間將不能登錄系統

    private Date lastChangePwdTime;//最近修改密碼時間，超出時間間隔，提示用戶修改密碼

    private String limitMultiLogin;//是否允許賬號同一個時刻多人在線，Y/N

    private Date createTime;//創建時間

    private Date updateTime;//修改時間

}

　　vo，負責傳輸數據，如接參、傳參，當一個vo不滿足多個業務需求時，可以新建多個vo類

@Data
public class SysUserVo extends PageCondition implements Serializable {

    @In(values="userIdList")//in查詢
    private String userId;//用戶id

    @Like
    private String loginName;//登錄名

    private String userName;//用戶名稱

    private String password;//登錄密碼

    private String valid;//軟刪除標識，Y/N

    private String limitedIp;//限制允許登錄的IP集合

    private Date expiredTime;//賬號失效時間，超過時間將不能登錄系統

    private Date lastChangePwdTime;//最近修改密碼時間，超出時間間隔，提示用戶修改密碼

    private String limitMultiLogin;//是否允許賬號同一個時刻多人在線，Y/N

    @Between(min="createTimeStart", max="createTimeEnd")//開啟區間查詢
    private Date createTime;//創建時間

    private Date updateTime;//修改時間

    private String oldPassword;//修改密碼時輸入的舊密碼

    private String newPassword;//修改密碼時輸入的新密碼

    private Date createTimeStart;
    private Date createTimeEnd;
    private List<String> userIdList;
}

　　main測試

public static void main(String[] args) {
        SqlUtil.sqlType="com.mysql.cj.jdbc.Driver";
        SysUserVo sysUserVo=new SysUserVo();
        //like查詢
        sysUserVo.setLoginName("張三");

        //日期區域查詢
        sysUserVo.setCreateTimeStart(new Date());
        sysUserVo.setCreateTimeEnd(new Date());

        //in查詢
        ArrayList<String> userIds=new ArrayList<>();
        userIds.add("1");
        userIds.add("2");
        sysUserVo.setUserIdList(userIds);

        //排序  asc desc
        sysUserVo.setSidx("createTime");
        sysUserVo.setSord("desc");

        //根據實體、Vo直接拼接全部SQL
        StringBuilder sql=SqlUtil.joinSqlByEntityAndVo(SysUser.class,sysUserVo);
        System.out.println(sql.toString());
    }

　　結果

15:10:21.457 [main] DEBUG cn.huanzi.qch.baseadmin.util.SqlUtil - entity中沒有這個字段，拼接查詢SQL直接跳過：oldPassword
15:10:21.467 [main] DEBUG cn.huanzi.qch.baseadmin.util.SqlUtil - entity中沒有這個字段，拼接查詢SQL直接跳過：newPassword
15:10:21.467 [main] DEBUG cn.huanzi.qch.baseadmin.util.SqlUtil - entity中沒有這個字段，拼接查詢SQL直接跳過：createTimeStart
15:10:21.467 [main] DEBUG cn.huanzi.qch.baseadmin.util.SqlUtil - entity中沒有這個字段，拼接查詢SQL直接跳過：createTimeEnd
15:10:21.467 [main] DEBUG cn.huanzi.qch.baseadmin.util.SqlUtil - entity中沒有這個字段，拼接查詢SQL直接跳過：userIdList
select user_id ,login_name ,user_name ,password ,valid ,limited_ip ,expired_time ,last_change_pwd_time ,limit_multi_login ,create_time ,update_time from sys_user where '1'='1'  and user_id in ('1','2') and login_name like '%張三%' and create_time > str_to_date( '2020-10-10 15:10:21','%Y-%m-%d %H:%i:%s') and create_time < str_to_date( '2020-10-10 15:10:21','%Y-%m-%d %H:%i:%s') order by create_time desc

　　美化后的SQL

　　業務沖突問題，場景重現：

　　A只要對loginName使用@Like查詢，其他全部是等值查詢，但是B想要對userName使用@Like查詢，其他全部是等值查詢，這時候兩者的業務需要體現在同一個Vo類中就沖突了，如果我們在entity映射類中使用自定義注解，這種沖突情況就不能很好地解決，因為映射類應當有且只有一個，但現在改成在Vo類中使用自定義注解，我們可以新建多個對應Vo類來解決這種沖突問題

　　代碼開源

　　注：本文的的代碼，在base-admin項目的SqlUtil.java工具類中

　　代碼已經開源、托管到我的GitHub、碼云：

　　GitHub：https://github.com/huanzi-qch/base-admin

　　碼云：https://gitee.com/huanzi-qch/base-admin

版權聲明

作者：huanzi-qch

出處：https://www.cnblogs.com/huanzi-qch

若標題中有“轉載”字樣，則本文版權歸原作者所有。若無轉載字樣，本文版權歸作者所有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文鏈接，否則保留追究法律責任的權利.

態SQL

官方文檔：https://mybatis.org/mybatis-3/zh/dynamic-sql.html

介紹

什么是動態SQL：動態SQL指的是根據不同的查詢條件 , 生成不同的Sql語句.

官網描述：
MyBatis 的強大特性之一便是它的動態 SQL。如果你有使用 JDBC 或其它類似框架的經驗，你
就能體會到根據不同條件拼接 SQL 語句的痛苦。例如拼接時要確保不能忘記添加必要的空格，還要注意
去掉列表最后一個列名的逗號。利用動態 SQL 這一特性可以徹底擺脫這種痛苦。
雖然在以前使用動態 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射語
句中的強大的動態 SQL 語言得以改進這種情形。
動態 SQL 元素和 JSTL 或基于類似 XML 的文本處理器相似。在 MyBatis 之前的版本中，有
很多元素需要花時間了解。MyBatis 3 大大精簡了元素種類，現在只需學習原來一半的元素便可。
MyBatis 采用功能強大的基于 OGNL 的表達式來淘汰其它大部分元素。
-------------------------------
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
-------------------------------

我們之前寫的 SQL 語句都比較簡單，如果有比較復雜的業務，我們需要寫復雜的 SQL 語句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引號，空格等缺失可能都會導致錯誤。

那么怎么去解決這個問題呢？這就要使用 mybatis 動態SQL，通過 if, choose, when, otherwise, trim, where, set, foreach等標簽，可組合成非常靈活的SQL語句，從而在提高 SQL 語句的準確性的同時，也大大提高了開發人員的效率。

搭建環境

新建一個數據庫表：blog

字段：id，title，author，create_time，views

CREATE TABLE `blog` (
`id` varchar(50) NOT NULL COMMENT '博客id',
`title` varchar(100) NOT NULL COMMENT '博客標題',
`author` varchar(30) NOT NULL COMMENT '博客作者',
`create_time` datetime NOT NULL COMMENT '創建時間',
`views` int(30) NOT NULL COMMENT '瀏覽量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

創建Mybatis基礎工程

IDutil工具類

public class IDUtil {
public static String genId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}

實體類編寫【注意set方法作用】

import java.util.Date;
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
//set，get....
}

編寫Mapper接口及xml文件

public interface BlogMapper {
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiaohan.mapper.BlogMapper">
</mapper>

mybatis核心配置文件，下劃線駝峰自動轉換

<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--注冊Mapper.xml-->
<mappers>
<mapper resource="mapper/BlogMapper.xml"/>
</mappers>

插入初始數據

編寫接口

//新增一個博客
int addBlog(Blog blog);

sql配置文件

<insert id="addBlog" parameterType="blog">
insert into blog (id, title, author, create_time, views)
values (#{id},#{title},#{author},#{createTime},#{views});
</insert>

初始化博客方法

@Test
public void addInitBlog(){
SqlSession session=MybatisUtils.getSession();
BlogMapper mapper=session.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setId(IDUtil.genId());
blog.setTitle("Mybatis如此簡單");
blog.setAuthor("小涵");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("Java如此簡單");
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("Spring如此簡單");
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("微服務如此簡單");
mapper.addBlog(blog);
session.close();
}

初始化數據完畢！接下來動態sql要來了，做好準備！！！

if 語句

需求：根據作者名字和博客名字來查詢博客！如果作者名字為空，那么只根據博客名字查詢，反之，則根據作者名來查詢

編寫接口類

//需求1
List<Blog> queryBlogIf(Map map);

編寫SQL語句

<!--需求1：
根據作者名字和博客名字來查詢博客！
如果作者名字為空，那么只根據博客名字查詢，反之，則根據作者名來查詢
select * from blog where title=#{title} and author=#{author}
-->
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog where
<if test="title !=null">
title=#{title}
</if>
<if test="author !=null">
and author=#{author}
</if>
</select>

測試

@Test
public void testQueryBlogIf(){
SqlSession session=MybatisUtils.getSession();
BlogMapper mapper=session.getMapper(BlogMapper.class);
HashMap<String, String> map=new HashMap<String, String>();
map.put("title","Mybatis如此簡單");
map.put("author","小涵");
List<Blog> blogs=mapper.queryBlogIf(map);
System.out.println(blogs);
session.close();
}

這樣寫我們可以看到，如果 author 等于 null，那么查詢語句為 select * from user where title=#{title}, 但是如果title為空呢？那么查詢語句為 select * from user where and author=#{author}，這是錯誤的 SQL 語句，如何解決呢？請看下面的 where 語句！

Where

修改上面的SQL語句；

<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title !=null">
title=#{title}
</if>
<if test="author !=null">
and author=#{author}
</if>
</where>
</select>

這個“where”標簽會知道如果它包含的標簽中有返回值的話，它就插入一個‘where’。此外，如果標簽返回的內容是以AND 或OR 開頭的，則它會剔除掉。【這是我們使用的最多的案例】

Set

同理，上面的對于查詢 SQL 語句包含 where 關鍵字，如果在進行更新操作的時候，含有 set 關鍵詞，我們怎么處理呢？

1. 編寫接口方法

int updateBlog(Map map);

2.sql配置文件

<!--注意set是用的逗號隔開-->
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title !=null">
title=#{title},
</if>
<if test="author !=null">
author=#{author}
</if>
</set>
where id=#{id};
</update>

3.測試

@Test
public void testUpdateBlog(){
SqlSession session=MybatisUtils.getSession();
BlogMapper mapper=session.getMapper(BlogMapper.class);
HashMap<String, String> map=new HashMap<String, String>();
map.put("title","動態SQL");
map.put("author","秦疆");
map.put("id","9d6a763f5e1347cebda43e2a32687a77");
mapper.updateBlog(map);
session.close();
}

choose語句

有時候，我們不想用到所有的查詢條件，只想選擇其中的一個，查詢條件有一個滿足即可，使用 choose 標簽可以解決此類問題，類似于 Java 的 switch 語句

1. 編寫接口方法

List<Blog> queryBlogChoose(Map map);

2. sql配置文件

<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="title !=null">
title=#{title}
</when>
<when test="author !=null">
and author=#{author}
</when>
<otherwise>
and views=#{views}
</otherwise>
</choose>
</where>
</select>

3. 測試類

@Test
public void testQueryBlogChoose(){
SqlSession session=MybatisUtils.getSession();
BlogMapper mapper=session.getMapper(BlogMapper.class);
HashMap<String, Object> map=new HashMap<String, Object>();
map.put("title","Java如此簡單");
map.put("author","小涵");
map.put("views",9999);
List<Blog> blogs=mapper.queryBlogChoose(map);
System.out.println(blogs);
session.close();
}

SQL片段

有時候可能某個 sql 語句我們用的特別多，為了增加代碼的重用性，簡化代碼，我們需要將這些代碼抽取出來，然后使用時直接調用。

提取SQL片段：

<sql id="if-title-author">
<if test="title !=null">
title=#{title}
</if>
<if test="author !=null">
and author=#{author}
</if>
</sql>

引用SQL片段：

<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace
-->
<include refid="if-title-author"></include>
<!-- 在這里還可以引用其他的 sql 片段 -->
</where>
</select>

注意：

①、最好基于單表來定義 sql 片段，提高片段的可重用性

②、在 sql 片段中不要包括 where

Foreach

將數據庫中前三個數據的id修改為1,2,3；

需求：我們需要查詢 blog 表中 id 分別為1,2,3的博客信息

1. 編寫接口

List<Blog> queryBlogForeach(Map map);

2. 編寫SQL語句

<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--
collection:指定輸入對象中的集合屬性
item:每次遍歷生成的對象
open:開始遍歷時的拼接字符串
close:結束時拼接的字符串
separator:遍歷對象之間需要拼接的字符串
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")"
separator="or">
id=#{id}
</foreach>
</where>
</select>

3. 測試

@Test
public void testQueryBlogForeach(){
SqlSession session=MybatisUtils.getSession();
BlogMapper mapper=session.getMapper(BlogMapper.class);
HashMap map=new HashMap();
List<Integer> ids=new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Blog> blogs=mapper.queryBlogForeach(map);
System.out.println(blogs);
session.close();
}

小結：其實動態 sql 語句的編寫往往就是一個拼接的問題，為了保證拼接準確，我們最好首先要寫原生的 sql 語句出來，然后在通過 mybatis 動態sql 對照著改，防止出錯。多在實踐中使用才是熟練掌握它的技巧

哈哈，終于總結完啦，以上就是mybatis基本動態sql的使用啦，后續還會持續更新動態sql的其他用法，如果對小涵的總結有不同的看法，直接評論就好啦！別忘了點波關注、轉發哦！

、概述

SQL是操作數據庫數據的結構化查詢語言，網頁的應用數據和后臺數據庫中的數據進行交互時會采用SQL。而SQL注入是將Web頁面的原URL、表單域或數據包輸入的參數，修改拼接成SQL語句，傳遞給Web服務器，進而傳給數據庫服務器以執行數據庫命令。如Web應用程序的開發人員對用戶所輸入的數據或cookie等內容不進行過濾或驗證(即存在注入點)就直接傳輸給數據庫，就可能導致拼接的SQL被執行，獲取對數據庫的信息以及提權，發生SQL注入攻擊。

SQL注入即是指web應用程序對用戶輸入數據的合法性沒有判斷或過濾不嚴，攻擊者可以在web應用程序中事先定義好的查詢語句的結尾上添加額外的SQL語句，在管理員不知情的情況下實現非法操作，以此來實現欺騙數據庫服務器執行非授權的任意查詢，從而進一步得到相應的數據信息。

SQL 注入漏洞（SQL Injection）是 Web 開發中最常見的一種安全漏洞。可以用它來從數據庫獲取敏感信息，或者利用數據庫的特性執行添加用戶，導出文件等一系列惡意操作，甚至有可能獲取數據庫乃至系統用戶最高權限。

而造成 SQL 注入的原因是因為程序沒有有效的轉義過濾用戶的輸入，使攻擊者成功的向服務器提交惡意的 SQL 查詢代碼，程序在接收后錯誤的將攻擊者的輸入作為查詢語句的一部分執行，導致原始的查詢邏輯被改變，額外的執行了攻擊者精心構造的惡意代碼。

很多 Web 開發者沒有意識到 SQL 查詢是可以被篡改的，從而把 SQL 查詢當作可信任的命令。殊不知，SQL 查詢是可以繞開訪問控制，從而繞過身份驗證和權限檢查的。更有甚者，有可能通過 SQL 查詢去運行主機系統級的命令。

二、特點

廣泛性

任何一個基于SQL語言的數據庫都可能被攻擊，很多開發人員在編寫Web應用程序時未對從輸入參數、Web表單、cookie等接受到的值進行規范性驗證和檢測，通常會出現SQL注入漏洞。

隱蔽性

SQL注入語句一般都嵌入在普通的HTTP請求中，很難與正常語句區分開，所以當前許多防火墻都無法識別予以警告，而且SQL注入變種極多，攻擊者可以調整攻擊的參數，所以使用傳統的方法防御SQL注入效果非常不理想。

危害大

攻擊者通過SQL注入獲取到服務器的庫名、表名、字段名，從而獲取到整個服務器中的數據，對網站用戶的數據安全有極大的威脅。攻擊者也可以通過獲取到的數據，得到后臺管理員的密碼，然后對網頁頁面進行惡意篡改。這樣不僅對數據庫信息安全造成嚴重威脅，對整個數據庫系統安全也影響重大。

操作方便

互聯網上有很多SQL注入工具，簡單易學，攻擊過程簡單，不需要專業知識也能自如運用。

三、SQL注入的原理

SQL注入攻擊是通過操作輸入來修改SQL語句，用以達到執行代碼對WEB服務器進行攻擊的方法。簡單的說就是在post/getweb表單、輸入域名或頁面請求的查詢字符串中插入SQL命令，最終使web服務器執行惡意命令的過程。在網站開發過程中，開發人員使用動態字符串構造SQL語句，用來創建所需的應用，這種情況下SQL語句在程序的執行過程中被動態的構造使用，可以根據不同的條件產生不同的SQL語句，比如需要根據不同的要求來查詢數據庫中的字段。這樣的開發過程其實為SQL注入攻擊留下了很多的可乘之機。

我們舉一個萬能鑰匙的例子來說明其原理：

考慮以下簡單的管理員登錄表單：

<form action="/login" method="POST">
    <p>Username: <input type="text" name="username" /></p>
    <p>Password: <input type="password" name="password" /></p>
    <p><input type="submit" value="登陸" /></p>
</form>

后端的 SQL 語句可能是如下這樣的：

let querySQL=`
    SELECT *
    FROM user
    WHERE username='${username}'
    AND psw='${password}'
`;
// 接下來就是執行 sql 語句...

目的就是來驗證用戶名和密碼是不是正確，按理說乍一看上面的 SQL 語句也沒什么毛病，確實是能夠達到我們的目的，可是你只是站在用戶會老老實實按照你的設計來輸入的角度來看問題，如果有一個惡意攻擊者輸入的用戶名是 admin' --，密碼隨意輸入，就可以直接登入系統了。WFT!----這就是SQL注入

我們之前預想的SQL 語句是：

SELECT * FROM user WHERE username='admin' AND psw='password'

但是惡意攻擊者用奇怪用戶名將你的 SQL 語句變成了如下形式：

SELECT * FROM user WHERE username='admin' --' AND psw='xxxx'

在 SQL 中,' --是閉合和注釋的意思，-- 是注釋后面的內容的意思，所以查詢語句就變成了：

SELECT * FROM user WHERE username='admin'

所謂的萬能密碼,本質上就是SQL注入的一種利用方式。惡意攻擊者不用我的密碼，就可以登錄進我的賬號，然后可以在里面為所欲為，然而這還只是最簡單的注入，牛逼的 SQL 注入高手甚至可以通過 SQL 查詢去運行主機系統級的命令，將你主機里的內容一覽無余，這里我也沒有這個能力講解的太深入，畢竟不是專業研究這類攻擊的，但是通過以上的例子，已經了解了 SQL 注入的原理，我們基本已經能找到防御 SQL 注入的方案了。

一次SQL注入的過程包括以下幾個過程：

獲取用戶請求參數
拼接到代碼當中
SQL語句按照我們構造參數的語義執行成功

SQL注入的必備條件：

1.可以控制輸入的數據

2.服務器要執行的代碼拼接了控制的數據。

我們會發現SQL注入流程中與正常請求服務器類似，只是黑客控制了數據，構造了SQL查詢，而正常的請求不會SQL查詢這一步，SQL注入的本質:數據和代碼未分離，即數據當做了代碼來執行。

四、攻擊特點

SQL注入攻擊是目前web應用網絡攻擊中最常見的手段之一，安全風險較高，在一定程度上超過緩沖區溢出漏洞，而市場上的防火墻又不能對SQL注入漏洞進行有效的檢測和防范。防火墻為了使正常網絡應用程序訪問服務器端的數據，必須允許從互聯網到Web服務器的正向連接，因此一旦web網絡應用程序存在注入漏洞，攻擊者就可以獲取訪問數據庫的權利進而獲得數據庫所在服務器的訪問權在某些情況下，SQL注入攻擊的風險要高于緩沖區溢出漏洞等所有其他漏洞。SQL注入攻擊普遍存在范圍廣、實現容易、破壞性大等特點。 [8]

SQL注入攻擊者在HTTP請求中輸入含有惡意構造且語法合法的SQL語句，只要應用程序中沒有做嚴格的處理（例如校驗或預拼接），那么就會出現SQL注入漏洞危險，目前以PHP、Perl、Cold Fusion Management等技術與Oracle、SQLServer、Sybase、DB2等數據管理系統相結合的Web應用程序都發現有SQL注入漏洞。 [8]

SQL注入技術公布后不久，互聯網上出現了很多例如教主的HDSI、NBSI、明小子的Domain等SQL注入工具，對那些存在SQL注入的網站以及Web應用程序進行攻擊，很容易就可以獲取其服務器的控制權。

五、危害

①獲取數據庫信息

管理員后臺用戶名和密碼
獲取其他數據庫敏感信息：用戶名、密碼、手機號碼、身份證、銀行卡信息……
整個數據庫：脫褲

②獲取服務器權限

③植入Webshell，獲取服務器后門

④讀取服務器敏感文件

六、如何防御

嚴格限制Web應用的數據庫的操作權限，給此用戶提供僅僅能夠滿足其工作的最低權限，從而最大限度的減少注入攻擊對數據庫的危害
后端代碼檢查輸入的數據是否符合預期，嚴格限制變量的類型，例如使用正則表達式進行一些匹配處理。
對進入數據庫的特殊字符（'，"，\，<，>，&，*，; 等）進行轉義處理，或編碼轉換。基本上所有的后端語言都有對字符串進行轉義處理的方法，比如 lodash 的 lodash._escapehtmlchar 庫。
所有的查詢語句建議使用數據庫提供的參數化查詢接口，參數化的語句使用參數而不是將用戶輸入變量嵌入到 SQL 語句中，即不要直接拼接 SQL 語句。例如 Node.js 中的 mysqljs 庫的 query 方法中的 ? 占位參數。

mysql.query(`SELECT * FROM user WHERE username=? AND psw=?`, [username, psw]);

在應用發布之前建議使用專業的 SQL 注入檢測工具進行檢測，以及時修補被發現的 SQL 注入漏洞。網上有很多這方面的開源工具，例如 sqlmap、SQLninja 等。
避免網站打印出 SQL 錯誤信息，比如類型錯誤、字段不匹配等，把代碼里的 SQL 語句暴露出來，以防止攻擊者利用這些錯誤信息進行 SQL 注入。
不要過于細化返回的錯誤信息，如果目的是方便調試，就去使用后端日志，不要在接口上過多的暴露出錯信息，畢竟真正的用戶不關心太多的技術細節，只要話術合理就行。

碰到要操作的數據庫的代碼，一定要慎重，小心使得萬年船，多找幾個人多來幾次 code review，將問題都暴露出來，而且要善于利用工具，操作數據庫相關的代碼屬于機密，沒事不要去各種論壇曬自家站點的 SQL 語句，萬一被人盯上了呢？

參考：

https://juejin.cn/post/6844903772930441230

https://zoumiaojiang.com/article/common-web-security/

在線咨詢

上一篇：蘋果Safari瀏覽器支持“垂直表單控件”，可實現語
下一篇：為什么視頻網站的視頻鏈接地址是blob

您的項目需求

*請認真填寫需求信息，我們會在24小時內與您取得聯系。

整合營銷服務商

利用反射跟自定義注解拼接實體對象的查詢SQL

前言

代碼

測試與效果

后記

更新

代碼開源

版權聲明

態SQL

介紹

搭建環境

創建Mybatis基礎工程

IDutil工具類

實體類編寫 【注意set方法作用】

編寫Mapper接口及xml文件

mybatis核心配置文件，下劃線駝峰自動轉換

插入初始數據

sql配置文件

初始化博客方法

if 語句

Where

Set

choose語句

SQL片段

Foreach

您的項目需求

　前言

　　代碼

　　測試與效果

　　后記

　　更新

　　代碼開源

實體類編寫【注意set方法作用】