Shulou(Shulou.com)06/02 Report--

Editor to share with you how to use the query function of MyBatis multi-table operation. I hope you will get something after reading this article. Let's discuss it together.

One-to-one query

The relationship between the user table and the order table is that one user has multiple orders, and one order belongs to only one user.

Demand for one-to-one query: query an order and at the same time find out who the order belongs to

When you only query the order table, you also need to query the user table, so you need to check out all the data to encapsulate SELECT *, o.id oid FROM orders Omega user u WHERE o.uid=u.id.

Create Order and User entities

Order

Public class Order {private int id; private Date ordertime; private double total; / / indicates which user private User user the current order belongs to

User

Public class User {private int id; private String username; private String password; private Date birthday

Create an OrderMapper interface

Public interface UserMapper {/ / query all methods public List findAll ();}

Configure OrderMapper.xml

SELECT *, o.id oid FROM orders omega user u WHERE o.uid=u.id

SqlMapConfig.xml

In an one-to-one configuration, a user is created in the order entity, so the property properties are written in user.**, but you can also use association here

SELECT *, o.id oid FROM orders o mai user u WHERE o.uid=u.id one-to-many query model

The relationship between the user table and the order table is that one user has multiple orders, and one belongs to only one user.

One-to-many query requirements: query a user and at the same time find out the orders that the user has

Package com.zg.domain;import java.util.Date;import java.util.List;public class User {private int id; private String username; private String password; private Date birthday; / / describes which orders exist in the current user private List orderList; public List getOrderList () {return orderList;} public void setOrderList (List orderList) {this.orderList = orderList } @ Override public String toString () {return "User {" + "id=" + id + ", username='" + username +'\'+ ", password='" + password +'\'+ ", birthday=" + birthday + ", orderList=" + orderList +'}' } public int getId () {return id;} public void setId (int id) {this.id = id;} public String getUsername () {return username;} public void setUsername (String username) {this.username = username;} public String getPassword () {return password;} public void setPassword (String password) {this.password = password } public Date getBirthday () {return birthday;} public void setBirthday (Date birthday) {this.birthday = birthday;}}

Modify User solid

Package com.zg.domain;import java.util.Date;import java.util.List;public class User {private int id; private String username; private String password; private Date birthday; / / describes which orders exist in the current user private List orderList; public List getOrderList () {return orderList;} public void setOrderList (List orderList) {this.orderList = orderList } @ Override public String toString () {return "User {" + "id=" + id + ", username='" + username +'\'+ ", password='" + password +'\'+ ", birthday=" + birthday + ", orderList=" + orderList +'}' } public int getId () {return id;} public void setId (int id) {this.id = id;} public String getUsername () {return username;} public void setUsername (String username) {this.username = username;} public String getPassword () {return password;} public void setPassword (String password) {this.password = password } public Date getBirthday () {return birthday;} public void setBirthday (Date birthday) {this.birthday = birthday;}}

Create a UserMapper interface

Package com.zg.mapper;import com.zg.domain.User;import java.util.List;public interface UserMapper {public List findAll ();}

Configure UserMapper.xml

Select *, o.id oid from user udeaux orders o where u.id=o.uid

test

@ Test// test one to many public void test2 () throws IOException {InputStream resourceAsStream = Resources.getResourceAsStream ("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder () .build (resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession (); UserMapper mapper = sqlSession.getMapper (UserMapper.class); List userList = mapper.findAll (); for (User user: userList) {System.out.println (user) } sqlSession.close ();}

Many-to-many query

The relationship between the user table and the role table is that a user has multiple roles, and a role is used by multiple users.

Requirements for many-to-many queries: query users query all roles of the user at the same time

Select * from user uJournal syscraper usernames role ur, sys_role r where u.id=ur.userId and ur.roleId=r.id

Create Role entities and modify User entities

Add UserMapper Interfac

Package com.zg.mapper;import com.zg.domain.User;import java.util.List;public interface UserMapper {public List findAll (); public List findUserAndRoleAll ();}

Configure UserMapper.xml

Select * from user uJournal syscraper usernames role ur, sys_role r where u.id=ur.userId and ur.roleId=r.id

Test code

@ Test// Test many-to-many public void test3 () throws IOException {InputStream resourceAsStream = Resources.getResourceAsStream ("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder () .build (resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession (); UserMapper mapper = sqlSession.getMapper (UserMapper.class); List userAndRoleAll = mapper.findUserAndRoleAll (); for (User user: userAndRoleAll) {System.out.println (user) } sqlSession.close ();}

After reading this article, I believe you have a certain understanding of "how to use the query function of MyBatis multi-table operation". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.