一、 项目介绍
1、Mybatis的Dao接口动态代理实现CRUD（增、删、改、查）案例
2、项目开发工具：
(1) jdk1.8.0_92
(2) mysql-5.5.45-winx64
(3) Navicat_for_MySQL_11.0.10
(4) IDEA-2019.3.4
(5) apache-maven-3.5.2
3、项目目录结构
4、数据库信息
/*
Navicat MySQL Data Transfer
Source Server : sougu
Source Server Version : 50545
Source Host : localhost:3306
Source Database : mybatis
Target Server Type : MYSQL
Target Server Version : 50545
File Encoding : 65001
Date: 2020-10-14 19:59:07
*/
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) DEFAULT NULL,
`item_price` float(6,1) DEFAULT NULL,
`item_detail` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000004 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_item
-- ----------------------------
INSERT INTO `tb_item` VALUES ('1000001', '华为手机', '5000.0', '华为P40');
INSERT INTO `tb_item` VALUES ('1000002', '小米', '5300.0', '小米10至尊版');
INSERT INTO `tb_item` VALUES ('1000003', '一加', '4000.0', '一+8');
-- ----------------------------
-- 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) DEFAULT NULL,
`order_number` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pkuid` (`user_id`),
CONSTRAINT `pkuid` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES ('1001', '1001', '10000234');
INSERT INTO `tb_order` VALUES ('1002', '1002', '10000345');
-- ----------------------------
-- Table structure for tb_orderdetail
-- ----------------------------
DROP TABLE IF EXISTS `tb_orderdetail`;
CREATE TABLE `tb_orderdetail` (
`id` int(11) NOT NULL,
`order_id` int(32) DEFAULT NULL,
`item_id` int(32) DEFAULT NULL,
`total_price` double(20,1) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_orderid` (`order_id`),
KEY `fk_itemid` (`item_id`),
CONSTRAINT `fk_itemid` FOREIGN KEY (`item_id`) REFERENCES `tb_item` (`id`),
CONSTRAINT `fk_orderid` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_orderdetail
-- ----------------------------
INSERT INTO `tb_orderdetail` VALUES ('1004', '1002', '1000003', null, '1');
INSERT INTO `tb_orderdetail` VALUES ('10001', '1001', '1000001', null, '1');
INSERT INTO `tb_orderdetail` VALUES ('10002', '1001', '1000002', null, '1');
INSERT INTO `tb_orderdetail` VALUES ('10003', '1002', '1000002', null, '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,
`password` varchar(100) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
`birthday` datetime DEFAULT NULL,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1001', 'cxx', 'cru123', '陈小小', '22', '男', '1998-03-16 11:33:14', '2020-10-15 11:33:43', '2020-10-16 19:52:11');
INSERT INTO `tb_user` VALUES ('1002', 'cbb', 'cjh234', '陈宝宝', '23', '男', '1997-07-18 11:34:40', '2020-10-15 11:34:54', '2020-10-16 19:52:18');
-- ----------------------------
二、源码
1、pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.hisoft</groupId>
<artifactId>mybatisdemo1</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- 单元测试依赖 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- 日志依赖-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.29</version>
</dependency>
</dependencies>
<!-- 控制配置maven构建项目的参数设置，设置jdk的版本-->
<build>
<!-- 配置插件-->
<plugins>
<!-- 配置具体的插件-->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<!-- 插件名字-->
<artifactId>maven-compiler-plugin</artifactId>
<!-- 插件版本-->
<version>3.8.1</version>
<!-- 配置插件的信息-->
<configuration>
<!-- 告诉maven我们写的项目是在jdk1.8上编译的-->
<source>1.8</source>
<!-- 告诉maven我们的程序应该运行在1.8的jdk上-->
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
2、resources中的配置文件
(1) jdbc.properties（mysql数据库的配置信息）
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:/ / /suogu
username=root
password=root
(2) log4j.properties（日志相关配置信息）
log4j.rootLogger=DEBUG,A1
log4j.logger.org.mybatis=DEBUG
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c]-[%p] %m%n
(3) mybatis-config.xml（mybatis框架相关配置信息）
<?xml version="1.0" encoding="UTF-8" ?>
<!--Mybatis约束信息-->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--Mybatis的主配置文件-->
<configuration>
<!-- 导入数据源-->
<properties resource="jdbc.properties"></properties>
<!--开启驼峰匹配-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--配置环境，名字可以任意起-->
<environments default="development">
<!--配置development环境,id的值与上方default的值一样-->
<environment id="development">
<!--配置事务类型-->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源（连接池）-->
<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>
<!-- 指定映射配置文件的位置，映射配置文件指的是每个Mapper独立的配置文件-->
<mappers>
<mapper class="com.hisoft.dao.UserMapper"></mapper>
</mappers>
</configuration>
(4) UserMapper.xml（UserMapper接口的映射文件）
<?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.hisoft.dao.UserMapper">
<!-- 高级查询 *********************************************************-->
<!--一对一查询-->
<resultMap id="orderUserResultMap" type="com.hisoft.pojo.Order" autoMapping="true">
<id property="id" column="id"/>
<!-- association: 完成子对象的映射
property : 表示自对象的属性名
javaType:指定对象的数据类型
autoMapping="true"完成自对象的属性的自动映射
-->
<association property="tbuser" javaType="com.hisoft.pojo.TbUser" autoMapping="true">
<!-- association书写的内容，完成自对象的映射，某个列，对应自对象的某个属性，属性的内容，参考resultMap的子标签
如果自对象的id的查询出来的名称不固定，需要去起别名，去固定。-->
<id property="id" column="id"/>
</association>
</resultMap>
<select id="queryOrderByOrderName" resultMap="orderUserResultMap">
select * from tb_order o left join tb_user u on o.user_id = u.id
where o.order_number = #{orderNumber}
</select>
<!--一对多查询-->
<resultMap id="orderUserDetailsResultMap" type="com.hisoft.pojo.Order" autoMapping="true">
<id property="id" column="id"
/>
<!--
collection
property:集合的变里名
javaType :集合类型ofType : 集合中保存对象的数据类型
-->
<association property="tbuser" javaType="com.hisoft.pojo.TbUser" autoMapping="true">
<!-- association书写的内容，完成自对象的映射，某个列，对应自对象的某个属性，属性的内容，参考resultMap的子标签如果自对象的id的查询出来的名称不固定，需要去起别名，去固定。-->
<id property="id" column="id"/>
</association>
<collection property="orderDetails" javaType="List" ofType="com.hisoft.pojo.OrderDetail" autoMapping="true">
<id property="id" column="detailid"/>
</collection>
</resultMap>
<select id="queryOrderAndUserDetailsByOrderName" resultMap="orderUserDetailsResultMap">
select *,d.id as detailid from tb_order o
left join tb_user u on o.user_id = u.id
left join tb_orderdetail d on o.id = d.order_id
where o.order_number = #{orderNumber}
</select>
<!--多对多查询-->
<resultMap id="orderUserDetailsItemResultMap" type="com.hisoft.pojo.Order" autoMapping="true">
<id property="id" column="id"/>
<!--
collection
property:集合的变里名
javaType :集合类型
ofType : 集合中保存对象的数据类型
-->
<association property="tbuser" javaType="com.hisoft.pojo.TbUser" autoMapping="true">
<!-- association书写的内容，完成自对象的映射，某个列，对应自对象的某个属性，属性的内容，参考resultMap的子标签
如果自对象的id的查询出来的名称不固定，需要去起别名，去固定。-->
<id property="id" column="uid"/>
</association>
<collection property="orderDetails" javaType="List" ofType="com.hisoft.pojo.OrderDetail" autoMapping="true">
<id property="id" column="detailid"/>
<association property="item" javaType="com.hisoft.pojo.Item" autoMapping="true">
<id property="id" column="itemid"/>
</association>
</collection>
</resultMap>
<select id="queryOrderAndUserAndDetailsAndItemByOrderName" resultMap="orderUserDetailsItemResultMap">
select *,u.id uid,d.id as detailid,ti.id itemid from tb_order o
left join tb_user u on o.user_id = u.id
left join tb_orderdetail d on o.id = d.order_id
left join tb_item ti on d.item_id = ti.id
where o.order_number = #{orderNumber}
</select>
</mapper>
3、pojo层（实体映射层）
TbUser.java
package com.hisoft.pojo;
import java.util.Date;
public class TbUser {
private int id;
private String username;
private String password;
private String name;
private int age;
private String sex;
private Date birthday;
private Date created;
private Date updated;
public int getId() {
return id;
}
public void setId(int 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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
public Date getUpdated() {
return updated;
}
public void setUpdated(Date updated) {
this.updated = updated;
}
public TbUser() {
}
public TbUser(int id, String username, String password, String name, int age, String sex, Date birthday, Date cerated, Date updated) {
this.id = id;
this.username = username;
this.password = password;
this.name = name;
this.age = age;
this.sex = sex;
this.birthday = birthday;
this.created = created;
this.updated = updated;
}
@Override
public String toString() {
return "TbUser{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", created=" + created +
", updated=" + updated +
'}';
}
}
Order.java
package com.hisoft.pojo;
import java.util.List;
//订单
public class Order {
private int id;
private Long userId;
private String orderNumber;
private TbUser tbuser;
private List<OrderDetail> orderDetails;
public List<OrderDetail> getOrderDetails() {
return orderDetails;
}
public void setOrderDetails(List<OrderDetail> orderDetails) {
this.orderDetails = orderDetails;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
public TbUser getTbuser() {
return tbuser;
}
public void setTbuser(TbUser tbuser) {
this.tbuser = tbuser;
}
public Order() {
}
public Order(int id, Long userId, String orderNumber, TbUser tbuser, List<OrderDetail> orderDetails) {
this.id = id;
this.userId = userId;
this.orderNumber = orderNumber;
this.tbuser = tbuser;
this.orderDetails = orderDetails;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", userId=" + userId +
", orderNumber='" + orderNumber + '\'' +
", tbuser=" + tbuser +
", orderDetails=" + orderDetails +
'}';
}
}
OrderDetail.java
package com.hisoft.pojo;
import java.io.Serializable;
//订单明细--需要实现序列化接口
public class OrderDetail implements Serializable {
private int id;
private int orderID;
private int itemId;
private double totalPrice;
private int status;
private Item item;
public Item getItem() {
return item;
}
public void setItem(Item item) {
this.item = item;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getOrderID() {
return orderID;
}
public void setOrderID(int orderID) {
this.orderID = orderID;
}
public int getItemId() {
return itemId;
}
public void setItemId(int itemId) {
this.itemId = itemId;
}
public double getTotalPrice() {
return totalPrice;
}
public void setTotalPrice(double totalPrice) {
this.totalPrice = totalPrice;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public OrderDetail() {
}
public OrderDetail(int id, int orderID, int itemId, double totalPrice, int status, Item item) {
this.id = id;
this.orderID = orderID;
this.itemId = itemId;
this.totalPrice = totalPrice;
this.status = status;
this.item = item;
}
@Override
public String toString() {
return "OrderDetail{" +
"id=" + id +
", orderID=" + orderID +
", itemId=" + itemId +
", totalPrice=" + totalPrice +
", status=" + status +
", item=" + item +
'}';
}
}
Item.java
package com.hisoft.pojo;
import java.io.Serializable;
//商品详情--需要实现序列化接口
public class Item implements Serializable {
private int id;
private String itemName;
private double itemPrice;
private String itemDetail;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public double getItemPrice() {
return itemPrice;
}
public void setItemPrice(double itemPrice) {
this.itemPrice = itemPrice;
}
public String getItemDetail() {
return itemDetail;
}
public void setItemDetail(String itemDetail) {
this.itemDetail = itemDetail;
}
public Item() {
}
public Item(int id, String itemName, double itemPrice, String itemDetail) {
this.id = id;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.itemDetail = itemDetail;
}
@Override
public String toString() {
return "Item{" +
"id=" + id +
", itemName='" + itemName + '\'' +
", itemPrice=" + itemPrice +
", itemDetail='" + itemDetail + '\'' +
'}';
}
}
4、dao层（直接与数据库进行交互的层）
UserMapper.java
package com.hisoft.dao;
import com.hisoft.pojo.Order;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserMapper {
//****************************************************************
//高级查询之一对一查询:查询订单，并且查询出下单人的信息
public Order queryOrderByOrderName(@Param("orderNumber") String orderNumber);
//一对多查询:查询订单，查询出下单人信息并且查询出订单详情
public Order queryOrderAndUserDetailsByOrderName(String orderNumber);
//多对多查询:查询订单，查询出下单人信息并且查询出订单详情中的商品数据。
public Order queryOrderAndUserAndDetailsAndItemByOrderName(String orderNumber);
}
5、test文件
UserTset.java
import com.hisoft.dao.UserMapper;
import com.hisoft.pojo.Order;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.apache.ibatis.io.Resources;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class UserTset {
//new 一个userDao1对象
private UserMapper userMapper;
private SqlSession sqlSession;//定义成全局的对象
private InputStream in;
@Before
public void init() throws IOException {
//读取配置文件
String resource = "mybatis-config.xml";
in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = ssfb.build(in);
//生产sqlSession对象
sqlSession = sqlSessionFactory.openSession();
//使用sqlSession创建dao接口的代理对象
userMapper = sqlSession.getMapper(UserMapper.class);
}
//**************************************************
//高级查询之一对一查询
@Test
public void queryOrderByOrderName() {
Order order = userMapper.queryOrderByOrderName("10000234");
System.out.println(order);
}
//一对多
@Test
public void queryOrderAndUserDetailsByOrderName() {
Order order = userMapper.queryOrderAndUserDetailsByOrderName("10000234");
System.out.println(order);
}
//多对多
@Test
public void queryOrderAndUserAndDetailsAndItemByOrderName(){
Order order = userMapper.queryOrderAndUserAndDetailsAndItemByOrderName("10000234");
System.out.println(order);
}
@After
public void destroy() throws IOException {
// 提交事务
sqlSession.commit();//不开启事务是写入不到数据库里去的
//释放资源
sqlSession.close();
in.close();
}
}
5、程序运行结果截图
一对一查询：
一对多查询：
多对多查询：
3115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
