本文介绍了一对多及多对多映射关联在MyBatis中的实现方法，包括映射类的设计、XML配置文件的编写以及具体的SQL语句。通过对顾客与订单、用户与商品的关系建模，展示了如何进行关联查询。
一对多映射关联
t_customer表
t_orders表
@Data
public class Customer {
private Integer id;
private String username;
private String jobs;
private String phone;
/**
* 一对多关联订单
*/
private List<Orders>ordersList;
}
@Data
public class Orders {
/**
* 订单id
*/
private Integer id;
/**
* 订单编号
*/
private String number;
/**
* 多对一关联顾客
*/
private Customer customer_order;
}
配置文件
customerMapper.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">
<!-- namespace表示命名空间 -->
<mapper namespace="mybatis.mapper.CustomerMapper">
<!-- 1 .<if>元素使用 根据条件查询 -->
<select id="findCustomerByNameAndJobsWithIf" parameterType="mybatis.pojo.Customer"
resultType="mybatis.pojo.Customer">
select * from t_customer where 1=1
<if test="username !=null and username !=''">
and username like concat('%',#{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
and jobs= #{jobs}
</if>
</select>
<!-- 2.<where>元素使用 根据条件查询 -->
<select id="findCustomerByNameAndJobsWithWhere" parameterType="mybatis.pojo.Customer"
resultType="mybatis.pojo.Customer">
select * from t_customer
<where>
<if test="username !=null and username !=''">
and username like concat('%',#{username},'%')
</if>
<if test="jobs !=null and jobs !=''">
and jobs= #{jobs}
</if>
</where>
</select>
<!-- 3. <trim>元素 -->
<select id="findCustomerByNameAndJobs" parameterType="mybatis.pojo.Customer"
resultType="mybatis.pojo.Customer">
select * from t_customer
<trim prefix="where" prefixOverrides="and" >
<if test="username !=null and username !=''">
and username like concat('%',#{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
and jobs= #{jobs}
</if>
</trim>
</select>
<!--4. <choose>(<when>、<otherwise>)元素使用 -->
<select id="findCustomerByNameOrJobsWithChoose" parameterType="mybatis.pojo.Customer"
resultType="mybatis.pojo.Customer">
select * from t_customer where 1=1
<choose>
<when test="username !=null and username !=''">
and username like concat('%',#{username}, '%')
</when>
<when test="jobs !=null and jobs !=''">
and jobs= #{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>
<!--5.根据客户名模糊查询客户信息列表 常规影像查询 -->
<select id="findCustomerByName" parameterType="String"
resultType="mybatis.pojo.Customer">
select * from t_customer where username like '%${value}%'
</select>
<!-- 对于不支持自动生成主键的数据库，或取消自主增长规则的数据库可以自定义主键生成规则 -->
<insert id="insertCustomer" parameterType="mybatis.pojo.Customer">
<selectKey keyProperty="id" resultType="Integer" order="BEFORE">
select if(max(id) is null, 1, max(id) +1) as newId from t_customer
</selectKey>
insert into t_customer(id,username,jobs,phone)
values(#{id},#{username},#{jobs},#{phone})
</insert>
<!--<foreach>元素使用 -->
<select id="findCustomerByIds" parameterType="List"
resultType="mybatis.pojo.Customer">
select * from t_customer where id in
<foreach item="id" index="index" collection="list" open="("
separator="," close=")">
#{id}
</foreach>
</select>
<!-- 4.更新客户信息 -->
<update id="updateCustomer" parameterType="mybatis.pojo.Customer">
update t_customer
set username=#{username},jobs=#{jobs},phone=#{phone}
where id=#{id}
</update>
<!--update 更新字段 set-->
<!-- <set>元素 -->
<update id="updateCustomerWithSet" parameterType="mybatis.pojo.Customer">
update t_customer
<set>
<if test="username !=null and username !=''">
username=#{username},
</if>
<if test="jobs !=null and jobs !=''">
jobs=#{jobs},
</if>
<if test="phone !=null and phone !=''">
phone=#{phone},
</if>
</set>
where id=#{id}
</update>
<!-- 5.删除客户信息 -->
<delete id="deleteCustomer" parameterType="Integer">
delete from t_customer where id=#{id}
</delete>
<!--定义代码片段 -->
<sql id="customerColumns">id,username,jobs,phone</sql>
<select id="findCustomerById" parameterType="Integer"
resultType="mybatis.pojo.Customer">
select <include refid="customerColumns"/>
from t_customer
where id = #{id}
</select>
<!--一对多映射 嵌套结果-->
<!-- 一对多：查看某一用户及其关联的订单信息
注意：当关联查询出的列名相同，则需要使用别名区分 -->
<select id="findCustomerWithOrders" parameterType="int"
resultMap="findPersonWithOrdersResult">
SELECT t.*,o.id as ordersid,o.number,o.customer_id
from t_customer t,t_orders o
WHERE t.id = o.customer_id
and t.id = #{id}
</select>
<resultMap type="mybatis.pojo.Customer" id="findPersonWithOrdersResult">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="jobs" column="jobs"/>
<result property="phone" column="phone"/>
<!--一对多 查询方式-->
<!-- 一对多关联映射：collection
ofType表示属性集合中元素的类型，List<Orders>属性即Orders类 column 对应的是表的字段名称 -->
<collection property="ordersList" ofType="mybatis.pojo.Orders" javaType="List">
<id property="id" column="ordersid"/>
<result property="number" column="number"/>
</collection>
</resultMap>
<!-- 一对多 嵌套 查询 -->
<select id="findCustomerWithOrders2" parameterType="int"
resultMap="findPersonWithOrdersResult2">
SELECT t.*
from t_customer t
WHERE t.id = #{id}
</select>
<resultMap type="mybatis.pojo.Customer" id="findPersonWithOrdersResult2">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="jobs" column="jobs"/>
<result property="phone" column="phone"/>
<collection property="ordersList" ofType="mybatis.pojo.Orders" column="id"
select="mybatis.mapper.OrdersMapper.findOrdersByCustomerId">
</collection>
</resultMap>
</mapper>
OrderMaper.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="mybatis.mapper.OrdersMapper">
<!-- 查询 对应一对多嵌套查询 -->
<resultMap type="mybatis.pojo.Orders" id="findOrdersByCustomerResult">
<id property="id" column="id"/>
<result property="number" column="number"/>
</resultMap>
<select id="findOrdersByCustomerId" parameterType="int" resultMap="findOrdersByCustomerResult">
select * from t_orders orders where orders.customer_id=#{customer_id}
</select>
</mapper>
测试：
@Test
public void TestfindCustomerWithOrders(){
SqlSession sqlSession = MyBatisUtils.getSession();
CustomerMapper customerMapper = sqlSession.getMapper(CustomerMapper.class);
List<Customer> customerList = customerMapper.findCustomerWithOrders(1);
customerList.forEach(customer -> {
System.out.println(customer);
});
sqlSession.close();
}
查询结果如下：
2021-02-03 09:56:36 [ main:800 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@43d7741f]
2021-02-03 09:56:36 [ main:805 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT t.*,o.id as ordersid,o.number,o.customer_id from t_customer t,t_orders o WHERE t.id = o.customer_id and t.id = ?
2021-02-03 09:56:36 [ main:846 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Integer)
2021-02-03 09:56:36 [ main:876 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 3
Customer(id=1, username=杜甫, jobs=程序员, phone=12121, ordersList=[Orders(id=1, number=11111, customer_order=null), Orders(id=2, number=11112, customer_order=null), Orders(id=3, number=111112, customer_order=null)])
2021-02-03 09:56:36 [ main:942 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.resetAutoCommit(JdbcTransaction.java:123) Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@43d7741f]
2021-02-03 09:56:36 [ main:946 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.close(JdbcTransaction.java:91) Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@43d7741f]
2021-02-03 09:56:36 [ main:947 ] - [ DEBUG ] org.apache.ibatis.datasource.pooled.PooledDataSource.pushConnection(PooledDataSource.java:363) Returned connection 1138193439 to pool.
多对多映射关联
t_user表
t_product表
user_product表
@Data
public class User {
/**
* ID 用户id
*/
private Integer id;
/**
* username 用户名
*/
private String username;
/**
* password 用户密码
*/
private String password;
/**
* 多对多订单
*/
private List<Product>productList;
}
@Data
public class Product {
/**
* 商品id
*/
private Integer id;
/**
* 商品名称
*/
private String name;
/**
* 商品单价
*/
private Double price;
/**
*与用户多对多 一个订单可以被多个人购买 一个人可以买多个订单
*/
private List<User> userList;
}
productMapper
<?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="mybatis.mapper.ProductMapper">
<select id="findProductById" parameterType="Integer"
resultType="mybatis.pojo.Product">
SELECT * from t_product where id IN(
SELECT product_id FROM user_product WHERE user_id = #{id}
)
</select>
</mapper>
UserMapper
<?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="mybatis.mapper.UserMapper">
<resultMap id="resultMap" type="mybatis.pojo.User" >
<id property="id" column="t_id"/>
<result property="username" column="t_username"/>
<result property="password" column="t_password"/>
</resultMap>
<select id="findAllUser" resultMap="resultMap">
select * from t_user
</select>
<!-- 多对多嵌套查询：通过执行另外一条SQL映射语句来返回预期的特殊类型 -->
<select id="findUserWithPorduct" parameterType="Integer"
resultMap="UserWithProductResult">
select * from t_user WHERE t_id=#{id}
</select>
<resultMap type="mybatis.pojo.User" id="UserWithProductResult">
<id property="id" column="t_id" />
<result property="username" column="t_username" />
<result property="password" column="t_password" />
<collection property="productList" column="t_id" ofType="mybatis.pojo.Product"
select="mybatis.mapper.ProductMapper.findProductById">
</collection>
</resultMap>
<!-- 多对多嵌套结果查询：查询某订单及其关联的商品详情 -->
<select id="findUserWithPorduct2" parameterType="Integer"
resultMap="UserWithProductResult2">
select u.* ,p.id as productId ,p.name,p.price
from t_user u,t_product p,user_product up
WHERE u.t_id = up.user_id
and p.id = up.product_id
and u.t_id = #{id}
</select>
<!-- 嵌套结果查询 属性 -->
<resultMap type="mybatis.pojo.User" id="UserWithProductResult2">
<id property="id" column="t_id" />
<result property="username" column="t_username" />
<result property="password" column="t_password" />
<!-- 多对多关联映射：collection -->
<collection property="productList" ofType="mybatis.pojo.Product" javaType="List">
<id property="id" column="productId" />
<result property="name" column="name" />
<result property="price" column="price" />
</collection>
</resultMap>
</mapper>
测试：
@Test
public void TestfindUserWithPorduct2() {
SqlSession sqlSession = MyBatisUtils.getSession();
System.out.println(sqlSession);
List<User>userList = sqlSession.selectList("mybatis.mapper.UserMapper.findUserWithPorduct2", 1);
userList.forEach(user -> {
System.out.println(user);
});
sqlSession.close();
}
测试结果：
2021-02-03 10:11:33 [ main:1044 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1cab0bfb]
2021-02-03 10:11:33 [ main:1047 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: select u.* ,p.id as productId ,p.name,p.price from t_user u,t_product p,user_product up WHERE u.t_id = up.user_id and p.id = up.product_id and u.t_id = ?
2021-02-03 10:11:33 [ main:1083 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Integer)
2021-02-03 10:11:33 [ main:1111 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 2
User(id=1, username=admin, password=admin, productList=[Product(id=1, name=手机, price=10000.0, userList=null), Product(id=2, name=电脑, price=80000.0, userList=null)])
2021-02-03 10:11:33 [ main:1173 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.resetAutoCommit(JdbcTransaction.java:123) Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1cab0bfb]
2021-02-03 10:17:00 [ main:845 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@43d7741f]
2021-02-03 10:17:00 [ main:848 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: select * from t_user WHERE t_id=?
2021-02-03 10:17:00 [ main:885 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Integer)
2021-02-03 10:17:00 [ main:946 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 1
2021-02-03 10:17:00 [ main:995 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT * from t_product where id IN( SELECT product_id FROM user_product WHERE user_id = ? )
2021-02-03 10:17:00 [ main:998 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Integer)
2021-02-03 10:17:00 [ main:1002 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 2
User(id=1, username=admin, password=admin, productList=[Product(id=1, name=手机, price=10000.0, userList=null), Product(id=2, name=电脑, price=80000.0, userList=null)])
2021-02-03 10:17:00 [ main:1003 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.resetAutoCommit(JdbcTransaction.java:123) Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@43d7741f]
2021-02-03 10:17:00 [ main:1004 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.close(JdbcTransaction.java:91) Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@43d7741f]
2021-02-03 10:17:00 [ main:1004 ] - [ DEBUG ] org.apache.ibatis.datasource.pooled.PooledDataSource.pushConnection(PooledDataSource.java:363) Returned connection 1138193439 to pool.
扫一扫
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
