一 输入映射-包装类型
- 需求：查询用户综合信息，需要传入查询条件（可能包括用户信息，其他信息）
User.java
package entity;
import java.util.Date;
public class User {
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
UserCustom.java
package entity;
public class UserCustom extends User {
//扩展User类
}
UserQuerVo.java
package entity;
public class UserQuerVo {
private UserCustom userCustom;
//包装UserCustom
public UserCustom getUserCustom() {
return userCustom;
}
public void setUserCustom(UserCustom userCustom) {
this.userCustom = userCustom;
}
}
userMapper.xml
<!--综合信息查询 根据姓名和性别-->
<select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
SELECT * FROM user WHERE username = #{userCustom.username} AND sex LIKE "%${userCustom.sex}%"
</select>
UserDaoMapperTest.java
@Test
public void findUserByinfo() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
UserQuerVo userQuerVo = new UserQuerVo();
UserCustom userCustom = new UserCustom();
userCustom.setUsername("马云");
userCustom.setSex("男");
userQuerVo.setUserCustom(userCustom);
UserCustom userCustom1= userDaoMapper.findUserByinfo(userQuerVo);
System.out.println(userCustom1);
}
测试结果
二 输出映射
1 resultType
- 使用resultType进行输出映射，只有查询出来的列明和pojo中的属性名一致，该列才可以映射成功
userMapper.xml
<!--输出映射 pojo resultType-->
<select id="findUserCount" parameterType="entity.UserQuerVo" resultType="int">
SELECT COUNT(*) FROM user WHERE username LIKE "%${userCustom.username}%"
</select>
UserDaoMapper.java
//输出映射 pojo resultType
public int findUserCount(UserQuerVo userQuerVo) throws Exception;
UserDaoMapperTest.java
@Test
public void findUserCount() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
UserQuerVo userQuerVo = new UserQuerVo();
UserCustom userCustom = new UserCustom();
userCustom.setUsername("马");
userQuerVo.setUserCustom(userCustom);
int num = userDaoMapper.findUserCount(userQuerVo);
System.out.println(num);
}
测试结果
2 resultMap
- 如果查询出来的列名和pojo的属性名不一致。通过定义一个resultMap对列名和pojo属性名之间作一个映射关系。
- 1.定义 resultMap
- 2.使用 resultMap作为输出映射类型
userMapper.xml
<!--配置resultMap映射-->
<resultMap id="PojoresultMap" type="entity.User">
<id column="i" property="id"></id>
<result column="us" property="username"></result>
<result column="ad" property="address"></result>
</resultMap>
<!--输出映射 pojo resultMap-->
<select id="findUserByResultMap" parameterType="int" resultMap="PojoresultMap">
SELECT id i,username us, address ad FROM user WHERE id = #{userCustom.id}
</select>
UserDaoMapper.java
//输出映射 pojo resultMap
public User findUserByResultMap(int id) throws Exception;
UserDaoMapperTest.java
@Test
public void findUserByResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
User user = userDaoMapper.findUserByResultMap(10);
System.out.println(user);
}
测试结果
三 动态sql
1 解释
- mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接，组装
2 if 判断
- 需求 用户信息综合查询列表和用户信息列表总数这两个statement的定义使用动态sql。对查询条件进行判断，如果输入参数不为空才进行查询条件拼接
<select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
SELECT * FROM user
<where>
<if test="userCustom.username != null">
<if test="userCustom.username != null and userCustom.username != ''">
and username = #{userCustom.username}
</if>
<if test="userCustom.sex != null and userCustom.sex != ''">
and sex like "%${userCustom.sex}%"
</if>
</if>
</where>
</select>
测试执行.png
3 sql片段
userMapper.xml
<!--动态sql片段
id:sql片段唯一标识
基于单表来定义sql片段，这样的话这个sql片段可重用性才高，在sql片段可重用性才高
在sql片段不要包括where
-->
<sql id="qusery_user_where">
<if test="userCustom.username != null">
<if test="userCustom.username != null and userCustom.username != ''">
and username = #{userCustom.username}
</if>
<if test="userCustom.sex != null and userCustom.sex != ''">
and sex like "%${userCustom.sex}%"
</if>
</if>
</sql>
<select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
SELECT * FROM user
<where>
<!--sql片段引用-->
<include refid="qusery_user_where"></include>
</where>
4 foreach
userMapper.xml
<!--输入list集合 foreach遍历-->
<select id="findUserList" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
SELECT * FROM user WHERE
<!--collection:指定输入对象中集合属性
item:每次遍历生成对象
open:开始遍历时拼接串
close:结束遍历时拼接串
separator:遍历两个对象中需要拼接的串
-->
<foreach collection="ids" item="id" open="(" close=")" separator="or">
<!--每次遍历需要拼接的串-->
id = #{id}
</foreach>
</select>
UserDaoMapper.java
//
public UserCustom findUserList(UserQuerVo userQuerVo) throws Exception;
测试
@Test
public void findUserList() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
UserQuerVo userQuerVo = new UserQuerVo();
List ids = new ArrayList();
ids.add(1);
userQuerVo.setIds(ids);
UserCustom userList = userDaoMapper.findUserList(userQuerVo);
System.out.println(userList);
}
网友评论