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进行处理.
