Mybatis、ResultMap、sql的学习

ResultMap简介和用法

resultMap是Mybatis最强大的元素，它可以将查询到的复杂数据（比如查询到几个表中数据）映射到一个结果集当中。

<!--column不做限制，可以为任意表的字段，而property须为type 定义的pojo属性-->
<resultMap id="唯一的标识" type="映射的pojo对象">
  <id column="表的主键字段，或者可以为查询语句中的别名字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
  <result column="表的一个字段（可以为任意表的一个字段）" jdbcType="字段类型" property="映射到pojo对象的一个属性（须为type定义的pojo对象中的一个属性）"/>
  <association property="pojo的一个对象属性" javaType="pojo关联的pojo对象">
    <id column="关联pojo对象对应表的主键字段" jdbcType="字段类型" property="关联pojo对象的主席属性"/>
    <result  column="任意表的字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
  </association>
  <!-- 集合中的property须为oftype定义的pojo对象的属性-->
  <collection property="pojo的集合属性" ofType="集合中的pojo对象">
    <id column="集合中pojo对象对应的表的主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
    <result column="可以为任意表的字段" jdbcType="字段类型" property="集合中的pojo对象的属性" />  
  </collection>
</resultMap>

resultType可以把查询结果封装到pojo类型中，但必须pojo类的属性名和查询到的数据库表的字段名一致。如果sql查询到的字段与pojo的属性名不一致，则需要使用resultMap将字段名和属性名对应起来，进行手动配置封装，将结果映射到pojo中

• 如果collection标签是使用嵌套查询，格式如下：

<collection column="传递给嵌套查询语句的字段参数" property="pojo对象中集合属性" ofType="集合属性中的pojo对象" select="嵌套的查询语句" > 
 </collection>

案例

src\main\java\com\lgl\bean\Order.java

public class Order {
    // 订单id
    private int id;
    // 用户id
    private Integer userId;
   // private Integer user_id;
    // 订单号
    private String number;
    // 订单创建时间
    private Date createtime;
    // 备注
    private String note;
    private User user;

src\main\java\com\lgl\bean\OrderUser.java

public class OrderUser {

    private int oid;
    private Integer user_id;
    private String number;
    private Date createtime;
    private String note;

    private int uid;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

src\main\java\com\lgl\bean\User.java

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    private List<Order> orders;

src\main\java\com\lgl\dao\OrderDao.java

public interface OrderDao {
    public List<Order> findAll();
    List<OrderUser> findAllOrderUser();
    List<Order> findAllOrder2();
}

src\main\java\com\lgl\dao\UserDao.java

public interface UserDao {
    public List<User> findByUser(User user);
    List<User> findByIds(@Param("ids") List<Integer> list);
    List<User> findAllUsers();
}

src\main\resources\com\lgl\dao\OrderDao.xml

<mapper namespace="com.lgl.dao.OrderDao">
<!--  <select id="findAll" resultType="order">-->
<!--       select * from `order`;-->
<!--  </select>-->
    <resultMap id="OrderMap" type="com.lgl.bean.Order">

        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
    </resultMap>
     <select id="findAll" resultMap="OrderMap">
         select * from `order`;
     </select>

    <select id="findAllOrderUser" resultType="com.lgl.bean.OrderUser">
        select o.id as oid,
       o.user_id,
       o.number,
       o.createtime,
       o.note,
       u.id as uid,
       u.username,
       u.birthday,
       u.sex,
       u.address
       from `order` o left join `user` u
       on o.user_id=u.id;
    </select>

     <resultMap id="findAllOrder2Map" type="com.lgl.bean.Order" autoMapping="true">
         <id property="id" column="id"></id>
         <association  property="user" javaType="com.lgl.bean.User" autoMapping="true">
             <id column="uid" property="id"/>

         </association>
     </resultMap>
     <select id="findAllOrder2" resultMap="findAllOrder2Map">
       select o.id as id,
       o.user_id as userId,
       o.number,
       o.createtime,
       o.note,
       u.id as uid,
       u.username,
       u.birthday,
       u.sex,
       u.address
       from `order` o left join `user` u
       on o.user_id=u.id;
     </select>
</mapper>

src\main\resources\com\lgl\dao\UserDao.xml

<mapper namespace="com.lgl.dao.UserDao">
  <select id="findByUser" resultType="user">
       select * from user
       <where>
           <if test="username != null and username != '' ">
               and username like #{username}
           </if>
           <if test="address != null and address != ''">
               and address like #{address}
           </if>
           <if test="sex != null and sex != ''">
               and sex like #{sex}
           </if>
       </where>
  </select>

    <select id="findByIds" parameterType="list" resultType="user">
        select  * from user
        <where>
            <foreach collection="ids" item="id" open="id in(" close= ")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

    <resultMap id="findAllUsersMap" type="com.lgl.bean.User" autoMapping="true">
       <id property="id" column="uid"></id>
        <collection property="orders" ofType="com.lgl.bean.Order" autoMapping="true">
         <id property="id" column="oid"></id>
        </collection>
    </resultMap>

    <select id="findAllUsers" resultMap="findAllUsersMap">
         select u.id as uid,
        u.username,
        u.sex,
        u.address,
        o.id as oid,
        o.user_id as userId,
        o.number,
        o.createtime,
        o.note
        from `user` u left join `order` o
        on u.id=o.user_id;
    </select>
</mapper>

src\main\resources\SqlMapConfig.xml

 <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis02?characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        <mapper resource="UserMapper.xml"/>-->
        <mapper resource="com/lgl/dao/OrderDao.xml"/>
        <mapper resource="com/lgl/dao/UserDao.xml"/>
    </mappers>

src\test\java\com\lgl\pack01\Test01.java

@Test
    public void test01(){
        SqlSession session = MySessionUtils.getSession();

        OrderDao dao = session.getMapper(OrderDao.class);
        List<Order> list = dao.findAll();

        System.out.println(list);
        session.commit();
        session.close();
    }
}

src\test\java\com\lgl\pack01\Test02.java

        User user= new User();
//        user.setUsername("张%");
//        user.setAddress("北京市");
        UserDao dao = session.getMapper(UserDao.class);
        List<User> list = dao.findByUser(user);

        System.out.println(list);

src\test\java\com\lgl\pack01\Test03.java

UserDao dao = session.getMapper(UserDao.class);
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(3);
        list.add(5);
        List<User> users = dao.findByIds(list);

        System.out.println(users);

src\test\java\com\lgl\pack01\Test04.java

OrderDao orderDao = session.getMapper(OrderDao.class);
        List<OrderUser> list = orderDao.findAllOrderUser();

        System.out.println(list);

src\test\java\com\lgl\pack01\Test05.java

OrderDao orderDao = session.getMapper(OrderDao.class);
        List<Order> list = orderDao.findAllOrder2();

        System.out.println(list);

src\test\java\com\lgl\pack01\Test06.java

UserDao userDao = session.getMapper(UserDao.class);
        List<User> list = userDao.findAllUsers();

        System.out.println(list);