[Java] Mybatis中输入输出映射与动态Sql图文详解

package cn.mybatis.po;
 
public class userpojo extends user{
 private user user;
 
 public void setuser(user user) {
 this.user = user;
 }
 
 public user getuser() {
 return user;
 }
}
 
userpojo

package cn.mybatis.po;
 
public class uservo {
 private userpojo userpojo;
 
 public userpojo getuserpojo() {
 return userpojo;
 }
 
 public void setuserpojo(userpojo userpojo) {
 this.userpojo = userpojo;
 }
}
 
uservo

//测试包装类型的查询
 public list<userpojo> finduserlist(uservo uservo) throws exception;

@test
 public void testfinduserlist() throws exception {
 sqlsession sqlsession = sqlsessionfactory.opensession();
 usermapper usermapper = sqlsession.getmapper(usermapper.class);
 
 userpojo userpojo = new userpojo();
 uservo uservo = new uservo();
 userpojo.setsex("男");
 userpojo.setusername("u");
 uservo.setuserpojo(userpojo);
 
 list<userpojo> userpojolist = usermapper.finduserlist(uservo);
 
 system.out.println(userpojolist);
 }

select count(*) from t_user
 
select username from t_user where id = 2

<?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="cn.mybatis.mapper.usermapper">
 
 <!--定义resultmap
 type:resultmap最终映射的java对象类型
 id:对resultmap的标识
 -->
 <resultmap id="userresultmap" type="user">
 <!--id:标识查询结果集中的唯一标识-->
 <id column="_id" property="id"></id>
 <!--result:标识查询结果集中其他列的标识-->
 <result column="_username" property="username"></result>
 <result column="_password" property="password"></result>
 <result column="_sex" property="sex"></result>
 <result column="_address" property="address"></result>
 </resultmap>
 
 <select id="finduserbyid_resultmap" parametertype="int" resultmap="userresultmap">
 select id _id, username _username, password _password, address _address, sex _sex from t_user where id = #{id}
 </select>
</mapper>

//测试resultmap
public user finduserbyid_resultmap(int id) throws exception;

@test
 public void testfinduserbyid_resultmap() throws exception {
 sqlsession sqlsession = sqlsessionfactory.opensession();
 usermapper usermapper = sqlsession.getmapper(usermapper.class);
 
 user user = usermapper.finduserbyid_resultmap(2);
 
 system.out.println(user);
 }

<select id="finduserlist" parametertype="cn.mybatis.po.uservo" resulttype="cn.mybatis.po.userpojo">
 select * from t_user
 <where>
  <if test="userpojo != null">
  <if test="userpojo.sex != null and userpojo.sex != ''">
   and sex = #{userpojo.sex}
  </if>
  <if test="userpojo.username != null and userpojo.username != ''">
   and username like '%${userpojo.username}%'
  </if>
  </if>
 </where>
 </select>

<!--定义sql片段-->
 <sql id="query_user_info">
 <if test="userpojo != null">
  <if test="userpojo.sex != null and userpojo.sex != ''">
  and sex = #{userpojo.sex}
  </if>
  <if test="userpojo.username != null and userpojo.username != ''">
  and username like '%${userpojo.username}%'
  </if>
 </if>
 </sql>

<select id="finduserlist" parametertype="cn.mybatis.po.uservo" resulttype="cn.mybatis.po.userpojo">
 select * from t_user
 <where>
  <include refid="query_user_info"></include>
 </where>
 </select>

<sql id="query_ids">
 <if test="ids != null">
  <!--
  select * from t_user where 1=1 and (id = 1 or id =2 or id = 3)
  cilleation: 指定的是输入参数集合的属性名
  item:每次遍历的名称
  open:开始遍历时拼接串
  close:结束遍历时候拼接的串
  separator:遍历的两个对象中间需要拼接的串
  -->
  <foreach collection="ids" item="item_id" open="and (" close=")" separator=" or ">
  id=#{item_id}
  </foreach>
 </if>
 </sql>

<select id="finduserbyids" parametertype="uservo" resulttype="userpojo">
select * from t_user
<where>
 <include refid="query_ids"></include>
</where>
</select>