一对多查询模型
用户和订单表的关系为，一个用户有多个订单，一个订单只能属于一个用户。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
一对多查询需求：查询多有用户，与此同时查询用户具有的订单信息。文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
一对多查询语句
对应的sql语句文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
select u.*,o.ordertime,o.total,o.uid from user u left join orders o on u.id = o.uid;
查询结果如下：文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
| id | username | password | birthday | ordertime | total | uid |
|---|---|---|---|---|---|---|
| 1 | lucy | 123 | 2022-03-17 17:15:56 | 2022-03-17 17:15:33 | 3000 | 1 |
| 1 | lucy | 123 | 2022-03-17 17:15:56 | 2022-03-17 17:15:33 | 4000 | 1 |
| 2 | tom | 123 | 2022-03-17 17:15:56 | 2022-03-17 17:15:33 | 5000 | 2 |
代码实现
修改User实体
/**
* 用户信息
*
* @name: User
* @author: terwer
* @date: 2022-05-08 17:41
*/
class User {
var id: Int? = null
var username: String? = null
// 代表当前用户具备那些订单
var orderList: List<Order>? = null
override fun toString(): String {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", orderList=" + orderList +
'}'
}
}
/**
* 用户信息
*
* @name: User
* @author: terwer
* @date: 2022-03-17 17:41
**/
public class User {
private Integer id;
private String username;
// 代表当前用户具备那些订单
private List<Order> orderList;
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 List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", orderList=" + orderList +
'}';
}
}
创建UserMapper
/**
* 用户映射
*
* @name: UserMapper
* @author: terwer
* @date: 2022-05-08 00:03
*/
interface UserMapper {
// 查询所有用户信息以及用户关联的订单信息
fun findAll(): List<User?>?
}
/**
* 用户映射
*
* @name: UserMapper
* @author: terwer
* @date: 2022-03-28 00:03
**/
public interface UserMapper {
// 查询所有用户信息以及用户关联的订单信息
public List<User> findAll();
}
配置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.terwergreen.mapper.UserMapper">
<resultMap id="userMap" type="com.terwergreen.pojo.User">
<result property="id" column="id"></result>
<result property="username" column="username"></result>
<collection property="orderList" ofType="com.terwergreen.pojo.Order">
<result property="id" column="uid"></result>
<result property="orderTime" column="ordertime"></result>
<result property="total" column="total"></result>
</collection>
</resultMap>
<!-- resultMap：手动配置实体属性与表字段的映射关系 -->
<select id="findAll" resultMap="userMap">
select u.*,o.ordertime,o.total,o.uid from user u left join orders o on u.id = o.uid
</select>
</mapper>
测试
@Test
@Throws(IOException::class)
fun test2() {
val resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml")
val sqlSessionFactory = SqlSessionFactoryBuilder().build(resourceAsStream)
val sqlSession = sqlSessionFactory.openSession()
val userMapper = sqlSession.getMapper(UserMapper::class.java)
val userList = userMapper.findAll()
for (user in userList) {
println(user)
}
}
@Test
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.findAll();
for (User user : userList) {
System.out.println(user);
}
}
执行结果文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html文章更新历史
2022/05/08 feat:新增Kotlin支持 文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-many-query.html
- 扫码加我微信
- 验证消息请输入：来自你的博客
-
- 我的微信公众号
- 微信扫一扫与我交流吧
-
评论