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;
}
--转自