1          在标准的mybatis的程序中，需要预先定义返回的结果对应的类，然后再在<select>标签中通过resultType或resultMap来指定返回的对象类型，如下

<select id="selectByPrimaryKey" resultType="bnsy.sis.model.Store" parameterType="java.lang.Integer" >
  select 
  *
  from Store
  where storeId = #{storeid,jdbcType=INTEGER}
</select>

 

2          但是，查询是非常灵活的，千变万化，如果为每一个返回结果都建立一个对应的类将会是一个非常大的工作量，幸运的是我们可以通过Map（键值对）来映射返回的查询结构，然后通过List（列表）将Map对象集合传递出去，如下

 

3          StoreMapper.xml

<select id="getByQuery" parameterType="java.lang.String" resultType="java.util.Map">
  SELECT [storeId]
  ,[userName]
  ,[password]
  ,case [state]
  when 0 then '不可用'
  when 1 then '可用'
  else '未知'
  end [state]
  ,[phoneNum]
  ,[storeCode]
  ,[itemCode]
  ,[token]
  ,[addTime]
  ,[lastModTime]
  FROM [dbo].[Store]
  WHERE 1=1
    AND (
      ([userName] LIKE '%'+#{queryStr}+'%' or ''=#{queryStr})
      or ([storeCode] LIKE '%'+#{queryStr}+'%' or ''=#{queryStr})
    )
</select>

 

4          StoreMapper接口

//获取查询结果
List<Map<String, Object>> getByQuery(String queryStr);

 

5          StoreService接口

//获取查询结果
public List<Map<String, Object>> getByQuery(String queryStr);

 

6          StoreService接口实现

@Override
public List<Map<String, Object>> getByQuery(String queryStr) {
    return storeMapper.getByQuery(queryStr);
}

 

7          通过Controller返回json数据

@RequestMapping(value = "/getQueryData", method = RequestMethod.GET)
@ResponseBody
public Object getQueryData(HttpServletRequest request){

    String queryStr = request.getParameter("queryStr");
    List<Map<String, Object>> stores = storeService.getByQuery(queryStr);//storeService.getAll();

    Map map = new HashMap();
    map.put("msg", "ok");
    map.put("totalCount", stores.size());
    map.put("pageIndex", 1);
    map.put("pageSize", 3);
    map.put("list", stores);

    return map;
}

 

8          分页查询

<!-- 返回分页结果 -->
<select id="getItemsByQuery" resultType="java.util.Map">
  <include refid="rst_getByQuery" />
  SELECT * FROM rst_getByQuery
  WHERE rowNum between #{beginIndex} and#{endIndex}
</select>
<!-- 返回查询到的记录数 -->
<select id="getCountByQuery" resultType="java.lang.Integer">
  <include refid="rst_getByQuery" />
  SELECT COUNT(1) from rst_getByQuery
</select>
<!-- sql片段，供上面两个查询语句使用 -->
<sql id="rst_getByQuery">
WITHrst_getByQuery AS (
    SELECT [storeId]
    ,[userName]
    ,[password]
    ,case [state]
    when 0 then '不可用'
    when 1 then '可用'
    else '未知'
    end [state]
    ,[phoneNum]
    ,[storeCode]
    ,[itemCode]
    ,[token]
    ,[addTime]
    ,[lastModTime]
    ,ROW_NUMBER() over(order by addTimedesc) rowNum
 ,m.mallName
    FROM [dbo].[Store] s
   join [dbo].[Mall] m onm.mallId=s.mallId
    WHERE 1=1
    AND (
      ([userName] LIKE'%'+#{queryStr}+'%' or ''=#{queryStr})
      or ([storeCode] LIKE'%'+#{queryStr}+'%' or ''=#{queryStr})
    )
)
</sql>