使用基于注解的mybatis時,利用反射和注解生成sql語句

GladysStein 7年前發布 | 33K 次閱讀 SQL MyBatis MyBatis3 持久層框架

在開發時遇到一個問題，在使用基于注解的mybatis插入一個對象到mysql時，在寫sql語句時需要列出對象的所有屬性，所以在插入一個擁有10個以上屬性的對象時sql語句就會變得很長，寫起來也很不方便,也很容易拼錯。google了一下也沒有找到什么解決方式(可能是姿勢不對)，在stackoverflow上提的問題截止目前還沒有人回答。所以自己想了一個基于反射和注解的解決辦法

下面是之前的代碼片段:

@Insert("insert into poi_shop(name,brand,tags,status,phone,mobile,business_time,address,city,lng,lat,business_type,attribute_json) values(#{name},#{brand},#{tags},#{status},#{phone},#{mobile},#{business_time},#{address},#{city},#{lng},#{lat},#{business_type},#{attribute_json})")
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
public Long insertPoiInfo(PoiBo poiBo);

第一版(利用反射)

首先想到的是可以利用反射獲得對象的所有屬性，然后拼接成sql語句。所以寫了一個基于反射拼裝sql語句的方法，然后基于mybatis動態獲得sql語句的方式獲得完整的sql 具體的代碼如下:

接口層改為下面的樣子，sql語句的生成放到PoiSqlProvider的insertPoiBo方法中

@InsertProvider(type = PoiSqlProvider.class, method = "insertPoiBo")
public Long insertPoiInfo(@Param("poiBo")PoiBo poiBo);

PoiSqlProvider.class

public String insertPoiBo(Map<String,Object> map){
       PoiBo poiBo = (PoiBo)map.get("poiBo");
       StringBuilder sql = new StringBuilder("insert into poi_shop ");
       //get sql via reflection
       Map<String,String> sqlMap = getAllPropertiesForSql(poiBo, "poiBo");
       //
       sql.append(sqlMap.get("field")).append(sqlMap.get("value"));
       System.out.println(sql.toString());
       return sql.toString();

   }

//根據傳入的對象 基于反射生成兩部分sql語句
   private  Map<String,String> getAllPropertiesForSql(Object obj, String objName){

       Map<String,String> map = new HashMap<String,String>();
        if(null == obj) return map;
       StringBuilder filedSql = new StringBuilder("(");
       StringBuilder valueSql = new StringBuilder("value (");
       Field[] fields = obj.getClass().getDeclaredFields();
       for (int i = 0; i < fields.length; i++) {
           filedSql.append(fields[i].getName() + ",");
           valueSql.append("#{" + objName + "." + fields[i].getName() + "},");
       }

       //remove last ','
       valueSql.deleteCharAt(valueSql.length() - 1);
       filedSql.deleteCharAt(filedSql.length() - 1);
       valueSql.append(") ");
       filedSql.append(") ");
       map.put("field",filedSql.toString());
       map.put("value", valueSql.toString());

       System.out.println("database filed sql: " + filedSql.toString());
       System.out.println("value sql:" + valueSql.toString());

       return map;
   }

下面是基于反射生成的兩部分sq語句和最后拼接的語句

database filed sql:

(id,name,brand,tags,status,phone,mobile,business_time,address,city,lng,lat,business_type,attribute_json,updated_at,created_at)

value sql:

value(#{poiBo.id},#{poiBo.name},#{poiBo.brand},#{poiBo.tags},#{poiBo.status},#{poiBo.phone},#{poiBo.mobile},#{poiBo.business_time},#{poiBo.address},#{poiBo.city},#{poiBo.lng},#{poiBo.lat},#{poiBo.business_type},#{poiBo.attribute_json},#{poiBo.updated_at},#{poiBo.created_at}) 

insert into poi_shop (id,name,brand,tags,status,phone,mobile,business_time,address,city,lng,lat,business_type,attribute_json,updated_at,created_at) value (#{poiBo.id},#{poiBo.name},#{poiBo.brand},#{poiBo.tags},#{poiBo.status},#{poiBo.phone},#{poiBo.mobile},#{poiBo.business_time},#{poiBo.address},#{poiBo.city},#{poiBo.lng},#{poiBo.lat},#{poiBo.business_type},#{poiBo.attribute_json},#{poiBo.updated_at},#{poiBo.created_at})

要注意的是如果數據庫的字段名和插入對象的屬性名不一致，那么不能使用生成的database filed sql。

最終版(加入注解)

上面的getAllPropertiesForSql方法有個缺點，如果數據庫的字段名和類的屬性名不一致，就不能依靠反射獲得sql了。所以借鑒老大的ORM框架也寫了一個注解Column，用于model類的屬性上，表明屬性所對應數據庫字段。下面是Column注解的snippet。

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/* 定義字段的注解*/
@Retention(RetentionPolicy.RUNTIME)
/*該注解只能用在成員變量上*/
@Target(ElementType.FIELD)
public @interface Column {

    /**
     * 用來存放字段的名字 如果未指定列名，默認列名使用成員變量名
     *
     * @return
     */
    String name() default "";
    }

之后在model類屬性上加入對應的注解,省略getter和setter。Column的name為空時，代表屬性名和字段名一致。

public class PoiBo {

    @Column
    private Long id;
    @Column(name = "poi_name")
    private String name;//表示name屬性對應數據庫poi_name字段
    @Column(name = "poi_brand")
    private String brand;//表示brand屬性對應數據庫poi_brand字段
    @Column
    private String tags;
    @Column
    private Integer status;
    @Column
    private String phone;
    @Column
    private String mobile;
    @Column
    private String business_time;
    @Column
    private Float average_price;
    @Column
    private String address;
    @Column
    private String city;
    @Column
    private Double lng;
    @Column
    private Double lat;
    @Column
    private String business_type;
    @Column
    private String attribute_json;
    @Column
    private Timestamp updated_at;
    @Column
    private Timestamp created_at;
    }

修改getAllPropertiesForSql方法，通過獲取類屬性上的注解獲得數據庫字段名。

private  Map<String,String> getAllPropertiesForSql(Object obj, String objName){

        Map<String,String> map = new HashMap<String,String>();
         if(null == obj) return map;
        StringBuilder filedSql = new StringBuilder("(");
        StringBuilder valueSql = new StringBuilder("value (");
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
                // 判斷該成員變量上是不是存在Column類型的注解
                if (!field.isAnnotationPresent(Column.class)) {
                    continue;
                }

                Column c = field.getAnnotation(Column.class);// 獲取實例
                // 獲取元素值
                String columnName = c.name();
                // 如果未指定列名，默認列名使用成員變量名
                if ("".equals(columnName.trim())) {
                    columnName = field.getName();
                }

            filedSql.append(columnName + ",");
            valueSql.append("#{" + objName + "." + field.getName() + "},");
        }
        //remove last ','
        valueSql.deleteCharAt(valueSql.length() - 1);
        filedSql.deleteCharAt(filedSql.length() - 1);
        valueSql.append(") ");
        filedSql.append(") ");
        map.put("field",filedSql.toString());
        map.put("value", valueSql.toString());

        System.out.println("database filed sql: " + filedSql.toString());
        System.out.println("value sql:" + valueSql.toString());

        return map;
    }

利用反射+注解之后的輸出結果，可以看到sql語句正確按照name的Column注解的輸出了name屬性對應的數據庫字段是poi_name.

database filed sql: 

(id,poi_name,poi_brand,tags,status,phone,mobile,business_time,average_price,address,city,lng,lat,business_type,attribute_json,updated_at,created_at) 

value sql:
value(#{poiBo.id},#{poiBo.name},#{poiBo.brand},#{poiBo.tags},#{poiBo.status},#{poiBo.phone},#{poiBo.mobile},#{poiBo.business_time},#{poiBo.average_price},#{poiBo.address},#{poiBo.city},#{poiBo.lng},#{poiBo.lat},#{poiBo.business_type},#{poiBo.attribute_json},#{poiBo.updated_at},#{poiBo.created_at}) 

insert into poi_shop 
(id,poi_name,poi_brand,tags,status,phone,mobile,business_time,average_price,address,city,lng,lat,business_type,attribute_json,updated_at,created_at) value (#{poiBo.id},#{poiBo.name},#{poiBo.brand},#{poiBo.tags},#{poiBo.status},#{poiBo.phone},#{poiBo.mobile},#{poiBo.business_time},#{poiBo.average_price},#{poiBo.address},#{poiBo.city},#{poiBo.lng},#{poiBo.lat},#{poiBo.business_type},#{poiBo.attribute_json},#{poiBo.updated_at},#{poiBo.created_at})

來自：http://www.importnew.com/22918.html

本文由用戶 GladysStein 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1482999309675.html

SQL MyBatis MyBatis3 持久層框架

使用基于注解的mybatis時,利用反射和注解生成sql語句

第一版(利用反射)

最終版(加入注解)

相關經驗

相關資訊

相關文檔

目錄