1, Simple one-to-one cascade query
based on the previous chapters, some simple applications can be handled, but in actual projects, it is often associated table queries, such as one-to-one, one to many, etc. How are these queries handled? Let's talk about this. The previous section describes some operations of single table mapping, but multi table mapping is often used in our actual projects. In Java entity object pairs, one to many can be implemented according to List and Set, both of which are implemented through the collection tag in mybitis. This article introduces many to one table Association queries in multiple tables.
create two tables, assuming that a teacher corresponds to a class
CREATE TABLE `teacher` ( `t_id` int(11) NOT NULL AUTO_INCREMENT, `t_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; insert into `teacher`(`t_id`,`t_name`) values (1,'Zhang San'); insert into `teacher`(`t_id`,`t_name`) values (2,'Li Si'); CREATE TABLE `class` ( `c_id` int(11) NOT NULL AUTO_INCREMENT, `c_name` varchar(20) DEFAULT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`c_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; insert into `class`(`c_id`,`c_name`,`teacher_id`) values (1,'Java',1); insert into `class`(`c_id`,`c_name`,`teacher_id`) values (2,'UI',2); CREATE TABLE `student` ( `s_id` int(11) NOT NULL AUTO_INCREMENT, `s_name` varchar(20) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`s_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; insert into `student`(`s_id`,`s_name`,`class_id`) values (1,'AA',1); insert into `student`(`s_id`,`s_name`,`class_id`) values (2,'BB',1); insert into `student`(`s_id`,`s_name`,`class_id`) values (3,'CC',1); insert into `student`(`s_id`,`s_name`,`class_id`) values (4,'DD',2); insert into `student`(`s_id`,`s_name`,`class_id`) values (5,'EE',2); insert into `student`(`s_id`,`s_name`,`class_id`) values (6,'FF',2);
Create entity
public class Teacher {
private int id;
private String name;
public class Classes {
private int id;
private String name;
private Teacher teacher;
The ClassesMapper.xml query will appear in two ways
Method 1: cascade query
<resultMap id="classResultMap" type="cn.hxzy.mybatis.entity.Classes">
<id column="c_id" property="id"/>
<result column="c_name" property="name"/>
<result column="t_id" property="teacher.id"/>
<result column="t_name" property="teacher.name"/>
</resultMap>
<select id="selectAll" resultMap="classResultMap">
SELECT * FROM `class` c LEFT JOIN `teacher` t ON t.`t_id`=c.`teacher_id`
</select>
2, Nested results and nested queries
method 2: nested results. Nested result mapping is used to process a subset of duplicate union results and encapsulate the data of the join table query (remove duplicate data)
<resultMap id="classResultMap2" type="cn.hxzy.mybatis.entity.Classes">
<id column="c_id" property="id"/>
<result column="c_name" property="name"/>
<association property="teacher" javaType="cn.hxzy.mybatis.entity.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
<select id="selectAll" resultMap="classResultMap2">
SELECT * FROM `class` c LEFT JOIN `teacher` t ON t.`t_id`=c.`teacher_id`
</select>
Method 3: nested query, which returns the expected complex type by executing another SQL mapping statement
<select id="getClass2" resultMap="ClassResultMap2">
select * from class
</select>
<resultMap type="cn.hxzy.mybatis.entity.Classes" id="ClassResultMap2">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" select="getTeacher"></association>
</resultMap>
<select id="getTeacher" parameterType="int" resultType="cn.hxzy.mybatis.entity.Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
association defines the encapsulation rules of associated objects
Select: indicates that the current attribute is the result of calling the method specified in select.
Column: specifies which column value to pass to this method.
Note: the difference between $and #:
#{} use placeholder? Compile and send SQL by; Benefits: prevent SQL injection (recommended)
${} splices the parameters filled in by the user directly into SQL. Disadvantages: SQL injection;
Note: using #{} cannot generate table names and field names, so ${} must be used in fields and table names.
Converting #{id} to ${id} will report There is no getter for property named 'id' in 'class java.lang.Integer'. Just change the parameter to ${u parameter} or higher 3.5.6.
practice:
1. Create the project mybatis11 and complete the query of the following user roles using nested query and nested results respectively. surface Data download.
you need to use two kinds of queries to complete the following functions:
1. When querying a single user, query its role.
reference code:
<mapper namespace="cn.hxzy.mapper.UserMapper">
<!-- Nested results-->
<resultMap id="userResultMap1" type="cn.hxzy.entity.User">
<id column="u_id" property="id"/>
<result column="u_name" property="name"/>
<result column="login_name" property="loginName"/>
<result column="login_password" property="loginPassword"/>
<result column="u_create_time" property="createTime"/>
<result column="u_update_time" property="updateTime"/>
<association property="role" javaType="cn.hxzy.entity.Role">
<id column="r_id" property="id"/>
<result column="r_name" property="name"/>
<result column="remark" property="remark"/>
<result column="r_create_time" property="createTime"/>
<result column="r_update_time" property="updateTime"/>
</association>
</resultMap>
<sql id="user">u.id u_id,u.name u_name,u.login_name login_name, u.login_password login_password,
u.create_time u_create_time,u.update_time u_update_time</sql>
<sql id="role">r.id r_id,r.name r_name,r.remark remark,r.create_time r_create_time,r.update_time r_update_time</sql>
<select id="findByIdWithRole1" resultMap="userResultMap1">
select
<include refid="user"/>,
<include refid="role"/>
from user u left join role r on u.role_id=r.id where u.id=#{id}
</select>
<!-- nested queries -->
<resultMap id="userResultMap2" type="cn.hxzy.entity.User">
<id column="u_id" property="id"/>
<result column="u_name" property="name"/>
<result column="login_name" property="loginName"/>
<result column="login_password" property="loginPassword"/>
<result column="u_create_time" property="createTime"/>
<result column="u_update_time" property="updateTime"/>
<association property="role" javaType="cn.hxzy.entity.Role" column="role_id" select="getRole">
</association>
</resultMap>
<select id="findByIdWithRole2" resultMap="userResultMap2">
select
<include refid="user"/>,role_id
from user u where id=#{id}
</select>
<select id="getRole" resultType="cn.hxzy.entity.Role">
select id,name,remark,create_time createTime,update_time updateTime from role where id=#{id}
</select>
</mapper>
3, One to many one-way query
Entity class
public class Student {
private int id;
private String name;
public class Classes {
private int id;
private String name;
private Teacher teacher;
private List<Student> students;
Method 1: nested result: use nested result mapping to process subsets of duplicate joint results
<select id="findAll" resultMap="ClassResultMap3">
SELECT * FROM `class` c LEFT JOIN `student` s ON c.`c_id` =s.`class_id`
</select>
<resultMap type="cn.hxzy.mybatis.entity.Classes" id="ClassResultMap3">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<collection property="students" ofType="cn.hxzy.mybatis.entity.Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
Note: ofType specifies the object type in the students collection
Method 2: nested query, which returns the expected complex type by executing another SQL mapping statement
<select id="findAll" resultMap="ClassResultMap4">
select * from class
</select>
<resultMap type="cn.hxzy.mybatis.entity.Classes" id="ClassResultMap4">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<collection property="students" ofType="cn.hxzy.mybatis.entity.Student"
column="c_id" select="getStudent"></collection>
</resultMap>
<select id="getStudent" parameterType="int" resultType="cn.hxzy.mybatis.entity.Student">
SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
</select>
Collection defines the encapsulation rules for attributes associated with collection types
ofType: Specifies the type of elements in the collection
In mybatis, association relationships are commonly used as follows:
One to one javaType
One to many ofType
Exercise: (dictation for 30 minutes)
1. In the above project mybatis 11, use nested query to complete the query of user role resources. Complete the following functions:
1. When querying a single role, all resources under its role are queried.
2. When querying a single user, query all the roles and resources owned by the role.
reference code:
UserDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.hxzy.dao.UserDao">
<resultMap type="cn.hxzy.entity.User" id="UserMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="loginName" column="login_name" jdbcType="VARCHAR"/>
<result property="loginPassword" column="login_password" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
<association property="role" column="role_id" select="cn.hxzy.dao.RoleDao.queryById"></association>
</resultMap>
<!--Query single-->
<select id="queryById" resultMap="UserMap">
select
id, name, login_name, login_password, role_id, create_time, update_time
from mybatis.user
where id = #{id}
</select>
</mapper>
RoleDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.hxzy.dao.RoleDao">
<resultMap type="cn.hxzy.entity.Role" id="RoleMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="remark" column="remark" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
<collection property="resources" ofType="cn.hxzy.entity.Resource" column="id"
select="cn.hxzy.dao.ResourceDao.queryByRoleId"></collection>
</resultMap>
<!--Query single-->
<select id="queryById" resultMap="RoleMap">
select
id, name, remark, create_time, update_time
from mybatis.role
where id = #{id}
</select>
</mapper>
ResourceDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.hxzy.dao.ResourceDao">
<resultMap type="cn.hxzy.entity.Resource" id="ResourceMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="url" column="url" jdbcType="VARCHAR"/>
<result property="pid" column="pid" jdbcType="INTEGER"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
<result property="orderNumber" column="order_number" jdbcType="INTEGER"/>
</resultMap>
<!--Query single-->
<select id="queryByRoleId" resultMap="ResourceMap">
select
id, name, url, pid, create_time, update_time, order_number
from resource re left join role_resource rr on re.id = rr.resource_id where rr.role_id=#{roleId}
</select>
</mapper>
4, Cache (understand)
1. L1 Cache: HashMap local Cache based on perpetual Cache. Its storage scope is Session. When Session clearCache or close, all caches in the Session will be cleared. (on by default)
2. The mechanism of L2 cache is the same as that of L1 cache. By default, it is stored in perpetual cache and HashMap. The difference is that its storage scope is Mapper(Namespace), and the storage source can be customized, such as Ehcache.
3. For the cache data update mechanism, when a C/U/D operation is performed on the of a scope (L1 cache Session / L2 cache Namespaces), by default, the caches in all select ions under the scope will be clear ed.
The first level cache is to query the same object multiple times without sending SQL when the Session is not closed. mybatis turns on the first level cache by default.
The L2 cache usually needs to be added to * Mapper.xml and globally opened in the mybatis configuration file.
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>
note: when the first L1 cache is closed, the data will be brushed into another session of SqlSessionFactory to obtain the L2 cache content, that is, the previous session must be closed or commit ted, and the subsequent session can be obtained.
SqlSessionFactory factory = MybatisUtils.getFactory(); SqlSession session1 = factory.openSession(); SqlSession session2 = factory.openSession(); String statement = "com.ibaits.mapper.userMapper.getUser"; CUser user = session1.selectOne(statement, 1); session1.commit(); System.out.println(user); user = session2.selectOne(statement, 1); session2.commit(); System.out.println(user);
-
All select statements in the mapping statement file will be cached.
-
All insert, update and delete statements in the mapping statement file refresh the cache.
-
The cache will be reclaimed using the Least Recently Used (LRU) algorithm.
-
The cache is refreshed according to the specified time interval.
-
The cache stores 1024 objects
<cache
Occurrence = "FIFO" / / the recycling policy is first in first out
flushInterval = "60000" / / automatic refresh time 60s
size = "512" / / cache 512 reference objects at most
readOnly = "true" / > / / read only
5, Delayed loading (understand)
cglib needs to be used as the proxy mode. Import cglib dependencies as follows
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>2.2.2</version>
</dependency>
then add in the global configuration file
<!-- Global configuration parameters -->
<settings>
<!-- Delayed loading master switch -->
<setting name="lazyLoadingEnabled" value="true" />
<!-- Set demand loading -->
<setting name="aggressiveLazyLoading" value="false" />
</settings>
With the above configuration, all nested queries will be loaded late, but all nested results will not be affected.
6, Discriminator (from)
mybatis can use discriminator to judge the value of a column, and then change the encapsulation behavior according to the value of a column.
case: encapsulating Employee
if a girl is found out: query the Department information, otherwise do not query;
if it's a boy, put last_ The value of the name column is assigned to email;
<resultMap type="cn.hxzy.mybatis.bean.Employee" id="MyEmpDis">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<discriminator javaType="string" column="gender">
<!--girl student resultType:Specifies the encapsulated result type; Can not be missing./resultMap-->
<case value="0" resultType="employee">
<association property="dept" select="cn.hszy.mybatis.mapper.DepartmentMapper.getDeptById"
column="d_id"></association>
</case>
<!--schoolboy ;If it's a boy, put last_name The value of this column is assigned to email; -->
<case value="1" resultType="employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="last_name" property="email"/>
<result column="gender" property="gender"/>
</case>
</discriminator>
</resultMap>
7, Call stored procedure
create tables and stored procedures.
create table p_user(
id int primary key auto_increment,
name varchar(10),
sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into p_user(name,sex) values('A',"male");
insert into p_user(name,sex) values('B',"female");
insert into p_user(name,sex) values('C',"male");
create a stored procedure (query the number of men or women. If 0 is passed in, it is a woman, otherwise it is a man)
DELIMITER $ CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT) BEGIN IF sex_id=0 THEN SELECT COUNT(*) FROM p_user WHERE p_user.sex='female' INTO user_count; ELSE SELECT COUNT(*) FROM p_user WHERE p_user.sex='male' INTO user_count; END IF; END $
Call stored procedure
DELIMITER ; SET @user_count = 0; CALL ges_user_count(1, @user_count); SELECT @user_count;
Query the number of men or women. If 0 is passed in, it is a woman, otherwise it is a man
mapper.xml
<mapper namespace="cn.hxzy.mybatis.test7.userMapper">
<select id="getCount" statementType="CALLABLE" parameterMap="getCountMap">
call ges_user_count(?,?)
</select>
<parameterMap type="map" id="getCountMap">
<parameter property="sex_id" mode="IN" jdbcType="INTEGER"/>
<parameter property="user_count" mode="OUT" jdbcType="INTEGER"/>
</parameterMap>
</mapper>
Execute query
Map<String, Integer> parameterMap = new HashMap<String, Integer>();
parameterMap.put("sex_id", 1);
parameterMap.put("user_count", -1);
session.selectOne(statement, parameterMap);
Integer result = parameterMap.get("user_count");
System.out.println(result);
8, Paging interceptor
Paging can be divided into logical paging and physical paging. Logical pagination is that when our program displays the data of each page, we first query 1000 data in the table, and then select 100 data to display according to the "page number" of the current page.
physical paging is a program in which the program first determines which to which of the 1000 items should be selected, and then the database queries the 100 items required by the program according to the information given by the program and returns them to us.
the paging of mybatis is usually carried out with the help of third-party paging plug-ins during development
add the following two third-party jar packages
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.0.0</version>
</dependency>
mybatis configuration file add in
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
You can complete the query by calling the static method before querying.
@Test
public void testGetAll() {
SqlSessionFactory factory = MybatisUtils.getFactory();
SqlSession session = factory.openSession();
String statement = "com.day03_mybaits.test2.userMapper.getAllUsers";
Page<User> startPage = PageHelper.startPage(1, 12);
List<User> list = session.selectList(statement);
session.close();
for (User user : list) {
System.out.println(user);
}
System.out.println(startPage.getPages());
}
Page using json serialization tool will cause other properties (total pages and total entries) to disappear. You can use PageInfo to make up for this problem
public PageInfo<MenuGroup> findAll(Integer page, Integer size) {
Page<MenuGroup> objects = PageHelper.startPage(page, size);
menuGroupMapper.selectByExample(new MenuGroupExample());
return new PageInfo<>(objects);
}
9, Polymorphous parameter problem
because jdk7 was unable to obtain the name of method parameters through reflection, jdk8 has this function, but it is not enabled. Therefore, when the method in the interface has multiple parameters, it cannot be recognized normally in xml.
List<Resource> queryAll(int start,int size,String name);
xml
<select id="queryAll" resultType="cn.hxzy.entity.Resource">
select id, name, url, pid, create_time, update_time, order_number
from resource where name like #{name} limit #{start},#{size}
</select>
The solutions are as follows:
1. Search and open jdk8 reflection to obtain the parameter name, or upgrade jdk9.
2. Use [arg2, arg1, arg0, param3, param1, param2] instead of #{name}, #{start} and #{size}
3. Add the mybatis annotation org.apache.ibatis.annotations.Param as follows.
List<Resource> queryAll(@Param("start") int start,@Param("size") int size,@Param("name") String name);