MyBatis多表查询
一对一查询
身份证和人员就是一对一的关系,一个人有一张身份证,一张身份证也只属于一个人。
准备测试用的数据库和表
-- 创建数据库
CREATE DATABASE mybatistest3;
-- 使用数据库
USE mybatistest3;
-- 身份证表
CREATE TABLE idcard(
id INT PRIMARY KEY AUTO_INCREMENT,
icode VARCHAR(18)
);
-- 人员表
CREATE TABLE person(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
page INT,
idcard_id INT UNIQUE,
CONSTRAINT idcard_id_fk FOREIGN KEY(idcard_id) REFERENCES idcard(id)
);
-- 添加身份证数据
INSERT INTO idcard(icode) VALUES('420923199111060616');
INSERT INTO idcard(icode) VALUES('422209199210010620');
INSERT INTO idcard(icode) VALUES('422209199512011119');
-- 添加人员信息
INSERT INTO person(pname,page,idcard_id) VALUES('张三',18,1);
INSERT INTO person(pname,page,idcard_id) VALUES('李四',20,2);
INSERT INTO person(pname,page,idcard_id) VALUES('王五',19,3);
创建映射接口
在com.itheima.dao
目录下创建PersonDao映射接口
package com.itheima.dao;
import com.itheima.domain.Person;
import java.util.List;
public interface PersonDao {
//根据Person的id查询
public Person selectPersonById(Integer id);
//查询所有的Person对象
public List<Person> selectAllPerson();
}
创建映射文件
在resources文件夹下创建com\itheima\mapper
文件夹,在此文件夹下创建PersonMapper.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="com.itheima.dao.PersonDao">
<!--描述结果集-->
<resultMap id="personAndCard" type="com.itheima.domain.Person">
<id property="pid" column="pid"></id>
<result property="pname" column="pname"/>
<result property="page" column="page"/>
<!--Person中包含IdCard-->
<association property="idcard" javaType="com.itheima.domain.IdCard">
<id property="id" column="idcard_id"/>
<result property="icode" column="icode"/>
</association>
</resultMap>
<!--一对一根据id查询个人信息-->
<select id="selectPersonById" parameterType="Integer" resultMap="personAndCard" >
select p.*,ic.icode from idcard ic,person p where p.idcard_id=ic.id and p.pid=#{pid}
</select>
<!--查询所有Person信息-->
<select id="selectAllPerson" resultMap="personAndCard">
select p.*,ic.icode
from person p,idcard ic
where p.idcard_id=ic.id
</select>
</mapper>
将映射文件配置到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>
<!-- 引用config.properties文件中的jdbc配置信息-->
<properties resource="jdbc.properties"/>
<!-- MyBatis可以配置成适应多种环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<mapper resource="com/itheima/dao/PersonMapper.xml"/>
</mappers>
</configuration>
创建测试类
import com.itheima.dao.PersonDao;
import com.itheima.domain.Person;
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.Test;
import java.io.IOException;
import java.util.List;
public class PersonDaoTest {
@Test
public void test() {
try {
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
SqlSession sqlSession = ssf.openSession(true);
PersonDao personDao = sqlSession.getMapper(PersonDao.class);
Person p = personDao.selectPersonById(1);
System.out.println(p);
System.out.println("-------------------");
List<Person> list = personDao.selectAllPerson();
for (Person person : list) {
System.out.println(person);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
一对多查询
用户和订单就是一对多的关系,一个用户可以有多个订单,但是一个订单不能给多个用户。
准备测试用的数据库和表
-- 创建用户表
CREATE TABLE USER(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
PASSWORD VARCHAR(20)
);
INSERT INTO USER(username,PASSWORD) VALUES('zhangsan','123');
INSERT INTO USER(username,PASSWORD) VALUES('lisi','777');
INSERT INTO USER(username,PASSWORD) VALUES('wangwu','666');
-- 创建订单表
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
ordersn VARCHAR(10), -- 订单号
user_id INT, -- 用户id
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES USER(uid)
);
INSERT INTO orders(ordersn,user_id) VALUES('2019081701',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081702',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081703',2);
INSERT INTO orders(ordersn,user_id) VALUES('2019081704',2);
创建USER类
在com.itheima.domain
包下创建User类,一个User对象可能包含多个订单
package com.itheima.domain;
public class Orders {
private int id;
private String ordersn;
//此处省略get和set方法
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordersn='" + ordersn + '\'' +
'}';
}
}
创建Orders类
在com.itheima.domain
包下创建User类,一个User对象可能包含多个订单
package com.itheima.domain;
import java.util.List;
public class User {
private Integer uid;
private String username;
private String password;
private List<Orders> ordersList;
//此处省略get和set方法
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", username='" + username + '\'' +
", password='" + password + '\'' +
", ordersList=" + ordersList +
'}';
}
}
创建映射接口
在com.itheima.dao2
包下创建UserDao
接口
package com.itheima.dao2;
import com.itheima.domain.Orders;
import com.itheima.domain.User;
import java.util.List;
public interface UserDao {
//根据用户id查询用户信息以及,它所有的订单信息
public User selectUserOrderById(Integer uid);
}
创建映射文件
在resources的com\itheima\mapper
文件夹下,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">
<mapper namespace="com.itheima.dao.UserDao">
<resultMap id="userAndOrders" type="com.itheima.domain.User">
<id property="uid" column="uid"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<collection property="ordersList" ofType="com.itheima.domain.Orders">
<id property="id" column="id"/>
<result property="ordersn" column="ordersn"/>
</collection>
</resultMap>
<!--查询一个用户的订单信息-->
<select id="selectUserOrderById" parameterType="Integer" resultMap="userAndOrders">
select u.*,o.ordersn from user u ,orders o where u.uid=o.user_id and u.uid=#{uid}
</select>
</mapper>
创建测试类
import com.itheima.dao2.UserDao;
import com.itheima.domain.Orders;
import com.itheima.domain.User;
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.Test;
import java.io.IOException;
import java.util.List;
public class UserDaoTest {
@Test
public void test(){
SqlSessionFactory ssf = null;
try {
ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
SqlSession sqlSession = ssf.openSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
//查询uid为1的所有用户的订单
User user = mapper.selectUserOrderById(1);
System.out.println(user);
} catch (IOException e) {
e.printStackTrace();
}
}
}
多对多查询
商品和订单就是多对多的关系,一个订单可以有多个商品,一个商品可以属于多个订单。
准备测试用的数据库和表
-- 用户表前面创建过,这里就不用创建了
-- 创建订单表
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
ordersn VARCHAR(10), -- 订单号
user_id INT, -- 用户id
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES USER(uid)
);
INSERT INTO orders(ordersn,user_id) VALUES('2019081701',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081702',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081703',2);
INSERT INTO orders(ordersn,user_id) VALUES('2019081704',2);
-- 创建商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
price DOUBLE
);
-- 添加商品信息
INSERT INTO product(pname,price) VALUES('小米9',2799);
INSERT INTO product(pname,price) VALUES('魅族',3699);
INSERT INTO product(pname,price) VALUES('Iphone11',7999);
INSERT INTO product(pname,price) VALUES('OPPO9',3789);
INSERT INTO product(pname,price) VALUES('华为P30',5699);
INSERT INTO product(pname,price) VALUES('坚果3',3466);
-- 订单和商品的中间表(订单详情表)
CREATE TABLE orders_detail(
id INT PRIMARY KEY AUTO_INCREMENT,
orders_id INT,
product_id INT,
CONSTRAINT orders_id FOREIGN KEY (orders_id) REFERENCES orders(id),
CONSTRAINT product_id FOREIGN KEY (product_id) REFERENCES product(id)
);
-- 添加订单信息
INSERT INTO orders_detail(orders_id,product_id) VALUES(1,1);
INSERT INTO orders_detail(orders_id,product_id) VALUES(1,2);
INSERT INTO orders_detail(orders_id,product_id) VALUES(3,2);
INSERT INTO orders_detail(orders_id,product_id) VALUES(3,3);
INSERT INTO orders_detail(orders_id,product_id) VALUES(2,3);
INSERT INTO orders_detail(orders_id,product_id) VALUES(2,4);
INSERT INTO orders_detail(orders_id,product_id) VALUES(4,3);
INSERT INTO orders_detail(orders_id,product_id) VALUES(4,1);
创建Pruduct类
package com.itheima.domain;
import java.util.List;
public class Product {
private int pid;
private String pname;
private Double price;
//一个商品可以被多个订单引用
private List<Orders> ordersList;
//此处省略的get和set方法
@Override
public String toString() {
return "Product{" +
"pid=" + pid +
", pname='" + pname + '\'' +
", price=" + price +
", ordersList=" + ordersList +
'}';
}
}
创建Orders类
package com.itheima.domain;
import java.util.List;
public class Orders {
private int id;
private String ordersn;
//一个订单也可以包含多个商品
private List<Product> productList;
//此处省略了get和set方法
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordersn='" + ordersn + '\'' +
", productList=" + productList +
'}';
}
}
创建映射接口
public interface OrdersDao {
//查询所有订单和产品信息
public List<Orders> selectAllOrdersAndProducts();
}
创建映射文件
在resources文件夹下创建com\itheima\mapper
文件夹下,创建映射文件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">
<mapper namespace="com.itheima.dao2.OrdersDao">
<resultMap id="allOrdersAndProducts" type="com.itheima.domain.Orders">
<id property="id" column="id"/>
<result property="ordersn" column="ordersn"/>
<collection property="productList" ofType="com.itheima.domain.Product">
<id property="pid" column="pid"/>
<result property="pname" column="pname"/>
<result property="price" column="price"/>
</collection>
</resultMap>
<select id="selectAllOrdersAndProducts" resultMap="allOrdersAndProducts">
SELECT o.*,p.id AS pid,p.pname,p.price FROM orders o , product p , orders_detail od
WHERE od.orders_id=o.id AND od.product_id=p.id
</select>
</mapper>
创建测试类
public class OrdersAndProductTest {
@Test
public void test(){
try {
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
SqlSession sqlSession = ssf.openSession();
OrdersDao mapper = sqlSession.getMapper(OrdersDao.class);
List<Orders> orders = mapper.selectAllOrdersAndProducts();
for (Orders order : orders) {
System.out.println(order);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}