Mysql里查询字段为Json格式的数据模糊查询以及分页方法

 1 public void datagrid(CustomFormEntity customForm,HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
 2         //搜索参数
 3         StringBuffer searchSql=new StringBuffer("1=1");
 4         String showName = customForm.getShowName();
 5         if (StringUtil.isNotEmpty(showName) ) {
 6             searchSql.append(" and ").append(" mJson->'$.showName' like '%"+showName+"%'");
 7         }
 8         //头部
 9         StringBuffer sbHead=new StringBuffer();
10         sbHead.append("SELECT * ");
11         //查询体
12         StringBuffer sbBody=new StringBuffer();
13         sbBody.append(" from ");
14         sbBody.append(" cf_model ");
15         sbBody.append(" where ");
16         sbBody.append(searchSql);
17         sbBody.append(" order by sort asc");
18         //查询
19         Session session=this.systemService.getSession();
20         //获取总数
21         List<Object> listObj=systemService.findListbySql("select count(*) "+sbBody.toString());
22         long count =Long.valueOf(""+listObj.get(0));
23         
24         int rows = dataGrid.getRows();
25         int page = dataGrid.getPage();
26         //解决查询搜索页码问题-获取不到数据
27         if(((page-1)*rows)>=count){
28             page=1;
29         }
30         //获取分页后数据
31         String sql=sbHead.toString()+" "+sbBody.toString()+" LIMIT "+(page-1)*rows+","+dataGrid.getRows();
32         //查询结构
33         List<Map<String,Object>> c=systemService.findForJdbc(sql,new Object[]{});
34         int size = c.size();
35         List<Map<String,Object>> results= new ArrayList<Map<String,Object>>();
36         for (int k = 0; k < size; k++) {
37             Map map = (Map)JSON.parse((String)c.get(k).get("mJson"));
38             map.put("id", c.get(k).get("id"));
39             results.add(map);
40         }
41         //set data
42         dataGrid.setResults(results);
43         dataGrid.setTotal((int)count);
44         TagUtil.datagrid(response, dataGrid);
45     }

 

 

json表里面有个mJson字段数据如下面的Json格式

{"dataType":"1","id":"","ifMust":"1","laySort":"1","maxLen":"2","minLen":"1","showName":"身高"}

需要对其中的f开头的Json key值所对的value进行模糊查询，方法如下：

select *  from t_table_name where 1=1 and  mJson->'$.showName' like '%高%' order by sort asc

 

转载于:https://www.cnblogs.com/dravenT1/p/9436098.html