MyBatis part2

Before knowing many to one and one to many, the feeling after learning is like learning the joint table query in MySQL statements (there are two tables first, and the two are connected by the same key value between them), and those statements are written in the Java program that operates the database.

Preparation before learning: there are two tables, one for Student and one for Teacher.

First of all, the two tables are linked. The tid column in the Student table corresponds to the id column in the Teacher table. In this way, the two tables can be linked to facilitate subsequent addition, deletion, modification and query. Then we should know what environment many to one is used in

Many to one processing

When there are multiple students, corresponding to a teacher, we want to query all the student information, and also want to know who their corresponding teacher is?

Test:

1. Import required dependencies

<!-- Import dependency -->
<dependencies>
    <!-- Package of unit test Junit -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.10</version>
    </dependency>
    <!-- Mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>
    <!-- Operation database -->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <!-- lombok -->
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
</dependencies>

2. Establish the entity class Teacher, Student corresponding to the two tables

@Data
public class Teacher {
    private int id;
    private String name;
}

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
    
    private Teacher teacher;//Because we need to show who the teacher is
}

3. Establish corresponding Mapper interface

public interface StudentMapper {

    List<Student> getStudents();
}


public interface TeacherMapper {


}

4. Set up Mapper.xml file (many to one embodiment)!!! )

<?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.feng.mapper.StudentMapper">

    <resultMap id="StudentTeacher" type="Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <select id="getStudents" resultMap="StudentTeacher">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id
    </select>
</mapper>

5. To bind the Mapper interface required for registration in Mybatis-config.xml or (scan package directly)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--xml The top and bottom positions of the labels of the elements in are required-->
    <properties resource="db.properties"/>

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <!--Alias configuration of package-->
    <typeAliases>
        <package name="com.feng.pojo"/>
        <!--<typeAlias type="com.kuang.pojo.User" alias="User"/>-->
    </typeAliases>

    <!-- One environments Label elements can have multiple configurations -->
    <environments default="development">
        <!-- Every one of them environment Represents a specific environment -->
        <environment id="development">
            <!--transactionManager Transaction manager -->
            <transactionManager type="JDBC"/>
            <!-- dataSource Data source configuration -->
            <dataSource type="POOLED">
                <!-- Configuration of connection database i-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/feng/mapper/StudentMapper.xml"/>

    </mappers>

</configuration>

6. Establish test class to see if it is successful

    @Test
    public void testGetStudent(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        System.out.println(mapper.getStudents());
    }

Console printing:

One to many processing

Now we need to check the teacher and know what students the teacher corresponds to

1. The environment is the same as the above example

2. Establish the entity class Teacher, Student corresponding to the two tables

@Data
public class Teacher {
    private int id;
    private String name;

   private List<Student> students; //To view the students corresponding to the teacher
}

@Data
public class Student {
    private int id;
    private String name;
    private int tid;

}

3. Establish corresponding Mapper interface

public interface TeacherMapper {

    public Teacher getTeacher(int id);

}
public interface StudentMapper {

}

4. Set up Mapper.xml file (one to many embodiment)!!! )

<?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.feng.mapper.TeacherMapper">

<!--One to many, one student to many teachers-->
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid = t.id and t.id=#{id}
    </select>

</mapper>

5. To bind the Mapper interface required for registration in Mybatis-config.xml

6. Establish test class to see if it is successful

    @Test
    public void testGetTeacher(){
        SqlSession session = MyBatisUtils.getSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        System.out.println(mapper.getTeacher(1));
    }

Printout:

• For students, connect multiple students with one teacher
• For teachers, a group, a teacher, has many students [one to many]

Note:

1. association - association [many to one]
2. collection [one to many]
3. javaType & ofType
   1. JavaType is used to specify the type of property in entity class
   2. ofType is used to specify pojo type mapped to List or collection, and constraint type in generics!

Dynamic SQL

One of the powerful features of MyBatis is its dynamic SQL. If you have the experience of using JDBC or other similar frameworks, you can realize how painful it is to splice SQL statements according to different conditions. When splicing, make sure that you don't forget the necessary spaces, and that you omit the comma at the end of the column name list. We can get rid of this pain completely by using dynamic SQL.

preparation:

1. Build a database

CREATE TABLE `blog` (
  `id` VARCHAR(50) NOT NULL COMMENT 'Blog id',
  `title` VARCHAR(100) NOT NULL COMMENT 'Blog title',
  `author` VARCHAR(30) NOT NULL COMMENT 'Blogger',
  `create_time` DATETIME NOT NULL COMMENT 'Creation time',
  `views` INT(30) NOT NULL COMMENT 'Browse volume'
) ENGINE=INNODB DEFAULT CHARSET=utf8

2. Create the corresponding entity class in the project

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createDate;
    //In the database, we usually use underscores to divide and name,
    private int views;

}

3. Establish corresponding Mapper interface and xml configuration file

public interface BolgMapper {
    int addBlog(Blog blog);  //Write method to insert data
}

<?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.feng.mapper.BolgMapper">

    <insert id="addBlog" parameterType="Blog">
        insert into mybatis.blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createDate},#{views})
    </insert>
</mapper>

4. Register the corresponding binding in mybatis config file

5. Create test class and write data

@Test
public void testAddBlog() {

    SqlSession session = MyBatisUtils.getSession(true);//Turn on auto submit, and then build a getSession method with a Boolean parameter in MyBatisUtils
    BolgMapper mapper = session.getMapper(BolgMapper.class);

    Blog blog1 = new Blog();
    blog1.setId(IDUtils.getId());
    blog1.setTitle("mybatis so easy");
    blog1.setAuthor("AA");
    blog1.setCreateDate(new Date());
    blog1.setViews(99);
    mapper.addBlog(blog1);


    Blog blog2 = new Blog();
    blog2.setId(IDUtils.getId());
    blog2.setTitle("Java so easy");
    blog2.setAuthor("BB");
    blog2.setCreateDate(new Date());
    blog2.setViews(99);
    mapper.addBlog(blog2);

    Blog blog3 = new Blog();
    blog3.setId(IDUtils.getId());
    blog3.setTitle("data so easy");
    blog3.setAuthor("CC");
    blog3.setCreateDate(new Date());
    blog3.setViews(99);
    mapper.addBlog(blog3);

    Blog blog4 = new Blog();
    blog4.setId(IDUtils.getId());
    blog4.setTitle("dou so easy");
    blog4.setAuthor("DD");
    blog4.setCreateDate(new Date());
    blog4.setViews(99);
    mapper.addBlog(blog4);

    Blog blog5 = new Blog();
    blog5.setId(IDUtils.getId());
    blog5.setTitle("nan so easy");
    blog5.setAuthor("EE");
    blog5.setCreateDate(new Date());
    blog5.setViews(99);
    mapper.addBlog(blog5);

    Blog blog6 = new Blog();
    blog6.setId(IDUtils.getId());
    blog6.setTitle("yiyi so easy");
    blog6.setAuthor("FF");
    blog6.setCreateDate(new Date());
    blog6.setViews(99);
    mapper.addBlog(blog6);
}

//Where IDUtils is a tool class written under Utils
public class IDUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

Write data. Check the database here and find that it has been written successfully

IF

What dynamic SQL usually does is conditionally include part of the where clause. For example:

Write a method in the interface of BlogMapper in the chestnut above:

public interface BolgMapper {

    List<Blog> getBlogByIf(Map map);

}

Write the first dynamic Sql statement for the corresponding BlogMapper.xml in the chestnut above. We want to optionally search by two conditions: "title" and "author":

<select id="getBlogByIf" resultType="Blog" parameterType="map">
    select * from mybatis.blog
    <where>
        <if test="title!=null">
            title = #{title}
        </if>
        <if test="author!=null">
            and author =#{author}
        </if>
    </where>
</select>

Write a test class to test. At this time, log with Log4j

@Test
    public void testgetBlogByIf() {

        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","dou so easy");
        map.put("author","DD");
        List<Blog> list = mapper.getBlogByIf(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
    }

You will find the data exactly consistent with the above two conditions

When one of the conditions is not satisfied, the query cannot be found. Or when only one of the conditions is met each time, the corresponding data will be returned

chooose(when,otherwise)

Sometimes, we don't want to use all the conditional statements, but just choose one or two of them. In this case, MyBatis provides the choose element, which is a bit like the switch statement in Java.

It's the same as the above example, but this time, if "title" is provided, search by "title". If "author" is provided, search by "author". If neither of them is provided, all eligible blogs will be returned (if neither of the chestnuts is satisfied, they will go other way, which is equivalent to the default in Java switch)

Write method in interface first

 List<Blog> queryBlogChoose(Map map);

Continue to write sql statements in xml

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

Write a test class to test

//Just want to query the data whose author is EE
@Test
    public void testqueryBlogChoose() {

        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
       
        map.put("author","EE");
        List<Blog> list = mapper.queryBlogChoose(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
    }

When I add the title, but add the author whose title is wrong, the data returned is as follows, which is only the data satisfying the title

When the above two conditions are not met, the views=99 will be printed

trim(where,set)

The WHERE element knows to insert a "WHERE" clause only if more than one if condition has a value. Also, if the final content starts with "AND" OR, "the WHERE element knows how to remove them.

select * from mybatis.blog
<where>
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</where>

If the where element doesn't follow the normal pattern, we can still customize the function we want by customizing the trim element. For example, the custom trim element equivalent to the where element is:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>

The prefixOverrides property ignores pipe delimited text sequences (note that spaces are also necessary in this case). The result is that all the content specified in the prefixOverrides property will be removed and the content specified in the prefixOverrides property will be inserted.

A similar solution for dynamically updating statements is called set. The set element can be used to dynamically contain columns that need to be updated, leaving out the others. For example:

<update id="updateBlog" parameterType="map">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

Here, the set element dynamically prefixes the set keyword and eliminates irrelevant commas, which are likely to be left behind the generated assignment statement after the conditional statement is used. set true face:

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

Test the updateBlog above

Write test class

@Test
public void testupdateBlog(){
    SqlSession session = MyBatisUtils.getSession(true);
    BolgMapper mapper = session.getMapper(BolgMapper.class);

    HashMap<String, String> map = new HashMap<String, String>();

    map.put("id","680b9f77c78c4429be1a9b7992da54a9");
    map.put("author","QQQ");
    mapper.updateBlog(map);
}

Update success

Foreach

Another common operation of dynamic SQL is to traverse a collection, usually when building IN conditional statements. For example:

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from mybatis.blog

    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>

</select>
<!--Query as id=?All data at-->
<!--collection:Set type; item: The variable name of the collection; open What does the cycle begin with; close What does the cycle end with,separator:What is the Division -->

Test:

@Test
    public void testqueryBlogForeach() {
        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String,Object> map = new HashMap<String,Object>();
        ArrayList<String> list = new ArrayList<String>();
        list.add("6ff5e719ea004feab2f08d35ac6dd2fe");
        list.add("a79fe6f2fe96403e9a5349b2aaabded6");
        list.add("680b9f77c78c4429be1a9b7992da54a9");
        map.put("ids",list);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

    }

As a result, the traversed data will be printed: