jpi多表联查_使用Mybatis进行多表联查操作

(1)增加一个测试数据库shop_order，sql语句如下：

CREATE DATABASE`shop_order`;USE`shop_order`;CREATE TABLE`t_user` (

`id`INT(11) NOT NULLAUTO_INCREMENT,

`username`VARCHAR(20) DEFAULT NULL,

`password`VARCHAR(50) DEFAULT NULL,

`sex`VARCHAR(2) DEFAULT NULL,

`brithday` DATEDEFAULT NULL,

`address`VARCHAR(200) DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=INNODB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;CREATE TABLE`t_product` (

`id`INT(11) NOT NULLAUTO_INCREMENT,

`name`VARCHAR(100) DEFAULT NULL,

`price`FLOAT DEFAULT NULL,

`description`TEXT,PRIMARY KEY(`id`)

) ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;CREATE TABLE`t_order` (

`id`INT(11) NOT NULLAUTO_INCREMENT,

`userId`INT(11) DEFAULT NULL,

`createTime`DATETIME DEFAULT NULL,

`state`VARCHAR(20) DEFAULT NULL,PRIMARY KEY(`id`),KEY`fk_userId` (`userId`),CONSTRAINT `fk_userId` FOREIGN KEY (`userId`) REFERENCES`t_user` (`id`)

) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;CREATE TABLE`t_orderdetail` (

`id`INT(11) NOT NULLAUTO_INCREMENT,

`productId`INT(11) DEFAULT NULL,

`orderId`INT(11) DEFAULT NULL,

`num`INT(11) DEFAULT NULL,

`price`FLOAT DEFAULT NULL,PRIMARY KEY(`id`),KEY`fk_productId` (`productId`),KEY`fk_orderId` (`orderId`),CONSTRAINT `fk_orderId` FOREIGN KEY (`orderId`) REFERENCES`t_order` (`id`),CONSTRAINT `fk_productId` FOREIGN KEY (`productId`) REFERENCES`t_product` (`id`)

) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;INSERT INTO `t_user`(`id`,`username`,`password`,`sex`,`brithday`,`address`) VALUES (16,'小黄','2222','男','2014-01-01','中国'),(17,'陌拜','1100','男','2014-03-03','深圳'),(18,'本拉登','9999','男','2003-09-09','印度'),(19,'奥巴马小号','1399','男','2005-01-09','美国');INSERT INTO `t_product`(`id`,`name`,`price`,`description`) VALUES (6,'飞机',20000000,'预售，提货时间暂定'),(7,'坦克',300000000,'预售，提货时间暂定'),(8,'大炮',500000,'预售，提货时间暂定'),(9,'航母',900000000,'预售，提货时间暂定');INSERT INTO `t_order`(`id`,`userId`,`createTime`,`state`) VALUES (1,18,'2015-07-04 00:00:00','已付'),(2,19,'2017-01-02 00:00:00','已付'),(3,16,'2017-12-13 10:07:21','待付'),(4,18,'2017-12-05 14:44:04','报废');INSERT INTO `t_orderdetail`(`id`,`productId`,`orderId`,`num`,`price`) VALUES (1,8,1,6,3000000),(2,9,2,1,900000000),(3,6,NULL,NULL,NULL);

(2)建立程序的结构，结构如下

(3)建立实体类，与数据库中的表对应

Order.java代码如下：

packageentity;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;public classOrder {private intid;privateDate userId;privateString createTime;privateString state;

//注意这里报错是正常的，因为这里之后引用了其他的实体类，它们还没被定义，把所有的实体类建立好了以后，保存一下就可以了private User user; //一个订单对应一个用户，所以在订单中放置一个User的对象//一个订单可以有多件商品，这里对应多个商品详情，所以放置一个商品详情集合

private List orderDetails = new ArrayList();publicOrder() {super();

}public Order(intid, Date userId, String createTime, String state) {super();this.id =id;this.userId =userId;this.createTime =createTime;this.state =state;

}public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}publicDate getUserId() {returnuserId;

}public voidsetUserId(Date userId) {this.userId =userId;

}publicString getCreateTime() {returncreateTime;

}public voidsetCreateTime(String createTime) {this.createTime =createTime;

}publicString getState() {returnstate;

}public voidsetState(String state) {this.state =state;

}publicUser getUser() {returnuser;

}public voidsetUser(User user) {this.user =user;

}public ListgetOrderDetails() {returnorderDetails;

}public void setOrderDetails(ListorderDetails) {this.orderDetails =orderDetails;

}

}

OrderDetail.java类的代码如下：

packageentity;public classOrderDetail {private intid;private intproductId;private intorderId;private doubleprice;private intnum;private Order order; //一个订单详情，对应某一个订单，所有在这里添加一个订单对象

private Product product; //一个订单详情里面存放一种商品，对应一个商品详情

publicOrderDetail() {super();

}public OrderDetail(int id, int productId, int orderId, double price, intnum) {super();this.id =id;this.productId =productId;this.orderId =orderId;this.price =price;this.num =num;

}public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}public doublegetPrice() {returnprice;

}public void setPrice(doubleprice) {this.price =price;

}public intgetNum() {returnnum;

}public void setNum(intnum) {this.num =num;

}publicOrder getOrder() {returnorder;

}public voidsetOrder(Order order) {this.order =order;

}public intgetProductId() {returnproductId;

}public void setProductId(intproductId) {this.productId =productId;

}public intgetOrderId() {returnorderId;

}public void setOrderId(intorderId) {this.orderId =orderId;

}publicProduct getProduct() {returnproduct;

}public voidsetProduct(Product product) {this.product =product;

}

}

Product.java类代码

packageentity;public classProduct {private intid;privateString name;private floatprice;privateString description;publicProduct() {super();

}public Product(int id, String name, floatprice, String description) {super();this.id =id;this.name =name;this.price =price;this.description =description;

}public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}public floatgetPrice() {returnprice;

}public void setPrice(floatprice) {this.price =price;

}publicString getDescription() {returndescription;

}public voidsetDescription(String description) {this.description =description;

}

}

User.java类的代码如下：

packageentity;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;public classUser {private intid;privateString username;privateString password;privateString sex;privateDate brithday;privateString address;private List orders = new ArrayList();publicUser() {super();

}public User(intid, String username, String password, String sex,

Date brithday, String address) {super();this.id =id;this.username =username;this.password =password;this.sex =sex;this.brithday =brithday;this.address =address;

}public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}publicString getUsername() {returnusername;

}public voidsetUsername(String username) {this.username =username;

}publicString getPassword() {returnpassword;

}public voidsetPassword(String password) {this.password =password;

}publicString getSex() {returnsex;

}public voidsetSex(String sex) {this.sex =sex;

}publicDate getBrithday() {returnbrithday;

}public voidsetBrithday(Date brithday) {this.brithday =brithday;

}publicString getAddress() {returnaddress;

}public voidsetAddress(String address) {this.address =address;

}public ListgetOrders() {returnorders;

}public void setOrders(Listorders) {this.orders =orders;

}

}

(4)编写mybatis-config.xml文件

/p>

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

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

(5)编写SqlSessionUtil.java拿到数据库的操作对象SqlSession，代码如下

packagecommon;importjava.io.IOException;importjava.io.InputStream;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;public classSqlSessionUtil {public staticSqlSession getSession(){

InputStream input= null;try{

input= Resources.getResourceAsStream("mybatis-config.xml");

}catch(IOException e) {

e.printStackTrace();

}

SqlSessionFactoryBuilder builder=newSqlSessionFactoryBuilder();

SqlSessionFactory sqlSessionFactory=builder.build(input);

SqlSession session=sqlSessionFactory.openSession();returnsession;

}

}

(6)编写dao里面的接口，这边学习就只写了两个，里面的方法也很简单

OrderDao.java

packagedao;importjava.util.List;importentity.Order;public interfaceOrderDao {/*** 查询所有的订单，并取出该订单的用户信息

*@return

*/ListqueryAllOrder();}

UserDao.java

packagedao;importjava.util.List;importentity.User;public interfaceUserDao {/*** 查询所有用户，并显示每个用户的订单

*@return

*/ListqueryAllUser();

}

(7)编写mapper里面的xml文件，里面存放数据库语句

OrderMapper.xml

/p>

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

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

select *,od.id as o_id,us.id as u_id from t_order as od inner join t_user as us on od.userId=us.id

UserMapper.xml

/p>

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

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

SELECT *,o.id AS o_id FROM t_user AS u INNER JOIN t_order AS o ON u.id=o.userId

(8)在mybatis-config.xml中的标签里面标签下面添加如下映射

(9)编写方法测试

test.java

packagetext;importjava.util.List;importorg.apache.ibatis.session.SqlSession;importcommon.SqlSessionUtil;importdao.OrderDao;importdao.UserDao;importentity.Order;importentity.User;public classtest {public static voidmain(String[] args) {

SqlSession session= SqlSessionUtil.getSession();//获取数据库操作对象SqlSession

UserDao userDao = session.getMapper(dao.UserDao.class);

List users =userDao.queryAllUser();for(User user : users) {

System.out.println("用户："+user.getUsername()+"的订单");for(Order order : user.getOrders()) {

System.out.println("订单编号："+order.getId());

System.out.println("状态:"+order.getState());

}

System.out.println();

}

System.out.println("///");

System.out.println();

OrderDao orderdao= session.getMapper(dao.OrderDao.class);

List orders =orderdao.queryAllOrder();for(Order order : orders) {

System.out.println("订单编号："+order.getId());

System.out.println("购买者："+order.getUser().getUsername());

System.out.println("订单状态："+order.getState());

System.out.println();

}

session.close();　　//回收session,刷新缓冲区；

}

}