CGB2107-Day04-mybatis advanced usage
1. Mybatis operation
1.1 fuzzy query
1.1.1 edit test class
/*
Mybatis Job:
Requirement: query the data containing "fine" in name And in descending order of age
*/
@Test
public void findLike(){
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
//String name = "fine";
String name = "%essence%";
List<DemoUser> list = demoUserMapper.findLike(name);
System.out.println(list);
sqlSession.close();
}
1.1.2 edit xml Mapping File
<!--Description of fuzzy query: use%No. needs to be used""Package No.
matters needing attention: mybatis Medium sql Best lowercase. Because different systems are not case sensitive.
Key position: eclipse ctrl + shift + y a lowercase letter
ctrl + shift + u Capitalize/a lowercase letter
-->
<select id="findLike" resultType="com.jt.pojo.DemoUser">
<!--select * from demo_user where name like "%"#{name}"%" order by age desc -->
select * from demo_user where name like #{name} order by age desc
</select>
1.2 batch update
1.2.1 edit test method
/**
* Assignment 2: change the age of name Xiao Qiao / Da Qiao / Wang Zhaojun to 18 years old, female
*/
@Test
public void updateUser(){
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
Map<String,Object> map = new HashMap<>();
String[] array = {"Little Joe","Big Joe","Wang Zhaojun"};
map.put("names",array);
map.put("age",18);
map.put("sex","female");
demoUserMapper.updateUser(map);
sqlSession.close();
}
1.2.2 edit xml Mapping File
<!--Batch update operation-->
<update id="updateUser">
update demo_user set age = #{age},sex = #{sex}
where name in (
<foreach collection="names" item="name" separator=",">
#{name}
</foreach>
)
</update>
2. Mybatis optimization settings
About the order of Mybatis core configuration files
The content of element type "configuration" must match "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)".
2.1 mybatis simplification - alias package
2.1.1 business description
The path of POJO entity object needs to be written in mybatis xml Mapping file If you write the full path every time you edit, the development is relatively cumbersome So mybatis introduces the rule of alias package
2.1.2 implementing alias configuration
Define aliases in the core configuration file of mybatis
2.1.3 using alias mapping
2.1.4 using alias packages
Requirement: if there are multiple POJO entity classes in the project, it is more cumbersome to edit alias labels one by one So Mybatis provides the function of alias package
example:
com.jt.pojo.User,
com.jt.pojo.Dept,
com.jt.pojo.Order
Principle description: you can set the public package path com jt. POJO, if the package path is set The full path of the class will be spliced automatically in the future
xml core file configuration:
<!-- Configure alias -->
<typeAliases>
<!--Alias tags are only valid for a class.-->
<!--<typeAlias type="com.jt.pojo.DemoUser" alias="DemoUser"></typeAlias>-->
<!--package Specifies the information of the package path.-->
<package name="com.jt.pojo"/>
</typeAliases>
xml Mapping file configuration:
<mapper namespace="com.jt.mapper.DemoUserMapper">
<!--Mapping principle:
If the package path is defined in the configuration file,Then the path splicing will be completed automatically when mapping objects
resultType="com.jt.pojo.DemoUser"
-->
<select id="findAll" resultType="DemoUser">
select id,name,age,sex from demo_user
</select>
</mapper>
2.1.5 annotation and definition of alias
2.2 Mybatis simplified Sql tag
2.2.1 business description
There will be a large number of Sql statements in the xml Mapping file of mybatis With the increase of business, the number of Sql statements will also increase Some * * "Sql fragments" may be repeated If you want to simplify Sql statements, you can use Sql tag * * to simplify operations
example:
\1. select id,name,age,sex from demo_user where id = 1
\2. select id,name,age,sex from demo_user where name = xxx
2.2.2 sql tag usage
<!--2.simplify Sql label -->
<sql id="demo_user_sql">
select id,name,age,sex from demo_user
</sql>
<!--include Representative contains Sql label -->
<select id="findAll" resultType="DemoUser">
<include refid="demo_user_sql"/>
</select>
2.2.3 description of Sql tag
Advantages:
1. Using Sql tag can save xml file size
2. The structure of the code is relatively simple
Disadvantages:
1.Sql can only extract public Sql statements, which has some limitations
2. If a large number of Sql tags are used, the readability of the code is poor
3. Mybatis dynamic Sql
3.1 IF-WHERE usage
3.1.1 business requirements
Query data according to user object It is required to act as a where condition according to the non null property in the object Realize dynamic query
Case: multiple query criteria often appear in actual business, as shown in the figure:
3.1.2 edit test class
/*
* Encapsulate the object of DemoUser and query according to the non null property in the object
* */
@Test
public void testFindWhere(){
SqlSession sqlSession = sqlSessionFactory.openSession();
DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
DemoUser demoUser = new DemoUser();
demoUser.setAge(3000);
List<DemoUser> list = demoUserMapper.findWhere(demoUser);
System.out.println(list);
sqlSession.close();
}
3.1.3 edit xml Mapping File
<!--dynamic Sql case
thinking: If the data is not null,mybatis As a condition
if Label description:
test: The judgment condition can be written directly to the attribute
where label: Remove excess in condition and perhaps or of
explain: if and where Almost together.
-->
<select id="findWhere" resultType="DemoUser">
select id,name,age,sex from demo_user
<where>
<if test="name != null">name = #{name}</if>
<if test="age !=null"> and age=#{age}</if>
<if test="sex !=null"> and sex=#{sex}</if>
</where>
</select>
3.2 dynamic SQL set tag
3.2.1 edit test cases
/**
* Requirement: dynamically update data according to Id
*/
@Test
public void testUpdateSet(){
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
DemoUser user = new DemoUser();
user.setId(1).setName("Ambassador Shoushan");
demoUserMapper.updateUser(user);
sqlSession.close();
}
3.2.2 editing mapper interface
3.2.3 editing xml Mapping Files
<!--
rule: According to not in the object null Attribute as set condition
set Label description: remove set Redundant in condition ,number
-->
<update id="updateUser">
update demo_user
<set>
<if test="name !=null">name = #{name},</if>
<if test="age !=null">age = #{age},</if>
<if test="sex !=null">sex = #{sex}</if>
</set>
where
id = #{id}
</update>
3.3 dynamic SQL choose when otherwise
3.3.1 business requirements
Query the data according to the conditions If name exists, query by name; otherwise, query by sex
Supplementary notes:
Conditions: name = "Zhang San", sex = "male"
select * from demo_user where name = #{name}
3.3.2 edit test class
/**
* Requirement: if name exists, query by name; otherwise, query by sex
*/
@Test
public void testSelectChoose(){
SqlSession sqlSession = sqlSessionFactory.openSession(true);
DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
DemoUser user = new DemoUser();
user.setSex("male");
List<DemoUser> list = demoUserMapper.selectChoose(user);
System.out.println(list);
sqlSession.close();
}
3.3.3 edit xml Mapping File
<!--
demand: If you don't want to take all the conditions as if Judgment of.be mybatis Provides a branch structure switch
Syntax description:
choose:Representative branch structure,Only one condition is valid.
when: Specify the conditions and criteria for judgment if similar.
otherwise: If the above conditions are not met,This line of code is valid.
-->
<select id="selectChoose" resultType="DemoUser">
select * from demo_user
where
<choose>
<when test="name !=null">name = #{name}</when>
<otherwise>sex = #{sex}</otherwise>
</choose>
</select>
4 ResultMap
4.1 project construction
4.1.1 create table
- Create dept table int dept_id primary key auto increment, varchar type (40) dept_name
4.1.2 edit Dept POJO object
@Data
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Dept implements Serializable{
//Hump naming rules
private Integer deptId;
private String deptName;
}
4.2 usage of resulttype and resultMap
4.2.1 label description
resultType Description:
Automatic data encapsulation can only be realized when the field name in the result set is consistent with the name of the attribute
resultMap Description:
When the name of the field in the custom map implementation is inconsistent with that in the result set, you can use it
4.2.2 edit test class
@Test
public void testFindDept(){
SqlSession sqlSession = sqlSessionFactory.openSession();
DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
List<Dept> list = deptMapper.findAll();
System.out.println(list);
sqlSession.close();
}
12345678
4.2.3 edit business interface
4.2.4 edit xml Mapping File
<?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="com.jt.mapper.DeptMapper">
<!--
ORM thought: Object relational mapping.
attribute: deptId,deptName
field: dept_id,dept_name
resultType explain:
When the field name in the result set,If it is consistent with the name of the attribute,
Automatic data encapsulation will be realized.
resultMap="" Custom encapsulated data structure
-->
<select id="findAll" resultMap="deptRM">
select * from dept
</select>
<!--Custom mapping relationships
grammar:
1.id Tags represent primary keys (Each table will have a primary key)
1.1.column: Represents the field in the result set.
1.2.property: Properties in object
2.result Configuration information other than the primary key
-->
<resultMap id="deptRM" type="Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
</resultMap>
</mapper>
5. Related relationship
5.1 common relationships
- One to one example: 1 Person and ID number 2. Husband and wife 3 Employees and departments
- One to many example: One Department corresponds to multiple employees
- Many to many teachers and students
One teacher corresponds to multiple students
One student corresponds to multiple teachers
Core: consider the problem from one end
5.2 one to one
5.2.1 create table
Table name: emp employee table
Fields: id,name,age,dept_id
Table data description:
5.2.2 building code structure
5.2.2.1 create POJO
@Data
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Emp implements Serializable {
private Integer id;
private String name;
private Integer age;
private Integer deptId;
}
5.2.2.2 create EmpMapper interface
5.2.2.3 create empmapper XML Mapping File
<?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="com.jt.mapper.EmpMapper">
</mapper>
5.2.2.4 mybatis loading xml mapping file
Description: edit mybatis config XML file, add mapping file
5.2.3 one to one service encapsulation
Rule: use object encapsulation if one is
If it is multiple, it is encapsulated with the List collection
summary
-
Job: usage of fuzzy query '%' #{xxxx} '%', setname ('% XXXX%')
-
If a multivalued parameter is encountered, General packaging is Map aggregate. 1
-
mybatis-config. The configuration of XML files is sequential Not at will
-
Alias 1 Alias label 2 Alias package 3 annotation
-
The SQL tag extracts the public SQL and defines it through the SQL tag
-
Dynamic SQL where if / set / choose when
-
resultMap usage: solve the problem of inconsistent field and attribute names
-
resultType: when a single table is queried / the attribute is the same as the field,
-
Usage of association relationship one to one to many