一、在Orders类中映射实体类(一对一映射)
1.pojo类下的Orders类中添加一个user对象最为该Orders的一个属性
package com.pp.pojo;
import java.util.Date;
public class Orders {
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;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", userid=" + userid +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
", user=" + user +
'}';
}
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;
}
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;
}
}
如上面所示,添加了private User user,该句是一个user对象。由于添加了一个属性,所以要为其添加set()和get()方法,以及以及重新写toString()方法。让返回值为Orders实体类,并且Orders里面有含有User实体类。
2.User类的代码如下:
package com.pp.pojo;
import java.util.Date;
import java.util.List;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Orders> ;
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", ordersList=" + ordersList +
'}';
}
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 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;
}
}
这里的代码,跟前面的内容一样,没有改变,注意的是这里也是多了一个列表为ordersList的orders属性,它指向的也是以一个orders对象。并且还是一个list数组,代表的是多个orders对象。
3. 在pojo包下的QueryVo 类中添加一个数据类型为Integer的数组
这个类的内容在下面的第二个大标题中会使用到,即# 二、根据多个userid查多个user(一对多映射)
package com.pp.pojo;
import java.util.List;
public class QueryVo {
private User usser;
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
public User getUsser() {
return usser;
}
public void setUser(User user) {
this.usser = user;
}
}
这里再原来的基础上添加了属性名为ids,数据类型为Integer的数组。然后为该属性写了set()和get()构造方法。
4.
<?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.pp.mapper.UserMapper">
<!--一一对应-->
<resultMap id="orderAndUserResultMap" type="com.pp.pojo.Orders">
<id column="id" property="id"/>
<result column="user_id" property="userid"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<association property="user" javaType="com.pp.pojo.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findOrderAndUser" resultMap="orderAndUserResultMap">
select a.*,b.* from user a, orders b where a.id=b.user_id;
</select>
</mapper>
- 首先因为返回的是orders实体类,所以这里的
<resultMap>
标签的id换成orderAndUserResultMap - 然后type返回类型该为com.pp.pojo.Orders.里面的属性要先写Orders类的属性和实体类对应。而里面包含的User实体类属性,不是集合就需要使用
<association>
标签,然后该标签里面再写入user实体类和数据库一一对应的参数。 - 最后,再
<select>
标签中,id使用UserMapper抽象类中定义的方法名,resultMap写上面resultMap的id即可。
4. 在UserMapper中定义findOrderAndUser()方法
package com.pp.mapper;
import com.pp.pojo.Orders;
import com.pp.pojo.QueryVo;
import com.pp.pojo.User;
import java.util.List;
public interface UserMapper {
List<Orders> findOrderAndUser();
}
5.在UserTest测试类总写fun10()测试方法
@Test
public void fun10(){
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Orders> list = mapper.findOrderAndUser();
System.out.println(list);
}
二、根据多个userid查多个user(一对多映射)
原来的入口参数要么是实体类,要么是Java类型(Integer、String等),下面写的入口参数是List<Integer>
这种入口参数是集合。
1.在userMapper接口类中写入findUserByIds()方法
import com.pp.pojo.QueryVo;
import com.pp.pojo.User;
import java.util.List;
public interface UserMapper {
List<User> findUserByIds(QueryVo queryVo);
}
这里的定义的是一个User数组,入口参数类型是QueryVo 。
2.在UserMapper.xml配置文件中写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="com.pp.mapper.UserMapper">
<select id="findUserByIds" parameterType="com.pp.pojo.QueryVo" resultType="com.pp.pojo.User">
select * from user
<where>
<if test="ids !=null">
<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
</mapper>
标签中id填写UserMapper类的方法名;入口参数parameterType根据UserMapper中对应的方法中填写QueryVo的类具体路径,此处填写com.pp.pojo.QueryVo;返回值参数resultType填写UserMapper接口方法定义的User类路径,这里填写com.pp.pojo.User;然后标签里面包含的是sql语句,由于这里传进来的是含有多个id的参数,所以在这里需要循环遍历每个id,然后才能返回每个id对应的数据。
<if test="ids !=null">
<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
#{id}
</foreach>
</if>
这里先利用if语句判断ids列表是否为空,如果不为空,就使用foreach语句遍历collection为ids的数组。
3.在UserTest测试类编写fun11()测试方法
import com.pp.dao.UserDaoImpl;
import com.pp.mapper.UserMapper;
import com.pp.pojo.Orders;
import com.pp.pojo.QueryVo;
import com.pp.pojo.User;
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.ArrayList;
import java.util.Date;
import java.util.List;
public class UserTest {
private SqlSessionFactory factory;
@Test
@Before
public void Setup() throws IOException {
String res="SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(res);
factory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void fun11(){
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo queryVo = new QueryVo();
List<Integer> list = new ArrayList<Integer>();
list.add(24);
list.add(25);
list.add(26);
queryVo.setIds(list);
List<User> userList = mapper.findUserByIds(queryVo);
System.out.println(userList);
}
}
测试方法fun11()中,创建了queryVo,然后创建了一个数组,分别添加了24,25,26.然后将数组作为参数存到了queryVo创建的对象的 setIds()方法内。最后利用工厂调用mapper类的方法findUserByIds()。将queryVo存储到里面。最后通过传递的ids数组,返回多个user对象。
三、根据多个user查询用户信息(多对多查询)
1.在userMapper接口类中写入findUserByList()方法
import com.pp.pojo.QueryVo;
import com.pp.pojo.User;
import java.util.List;
public interface UserMapper {
List<User> findUserByList(List<User> list);
}
2.在UserMapper.xml配置文件中写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="com.pp.mapper.UserMapper">
<select id="findUserByList" parameterType="com.pp.pojo.QueryVo" resultType="com.pp.pojo.User">
select * from user where sex in
<foreach collection="list" item="user" open="(" close=")" separator=",">
#{user.sex}
</foreach>
</select>
</mapper>
<select>
中的id对应的是UserMapper抽象类的方法,入口参数parameterType是com.pp.pojo.QueryVo,resultType为com.pp.pojo.User。
3.在UserTest测试类编写fun12()测试方法
import com.pp.dao.UserDaoImpl;
import com.pp.mapper.UserMapper;
import com.pp.pojo.Orders;
import com.pp.pojo.QueryVo;
import com.pp.pojo.User;
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.ArrayList;
import java.util.Date;
import java.util.List;
public class UserTest {
private SqlSessionFactory factory;
@Test
@Before
public void Setup() throws IOException {
String res="SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(res);
factory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void fun12(){
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> list = new ArrayList<User>();
User user = new User();
user.setSex("女");
User user1 = new User();
user1.setSex("男");
list.add(user);
list.add(user1);
List<User> userByList = mapper.findUserByList(list);
System.out.println(userByList);
}
}
源码地址:https://gitee.com/yangforever/project-learning/tree/master/demo/Mybatis/mybatisday3/mybatis_day1