3.Mybatis输入参数POJO包装类 输出参数简单类型、ResultMap类
User
package com.itheima.mybatis.pojo;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String 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 + "]";
}
}
Orders
public class Orders implements Serializable{
@Override
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + "]";
}
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//附加对象 用户对象
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
}
QueryVo
public class QueryVo implements Serializable{
private static final long serialVersionUID = 1L;
private User user;
private List<Integer> idsList;
private Integer[] ids;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Integer> getIdsList() {
return idsList;
}
public void setIdsList(List<Integer> idsList) {
this.idsList = idsList;
}
public Integer[] getIds() {
return ids;
}
public void setIds(Integer[] ids) {
this.ids = ids;
}
}
sqlMapConfig.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>
<!-- 是用resource属性加载外部配置文件 -->
<properties resource="db.properties"/>
<!-- 别名 -->
<typeAliases>
<package name="com.itheima.mybatis.pojo"/>
</typeAliases>
<!-- 和spring整合后 environments配置将废除 -->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理 -->
<transactionManager type="JDBC" />
<!-- 数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- Mapper的位置 -->
<mappers>
<package name="com.itheima.mybatis.mapper"/>
</mappers>
</configuration>
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.itheima.mybatis.mapper.UserMapper">
<!-- 根据用户的名字模糊查询 -->
<select id="findUserByQueryVo" parameterType="QueryVo" resultType="User">
select * from user where username like '%${user.username}%'
</select>
<!-- 查询数据条数 -->
<select id="countUser" resultType="Integer">
select count(*) from user
</select>
<!-- 根据性别和名字查询用户
where 可以去掉第一个前AND
-->
<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
select * from user
<where>
<if test="sex != null and sex != ''">
and sex= #{sex}
</if>
<if test="username != null and username != ''">
and username like '%${username}%'
</if>
</where>
</select>
<!-- 根据多个id查询用户信息 (1,2,3) -->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
select * from user
<where>
id in
<foreach collection="idsList" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</where>
</select>
</mapper>
OrdersMapper.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.itheima.mybatis.mapper.OrderMapper"> <resultMap type="Orders" id="orders"> <id column="id" property="id"/> <result column="user_id" property="userId"/> </resultMap> <!-- 查询订单表order的所有数据 --> <!-- <select id="selectOrdersList" resultType="Orders"> --> <!-- 数据库字段名与pojo的属性名不一致时，采用手动映射resultMap类型 --> <select id="selectOrdersList" resultMap="orders"> select id,user_id,number,createtime,note from orders </select> </mapper>
UserMapper
public interface UserMapper {
//遵循四个原则
//1.接口 方法名 ==User.xml中的id名
//2.返回值类型 与 Mapper.xml文件中返回值类型要一致
//3.方法的入参类型要与Mapper.xml中入参的类型一致
//4.命名空间 绑定此接口
public List<User> findUserByQueryVo(QueryVo vo);
//查询数据条数
public Integer countUser();
//根据性别和名字查询用户
public List<User> selectUserBySexAndUsername(User user);
//根据多个id查询用户信息
// public List<User> selectUserByIds(Integer[] ids); //array
// public List<User> selectUserByIds(List<Integer> ids);
public List<User> selectUserByIds(QueryVo vo);
}
OrdersMapper
public interface OrderMapper {
//查询订单表order的所有数据
public List<Orders> selectOrdersList();
}
MybatisMapperTest
public class MybatisMapperTest {
public SqlSessionFactory sqlSessionFactory;
@Before
public void before() throws IOException{
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
}
//根据用户的名字模糊查询
@Test
public void testMapperQueryVo() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
User user = new User();
user.setUsername("五");
vo.setUser(user);
List<User> userList = mapper.findUserByQueryVo(vo);
for (User user2 : userList) {
System.out.println(user2);
}
}
//查询数据条数
@Test
public void testCountUser(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Integer count = mapper.countUser();
System.out.println(count);
}
//查询订单表order的所有数据
@Test
public void testOrdersList(){
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> list = mapper.selectOrdersList();
for (Orders orders : list) {
System.out.println(orders);
}
}
//根据性别和名字查询用户
@Test
public void testSelectUserBySexAndUsername(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
// user.setSex("1");
user.setUsername("明");
List<User> users = mapper.selectUserBySexAndUsername(user);
for (User user2 : users) {
System.out.println(user2);
}
}
//根据多个id查询用户信息
@Test
public void testSelectUserByIds(){
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
List<Integer> ids = new ArrayList<>();
ids.add(16);
ids.add(24);
ids.add(26);
vo.setIdsList(ids);
List<User> userList = mapper.selectUserByIds(vo);
for (User user : userList) {
System.out.println(user);
}
}
}
