一对一映射
SqlMapConfig.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="db.properties"></properties>
<typeAliases>
<!-- 定义别名后，可以在mapper.xml中直接使用别名来代替被定义的数据类型 -->
<!-- 针对单个别名定义
type:要定义为别名的数据类型
alias:别名名称
-->
<!-- <typeAlias type="com.yunxiang.mybatis.pojo.User" alias="user"/> -->
<!-- 批量定义别名
name:指定要定义别名的包名，
mybatis会将该包下的所有类，
自动定义别名，别名的名称是类名(类的首字母小写小写都可以)
-->
<package name="com.yunxiang.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理，事务控制由mybatis -->
<transactionManager type="JDBC" />
<!-- 数据库连接池，由mybatis管理 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 加载映射文件 -->
<mappers>
<!-- 提供resource来加载单个映射文件 -->
<!-- <mapper resource="sqlmap/User.xml"/>
<mapper resource="mapper/UserMapper.xml"/> -->
<!-- 提供mapper接口来单个加载单个映射文件
但是要保持mapper接口名与对应的xml文件名称一致，
并且在一个目录下。
-->
<!-- <mapper class="com.yunxiang.mybatis.dao.UserMapper"/> -->
<!-- 提供mapper接口来批量加载映射文件 -->
<package name="com.yunxiang.mybatis.dao"/>
</mappers>
</configuration>
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
username=root
password=root
OrdersMapper.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">
<!-- namespace命名空间，作用就是对SQL进行
分类化管理，在使用mapper代理时，namespace的值，
必须是对应接口的全路径名。并且接口中的抽象方法、接受参数类型和返回值类型，
必须与xml中的各标签的id值、接受参数类型和返回参数保持一致。并且xml和接口
必须放置同一个包下。
-->
<mapper namespace="com.yunxiang.mybatis.dao.OrdersMapper">
<!-- 定义resultMap
id:用于指定查询列中的唯一标识，如果有多个列组成唯一标识，配置多个id
-->
<resultMap type="orders" id="ordersResultMapper">
<id column="id" property="id"/>
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createTime" property="createTime"/>
<result column="note" property="note"/>
<!--
association:用于关联单个对象的信息
property:要将关联查询的用户对应到orders中的那个属性
javaType:表示映射对应的对象
-->
<association property="user" javaType="user">
<!--
id:用于指定关联用户的唯一标识
column:指定唯一标识用户的列
property:映射到user的那个属性
-->
<id column="user_id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="testFindOrdersUserResultMap" parameterType="int" resultMap="ordersResultMapper">
select
orders.*,
user.name,
user.sex,
user.address
from
orders,
user
where user.id=orders.user_id
</select>
</mapper>
Orders.java
import java.util.Date;
import java.util.List;
public class Orders {
private int id;
private int user_id;
private int number;
private Date createTime;
private String note;
//一对一的映射
private User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Orders [id=" + id + ", user_id=" + user_id + ", number=" + number + ", createTime=" + createTime
+ ", note=" + note + ", user=" + user + "]";
}
}
User.java
import java.util.Date;
public class User {
private int id;
private String name;
private Date brithday;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBrithday() {
return brithday;
}
public void setBrithday(Date brithday) {
this.brithday = brithday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", brithday=" + brithday + ", sex=" + sex + ", address=" + address
+ "]";
}
}
OrdersMapper.java
import java.util.List;
import com.yunxiang.mybatis.pojo.Orders;
public interface OrdersMapper {
List<Orders> testFindOrdersUserResultMap();
}
测试
import java.io.IOException;
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 org.junit.Before;
import org.junit.Test;
import com.yunxiang.mybatis.dao.OrdersMapper;
import com.yunxiang.mybatis.dao.UserMapper;
import com.yunxiang.mybatis.pojo.Orders;
import com.yunxiang.mybatis.pojo.User;
public class TestOrdersMapper {
private SqlSessionFactory factory;
@Before
public void setUp() throws Exception{
//mybatis配置文件
String resource = "SQLMapConfig.xml";
//得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建会话工厂
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindOrdersUserResultMap() throws Exception {
//通过工厂得到SQLSession
SqlSession session = factory.openSession();
//获取UserMapper对象
OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
List<Orders> list = ordersMapper.testFindOrdersUserResultMap();
System.out.println(list);
//释放资源
session.close();
}
}
一对多映射
OrdersMapper.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">
<!-- namespace命名空间，作用就是对SQL进行
分类化管理，在使用mapper代理时，namespace的值，
必须是对应接口的全路径名。并且接口中的抽象方法、接受参数类型和返回值类型，
必须与xml中的各标签的id值、接受参数类型和返回参数保持一致。并且xml和接口
必须放置同一个包下。
-->
<mapper namespace="com.yunxiang.mybatis.dao.OrdersMapper">
<!-- 查询订单及订单明细,使用继承 -->
<resultMap type="orders" id="ordersAndOrderDetailResultMap">
<!--订单信息 -->
<id column="id" property="id"/>
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createTime" property="createTime"/>
<result column="note" property="note"/>
<!-- 订单明细信息
一个订单关联了多个的订单明细
collection:对关联到的多条记录映射到集合属性中
property:将关联查询到的多条记录映射到Orders中的那个属性
ofType:指定映射到list集合属性中pojo的类型
-->
<collection property="list" ofType="ordersDetail">
<id column="orderdetail_id" property="id"/>
<result column="order_id" property="order_id"/>
<result column="item_id" property="item_id"/>
<result column="item_unm" property="item_unm"/>
</collection>
</resultMap>
<select id="testFindOrdersAndOrderDetailResultMap" resultMap="ordersAndOrderDetailResultMap">
select
orders.*,
orderdetail.id orderdetail_id,
orderdetail.order_id,
orderdetail.item_id,
orderdetail.item_unm
from
orders,
orderdetail
where orders.id = orderdetail.order_id
</select>
</mapper>
Orders.java
package com.yunxiang.mybatis.pojo;
import java.util.Date;
import java.util.List;
public class Orders {
private int id;
private int user_id;
private int number;
private Date createTime;
private String note;
//一对多
private List<OrdersDetail> list;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public List<OrdersDetail> getList() {
return list;
}
public void setList(List<OrdersDetail> list) {
this.list = list;
}
@Override
public String toString() {
return "Orders [id=" + id + ", user_id=" + user_id + ", number=" + number + ", createTime=" + createTime
+ ", note=" + note + ", list=" + list + "]";
}
}
OrdersDetail.java
public class OrdersDetail {
private int id;
private int order_id;
private int item_id;
private int item_unm;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getItem_id() {
return item_id;
}
public void setItem_id(int item_id) {
this.item_id = item_id;
}
public int getItem_unm() {
return item_unm;
}
public void setItem_unm(int item_unm) {
this.item_unm = item_unm;
}
public int getOrder_id() {
return order_id;
}
public void setOrder_id(int order_id) {
this.order_id = order_id;
}
@Override
public String toString() {
return "OrdersDetail [id=" + id + ", order_id=" + order_id + ", item_id=" + item_id + ", item_unm=" + item_unm
+ "]";
}
}
OrdersMapper.java
import java.util.List;
import com.yunxiang.mybatis.pojo.Orders;
public interface OrdersMapper {
List<Orders> testFindOrdersAndOrderDetailResultMap();
}
测试
import java.io.IOException;
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 org.junit.Before;
import org.junit.Test;
import com.yunxiang.mybatis.dao.OrdersMapper;
import com.yunxiang.mybatis.dao.UserMapper;
import com.yunxiang.mybatis.pojo.Orders;
import com.yunxiang.mybatis.pojo.User;
public class TestOrdersMapper {
private SqlSessionFactory factory;
@Before
public void setUp() throws Exception{
//mybatis配置文件
String resource = "SQLMapConfig.xml";
//得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建会话工厂
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindOrdersUserResultMap() throws Exception {
//通过工厂得到SQLSession
SqlSession session = factory.openSession();
//获取UserMapper对象
OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
List<Orders> list = ordersMapper.testFindOrdersAndOrderDetailResultMap();
System.out.println(list);
//释放资源
session.close();
}
}
多对多
思路：在User中定义List集合，再在Orders中定义List集合，再在OrdersDetail中定义Item；这是根据表是设计来实现的方法。
UserMapper.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">
<!-- namespace命名空间，作用就是对SQL进行
分类化管理，在使用mapper代理时，namespace的值，
必须是对应接口的全路径名。并且接口中的抽象方法、接受参数类型和返回值类型，
必须与xml中的各标签的id值、接受参数类型和返回参数保持一致。并且xml和接口
必须放置同一个包下。
-->
<mapper namespace="com.yunxiang.mybatis.dao.UserMapper">
<!-- 查询用户及购买商品 -->
<resultMap type="user" id="userAndItemResultMap">
<!-- 用户信息 -->
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 订单信息
一个用户对应多个订单，使用collection
-->
<collection property="orderList" ofType="orders">
<id column="id" property="id"/>
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createTime" property="createTime"/>
<result column="note" property="note"/>
<!-- 订单明细
一个订单有多个明细
-->
<collection property="list" ofType="ordersDetail">
<id column="orderdetail_id" property="id"/>
<result column="order_id" property="order_id"/>
<result column="item_id" property="item_id"/>
<result column="item_unm" property="item_unm"/>
<!-- 商品信息 -->
<association property="item" javaType="item">
<id column="item_id" property="id"/>
<result column="item_name" property="name"/>
<result column="item_detail" property="detail"/>
<result column="item_price" property="price"/>
</association>
</collection>
</collection>
</resultMap>
<select id="testFindUserAndItemResultMap" resultMap="userAndItemResultMap">
select
orders.*,
user.name,
user.sex,
user.address,
orderdetail.id orderdetail_id,
orderdetail.order_id,
orderdetail.item_id,
orderdetail.item_unm,
item.name item_name,
item.price,
item.detail
from
orders,
user,
orderdetail,
item
where user.id=orders.user_id and orders.id = orderdetail.order_id and item.id = orderdetail.item_id
</select>
</mapper>
User.java
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String name;
private Date brithday;
private String sex;
private String address;
//用户创建的订单列表
private List<Orders> orderList;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBrithday() {
return brithday;
}
public void setBrithday(Date brithday) {
this.brithday = brithday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", brithday=" + brithday + ", sex=" + sex + ", address=" + address
+ ", orderList=" + orderList + "]";
}
}
Orders.java
import java.util.Date;
import java.util.List;
public class Orders {
private int id;
private int user_id;
private int number;
private Date createTime;
private String note;
//一对多
private List<OrdersDetail> list;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<OrdersDetail> getList() {
return list;
}
public void setList(List<OrdersDetail> list) {
this.list = list;
}
@Override
public String toString() {
return "Orders [id=" + id + ", user_id=" + user_id + ", number=" + number + ", createTime=" + createTime
+ ", note=" + note + ", list=" + list + "]";
}
}
OrdersDetail.java
public class OrdersDetail {
private int id;
private int order_id;
private int item_id;
private int item_unm;
//明细对应的商品信息
private Item item;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getItem_id() {
return item_id;
}
public void setItem_id(int item_id) {
this.item_id = item_id;
}
public int getItem_unm() {
return item_unm;
}
public void setItem_unm(int item_unm) {
this.item_unm = item_unm;
}
public int getOrder_id() {
return order_id;
}
public void setOrder_id(int order_id) {
this.order_id = order_id;
}
public Item getItem() {
return item;
}
public void setItem(Item item) {
this.item = item;
}
@Override
public String toString() {
return "OrdersDetail [id=" + id + ", order_id=" + order_id + ", item_id=" + item_id + ", item_unm=" + item_unm + ", item=" + item + "]";
}
}
Item.java
import java.util.Date;
public class Item {
private int id;
private String name;
private float price;
private String detail;
private String pic;
private Date createTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
public String getPic() {
return pic;
}
public void setPic(String pic) {
this.pic = pic;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "Item [id=" + id + ", name=" + name + ", price=" + price + ", detail=" + detail + ", pic=" + pic
+ ", createTime=" + createTime + "]";
}
}
UserMapper.java
import java.util.List;
import com.yunxiang.mybatis.pojo.User;
public interface UserMapper {
List<User>testFindUserAndItemResultMap();
}
测试
import java.io.IOException;
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 org.junit.Before;
import org.junit.Test;
import com.yunxiang.mybatis.dao.OrdersMapper;
import com.yunxiang.mybatis.dao.UserMapper;
import com.yunxiang.mybatis.pojo.Orders;
import com.yunxiang.mybatis.pojo.User;
public class TestOrdersMapper {
private SqlSessionFactory factory;
@Before
public void setUp() throws Exception{
//mybatis配置文件
String resource = "SQLMapConfig.xml";
//得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建会话工厂
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserAndItemResultMap() throws Exception {
//通过工厂得到SQLSession
SqlSession session = factory.openSession();
//获取UserMapper对象
OrdersMapper ordersMapper = session.getMapper(UserMapper.class);
List<User> list = ordersMapper.testFindUserAndItemResultMap();
System.out.println(list);
//释放资源
session.close();
}
}
注：在mybatis映射时，如果是一对一的映射，在定义resultMap时，使用"association"子标签和"javaType"属性来定义被关联的pojo对象信息。如果是一对多的映射，在定义resultMap时，使用"collection"子标签和"ofType"属性来定义被关联的pojo对象信息。
966
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
