java学习笔记整理springmvc学习JavaEE 学习专题

mybatis输入映射(包装类型)+输出映射+动态sql

2017-06-30  本文已影响117人  Mr_欢先生

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

需求：用户信息的综合查询列表总数，通过查询总数和上边用户综合查询列表才可以实现分页。

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

文集：mybatis框架学习

上一篇下一篇

猜你喜欢

热点阅读