一般用list< map>来实现多表关系以及连查是最方便的，但是个别公司是要求用Vo实体方式。
hibernate和mybatis的关联关系个人比较喜欢用mybatis，虽然说要自己写sql,如果表关系比较多的话可以用一条sql来实现，hibernate中则容易把自己搞晕，mybatis是人为控制的不是自动生成的，hibernate是不可控的。
一对多：一个订单对应对个订单项
多对多：一个人对多个项目，一个项目对应对个人
1、用逆向生成工具生成订单表和订单项两张表的mapper和model
OrderVo
package com.liyingdong.model.vo; import com.liyingdong.model.Order; import com.liyingdong.model.OrderItem; import java.util.ArrayList; import java.util.List; ; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 18:14 */ public class OrderVo extends Order { private List<OrderItem> orderItemList=new ArrayList<OrderItem>(); public List<OrderItem> getOrderItemList() { return orderItemList; } public void setOrderItemList(List<OrderItem> orderItemList) { this.orderItemList = orderItemList; } }OrderItemVo
package com.liyingdong.model.vo; import com.liyingdong.model.Order; import com.liyingdong.model.OrderItem; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 18:17 */ public class OrderItemVo extends OrderItem { private Order order; public Order getOrder() { return order; } public void setOrder(Order order) { this.order = order; } }OrderMapper
增加查询订单的方法
package com.liyingdong.mapper; import com.liyingdong.model.Order; import com.liyingdong.model.vo.OrderVo; import org.apache.ibatis.annotations.Param; import org.springframework.stereotype.Repository; import java.util.List; @Repository public interface OrderMapper { int deleteByPrimaryKey(Integer orderId); int insert(Order record); int insertSelective(Order record); Order selectByPrimaryKey(Integer orderId); int updateByPrimaryKeySelective(Order record); int updateByPrimaryKey(Order record); List<OrderVo>selectByOrderId(@Param("orderId") Integer orderId); }OrderMapper.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.liyingdong.mapper.OrderMapper" > <resultMap id="BaseResultMap" type="com.liyingdong.model.Order" > <constructor > <idArg column="order_id" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="order_no" jdbcType="VARCHAR" javaType="java.lang.String" /> </constructor> </resultMap> <!-- 描述一对多 --> <resultMap id="OrderVoMap" type="com.liyingdong.model.vo.OrderVo"> <result property="orderId" column="order_id"></result> <result property="orderNo" column="order_no"></result> <collection property="orderItemList" ofType="com.liyingdong.model.OrderItem"> <result property="orderItemId" column="order_item_id"></result> <result property="productId" column="product_id"></result> <result property="quantity" column="quantity"></result> <result property="oid" column="oid"></result> </collection> </resultMap> <sql id="Base_Column_List" > order_id, order_no </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from t_hibernate_order where order_id = #{orderId,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from t_hibernate_order where order_id = #{orderId,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.liyingdong.model.Order" > insert into t_hibernate_order (order_id, order_no) values (#{orderId,jdbcType=INTEGER}, #{orderNo,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.liyingdong.model.Order" > insert into t_hibernate_order <trim prefix="(" suffix=")" suffixOverrides="," > <if test="orderId != null" > order_id, </if> <if test="orderNo != null" > order_no, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="orderId != null" > #{orderId,jdbcType=INTEGER}, </if> <if test="orderNo != null" > #{orderNo,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.liyingdong.model.Order" > update t_hibernate_order <set > <if test="orderNo != null" > order_no = #{orderNo,jdbcType=VARCHAR}, </if> </set> where order_id = #{orderId,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.liyingdong.model.Order" > update t_hibernate_order set order_no = #{orderNo,jdbcType=VARCHAR} where order_id = #{orderId,jdbcType=INTEGER} </update> <select id="selectByOrderId" resultMap="OrderVoMap" parameterType="java.lang.Integer" > select * from t_hibernate_order o,t_hibernate_order_item oi where o.order_id =oi.oid and oi.oid= #{orderId} </select> </mapper>OrderItemMapper.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.liyingdong.mapper.OrderItemMapper" > <resultMap id="BaseResultMap" type="com.liyingdong.model.OrderItem" > <constructor > <idArg column="order_item_id" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="product_id" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="quantity" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="oid" jdbcType="INTEGER" javaType="java.lang.Integer" /> </constructor> </resultMap> <resultMap id="OrderItemVoMap" type="com.liyingdong.model.vo.OrderItemVo"> <result property="orderItemId" column="order_item_id"></result> <result property="productId" column="product_id"></result> <result property="quantity" column="quantity"></result> <result property="oid" column="oid"></result> <association property="order" javaType="com.liyingdong.model.Order"> <result property="orderId" column="order_id"></result> <result property="orderNo" column="order_no"></result> </association> </resultMap> <sql id="Base_Column_List" > order_item_id, product_id, quantity, oid </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from t_hibernate_order_item where order_item_id = #{orderItemId,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from t_hibernate_order_item where order_item_id = #{orderItemId,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.liyingdong.model.OrderItem" > insert into t_hibernate_order_item (order_item_id, product_id, quantity, oid) values (#{orderItemId,jdbcType=INTEGER}, #{productId,jdbcType=INTEGER}, #{quantity,jdbcType=INTEGER}, #{oid,jdbcType=INTEGER}) </insert> <insert id="insertSelective" parameterType="com.liyingdong.model.OrderItem" > insert into t_hibernate_order_item <trim prefix="(" suffix=")" suffixOverrides="," > <if test="orderItemId != null" > order_item_id, </if> <if test="productId != null" > product_id, </if> <if test="quantity != null" > quantity, </if> <if test="oid != null" > oid, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="orderItemId != null" > #{orderItemId,jdbcType=INTEGER}, </if> <if test="productId != null" > #{productId,jdbcType=INTEGER}, </if> <if test="quantity != null" > #{quantity,jdbcType=INTEGER}, </if> <if test="oid != null" > #{oid,jdbcType=INTEGER}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.liyingdong.model.OrderItem" > update t_hibernate_order_item <set > <if test="productId != null" > product_id = #{productId,jdbcType=INTEGER}, </if> <if test="quantity != null" > quantity = #{quantity,jdbcType=INTEGER}, </if> <if test="oid != null" > oid = #{oid,jdbcType=INTEGER}, </if> </set> where order_item_id = #{orderItemId,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.liyingdong.model.OrderItem" > update t_hibernate_order_item set product_id = #{productId,jdbcType=INTEGER}, quantity = #{quantity,jdbcType=INTEGER}, oid = #{oid,jdbcType=INTEGER} where order_item_id = #{orderItemId,jdbcType=INTEGER} </update> <select id="selectByOrderItemId" resultMap="OrderItemVoMap" parameterType="java.lang.Integer" > select * from t_hibernate_order o,t_hibernate_order_item oi where o.order_id =oi.oid and oi.order_item_id= #{orderItemId} </select> </mapper>OneToManyService
package com.liyingdong.serivce; import com.liyingdong.model.vo.OrderItemVo; import com.liyingdong.model.vo.OrderVo; import java.util.List; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 18:42 */ public interface OneToManyService { List<OrderItemVo> selectByOrderItemId( Integer orderItemId ); List<OrderVo>selectByOrderId( Integer orderId); }OneToManyServiceImpl
package com.liyingdong.serivceimpl; import com.liyingdong.mapper.OrderItemMapper; import com.liyingdong.mapper.OrderMapper; import com.liyingdong.model.vo.OrderItemVo; import com.liyingdong.model.vo.OrderVo; import com.liyingdong.serivce.OneToManyService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 19:40 */ @Service public class OneToManyServiceImpl implements OneToManyService { @Autowired private OrderMapper orderMapper; @Autowired private OrderItemMapper orderItemMapper; @Override public List<OrderItemVo> selectByOrderItemId(Integer orderItemId) { return orderItemMapper.selectByOrderItemId(orderItemId); } @Override public List<OrderVo> selectByOrderId(Integer orderId) { return orderMapper.selectByOrderId(orderId); } }JUnit测试
package com.liyingdong.serivceimpl; import com.liyingdong.SpringBaseTest; import com.liyingdong.model.OrderItem; import com.liyingdong.model.vo.OrderItemVo; import com.liyingdong.model.vo.OrderVo; import com.liyingdong.serivce.OneToManyService; import org.junit.Test; import org.springframework.beans.factory.annotation.Autowired; import java.util.List; import static org.junit.Assert.*; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 19:43 */ public class OneToManyServiceImplTest extends SpringBaseTest { @Autowired private OneToManyService oneToManyService; @Test public void selectByOrderItemId() { List<OrderItemVo> orderItemVos = oneToManyService.selectByOrderItemId(36); OrderItemVo orderItemVo = orderItemVos.get(0); System.out.println(orderItemVo); System.out.println(orderItemVo.getOrder()); } @Test public void selectByOrderId() { List<OrderVo> orderVos = oneToManyService.selectByOrderId(8); OrderVo orderVo = orderVos.get(0); System.out.println(orderVo); for (OrderItem o : orderVo.getOrderItemList()) { System.out.println(o); } } }selectByOrderItemId
selectByOrderId
通过一个中间表来实现多对多
涉及表：t_hibernate_book、t_hibernate_book_category、t_hibernate_category
HbookVo
package com.liyingdong.model.vo; import com.liyingdong.model.Category; import com.liyingdong.model.Hbook; import java.util.ArrayList; import java.util.List; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 20:02 */ public class HbookVo extends Hbook { private List<Category> categories=new ArrayList<>(); public List<Category> getCategories() { return categories; } public void setCategories(List<Category> categories) { this.categories = categories; } }CategoryVo
package com.liyingdong.model.vo; import com.liyingdong.model.Category; import com.liyingdong.model.Hbook; import java.util.ArrayList; import java.util.List; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 20:04 */ public class CategoryVo extends Category { private List<Hbook> hbooks=new ArrayList<>(); public List<Hbook> getHbooks() { return hbooks; } public void setHbooks(List<Hbook> hbooks) { this.hbooks = hbooks; } }HbookCategoryMapper.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.liyingdong.mapper.HbookMapper" > <resultMap id="BaseResultMap" type="com.liyingdong.model.Hbook" > <constructor > <idArg column="book_id" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="book_name" jdbcType="VARCHAR" javaType="java.lang.String" /> <arg column="price" jdbcType="REAL" javaType="java.lang.Float" /> </constructor> </resultMap> <resultMap id="HbookVoMap" type="com.liyingdong.model.vo.HbookVo"> <result property="bookId" column="book_id"></result> <result property="bookName" column="book_name"></result> <result property="price" column="price"></result> <collection property="categories" ofType="com.liyingdong.model.Category"> <result property="categoryId" column="category_id"></result> <result property="categoryName" column="category_name"></result> </collection> </resultMap> <sql id="Base_Column_List" > book_id, book_name, price </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from t_hibernate_book where book_id = #{bookId,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from t_hibernate_book where book_id = #{bookId,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.liyingdong.model.Hbook" > insert into t_hibernate_book (book_id, book_name, price ) values (#{bookId,jdbcType=INTEGER}, #{bookName,jdbcType=VARCHAR}, #{price,jdbcType=REAL} ) </insert> <insert id="insertSelective" parameterType="com.liyingdong.model.Hbook" > insert into t_hibernate_book <trim prefix="(" suffix=")" suffixOverrides="," > <if test="bookId != null" > book_id, </if> <if test="bookName != null" > book_name, </if> <if test="price != null" > price, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="bookId != null" > #{bookId,jdbcType=INTEGER}, </if> <if test="bookName != null" > #{bookName,jdbcType=VARCHAR}, </if> <if test="price != null" > #{price,jdbcType=REAL}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.liyingdong.model.Hbook" > update t_hibernate_book <set > <if test="bookName != null" > book_name = #{bookName,jdbcType=VARCHAR}, </if> <if test="price != null" > price = #{price,jdbcType=REAL}, </if> </set> where book_id = #{bookId,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.liyingdong.model.Hbook" > update t_hibernate_book set book_name = #{bookName,jdbcType=VARCHAR}, price = #{price,jdbcType=REAL} where book_id = #{bookId,jdbcType=INTEGER} </update> <select id="selectByBid" resultMap="HbookVoMap" parameterType="java.lang.Integer" > select*from t_hibernate_book b,t_hibernate_book_category bc ,t_hibernate_category c where b.book_id=bc.bid and bc.cid=c.category_id and b.book_id= #{bid} </select> </mapper>CategoryMapper.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.liyingdong.mapper.CategoryMapper" > <resultMap id="BaseResultMap" type="com.liyingdong.model.Category" > <constructor > <idArg column="category_id" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="category_name" jdbcType="VARCHAR" javaType="java.lang.String" /> </constructor> </resultMap> <resultMap id="CategoryVoMap" type="com.liyingdong.model.vo.CategoryVo"> <result property="categoryId" column="category_id"></result> <result property="categoryName" column="category_name"></result> <collection property="hbooks" ofType="com.liyingdong.model.Hbook"> <result property="bookId" column="book_id"></result> <result property="bookName" column="book_name"></result> <result property="price" column="price"></result> </collection> </resultMap> <sql id="Base_Column_List" > category_id, category_name </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from t_hibernate_category where category_id = #{categoryId,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from t_hibernate_category where category_id = #{categoryId,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.liyingdong.model.Category" > insert into t_hibernate_category (category_id, category_name) values (#{categoryId,jdbcType=INTEGER}, #{categoryName,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.liyingdong.model.Category" > insert into t_hibernate_category <trim prefix="(" suffix=")" suffixOverrides="," > <if test="categoryId != null" > category_id, </if> <if test="categoryName != null" > category_name, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="categoryId != null" > #{categoryId,jdbcType=INTEGER}, </if> <if test="categoryName != null" > #{categoryName,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.liyingdong.model.Category" > update t_hibernate_category <set > <if test="categoryName != null" > category_name = #{categoryName,jdbcType=VARCHAR}, </if> </set> where category_id = #{categoryId,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.liyingdong.model.Category" > update t_hibernate_category set category_name = #{categoryName,jdbcType=VARCHAR} where category_id = #{categoryId,jdbcType=INTEGER} </update> <select id="selectByCid" resultMap="CategoryVoMap" parameterType="java.lang.Integer" > select*from t_hibernate_book b,t_hibernate_book_category bc ,t_hibernate_category c where b.book_id=bc.bid and bc.cid=c.category_id and c.category_id= #{cid} </select> </mapper>ManyToManyService
package com.liyingdong.serivce; import com.liyingdong.model.vo.CategoryVo; import com.liyingdong.model.vo.HbookVo; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 22:04 */ public interface ManyToManyService { HbookVo selectByBid(Integer bid); CategoryVo selectByCid(Integer cid); }ManyToManyServiceImpl
package com.liyingdong.serivceimpl; import com.liyingdong.mapper.CategoryMapper; import com.liyingdong.mapper.HbookMapper; import com.liyingdong.model.vo.CategoryVo; import com.liyingdong.model.vo.HbookVo; import com.liyingdong.serivce.ManyToManyService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 22:05 */ @Service public class ManyToManyServiceImpl implements ManyToManyService { @Autowired private HbookMapper hbookMapper; @Autowired private CategoryMapper categoryMapper; @Override public HbookVo selectByBid(Integer bid) { return hbookMapper.selectByBid(bid); } @Override public CategoryVo selectByCid(Integer cid) { return categoryMapper.selectByCid(cid); } }测试
package com.liyingdong.serivceimpl; import com.liyingdong.SpringBaseTest; import com.liyingdong.model.Category; import com.liyingdong.model.Hbook; import com.liyingdong.model.vo.CategoryVo; import com.liyingdong.model.vo.HbookVo; import com.liyingdong.serivce.ManyToManyService; import org.junit.Test; import org.springframework.beans.factory.annotation.Autowired; /** * @author 李瀛东 * @site www.xiaomage.com * @company xxx公司 * @create 2020-10-20 22:07 */ public class ManyToManyServiceImplTest extends SpringBaseTest { @Autowired private ManyToManyService manyToManyService; @Test public void selectByBid() { HbookVo hbookVo = manyToManyService.selectByBid(8); System.out.println(hbookVo); for (Category category : hbookVo.getCategories()) { System.out.println(category); } } @Test public void selectByCid() { CategoryVo categoryVo = manyToManyService.selectByCid(8); System.out.println(categoryVo); for (Hbook hbook : categoryVo.getHbooks()) { System.out.println(hbook); } } }selectByBid
selectByCid
