1.輸入映射——包裝類型
通過parameterType指定輸入?yún)?shù)的類型路呜，類型可以是簡單類型惜傲，hashmap,pojo包裝類型
需求：查詢用戶綜合信息，需要傳入查詢條件（可能包括用戶信息凛虽，其他信息）
AdminBean.java
package cn.huan.mybatis;
/**
* Created by 馬歡歡 on 2017/6/29.
*/
public class AdminBean {
private int a_id;
private String a_nameid;
private String a_username;
private String a_password;
private int a_trank;
@Override
public String toString() {
return "AdminBean{" +
"a_id=" + a_id +
", a_nameid='" + a_nameid + '\'' +
", a_username='" + a_username + '\'' +
", a_password='" + a_password + '\'' +
", a_trank=" + a_trank +
'}';
}
public int getA_id() {
return a_id;
}
public void setA_id(int a_id) {
this.a_id = a_id;
}
public String getA_nameid() {
return a_nameid;
}
public void setA_nameid(String a_nameid) {
this.a_nameid = a_nameid;
}
public String getA_username() {
return a_username;
}
public void setA_username(String a_username) {
this.a_username = a_username;
}
public String getA_password() {
return a_password;
}
public void setA_password(String a_password) {
this.a_password = a_password;
}
public int getA_trank() {
return a_trank;
}
public void setA_trank(int a_trank) {
this.a_trank = a_trank;
}
}
AdminCustom.java //可擴展用戶信息
package cn.huan.mybatis;
/**
* Created by 馬歡歡 on 2017/6/30.
*/
public class AdminCustom extends AdminBean {
//可擴展用戶信息
}
AdminQuerVo.java
package cn.huan.mybatis;
/**
* Created by 馬歡歡 on 2017/6/30.
*/
public class AdminQuerVo {
private AdminCustom adminCustom;
public AdminCustom getAdminCustom() {
return adminCustom;
}
public void setAdminCustom(AdminCustom adminCustom) {
this.adminCustom = adminCustom;
}
}
mapper.xml映射文件
<!--用戶信息綜合查詢-->
<select id="findUserList" parameterType="cn.huan.mybatis.AdminQuerVo"
resultType="cn.huan.mybatis.AdminCustom">
select * from admin where a_trank=#{adminCustom.a_trank} and a_username like '%${adminCustom.a_username}%'
</select>
接口AdminMapper.java
/**
* 用戶信息綜合查詢
* @param adminQuerVo
* @return
* @throws Exception
*/
public List<AdminCustom> findUserList(AdminQuerVo adminQuerVo) throws Exception;
測試實現(xiàn)
package cn.mapper;
import cn.huan.mybatis.AdminBean;
import cn.huan.mybatis.AdminCustom;
import cn.huan.mybatis.AdminQuerVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* Created by 馬歡歡 on 2017/6/29.
*/
public class AdminMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
//測試前執(zhí)行
public void setUp() throws IOException {
//配置文件
String resource = "mybatis-config.xml";
//的到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//創(chuàng)建會話工廠
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserList() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
//創(chuàng)建AdminMapper對象隅茎，mybatis自動生成mapper代理對象
AdminMapper adminMapper = sqlSession.getMapper(AdminMapper.class);
AdminQuerVo adminQuerVo = new AdminQuerVo();
AdminCustom adminCustom = new AdminCustom();
adminCustom.setA_username("員");
adminCustom.setA_trank(3);
adminQuerVo.setAdminCustom(adminCustom);
List<AdminCustom> list = adminMapper.findUserList(adminQuerVo);
System.out.println(list);
}
}
2.輸出映射：
a.resultType
- 使用resultType進行輸出映射，只有查詢出來的列明和pojo中的屬性名一致薯鼠，該列才可以映射成功
- 如果查詢出來的列名和pojo中的屬性名全不一致择诈，沒有創(chuàng)建pojo對象。
- 只要查詢出來的列名和pojo屬性有一個一致出皇，就會創(chuàng)建pojo對象羞芍。
需求：用戶信息的綜合查詢列表總數(shù)，通過查詢總數(shù)和上邊用戶綜合查詢列表才可以實現(xiàn)分頁郊艘。
mapper.xml映射文件
<select id="findUserCount" parameterType="cn.huan.mybatis.AdminQuerVo"
resultType="int">
select count(*) from admin where a_trank=#{adminCustom.a_trank} and a_username like '%${adminCustom.a_username}%'
</select>
接口AdminMapper.java
public int findUserCount(AdminQuerVo adminQuerVo) throws Exception;
測試實現(xiàn)
package cn.mapper;
import cn.huan.mybatis.AdminBean;
import cn.huan.mybatis.AdminCustom;
import cn.huan.mybatis.AdminQuerVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* Created by 馬歡歡 on 2017/6/29.
*/
public class AdminMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
//測試前執(zhí)行
public void setUp() throws IOException {
//配置文件
String resource = "mybatis-config.xml";
//的到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//創(chuàng)建會話工廠
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserCount() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
//創(chuàng)建AdminMapper對象荷科，mybatis自動生成mapper代理對象
AdminMapper adminMapper = sqlSession.getMapper(AdminMapper.class);
AdminQuerVo adminQuerVo = new AdminQuerVo();
AdminCustom adminCustom = new AdminCustom();
adminCustom.setA_username("員");
adminCustom.setA_trank(3);
adminQuerVo.setAdminCustom(adminCustom);
int count= adminMapper.findUserCount(adminQuerVo);
System.out.println(count);
}
}
- 小結:查詢出來的結果集只有一行且一列,可以使用簡單類型進行輸出映射.
b.resultMap使用方法
如果查詢出來的列名和pojo的屬性名不一致。通過定義一個resultMap對列名和pojo屬性名之間作一個映射關系纱注。
1.定義 resultMap
2.使用 resultMap作為輸出映射類型
需求: 將 下面的sql語句用 AdminBean完成映射
SELECT a_nameid id_ ,a_username name_ FROM admin WHERE a_nameid=#{value}
1.定義resultMap
將查詢結果 和AdminBean做映射
<!--定義resultMap-->
<!--將 SELECT a_nameid id_ ,a_username name_ FROM admin WHERE a_nameid=#{value}查詢結果 和AdminBean做映射-->
<resultMap id="adminResultMap" type="adminBean">
<!--id：表示查詢結果的唯一標識
column:查詢出來的列名
property：type指定的pojo類型中的屬性名
-->
<id column="id_" property="a_nameid"/>
<!--result：對普通名稱映射定義
column:查詢出來的列名
property：type指定的pojo類型中的屬性名
-->
<result column="name_" property="a_username"/>
</resultMap>
<select id="findUserByResultMmap" parameterType="int"
resultMap="adminResultMap"> -- 定義的resultMap 如果這個resultMap在其它的mapper文件 前面需要加namedpace
SELECT a_nameid id_ ,a_username name_ FROM admin WHERE a_id=#{value}
</select>
接口AdminMapper.java
public AdminBean findUserByResultMmap(int id) throws Exception;
測試類：
@Test
public void testFindUserByResultMmap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
//創(chuàng)建AdminMapper對象畏浆，mybatis自動生成mapper代理對象
AdminMapper adminMapper = sqlSession.getMapper(AdminMapper.class);
AdminBean adminBean= adminMapper.findUserByResultMmap(10);
System.out.println(adminBean);
}
3.動態(tài)sql語句
a.動態(tài)sql語句--if判斷 及where
<!--用戶信息綜合查詢-->
<select id="findUserList" parameterType="cn.huan.mybatis.AdminQuerVo"
resultType="cn.huan.mybatis.AdminCustom">
select * from admin
<where>
<if test="adminCustom!=null">
<if test="adminCustom.a_trank!=null and adminCustom.a_trank!='' ">
and a_trank=#{adminCustom.a_trank}
</if>
<if test="adminCustom.a_username!=null and adminCustom.a_username!=''">
and a_username like '%${adminCustom.a_username}%'
</if>
</if>
</where>
</select>
b.動態(tài)sql片段
定義
<sql id="query_user_where">
<if test="adminCustom!=null">
<if test="adminCustom.a_trank!=null and adminCustom.a_trank!='' ">
and a_trank=#{adminCustom.a_trank}
</if>
<if test="adminCustom.a_username!=null and adminCustom.a_username!=''">
and a_username like '%${adminCustom.a_username}%'
</if>
</if>
</sql>
引用
<!--用戶信息綜合查詢-->
<select id="findUserList" parameterType="cn.huan.mybatis.AdminQuerVo"
resultType="cn.huan.mybatis.AdminCustom">
select * from admin
<where>
<!--如果refid指定id不在本mapper中 前面需要加上namespace-->
<include refid="query_user_where"></include>
</where>
</select>
c.sql--foreach
需求：
SELECT a_nameid id_ ,a_username name_ FROM admin WHERE a_username like '%${value}%' and( a_id=1 OR a_id=2);
SELECT a_nameid id_ ,a_username name_ FROM admin WHERE a_id IN(1,2,3);
<!--用戶信息綜合查詢-->
<select id="findUserList" parameterType="cn.huan.mybatis.AdminQuerVo"
resultType="cn.huan.mybatis.AdminCustom">
select * from admin
<!--collection:指定輸入對象中集合屬性
item:每次遍歷生成對象
open:開始遍歷時拼接串
close:結束遍歷時拼接串
separator:遍歷兩個對象中需要拼接的串
-->
<where>
<foreach collection="ids" item="admin_id" open="and(" close=")" separator="or">
id=#{admin_id}
</foreach>
</where>
</select>
<select id="findUserList" parameterType="cn.huan.mybatis.AdminQuerVo"
resultType="cn.huan.mybatis.AdminCustom">
select * from admin
<!--collection:指定輸入對象中集合屬性
item:每次遍歷生成對象
open:開始遍歷時拼接串
close:結束遍歷時拼接串
separator:遍歷兩個對象中需要拼接的串
-->
<where>
<foreach collection="ids" item="admin_id" open="and a_id in(" close=")" separator=",">
id=#{admin_id}
</foreach>
</where>
</select>
上一篇：mybatis全局配置文件mybatis-config.xml
