mybatis mysql 多表 联合查询_基于MyBatis的多表联合查询表数据

1.下面是我的idea的目录结构:

mybatis01.jpg

2.下面是com.it.pojo的代码:

package com.it.pojo;

public class User {

private int uid;

private String name;

private String pass;

private String phone;

//setter和getter,toString省略

}

public class Types {

private String tid;

private String name;

//setter和getter,toString省略

}

public class Product {

private String pid;

private String name;

private String img;

private double price;

private Types t;

//setter和getter,toString省略

}

public class Detail {

private String did;

private int count;

private Product p;

//setter和getter,toString省略

}

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;

//setter和getter,toString省略

}

3.下面是mybatis的核心文件配置

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

4.下面是各个mapper文件的配置

下面是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};

下面是TypesMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from types where tid=#{tid};

下面是ProductMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from products where pid=#{pid};

下面是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};

下面是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};

5.下面是测试代码以及测试结果展示

import com.it.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 testGetOrderByOid(){

Order order = session.selectOne("com.it.pojo.OrderMapper.getOrderById", "7891834e633011eab732005056c00001");

System.out.println(order);

}

}

下面是测试结果展示:

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 1684890795.

Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@646d64ab]

==> Preparing: select * from orders where oid=?;

==> Parameters: 7891834e633011eab732005056c00001(String)

<== Columns: oid, price, addr, payType, uid

<== Row: 7891834e633011eab732005056c00001, 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: 7891834e633011eab732005056c00001(String)

<==== Columns: did, count, pid, oid

<==== Row: 61f4c1d3633211eab732005056c00001, 1, b8591f77633111eab732005056c00001, 7891834e633011eab732005056c00001

======> Preparing: select * from products where pid=?;

======> Parameters: b8591f77633111eab732005056c00001(String)

<====== Columns: pid, name, img, price, tid

<====== Row: b8591f77633111eab732005056c00001, mac.pro, mac.jpg, 21999, 007ca8fb632f11eab732005056c00001

========> Preparing: select * from types where tid=?;

========> Parameters: 007ca8fb632f11eab732005056c00001(String)

<======== Columns: tid, name

<======== Row: 007ca8fb632f11eab732005056c00001, digit

<======== Total: 1

<====== Total: 1

<==== Row: 61f4c3b1633211eab732005056c00001, 1, b8592172633111eab732005056c00001, 7891834e633011eab732005056c00001

======> Parameters: b8592172633111eab732005056c00001(String)

<====== Columns: pid, name, img, price, tid

<====== Row: b8592172633111eab732005056c00001, iphone, iphone.jpg, 9999, 007ca8fb632f11eab732005056c00001

<====== Total: 1

<==== Row: 61f4c400633211eab732005056c00001, 1, b85921bc633111eab732005056c00001, 7891834e633011eab732005056c00001

======> Parameters: b85921bc633111eab732005056c00001(String)

<====== Columns: pid, name, img, price, tid

<====== Row: b85921bc633111eab732005056c00001, yagao, yagao.jpg, 50, 007ca7cd632f11eab732005056c00001

========> Parameters: 007ca7cd632f11eab732005056c00001(String)

<======== Columns: tid, name

<======== Row: 007ca7cd632f11eab732005056c00001, house

<======== Total: 1

<====== Total: 1

<==== Total: 3

<== Total: 1

Order{oid='7891834e633011eab732005056c00001', price=30998.0, addr='beijingxisanqi', payType='zhibubao', u=user{uid=1, name='wukong', pass='888888', phone='13333333333'}, Details=[Detail{did='61f4c1d3633211eab732005056c00001', count=1, p=Product{pid='b8591f77633111eab732005056c00001', name='mac.pro', img='mac.jpg', price=21999.0, t=Types{tid='007ca8fb632f11eab732005056c00001', name='digit'}}}, Detail{did='61f4c3b1633211eab732005056c00001', count=1, p=Product{pid='b8592172633111eab732005056c00001', name='iphone', img='iphone.jpg', price=9999.0, t=Types{tid='007ca8fb632f11eab732005056c00001', name='digit'}}}, Detail{did='61f4c400633211eab732005056c00001', count=1, p=Product{pid='b85921bc633111eab732005056c00001', name='yagao', img='yagao.jpg', price=50.0, t=Types{tid='007ca7cd632f11eab732005056c00001', name='house'}}}]}

Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@646d64ab]

Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@646d64ab]

Returned connection 1684890795 to pool.