mybatis高級查詢——基于xml配置（詳細介紹）
提示
文章目錄
- mybatis高級查詢——基于xml配置（詳細介紹）
- 一、準備作業
- 二、一對一查詢
- 1. 需求分析
- 2. 代碼實作
- 三、一對多查詢
一、準備作業
步驟：
1、資料如下：
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_item
-- ----------------------------
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_name` varchar(32) NOT NULL COMMENT '商品名稱',
`item_price` float(6,1) NOT NULL COMMENT '商品價格',
`item_detail` text COMMENT '商品描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_item
-- ----------------------------
INSERT INTO `tb_item` VALUES ('1', 'iPhone 6', '5288.0', '蘋果公司新發布的手機產品，');
INSERT INTO `tb_item` VALUES ('2', 'iPhone 6 plus', '6288.0', '蘋果公司發布的新大屏手機，');
-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`order_number` varchar(20) NOT NULL COMMENT '訂單號',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES ('1', '1', '20140921001');
INSERT INTO `tb_order` VALUES ('2', '2', '20140921002');
INSERT INTO `tb_order` VALUES ('3', '1', '20140921003');
-- ----------------------------
-- Table structure for tb_orderdetail
-- ----------------------------
DROP TABLE IF EXISTS `tb_orderdetail`;
CREATE TABLE `tb_orderdetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(32) DEFAULT NULL COMMENT '訂單號',
`item_id` int(32) DEFAULT NULL COMMENT '商品id',
`total_price` double(20,0) DEFAULT NULL COMMENT '商品總價',
`status` int(11) DEFAULT NULL COMMENT '狀態',
PRIMARY KEY (`id`),
KEY `FK_orderdetail_1` (`order_id`),
KEY `FK_orderdetail_2` (`item_id`),
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`),
CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`item_id`) REFERENCES `tb_item` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_orderdetail
-- ----------------------------
INSERT INTO `tb_orderdetail` VALUES ('1', '1', '1', '5288', '1');
INSERT INTO `tb_orderdetail` VALUES ('2', '1', '2', '6288', '1');
INSERT INTO `tb_orderdetail` VALUES ('3', '2', '2', '6288', '1');
INSERT INTO `tb_orderdetail` VALUES ('4', '3', '1', '5288', '1');
-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(100) DEFAULT NULL COMMENT '用戶名',
`password` varchar(100) DEFAULT NULL COMMENT '密碼',
`name` varchar(100) DEFAULT NULL COMMENT '姓名',
`age` int(10) DEFAULT NULL COMMENT '年齡',
`sex` int(11) DEFAULT NULL COMMENT '0-女 1-男',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'zhangsan', '123456', '張三', '30', '1');
INSERT INTO `tb_user` VALUES ('2', 'lisi', '123456', '李四', '21', '0');
INSERT INTO `tb_user` VALUES ('3', 'wangwu', '123456', '王五', '22', '1');
INSERT INTO `tb_user` VALUES ('4', 'zhangwei', '123456', '張偉', '20', '1');
INSERT INTO `tb_user` VALUES ('5', 'lina', '123456', '李娜', '28', '0');
INSERT INTO `tb_user` VALUES ('6', '蔡徐坤', '123', '小菜', '18', '1');
2、包結構：創建java專案，匯入jar包和log4j日志組態檔以及連接資料庫的組態檔如下圖：
3、運行sql，得到各表之間的關系：
4、創建物體pojo類：
pojo類代碼如下：
package com.it.pojo;
/**
* 商品表
*/
public class Item {
private Integer id;
private String itemName;
private Float itemPrice;
private String itemDetail;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public Float getItemPrice() {
return itemPrice;
}
public void setItemPrice(Float itemPrice) {
this.itemPrice = itemPrice;
}
public String getItemDetail() {
return itemDetail;
}
public void setItemDetail(String itemDetail) {
this.itemDetail = itemDetail;
}
@Override
public String toString() {
return "Item{" +
"id=" + id +
", itemName='" + itemName + '\'' +
", itemPrice=" + itemPrice +
", itemDetail='" + itemDetail + '\'' +
'}';
}
}
package com.it.pojo;
import java.util.List;
/**
* 訂單表
*
*/
public class Order {
private Integer id;
private String orderNumber;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
}
package com.it.pojo;
public class Orderdetail {
private Integer id;
private Double totalPrice;
private Integer status;
public Double getTotalPrice() {
return totalPrice;
}
public void setTotalPrice(Double totalPrice) {
this.totalPrice = totalPrice;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@Override
public String toString() {
return "Orderdetail [id=" + id + ", totalPrice=" + totalPrice
+ ", status=" + status + "]";
}
}
package com.it.pojo;
import java.io.Serializable;
import java.util.List;
public class User implements Serializable{
private Long id;
// 用戶名
private String userName;
// 密碼
private String password;
// 姓名
private String name;
// 年齡
private Integer age;
//0-女 1-男
private Integer sex;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", age=" + age +
", sex=" + sex +
'}';
}
}
5、配置UserMapper.xml映射檔案和介面
需求：查詢id=1的用戶資訊
介面:
package com.it.dao;
import com.it.pojo.User;
import org.apache.ibatis.annotations.Param;
public interface UserDao {
User findById(@Param("id") Long id);
}
映射檔案:
<?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 namespace="com.it.dao.UserDao">
<resultMap id="userMap" type="user" autoMapping="true">
<id column="id" property="id"/>
<result column="user_name" property="userName"/>
</resultMap>
<select id="findById" resultMap="userMap">
select * from tb_user where id=#{id}
</select>
</mapper>
注意：（1）此時核心組態檔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">
<!--mybatis核心配置的根標簽-->
<configuration>
<properties resource="jdbc.properties"/>
<!--取別名-->
<typeAliases>
<package name="com.it.pojo"/>
</typeAliases>
<!--mybatis環境的配置,可以配置多個 比如 開發環境,測驗環境,生產環境-->
<environments default="development">
<!--通常我們只配置一個就可以,id是環境的名稱-->
<environment id="development">
<!--事務管理器,有JDBC來管理-->
<transactionManager type="JDBC"/>
<!--資料源配置,POOLED表示使用mybatis自帶的資料庫連接池-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClass}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.userName}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--批量加載映射檔案-->
<mappers>
<!--加載映射檔案,-->
<mapper resource="UserDao.xml"/>
</mappers>
</configuration>
（2）log4j.properties為：
# 設定Logger輸出級別和輸出目的地
log4j.rootLogger=debug, stdout
# 把日志資訊輸出到控制臺
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout
（3）jdbc.properties為：
jdbc.url=jdbc:mysql:///day07_01
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.userName=root
jdbc.password=root
關于這幾個檔案的配置，在我的另一篇文章中有詳細的介紹，在此不做贅述，
此時工程結構圖如下：
6、單元測驗
目的:測驗框架是否搭建成功
?思路:通過junit注解@BeforeClass初始化一次Mapper動態代理物件,其他方法直接呼叫公共mapper變數即可
代碼如下：
package com.it.test;
import com.it.dao.UserDao;
import com.it.pojo.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.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class TestAll {
private static UserDao userDao;
@BeforeClass
public static void init(){
//1.加載核心組態檔,構建會話工廠
InputStream in = null;
try {
in = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
//獲取會話物件
SqlSession session = sessionFactory.openSession();
//獲取代理物件
userDao = session.getMapper(UserDao.class);
}
@Test
public void test1(){
User user = userDao.findById(1l);
System.out.println(user.toString());
}
}
運行結果如下則說明搭建成功：
二、一對一查詢
需求：通過訂單編號20140921003查詢出訂單資訊，并查詢出下單人資訊，
說明:一個訂單編號對應一個訂單，一個訂單只能屬于一個人，所以上述需求實作是一對一的實作
1. 需求分析
步驟：
（1）書寫查詢的sql
（2）訂單物體添加屬性映射
（3）添加order介面及方法
（4）創建order映射檔案,撰寫SQL
（5）測驗
2. 代碼實作
（1）書寫查詢的sql
代碼如下：
-- 分步查詢
-- 1.查詢訂單資訊
select * from tb_order where order_number='20140921003';-- user_id=1
-- 2.根據用戶ID查詢用戶資訊
select * from tb_user where id=1;
-- 一步查詢
select * from tb_order as tor,tb_user as tus where tor.user_id=tus.id
and tor.order_number='20140921003';
（2）訂單物體添加屬性映射
在Order類中添加屬性：
private User tuser;
private List<Orderdetail> orderdetails;
（3）添加Order介面及方法：
package com.it.dao;
import com.it.pojo.Order;
import org.apache.ibatis.annotations.Param;
public interface OrderDao {
//根據訂單編號查詢訂單資訊,包含用戶資訊
Order findByOrderNumber(@Param("orderNumber") String orderNumber);
}
（4）創建order映射檔案,撰寫SQL
<?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 namespace="com.it.dao.OrderDao">
<resultMap id="orderMap" type="order" autoMapping="true">
<id column="order_id" property="id"/>
<result column="order_number" property="orderNumber"/>
<!--說明:
association用于一對一映射
property="tuser":表示關聯訂單類中的User變數
javaType="user":表示關聯的變數型別
-->
<association property="tuser" javaType="user" autoMapping="true">
<id column="id" property="id"/>
<result column="user_name" property="userName"/>
</association>
</resultMap>
<select id="findByOrderNumber" resultMap="orderMap">
select tus.*,tor.id as order_id,tor.order_number
from tb_order as tor,
tb_user as tus
where tor.user_id = tus.id
and tor.order_number = #{orderNumber}
</select>
</mapper>
注意此時的核心組態檔：
（5）測驗
代碼如下：
package com.it.test;
import com.it.dao.OrderDao;
import com.it.dao.UserDao;
import com.it.pojo.Order;
import com.it.pojo.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.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class TestAll {
private static UserDao userDao;
private static OrderDao orderDao;
@BeforeClass
public static void init(){
//1.加載核心組態檔,構建會話工廠
InputStream in = null;
try {
in = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
//獲取會話物件
SqlSession session = sessionFactory.openSession();
//獲取代理物件
userDao = session.getMapper(UserDao.class);
orderDao=session.getMapper(OrderDao.class);
}
@Test
public void test1(){
User user = userDao.findById(1l);
System.out.println(user.toString());
}
@Test
public void test2(){
Order order = orderDao.findByOrderNumber("20140921003");
System.out.println(order);
}
}
運行結果如下圖：
注意：mybatis框架的一對一查詢格式：
<resultMap id="映射ID" type="主表物體名稱" autoMapping="true" >
<!-- 添加主表語主表物體映射 -->
......
<!--association：配置關聯物件（User）的映射關系,一般與resultMap標簽聯合使用 -->
<association property="主表物體中對應從表的屬性名稱" javaType="從表物體型別" autoMapping="true">
<!-- 添加從表中欄位與物體屬性映射關系 -->
</association>
</resultMap>
三、一對多查詢
需求：查詢id為1的用戶及其訂單資訊
分析：一個用戶可以有多個訂單，一個訂單只能屬于一個用戶，
步驟：
1、查詢SQL分析
-- 1.分步查詢
-- 1.1 查詢用戶資訊
select * from tb_user where id=1;
-- 1.2 根據用戶id查詢訂單集合
select * from tb_order where user_id=1;
-- 2.一次性獲取
select
tus.*,
tor.id as order_id,
tor.order_number
from tb_user as tus,
tb_order as tor
where tus.id = tor.user_id
and tus.id = 1;
2、添加關聯關系
User物體添加映射關系：
public class User implements Serializable{
private List<Order> orders;
private Long id;
// 用戶名
private String userName;
// 密碼
private String password;
// 姓名
private String name;
.....
3、撰寫介面方法（在UserDao中）
User findUserAndOrderById(@Param("id")Long id);
4、撰寫映射檔案（在UserDao.xml中）
<resultMap id="userOrderMap" type="user" autoMapping="true">
<id column="id" property="id"/>
<result column="user_name" property="userName"/>
<collection property="orders" javaType="list" ofType="order" autoMapping="true">
<id column="order_id" property="id"/>
<result column="order_number" property="orderNumber"/>
</collection>
</resultMap>
<select id="findUserAndOrderById" resultMap="userOrderMap" >
select
tus.*,
tor.id as order_id,
tor.order_number
from tb_user as tus,
tb_order as tor
where tus.id = tor.user_id
and tus.id = #{id}
</select>
5、測驗
@Test
public void test3(){
User user = userDao.findUserAndOrderById(1l);
System.out.println(user);
}
運行結果如下圖：
注意：mybatis框架的一對多查詢格式：
<!--配置一對多關系映射-->
<resultMap id="xx" type="xx" autoMapping="true">
<!--user表主鍵映射-->
<id column="xx" property="xx"/>
<!--映射物體類中List<Order>集合使用功能Collection標簽-->
<collection property="xxx" javaType="list" ofType="xxx" autoMapping="true">
<!--主鍵映射-->
<id column="xx" property="xx"/>
<result column="xx" property="xx"/>
</collection>
</resultMap>
碼字不易，喜歡的朋友麻煩點個贊吧！
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/205124.html
標籤：其他