1. Mybatis operation
1.1 fuzzy query
1.1.1 edit test class
/*
Mybatis Job:
Requirement: query the data containing "fine" in name, and arrange it in descending order by 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 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
2.0 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 the full path is written every time, the development is relatively cumbersome. Therefore, 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
Note: alias can be used to replace the full path of type in the future
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. Therefore, Mybatis provides the function of alias package
example:
com.jt.pojo.User,
com.jt.pojo.Dept,
com.jt.pojo.Order
Principle description: the public package path com.jt.pojo can be set. 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 valid only for a class.-->
<!--<typeAlias type="com.jt.pojo.DemoUser" alias="DemoUser"></typeAlias>-->
<!--package What is specified is 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 definition of alias
2.2 Mybatis simplified sql tag
2.2.1 business description
There are a large number of Sql statements in the xml Mapping file of mybatis. With the increase of business, the number of Sql statements will increase, and some Sql fragments can be repeated. If you want to simplify Sql statements, you can use Sql tags to simplify operations
example:
- select id,name,age,sex from demo_user where id = 1
- 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.2 description of Sql tag
Advantages:
- Using Sql tags can save xml file size
- The structure of the code is relatively simple
Disadvantages: - Sql can only extract public Sql statements, which is slightly more limited
- If a lot 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 the user object. It is required to act as the where condition according to the non null attribute in the object to 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 editing xml Mapping Files
<!--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: Based on objects that are not null Properties 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 data according to 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.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 usage
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 field name in the result set is inconsistent with the attribute in the object, you can use resultMap to realize custom encapsulation
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();
}
4.2.3 editing business interface
4.2.4 editing xml Mapping Files
<?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 Labels 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. Relationship
5.1 common relationships
One to one example: 1. person and ID number 2. husband and wife 3. staff and department
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 the mybatis-config.xml file and add a 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.
-
The configuration of mybatis-config.xml file is sequential. It cannot be arbitrary
-
Alias writing 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