mybatis“集合嵌套查询”和“集合嵌套结果”两种方法实现数据库一对多关系

chopinli 2017-06-16

展开全文

两个实体类分别如下：User用户类和Goods商品类。一个用户对应多个商品（一对多）

[html] view plain copy

package com.leo.entity;
import java.util.List;
public class User {
private Integer id;
private String username;
private Integer age;
private String address;
private List<Goods> goodsList;
public List<Goods> getGoodsList() {
return goodsList;
}
public void setGoodsList(List<Goods> goodsList) {
this.goodsList = goodsList;
}
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 Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", age=" + age
+ ", address=" + address + ", goodsList=" + goodsList + "]";
}
}

Goods商品类

[html] view plain copy

package com.leo.entity;
public class Goods {
private Integer id;
private String goodsName;
private Integer goodsNumber;
private Integer user_id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getGoodsName() {
return goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
public Integer getGoodsNumber() {
return goodsNumber;
}
public void setGoodsNumber(Integer goodsNumber) {
this.goodsNumber = goodsNumber;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
}

User实体类的mapper映射文件:UserDao.xml

[html] view plain copy

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN"
"http:///dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.leo.mapper.UserDao">
<resultMap type="User" id="userMap">
<id column="u_id" property="id" />
<result column="username" property="username" />
<result column="age" property="age" />
<result column="address" property="address" />
<collection property="goodsList" ofType="Goods" column="u_id" select="com.leo.mapper.GoodsDao.selectGoodsForUser" />
</resultMap>
<select id="getUserinfoById" parameterType="int" resultMap="userMap">
select
u.id as u_id,
u.username,
u.age,
u.address
from
user u
where
u.id =${value};
</select>
</mapper>

Goods实体类的mapper映射文件:GoodsDao.xml

[html] view plain copy

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN"
"http:///dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.leo.mapper.GoodsDao">
<select id="selectGoodsForUser" parameterType="int" resultType="Goods">
SELECT id,goodsName,goodsNumber,user_id FROM Goods WHERE user_id = #{value}
</select>
</mapper>

mabatis的环境配置文件mabatis-config.xml

[html] view plain copy

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-////DTD Config 3.0//EN"
"http:///dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="com/leo/resources/db.properties" />
<typeAliases>
<typeAlias alias="User" type="com.leo.entity.User"/>
<typeAlias alias="Goods" type="com.leo.entity.Goods"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driverClass}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/leo/entity/UserDao.xml" />
<mapper resource="com/leo/entity/GoodsDao.xml" />
</mappers>
</configuration>

测试的servlet(也可以用main函数测试)

[html] view plain copy

package com.leo.servlet;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.leo.entity.Goods;
import com.leo.entity.User;
import com.leo.mapper.GoodsDao;
import com.leo.mapper.UserDao;
/**
* Servlet implementation class MybatisServlet
*/
@WebServlet("/MybatisServlet")
public class MybatisServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
InputStream is = Resources.getResourceAsStream("com/leo/resources/mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession();
// UserDao ud = session.getMapper(UserDao.class);
GoodsDao gd = session.getMapper(GoodsDao.class);
List<Goods> goodsList= gd.selectGoodsForUser(1);
// User user = ud.getUserinfoById(1);
// System.out.println(user);
// List<Goods> goodsList = user.getGoodsList();
for (Goods goods : goodsList) {
System.out.println(goods.getId()+" "+ goods.getGoodsName()+" "+goods.getGoodsNumber()+ " "+ goods.getUser_id());
}
session.commit();
session.close();
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}

以上是集合嵌套查询，还有一种方式是集合嵌套结果，这种方式只需要一个实体类文件即可，它是一种级联查询，自动完成的

下面用集合嵌套结果这种方式：

只需要改动UserDao.xml,且只是用这一个映射文件就可以完成

[html] view plain copy

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN"
"http:///dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.leo.mapper.UserDao">
<resultMap type="Goods" id="goodsMap">
<id column="g_id" property="id"/>
<result column="goodsName" property="goodsName"/>
<result column="goodsNumber" property="goodsNumber"/>
<result column="user_id" property="user_id"/>
</resultMap>
<resultMap type="User" id="userMap">
<id column="u_id" property="id" />
<result column="username" property="username" />
<result column="age" property="age" />
<result column="address" property="address" />
<collection property="goodsList" ofType="Goods" resultMap="goodsMap" />
</resultMap>
<select id="getUserinfoById" parameterType="int" resultMap="userMap">
select
u.id as u_id,
u.username,
u.age,
u.address,
g.id as g_id,
g.goodsName,
g.goodsNumber,
g.user_id
from
user u
inner join goods g on u.id = g.user_id
where
u.id =${value};
</select>
</mapper>