1.输入映射——包装类型
通过parameterType指定输入参数的类型，类型可以是简单类型，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;
测试实现
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
//测试前执行
public void setUp() throws IOException {
//配置文件
String resource = "mybatis-config.xml";
//的到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserList() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
//创建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中的属性名全不一致，没有创建pojo对象。
- 只要查询出来的列名和pojo属性有一个一致，就会创建pojo对象。
需求：用户信息的综合查询列表总数，通过查询总数和上边用户综合查询列表才可以实现分页。
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;
测试实现
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
//测试前执行
public void setUp() throws IOException {
//配置文件
String resource = "mybatis-config.xml";
//的到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserCount() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
//创建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();
//创建AdminMapper对象，mybatis自动生成mapper代理对象
AdminMapper adminMapper = sqlSession.getMapper(AdminMapper.class);
AdminBean adminBean= adminMapper.findUserByResultMmap(10);
System.out.println(adminBean);
}
3.动态sql语句
a.动态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.动态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
