order操作符分页

环境：

IDEA J、SpringBoot、Mybatis、Mysql

废话不多说，直接开冲

通常情况下，一个 Dao层接口都有对应一个 mapper.xml 文件。下面是 xml的语句（每一个dao层的xml文件都要引入）

 1 <sql id="Data_Base">数据库名</sql>
 2     <sql id="Table_Name">数据表名</sql>
 3     <sql id="Page_Sql">
 4         <if test="alias != null">
 5             AS ${alias}
 6         </if>
 7         <if test="join != null">
 8             <foreach item="itemJoin" collection="join" index="itemJoinIndex">
 9                 ${itemJoin.joinType} JOIN ${itemJoin.tableName} ${itemJoin.tableNameAlias} ON ${itemJoin.expression}
10             </foreach>
11         </if>
12         <if test="conditionSql != null">
13             <where>
14                 ${conditionSql}
15             </where>
16         </if>
17         <if test="order != null">
18             ORDER BY ${order}
19         </if>
20     </sql>
21     <select id="pageQuery" parameterType="com.xiwi.hongmen.util.PageQueryParams" resultType="java.util.Map">
22         SELECT
23         <choose>
24             <when test="field == null">
25                 *
26             </when>
27             <otherwise>
28                 ${field}
29             </otherwise>
30         </choose>
31         FROM <include refid="Data_Base" />.<include refid="Table_Name" />
32         <include refid="Page_Sql" />
33         limit #{pageIndex}, #{pageSize}
34     </select>
35     <select id="pageQueryCount" parameterType="com.xiwi.hongmen.util.PageQueryParams" resultType="java.lang.Integer">
36         SELECT
37         COUNT(*)
38         FROM <include refid="Data_Base" />.<include refid="Table_Name" />
39         <include refid="Page_Sql" />
40     </select>

编写 BaseDao接口类（给要实现公共方法的 dao层接口继承。嗯接口继承接口）：

1 public interface BaseDao {
2     List<Map<String, Object>> pageQuery(PageQueryParams pageQueryParams);
3     Integer pageQueryCount(PageQueryParams pageQueryParams);
4 }

编写 BaseService接口类（给要实现公共方法的 service层接口继承。嗯还是接口继承接口）：

1 public interface BaseService {
2     PageQueryResult pageQuery(PageQueryParams pageQueryParams);
3     Integer pageQueryCount(PageQueryParams pageQueryParams);
4 }

编写 BaseServiceImpl接口实现类（给要实现公共方法的 impl(接口实现)层继承）这里就要开始具体写实现了：

 1 public class BaseServiceImpl implements BaseService {
 2     // 这里到时候是要到 继承该类的 类中进行初始化赋值的
 3     protected BaseDao baseDao;
 4 
 5     // 有用缓存就 用没用就不管
 6     @Resource
 7     private RedisUtil redisUtil;
 8 
 9     @Override
10     public PageQueryResult pageQuery(PageQueryParams pageQueryParams) {
11         // 查缓存 有缓存直接返回 begin。没用缓存的这一段可以去掉
12         String redisKey = pageQueryParams.toString();
13         PageQueryResult cache = redisUtil.get(redisKey, PageQueryResult.class);
14         if (cache != null && 1==0) {
15             System.out.println("有缓存");
16             return cache;
17         }
18        // 查缓存 有缓存直接返回 end
19 
20         Integer totalCount = this.pageQueryCount(pageQueryParams);
21         pageQueryParams.setPageIndex(
22                 pageQueryParams.getPageIndex() - 1
23         );
24 
25         int pageCount = 1;
26         if (pageQueryParams.getPageSize() != 0) {
27             pageCount = new Double(Math.ceil(new BigDecimal(totalCount).doubleValue() / new BigDecimal(pageQueryParams.getPageSize()).doubleValue())).intValue();
28         } else {
29             pageQueryParams.setPageSize(totalCount);
30         }
31 
32         // 这里就是调用 实现该类的xml中 id为pageQuery 的查询语句
33         List<Map<String, Object>> list = baseDao.pageQuery(pageQueryParams);
34 
35         PageQueryResult pageQueryResult = new PageQueryResult()
36                 .setPageIndex(pageQueryParams.getPageIndex()+1)
37                 .setPageCount(pageCount)
38                 .setTotalCount(totalCount)
39                 .setData(list);
40 
41         // 把查出来的数据 存个缓存。没用缓存可以把这一句删掉
42         redisUtil.set(redisKey, pageQueryResult,60);
43 
44         return pageQueryResult;
45     }
46 
47     // 这个方法是获取数据数量的，用于分页
48     @Override
49     public Integer pageQueryCount(PageQueryParams pageQueryParams) {
50         return baseDao.pageQueryCount(pageQueryParams);
51     }
52 }

xxxServiceImpl类（这个是你自己的服务接口实现类）：

 1 @Service("sysAreaService")
 2 public class SysAreaServiceImpl extends BaseServiceImpl implements SysAreaService {
 3 
 4     private SysAreaDao sysAreaDao;
 5     @Autowired
 6     public void setSysAreaDao(SysAreaDao sysAreaDao) {
 7         this.sysAreaDao = sysAreaDao;
 8         this.baseDao = sysAreaDao;
 9     }
10 
11     // ...这里可以写你自己的接口实现
12 }

上面所用到的 PageQueryParams类和 PageQueryResult类，是我自己按需封装的。经供参考

 1 public class PageQueryResult {
 2     private Integer pageIndex; // 当前页码
 3     private Integer pageCount; // 当前页数据数量
 4     private Integer totalCount; // 总页数的所有数量
 5     private List<Map<String, Object>> data; // 数据
 6 
 7 
 8     public PageQueryResult setPageIndex(Integer pageIndex) {
 9         this.pageIndex = pageIndex;
10         return this;
11     }
12 
13     public Integer getPageIndex() {
14         return pageIndex;
15     }
16 
17     public PageQueryResult setPageCount(Integer pageCount) {
18         this.pageCount = pageCount;
19         return this;
20     }
21     public Integer getPageCount() {
22         return pageCount;
23     }
24     public PageQueryResult setTotalCount(Integer totalCount) {
25         this.totalCount = totalCount;
26         return this;
27     }
28 
29     public Integer getTotalCount() {
30         return totalCount;
31     }
32 
33     public PageQueryResult setData(List<Map<String, Object>> data) {
34         this.data = data;
35         return this;
36     }
37     public List<Map<String, Object>> getData() {
38         return data;
39     }
40     
41     @Override
42     public String toString() {
43         return "PageQueryResult{" +
44                 "pageIndex=" + pageIndex +
45                 ", pageCount=" + pageCount +
46                 ", totalCount=" + totalCount +
47                 ", data=" + data +
48                 '}';
49     }
50 }

  1 public class PageQueryParams {
  2     private Integer pageIndex = 1;
  3     private Integer pageSize = 0;
  4     private List<PageConditionParams> condition; // 条件逻辑表达式
  5     private String order; // order操作符
  6     private String field; // field操作符
  7     private List<PageJoinParams> join; // join操作符
  8     private String alias; // alias操作符
  9     private String group; // group操作符(暂还未用到)
 10     private String whereRaw;// whereRaw操作符(暂还未用到)
 11     private String orderRaw;// orderRaw操作符(暂还未用到)
 12     private String conditionSql; // 存储具体的逻辑Sql语句
 13 
 14 
 15     /**
 16      * 生成查询条件SQL
 17      */
 18     private String buildWhere(Map<String, List<PageConditionParams>> conditionParams) {
 19         StringBuffer whereStr = new StringBuffer();
 20         for(Map.Entry<String, List<PageConditionParams>> item : conditionParams.entrySet()){
 21             List<String> sqlList = new LinkedList<>();
 22             for (int i = 0; i < item.getValue().size(); i++) {
 23                 PageConditionParams pageConditionParams = item.getValue().get(i);
 24                 if (pageConditionParams.getField().contains("|")) {
 25                     // 不同字段使用相同查询条件（OR）
 26                     String[] fields = pageConditionParams.getField().split("\\|");
 27                     List<String> tempSqlList = new LinkedList<>();
 28                     for (String field : fields) {
 29                         tempSqlList.add(this.parseWhereItem(field, pageConditionParams.getExpression(), pageConditionParams.getValue()));
 30                     }
 31                     sqlList.add(
 32                             " " + item.getKey() +
 33                                     " ( " +
 34                                     StringUtils.join(tempSqlList, " OR ") +
 35                                     " ) "
 36 
 37                     );
 38                 } else if (pageConditionParams.getField().contains("&")) {
 39                     // 不同字段使用相同查询条件（AND）
 40                     String[] fields = pageConditionParams.getField().split("&");
 41                     List<String> tempSqlList = new LinkedList<>();
 42                     for (String field : fields) {
 43                         tempSqlList.add(this.parseWhereItem(field, pageConditionParams.getExpression(), pageConditionParams.getValue()));
 44                     }
 45                     sqlList.add(
 46                             " " + item.getKey() +
 47                                     " ( " +
 48                                     StringUtils.join(tempSqlList, " AND ") +
 49                                     " ) "
 50 
 51                     );
 52                 } else {
 53                     // 对字段使用表达式查询
 54                     sqlList.add(
 55                             " " + item.getKey() +
 56                                     " ( " +
 57                                     this.parseWhereItem(pageConditionParams.getField(), pageConditionParams.getExpression(), pageConditionParams.getValue()) +
 58                                     " ) "
 59                     );
 60                 }
 61             }
 62 
 63             String sqlStr = StringUtils.join( sqlList, " ");
 64             if (whereStr.length() == 0) {
 65                 sqlStr = sqlStr.substring(item.getKey().length() + 1);
 66             }
 67             whereStr.append(sqlStr);
 68         }
 69         return whereStr.toString();
 70     }
 71 
 72     /**
 73      * 分析查询表达式
 74      * @param logic     查询逻辑 and or xor
 75      * @param condition 查询数组
 76      * @return
 77      */
 78     private Map<String, List<PageConditionParams>> parseWhereExp(String logic, List<PageConditionParams> condition) {
 79         List<String> fields = new LinkedList<>();
 80         Map<String, PageConditionParams> tempCondition = new LinkedHashMap<>();
 81         for (int i = 0; i < condition.size(); i++) {
 82             fields.add(condition.get(i).getField());
 83             tempCondition.put(condition.get(i).getField(), condition.get(i));
 84         }
 85 
 86         Map<String, List<PageConditionParams>> resultMap = new LinkedHashMap<>();
 87         resultMap.put(logic, new LinkedList<PageConditionParams>() {{
 88             for (String field: fields) {
 89                 add(
 90                         new PageConditionParams()
 91                                 .setField(field)
 92                                 .setExpression(tempCondition.get(field).getExpression())
 93                                 .setValue(tempCondition.get(field).getValue())
 94                 );
 95             }
 96         }});
 97         System.out.println(resultMap);
 98         return resultMap;
 99     }
100 
101     // where子单元分析
102     private String parseWhereItem(String field, String expression, Object value) {
103         StringBuffer stringBuffer = new StringBuffer();
104 
105 
106         String fieldStr = this.parseKey(field);
107         Object valueObj = this.parseValue(value);
108 
109 
110         Map<String, List<String>> expMap = new LinkedHashMap<String, List<String>>() {{
111             put("comparison", new LinkedList<String>() {{
112                 add("=");add("<>");add(">");add(">=");add("<");add("<=");
113             }}); // 比较运算
114             put("like", new LinkedList<String>() {{
115                 add("LIKE");add("NOT LIKE");
116             }}); // 模糊匹配
117             put("exp", new LinkedList<String>() {{
118                 add("EXP");
119             }}); // 表达式查询
120             put("null", new LinkedList<String>() {{
121                 add("NOT NULL");add("NULL");
122             }}); // NULL 查询
123             put("in", new LinkedList<String>() {{
124                 add("NOT IN");add("IN");
125             }}); // IN 查询
126         }};
127         // 判断 操作符
128         if (expMap.get("comparison").contains(expression)) {
129             // 比较运算
130             stringBuffer.append( fieldStr )
131                     .append( " " )
132                     .append( expression )
133                     .append( " " )
134                     .append( valueObj );
135         } else if (expMap.get("like").contains(expression)) {
136             stringBuffer.append( fieldStr )
137                     .append( " " )
138                     .append( expression )
139                     .append( " " )
140                     .append( valueObj );
141         } else if (expMap.get("exp").contains(expression)) {
142 
143         } else if (expMap.get("null").contains(expression)) {
144 
145         } else if (expMap.get("in").contains(expression)) {
146             String inStr = value.toString();
147             if (value instanceof List<?>) {
148                 inStr = " (" + StringUtils.join((List<?>) value, ",") + ") ";
149             }
150             stringBuffer.append( fieldStr )
151                     .append( " " )
152                     .append( expression )
153                     .append( " " )
154                     .append( inStr );
155         } else {
156             System.out.println("错误的操作表达式"); // 这里要完善是抛出异常 会比较妥当
157         }
158         return stringBuffer.toString();
159     }
160 
161 
162     /**
163      * 字段名分析
164      */
165     private String parseKey(String field) {
166         StringBuffer stringBuffer = new StringBuffer();
167         if (field.contains(".")) {
168             String[] tableAndField = field.split("\\.");
169             for (String tempTableAndField: tableAndField) {
170                 stringBuffer.append( "`" );
171                 stringBuffer.append( tempTableAndField );
172                 stringBuffer.append( "`" );
173                 stringBuffer.append( "." );
174             }
175             stringBuffer.setLength(stringBuffer.length()-1); // 去除最后一个 .
176         } else {
177             stringBuffer.append( "`" );
178             stringBuffer.append( field );
179             stringBuffer.append( "`" );
180         }
181         return stringBuffer.toString();
182     }
183     /**
184      * value分析
185      */
186     private String parseValue(Object value) {
187         StringBuffer stringBuffer = new StringBuffer();
188         if (value instanceof String) {
189             stringBuffer.append( "'" )
190                     .append( value )
191                     .append( "'" );
192         } else if (value instanceof Integer) {
193             stringBuffer.append(value);
194         }
195 
196         return stringBuffer.toString();
197     }
198 
199 
200     public void clear() {
201         this.pageIndex = 1;;
202         this.pageSize = 0;
203         this.condition = null;
204         this.order = null;
205         this.field = null;
206         this.join = null;
207         this.alias = null;
208         this.group = null;
209         this.whereRaw = null;
210         this.orderRaw = null;
211     }
212 
213     public Integer getPageIndex() {
214         return pageIndex;
215     }
216     public void setPageIndex(Integer pageIndex) {
217         if (pageIndex != null) {
218             this.pageIndex = pageIndex;
219         }
220     }
221 
222     public Integer getPageSize() {
223         return pageSize;
224     }
225     public void setPageSize(Integer pageSize) {
226         if (pageSize != null) {
227             this.pageSize = pageSize;
228         }
229 
230     }
231 
232 
233 
234     // 取 条件表达式 字段名 数组
235     private List<String> getConditionField() {
236         List<String> fields = new LinkedList<>();
237         for (PageConditionParams pageConditionParams : this.condition) {
238             fields.add(pageConditionParams.getField());
239         }
240         return fields;
241     }
242     public List<PageConditionParams> getCondition() {
243         return condition;
244     }
245     public void setCondition(List<PageConditionParams> condition) {
246         this.condition = condition;
247     }
248     public void addCondition(List<PageConditionParams> condition) {
249 
250         // 这里取到具体执行的 sql (是的 这个类的本质就是 拼接sql 就酱)
251         Map<String, List<PageConditionParams>> map = this.parseWhereExp("AND", condition);
252         List<String> sqlList = new LinkedList<>();
253         this.conditionSql = this.buildWhere(map);
254         System.out.println(this.conditionSql);
255         
256     }
257 
258     public String getOrder() {
259         return order;
260     }
261 
262     public void setOrder(String order) {
263         this.order = order;
264     }
265 
266     public String getField() {
267         return field;
268     }
269 
270     public void setField(String field) {
271         this.field = field;
272     }
273 
274     public List<PageJoinParams> getJoin() {
275         return join;
276     }
277     public void setJoin(List<PageJoinParams> join) {
278         this.join = join;
279     }
280     public void addJoin() {
281 
282     }
283 
284     public String getAlias() {
285         return alias;
286     }
287 
288     public void setAlias(String alias) {
289         this.alias = alias;
290     }
291 
292     public String getGroup() {
293         return group;
294     }
295 
296     public void setGroup(String group) {
297         this.group = group;
298     }
299 
300     public String getWhereRaw() {
301         return whereRaw;
302     }
303 
304     public void setWhereRaw(String whereRaw) {
305         this.whereRaw = whereRaw;
306     }
307 
308     public String getOrderRaw() {
309         return orderRaw;
310     }
311 
312     public void setOrderRaw(String orderRaw) {
313         this.orderRaw = orderRaw;
314     }
315 
316     @Override
317     public String toString() {
318 
319         StringBuffer stringBuffer = new StringBuffer();
320 
321         String conStr = "";
322         if (condition != null) {
323             for (int i = 0; i < condition.size(); i++) {
324                 PageConditionParams pageConditionParams =  condition.get(i);
325                 stringBuffer.append( "{" );
326                 stringBuffer.append( pageConditionParams.getField() );
327                 stringBuffer.append( "," );
328                 stringBuffer.append( pageConditionParams.getExpression() );
329                 stringBuffer.append( "," );
330                 stringBuffer.append( pageConditionParams.getValue() );
331                 stringBuffer.append( "}," );
332             }
333             if (stringBuffer.length() > 0) {
334                 conStr = stringBuffer.substring(0, stringBuffer.length()-1);
335             }
336         }
337 
338 
339         stringBuffer.setLength(0);
340         if (stringBuffer == null) {
341             stringBuffer = new StringBuffer();
342         }
343         String joinStr = "";
344         if (join != null) {
345             for (int i = 0; i < join.size(); i++) {
346                 PageJoinParams pageJoinParams = join.get(i);
347                 stringBuffer.append( "{" );
348                 stringBuffer.append( pageJoinParams.getTableName() );
349                 stringBuffer.append( "," );
350                 stringBuffer.append( pageJoinParams.getTableNameAlias() );
351                 stringBuffer.append( "," );
352                 stringBuffer.append( pageJoinParams.getExpression() );
353                 stringBuffer.append( "," );
354                 stringBuffer.append( pageJoinParams.getJoinType() );
355                 stringBuffer.append( "}," );
356             }
357             if (stringBuffer.length() > 0) {
358                 joinStr = stringBuffer.substring(0, stringBuffer.length()-1);
359             }
360         }
361 
362         
363         return "PageQueryParams{" +
364                 "pageIndex=" + pageIndex +
365                 ", pageSize=" + pageSize +
366                 ", condition=" + "[" + conStr + "]" +
367                 ", order='" + order + '\'' +
368                 ", field='" + field + '\'' +
369                 ", join=" + "[" + joinStr + "]" +
370                 ", alias='" + alias + '\'' +
371                 ", group='" + group + '\'' +
372                 ", whereRaw='" + whereRaw + '\'' +
373                 ", orderRaw='" + orderRaw + '\'' +
374                 '}';
375     }
376 }

工具类（ResultJson）：

 1 public class ResultJson extends LinkedHashMap<String, Object> {
 2 
 3     public ResultJson code(Integer code) {
 4         this.put("code", code);
 5         return this;
 6     }
 7 
 8     public ResultJson data(Object data) {
 9         this.put("data", data);
10         return this;
11     }
12 
13     public ResultJson msg(String msg) {
14         this.put("msg", msg);
15         return this;
16     }
17 
18 
19     public ResultJson success(Object data) {
20         this.put("code", 1);
21         this.put("data", data);
22         this.put("msg", "操作成功");
23         return this;
24     }
25     public ResultJson success(Object data, String msg) {
26         this.put("code", 1);
27         this.put("data", data);
28         this.put("msg", msg);
29         return this;
30     }
31     public ResultJson success(PageInfo<?> pageInfo, String msg) {
32         this.put("code", 1);
33         Map<String, Object> data = new LinkedHashMap<>();
34         data.put("page_index", pageInfo.getPageNum());
35         data.put("total_count", pageInfo.getTotal());
36         data.put("data", pageInfo.getList());
37         this.put("data", data);
38         this.put("msg", msg);
39         return this;
40     }
41     public ResultJson success(PageQueryResult pageQueryResult, String msg) {
42         this.put("code", 1);
43         Map<String, Object> data = new LinkedHashMap<>();
44         data.put("page_index", pageQueryResult.getPageIndex());
45         data.put("page_count", pageQueryResult.getPageCount());
46         data.put("total_count", pageQueryResult.getTotalCount());
47         data.put("data", pageQueryResult.getData());
48         this.put("data", data);
49         this.put("msg", msg);
50         return this;
51     }
52     public ResultJson error(Object data) {
53         this.put("code", 0);
54         this.put("data", data);
55         this.put("msg", "操作失败");
56         return this;
57     }
58     public ResultJson error(Object data, String msg) {
59         this.put("code", 0);
60         this.put("data", data);
61         this.put("msg", msg);
62         return this;
63     }
64     public ResultJson paramEmpty(String msg) {
65         this.put("code", -1);
66         this.put("data", "");
67         this.put("msg", msg);
68         return this;
69     }
70 
71 
72     public ResultJson toJson(Integer code, Object data, String msg) {
73         this.put("code", code);
74         this.put("data", data);
75         this.put("msg", msg);
76         return this;
77     }
78     public ResultJson toJson(Integer code, String msg) {
79         this.put("code", code);
80         this.put("data", "");
81         this.put("msg", msg);
82         return this;
83     }
84 }

示例：

 1 @Test
 2     void contextLoads() {
 3         PageQueryParams pageQueryParams = new PageQueryParams();
 4         pageQueryParams.addCondition(new LinkedList<PageConditionParams>() {{
 5             add(
 6                     new PageConditionParams()
 7                             .setField("title")
 8                             .setExpression("=") // 可以换成 > , < , <> 等比较运算符
 9                             .setValue("title的值")
10             ); // 生成的sql: (`title` = 'title的值')
11             add(
12                     new PageConditionParams()
13                             .setField("name|user")
14                             .setExpression("LIKE")
15                             .setValue("%用户名%")
16             ); // 生成的sql: (`name` LIKE '%用户名%' or `user` LIKE '%用户名%')
17         }});
18         PageQueryResult sysConfigList = sysConfigService.pageQuery(pageQueryParams);
19         System.out.println(sysConfigList);
20     }

待完善...

posted @ 2020-09-30 10:30 Xiwi 阅读(745) 评论(0) 收藏举报

刷新页面返回顶部

Xiwi

[JavaWeb] SpringBoot框架 结合 Mybatis框架 实现 BaseDao 公共方法 包括 join/alias/condition/order操作符 分页

公告

[JavaWeb] SpringBoot框架结合 Mybatis框架实现 BaseDao 公共方法包括 join/alias/condition/order操作符分页