CGB2107-Day04-mybatis advanced usage

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
    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);

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

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
    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"};

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=",">

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
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 -->
        <!--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"/>

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
    <select id="findAll" resultType="DemoUser">
        select id,name,age,sex from demo_user

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
\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

    <!--include Representative contains Sql label -->
    <select id="findAll" resultType="DemoUser">
        <include refid="demo_user_sql"/>

2.2.3 description of Sql tag

1. Using Sql tag can save xml file size
2. The structure of the code is relatively simple
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
    * */
    public void testFindWhere(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
        DemoUser demoUser = new DemoUser();
        List<DemoUser> list = demoUserMapper.findWhere(demoUser);

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
                <if test="name != null">name = #{name}</if>
                <if test="age !=null"> and age=#{age}</if>
                <if test="sex !=null"> and sex=#{sex}</if>

3.2 dynamic SQL set tag

3.2.1 edit test cases

     * Requirement: dynamically update data according to Id
    public void testUpdateSet(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
        DemoUser user = new DemoUser();
        user.setId(1).setName("Ambassador Shoushan");


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
                <if test="name !=null">name = #{name},</if>
                <if test="age !=null">age = #{age},</if>
                <if test="sex !=null">sex = #{sex}</if>
                id = #{id}

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
    public void testSelectChoose(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        DemoUserMapper demoUserMapper = sqlSession.getMapper(DemoUserMapper.class);
        DemoUser user = new DemoUser();
        List<DemoUser> list = demoUserMapper.selectChoose(user);

3.3.3 edit xml Mapping File

        demand: If you don't want to take all the conditions as if Judgment 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
                <when test="name !=null">name = #{name}</when>
                <otherwise>sex = #{sex}</otherwise>

4 ResultMap

4.1 project construction

4.1.1 create table

  1. Create dept table int dept_id primary key auto increment, varchar type (40) dept_name

4.1.2 edit Dept POJO object

@Accessors(chain = true)
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

    public void testFindDept(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
        List<Dept> list = deptMapper.findAll();

4.2.3 edit business interface

4.2.4 edit xml Mapping File

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-// Mapper 3.0//EN"
<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
    <!--Custom mapping relationships
   Tags represent primary keys (Each table will have a primary key)
                1.1.column: Represents the field in the result set.
       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"/>

5. Related relationship

5.1 common relationships

  1. One to one example: 1 Person and ID number 2. Husband and wife 3 Employees and departments
  2. One to many example: One Department corresponds to multiple employees
  3. 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 create POJO
@Accessors(chain = true)
public class Emp implements Serializable {
    private Integer id;
    private String name;
    private Integer age;
    private Integer deptId;
} create EmpMapper interface create empmapper XML Mapping File
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-// Mapper 3.0//EN"
<mapper namespace="com.jt.mapper.EmpMapper">

</mapper> 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


  1. Job: usage of fuzzy query '%' #{xxxx} '%', setname ('% XXXX%')

  2.  	If a multivalued parameter is encountered, General packaging is Map aggregate.
  3. mybatis-config. The configuration of XML files is sequential Not at will

  4. Alias 1 Alias label 2 Alias package 3 annotation

  5. The SQL tag extracts the public SQL and defines it through the SQL tag

  6. Dynamic SQL where if / set / choose when

  7. resultMap usage: solve the problem of inconsistent field and attribute names

  8. resultType: when a single table is queried / the attribute is the same as the field,

  9. Usage of association relationship one to one to many

Tags: Java

Posted by Pazuzu156 on Sun, 17 Apr 2022 12:43:54 +0930