外贸营销型网站,网页制作的公司哪家好,石家庄装修设计公司,wordpress段子1. SQL查询的统一抽象 MyBatis制动动态SQL的构造,利用动态SQL和自定义的参数Bean抽象,可以将绝大部分SQL查询抽象为一个统一接口,查询参数使用一个自定义bean继承Map,使用映射的方法构造多查询参数.在遇到多属性参数(例如order by,其参数包括列名,升序降序类型,以及可以多个列及…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 MapString, 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*/ListSortCond getSortCond();/*** 添加排序条件** param sortCond*/void addSortCond(SortCond sortCond);void addSortCond(ListSortCond 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 LinkedHashMapString, 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);}Overridepublic Integer getPage() {return (Integer) get(PAGE_KEY);}Overridepublic Integer getPageSize() {return (Integer) get(PAGESIZE_KEY);}Overridepublic void setPage(Integer page) {put(PAGE_KEY, page);}Overridepublic void setPageSize(Integer pageSize) {put(PAGESIZE_KEY, pageSize);}OverrideSuppressWarnings(unchecked)public ListSortCond getSortCond() {ListSortCond sortCondList (ListSortCond) get(SORTCOND_KEY);if (sortCondList null) {sortCondList new LinkedListSortCond();put(SORTCOND_KEY, sortCondList);}return sortCondList;}OverrideSuppressWarnings(unchecked)public void addSortCond(SortCond sortCond) {ListSortCond sortCondList (ListSortCond) get(SORTCOND_KEY);if (sortCondList null) {sortCondList new LinkedListSortCond();put(SORTCOND_KEY, sortCondList);}sortCondList.add(sortCond);}Overridepublic void addSortCond(ListSortCond sortCondList) {for (SortCond sortCond : sortCondList) addSortCond(sortCond);}Overridepublic 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());Resourceprotected 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*/ListResultPost 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;Service
public class PostServiceImpl extends AbstractService implements PostService {Overridepublic void create(Post post) {sqlSession.insert(post.insert, post);}Overridepublic ListResultPost select(GenericQueryParam params) {Integer rowCount sqlSession.selectOne(post.selectCount, params);if (rowCount 0) {return new ListResultPost(new LinkedListPost(), 0);}// 分页参数检查pageParamValidate(params, rowCount);int page params.getPage();int pageSize params.getPageSize();int offset (page - 1) * pageSize;RowBounds rowBounds new RowBounds(offset, pageSize);ListPost postList sqlSession.selectList(post.select, params, rowBounds);return new ListResultPost(postList, rowCount);}Overridepublic void update(Post post) {sqlSession.update(post.update, post);}
} 3)自定义参数bean的解析与转换 以SortCond为例,由于是多属性查询参数,所以我们需要自己定义参数在客户端的文本格式,从客户端传入后再使用自定义的Paser来将其包装成SortCond 例如此处我们定义的排序参数在url中的格式为 /api/post/query/title/an?page3pageSize200sortscreated:DESC|author:ASC 其中排序参数为 created:DESC|author:ASC , 解析类如下 package com.xxx.mybatistask.support.stringparser;import java.util.List;public interface ParserT {/*** 字符串转对象** param parseString 待转换字符串* return ListT 转换完成的对象List*/ListT 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 ParserSortCond {/*** 排序列分隔符*/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*/Overridepublic ListSortCond parseList(String parseString) {ListSortCond sortCondList Lists.newArrayList();// 将字符串切分为 {column order} 的形式MapString, String sortOrderMap Splitter.on(COL_SPLITTER).trimResults().omitEmptyStrings().withKeyValueSeparator(ORDER_SPLITTER).split(parseString);String column null;String order null;for (Map.EntryString, 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 idselectparameterTypecom.xxx.mybatistask.bean.query.GenericQueryParamresultTypecom.xxx.mybatistask.bean.Post![CDATA[selectid,title,content,author,status,createdfrompost]]whereif testid ! nulland id #{id}/ifif testtitle ! null and title ! and title like concat(%, #{title}, %)/ifif testauthor ! null and author ! and author like concat(%, #{author}, %)/ifif testcontent ! null and content ! and match(content) against(#{content})/ifif teststatus ! nulland status #{status}/ifif testcreated ! null and created ! and created #{created}/if/whereif test_parameter.getSortCond().size() ! 0order byforeach collection_parameter.getSortCond() itemsortCond 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)publicResponseBodyObject 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 colGenericQueryParam params new GenericQueryParam(page, pageSize);params.fill(colKey, colVal).fill(PostService.PostQueryPram.status.name(),PostStatus.valueOf(status));// sortsSortCondParser sortCondParser new SortCondParser(PostService.PostQueryPram.class);params.addSortCond(sortCondParser.parseList(sorts));ListResultPost 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 TypeHandlerPostStatus {/*** PostStatus插入数据库时转换的方法* 将使用PostStatus的code插入数据库** param preparedStatement* param index* param postStatus* param jdbcType* throws SQLException*/Overridepublic 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*/Overridepublic PostStatus getResult(ResultSet resultSet, String colName) throws SQLException {return PostStatus.codeOf(resultSet.getInt(colName));}Overridepublic PostStatus getResult(ResultSet resultSet, int colIndex) throws SQLException {return PostStatus.codeOf(resultSet.getInt(colIndex));}Overridepublic PostStatus getResult(CallableStatement callableStatement, int colIndex) throws SQLException {return PostStatus.codeOf(callableStatement.getInt(colIndex));}
} 在MyBatis配置文件中配置这个TypeHandler是其对PostStatus参数生效 typeHandlerstypeHandler handlercom.xxx.mybatistask.support.typehandler.PostStatusTypeHandlerjavaTypecom.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 JsonSerializerDate {private SimpleDateFormat sdf new SimpleDateFormat(yyyy-MM-dd HH:mm:ss);Overridepublic 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 JsonDeserializerDate {private SimpleDateFormat sdf new SimpleDateFormat(yyyy-MM-dd HH:mm:ss);Overridepublic 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转载于:https://www.cnblogs.com/zemliu/p/3248171.html
