Mybatis(二) SQL映射文件

SQL映射文件

　　单条件查询

1. 在UserMapper接口添加抽象方法

//根据用户名模糊查询
    List<User> getUserListByName();

2. 在UserMapper.xml里添加SQL

<!--根据用户名模糊查询-->
    <select id="getUserListByName" resultType="User" parameterType="String">
        select * from smbms_user where userName like concat('%',#{userName},'$')
    </select>

3. 测试类添加方法

//模糊查询
    @Test
    public void testUserListByName(){
        List<User> users = mapper.getUserListByName("李");
        for (User user : users){
            System.out.println(user.getUserName());
        }
    }

　　多条件查询以对象入参和以Map入参

1. 在UserMapper接口添加抽象方法

　　以对象入参查询

//对象入参查询
    List<User> getUserListByUser(User user);

　　1.2. 在UserMapper.xml里添加SQL

<!--以用户类型入参查询-->
    <select id="getUserListByUser" parameterType="User" resultType="User">
        select * from smbms_user
                    where userName like concat('%',#{userName},'%')
                    and userRole = #{userRole}
    </select>

　　1.3. 测试类添加方法

//以封装用户入参
    @Test
    public void testUserListByUser(){
        List<User> userList = new ArrayList<>();
        User user = new User();
        user.setUserName("赵");
        user.setUserRole(3);
        userList = mapper.getUserListByUser(user);
        for (User  user1: userList){
            System.out.println(user1.getUserName());
        }
    }

2.1. 以Map入参查询

　　在UserMapper.xml加

    <!-- 查询用户列表(参数：Map) -->
    <select id="getUserListByMap" resultType="User" parameterType="Map">
        select * from smbms_user
            where userName like CONCAT ('%',#{uName},'%') and userRole = #{uRole}
    </select>

　　在UserMapper接口添加对应xml

　　 /**
     * 根据用户名称查询用户列表(模糊查询)
     * @return
     */
    public List<User> getUserListByMap(Map<String,String> userMap);

　　在测试方法添加Map入参方法

//以map入参模糊查
    @Test
    public void getUserListByMap(){
        List<User> users = new ArrayList<User>();
        sqlSession = MyBatisUtil.createSqlSession();
        Map<String,String> userMap = new HashMap<String, String>();
        userMap.put("uName","赵");
        userMap.put("uRole","3");
        //第一种方式:调用selectList方法执行查询操作
//        users = sqlSession.selectList("org.hdax.dao.UserMapper.getUserListByName","赵");

        //第二种方式:调用getMapper(Mapper.class)执行dao接口方法来实现对数据库的查询操作
        users = sqlSession.getMapper(UserMapper.class).getUserListByMap(userMap);

        for (User user :users){
            logger.debug("testGetUserListByUserName userCode: " + user.getUserCode() + " and userName: " + user.getUserName());
        }
    }

　　自定义查询结果映射

1. User中加 age 和 userRoleName属性

public class User {
    private Integer id; //id 
    private String userCode; //用户编码
    private String userName; //用户名称
    private String userPassword; //用户密码
    private Integer gender;  //性别
    private Date birthday;  //出生日期
    private String phone;   //电话
    private String address; //地址
    private Integer userRole;    //用户角色ID
    private Integer createdBy;   //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy;     //更新者
    private Date modifyDate;   //更新时间
    
    private Integer age;//年龄
    private String userRoleName; //用户角色名称
   
　　 //省略 getter setter
    }

1. UserMapper.xml中添加映射语句

<!-- 查询用户表记录数 -->
    <select id="count" resultType="int">
        select count(1) as count from smbms_user
    </select>
    <!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
    <resultMap type="User" id="userList">
        <result property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="phone" column="phone"/>
        <result property="birthday" column="birthday"/>
        <result property="gender" column="gender"/>
        <result property="userRole" column="userRole"/>
        <result property="userRoleName" column="roleName"/>
    </resultMap>
    
    
    <!-- 查询用户列表(参数：对象入参) -->
    <select id="getUserList" resultMap="userList" parameterType="User">
        select u.*,r.roleName from smbms_user u,smbms_role r
            where u.userName like CONCAT ('%',#{userName},'%') 
                    and u.userRole = #{userRole} and u.userRole = r.id
    </select>

　　在接口添加方法

　　/**
     * 查询用户列表(参数：对象入参)
     * @return
     */
    public List<User> getUserList(User user);

　　测试方法

　　/**
     * 以User传入模糊查询
     */
    @Test
    public void getUserList(){
        List<User> users = new ArrayList<User>();
        sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserName("赵");
        user.setUserRole(3);

        //第二种方式:调用getMapper(Mapper.class)执行dao接口方法来实现对数据库的查询操作
        users = sqlSession.getMapper(UserMapper.class).getUserList(user);

        /**
         * 若设置resultMap的自动映射级别为NONE，
         * 那么没有进行映射匹配的属性（比如：address等）则输出为null
         * 若不设置resultMap的自动映射级别，则不管是否进行了映射，所有的属性值均可输出
         */
        for(User u: users){
            logger.debug("testGetUserList userCode: " + u.getUserCode() +
                    " and userName: " + u.getUserName() +
                    " and userRole: " + u.getUserRole() +
//                    " and userRoleName: " + u.getUserRoleName() +
                    " and age: " + u.getAge() +
                    " and address: " + u.getAddress());
        }
    }

模糊查询商品两表联查使用resultMap来自定义映射显示结果

　　1. 添加Bill类

package org.hdax.pojo;


import java.math.BigDecimal;
import java.util.Date;

public class Bill {

  private int id;                  //id
  private String billCode;         //账单编码
  private String productName;     //商品名称
  private String productDesc;     //商品描述
  private String productUnit;     //商品单位
  private BigDecimal productCount;  //商品数量
  private BigDecimal totalPrice;    //总金额
  private int isPayment;            //是否支付
  private int providerId;           //供应商ID
  private int createdBy;            //创建者
  private Date creationDate;        //创建时间
  private int modifyBy;           //更新者
  private Date modifyDate;        //更新时间

  private String providerName;    //供应商名称
　　//getter setter 省略
}

　　2. BillMapper.xml 配置文件

<?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="org.hdax.dao.BillMapper">
    <resultMap id="billList" type="Bill">
        <result property="id" column="id"/>
        <result property="billCode" column="billCode"/>
        <result property="productName" column="productName"/>
        <result property="providerName" column="proName"/>
        <result property="isPayment" column="isPayment"/>
        <result property="creationDate" column="creationDate"/>
    </resultMap>
    <!--查询订单列表-->
    <select id="getBillList" resultMap="billList">
        select b.*,p.proName from smbms_bill b,smbms_provider p
            where b.productName like CONCAT ('%',#{productName},'%')
                    and b.providerId = #{providerId}
                    and b.isPayment = #{isPayment}
                    and b.providerId = p.id
    </select>
</mapper>

　　3. 接口

package org.hdax.dao;

import org.hdax.pojo.Bill;

import java.util.List;

public interface BillMapper {
    /**
     * 查询订单列表
     * @param bill
     * @return
     */
    public List<Bill> getBillList(Bill bill);
}

　　4. 测试方法

import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.hdax.dao.BillMapper;
import org.hdax.pojo.Bill;
import org.hdax.util.MyBatisUtil;
import org.junit.Test;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class BillMapperTest {
    private Logger logger = Logger.getLogger(BillMapperTest.class);

    @Test
    public void testGetBillList(){
        SqlSession session = null;
        List<Bill> bills = new ArrayList<Bill>();
        session = MyBatisUtil.createSqlSession();
        Bill bill = new Bill();
        bill.setIsPayment(2);
        bill.setProductName("油");
        bill.setProviderId(7);
        bills = session.getMapper(BillMapper.class).getBillList(bill);

        for(Bill bill2: bills){
            logger.debug("testGetBillList id: " + bill2.getId() +
                    " and BillCode: " + bill2.getBillCode() +
                    " and ProductName: " + bill2.getProductName() +
                    " and ProviderName: " + bill2.getProviderName() +
                    " and TotalPrice: " + bill2.getTotalPrice() +
                    " and IsPayment: " + bill2.getIsPayment()+
                    " and CreationDate:" + new SimpleDateFormat("yyyy-MM-dd").format(bill2.getCreationDate()));
        }
        
        //关闭释放资源
        MyBatisUtil.closeSqlSession(session);
    }
}

增改删查操作

　　1. UserMapper.xml配置文件

　　 <!--增加用户-->
    <insert id="add" parameterType="User">
        insert into smbms_user (userCode,userName,userPassword,gender,birthday,phone,
                                address,userRole,createdBy,creationDate)
                values (#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},
                #{address},#{userRole},#{createdBy},#{creationDate})
    </insert>

    <!--修改用户信息-->
    <update id="modify" parameterType="User">
        update smbms_user set userCode=#{userCode},userName=#{userName},userPassword=#{userPassword},
                        gender=#{gender},birthday=#{birthday},phone=#{phone},address=#{address},
                        userRole=#{userRole},modifyBy=#{modifyBy},modifyDate=#{modifyDate}
                 where id = #{id}
    </update>

　　2. UserMapper接口

/**
     *增加用户
     * @param user
     * @return
     */
    public int add(User user);

    /**
     * 修改用户
     * @param user
     * @return
     */
    public int modify(User user);

　　测试方法

　　//添加用户
    @Test
    public void testAdd() throws ParseException {
        logger.debug("testAdd !===================");
        int count = 0;
        sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserCode("test001");
        user.setUserName("测试用户001");
        user.setUserPassword("1234567");
        Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1984-12-12");
        user.setBirthday(birthday);
        user.setCreationDate(new Date());
        user.setAddress("地址测试");
        user.setGender(1);
        user.setPhone("13688783697");
        user.setUserRole(1);
        user.setCreatedBy(1);
        user.setCreationDate(new Date());
        count = sqlSession.getMapper(UserMapper.class).add(user);

        logger.debug("testAdd count: " + count);
    }

    //修改用户
    @Test
    public void testModify() throws ParseException {
        int count = 0;
        User user = new User();
        user.setId(18);
        user.setUserCode("testmodify");
        user.setUserName("测试用户修改");
        user.setUserPassword("0000000");
        Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1980-10-10");
        user.setBirthday(birthday);
        user.setCreationDate(new Date());
        user.setAddress("地址测试修改");
        user.setGender(2);
        user.setPhone("13600002222");
        user.setUserRole(2);
        user.setModifyBy(1);
        user.setModifyDate(new Date());
        sqlSession = MyBatisUtil.createSqlSession();
        count = sqlSession.getMapper(UserMapper.class).modify(user);
        logger.debug("testModify count: " + count);
    }

多参数入参

　　修改当前用户密码

　　1. UserMapper.xml配置文件

　　<!--修改当前用户密码-->
    <update id="updatePwd">
        update smbms_user set userPassword=#{userPassword} where id=#{id}
    </update>

    <!-- 根据userId删除用户信息 -->
    <delete id="deleteUserById" parameterType="Integer">
        delete from smbms_user where id = #{id}
    </delete>

　　2. UserMapper接口

　　/**
     * 修改当前用户密码
     * @param id
     * @param pwd
     * @return
     */
    public int updatePwd(@Param("id")Integer id,@Param("userPassword")String pwd);

    /**
     * 根据userId删除用户信息
     * @param delid
     * @return
     */
    public int deleteUserById(@Param("id")Integer delid);

　　3. 测试方法

　　/**
     * 修改当前用户密码
     */
    @Test
    public void testUpdatePwd(){
        String pwd ="8888888";
        Integer id = 1;
        int count = 0;
        sqlSession = MyBatisUtil.createSqlSession();
        count = sqlSession.getMapper(UserMapper.class).updatePwd(id,pwd);

        logger.debug("testUpdatePwd count： " + count);
    }

    /**
     * 根据userId删除用户信息
     */
    @Test
    public void testDeleteUserById(){
        Integer delId = 17;
        int count = 0;
        sqlSession = MyBatisUtil.createSqlSession();
        count = sqlSession.getMapper(UserMapper.class).deleteUserById(delId);

        logger.debug(count);
    }

小结: 参数前加@Param注解:

　　public int updatePwd(@Param("id")Integer id,@Param("userPassword")String pwd);

　　使用注解@Param来传入多个参数,如@Param("userPassword")String pwd,相当于将该参数pwd重命名为userPassword,在映射的SQL中需要使用#{ 注解名称 },如#{ user Password }.

实现对供应商表的增删改操作

　　1. ProviderMapper.xml SQL映射文件

<!--添加供应商-->
    <insert id="addProvider" parameterType="Provider">
        insert into smbms_provider(proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,createdBy,creationDate)
        values (#{proCode},#{proName},#{proDesc},#{proContact},#{proPhone},#{proAddress},#{proFax},#{createdBy},#{creationDate})
    </insert>

    <!--根据供应商ID修改供应商信息-->
    <update id="updateProvider" parameterType="Provider">
        update smbms_provider set proName = #{proName} where id = #{id}
    </update>

    <!--根据供应商ID删除供应商信息-->
    <delete id="delProvider">
        delete from smbms_provider where id = #{id}
    </delete>

　　2. ProviderMapper 接口

/**
    * 增加供应商
    * @param provider
    * @return
    */
   public int addProvider(Provider provider);

   /**
    * 根据供应商ID修改供应商
    * @param provider
    * @return
    */
   public int updateProvider(Provider provider);

   /**
    * 根据供应商ID删除信息
    * @param integer
    * @return
    */
   public int delProvider(@Param("id") Integer integer);

　　3. 测试方法

//增加供应商
    @Test
    public void testaddProvider(){
        session = MyBatisUtil.createSqlSession();

        Provider provider = new Provider();
        provider.setProCode("BJ_GYS111");
        provider.setProName("供应商测试");
        provider.setProContact("张三");
        provider.setProAddress("供应商测试地址");
        provider.setProPhone("13566667777");
        provider.setCreatedBy(1);
        provider.setCreationDate(new Date());
        provider.setProFax("010-588876565");
        provider.setProDesc("供应商测试描述");

        int count = session.getMapper(ProviderMapper.class).addProvider(provider);
        logger.debug("testAdd count: " + count);
    }

    /**
     * 根据ID修改供应商
     */
    @Test
    public void updateProvider(){
        session = MyBatisUtil.createSqlSession();
        Provider provider = new Provider();
        provider.setId(18);
        provider.setProName("供应商");
        int count = session.getMapper(ProviderMapper.class).updateProvider(provider);

        logger.debug(count);
    }

    /**
     * 根据ID删除
     */
    @Test
    public void deleteProvider(){
        session = MyBatisUtil.createSqlSession();
        int count = session.getMapper(ProviderMapper.class).delProvider(18);
        logger.debug(count);
    }

实现高级结果映射

　　使用association处理一对一关联关系

　　1. 实体类User.java

public class User {

  private long id;    //id
  private String userCode;      //用户编码
  private String userName;      //用户名称
  private String userPassword;  //用户密码
  private long gender;          //性别
  private Date birthday;        //出生日期
  private String phone;         //电话
  private String address;       //地址
  private long userRole;        //用户角色ID
  private long createdBy;       //创建者
  private Date creationDate;    //创建者
  private long modifyBy;        //更新者
  private Date modifyDate;      //更新时间

  private Integer age;//年龄
  //private String userRoleName; //用户角色名称

  //association
  private Role role; //用户角色
　　//省略 getter setter 方法
}

　　2. UserMapper.xml SQL配置文件

　　<!-- 根据roleId获取用户列表 association start-->
    <!--第一种-->
   <!-- <resultMap id="userRoleResult" type="User">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="userRole" column="userRole"/>
        <association property="role" javaType="Role">
            <id property="id" column="id"/>
            <result property="roleCode" column="roleCode"/>
            <result property="roleName" column="roleName"/>
        </association>
    </resultMap>-->

    <!--第二种-->
    <resultMap type="User" id="userRoleResult">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode" />
        <result property="userName" column="userName" />
        <result property="userRole" column="userRole" />
        <association property="role" javaType="Role" resultMap="roleResult"/>
    </resultMap>

    <resultMap type="Role" id="roleResult">
        <id property="id" column="r_id"/>
        <result property="roleCode" column="roleCode"/>
        <result property="roleName" column="roleName"/>
    </resultMap>


    <select id="getUserListByRoleId" parameterType="Integer" resultMap="userRoleResult">
        select u.*,r.id as r_id,r.roleCode,r.roleName from smbms_user u,smbms_role r
            where u.userRole = #{userRole} and u.userRole = r.id
    </select>
    <!--association end-->

　　3. UserMapper接口

　　/**
     * 根据roleId获取用户列表
     * @param roleId
     * @return
     */
    public List<User> getUserListByRoleId(@Param("userRole")Integer roleId);

　　4. 测试方法

　　/**
     * 根据roleId获取用户列表
     */
    @Test
    public void getUserListByRoleIdTest(){
        List<User> users = new ArrayList<User>();
        Integer roleId = 3;
        sqlSession = MyBatisUtil.createSqlSession();
        users = sqlSession.getMapper(UserMapper.class).getUserListByRoleId(roleId);

        logger.debug("getUserListByRoleIdTest userList.size : " + users.size());
        for (User user : users){
            /*修改Map级别前*/
            /*logger.debug("userList =====> userName: " + user.getUserName() + ", Role: "
            + user.getRole().getId() + " --- " + user.getRole().getRoleCode()
            + " --- " + user.getRole().getRoleName());*/

            /*修改Map级别后*/
            logger.debug("userList =====> userName: " + user.getUserName()
                    +", <未做映射字段>userPassword: " + user.getUserPassword()
                    + ", Role: " + user.getRole().getId() + " --- "
                    + user.getRole().getRoleCode() +" --- " + user.getRole().getRoleName());
        }
    }

使用collection处理一对多关联关系

　　1. 实体类UserMapper

public class User {

  private long id;    //id
  private String userCode;      //用户编码
  private String userName;      //用户名称
  private String userPassword;  //用户密码
  private long gender;          //性别
  private Date birthday;        //出生日期
  private String phone;         //电话
  private String address;       //地址
  private long userRole;        //用户角色ID
  private long createdBy;       //创建者
  private Date creationDate;    //创建者
  private long modifyBy;        //更新者
  private Date modifyDate;      //更新时间
  private Integer userId;    //用户ID

    //collection
  private List<Address> addressList;//用户地址列表
  //省略 getter 和 setter 方法
}

　　2. UserMapper.xml SQL配置文件

<!-- 获取指定用户的地址列表(user表-address表：1对多关系) collection start-->
    <!--第一种-->
    <!--<resultMap type="User" id="userAddressResult">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <collection property="addressList" ofType="Address">
            <id property="id" column="a_id"/>
            <result property="postCode" column="postCode"/>
            <result property="tel" column="tel"/>
            <result property="contact" column="contact"/>
            <result property="addressDesc" column="addressDesc"/>
        </collection>
    </resultMap>-->

    <!--第二种-->
    <resultMap type="User" id="userAddressResult">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <collection property="addressList" ofType="Address" resultMap="addressResult"/>
    </resultMap>

    <resultMap type="Address" id="addressResult">
        <id property="id" column="a_id"/>
        <result property="postCode" column="postCode"/>
        <result property="tel" column="tel"/>
        <result property="contact" column="contact"/>
        <result property="addressDesc" column="addressDesc"/>
    </resultMap>

    <select id="getAddressListByUserId" parameterType="Integer" resultMap="userAddressResult">
        select u.*,a.id as a_id,a.contact,a.addressDesc,a.postCode,a.tel
                from smbms_user u,smbms_address a where u.id = a.userId and u.id=#{id}
    </select>
    <!-- collection end -->

　　3. UserMapper接口

　　/**
     * 获取指定用户的地址列表(collection)
     * @param userId
     * @return
     */
    public List<User> getAddressListByUserId(@Param("id")Integer userId);

　　4. 测试方法

　　/**
     * 根据用户Id获取指定用户的地址列表(collection)
     */
    @Test
    public void getAddressListByUserIdTest(){
        List<User> users = new ArrayList<User>();
        Integer userId = 1;
        users = MyBatisUtil.createSqlSession().getMapper(UserMapper.class).getAddressListByUserId(userId);
        for (User user : users){
            logger.debug("userList(include:addresslist) =====> userCode: " + user.getUserCode() + ", userName: " + user.getUserName());
            for(Address address : user.getAddressList()){
                logger.debug("address ----> id: " + address.getId() + ", contact: " + address.getContact()
                        + ", addressDesc: " + address.getAddressDesc() + ", tel: " + address.getTel()
                        + ", postCode: " + address.getPostCode());
            }
        }
    }

　　association 实现订单表的查询操作

　　1. 实体类

package org.hdax.pojo;


import java.math.BigDecimal;
import java.util.Date;

public class Bill {

    private int id;                  //id
    private String billCode;         //账单编码
    private String productName;     //商品名称
    private String productDesc;     //商品描述
    private String productUnit;     //商品单位
    private BigDecimal productCount;  //商品数量
    private BigDecimal totalPrice;    //总金额
    private int isPayment;            //是否支付
    private int providerId;           //供应商ID
    private int createdBy;            //创建者
    private Date creationDate;        //创建时间
    private int modifyBy;           //更新者
    private Date modifyDate;        //更新时间

    private Provider provider;      //供应商表
    //省略getter and setter
}

　　2. Mapper.xml 配置文件

<?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="org.hdax.dao.BillMapper">
    <resultMap id="billList" type="Bill">
        <result property="id" column="id"/>
        <result property="billCode" column="billCode"/>
        <result property="productName" column="productName"/>
        <result property="isPayment" column="isPayment"/>
        <result property="creationDate" column="creationDate"/>
        <association property="provider" javaType="Provider" resultMap="providerResult"/>
    </resultMap>

    <resultMap type="Provider" id="providerResult">
        <id property="id" column="p_id"/>
        <result property="proCode" column="proCode"/>
        <result property="proName" column="proName"/>
        <result property="proContact" column="proContact"/>
        <result property="proPhone" column="proPhone"/>
    </resultMap>

    <!--根据多参条件查询订单表-->
    <select id="getBillList2" resultMap="billList">
        SELECT p.`id` as p_id,b.`billCode`,b.`productName`,p.`proCode`,p.`proName`,p.`proContact`,p.`proPhone`,b.`totalPrice`,b.`isPayment`
            FROM smbms_bill b,smbms_provider p
            WHERE b.`productName` like concat('%', #{productName} ,'%')
            and b.providerId = #{providerId}
            and b.isPayment = #{isPayment}
            and b.`providerId` = p.`id`
    </select>
</mapper>

　　3. Mapper接口

public interface BillMapper {
    /**
     * 查询订单列表
     * @param bill
     * @return
     */
    public List<Bill> getBillList(Bill bill);

    /**
     * 根据条件查询订单
     * @param productName
     * @param providerId
     * @param isPayment
     * @return
     */
    public List<Bill> getBillList2(@Param("productName")String productName,
                                   @Param("providerId")Integer providerId,
                                   @Param("isPayment")Integer isPayment);
}

　　4. 测试方法

import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.hdax.dao.BillMapper;
import org.hdax.pojo.Bill;
import org.hdax.util.MyBatisUtil;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class BillMapperTest {
    private Logger logger = Logger.getLogger(BillMapperTest.class);

    /**
     * 根据多参条件查询订单表
     */
    @Test
    public void testGetBillList2(){
        SqlSession session1 = null;
        List<Bill> bills = new ArrayList<Bill>();
        try {
            session1 = MyBatisUtil.createSqlSession();
            String productName = "油";
            int providerId = 7;
            int isPayment = 2;
            bills = session1.getMapper(BillMapper.class).getBillList2(productName, providerId, isPayment);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(session1);
        }
        for (Bill bill : bills){
            logger.debug("testGetBillList id: " + bill.getId() +
                    " and BillCode: " + bill.getBillCode() +
                    " and ProductName: " + bill.getProductName() +
                    " and totalPrice: " + bill.getTotalPrice() +
                    " and isPayment: " + bill.getIsPayment() +
                    " , Provider : " + bill.getProvider().getId() +
                    " and providerCode: " + bill.getProvider().getProCode() +
                    " and providerName: " + bill.getProvider().getProName()+
                    " and proContact: " + bill.getProvider().getProContact()+
                    " and proPhone:" + bill.getProvider().getProPhone());
        }

    }
}

使用collection获取供应商及其订单列表

　　1. 修改Provider实体类

package org.hdax.pojo;

import java.util.Date;
import java.util.List;

public class Provider {

  private long id;            //id
  private String proCode;     //供应商编码
  private String proName;     //供应商名称
  private String proDesc;     //供应商描述
  private String proContact;  //供应商联系人
  private String proPhone;    //供应商电话
  private String proAddress;  //供应商地址
  private String proFax;      //供应商传真
  private long createdBy;     //创建者
  private Date creationDate;    //创建时间
  private Date modifyDate;      //更新者
  private long modifyBy;      //更新时间

  //collection
  private List<Bill>billList; //订单列表
  //省略getter  and setter  
}

　　2. ProviderMapper.xml 配置文件

　　<!-- 获取指定供应商的订单列表(provider表-bill表：1对多关系) collection start-->
    <resultMap id="providerBillResult" type="Provider">
        <id property="id" column="id"/>
        <result property="proCode" column="proCode"/>
        <result property="proName" column="proName"/>
        <result property="proContact" column="proContact"/>
        <result property="proPhone" column="proPhone"/>
        <collection property="billList" ofType="Bill" resultMap="billResult"/>
    </resultMap>

    <resultMap id="billResult" type="Bill">
        <id property="id" column="b_id"/>
        <result property="billCode" column="billCode"/>
        <result property="productName" column="productName"/>
        <result property="totalPrice" column="totalPrice"/>
        <result property="isPayment" column="isPayment"/>
    </resultMap>

    <!--根据供应商ID查询所有订单-->
    <select id="getBillListByProviderId" parameterType="Integer" resultMap="providerBillResult">
        SELECT p.*,b.`billCode`,b.`productName`,b.`totalPrice`,b.`isPayment`
        FROM smbms_provider AS p INNER JOIN  smbms_bill AS b
        ON  p.`id` = #{id}
    </select>
    <!-- collection end -->

　　3. ProviderMapper接口

　　/**
    * 获取指定供应商的订单列表(collection)
    * @param id
    * @return
    */
   public Provider getBillListByProviderId(@Param("id")Integer id);

　　4. 测试方法

　　/**
     * 取指定供应商的订单列表(provider表-bill表：1对多关系)
     */
    @Test
    public void getProviderId(){
        session = MyBatisUtil.createSqlSession();
        Provider provider = null;
        Integer providerId = 4;
        provider = session.getMapper(ProviderMapper.class).getBillListByProviderId(providerId);
        if (null != provider){
            logger.debug("provider(include:billlist) =====> proCode: " + provider.getProCode()
                    + ", proName: " + provider.getProName() + ", proContact: " + provider.getProContact()
                    + ", proPhone: " + provider.getProPhone());
            if (provider.getBillList().size()>0){
                for (Bill bill :provider.getBillList()){
                    logger.debug("bill ----> id: " + bill.getId() + ", billCode: " + bill.getBillCode()
                            + ", productName: " + bill.getProductName() + ", totalPrice: " + bill.getTotalPrice()
                            + ", isPayment: " + bill.getIsPayment());
                }
            }else{
                logger.debug("该供应商下无订单");
            }
        }else{
            logger.debug("无此供应商！");
        }
    }

使用foreach完成复杂查询

　　foreach迭代数组类型的入参

　　1. UserMapper.xml配置文件

　　<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_array -->
    <select id="getUserByRoleId_foreach_array" resultMap="userMapRole">
        select * from smbms_user where userRole in
            <foreach collection="array" item="roleIds" open="(" separator=","  close=")">
                #{roleIds}
            </foreach>
    </select>
    <resultMap id="userMapRole" type="User">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
    </resultMap>

　　2. UserMapper接口

　　/**
     * 根据用户角色列表，获取该角色列表下用户列表信息-foreach_array
     * @param roleIds
     * @return
     */
    public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);

　　3. 测试方法

　　//根据用户角色列表，获取该角色列表下用户列表信息-foreach_array
    @Test
    public void testGetUserByRoleId_foreach_array(){
        List<User> userList = new ArrayList<User>();
        Integer[] roleIds = {2,3};
        sqlSession = MyBatisUtil.createSqlSession();
        userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds);

        logger.debug("userList.size ----> " + userList.size());

        for (User user : userList){
            logger.debug("user ===========> id: " + user.getId()+
                    ", userCode: " + user.getUserCode() +
                    ", userName: " + user.getUserName() +
                    ", userRole: " + user.getUserRole());
        }
    }

foreach迭代List类型的入参

　　1. UserMapper.xml配置文件

　　<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list -->
    <select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
        select * from smbms_user where userRole in
            <foreach collection="list" item="roleList" open="(" separator="," close=")">
                #{roleList}
            </foreach>
    </select>
    <resultMap id="userMapByRole" type="User">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
    </resultMap>

　　2. UserMapper接口

　　 /**
     * 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list
     * @param roleList
     * @return
     */
    public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);

　　3. 测试方法

　　//根据用户角色列表，获取该角色列表下用户列表信息-foreach_list
    @Test
    public void testGetUserByRoleId_foreach_list(){
        List<User> userList =  new ArrayList<User>();
        List<Integer> roleList = new ArrayList<Integer>();
        roleList.add(2);
        roleList.add(3);
        sqlSession = MyBatisUtil.createSqlSession();
        userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(roleList);

        logger.debug("userList.size ----> " + userList.size());
        for(User user : userList){
            logger.debug("user ===========> id: " + user.getId()+
                    ", userCode: " + user.getUserCode() +
                    ", userName: " + user.getUserName() +
                    ", userRole: " + user.getUserRole());
        }
    }

使用动态SQL语句foreach获取指定供应商列表下的订单列表

　　1. BillMapper.xml配置文件

　　<!-- 根据供应商列表，获取该供应商列表下订单列表信息-foreach_array -->
    <select id="getBillByProviderId_foreach_array" resultMap="billMapByProIds">
        select * from smbms_bill where providerId in
        <foreach collection="array" item="proIds" open="(" separator="," close=")">
            #{proIds}
        </foreach>
    </select>
    <resultMap id="billMapByProIds" type="Bill">
        <id property="id" column="id"/>
        <result property="billCode" column="billCode"/>
        <result property="productName" column="productName"/>
        <result property="totalPrice" column="totalPrice"/>
    </resultMap>

　　2. BillMapper接口

　　/**
     * 根据供应商列表，获取该供应商列表下订单列表信息-foreach_array
     * @param proIds
     * @return
     */
    public List<Bill> getBillByProviderId_foreach_array(Integer[] proIds);

　　3. 测试方法

　　//根据供应商列表，获取该供应商列表下订单列表信息-foreach_array
    @Test
    public void testGetBillByProviderId_foreach_array(){
        SqlSession session = null;
        List<Bill> bills = new ArrayList<Bill>();
        Integer[] proIds = {1,14};

        try {
            session = MyBatisUtil.createSqlSession();
            bills = session.getMapper(BillMapper.class).getBillByProviderId_foreach_array(proIds);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(session);
        }

        logger.debug("billList.size ----> " + bills.size());
        for(Bill bill : bills){
            logger.debug("bill ===========> id: " + bill.getId()+
                    ", billCode: " + bill.getBillCode() +
                    ", productName: " + bill.getProductName() +
                    ", totalPrice: " + bill.getTotalPrice() +
                    ", providerId: " + bill.getProviderId());
        }
    }

以上把参数类型改为List再次实现功能

　　1. BillMapper.xml配置文件

　　<resultMap id="billMapByProIds" type="Bill">
        <id property="id" column="id"/>
        <result property="billCode" column="billCode"/>
        <result property="productName" column="productName"/>
        <result property="totalPrice" column="totalPrice"/>
    </resultMap>

    <!-- 根据供应商列表，获取该供应商列表下订单列表信息-foreach_list -->
    <select id="getBillByProviderId_foreach_list" resultMap="billMapByProIds">
        select * from smbms_bill where providerId in
        <foreach collection="list" item="proList" open="(" separator="," close=")">
            #{proList}
        </foreach>
    </select>

　　2. BillMapper接口

　　/**
     * 根据供应商列表，获取该供应商列表下订单列表信息-foreach_list
     * @param proList
     * @return
     */
    public List<Bill> getBillByProviderId_foreach_list(List<Integer> proList);

　　3. 测试方法

//根据供应商列表，获取该供应商列表下订单列表信息-foreach_list
    @Test
    public void testGetBillByProviderId_foreach_list(){
        SqlSession sqlSession = null;
        List<Bill> billList = new ArrayList<Bill>();
        List<Integer> proList = new ArrayList<Integer>();
        proList.add(1);
        proList.add(14);
        try {
            sqlSession = MyBatisUtil.createSqlSession();
            billList = sqlSession.getMapper(BillMapper.class).getBillByProviderId_foreach_list(proList);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        logger.debug("billList.size ----> " + billList.size());
        for(Bill bill : billList){
            logger.debug("bill ===========> id: " + bill.getId()+
                    ", billCode: " + bill.getBillCode() +
                    ", productName: " + bill.getProductName() +
                    ", totalPrice: " + bill.getTotalPrice() +
                    ", providerId: " + bill.getProviderId());
        }
  }

foreach迭代Map类型的入参

　　多参和单参

　　1. UserMapper.xml配置文件

　　<resultMap id="userMapByRole" type="User">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
    </resultMap>


    <!-- 根据用户角色列表和性别(多参数)，获取该角色列表下并指定性别的用户列表信息-foreach_map -->
    <select id="getUserByConditionMap_foreach_map" resultMap="userMapByRole">
        select * from smbms_user where gender = #{gender} and userRole in
        <foreach collection="roleIds" item="roleMap" open="(" separator="," close=")">
            #{roleMap}
        </foreach>
    </select>

    <!-- 根据用户角色列表(单参数)，获取该角色列表下用户列表信息-foreach_map -->
    <select id="getUserByRoleId_foreach_map" resultMap="userMapByRole">
        select * from smbms_user where userRole in
        <foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
            #{roleMap}
        </foreach>
    </select>

　　2. UserMapper接口

　　/**
     * 根据用户角色列表和性别(多参数)，获取该角色列表下指定性别的用户列表信息-foreach_map
     * @param conditionMap
     * @return
     */
    public List<User> getUserByConditionMap_foreach_map(Map<String,Object> conditionMap);

    /**
     * 根据用户角色列表，获取该角色列表下用户列表信息-foreach_map(单参数封装成map)
     * @param roleMap
     * @return
     */
    public List<User> getUserByRoleId_foreach_map(Map<String,Object> roleMap);

　　3. 测试方法

　　//根据用户角色列表和性别(多参数)，获取该角色列表下指定性别的用户列表信息-foreach_map
    @Test
    public void testGetUserByConditionMap_foreach_map() {
        List<User> userList = new ArrayList<User>();
        Map<String, Object> conditionMap = new HashMap<String, Object>();
        List<Integer> roleList = new ArrayList<Integer>();
        roleList.add(2);
        roleList.add(3);
        conditionMap.put("gender", 1);
        conditionMap.put("roleIds", roleList);
        sqlSession = MyBatisUtil.createSqlSession();
        userList = sqlSession.getMapper(UserMapper.class).getUserByConditionMap_foreach_map(conditionMap);

        logger.debug("userList.size ----> " + userList.size());
        for (User user : userList) {
            logger.debug("user ===========> id: " + user.getId() +
                    ", userCode: " + user.getUserCode() +
                    ", userName: " + user.getUserName() +
                    ", gender: " + user.getGender() +
                    ", userRole: " + user.getUserRole());
        }
    }

    //根据用户角色列表，获取该角色列表下用户列表信息-foreach_map(单参数封装成map)
    @Test
    public void testGetUserByRoleId_foreach_map() {
        List<User> userList = new ArrayList<User>();
        List<Integer> roleList = new ArrayList<Integer>();
        roleList.add(2);
        roleList.add(3);
        Map<String, Object> roleMap = new HashMap<String, Object>();
        roleMap.put("rKey", roleList);
        sqlSession = MyBatisUtil.createSqlSession();
        userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_map(roleMap);

        logger.debug("userList.size ----> " + userList.size());
        for (User user : userList) {
            logger.debug("user ===========> id: " + user.getId() +
                    ", userCode: " + user.getUserCode() +
                    ", userName: " + user.getUserName() +
                    ", userRole: " + user.getUserRole());
        }
    }

    //在test之后执行
    @After
    public void close() {
        if (null != sqlSession) {
            sqlSession.commit();
        }
        MyBatisUtil.closeSqlSession(sqlSession);
    }

使用动态SQL语句foreach获取多参数下的订单列表

　　1. BillMapper.xml配置文件

　　<!-- 根据供应商列表和订单编码(多参数)，获取该供应商列表下指定订单编码的订单列表表信息-foreach_map -->
    <select id="getBillByConditionMap_foreach_map" resultMap="billMapByProIds">
        select * from smbms_bill 
            where billCode like CONCAT ('%',#{billCode},'%') and providerId in 
            <foreach collection="providerIds" item="proList" open="(" separator="," close=")">
                #{proList}
            </foreach>
    </select>

    <resultMap type="Bill" id="billMapByProIds">
        <id property="id" column="id"/>
        <result property="billCode" column="billCode"/>
        <result property="productName" column="productName"/>
        <result property="totalPrice" column="totalPrice"/>
    </resultMap>

　　2. BillMapper接口

　　/**
     * 根据供应商列表和订单编码(多参数)，获取该供应商列表下指定订单编码的订单列表表信息-foreach_map
     * @param conditionMap
     * @return
     */
    public List<Bill>  getBillByConditionMap_foreach_map(Map<String,Object> conditionMap);

　　3. 测试方法

　　//根据供应商列表和订单编码(多参数)，获取该供应商列表下指定订单编码的订单列表表信息-foreach_map
    @Test
    public void testGetBillByConditionMap_foreach_map(){
        SqlSession sqlSession = null;
        List<Bill> billList = new ArrayList<Bill>();
        Map<String, Object> conditionMap = new HashMap<String,Object>();
        List<Integer> proList = new ArrayList<Integer>();
        proList.add(1);
        proList.add(14);
        conditionMap.put("billCode", "BILL2016");
        conditionMap.put("providerIds",proList);
        try {
            sqlSession = MyBatisUtil.createSqlSession();
            billList = sqlSession.getMapper(BillMapper.class).getBillByConditionMap_foreach_map(conditionMap);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        logger.debug("billList.size ----> " + billList.size());
        for(Bill bill : billList){
            logger.debug("bill ===========> id: " + bill.getId()+
                    ", billCode: " + bill.getBillCode() +
                    ", productName: " + bill.getProductName() +
                    ", totalPrice: " + bill.getTotalPrice() +
                    ", providerId: " + bill.getProviderId());
        }
    }

choose( when, otherwise )

　　1. UserMapper.xml配置文件

　　<!-- 查询用户列表(choose) -->
    <select id="getUserList_choose" resultType="User">
        select * from smbms_user where 1=1
        <choose>
            <when test="userName != null and userName != ''">
                and userName like CONCAT ('%',#{userName},'%')
            </when>
            <when test="userCode != null and userCode != ''">
                and userCode like CONCAT ('%',#{userCode},'%')
            </when>
            <when test="userRole != null">
                and userRole=#{userRole}
            </when>
            <!-- 没有符合的条件后执行 -->
            <otherwise>
                <!-- and YEAR(creationDate) = YEAR(NOW()) -->
                and YEAR(creationDate) = YEAR(#{creationDate})
            </otherwise>
        </choose>
    </select>

　　2. UserMapper接口

　　/**
     * 查询用户列表(choose)
     * @param userName
     * @param roleId
     * @param userCode
     * @param creationDate
     * @return
     */
    public List<User> getUserList_choose(@Param("userName")String userName,
                                         @Param("userRole")Integer roleId,
                                         @Param("userCode")String userCode,
                                         @Param("creationDate") Date creationDate);

　　3. 测试方法

　　//查询用户列表(choose)
    @Test
    public void testGetUserList_choose() throws ParseException {
        List<User> userList = new ArrayList<User>();
        sqlSession = MyBatisUtil.createSqlSession();
        String userName = "";
        Integer roleId = null;
        String userCode = "";
        Date creationDate = new SimpleDateFormat("yyyy-MM-dd").parse("2016-01-01");
        userList = sqlSession.getMapper(UserMapper.class).getUserList_choose(userName, roleId, userCode, creationDate);

        logger.debug("userlist.size ----> " + userList.size());
        for (User user : userList) {
            logger.debug("testGetUserList_choose=======> id: " + user.getId() +
                    " and userCode: " + user.getUserCode() +
                    " and userName: " + user.getUserName() +
                    " and userRole: " + user.getUserRole() +
                    " and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(user.getCreationDate()));
        }
    }

    //在test之后执行
    @After
    public void close() {
        if (null != sqlSession) {
            sqlSession.commit();
        }
        MyBatisUtil.closeSqlSession(sqlSession);
    }

使用动态SQL语句choose查询供应商列表

　　查询条件:供应商编码,供应商名称,供应商联系人,(模糊查询),查询时间在本年内.

　　1. ProviderMapper.xml配置文件

　　<!-- 查询供应商列表(choose) -->
    <select id="getProviderList_choose" resultType="Provider">
        select * from smbms_provider where 1=1
        <choose>
            <when test="proName != null and proName != ''">
                and proName like CONCAT ('%',#{proName},'%')
            </when>
            <when test="proCode != null and proCode != ''">
                and proCode like CONCAT ('%',#{proCode},'%')
            </when>
            <when test="proContact != null and proContact != ''">
                and proContact like CONCAT ('%',#{proContact},'%')
            </when>
            <!-- when没有满足的 执行 -->
            <otherwise>
                and YEAR(creationDate) = YEAR(NOW())
            </otherwise>
        </choose>
    </select>

　　2. ProviderMapper接口

　　/**
    * 查询供应商列表(choose)
    * @param proCode
    * @param proName
    * @param proContact
    * @return
    */
   public List<Provider> getProviderList_choose(@Param("proCode")String proCode,
                                                @Param("proName")String proName,
                                                @Param("proContact")String proContact);

　　3. 测试方法

　　//查询供应商列表(choose)
    @Test
    public void testGetProviderList_choose(){
        SqlSession sqlSession = null;
        List<Provider> providerList = new ArrayList<Provider>();
        try {
            sqlSession = MyBatisUtil.createSqlSession();
            String proName = "北京";
            String proCode = "";
            String proContact = "";
            providerList = sqlSession.getMapper(ProviderMapper.class).getProviderList_choose(proCode,proName,proContact);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        logger.debug("providerList.size ----> " + providerList.size());
        for(Provider provider: providerList){
            logger.debug("testGetProviderList_choose=======> id: " + provider.getId() +
                    " and proCode: " + provider.getProCode() +
                    " and proName: " + provider.getProName() +
                    " and proContact: " + provider.getProContact() +
                    " and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(provider.getCreationDate()));
        }
    }

    //在test之后执行
    @After
    public void close(){
        if(null != session){
            session.commit();
        }
        MyBatisUtil.closeSqlSession(session);
    }

实现分页

　　1. UserMapper.xml配置文件

　　<!-- 查询用户列表(分页显示) -->
    <select id="getUserList" resultMap="userList">
        select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole = r.id
        <if test="userRole != null">
            and u.userRole = #{userRole}
        </if>
        <if test="userName != null and userName != ''">
            and u.userName like CONCAT ('%',#{userName},'%')
        </if>
        order by creationDate DESC limit #{from},#{pageSize}
    </select>

　　2. UserMapper接口

　　/**
     * 查询用户列表(分页显示)
     * @param userName
     * @param roleId
     * @param currentPageNo
     * @param pageSize
     * @return
     */
    public List<User> getUserList(@Param("userName")String userName,
                                  @Param("userRole")Integer roleId,
                                  @Param("from")Integer currentPageNo,
                                  @Param("pageSize")Integer pageSize);

　　3. 测试方法

　　//查询用户列表(分页显示)
    @Test
    public void testGetUserList(){
        SqlSession sqlSession = null;
        List<User> userList = new ArrayList<User>();
        try {
            sqlSession = MyBatisUtil.createSqlSession();
            String userName = "";
            Integer roleId = null;
            Integer pageSize = 5;
            Integer currentPageNo = 0;
            userList = sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId,currentPageNo,pageSize);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        logger.debug("userlist.size ----> " + userList.size());
        for(User user: userList){
            logger.debug("testGetUserList=======> id: " + user.getId() +
                    " and userCode: " + user.getUserCode() +
                    " and userName: " + user.getUserName() +
                    " and userRole: " + user.getUserRole() +
                    " and userRoleName: " + user.getUserRoleName() +
                    " and age: " + user.getAge() +
                    " and phone: " + user.getPhone() +
                    " and gender: " + user.getGender()+
                    " and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(user.getCreationDate()));
        }
    }

    //在test之后执行
    @After
    public void close() {
        if (null != sqlSession) {
            sqlSession.commit();
        }
        MyBatisUtil.closeSqlSession(sqlSession);
    }

实现查询供应商列表和订单列表的分页显示

　　为供应商管理模块的查询供应商列表功能增加分页功能显示

　　列表结果均按照创建时间降序排列

　　1. ProviderMapper.xml配置文件

　　<!-- 查询供应商列表 (分页显示)-->
    <select id="getProviderList" resultType="Provider">
        select * from smbms_provider
        <trim prefix="where" prefixOverrides="and | or">
            <if test="proCode != null and proCode != ''">
                and proCode like CONCAT ('%',#{proCode},'%')
            </if>
            <if test="proName != null and proName != ''">
                and proName like CONCAT ('%',#{proName},'%')
            </if>
        </trim>
        order by creationDate DESC limit #{from},#{pageSize}
    </select>

　　2. ProviderMapper接口

　　/**
    * 查询供应商列表(分页显示)
    * @param proCode
    * @param proName
    * @param currentPageNo
    * @param pageSize
    * @return
    */
   public List<Provider> getProviderList(@Param("proCode")String proCode,
                                         @Param("proName")String proName,
                                         @Param("from")Integer currentPageNo,
                                         @Param("pageSize")Integer pageSize);

　　3. 测试方法

　　//查询供应商列表 (分页显示)
    @Test
    public void testGetProviderList() {
        List<Provider> providerList = new ArrayList<Provider>();
        session = MyBatisUtil.createSqlSession();
        String proCode = "BJ";
        String proName = null;
        Integer pageSize = 5;
        Integer currentPageNo = 0;
        providerList = session.getMapper(ProviderMapper.class).getProviderList(proCode, proName, currentPageNo, pageSize);
        logger.debug("providerList size----> " + providerList.size());
        for (Provider provider : providerList) {
            logger.debug("testGetProviderList id: " + provider.getId() +
                    " and proCode: " + provider.getProCode() +
                    " and proName: " + provider.getProName() +
                    " and proPhone: " + provider.getProPhone() +
                    " and proContact: " + provider.getProContact() +
                    " and proFax: " + provider.getProFax() +
                    " and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(provider.getCreationDate()));
        }
    }

为订单管理功能模块的查询订单列表功能增加分页显示

　　列表结果均按照创建时间降序排列

　　1. 修改实体类

public class Bill {
    private Integer id;   //id 
    private String billCode; //账单编码 
    private String productName; //商品名称 
    private String productDesc; //商品描述 
    private String productUnit; //商品单位
    private BigDecimal productCount; //商品数量 
    private BigDecimal totalPrice; //总金额
    private Integer isPayment; //是否支付 
    private Integer providerId; //供应商ID 
    private Integer createdBy; //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy; //更新者
    private Date modifyDate;//更新时间
    
    private String providerName;//供应商名称
    
    //association
    //private Provider provider;
        //省略gterr 和 setter
}

　　1. BillMapper.xml配置文件

　　<!-- 根据条件查询订单表(分页显示) -->
    <select id="getBillList" resultType="Bill">
        select b.*,p.proName as providerName from smbms_bill b,smbms_provider p where b.providerId = p.id
        <if test="productName != null and productName != ''">
            and b.productName like CONCAT ('%',#{productName},'%')
        </if>
        <if test="providerId != null">
            and b.providerId = #{providerId}
        </if>
        <if test="isPayment != null">
            and b.isPayment = #{isPayment}
        </if>
        order by creationDate DESC limit #{from},#{pageSize}
    </select>

　　2. BillMapper接口

　　/**
     * 根据条件查询订单表(分页显示)
     * @param productName
     * @param providerId
     * @param isPayment
     * @param currentPageNo
     * @param pageSize
     * @return
     */
    public List<Bill> getBillList(@Param("productName")String productName,
                                  @Param("providerId")Integer providerId,
                                  @Param("isPayment")Integer isPayment,
                                  @Param("from")Integer currentPageNo,
                                  @Param("pageSize")Integer pageSize);

　　3. 测试方法

　　//根据条件查询订单表(分页显示)
    @Test
    public void testGetBillList() {
        SqlSession sqlSession = null;
        List<Bill> billList = new ArrayList<Bill>();
        try {
            sqlSession = MyBatisUtil.createSqlSession();
            String productName = null;
            Integer providerId = null;
            Integer isPayment = 2;
            Integer pageSize = 5;
            Integer currentPageNo = 0;
            billList = sqlSession.getMapper(BillMapper.class).getBillList(productName,providerId,isPayment,currentPageNo,pageSize);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        logger.debug("billList.size ---> " + billList.size());
        for(Bill bill: billList){
            logger.debug("testGetBillList id: " + bill.getId() +
                    " and BillCode: " + bill.getBillCode() +
                    " and ProductName: " + bill.getProductName() +
                    " and totalPrice: " + bill.getTotalPrice() +
                    " and isPayment: " + bill.getIsPayment() +
                    " and providerId : " + bill.getProviderId() +
                    " and providerName: " + bill.getProviderName() +
                    " and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(bill.getCreationDate()));
        }
    }

总结:

　　MyBatis在SQL映射文件中可以使用灵活,智能的动态SQL来实现SQL映射.

　　if+set: 用于更新操作.

　　if+where: 用于多条件查询.

　　if+trim: 用于多条件查询(替代where)或者更新操作(替代set).

　　choose(when , otherwise): 用于多条件查询选其一操作.

　　foreach: 用于复杂查询,主要用于in条件查询中,迭代集合,其中最关键的部分就是collection属性,根据不同的入参类型,该属性值亦不同.

　　　　入参对象为一个List实例,collection属性值为list.

　　　　入参对象为一个数组,collection属性值为array.

　　　　入参对象为多个,需要把它们封装为一个Map进行处理.

posted @ 2019-09-06 11:47 _L先生阅读(698) 评论(0) 编辑收藏举报

刷新页面返回顶部

Mybatis(二) SQL映射文件

公告