mybatis多条件查询

{
  "msg":"ok",
  "code":1,
  "data":[
    {
      "sheetFrom":2,
      "tst":[
        {
          "actionName":"revoke"
        },
      ],
      "ifmBalkNo":"1",
      "buttonList":[
        "revoke",
        "summit"
      ]
    }
  ]
}

最近做项目,接口速度跟不上,用的是java,刚好可以学以下,问后台要了源码,自己捣鼓一下,我尝试写一个查询语句

我尝试写一个查询语句
结构如上,涉及多表查询,这个我那会阿,
我会一个一个的查,要我写查询上面的结构
最外层的结构好搞,buttonList
,tst
要我来写,要循环查询数据库了,分几步走,代码如下

for (int x = 0; x < list.size(); x++) {
            Sheet sheet = list.get(x);
                SearchActionsReq actionsReq = new SearchActionsReq();
                actionsReq.sheettype= sheet.getSheetType();
                actionsReq.step = sheet.getStep();
                Log.e("===========查询buttons==========");
                List<String> ls = mSheetFlowDao.getActions(actionsReq);
                sheet.setButtonList(ls);
                Log.e("===========查询tsts==========");
                List<Tst> lss = mSheetFlowDao.getTst(actionsReq);
                sheet.tst = lss;
            }

我想在mapper里搞定所有,服务器没有参考,看一下后台写的代码,咳咳,我觉得还可以抢救一下,springboot+mybatis就这样吗!!!

public JSONObject getMegList(HttpServletRequest request){
        //获取参数
        String data=request.getParameter("data");
        JSONObject jsondata= JSONObject.parseObject(data);
        //获取token
        String token = jsondata.getString("token");
        JSONObject json=new JSONObject();
        JSONObject json1=new JSONObject();
        //判断token是否存在
        if (token!=null&&!"".equals(token)) {
            try {
                SysUserToUser sysUserToUser = JwtUtils.decode(token, SysUserToUser.class);
                if (sysUserToUser!=null) {
                    int page = jsondata.getIntValue("page");
                    Map<String, Object> map=new HashMap<String,Object>();
                    map.put("param1", page);
                    map.put("param2", 15);
                    map.put("param3", sysUserToUser.getId());
                    List<Map<String, Object>> list = messageDao.list(map);
                    json.put("code", 1);
                    json.put("msg", "获取信息列表成功");
                    json.put("data", list);
                    json.put("page", page);
                    int size=list.size();
                    int pagetotal=0;
                    pagetotal=size%15==0?size/15:size/15+1;
                    json.put("totalpage", pagetotal);
                    return json;
                } else {
                    //未查到用户关系
                    json.put("code", 2);
                    json.put("msg", "未查到用户关系!");
                    json.put("data", json1);
                    return json;
                }
                
            } catch (InvalidKeyException | NoSuchAlgorithmException | IllegalStateException | SignatureException
                    | IOException | JWTVerifyException e) {
                e.printStackTrace();
                json.put("code", 2);
                json.put("msg", "token验证错误!");
                json.put("data", json1);
                return json;
            }
        } else {
            //获取token失败
            json.put("code", 2);
            json.put("msg", "token获取失败!");
            json.put("data", json1);
            return json;
        }
    }

自己查阅
不多说,上代码

<?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.unicom.palm.model.tables.generated.SheetMapper">
    <resultMap id="BaseResultMap" type="com.unicom.palm.model.tables.generated.Sheet">
        <!-- WARNING - @mbggenerated This element is automatically generated by 
            MyBatis Generator, do not modify. -->
        <constructor>
            <idArg column="id" javaType="java.math.BigDecimal" jdbcType="NUMERIC" />
            <arg column="sheet_id" javaType="java.math.BigDecimal" jdbcType="NUMERIC" />
            <arg column="sheet_from" javaType="java.lang.Integer" jdbcType="INTEGER" />
        </constructor>
        <collection property="buttonList" column="{sheet_type=sheet_type,step=step}"
            javaType="ArrayList" ofType="java.lang.String" select="searchActions">
        </collection>
        <collection property="tst" column="{sheet_type=sheet_type,step=step}"
            javaType="ArrayList" ofType="com.unicom.palm.model.entity.Tst" select="searchActions2">
        </collection>
        <!-- <discriminator javaType="int" column="draft"> <case value="1" resultType="DraftPost" 
            /> </discriminator> -->
        <!-- <association></association> 对象分割 -->
    </resultMap>
    <!-- 查询工单开始 -->
    <select id="searchSheetTask" parameterType="com.unicom.palm.model.entity.SearchTasksReq"
        resultMap="BaseResultMap">
        select * from t_palm_sheet where user_id=#{userId, jdbcType=NUMERIC} 
            and ifm_balk_no=#{keyword,jdbcType=VARCHAR} LIMIT 20*#{page} OFFSET (#{page}-1)*20
        <!-- select c.*,t.*
        from t_palm_sheet t,t_palm_sheet_flow c
        where c.sheet_type=t.sheet_type
        and c.step=t.step
        and t.user_id=#{userId, jdbcType=NUMERIC}
        and t.ifm_balk_no =#{keyword,jdbcType=VARCHAR}
        LIMIT 20*#{page} OFFSET (#{page}-1)*20 -->
    </select>
    <!-- 查询工单结束 -->

    <!-- 查询actions -->
    <resultMap id="actions2" type="com.unicom.palm.model.entity.Tst">
            <result property="step" column="step" />
            <result property="actionId" column="action_id" />
            <result property="actionUrl" column="action_url" />
            <result property="actionOrder" column="action_order" />
            <result property="sheetType" column="sheet_type"  />
            <result property="actionName" column="action_name"  />
            <result property="nextStep" column="next_step"  />
    </resultMap>
    <resultMap id="actions" type="java.lang.String">
        <result column="action_name" />
    </resultMap>
    <select id="searchActions" parameterType="map"
        resultMap="actions">
        select
        id, step, action_id, action_url, action_order,
        sheet_type,action_name,
        next_step
        from t_palm_sheet_flow
        where sheet_type
        =
        #{sheet_type,jdbcType=INTEGER}
        and step = #{step,jdbcType=INTEGER}
    </select>
    <select id="searchActions2" parameterType="map"
        resultMap="actions2">
        select
        id, step, action_id, action_url, action_order,
        sheet_type,action_name,
        next_step
        from t_palm_sheet_flow
        where sheet_type
        =
        #{sheet_type,jdbcType=INTEGER}
        and step = #{step,jdbcType=INTEGER}
    </select>
    <!-- 查询actions -->
</mapper>
最后,我只要
List<Sheet> list = new ArrayList<Sheet>();
        SheetMapper mSearchSheetMapper = mapper(SheetMapper.class);
        list = mSearchSheetMapper.searchSheetTask(req);
        
以前是这样:
List<Sheet> list = new ArrayList<Sheet>();
        SheetMapper mSearchSheetMapper = mapper(SheetMapper.class);
        list = mSearchSheetMapper.searchSheetTask(req);


======就是它,完全没必要存在
for (int x = 0; x < list.size(); x++) {
            Sheet sheet = list.get(x);
                SearchActionsReq actionsReq = new SearchActionsReq();
                actionsReq.sheettype= sheet.getSheetType();
                actionsReq.step = sheet.getStep();
                Log.e("===========查询buttons==========");
                List<String> ls = mSheetFlowDao.getActions(actionsReq);
                sheet.setButtonList(ls);
                Log.e("===========查询tsts==========");
                List<Tst> lss = mSheetFlowDao.getTst(actionsReq);
                sheet.tst = lss;
            }
======          

精简了部分代码,这算是一对多的实现方式吧~

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容