mybatis联合查询（未关联）

来源：互联网发布：淘宝运营专员每天做啥编辑：程序博客网时间：2024/05/17 10:04

描述：有三张表，商家登录表，商家表，商家资料信息表。商家登录后，通过商家id去查询商家表信息，然后通过商家表id去查询商家资料表信息，登录成功后需保存三张表信息到session中。这其中需要用到联合查询。

1.Mapper

<span style="font-family:Microsoft YaHei;"><select id="selectAccountInfors" parameterType="java.lang.String" resultType="map">  select    sm.id as sm_id, `name`,......,  smi.id as smi_id, ......, mark<span style="white-space:pre"></span>  from seller_merchant as sm <span style="white-space:pre"></span>//联合查询第二张和第三张表  inner join seller_merchant_info as smi on smi.merchantId = sm.id  where sm.accountId=#{accountid}  </select></span>

2.dao返回List<String,Object>

<span style="font-family:Microsoft YaHei;">/** * 查询登录商家的商户表和商户资料表信息 * @param accountid * @return */<strong><span style="color:#ff0000;">public List<Map<String,Object>> selectAccountInfors(String accountid);</span></strong></span>

3.impl

<span style="font-family:Microsoft YaHei;">public boolean queryMerchant(SellerMerchantAccount merchant,HttpSession session) {CriteriaParameter cp = new CriteriaParameter();Criteria cri = cp.createCriteria();cri.equalTo("account", merchant.getAccount());cri.equalTo("state", 0);//判断是否有该用户，且该用户的状态必须是可用状态List<SellerMerchantAccount> account = accountMapper.selectByExample(cp);if(!account.isEmpty()){cri.equalTo("password", merchant.getPassword());//判断用户名，密码，状态List<SellerMerchantAccount> acc = accountMapper.selectByExample(cp);if(!acc.isEmpty()){//根据用户账号id获取商家表信息String accountid = null;for (SellerMerchantAccount sellerMerchantAccount : acc) {accountid = sellerMerchantAccount.getId();}MerchantInformation information = new MerchantInformation();information.setId(accountid);information.setAccount(merchant.getAccount());information.setLoginTime(BaseUtil.timeToString(new Date()));<strong><span style="color:#ff0000;">List<Map<String,Object>> l = accountMapper.</span></strong><span style="color:#ff0000;"><strong>selectAccountInfors</strong></span><strong><span style="color:#ff0000;">(accountid)</span></strong>;for (Map<String, Object> map : l) {information.setLoginAccountInfors(map);}//将用户的账号id，账号，登录时间，商户表，商户资料信息表保存到session中session.setAttribute("MERCHANT_LOGIN-INFOR",information);return true;}}return false;}</span>

4.controller

<span style="font-family:Microsoft YaHei;">/** * 商户登录 * @param account * @return */@RequestMapping(value="/loginMerchant",method=RequestMethod.POST)public ResultData loginMerchant(SellerMerchantAccount account,HttpSession session){try {return new ResultData(merchantService.queryMerchant(account, session));} catch (Exception e) {e.printStackTrace();return SchExceptionUtil.ExceptionConvertState(e);}}</span>

5.实体类（是自己创建的，没有数据库，相对于用来保存信息的）

<span style="font-family:Microsoft YaHei;">/** * 商户账户id */private String id ;/** * 商户账户 */private String account;/** * 商户登录时间 */private String loginTime;/** * 商户表和商户资料表 */<span style="color:#ff0000;"><strong>private Map<String,Object> loginAccountInfors;//用来保存联合查询的结果</strong></span></span>

联合查询就用这种方式，返回Map键值对；关联对象就用<association>，集合就用<collection>

1 0