1. SQL查询的统一抽象

MyBatis制动动态SQL的构造,利用动态SQL和自定义的参数Bean抽象,可以将绝大部分SQL查询抽象为一个统一接口,查询参数使用一个自定义bean继承Map,使用映

射的方法构造多查询参数.在遇到多属性参数(例如order by,其参数包括列名,升序降序类型,以及可以多个列及升降序类型凭借在order by之后)无法使用简单的

key-value表示时,可以将参数单独抽象为一个类.

将要用到的bean

package com.xxx.mybatistask.bean;
import com.xxx.mybatistask.support.jsonSerializer.JsonDateDeserializer;
import com.xxx.mybatistask.support.jsonSerializer.JsonDateSerializer;
import org.codehaus.jackson.map.annotate.JsonDeserialize;
import org.codehaus.jackson.map.annotate.JsonSerialize;
import java.util.Date;
public class Post {    
private int id;    
private String title;    
private String content;   
private String author;    
private PostStatus status;    
private Date created;    
public int getId() {        
return id;
    }    
public void setId(int id) {        
this.id = id;
    }    
public String getTitle() {        
return title;
    }    
public void setTitle(String title) {        
this.title = title;
    }   
public String getContent() {        
return content;
    }    
public void setContent(String content) {        
this.content = content;
    }    
public String getAuthor() {        
return author;
    }    
public void setAuthor(String author) {        
this.author = author;
    }    
public PostStatus getStatus() {        
return status;
    }    
public void setStatus(PostStatus status) {        
this.status = status;
    }

@JsonSerialize(using = JsonDateSerializer.class)    
public Date getCreated() {        
return created;
    }
@JsonDeserialize(using = JsonDateDeserializer.class)   、
 public void setCreated(Date created) {        
 this.created = created;
    }
}

1)参数Bean设计

总的参数Map抽象接口设计

package com.xxx.mybatistask.bean.query;
import java.util.Map;
public interface QueryParam extends Map<String, Object> {    /**
     * 新增查询参数
     *
     * @param key   参数名
     * @param value 参数值
     * @return
     */
    QueryParam fill(String key, Object value);
}

列表查询参数接口

package com.xxx.mybatistask.bean.query;
import java.util.List;
public interface ListQueryParam extends QueryParam {    /**
     * 获取排序条件集合
     *
     * @return
     */
    List<SortCond> getSortCond();    /**
     * 添加排序条件
     *
     * @param sortCond     */
    void addSortCond(SortCond sortCond);    void addSortCond(List<SortCond> sortCondList);    /**
     * 获取当前页数
     *
     * @return
     */
    Integer getPage();    /**
     * 获取每页查询记录数
     *
     * @return
     */
    Integer getPageSize();    /**
     * 设置当前页数     */
    void setPage(Integer page);    /**
     * 设置每页查询记录数     */
    void setPageSize(Integer pageSize);
}

列表查询参数接口实现

package com.xxx.mybatistask.bean.query;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
public class GenericQueryParam extends LinkedHashMap<String, Object> implements ListQueryParam {    /**
     * 最大单页记录数     */
    public final static int MAX_PAGE_SIZE = 100;    /**
     * 当前页面key     */
    private final static String PAGE_KEY = "__page";    /**
     * 单页记录数key     */
    private final static String PAGESIZE_KEY = "__pagesize";    /**
     * 排序参数List key     */
    private final static String SORTCOND_KEY = "__sortcond";    public GenericQueryParam() {        this(1, 10);
    }    public GenericQueryParam(
            Integer page,
            Integer pageSize
    ) {
        setPage(page);
        setPageSize(pageSize);
    }

    @Override    public Integer getPage() {        return (Integer) get(PAGE_KEY);
    }

    @Override    public Integer getPageSize() {        return (Integer) get(PAGESIZE_KEY);
    }

    @Override    public void setPage(Integer page) {
        put(PAGE_KEY, page);
    }

    @Override    public void setPageSize(Integer pageSize) {
        put(PAGESIZE_KEY, pageSize);
    }

    @Override
    @SuppressWarnings("unchecked")    public List<SortCond> getSortCond() {
        List<SortCond> sortCondList = (List<SortCond>) get(SORTCOND_KEY);        if (sortCondList == null) {
            sortCondList = new LinkedList<SortCond>();
            put(SORTCOND_KEY, sortCondList);
        }        return sortCondList;
    }

    @Override
    @SuppressWarnings("unchecked")    public void addSortCond(SortCond sortCond) {
        List<SortCond> sortCondList = (List<SortCond>) get(SORTCOND_KEY);        if (sortCondList == null) {
            sortCondList = new LinkedList<SortCond>();
            put(SORTCOND_KEY, sortCondList);
        }

        sortCondList.add(sortCond);
    }

    @Override    public void addSortCond(List<SortCond> sortCondList) {        for (SortCond sortCond : sortCondList) addSortCond(sortCond);
    }

    @Override    public QueryParam fill(String key, Object value) {
        put(key, value);        return this;
    }
}

排序参数的抽象

package com.xxx.mybatistask.bean.query;public class SortCond {    /**
     * 排序类型枚举     */
    public enum Order {
        ASC, DESC
    }    /**
     * 排序类型     */
    private String column;    /**
     * 排序类型     */
    private Order order;    public SortCond(String column) {        this(column, Order.DESC);
    }    public SortCond(String column, Order order) {        this.column = column;        this.order = order;
    }    public String getColumn() {        return column;
    }    public Order getOrder() {        return order;
    }
}

2)Service查询接口设计

package com.xxx.mybatistask.service;
import com.xxx.mybatistask.bean.query.GenericQueryParam;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.annotation.Resource;
public abstract class AbstractService {    
protected final Logger logger = LoggerFactory.getLogger(getClass());

    @Resource    protected SqlSession sqlSession;    /**
     * 分页参数校验
     *
     * @param params
     * @param rowCount
     * @return
     */
    protected void pageParamValidate(GenericQueryParam params, 
    int rowCount) {        
    int page = params.getPage();       
    int pageSize = params.getPageSize();        
    if (page < 1) page = 1;        
    if (pageSize < 1) pageSize = 1;       
    if (pageSize > GenericQueryParam.MAX_PAGE_SIZE)
            pageSize = GenericQueryParam.MAX_PAGE_SIZE;        
            int maxPage = (int) Math.ceil((double) rowCount / pageSize);        
            if (page > maxPage) page = maxPage;

        params.setPage(page);
        params.setPageSize(pageSize);
    }
}

package com.xxx.mybatistask.service;
import com.xxx.mybatistask.bean.Post;
import com.xxx.mybatistask.bean.query.GenericQueryParam;
import com.xxx.mybatistask.bean.query.ListResult;
public interface PostService {    /**
     * 查询参数列名枚举     */
    public enum PostQueryPram {
        title, content, author, status, created
    }    void create(Post post);    /**
     * 翻页查询
     *
     * @param param
     * @return
     */
    ListResult<Post> select(GenericQueryParam param);    
    void update(Post post);
}

package com.xxx.mybatistask.service.impl;
import com.xxx.mybatistask.bean.Post;
import com.xxx.mybatistask.bean.query.GenericQueryParam;
import com.xxx.mybatistask.bean.query.ListResult;
import com.xxx.mybatistask.service.AbstractService;
import com.xxx.mybatistask.service.PostService;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Service;
import java.util.LinkedList;import java.util.List;

@Servicepublic class PostServiceImpl extends AbstractService implements PostService {

    @Override    public void create(Post post) {
        sqlSession.insert("post.insert", post);
    }

    @Override    
    public ListResult<Post> select(GenericQueryParam params) {
        Integer rowCount = sqlSession.selectOne("post.selectCount", params);        
        if (rowCount == 0) {            
        return new ListResult<Post>(new LinkedList<Post>(), 0);
        }        
        // 分页参数检查        
        pageParamValidate(params, rowCount);        
        int page = params.getPage();        
        int pageSize = params.getPageSize();        
        int offset = (page - 1) * pageSize;

        RowBounds rowBounds = new RowBounds(offset, pageSize);
        List<Post> postList = sqlSession.selectList("post.select", params, rowBounds);        
        return new ListResult<Post>(postList, rowCount);
    }

    @Override    
    public void update(Post post) {
        sqlSession.update("post.update", post);
    }
}

3)自定义参数bean的解析与转换

以SortCond为例,由于是多属性查询参数,所以我们需要自己定义参数在客户端的文本格式,从客户端传入后再使用自定义的Paser来将其包装成SortCond

例如此处我们定义的排序参数在url中的格式为

/api/post/query/title/an?page=3&pageSize=200&sorts=created:DESC|author:ASC

其中排序参数为 "created:DESC|author:ASC" , 解析类如下

package com.xxx.mybatistask.support.stringparser;
import java.util.List;public interface Parser<T> {    /**
     * 字符串转对象
     *
     * @param parseString 待转换字符串
     * @return List<T>  转换完成的对象List     */
    List<T> parseList(String parseString);
}

package com.xxx.mybatistask.support.stringparser;
import com.google.common.base.Splitter;
import com.google.common.collect.Lists;
import com.xxx.mybatistask.bean.query.SortCond;
import java.util.List;import java.util.Map;
public class SortCondParser implements Parser<SortCond> {    /**
     * 排序列分隔符     */
    private static final String COL_SPLITTER = "|";    /**
     * 顺序类型分隔符     */
    private static final String ORDER_SPLITTER = ":";    /**
     * 列名检查     */
    private Class<? extends Enum> columnEnumCls;    
    public SortCondParser(Class<? extends Enum> columnEnumCls) {        
    this.columnEnumCls = columnEnumCls;
    }    /**
     * 将字符串转换为SortCond
     * 字符串的标准格式为
     * title:ASC|created:DESC
     *
     * @param parseString 待转换字符串
     * @return
     */
    @Override    public List<SortCond> parseList(String parseString) {
        List<SortCond> sortCondList = Lists.newArrayList();        
        // 将字符串切分为 {"column" => "order"} 的形式
        Map<String, String> sortOrderMap =
                Splitter.on(COL_SPLITTER)
                        .trimResults()
                        .omitEmptyStrings()
                        .withKeyValueSeparator(ORDER_SPLITTER)
                        .split(parseString);

        String column = null;
        String order = null;        
        for (Map.Entry<String, String> entry : sortOrderMap.entrySet()) {            
        // 验证column合法性
            column = entry.getKey();            
            if (column != null && !column.equals("")) {
                Enum.valueOf(columnEnumCls, column);
            } else {                break;
            }            // 验证order合法性
            order = entry.getValue();            
            if (order != null && !order.equals("")) {
                Enum.valueOf(SortCond.Order.class, order);
            } else {
                order = SortCond.Order.DESC.name();
            }

            sortCondList.add(new SortCond(column, SortCond.Order.valueOf(order)));
        }        return sortCondList;
    }
}

4) 动态查询SQL的编写

<select id="select"
            parameterType="com.xxx.mybatistask.bean.query.GenericQueryParam"
            resultType="com.xxx.mybatistask.bean.Post">
        <![CDATA[
            select
                id,
                title,
                content,
                author,
                status,
                created
            from
                post        ]]>
        <where>
            <if test="id != null">
                and id = #{id}            
                </if>
            <if test="title != null and title != ''">
                and title like concat('%', #{title}, '%')            
                </if>
            <if test="author != null and author != ''">
                and author like concat('%', #{author}, '%')            
                </if>
            <if test="content != null and content != ''">
                and match(content) against(#{content})            
                </if>
            <if test="status != null">
                and status = #{status}            </if>
            <if test="created != null and created != ''">
                and created = #{created}            </if>
        </where>
        <if test="_parameter.getSortCond().size() != 0">
            order by            
            <foreach collection="_parameter.getSortCond()" item="sortCond" separator=",">
                ${sortCond.column} ${sortCond.order}            
                </foreach>
        </if>

    </select>

至此SQL抽象接口以及完成,结合SortCond类,动态SQL和OGNL动态生成了order by参数,而类似的像 JOIN ... ON (USING) 或者 GROUP BY ... HAVING 等查询参

数条件,也可以将其抽象成bean,通过GenericQueryParam成员变量的形式拼接到SQL查询语句中来

另外代码中并没有对参数进行过多的检查,原因是:

1. MyBatis SQL查询使用prepareStatement,对于注入问题相对安全

2. 动态SQL查询使用<if>判断where查询条件,如果参数中的map key不是有效列名,将不会拼接到SQL语句中

3. 即使由于恶意用户篡改参数格式造成不规范参数的SQL查询异常,对于这种异常只需要重定向到全局error页面即可

5) Controller调用示例

@RequestMapping(value = "/query/{colKey}/{colVal}", method = RequestMethod.GET)    
public
    @ResponseBody
    Object query(
            @PathVariable String colKey,
            @PathVariable String colVal,
            @RequestParam(value = "status", required = false) String status,
            @RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
            @RequestParam(value = "pageSize", required = false, defaultValue = "10") Integer pageSize,
            @RequestParam(value = "sorts", required = false, defaultValue = "") String sorts
    ) {        // page and col
        GenericQueryParam params = new GenericQueryParam(page, pageSize);
        params.fill(colKey, colVal)
                .fill(
                    PostService.PostQueryPram.status.name(),
                    PostStatus.valueOf(status)
                );        // sorts
        SortCondParser sortCondParser = new SortCondParser(PostService.PostQueryPram.class);
        params.addSortCond(sortCondParser.parseList(sorts));

        ListResult<Post> postList = postService.select(params);        
        return dataJson(postList);
    }

2. TypeHandler设计

上文中的bean Post类中status属性类型是enum类,如下

package com.xxx.mybatistask.bean;
public enum PostStatus {
    NORMAL(0, "正常"), LOCKED(1, "锁定");    
    private int code;    
    private String text;    
    private PostStatus(int code, String text) {        
    this.code = code;        
    this.text = text;
    }    
    public int code() {        
    return code;
    }    
    public String text() {        
    return text;
    }    
    public static PostStatus codeOf(int code) {        
    for (PostStatus postStatus : PostStatus.values()) {           
     if (postStatus.code == code) {                
     return postStatus;
            }
        }        
        throw new IllegalArgumentException("invalid code");
    }    
    public static boolean contains(String text) {        
    for (PostStatus postStatus : PostStatus.values()) {            
    if (postStatus.toString().equals(text)) {                
    return true;
            }
        }        
        return false;
    }
}

而这个属性在数据库中的类型实际上市一个tinyint表示的标记位,为了让mybatis jdbc自动转换这个tinyint标记位为enum(查询时)和转换enum为tinyint(插入

更新时),需要编写mybatis typehandler

package com.xxx.mybatistask.support.typehandler;

import com.xxx.mybatistask.bean.PostStatus;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PostStatusTypeHandler implements TypeHandler<PostStatus> {    /**
     * PostStatus插入数据库时转换的方法
     * 将使用PostStatus的code插入数据库
     *
     * @param preparedStatement
     * @param index
     * @param postStatus
     * @param jdbcType
     * @throws SQLException     */
    @Override    
    public void setParameter(PreparedStatement preparedStatement, int index, PostStatus postStatus, JdbcType jdbcType) throws SQLException {
        preparedStatement.setInt(index, postStatus.code());
    }    /**
     * status查询出来时转为PostStatus的方法
     *
     * @param resultSet
     * @param colName
     * @return
     * @throws SQLException     */
    @Override    
    public PostStatus getResult(ResultSet resultSet, String colName) throws SQLException {        
    return PostStatus.codeOf(resultSet.getInt(colName));
    }

    @Override    
    public PostStatus getResult(ResultSet resultSet, int colIndex) throws SQLException {        
    return PostStatus.codeOf(resultSet.getInt(colIndex));
    }

    @Override    
    public PostStatus getResult(CallableStatement callableStatement, int colIndex) throws SQLException {        
    return PostStatus.codeOf(callableStatement.getInt(colIndex));
    }
}

在MyBatis配置文件中配置这个TypeHandler是其对PostStatus参数生效

    <typeHandlers>
        <typeHandler handler="com.xxx.mybatistask.support.typehandler.PostStatusTypeHandler"
                     javaType="com.xxx.mybatistask.bean.PostStatus"/>
    </typeHandlers>

3. 特殊参数的序列化与反序列化

由于需要实现接收和响应JSON数据,自动将JSON数据包装为具体对象类,此处使用了Spring的@ResponseBody以及@RequestBody标签,JSON的转换器为org.codehaus.jackson

但是对于某些特殊属性,例如此处的Post里的created属性,在bean中表现为Date类型,而在数据库中为TIMESTAMP类型,如果直接输出到JSON响应中,将会输出timestamp的毫秒数,为了格式化为自定义的格式,我们需要自定义一个JSON序列化(转为响应文本时)与反序列化(接收请求参数转为POST类时)的类.如下

序列化类

package com.xxx.mybatistask.support.jsonSerializer;
import org.codehaus.jackson.JsonGenerator;
import org.codehaus.jackson.JsonProcessingException;
import org.codehaus.jackson.map.JsonSerializer;
import org.codehaus.jackson.map.SerializerProvider;
import java.io.IOException;
import java.text.SimpleDateFormat;import java.util.Date;
public class JsonDateSerializer extends JsonSerializer<Date> {    
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override    
    public void serialize(Date date, JsonGenerator jsonGenerator, SerializerProvider serializerProvider) throws IOException, JsonProcessingException 
    {
        jsonGenerator.writeString(sdf.format(date));
    }
}

反序列化类

package com.xxx.mybatistask.support.jsonSerializer;
import org.codehaus.jackson.JsonNode;
import org.codehaus.jackson.JsonParser;
import org.codehaus.jackson.ObjectCodec;
import org.codehaus.jackson.map.DeserializationContext;
import org.codehaus.jackson.map.JsonDeserializer;
import java.io.IOException;import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;public class JsonDateDeserializer extends JsonDeserializer<Date> {    
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override    
    public Date deserialize(JsonParser jsonParser,
                            DeserializationContext deserializationContext)            
                            throws IOException {
        ObjectCodec oc = jsonParser.getCodec();
        JsonNode node = oc.readTree(jsonParser);        
        try {            
        return sdf.parse(node.getTextValue());
        } catch (ParseException e) {
            e.printStackTrace();
        }        return null;
    }
}

然后注意在Post类中标明,当Jackson序列化Post类为JSON串或将JSON串反序列化成Post类时,将调用这两个类,Post类的代码片段

    @JsonSerialize(using = JsonDateSerializer.class)    
    public Date getCreated() {        
    return created;
    }

    @JsonDeserialize(using = JsonDateDeserializer.class)    
    public void setCreated(Date created) {        
    this.created = created;
    }

                                                                                                                         --转自