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 { /** * 新增查询参数 * * @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 getSortCond(); /** * 添加排序条件 * * @param sortCond */ void addSortCond(SortCond sortCond); void addSortCond(List 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 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 getSortCond() { List sortCondList = (List) get(SORTCOND_KEY); if (sortCondList == null) { sortCondList = new LinkedList(); put(SORTCOND_KEY, sortCondList); } return sortCondList; } @Override @SuppressWarnings("unchecked") public void addSortCond(SortCond sortCond) { List sortCondList = (List) get(SORTCOND_KEY); if (sortCondList == null) { sortCondList = new LinkedList(); put(SORTCOND_KEY, sortCondList); } sortCondList.add(sortCond); } @Override public void addSortCond(List 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 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 select(GenericQueryParam params) { Integer rowCount = sqlSession.selectOne("post.selectCount", params); if (rowCount == 0) { return new ListResult(new LinkedList(), 0); } // 分页参数检查 pageParamValidate(params, rowCount); int page = params.getPage(); int pageSize = params.getPageSize(); int offset = (page - 1) * pageSize; RowBounds rowBounds = new RowBounds(offset, pageSize); List postList = sqlSession.selectList("post.select", params, rowBounds); return new ListResult(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 { /** * 字符串转对象 * * @param parseString 待转换字符串 * @return List 转换完成的对象List */ List 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 { /** * 排序列分隔符 */ private static final String COL_SPLITTER = "|"; /** * 顺序类型分隔符 */ private static final String ORDER_SPLITTER = ":"; /** * 列名检查 */ private Classextends Enum> columnEnumCls; public SortCondParser(Classextends Enum> columnEnumCls) { this.columnEnumCls = columnEnumCls; } /** * 将字符串转换为SortCond * 字符串的标准格式为 * title:ASC|created:DESC * * @param parseString 待转换字符串 * @return */ @Override public List parseList(String parseString) { List sortCondList = Lists.newArrayList(); // 将字符串切分为 {"column" => "order"} 的形式 Map sortOrderMap = Splitter.on(COL_SPLITTER) .trimResults() .omitEmptyStrings() .withKeyValueSeparator(ORDER_SPLITTER) .split(parseString); String column = null; String order = null; for (Map.Entry 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"> 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查询使用判断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 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的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 { 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 { 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; }
THE END
