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