工程目录结构查看:
1.数据库文件yhmis.sql
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.7.16-log : Database - mybatisdb3
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatisdb3` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mybatisdb3`;
/*Table structure for table `tb_door` */
DROP TABLE IF EXISTS `tb_door`;
CREATE TABLE `tb_door` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`tel` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `tb_door` */
insert into `tb_door`(`id`,`name`,`tel`) values (1,'永和大王（北三环西路店）','110-62112313'),(2,'永和大王（北京南站店）','007-62112323'),(3,'北京西','007-62112324');
/*Table structure for table `tb_order` */
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
`id` int(11) NOT NULL,
`door_id` int(11) DEFAULT NULL,
`order_no` varchar(7) DEFAULT NULL,
`order_type` varchar(50) DEFAULT NULL,
`person_num` tinyint(4) DEFAULT NULL,
`cashier` varchar(30) DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`payment_type` varchar(30) DEFAULT NULL,
`price` double(8,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_Reference_2` (`door_id`),
CONSTRAINT `FK_Reference_2` FOREIGN KEY (`door_id`) REFERENCES `tb_door` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `tb_order` */
insert into `tb_order`(`id`,`door_id`,`order_no`,`order_type`,`person_num`,`cashier`,`create_time`,`end_time`,`payment_type`,`price`) values (2,2,'P000001','堂食',4,'王强','2018-04-24 16:05:00','2018-04-24 16:05:00','支付宝',18.00),(10,1,'P000011','堂食',1,'张静','2018-06-15 11:25:11','2018-04-17 07:24:38','微支付',8.00);
/*Table structure for table `tb_order_detail` */
DROP TABLE IF EXISTS `tb_order_detail`;
CREATE TABLE `tb_order_detail` (
`id` int(11) NOT NULL,
`order_id` int(11) DEFAULT NULL,
`num` tinyint(4) DEFAULT NULL,
`item` varchar(30) DEFAULT NULL,
`price` double(8,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `tb_order_detail` */
insert into `tb_order_detail`(`id`,`order_id`,`num`,`item`,`price`) values (1,10,1,'玉米肉松蛋饼2P<br/>1X --玉米肉松蛋饼<br/>1',8.00),(2,10,1,'爆有料三谷饭团套餐<br/>1X --爆有料三谷饭团 1X ',10.00),(3,2,4,'玉米肉松蛋饼2P<br/>1 X --玉米肉松蛋饼<br/>',32.00);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2. 测试类OrderTest.java
package yhmis;
import java.io.IOException;
import java.io.InputStream;
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.swliu.yhmis.pojo.Order;
import com.swliu.yhmis.pojo.OrderMapper;
public class OrderTest {
//成员变量，线程安全的
private SqlSessionFactory factory;
@Before //获取factory
public void init() throws IOException{
InputStream is =
Resources.getResourceAsStream("sqlMapConfig.xml");
factory = new SqlSessionFactoryBuilder().build(is);
}
@Test //查询某个订单
public void get(){
SqlSession session = factory.openSession();
OrderMapper mapper = session.getMapper(OrderMapper.class);
//Order order = mapper.get(2);
Order order = mapper.get(10);
System.out.println(order);
}
}
3.配置事务、数据源、映射文件、全局配置文件 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="jdbc.properties"/>
<!-- 全局配置，驼峰规则，默认不开启，结果值null -->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 简化类，起别名 -->
<typeAliases>
<typeAlias type="com.swliu.yhmis.pojo.Order" alias="Order"/>
<typeAlias type="com.swliu.yhmis.pojo.Door" alias="door"/>
<typeAlias type="com.swliu.yhmis.pojo.OrderDetail" alias="OrderDetail"/>
</typeAliases>
<!-- 配置环境、事务、数据源、映射文件 -->
<environments default="test">
<environment id="test">
<!-- 支持两种类型：JDBC/MANAGE -->
<transactionManager type="JDBC"/>
<!-- 数据源：POOLED/UNPOOLED/JNDI -->
<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>
<!-- 告诉mybatis映射文件存在 -->
<mappers>
<mapper class="com.swliu.yhmis.pojo.OrderMapper"/>
</mappers>
</configuration>jdbc.properties数据源配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/yhmis?characterEncoding=utf8
username=root
password=rootswliu4.配置映射文件的命名空间（java包）: 包的路径.映射文件的名称 OrderMapper.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.swliu.yhmis.pojo.OrderMapper">
<!-- 查询某个订单，包含门店信息，包含订单详情信息 -->
<select id="get" parameterType="int" resultMap="orderRM">
SELECT o.id,d.id as is_door_id,o.door_id,o.order_no,o.order_type,o.person_num,o.cashier,
o.create_time,o.end_time,o.payment_type,o.price,
d.name,d.tel
FROM
( SELECT id,door_id,order_no,order_type,person_num,
cashier,create_time,end_time,payment_type,price
FROM tb_order WHERE id=#{pid} ) o
LEFT JOIN
( SELECT id,NAME,tel FROM tb_door ) d
ON d.id=o.door_id
</select>
<!-- 订单的映射 -->
<resultMap type="Order" id="orderRM">
<!-- 主键 -->
<id property="id" column="id"/>
<!-- 普通属性 -->
<result property="orderNo" column="order_no"/>
<result property="orderType" column="order_type"/>
<result property="personNum" column="person_num"/>
<result property="cashier" column="cashier"/>
<result property="createTime" column="create_time"/>
<result property="endTime" column="end_time"/>
<result property="paymentType" column="payment_type"/>
<result property="price" column="price"/>
<!-- 对一，一个订单所属一个门店
property 关联对象中声明的名称
javaType 关联对象它对应对象类
-->
<association property="door" javaType="Door">
<!-- 主键 -->
<id property="id" column="is_door_id"/>
<!-- 普通属性 -->
<result property="name" column="name"/>
<result property="tel" column="tel"/>
</association>
</resultMap>
</mapper>
对多语法:
<?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.swliu.yhmis.pojo.OrderMapper">
<!-- 订单的映射 autoMapping自动映射和驼峰规则全局设定来配合 -->
<resultMap type="Order" id="orderResultMap" autoMapping="true">
<!-- 主键 -->
<id property="id" column="id"/>
<!-- 对一，一个订单所属一个门店
property 关联对象中声明的名称
javaType 关联对象它对应对象类
select 指的另外查询标签id
column 当前表中对应外键
-->
<association property="door" select="getDoorInfo" column="door_id" javaType="Door" autoMapping="true"/>
<!-- 对多，一个订单有多个订单详情
property pojo中配置的属性名称
ofType 集合元素类型
个人感觉:orderDetails无主表order的外键不适用于懒加载
<collection property="orderDetails" ofType="OrderDetail" autoMapping="true"/>
-->
</resultMap>
<!-- 查询某个订单，包含门店信息，包含订单详情信息 -->
<select id="get" parameterType="int" resultMap="orderResultMap">
select id,door_id,order_no,order_type,person_num,
cashier,create_time,end_time,payment_type,price
from tb_order where id=#{pid}
</select>
<!-- 查询某个订单对应门店信息 -->
<select id="getDoorInfo" parameterType="int" resultType="door">
select * from tb_door where id=#{id}
</select>
</mapper>
对一联查结果:
SELECT o.id,d.id as is_door_id,o.door_id,o.order_no,o.order_type,o.person_num,o.cashier,
o.create_time,o.end_time,o.payment_type,o.price,
d.name,d.tel
FROM
( SELECT id,door_id,order_no,order_type,person_num,cashier,
create_time,end_time,payment_type,price
FROM tb_order WHERE id=10 ) o
LEFT JOIN
( SELECT id,NAME,tel
FROM tb_door ) d
ON o.door_id=d.id;对多联查结果:
SELECT
o.id,o.order_no,o.order_type,o.person_num,o.cashier,o.create_time,o.end_time,o.payment_type,o.price,
o.is_door_id,o.name,o.tel,
od.id AS is_order_detail_id,od.order_id,od.num,od.item,od.price
FROM
(
SELECT
o.id,o.order_no,o.order_type,o.person_num,o.cashier,o.create_time,o.end_time,o.payment_type,o.price,
d.id AS is_door_id,d.name,d.tel
FROM
( SELECT id,door_id,order_no,order_type,person_num,
cashier,create_time,end_time,payment_type,price
FROM tb_order WHERE id=10 ) o
LEFT JOIN
( SELECT id,NAME,tel FROM tb_door ) d
ON d.id=o.door_id
) o
LEFT JOIN
(
SELECT id,order_id,num,item,price
FROM tb_order_detail WHERE order_id=10
) od
ON o.id=od.order_idSELECT
o.id,o.order_no,o.order_type,o.person_num,o.cashier,o.create_time,o.end_time,o.payment_type,o.price,
o.is_door_id,o.name,o.tel,
od.is_order_detail_id,od.order_id,od.num,od.item,od.price
FROM
(
SELECT
o.id,o.order_no,o.order_type,o.person_num,o.cashier,o.create_time,o.end_time,o.payment_type,o.price,
d.id AS is_door_id,d.name,d.tel
FROM
( SELECT id,door_id,order_no,order_type,person_num,
cashier,create_time,end_time,payment_type,price
FROM tb_order WHERE id=10 ) o
LEFT JOIN
( SELECT id,NAME,tel FROM tb_door ) d
ON d.id=o.door_id
) o
LEFT JOIN
(
SELECT id AS is_order_detail_id,order_id,num,item,price
FROM tb_order_detail WHERE order_id=10
) od
ON o.id=od.order_id5.javaBean类BasePojo.java
package com.swliu.yhmis.pojo;
import java.io.Serializable;
//标识是序列化
public class BasePojo implements Serializable{
}
Order.java
package com.swliu.yhmis.pojo;
import java.util.Date;
import java.util.List;
public class Order extends BasePojo{
private static final long serialVersionUID = 1L;
//关联对象配置：
//描述外键，由订单出发去找门店信息，对一
private Door door;
//描述外键，由订单出发去找订单详情信息，对多（集合<> List<OrderDetail>）
private List<OrderDetail> orderDetails;
private Integer id;
//private Integer doorId;//配置对象关联后，实体类外键id用不着
private String orderNo;
private String orderType;
private Integer personNum;
private String cashier;
private Date createTime;
private Date endTime;
private String paymentType;
private Double price;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public String getOrderType() {
return orderType;
}
public void setOrderType(String orderType) {
this.orderType = orderType;
}
public Integer getPersonNum() {
return personNum;
}
public void setPersonNum(Integer personNum) {
this.personNum = personNum;
}
public String getCashier() {
return cashier;
}
public void setCashier(String cashier) {
this.cashier = cashier;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getEndTime() {
return endTime;
}
public void setEndTime(Date endTime) {
this.endTime = endTime;
}
public String getPaymentType() {
return paymentType;
}
public void setPaymentType(String paymentType) {
this.paymentType = paymentType;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public Door getDoor() {
return door;
}
public void setDoor(Door door) {
this.door = door;
}
public List<OrderDetail> getOrderDetails() {
return orderDetails;
}
public void setOrderDetails(List<OrderDetail> orderDetails) {
this.orderDetails = orderDetails;
}
@Override
public String toString() {
return "Order [door=" + door + ", orderDetails=" + orderDetails + ", id=" + id + ", orderNo=" + orderNo
+ ", orderType=" + orderType + ", personNum=" + personNum + ", cashier=" + cashier + ", createTime="
+ createTime + ", endTime=" + endTime + ", paymentType=" + paymentType + ", price=" + price + "]";
}
}
Door.java
package com.swliu.yhmis.pojo;
public class Door extends BasePojo{
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private String tel;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
@Override
public String toString() {
return "Door [id=" + id + ", name=" + name + ", tel=" + tel + "]";
}
}
OrderDetail.java
package com.swliu.yhmis.pojo;
public class OrderDetail extends BasePojo{
private static final long serialVersionUID = 1L;
private Integer id;
//private Integer orderId;//配置对象关联后，实体类外键id用不着
private Integer num;
private String item;
private Double price;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
public String getItem() {
return item;
}
public void setItem(String item) {
this.item = item;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Override
public String toString() {
return "OrderDetail [id=" + id + ", num=" + num + ", item=" + item + ", price=" + price
+ "]";
}
}
6.log4j.properties日志打印
log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG7.pom.xml
<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.swliu</groupId>
<artifactId>mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<argLine>-Dfile.encoding=UTF-8</argLine>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.40</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
<!--
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.16</version>
<configuration>
<forkMode>once</forkMode>
<argLine>-Dfile.encoding=UTF-8</argLine>
</configuration>
</plugin>
</plugins>
</build>
-->
</project>
8.接口 OrderMapper.java
package com.swliu.yhmis.pojo;
public interface OrderMapper {
public Order get(Integer orderId);
}打印信息: 略
版权声明：本文为qq_31338839原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。