来千锋学习的第25天
Mybatis实现订单案例的五表联合操作
上一个笔记中已经完成了五表的设计,本案例使用mybatis来完成相应的查询功能
pom文件添加junit,mysql, mybatis(3.4.4)
设置mybatis的配置文件
/p>
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
db.properties数据库的配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mall
user=root
pass=123456
设计pojo类
User.java
package com.qfedu.pojo;
public class User {
private int uid;
private String name;
private String pass;
private String phone;
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", name='" + name + '\'' +
", pass='" + pass + '\'' +
", phone='" + 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;
}
}
Type.java
package com.qfedu.pojo;
public class Types {
private String tid;
private String name;
@Override
public String toString() {
return "Types{" +
"tid=" + tid +
", name='" + name + '\'' +
'}';
}
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;
}
}
Product.java
package com.qfedu.pojo;
public class Product {
private String pid;
private String name;
private String img;
private double price;
private Types t;
@Override
public String toString() {
return "Product{" +
"pid='" + pid + '\'' +
", name='" + name + '\'' +
", img='" + img + '\'' +
", price=" + price +
", t=" + t +
'}';
}
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;
}
}
Detail.java
package com.qfedu.pojo;
public class Detail {
private String did;
private int count;
private Product pro;
@Override
public String toString() {
return "Detail{" +
"did='" + did + '\'' +
", count=" + count +
", pro=" + 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 Product getPro() {
return pro;
}
public void setPro(Product pro) {
this.pro = pro;
}
}
Order.java
package com.qfedu.pojo;
import java.util.List;
public class Order {
private String oid;
private double price;
private String addr;
private String payType;
private User u;
private List details;
@Override
public String toString() {
return "Order{" +
"oid='" + oid + '\'' +
", price=" + price +
", addr='" + addr + '\'' +
", payType='" + payType + '\'' +
", u=" + u +
", 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 User getU() {
return u;
}
public void setU(User u) {
this.u = u;
}
public List getDetails() {
return details;
}
public void setDetails(List details) {
this.details = details;
}
}
设置映射文件mapper
OrderMapper.xml
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from orders where oid = #{oid}
UserMapper.xml
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from users where uid = #{uid}
DetailMapper.xml
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from details where oid = #{oid}
ProductMapper.xml
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from products where pid = #{uid}
TypeMapper.xml
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from types where tid = #{tid}
TestOrder.java
package com.qfedu.test;
import com.qfedu.pojo.Order;
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 testSession(){
// System.out.println(session);
// }
@Test
public void testGetOrderByOid(){
Order order = session.selectOne("com.qfedu.pojo.OrderMapper.getOrderByOid", "d44e970b629d11eaad320242ac110003");
System.out.println(order);
}
}
运行结果
/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/bin/java -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:/Applications/IntelliJ IDEA.app/Contents/lib/idea_rt.jar=50351:/Applications/IntelliJ IDEA.app/Contents/bin" -Dfile.encoding=UTF-8 -classpath "/Applications/IntelliJ IDEA.app/Contents/lib/idea_rt.jar:/Applications/IntelliJ IDEA.app/Contents/plugins/junit/lib/junit-rt.jar:/Applications/IntelliJ IDEA.app/Contents/plugins/junit/lib/junit5-rt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/charsets.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/deploy.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/cldrdata.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/dnsns.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/jaccess.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/jfxrt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/localedata.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/nashorn.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/sunec.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/sunjce_provider.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/sunpkcs11.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/ext/zipfs.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/javaws.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/jce.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/jfr.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/jfxswt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/jsse.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/management-agent.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/plugin.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/resources.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/jre/lib/rt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/lib/ant-javafx.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/lib/dt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/lib/javafx-mx.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/lib/jconsole.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/lib/packager.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/lib/sa-jdi.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_201.jdk/Contents/Home/lib/tools.jar:/Users/james/Documents/NZ1903/lesson/Days22Mybatis2/target/test-classes:/Users/james/Documents/NZ1903/lesson/Days22Mybatis2/target/classes:/Users/james/Documents/doc/repository/junit/junit/4.12/junit-4.12.jar:/Users/james/Documents/doc/repository/org/hamcrest/hamcrest-core/1.3/hamcrest-core-1.3.jar:/Users/james/Documents/doc/repository/mysql/mysql-connector-java/5.1.44/mysql-connector-java-5.1.44.jar:/Users/james/Documents/doc/repository/org/mybatis/mybatis/3.4.4/mybatis-3.4.4.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 com.qfedu.test.TestOrders,testGetOrderByOid
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Opening JDBC Connection
Wed Mar 11 11:34:57 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@79efed2d]
==> Preparing: select * from orders where oid = ?
==> Parameters: d44e970b629d11eaad320242ac110003(String)
<== Columns: oid, price, addr, payType, uid
<== Row: d44e970b629d11eaad320242ac110003, 30998, 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: d44e970b629d11eaad320242ac110003(String)
<==== Columns: did, count, pid, oid
<==== Row: 35984c20629e11eaad320242ac110003, 2, 321607c2629b11eaad320242ac110003, d44e970b629d11eaad320242ac110003
======> Preparing: select * from products where pid = ?
======> Parameters: 321607c2629b11eaad320242ac110003(String)
<====== Columns: pid, name, img, price, tid
<====== Row: 321607c2629b11eaad320242ac110003, iphone x, iphone.jpg, 9999, dd6501cb628111eaad320242ac110003
========> Preparing: select * from types where tid = ?
========> Parameters: dd6501cb628111eaad320242ac110003(String)
<======== Columns: tid, name
<======== Row: dd6501cb628111eaad320242ac110003, digit
<======== Total: 1
<====== Total: 1
<==== Row: 569250ff629e11eaad320242ac110003, 1, ba23b88a629b11eaad320242ac110003, d44e970b629d11eaad320242ac110003
======> Preparing: select * from products where pid = ?
======> Parameters: ba23b88a629b11eaad320242ac110003(String)
<====== Columns: pid, name, img, price, tid
<====== Row: ba23b88a629b11eaad320242ac110003, yagao, heiren.jpg, 99, dd64fd48628111eaad320242ac110003
========> Preparing: select * from types where tid = ?
========> Parameters: dd64fd48628111eaad320242ac110003(String)
<======== Columns: tid, name
<======== Row: dd64fd48628111eaad320242ac110003, house
<======== Total: 1
<====== Total: 1
<==== Row: 56f00543629e11eaad320242ac110003, 1, f07de571628211eaad320242ac110003, d44e970b629d11eaad320242ac110003
======> Preparing: select * from products where pid = ?
======> Parameters: f07de571628211eaad320242ac110003(String)
<====== Columns: pid, name, img, price, tid
<====== Row: f07de571628211eaad320242ac110003, mac pro, mac.jpg, 21999, dd6501cb628111eaad320242ac110003
<====== Total: 1
<==== Total: 3
<== Total: 1
Order{oid='d44e970b629d11eaad320242ac110003', price=30998.0, addr='beijingxisanqi', payType='zhibubao', u=User{uid=1, name='wukong', pass='888888', phone='13333333333'}, details=[Detail{did='35984c20629e11eaad320242ac110003', count=2, pro=Product{pid='321607c2629b11eaad320242ac110003', name='iphone x', img='iphone.jpg', price=9999.0, t=Types{tid=dd6501cb628111eaad320242ac110003, name='digit'}}}, Detail{did='569250ff629e11eaad320242ac110003', count=1, pro=Product{pid='ba23b88a629b11eaad320242ac110003', name='yagao', img='heiren.jpg', price=99.0, t=Types{tid=dd64fd48628111eaad320242ac110003, name='house'}}}, Detail{did='56f00543629e11eaad320242ac110003', count=1, pro=Product{pid='f07de571628211eaad320242ac110003', name='mac pro', img='mac.jpg', price=21999.0, t=Types{tid=dd6501cb628111eaad320242ac110003, name='digit'}}}]}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@79efed2d]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@79efed2d]
Process finished with exit code 0
最怕自己平庸碌碌还安慰自己平凡可贵。加油!