1. 需求:动态传递表名,列名以及条件,然后根据传递的数据返回数据
2.项目结构:
3. 实现:
3.1. 加入配置文件mybatis-config.xml以及CommonMapper.xml
CommonMapper.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xk.mybatis.mapper.CommonMapper">
<select id="getData" resultType="java.util.Map" parameterType="java.util.Map">
SELECT
<if test="property==null">
*
</if>
<if test="property!=null">
${property}
</if>
FROM ${tableName}
<where>
<if test="conds!=null">
<foreach collection="conds" item="cond" separator="and" >
${cond.column} ${cond.oper} ${cond.value}
</foreach>
</if>
</where>
<if test="sorts!=null ">
order by
<foreach collection="sorts" item="sort" separator="," >
${sort.property} ${sort.direction}
</foreach>
</if>
</select>
</mapper>
3.2 控制层
@Controller
@RequestMapping("/api/base/common")
public class NormalController {
@Autowired
private CommonService commonService ;
@RequestMapping(value = "/", method = {RequestMethod.GET})
@ResponseBody
public APIResponse<List<Map<String,String>>> getData(QueryDto queryDto){
return new APIResponse<List<Map<String,String>>>(commonService.getData(queryDto));
}
}
3.3 service层
@Service
public class CommonService {
@Autowired
private CommonMapper commonMapper;
@Transactional(readOnly=true)
public List<Map<String,String>> getData(QueryDto queryDto){
Map<String,Object> map = new HashMap<>();
if(queryDto==null){
return null;
}
// 设置表名
map.put("tableName",queryDto.getTname());
// 设置获取的属性名
String[] properties = queryDto.getProperty() ;
String property = null;
if(properties!=null){
property = String.join(",",queryDto.getProperty());
}
map.put("property",property);
// 设置条件
map.put("conds",queryDto.generatorConds());
// 设置排序
map.put("sorts",queryDto.generatorSort());
return commonMapper.getData(map);
}
}
3.4 通用查询类:
public class QueryDto implements Serializable {
private String tname;
private String[] property;
private String searchConds;
/**
* 排序字段_排序规则
*/
private String[] sorts;
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public String[] getProperty() {
return property;
}
public void setProperty(String[] property) {
this.property = property;
}
public String getSearchConds() {
return searchConds;
}
public void setSearchConds(String searchConds) {
this.searchConds = searchConds;
}
public String[] getSorts() {
return sorts;
}
public void setSorts(String[] sorts) {
this.sorts = sorts;
}
/**
* 生成排序
*
* @return List<Sort>
*/
public List<Sort> generatorSort() {
List<Sort> list = new ArrayList<Sort>();
if (sorts != null && sorts.length > 0) {
for (String sort : sorts) {
String[] sortArr = sort.split("_");
if (sortArr.length == 1) { //默认升序
list.add(new Sort(sort, Direction.ASC));
}
// 排序
if (sortArr.length > 1) {
if (Direction.ASC.equalsIgnoreCase(sortArr[1]) || Direction.DESC.equalsIgnoreCase(sortArr[1])) {
list.add(new Sort(sortArr[0], sortArr[1]));
}
}
}
return list;
}
return null;
}
/**
* 将条件返回封装成list
*
* @return
*/
public List<Conds> generatorConds() {
// 获取条件,存储Map
List<Conds> list = new ArrayList<>();
if (!StringUtils.isEmpty(this.searchConds)) {
JSONArray json = JSONArray.fromObject(this.searchConds);
for (int i = 0; i < json.size(); i++) {
JSONObject jsonOne = json.getJSONObject(i);
// 获取属性
String fieldName = (String) jsonOne.get("name");
// 获取值
String value = (String) jsonOne.get("value");
if (!StringUtils.isEmpty(value)) {
String[] fieldArr = fieldName.split("_");
// 如果长度为1,默认为精确查询
if (fieldArr.length == 1) {
// ssb.add(fieldName, "=", (String) jsonOne.get("value"));
list.add(new Conds(fieldName, "=", value));
}
// 如果长度超过1
if (fieldArr.length > 1) {
list.add(new Conds(fieldArr[1], CondOper.getCond(fieldArr[0]), value));
}
}
}
return list;
}
return null;
}
}
**
* Created by Administrator on 2017/11/30.
* 条件封装类
*/
public class Conds {
/**
* 属性
*/
private String column;
/**
* 操作符
*/
private String oper;
/**
* 条件值
*/
private String value;
public Conds() {
}
public Conds(String column, String oper, String value) {
this.column = column;
this.oper = oper;
this.value = value;
}
public String getOper() {
if ("like".equalsIgnoreCase(oper)){
this.value = "'%"+value+"%'";
}
if ("llike".equalsIgnoreCase(oper)){
this.oper ="like";
this.value = "'%"+value+"'";
}
if ("rlike".equalsIgnoreCase(oper)){
this.oper ="like";
this.value = "'"+value+"%'";
}
return oper;
}
public void setOper(String oper) {
this.oper = oper;
}
public String getValue() {
if ("like".equalsIgnoreCase(oper)){
return this.value;
}
return "'"+value+"'";
}
public void setValue(String value) {
this.value = value;
}
public String getColumn() {
return column;
}
public void setColumn(String column) {
this.column = column;
}
@Override
public String toString() {
return "Conds{" +
"column='" + column + '\'' +
", oper='" + oper + '\'' +
", value='" + value + '\'' +
'}';
}
}
其余的未列出...
3.4 测试
3.5 注意:
get请求传递JSON需要进行编码
用js默认的encodeURI即可。