Mybatis collection

demand

Example Query information about all users and orders associated with users. Primary information: User information Secondary information: order informationCopy the code

Analysis of the

In one-to-many associated query, only resultMap can be used to map results. 1. In one-to-many associated query, there are multiple SQL query results but only one mapping object. ResultType A record of the way the result mapping is done maps an object. 3. ResultMap completes the result mapping by mapping [master information] to a master object, [slave information] to a collection or object, and then encapsulating it into the master object.

Po class

PO User order list

@data public class UserOrderList {/** * private int ID; /** * username */ private String username; /** ** private Date birthday; /** ** private String sex; /** * private String address; /** * createTime */ private Long createTime; /** * private List<Order> orders; }Copy the code

Note: PO classes cannot be excluded using extends

UserMapper

Public interface UserMapper {/** * getUserOrderList * @param userId string userId * @return */ UserOrderList getUserOrderList(int  userId); @param userId string userId * @return */ UserOrderList getOrderListByUserId(int userId); }Copy the code

UserMapper.xml

Distributed query

resultMap

<! - more than a pair of distributed query - > < resultMap id = "selectOrderByUser" type = "com. XXX. www.mybatis.phase04.po.UserOrderList" > <! -- User information mapping --> <! - the column database field | property mapping Po field name of a class -- > < 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"/> <result column="create_time" property="createTime"/> <! -- One-to-many order association attribute mapping: use select reference mode --> <! Collection defines the encapsulation rules for attributes associated with collection types --> <! -- Property: orders --> <! -- column: column corresponding to SQL query result --> <! OfType: specifies the POJO type mapped to the list collection property --> <! -- fetchType: load type: lazy If used, this will replace the global configuration parameter lazyLoadingEnable --> <! - the select method of distributed query quotation - > < collection property = "orders" column = "id" ofType = "www.mybatis.phase04.po.Order com. XXX." select="com.xxx.www.mybatis.phase04.mapper.OrderMapper.selectOderByUserId" fetchType="lazy"> </collection> </resultMap>Copy the code

The query SQL

<! ParameterType ="int"> select id, resultMap="selectOrderByUser" username, birthday, sex, address, create_time FROM `user` u <if test="userId ! = null "> where u.id = #{userId} </if> </select>Copy the code

Even form word query

resultMap

<! - a one-to-many list query - > < resultMap id = "selectOrderListByUserId" type = "com. XXX. www.mybatis.phase04.po.UserOrderList" > <! -- user information mapping --> <id column="user_id" property=" ID "/> <result column="username" property="username"/> <result column="user_id" property="username"/> <result column="birthday" property="birthday"/> <result column="sex" property="sex"/> <result column="address" property="address"/> <! - a one-to-many order information mapping - > < collection property = "orders" ofType = "com. XXX. HTTP: / / www.mybatis.phase04.po.Order" > < id column = "order_id" property="orderId"/> <result column="order_number" property="orderNumber"/> <result column="user_id" property="userId"/>  <result column="prepayment_amount" property="prepaymentAmount"/> <result column="actually_paid_amount" property="actuallyPaidAmount"/> <result column="create_time" property="createTime"/> <result column="goods_kind" property="goodsKind"/> <result column="goods_quantity" property="goodsQuantity"/> <result column="payment_method" property="paymentMethod"/> <result column="trade_on" property="tradeOn"/> <result column="price_after_discount" property="priceAfterDiscount"/> </collection> </resultMap>Copy the code

The query SQL

<! ResultMap ="selectOrderListByUserId"> select u.id AS user_id, u.username, u.birthday, u.sex, u.address, o.id AS order_id, o.user_id, u.create_time, o.order_number, o.create_time, o.prepayment_amount, o.actually_paid_amount, o.goods_kind, o.goods_quantity, o.payment_method, o.trade_on, o.price_after_discount FROM `user` AS u JOIN `order` AS o where u.id = o.user_id and u.id = #{userId} </select>Copy the code

Note: If the primary key of both the primary table and the detail table is ID, only the first one can be queried. Id = user_id; oid = order_id

test

Public class Phase04Test {/** * mybatis */ private SqlSessionFactory SqlSessionFactory; private SqlSession sqlSession; @before public void init() throws Exception {// Load the global configuration file (at the same time load the mapping file) String resource = "phase04/ sqlmapconfig.xml "; InputStream inputStream = Resources.getResourceAsStream(resource); SqlsessionFactory = new after sqlsessionFactory = new after sqlsessionFactory = new after sqlsessionFactory = new SqlSessionFactoryBuilder().build(inputStream); / / create UserMapper object sqlSession = sqlSessionFactory openSession (); } @test public void testFindOrderById() {OrderMapper mapper = sqlsession.getmapper (OrderMapper.class); List<OrderExt> orderExtList = mapper.selectOrderAndUserInfo(); System.out.println(orderExtList); } @test public void getOrderByUserId() {// Obtain the proxy object UserMapper mapper = sqlsession.getmapper (usermapper.class); UserOrderList orderList = mapper.getUserOrderList(1); System.out.println(orderList); for (Order order: orderList.getOrders()){ System.out.println(order.toString()); }} @test public void getOrderListByUserId() {UserMapper mapper = sqlsession.getMapper (usermapper.class); UserOrderList orderList = mapper.getOrderListByUserId(1); System.out.println(orderList); for (Order order: orderList.getOrders()){ System.out.println(order.toString()); }} @test public void insertOrder() {OrderMapper mapper = sqlsession.getmapper (ordermapper.class); Order order = insertOrderInfo(); int rows = mapper.insertOrder(order); sqlSession.commit(); System.out.println(rows); System.out.println(order.getOrderNumber()); } /** * @return User */ private Order insertOrderInfo() {Order Order = new Order(); / / order number String orderNumber = OrderNumberFactory. GetOrderCode (1 l); order.setOrderNumber(orderNumber); // User ID order.setuserId (1); / / prepaid amount order. SetPrepaymentAmount (10 l); / / order. The actually paid amount setActuallyPaidAmount (10 l); Order.setcreatetime (system.currentTimemillis ()); // Order. SetGoodsKind (1); Order. SetGoodsQuantity (1); // Order. SetPaymentMethod (" wechat "); // Order. SetTradeOn ("12311"); / / the preferential price after the order. SetPriceAfterDiscount (10 l); return order; } @After public void closeSession(){ if (sqlSession ! = null){ sqlSession.close(); }}}Copy the code