一.MyBatis实现五表联合查询
上一个笔记中实现了五表的设计与数据添加,所以通过mybatis本次实现五表联合查询的操作.
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>nz.study</groupId>
<artifactId>Days22Mybatis2</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.4</version>
</dependency>
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.1.1</version>
</dependency>
</dependencies>
</project>
2.配置mybatis.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节点
里面有配置信息 分别是环境和映射
其中环境里有datasource，里面有我们熟悉的连接数据库的四个字符串
-->
<configuration>
<properties resource="db.properties" />
<!--
给当前mybatis项目添加日志功能，该STDOUT_LOGGING值的好处是不用添加第三方jar包就可以有日志的输出
-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--起别名,方便后面代码的书写 -->
<typeAliases>
<package name="nz.study.bean"/>
</typeAliases>
<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="${name}"/>
<property name="password" value="${pass}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="OrderMapper.xml"/>
<mapper resource="UserMapper.xml"/>
<mapper resource="DetailMapper.xml"/>
<mapper resource="ProductMapper.xml"/>
<mapper resource="TypeMapper.xml"/>
</mappers>
</configuration>
3.数据库配置文件db.properties
driver=org.mariadb.jdbc.Driver
url=jdbc:mariadb://localhost:3306/mall
name=root
pass=*******(自己数据库的密码)
4.设计bean类
(1)Users.java
package nz.study.bean;
public class Users {
private int uid;
private String name;
private String pass;
private String phone;
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPass() {
return pass;
}
public void setPass(String pass) {
this.pass = pass;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("Users{");
sb.append("uid=").append(uid);
sb.append(", name='").append(name).append('\'');
sb.append(", pass='").append(pass).append('\'');
sb.append(", phone='").append(phone).append('\'');
sb.append('}');
return sb.toString();
}
}
(2)Types.java
package nz.study.bean;
public class Types {
private String tid;
private String name;
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("Types{");
sb.append("tid='").append(tid).append('\'');
sb.append(", name='").append(name).append('\'');
sb.append('}');
return sb.toString();
}
public String getTid() {
return tid;
}
public void setTid(String tid) {
this.tid = tid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
(3)Products.java
package nz.study.bean;
public class Products {
private String pid;
private String name;
private String img;
private double price;
private Types t;
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("Products{");
sb.append("pid='").append(pid).append('\'');
sb.append(", name='").append(name).append('\'');
sb.append(", img='").append(img).append('\'');
sb.append(", price=").append(price);
sb.append(", t=").append(t);
sb.append('}');
return sb.toString();
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getImg() {
return img;
}
public void setImg(String img) {
this.img = img;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Types getT() {
return t;
}
public void setT(Types t) {
this.t = t;
}
}
(4)Orders.java
package nz.study.bean;
import java.util.List;
public class Orders {
private String oid;
private double price;
private String addr;
private String payType;
private Users u;
private List<Details> details;
public String getOid() {
return oid;
}
public void setOid(String oid) {
this.oid = oid;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public String getPayType() {
return payType;
}
public void setPayType(String payType) {
this.payType = payType;
}
public Users getU() {
return u;
}
public void setU(Users u) {
this.u = u;
}
public List<Details> getDetails() {
return details;
}
public void setDetails(List<Details> details) {
this.details = details;
}
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("Orders{");
sb.append("oid='").append(oid).append('\'');
sb.append(", price=").append(price);
sb.append(", addr='").append(addr).append('\'');
sb.append(", payType='").append(payType).append('\'');
sb.append(", u=").append(u);
sb.append(", details=").append(details);
sb.append('}');
return sb.toString();
}
}
(5)Details.java
package nz.study.bean;
public class Details {
private String did;
private int count;
private Products pro;
public String getDid() {
return did;
}
public void setDid(String did) {
this.did = did;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public Products getPro() {
return pro;
}
public void setPro(Products pro) {
this.pro = pro;
}
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("Details{");
sb.append("did='").append(did).append('\'');
sb.append(", count=").append(count);
sb.append(", pro=").append(pro);
sb.append('}');
return sb.toString();
}
}
5.设置映射文件
(1)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,
每个方法对应自己的sql语句,每个sql语句对应有一个id
整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="nz.study.bean.UserMapper">
<select id="getUserByUid" resultType="Users">
select * from users where uid = #{uid}
</select>
</mapper>
(2)TypeMapper.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="nz.study.bean.TypeMapper">
<select id="getTypeByTid" resultType="Types">
select *form types where tid = #{tid}
</select>
</mapper>
(3)ProductMapper.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="nz.study.bean.ProductMapper">
<select id="getProductByPid" resultMap="productMap">
select * from proceducts where pid = #{pid}
</select>
<resultMap id="productMap" type="Product">
<id column="pid" property="pid"></id>
<association property="t" column="tid" select="nz.study.bean.TypeMapper.getTypeBTid"/>
</resultMap>
</mapper>
(4)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="nz.study.bean.OrderMapper">
<select id="getOrderByOid" resultMap="orderMap">
select * from orders where oid = #{oid}
</select>
<resultMap id="orderMap" type="Orders">
<!--id代表主键,分别设置列(数据库字段)和属性的对应关系-->
<id property="oid" column="oid"></id>
<!--
result代表普通字段的映射,分别指定列与属性的对应
如果字段和属性名一致,可以省略
例:属性名叫做payType，字段名pay_type;
<result column="pay_type" property="payType" />
-->
<result column="price" property="price"/>
<result column="addr" property="addr"/>
<result column="payType" property="payType"/>
<!--
association关联,只要是"对一"的关系都可以使用association,代表关联
property代表Orders类中的属性u
column代表Orders表中的uid字段
select代表要使用该查询完成两表的联合查询得出user对象
-->
<association property="u" column="uid" select="nz.study.bean.UserMapper.getUserByUid"/>
<collection property="details" column="oid" select="nz.study.bean.DetailMapper.getDetailsByOid" ofType="detail"/>
</resultMap>
</mapper>
(5)DetailMapper.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="nz.study.bean.DetailMapper">
<select id="getDetailsBtyOid" resultMap="detailMap">
select * from details where oid = #{oid}
</select>
<resultMap id="detailMap" type="detail">
<id column="did" property="did"/>
<result property="count" column="count"/>
<association property="pro" column="pid" select="nz.study.bean.ProductMapper.getProductByPid"/>
</resultMap>
</mapper>
6.TestOrder
package nz.study.test;
import nz.study.bean.Orders;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
public class TestOrders {
private SqlSessionFactory sf = null;
private SqlSession session = null;
@Before
public void setUp(){
try{
sf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
session = sf.openSession();
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void tearDown(){
if(session != null){
session.close();
session = null;
}
}
@Test
public void testGetOrderByOid(){
Orders orders = session.selectOne("nz.study.bean.OrderMapper.getOrderByOid","aa385eb762d311ea811b54ab3aaa1b48");
System.out.println(orders);
}
}
7.运行结果
D:\develop\java\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:D:\IDEA\IntelliJ IDEA 2019.3.1\lib\idea_rt.jar=50699:D:\IDEA\IntelliJ IDEA 2019.3.1\bin" -Dfile.encoding=UTF-8 -classpath "D:\IDEA\IntelliJ IDEA 2019.3.1\lib\idea_rt.jar;D:\IDEA\IntelliJ IDEA 2019.3.1\plugins\junit\lib\junit5-rt.jar;D:\IDEA\IntelliJ IDEA 2019.3.1\plugins\junit\lib\junit-rt.jar;D:\develop\java\jre\lib\charsets.jar;D:\develop\java\jre\lib\deploy.jar;D:\develop\java\jre\lib\ext\access-bridge-64.jar;D:\develop\java\jre\lib\ext\cldrdata.jar;D:\develop\java\jre\lib\ext\dnsns.jar;D:\develop\java\jre\lib\ext\jaccess.jar;D:\develop\java\jre\lib\ext\jfxrt.jar;D:\develop\java\jre\lib\ext\localedata.jar;D:\develop\java\jre\lib\ext\nashorn.jar;D:\develop\java\jre\lib\ext\sunec.jar;D:\develop\java\jre\lib\ext\sunjce_provider.jar;D:\develop\java\jre\lib\ext\sunmscapi.jar;D:\develop\java\jre\lib\ext\sunpkcs11.jar;D:\develop\java\jre\lib\ext\zipfs.jar;D:\develop\java\jre\lib\javaws.jar;D:\develop\java\jre\lib\jce.jar;D:\develop\java\jre\lib\jfr.jar;D:\develop\java\jre\lib\jfxswt.jar;D:\develop\java\jre\lib\jsse.jar;D:\develop\java\jre\lib\management-agent.jar;D:\develop\java\jre\lib\plugin.jar;D:\develop\java\jre\lib\resources.jar;D:\develop\java\jre\lib\rt.jar;D:\CODE\Days22Mybatis2\target\test-classes;D:\CODE\Days22Mybatis2\target\classes;C:\Users\汪翔\.m2\repository\junit\junit\4.12\junit-4.12.jar;C:\Users\汪翔\.m2\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;C:\Users\汪翔\.m2\repository\mysql\mysql-connector-java\5.1.44\mysql-connector-java-5.1.44.jar;C:\Users\汪翔\.m2\repository\org\mybatis\mybatis\3.4.4\mybatis-3.4.4.jar;C:\Users\汪翔\.m2\repository\org\mariadb\jdbc\mariadb-java-client\2.1.1\mariadb-java-client-2.1.1.jar" com.intellij.rt.junit.JUnitStarter -ideVersion5 -junit4 nz.study.test.TestOrders,testGetOrderByOid
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1399499405.
Setting autocommit to false on JDBC Connection [org.mariadb.jdbc.MariaDbConnection@536aaa8d]
==> Preparing: select * from orders where oid = ?
==> Parameters: aa385eb762d311ea811b54ab3aaa1b48(String)
<== Columns: oid, price, addr, payType, uid
<== Row: aa385eb762d311ea811b54ab3aaa1b48, 30998.0, beijingxisanqi, zhibubao, 1
====> Preparing: select * from users where uid = ?
====> Parameters: 1(Integer)
<==== Columns: uid, name, pass, phone
<==== Row: 1, wukong, 888888, 13333333333
<==== Total: 1
====> Preparing: select * from details where oid = ?
====> Parameters: aa385eb762d311ea811b54ab3aaa1b48(String)
<==== Columns: did, count, oid, pid
<==== Row: d410e90562d411ea811b54ab3aaa1b48, 2, aa385eb762d311ea811b54ab3aaa1b48, 7a29313462d311ea811b54ab3aaa1b48
======> Preparing: select * from products where pid = ?
======> Parameters: 7a29313462d311ea811b54ab3aaa1b48(String)
<====== Columns: pid, name, img, price, tid
<====== Row: 7a29313462d311ea811b54ab3aaa1b48, mac pro, mac.jpg, 21999.0, b574b92962d211ea811b54ab3aaa1b48
========> Preparing: select * from types where tid = ?
========> Parameters: b574b92962d211ea811b54ab3aaa1b48(String)
<======== Columns: tid, name
<======== Row: b574b92962d211ea811b54ab3aaa1b48, digit
<======== Total: 1
<====== Total: 1
<==== Total: 1
<== Total: 1
Orders{oid='aa385eb762d311ea811b54ab3aaa1b48', price=30998.0, addr='beijingxisanqi', payType='zhibubao', u=Users{uid=1, name='wukong', pass='888888', phone='13333333333'}, details=[Details{did='d410e90562d411ea811b54ab3aaa1b48', count=2, pro=Products{pid='7a29313462d311ea811b54ab3aaa1b48', name='mac pro', img='mac.jpg', price=21999.0, t=Types{tid='b574b92962d211ea811b54ab3aaa1b48', name='digit'}}}]}
Resetting autocommit to true on JDBC Connection [org.mariadb.jdbc.MariaDbConnection@536aaa8d]
Closing JDBC Connection [org.mariadb.jdbc.MariaDbConnection@536aaa8d]
Returned connection 1399499405 to pool.
Process finished with exit code 0
今天是我在千峰线上学习的第23天,加油!!!
