本篇将以一个简单的商品交易过程中的操作为实例,讲解MyBatis高级映射,即一对一,一对多,多对多的处理。
由于属性和字段都是一致的这里就省略啦。
1.高级映射用到的数据库表
user表+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
item表
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| price | float | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
`order`表,order为关键字需要使用`order`,user_id为外键
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | YES | MUL | NULL | |
| number | varchar(32) | YES | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
orderdetail表
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| order_id | int(11) | YES | MUL | NULL | |
| item_id | int(11) | YES | MUL | NULL | |
| item_num | int(11) | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
2.PO类
创建PO类,针对1中创建的数据表,分别创建对应的类对象。由于属性和字段都是一致的这里就省略啦。
3.resultType实现
1)查询订单表,要求除了显示 订单ID,订单数量,订单时间以外,还需要显示 用户名,因为要显示用户名,所以会用到多表查询,这里由于关联的用户有一个外键,user_id,所以可以使用内连接进行查询,在控制台使用SQL命令操作如下mysql> select `order`.id, user.username,`order`.number, `order`.createtime from `order`, user where `order`.user_id=user.id;
+----+----------+--------+---------------------+
| id | username | number | createtime |
+----+----------+--------+---------------------+
| 1 | high | 3 | 2016-06-19 11:29:30 |
| 2 | high | 2 | 2016-06-18 12:23:20 |
| 3 | lily | 2 | 2016-05-12 12:22:20 |
+----+----------+--------+---------------------+
2)完成用于查询的UserOrder.java,由于从User中获取的数据只有username,大部分内容是从Order中获取,所以在构造PO类时,以需求最多的Order为父类,这样可以极大的减少工作量。
package pojo;
public class OrderUser extends Order {
private String username;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public String toString() {
return "\r\n OrderUser [username=" + username + super.toString() + "]";
}
}
3)完成 OrderUserMapper.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="mapper.OrderUserMapper">
<select id="selectOrderUser" resultType="pojo.OrderUser">
select `order`.id, user.username,`order`.number, `order`.createtime from `order`, user where `order`.user_id=user.id;
</select>
</mapper>
4)完成 OrderUserMapper 接口
package mapper;
import java.util.List;
import pojo.OrderUser;
public interface OrderUserMapper {
List<OrderUser> selectOrderUser() throws Exception;
}
5)测试类
package test;
import java.io.InputStream;import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import mapper.OrderUserMapper;
import pojo.OrderUser;
public class UserOrderTest {
public static void main(String[] args) throws Exception{
InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = sessionFactory.openSession(true);
OrderUserMapper orderUserMapper = session.getMapper(OrderUserMapper.class);
List<OrderUser> list = orderUserMapper.selectOrderUser();
System.out.println(list);
}
}
3.resultMap实现
resultMap实现的思路是Order中有一个外键user_id,那么通过user_id可以将User作为子查询,从而将查询结果作为Order的一个属性。注意这里我们是映射了整个User到Order中,而不仅仅是username。OrderUser类修改如下:package pojo;
public class OrderUser extends Order {
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
mapper.xml,由于在SQL语句中没有查询出user的birthday等信息,返回结果中就没有这些信息。
<?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="mapper.OrderUserMapper">
<resultMap id="orderUserMap" type="pojo.OrderUser">
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<!-- Order外键user_id关联的用户信息 -->
<association property="user" javaType="pojo.User">
<id column="user_id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<select id="selectOrderUserByResultMap" resultMap="orderUserMap">
select `order`.id, user.username, `order`.number, `order`.createtime from `order`, user where `order`.user_id=user.id;
</select>
</mapper>
测试类:
由于在SQL语句中没有查询出user的birthday等信息,返回结果中就没有这些信息。
package test;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import mapper.OrderUserMapper;
import pojo.OrderUser;
public class UserOrderTest {
public static void main(String[] args) throws Exception{
InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = sessionFactory.openSession(true);
OrderUserMapper orderUserMapper = session.getMapper(OrderUserMapper.class);
List<OrderUser> list = orderUserMapper.selectOrderUserByResultMap();
System.out.println(list);
}
}
<完>