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

下面是之前的代码片段:

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