Mybatis输入映射和输出映射
Mybatis支持输入映射哪些类型
简单类型、POJO(自定义类型),HashMap
POJO的包装的类型,就是类中还有其他类的对象
就是parameterType支持的输入映射的类型
<?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="com.hyx2.dao_two.mapper.UserMapper">
<select id="findUserById" parameterType="int" resultType="pojo.User">
select * from user where id = #{id}
</select>
</mapper>
输出映射
resultType
(1)支持的类型
基本类型、pojo、hashMap
(2)输出是POJO和POJO列表问题
不管是输出是单个POJO,还是POJO列表,在Mapper.xml中resultType都是一样的,只是在Mapper接口中返回值类型不一样。
(3)使用条件
使用resultType进行输出映射的时候,只要查询出来的列名和POJO的属性名一致,该列才可以映射成功。
只要查询出来的列名和属性名有一个能对应的上,就会创建POJO对象。
如果查询出来的列名和属性名没有一个能对应上,就不会创建POJO对象。
vo与po
po使用在持久层
vo使用在视图层,也就是使用mvc时向view中传入数据的模型
案例实现:
项目结构
UserMapper
import pojo.User;
import vo.UserQueryVo;
import java.util.List;
public interface UserMapper {
/**
* 找到用户
*/
public List<User> findUserList(UserQueryVo userQueryVo);
}
UserMapper.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="com.hyx2.vo_resulttype.mapper.UserMapper">
<select id="findUserList" parameterType="vo.UserQueryVo"
resultType="pojo.User">
select * from user where username like "%"#{user.username}"%" and sex = #{user.sex}
</select>
</mapper>
UserQueryVo
package vo;
import pojo.User;
public class UserQueryVo {
//用户信息
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
User,有一个数据库,数据库中有User表,表列的属性对于User类的属性
package pojo;
import java.util.Date;
public class User {
private Integer id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
public User() {
}
public User(String username, String sex, Date birthday, String address) {
this.username = username;
this.sex = sex;
this.birthday = birthday;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" + "id=" + id + ", username='" + username + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + ", address='" + address + '\'' + '}';
}
}
在sqlMapconfig中配置UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"></properties>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<!--数据库连接要素,数据库,数据库位置,账户,密码-->
<property name="driver" value="${jdbc.driver}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</dataSource>
</environment>
</environments>
<!--配置实体类的xml文件,在这文件中执行sql语句-->
<mappers>
<package name="mapper"></package>
</mappers>
</configuration>
db.properties数据库配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis01?characterEncoding=UTF-8
jdbc.username=root
jdbc.password=123
测试代码:
package test;
import mapper.UserMapper;
import pojo.User;
import vo.UserQueryVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Dmo01 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void fun0() throws IOException {
InputStream inputStream = Resources.getResourceAsStream( "sqlMapconfig.xml" );
//通过配置创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build( inputStream );
}
@Test
public void fun(){
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取UserMapper.class,通过反射机制可以找到UserMapper.xml文件
UserMapper userMapper = sqlSession.getMapper( UserMapper.class );
UserQueryVo userQueryVo = new UserQueryVo();
User user = new User();
user.setUsername( "小" );
user.setSex( "1" );
userQueryVo.setUser( user );
List<User> userList = userMapper.findUserList( userQueryVo );
for (User u : userList) {
System.out.println(u);
}
}
}
resultMap
使用条件
Mybatis使用resultMap完成高级输出结果映射
当列名和属性名不一致的情况,自己来指定如何进行映射
案例只需修改resultType中的案例中的UserMapper.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="com.hyx2.vo_resultmap.mapper.UserMapper">
<!--
resultMap作用:告诉人家,列和属性怎么建立关系
id:给一个唯一的标识
type:通过指定映射,映射成目标对象
-->
<resultMap id="userResultMap" type="pojo.User">
<!-- column:列,表的列,property:POJO中属性 -->
<id column="uid" property="id"></id>
<result column="uname" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</resultMap>
<select id="findUserList" parameterType="vo.UserQueryVo"
resultMap="userResultMap">
select id as uid,username as uname,birthday ,sex ,address from user where username
like "%"#{user.username}"%" and sex = #{user.sex}
</select>
</mapper>
动态SQL
需求(1):更新用户,前端修改了什么,SQL语句就包含哪些列
Update user set username=? where uid = ?
Update user set username=?,sex=? where uid = ?
Java 代码进行判断。
If(username ==null){
拼接SQL语句
}
需求(2):根据用户名和性别查询用户列表,
如果传递了用户名和性别,2个都作为条件
如果传递了用户名,没有传递性别,用户名作为条件
一个参数都没有传递,就没有条件
为了更好的解决上述需求,可以使用动态SQL进行实现
案例只需修改resultMap中的案例中的UserMapper.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="mapper.UserMapper">
<!--
resultMap作用:告诉人家,列和属性怎么建立关系
id:给一个唯一的标识
type:通过指定映射,映射成目标对象
-->
<resultMap id="userResultMap" type="pojo.User">
<!-- column:列,表的列,property:POJO中属性 -->
<id column="uid" property="id"></id>
<result column="uname" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</resultMap>
<select id="findUserList" parameterType="vo.UserQueryVo"
resultMap="userResultMap">
select id as uid,username as uname,birthday ,sex ,address
from user
<where>
<if test="user.username!=null and user.username!=''">
and username like "%"#{user.username}"%"
</if>
<if test="user.sex!=null and user.sex!=''">
and sex = #{user.sex}
</if>
</where>
</select>
</mapper>
测试代码如下:
import mapper.UserMapper;
import pojo.User;
import vo.UserQueryVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Dmo01 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void fun0() throws IOException {
InputStream inputStream = Resources.getResourceAsStream( "result_map_sqlMapconfig.xml" );
//通过配置创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build( inputStream );
}
@Test
public void fun(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper( UserMapper.class );
UserQueryVo userQueryVo = new UserQueryVo();
User user = new User();
user.setUsername( "王" );
userQueryVo.setUser( user );
List<User> userList = userMapper.findUserList( userQueryVo );
for (User u : userList) {
System.out.println(u);
}
}
}
SQL 片段,实现代码重用
<?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="mapper.UserMapper">
<!--
resultMap作用:告诉人家,列和属性怎么建立关系
id:给一个唯一的标识
type:通过指定映射,映射成目标对象
-->
<sql id="query_user_where">
<if test="user.username!=null and user.username!=''">
and username like "%"#{user.username}"%"
</if>
<if test="user.sex!=null and user.sex!=''">
and sex = #{user.sex}
</if>
</sql>
<resultMap id="userResultMap" type="pojo.User">
<!-- column:列,表的列,property:POJO中属性 -->
<id column="uid" property="id"></id>
<result column="uname" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</resultMap>
<select id="findUserList" parameterType="vo.UserQueryVo"
resultMap="userResultMap">
select id as uid,username as uname,birthday ,sex ,address
from user
<where>
<include refid="query_user_where"></include>
</where>
</select>
</mapper>
Foreach
案例:
先为上述案例中的UserMapper.class添加一个方法
package mapper;
import pojo.User;
import vo.UserQueryVo;
import java.util.List;
public interface UserMapper {
/**
* 找打用户在一定的范围内
* @param userQueryVo
* @return
*/
public List<User> findUserListByIds(UserQueryVo userQueryVo);
}
再写UserMapper.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="mapper.UserMapper">
<!--
resultMap作用:告诉人家,列和属性怎么建立关系
id:给一个唯一的标识
type:通过指定映射,映射成目标对象
-->
<resultMap id="userResultMap" type="pojo.User">
<!-- column:列,表的列,property:POJO中属性 -->
<id column="uid" property="id"></id>
<result column="uname" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</resultMap>
<select id="findUserListByIds" parameterType="vo.UserQueryVo"
resultMap="userResultMap">
select id as uid,username as uname,birthday ,sex ,address from user
<where>
<if test="ids!=null">
<foreach collection="ids" open="id in (" separator="," close=")" item="userId">
#{userId}
</foreach>
</if>
</where>
</select>
</mapper>
再为UserQueryVo.class添加一个新属性
package com.hyx2.vo_resultmap.vo;
import com.hyx2.vo_resultmap.pojo.User;
import java.util.ArrayList;
import java.util.List;
public class UserQueryVo {
//用户信息
private User user;
private List<Integer> ids = new ArrayList<Integer>( );
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
测试代码
import mapper.UserMapper;
import pojo.User;
import vo.UserQueryVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Dmo01 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void fun0() throws IOException {
InputStream inputStream = Resources.getResourceAsStream( "result_map_sqlMapconfig.xml" );
//通过配置创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build( inputStream );
}
@Test
public void fun() throws IOException {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper( UserMapper.class );
UserQueryVo userQueryVo = new UserQueryVo();
userQueryVo.getIds().add( 1 );
userQueryVo.getIds().add( 24 );
userQueryVo.getIds().add( 25 );
List<User> userList = userMapper.findUserListByIds( userQueryVo );
for (User u : userList) {
System.out.println(u);
}
}
}