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

在开发时遇到一个问题，在使用基于注解的mybatis插入一个对象到mysql时，在写sql语句时需要列出对象的所有属性，所以在插入一个拥有10个以上属性的对象时sql语句就会变得很长，写起来也很不方便,也很容易拼错。google了一下也没有找到什么解决方式(可能是姿势不对)，在stackoverflow上提的问题截止目前还没有人回答。所以自己想了一个基于反射和注解的解决办法
git地址：

giraffe0813

spring-mybatis-utils

a555c9 , up-to-date

下面是之前的代码片段:

1

2

3

@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")

publicLong insertPoiInfo(PoiBo poiBo);

第一版(利用反射)

首先想到的是可以利用反射获得对象的所有属性，然后拼接成sql语句。所以写了一个基于反射拼装sql语句的方法，然后基于mybatis动态获得sql语句的方式 获得完整的sql 具体的代码如下:
接口层改为下面的样子，sql语句的生成放到PoiSqlProvider的insertPoiBo方法中

1

2

@InsertProvider(type = PoiSqlProvider.class, method = "insertPoiBo")

publicLong insertPoiInfo(@Param("poiBo")PoiBo poiBo);

PoiSqlProvider.class

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

  publicString insertPoiBo(Map<String,Object> map){

       PoiBo poiBo = (PoiBo)map.get("poiBo");

       StringBuilder sql = newStringBuilder("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());

       returnsql.toString();

 

   }

 

//根据传入的对象 基于反射生成两部分sql语句

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

 

       Map<String,String> map = newHashMap<String,String>();

        if(null== obj) returnmap;

       StringBuilder filedSql = newStringBuilder("(");

       StringBuilder valueSql = newStringBuilder("value (");

       Field[] fields = obj.getClass().getDeclaredFields();

       for(inti = 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());

 

       returnmap;

   }

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

1

2

3

4

5

6

7

8

9

databasefiled 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})

 

insertinto 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。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

importjava.lang.annotation.ElementType;

importjava.lang.annotation.Retention;

importjava.lang.annotation.RetentionPolicy;

importjava.lang.annotation.Target;

 

/* 定义字段的注解*/

@Retention(RetentionPolicy.RUNTIME)

/*该注解只能用在成员变量上*/

@Target(ElementType.FIELD)

public @interface Column {

 

    /**

     * 用来存放字段的名字 如果未指定列名，默认列名使用成员变量名

     *

     * @return

     */

    String name() default"";

    }

之后在model类属性上加入对应的注解,省略getter和setter。Column的name为空时，代表属性名和字段名一致。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

publicclass PoiBo {

 

    @Column

    privateLong id;

    @Column(name = "poi_name")

    privateString name;//表示name属性对应数据库poi_name字段

    @Column(name = "poi_brand")

    privateString brand;//表示brand属性对应数据库poi_brand字段

    @Column

    privateString tags;

    @Column

    privateInteger status;

    @Column

    privateString phone;

    @Column

    privateString mobile;

    @Column

    privateString business_time;

    @Column

    privateFloat average_price;

    @Column

    privateString address;

    @Column

    privateString city;

    @Column

    privateDouble lng;

    @Column

    privateDouble lat;

    @Column

    privateString business_type;

    @Column

    privateString attribute_json;

    @Column

    privateTimestamp updated_at;

    @Column

    privateTimestamp created_at;

    }

修改getAllPropertiesForSql方法，通过获取类属性上的注解获得数据库字段名。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

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

 

        Map<String,String> map = newHashMap<String,String>();

         if(null== obj) returnmap;

        StringBuilder filedSql = newStringBuilder("(");

        StringBuilder valueSql = newStringBuilder("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());

 

        returnmap;

    }

利用反射+注解之后的输出结果，可以看到sql语句正确按照name的Column注解的输出了name属性对应的数据库字段是poi_name.

1

2

3

4

5

6

7

8

9

databasefiled 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})

 

insertinto 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})

原文出处： Giraffe

from: http://www.importnew.com/22918.html