[mybatis]多表关系查询及CRUD操作

OrderMapper.java

public interface OrderMapper {

    public List<Order> findOrderByLikeNumber(@Param("orderNumber")
                                                     String orderNumber);

    //public List<Order> findOrderByNumbers(List<String> numbers);

    public List<Order> findOrderByNumbers(@Param("numbers")
                                                  List<String> numbers);

    public Order findOrderById(Integer id);

    public Order findOrderUserById(Integer id);

    public Order findOrderUserItemById(Integer id);

    public Order findOrderUserItemProductById(Integer id);

    public int updateOrderById (Order order);

    public int deleteOrderById (Integer id);

    public int insertOrder (Order order);

    public int insertOrders (List<Order> orders);

}

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="cn.test.java.dao.OrderMapper">

    <!--
        autoMapping : 开启数据表字段与JavaBean的自动映射,基于驼峰规则
                      需要在mybatis配置文件中开启驼峰命名规则
                      <setting name="mapUnderscoreToCamelCase" value="true"/>
                例如:
                    Mysql     column   -> user_id
                    JavaBean  property -> userId
    -->
    <!--
        association : 一对一映射

                例如:JavaBean Order对象中有一个对User对象的引用,需要用association来映射
                class Order {
                    String oid;
                    User user;
                }
        association 属性说明 : {
            property : Order对象中User的属性名称user
            javaType : 对应Java中的User类型,
            autoMapping : 开启自动映射
        }
    -->
    <resultMap id="orderUser" type="Order" autoMapping="true">
        <id property="oid" column="oid"/>
        <association property="user" javaType="cn.test.java.domain.User" autoMapping="true">
            <id property="id" column="user_id"/>
        </association>
    </resultMap>

    <!--
        collection : 一对多映射

                例如:JavaBean Order对象中有一个List<OrderItem>,需要用collection来映射
                class Order {
                    String oid;
                    User user;
                    List<OrderItem> orderitemList;
                }
        collection 属性说明 : {
            property : Order对象中List<OrderItem>的属性名称orderitemList
            javaType : 对应Java中的List集合类型
            ofType   : List集合的泛型类型
            autoMapping : 开启自动映射
        }
    -->
    <resultMap id="orderUserItem" type="Order" autoMapping="true" extends="orderUser">
        <collection property="orderitemList" javaType="List"
                    ofType="cn.test.java.domain.Orderitem" autoMapping="true">
            <id column="item_id" property="itemId"/>
        </collection>
    </resultMap>

    <resultMap id="orderUserItemProduct" type="Order" autoMapping="true">
        <id property="oid" column="oid"/>
        <association property="user" javaType="User" autoMapping="true">
            <id property="id" column="user_id"/>
        </association>
        <collection property="orderitemList"
                    javaType="List" ofType="Orderitem" autoMapping="true">
            <id column="item_id" property="itemId"/>
            <association property="product" javaType="Product" autoMapping="true">
                <id property="pid" column="product_id"/>
            </association>
        </collection>
    </resultMap>

    <insert id="insertOrder" useGeneratedKeys="true" keyProperty="oid" keyColumn="oid">
        INSERT INTO tb_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            oid,
            <if test=" userId != null ">user_id,</if>
            <if test=" orderNumber != null and orderNumber.trim() != '' ">
                order_number,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            null,
            <if test=" userId != null ">
                #{userId},
            </if>
            <if test=" orderNumber != null and orderNumber.trim() != '' ">
                #{orderNumber},
            </if>
        </trim>
    </insert>

    <insert id="insertOrders" parameterType="List">
        INSERT INTO tb_order (oid,user_id,order_number) VALUES
        <if test="list != null and list.size() > 0">
            <foreach collection="list" item="item" index="index" separator=",">
                (
                null,
                #{item.userId},
                #{item.orderNumber}
                )
            </foreach>
        </if>
    </insert>


    <update id="updateOrderById" parameterType="Order">
        UPDATE tb_order
        <set>
            <trim suffixOverrides=",">
                <if test=" userId != null ">
                    user_id = #{userId},
                </if>
                <if test=" orderNumber != null ">
                    order_number = #{orderNumber}
                </if>
            </trim>
        </set>
        WHERE oid = #{oid}
    </update>

    <delete id="deleteOrderById" parameterType="int">
      DELETE FROM tb_order WHERE oid = #{id}
    </delete>

    <select id="findOrderByNumbers" parameterType="List" resultType="Order">
        SELECT * FROM tb_order
        <where>
            <!--public List<Order> findOrderByNumbers(List<String> numbers);-->
            <!--
            <if test="list != null and list.size() > 0">
                and order_number IN
                <foreach collection="list" item="item" index="index"
                        open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            -->
            <!--public List<Order> findOrderByNumbers(@Param("numbers")
                                                        List<String> numbers);-->
            <if test="numbers != null and numbers.size() > 0">
                and order_number IN
                <foreach collection="numbers" item="item"
                         index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="findOrderByLikeNumber" parameterType="String" resultType="Order">
        SELECT * FROM tb_order
        <where>
            <if test="orderNumber != null and orderNumber.trim() != ''">
                AND order_number LIKE CONCAT('%', #{orderNumber}, '%')
            </if>
        </where>
    </select>

    <select id="findOrderById" parameterType="int" resultType="Order">
        SELECT * FROM tb_order WHERE oid = #{id}
    </select>

    <select id="findOrderUserById" parameterType="int" resultMap="orderUser">
        SELECT * FROM tb_order as o
        LEFT JOIN tb_user as u ON o.user_id= u.id
        WHERE o.oid = #{id}
    </select>

    <select id="findOrderUserItemById" parameterType="int" resultMap="orderUserItem">
        SELECT * FROM tb_order as o
        LEFT JOIN tb_user as u ON o.user_id= u.id
        LEFT JOIN tb_orderitem AS it ON it.order_id = o.oid
        WHERE o.oid = #{id}
    </select>

    <select id="findOrderUserItemProductById"
            parameterType="int" resultMap="orderUserItemProduct">
        SELECT * FROM tb_order as o
        LEFT JOIN tb_user as u ON o.user_id= u.id
        LEFT JOIN tb_orderitem AS it ON it.order_id = o.oid
        LEFT JOIN tb_product AS p ON p.pid = it.product_id
        WHERE o.oid = #{id}
    </select>

</mapper>

Order.java

public class Order {

    private Integer oid;

    private Long userId;

    private String orderNumber;

    private User user;

    private List<Orderitem> orderitemList;
}

User.java

public class User {

    private Long id;

    private String userName;

    private String password;

    private String name;

    private Integer age;

    private Integer sex;

    private Date birthday;

    private Date created;

    private Date updated;
}

Orderitem.java

public class Orderitem {

    private Integer itemId;

    private Double totalPrice;

    private Integer status;

    private Product product;
}

Product.java

public class Product {

    private Integer pid;

    private String pname;

    private Float price;

    private String description;
}

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <properties resource="jdbc.properties"></properties>

    <settings>
        <!-- 开启驼峰标志 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <package name="cn.test.java.domain"></package>
    </typeAliases>

    <!--运行环境可以配置多个, default指定默认使用哪个-->
    <environments default="development">
        <!--配置环境, id是这个环境的唯一标识-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="cn.test.java.dao"/>
    </mappers>

</configuration>