{
"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;
}
======
精简了部分代码,这算是一对多的实现方式吧~
