mybatis advanced learning
Content introduction
- Dynamic sql
- Multi table query in mybatis
- Nested query in mybatis
- Lazy loading (delayed loading)
- cache
A mapping file - dynamic sql
When we want to execute different sql statements according to different conditions, we need to use dynamic sql.
For example: multi condition query, password modification and user basic information modification
1 if tag
Requirement: query users. If usename is not empty, add username condition; if sex is not empty, add sex condition
public interface UserDao {
List<User> find4If(User user);
}
<select id="find4If" resultType="user">
select * from user where 1=1
<if test="username != null and username != ''">
and username = #{username}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</select>
@Test
public void testIf() {
//0. Simulation conditions
User user = new User();
//user.setUsername("Wang Xiaoer");
user.setSex("female");
//1. Get sqlsession
SqlSession sqlSession = MybatisUtils.openSession();
//2. Get dao
UserDao userDao = sqlSession.getMapper(UserDao.class);
//3. Call methods in dao
List<User> list = userDao.find4If(user);
for (User user1 : list) {
System.out.println(user1);
}
//4. Commit transactions to release resources
MybatisUtils.commitAndClose(sqlSession);
}
Note: "and" should be used instead of "& &" when judging and in sql
2 where label
After using where, we don't need to write 1 = 1 (deal with the first condition and or). We can judge whether all conditions are not true. If they are not true, we don't add where; If one is true, add where, and dispose of and or in front of the first condition
Requirements: query users according to user name and gender, add those conditions if there are any conditions, and query all conditions if there are no conditions
List<User> find4IfAndWhere(User user);
<select id="find4IfAndWhere" resultType="user">
select * from user
<where>
<if test="username != null and username != ''">
and username = #{username}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</select>
@Test
public void testIfAndWhere() {
//0. Simulation conditions
User user = new User();
user.setUsername("Wang Xiaoer");
user.setSex("female");
//1. Get sqlsession
SqlSession sqlSession = MybatisUtils.openSession();
//2. Get dao
UserDao userDao = sqlSession.getMapper(UserDao.class);
//3. Call methods in dao
List<User> list = userDao.find4IfAndWhere(user);
for (User user1 : list) {
System.out.println(user1);
}
//4. Commit transactions to release resources
MybatisUtils.commitAndClose(sqlSession);
}
3 set label
Requirement: update the user information according to the id, and update those attributes if they have values
The set tag can remove the "," of the last field in set
int update4Set(User user);
<update id="update4Set">
update user
<set>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="sex != null and sex != ''">
sex = #{sex},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="address != null and address != ''">
address = #{address},
</if>
</set>
where id = #{id}
</update>
@Test
public void testSet() {
//0. Simulation conditions
User user = new User();
user.setId(50);
user.setUsername("Sleep 1");
user.setSex("male");
user.setBirthday(new Date());
//1. Get sqlsession
SqlSession sqlSession = MybatisUtils.openSession();
//2. Get dao
UserDao userDao = sqlSession.getMapper(UserDao.class);
//3. Call methods in dao
userDao.update4Set(user);
//4. Commit transactions to release resources
MybatisUtils.commitAndClose(sqlSession);
}
## 4 foreach label
foreach It is mainly used for circular traversal of data
For example:`select * from user where id in (1,2,3) `In such a statement, the parameter part passed in must depend on foreach Traversal can be achieved.
> <foreach>The tag is mainly used to traverse the collection. Its properties are:
> • collection: Represents the collection element to traverse
> If traversing a set,Attribute value is colletion perhaps list
> If traversing an array,Attribute value is array
> If you traverse the list perhaps array,The attribute value is the attribute name in the object
> • open: Represents the beginning of a statement
> • close: Representative closing part
> • item: Represents the variable name generated by traversing each element of the collection
> • separator: Delegate separator
### Traversal list
```java
List<User> find4ForeachByList(List ids);
<select id="find4ForeachByList" resultType="user">
select * from user where id in
<foreach collection="list" open="(" close=")" separator="," item="n">
#{n}
</foreach>
</select>
Traversal array
List<User> find4ForeachByArray(int[] ids);
<select id="find4ForeachByArray" resultType="user">
select * from user where id in
<foreach collection="array" open="(" close=")" separator="," item="n">
#{n}
</foreach>
</select>
Traverse the list or array properties in the bean
List<User> find4ForeachByQueryVo(QueryVo vo);
<select id="find4ForeachByQueryVo" resultType="user">
select * from user where id in
<foreach collection="ids" open="(" close=")" separator="," item="n">
#{n}
</foreach>
</select>
QueryVo class
/*
vo:value object Value object (object that encapsulates data)
*/
public class QueryVo {
private int[] ids;
public int[] getIds() {
return ids;
}
public void setIds(int[] ids) {
this.ids = ids;
}
}
5 sql fragment
Duplicate sql can be extracted from the mapping file and referenced with include when used, so as to achieve the purpose of sql reuse
For example, when querying, the name of the select field is usually used instead of select * in development, which is more efficient
<sql id="BaseSelectSQL">
select id,username,birthday,sex,address from user
</sql>
<select id="find4ForeachByQueryVo" resultType="user">
<include refid="BaseSelectSQL"/>
where id in
<foreach collection="ids" open="(" close=")" separator="," item="n">
#{n}
</foreach>
</select>
Two multi table relational mapping and query
1 entity relationship
Relationship table:
- One to many (many to one): Department and employee classification and line and line pictures, merchants and line users and orders
- Many to many: users collect line users and roles, students and course orders and goods
- One to one: citizen and ID number husband and wife
Table design:
- One to many: generally, a foreign key field is added on multiple tables to point to the primary key of one table
- Many to many: add an intermediate table and keep the primary keys of at least two other tables in the table. You can split many to many into two one to many
- one-on-one:
- The two watches merge into one
- Unique foreign key correspondence
- Primary key correspondence
java entity relationship and Design:
From the perspective of actual business, there are only two situations: one to many and one to one
One to many relationship: store the other party's collection in the current class
For one-to-one relationship: store each other's objects in the current class
2 one to many
Demand: query the information of the user with id=41 and its order information
sql analysis:
SELECT u.*,o.id oid,o.ordertime,o.money FROM USER u LEFT JOIN orders o ON u.id = o.uid WHERE u.id = 41;
analysis:
The returned should be a user object Object contains information about the order of ta
Step analysis:
- Create the orders table and the Order entity class
- Add the order collection in the User class, and provide get and set methods to indicate which orders the User has
- Add method User findUserByUidWithOrders(int uid) in UserDao
- Provide the statement tag in the mapping file of UserDao
- Handle one to many
Code implementation:
1. Create the orders table
CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) DEFAULT NULL, `ordertime` datetime DEFAULT NULL, `money` double DEFAULT NULL, PRIMARY KEY (`id`), KEY `uid` (`uid`), CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `orders` */ insert into `orders`(`id`,`uid`,`ordertime`,`money`) values (1,41,'2019-05-20 02:58:02',999.5),(2,45,'2019-02-14 07:58:00',1399),(3,41,'2019-06-01 21:00:02',1666);
2. Write Order entity
public class Order {
private Integer id;
private Date orderTime;
private Double money;
//get and set are generated by themselves
}
3. Write methods in OrderDao
User findUserByUidWithOrders(int uid);
4. In orderdao Write a one to many mapping in XML
<resultMap id="WithOrdersMap" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--
One to many mapping
Encapsulate the queried line records into order objects,Then join user of orderList Attribute
property:The property name of the opposite collection in the current class
ofType:Fully qualified class name of the other party-Support alias
-->
<collection property="orderList" ofType="order">
<!--Mapping relationship between query result and order object-->
<id property="id" column="oid"/>
<result property="orderTime" column="ordertime"/>
<result property="money" column="money"/>
</collection>
</resultMap>
<select id="findUserByUidWithOrders" resultMap="WithOrdersMap">
SELECT u.*,o.id oid,o.ordertime,o.money
FROM USER u
LEFT JOIN orders o
ON u.id = o.uid
WHERE u.id = #{uid};
</select>
public class B_TestManyTable {
@Test
public void testOne2Many() {
//Get session
SqlSession sqlSession = MybatisUtils.openSession();
//Get dao
UserDao userDao = sqlSession.getMapper(UserDao.class);
//Call dao method
User user = userDao.findUserByUidWithOrders(41);
System.out.println(user);
List<Order> orderList = user.getOrderList();
if (orderList != null) {
for (Order order : orderList) {
System.out.println(order);
}
}
//Commit transaction
MybatisUtils.commitAndClose(sqlSession);
}
}
3 one to one
Demand: query the order information with id=1 and its user information
sql analysis:
SELECT o.id oid,o.ordertime,o.money,u.* FROM orders o JOIN USER u ON o.uid = u.id WHERE o.id = 1;
Return an order object. The order information is encapsulated to the order object, and the user information is encapsulated to the user object in the order
Step analysis:
- Add a user object in the Order class to indicate which user the current Order belongs to
- Write the OrderDao interface and provide the corresponding mapping file
- Write the method findOrderByOidWithUser in OrderDao and provide the corresponding statement tag in the mapping file
Method 1: use resultMap to encapsulate the result set
- Provide the method order findorderbyoidwithuser in OrderDao_ 1(int oid);
- In orderdao XML provides the corresponding statement tag
Method 2: use the resultMap+association tag (one-to-one mapping configuration) to complete the encapsulation
- Provide the method order findorderbyoidwithuser in OrderDao_ 2(int oid);
- In orderdao XML provides the corresponding statement tag
Method 1: use resultMap to encapsulate the result set
public interface OrderDao {
Order findOrderByOidWithUser_1(int oid);
}
<resultMap id="WithUser_1" type="order">
<id property="id" column="oid"/>
<result property="orderTime" column="ordertime"/>
<result property="money" column="money"/>
<result property="user.id" column="id"/>
<result property="user.username" column="username"/>
<result property="user.birthday" column="birthday"/>
<result property="user.sex" column="sex"/>
<result property="user.address" column="address"/>
</resultMap>
<select id="findOrderByOidWithUser_1" resultMap="WithUser_1">
SELECT o.id oid,o.ordertime,o.money,u.*
FROM orders o
JOIN USER u
ON o.uid = u.id
WHERE o.id = #{oid};
</select>
Method 2: use the resultMap+association tag (one-to-one mapping configuration) to complete the encapsulation
public interface OrderDao {
Order findOrderByOidWithUser_1(int oid);
Order findOrderByOidWithUser_2(int oid);
}
<resultMap id="WithUser_2" type="order">
<id property="id" column="oid"/>
<result property="orderTime" column="ordertime"/>
<result property="money" column="money"/>
<!--
One to one mapping:Encapsulate the information of the queried object into the specified object
property:The property name of the opposite party in the current class
javaType:The fully qualified class name of the object
-->
<association property="user" javaType="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</association>
</resultMap>
<select id="findOrderByOidWithUser_2" resultMap="WithUser_2">
SELECT o.id oid,o.ordertime,o.money,u.*
FROM orders o
JOIN USER u
ON o.uid = u.id
WHERE o.id = #{oid};
</select>
Three nested queries
The complex sql statement of multi table query is divided into several simple query statements, and then nested by mybatis framework
Take a chestnut
* Demand: query an order and the user of the order at the same time * Joint query: select * from orders o join user u on o.uid = u.id where o.id = 1; * nested queries 1.Query order information first select * from orders where id = 1;-- You can find uid = 41; 2.Querying user information select * from user where id = 41;-- Query users according to the above query results
Nested queries are the basis of deferred loading
1 one to one
Demand: query an order and find the user of the order at the same time
1.Query order information first select * from orders where id = 1;-- You can find uid = 41; 2.Querying user information select * from user where id = 41;-- Query users according to the above query results
Step analysis
- Provide a method in UserDao: User findUserByUid(int uid);
- In userdao Just provide the statement of the above method in XML
- Provide a method in OrderDao: Order findOrderByOidWithUser_QianTao(int oid)
- In orderdao The statement of the above method is provided in XML
- To configure nested query, take uid in the result of querying order information as the query condition of user information in UserDao, and finally encapsulate user information to users in the order
code implementation
UserDao
User findUserByUid(int uid);
UserDao. The XML configuration is as follows
<select id="findUserByUid" resultType="user">
select * from user where id = #{uid}
</select>
OrderDao
Order findOrderByOidWithUser_QianTao(int oid);
OrderDao. Code in XML
<resultMap id="WithUser_QianTao" type="order">
<id property="id" column="id"/>
<result property="orderTime" column="ordertime"/>
<result property="money" column="money"/>
<!--
One to one nested query
The query results uid Get,As userDao One of statement Conditional query data,The result type is user,Assign to order Medium user object
-->
<association property="user" column="uid" select="com.itheima.dao.UserDao.findUserByUid" javaType="user"/>
</resultMap>
<select id="findOrderByOidWithUser_QianTao" resultMap="WithUser_QianTao">
select * from orders where id = #{oid}
</select>
2 one to many
Demand: query a user and find the orders that the user has at the same time
1.First, according to the user's id Query user information select * from user where id = 41; 2.Then according to the user's id Query all orders of the current user in the order table select * from orders where uid = 41;
Step analysis
- Provide methods in OrderDao: List findAllOrdersByUid(int uid);
- In orderdao Just provide the statement corresponding to the above method in XML
- Provide method in UserDao: User findUserByUidWithOrders_QianTao(int uid);
- In userdao Configure the statement of the above method in XML
- Configure nested query, take the user's id as the condition for querying orders, and query all orders
code implementation
OrderDao
List<Order> findAllOrdersByUid(int uid);
OrderDao.xml
<select id="findAllOrdersByUid" resultType="order">
select * from orders where uid = #{uid}
</select>
UserDao
User findUserByUidWithOrders_QianTao(int uid);
UserDao.xml
<resultMap id="WithOrdersMap_QianTao" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<!--
One to many nested query
Get in the result of query column Corresponding value(id)As query criteria,To execute orderDao One of statement,Assign the query result to the order set in the user
-->
<collection property="orderList" ofType="order" column="id"
select="com.itheima.dao.OrderDao.findAllOrdersByUid"/>
</resultMap>
<select id="findUserByUidWithOrders_QianTao" resultMap="WithOrdersMap_QianTao">
select * from user where id = #{uid}
</select>
3. Knowledge summary
one-on-one(Many to one)Configuration: Using<resultMap>+<association>Configure through column Conditions, execution select query One to many(Many to many)Configuration: Using<resultMap>+<collection>Configure through column Conditions, execution select query Advantages: simplify multi table query operation,It is the basis of lazy loading Disadvantages: execute multiple times sql Statement, wasting database performance
Four loading strategies
In enterprise development, the associated data will not be queried when querying all data. At this time, the delayed loading mechanism will be used
* One user with 1000 orders When we query users, do we want to query the associated orders together? no need When we query the order details, should we query the associated users together? use * Delayed loading strategy Data is loaded only when it is needed. Data is not loaded when it is not needed. Lazy loading is also called lazy loading. * Application scenario One to many or many to many,Lazy loading is generally used to query associated data One to one or many to one,Generally, immediate loading is used to query the associated data * be careful Lazy loading is based on nested queries
Classification:
- Global lazy loading
- Local lazy loading
- be careful:
- All need to be opened manually
- Local lazy loading takes precedence over global lazy loading
1 global delayed loading
In the core configuration file, set global deferred loading on
<settings>
<!--Enable global lazy loading-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--Setting any method does not trigger lazy loading,Except attribute get method-->
<setting name="lazyLoadTriggerMethods" value=""/>
</settings>
When debug ging or directly printing the modified object, the toString method of the object will be executed, which will trigger delayed loading. Therefore, we also need to configure the toString method in the core configuration file to not trigger lazy loading
Note: the local loading strategy takes precedence over the global loading strategy. Generally, one-to-one can be set to load immediately
2 local delayed loading
<association> and <collection>Tags have a common attribute fetchType="lazy | eager" lazy Lazy loading eager Load now
<association property="user" javaType="cn.itcast.domain.User" column="uid"
select="cn.itcast.dao.UserDao.findUserByUid" fetchType="eager"/>
<collection property="orderList" ofType="cn.itcast.domain.Order" column="id"
select="cn.itcast.dao.OrderDao.findAllOrdersByUid" fetchType="lazy"/>
Five cache
What is caching?
- Data stored in memory
Why cache?
- Because querying the data in memory is more efficient than querying the data in disk
What kind of data is suitable for caching?
- Frequent access to data that will not be frequently modified and less sensitive use of caching
What kind of data is not suitable for caching?
- Data with high sensitivity: bank account, commodity quantity and stock price
MyBatis is an excellent ORM framework with built-in caching mechanism
- Level 1 cache: enabled by default and cannot be closed. SqlSession level cache is the same as the life cycle of SqlSession
- L2 cache: enabled by default. To use it, you need to configure the mapper level (SqlSessionFactory level) cache
L1 cache
L1 cache is a SqlSession level cache, which is enabled by default and cannot be closed.
- When querying data, it is preferred to query data in the cache. If it is returned directly in the cache; If there is no data in the cache, go to the disk to query the data The queried data is put into the cache according to certain rules to facilitate the next query;
- When a session is closed or emptied, clearCache() empties the first level cache of the session
- Operation of emptying cache: save, update and delete (to ensure data consistency)
- The first level cache stores the queried objects
Demonstrate L1 cache: query an object twice in an sqlSession
@Test
//Test the existence of L1 cache: query an object twice in an sqlSession
public void testFirstCache() {
SqlSession sqlSession = MybatisUtils.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user1 = userDao.findUserByUid(42);
System.out.println(user1);
System.out.println("------Query again-------");
User user2 = userDao.findUserByUid(42);
System.out.println(user2);
System.out.println(user1 == user2);
MybatisUtils.commitAndClose(sqlSession);
}
Demonstrate emptying the L1 cache:
@Test
//Test clear L1 cache
public void testClearFirstCache() {
SqlSession sqlSession = MybatisUtils.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user1 = userDao.findUserByUid(42);
System.out.println(user1);
System.out.println("------wipe cache -------");
sqlSession.clearCache();
System.out.println("------Query again-------");
User user2 = userDao.findUserByUid(42);
System.out.println(user2);
System.out.println(user1 == user2);
MybatisUtils.commitAndClose(sqlSession);
}
L2 cache
The L2 cache belongs to the Mapper mapper level. You need to manually turn on < cache >, and let the entity class implement the serializable interface
The L2 cache is cross - sqlSession If you use the same sqlSession, you will not query in the L2 cache
When the SQL session is closed, mybatis will put the data into the L2 cache
The L2 cache only saves data, not objects
Use steps
1. Enable L2 cache in the core configuration file [enabled by default]
<!--Enable L2 cache support-->
<setting name="cacheEnabled" value="true"/>
2. In specified Mapper Enable L2 cache in mapping file <cache/>
<mapper namespace="cn.itcast.dao.UserDao">
<!--Enable L2 cache for current mapping-->
<cache/>
3. Modify entity class implementation serializable Serialization interface
4. Test L2 cache
@Test
//Test the existence of L2 cache: use two sqlsessions to query an object twice. Remember to close the first sqlsession after the query is completed
public void testSecondCache() {
SqlSession sqlSession1 = MybatisUtils.openSession();
UserDao userDao1 = sqlSession1.getMapper(UserDao.class);
User user1 = userDao1.findUserByUid(42);
System.out.println(user1);
//Close sqlSession
MybatisUtils.commitAndClose(sqlSession1);
System.out.println("------Query again-------");
SqlSession sqlSession2 = MybatisUtils.openSession();
UserDao userDao2 = sqlSession2.getMapper(UserDao.class);
User user2 = userDao2.findUserByUid(42);
System.out.println(user2);
System.out.println(user1 == user2);
//Close sqlSession
MybatisUtils.commitAndClose(sqlSession2);
}