New MyBatis | SQL query element mapping file

preface

C: In the last article, Teacher Zha introduced the core configuration file of MyBatis. At the beginning of this article, Teacher Zha will take you to learn the SQL mapping file of MyBatis. It is the most bright part of MyBatis (turn back to see the characteristics of MyBatis, the main advantages are here), and it is also the most contact part when we use MyBatis development in the future.

Don’t worry, MyBatis is incredibly powerful for SQL statement mapping, but SQL mapping files are pretty simple.

Below is the tag template of SQL mapping files. Teacher Zha will spend two or three chapters on the common usage of some tags.

mapperRoot tag
- Cache-ref references the cache configuration of other namespaces
- Cache Configures the cache for the given namespace
- resultMapThe custom result set mapping configuration describes the mapping between database result sets and objects, and is one of the most complex and powerful elements
- ~~parameterMap~~Custom parameter mapping configuration this element is deprecated and may be removed in the future! Use inline parameter mapping.
- SQL SQL blocks that can be reused
- Insert Mapping inserts statements
- Update Mapping update statement
- Delete Delete statement of the mapping
- Select mapping query statement

Mapper elements

The mapper element is the root tag of the SQL mapping file, which has a namespace attribute that can be understood as the identity of the current SQL mapping file.

In traditional SqlSession development, namespace attribute of mapper element and ID attribute of subelement combine to ensure the unique identity of SQL statement.


      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="userMapper">
    <select id="selectList" resultType="User">
        SELECT * FROM `user`
    </select>
</mapper>
Copy the code

List<User> userList = sqlSession.selectList("userMapper.selectList");
Copy the code

In the Mapper interface development of SqlSession, the namespace attribute of a Mapper element must be named as the fully qualified class name of the corresponding Mapper interface, and the lower subelement must correspond to method 1 and method 1 in the corresponding Mapper interface.

package com.example.mapper;
/ /... A little...
public interface UserMapper {

    /** * Query the user list *@return/ * /
    List<User> selectList(a);
	
}
Copy the code


      
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper">
    <! -- List<User> selectList(); -->
    <select id="selectList" resultType="User">
        SELECT * FROM `user`
    </select>
</mapper>
Copy the code

Teacher zha said: Mapper interface development is the last part of our last part of the SqlSession usage, later is also the main writing method, it is very easy to understand, GKD master.

The select element

In every project, queries are the most frequently applied and the most difficult part to apply. In SQL mapping files, the SELECT element is used to write query SQL and is one of the most commonly used elements in MyBatis.

The SELECT element has many attributes that can be used to configure the behavior of each statement in great detail.

Id A unique identifier in the namespace

During Mapper interface development, the ID value must be the same as the name of the corresponding method on the interface
ParameterType parameterType of the incoming SQL statement

Can be a fully qualified class name or alias for the parameter type

During Mapper interface development, parameterType must be consistent with the parameterType of the corresponding method on the interface
ResultType Type of the value returned by the SQL statement (see the resultMap element section for details)

Can be a fully qualified class name or alias of the return value type

During Mapper interface development, the resultType value must be consistent with the return value type of the corresponding method in the interface

Note: If the return value type is a collection, the resultType value should be represented as the generic type of the collection, not the collection type.

Next, Zha teacher through a few examples to guide you to master the use of the SELECT element.

Teacher Zha said: Teacher Zha just introduced the most frequently used attributes, if you want to know more about the meaning of attributes, you can go to the official website to check.

User name Query

In the first meeting MyBatis | quick start “on the basis of article database, we first to implement a fuzzy query based on user name.

First, we add a method to the Mapper interface.

public interface UserMapper {

    /** * fuzzy query * based on user name@paramName User name *@returnUser list */
    List<User> selectByName(String name);

}
Copy the code

We then add a query element corresponding to this method to the SQL mapping file.

<! -- List<User> selectByName(String name); -->
<! #{parameter name} -->
<select id="selectByName" parameterType="string" resultType="User">
    select * from user where name like concat('%', #{name}, '%')
</select>
Copy the code

Test it out:

class TestMyBatis {

    @Test
    void testSelectByName(a) throws IOException {
        // Get the SqlSession object
        try (SqlSession sqlSession = MyBatisUtils.openSession()) {

            // Get the Mapper interface
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            / / SQL execution
            List<User> userList = userMapper.selectByName("J");

            // Iterate over the data
            userList.forEach(System.out::println);
        } catch(Exception e) { e.printStackTrace(); }}}Copy the code

Console output:

-- Output SQL statement
select * from user where name like concat(The '%',? .The '%')
Copy the code

User [id=1, name=Jone, age=18, [email protected]]
User [id=2, name=Jack, age=20, [email protected]]
Copy the code

The difference between #{} and ${}

In the MyBatis log output from the console, we can see that the last SQL executed is the form of SQL that we wrote to solve SQL injection attacks in traditional JDBC development.

The output is this way because we use the form #{} to use the parameter. #{} represents a placeholder, which can accept simple type values or POJO property values. We use #{} to set values in preparedStatement to placeholders. Automatic Java and JDBC type conversions. #{} effectively prevents SQL injection.

Select * from user where name like ‘%#{name}%’ is incorrect.

In fact, there is a placeholder in MyBatis: ${}, but basically not use, as for the reason, we will try to know.

<! -- List<User> selectByName(String name); -->
<! ${parameter name} can be placed directly in a string -->
<select id="selectByName" parameterType="string" resultType="User">
    select * from user where name like '%${name}%'
</select>
Copy the code

Console output:

-- Output SQL statement
select * from user where name like '%J%'
Copy the code

Now you know why? To summarize the differences (interview questions) :

In the use of# {}Parameter syntax when MyBatis is createdPreparedStatementParameter placeholders, and securely setting parameters with placeholders (like using? ) [1]
In the use ofThe ${}When, MyBatis will put SQLThe ${}Replace it with the value of the corresponding variable. Suitable for inserting an unescaped string directly.
Using #{} can effectively prevent SQL injection and improve system security.

Multi-parameter query

ParameterType MyBatis query element parameterType MyBatis query element parameterType MyBatis query element parameterType MyBatis query element parameterType

In fact, it is very simple, and there are three popular methods:

Encapsulate multiple parameters into POJO/custom objects
Encapsulate multiple parameters into a Map/List collection
Annotate multiple parameters with @param annotations

We use a case to feel the difference between different ways: Case requirements: query the list of users by user name, age

Encapsulated into a POJO

First, we add a method to the Mapper interface.

public interface UserMapper {

    /** * Query by username and age *@paramUser User information *@returnUser list */
    List<User> selectByUser(User user);

}
Copy the code

We then add a query element corresponding to this method to the SQL mapping file.

<! -- List<User> selectByUser(User user); -->
<! When parameterType is an object, #{attribute name} returns the value of an attribute in the object.
<select id="selectByUser" parameterType="User" resultType="User">
    select
        * 
    from
        user 
    where 
        name like concat('%', #{name}, '%')
        and age = #{age}
</select>
Copy the code

Test it out:

class TestMyBatis {

    @Test
    void testSelectByUser(a) throws IOException {
        // Get the SqlSession object
        try (SqlSession sqlSession = MyBatisUtils.openSession()) {

            // Get the Mapper interface
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            / / SQL execution
            User user = new User();
            user.setName("J");
            user.setAge(20);
            List<User> userList = userMapper.selectByUser(user);

            // Iterate over the data
            userList.forEach(System.out::println);
        } catch(Exception e) { e.printStackTrace(); }}}Copy the code

Console output:

-- Output SQL statement
select * from user where name like concat(The '%',? .The '%') and age = ?
Copy the code

User [id=2, name=Jack, age=20, [email protected]]
Copy the code

Encapsulate to the Map collection

Teacher Zha said: Teacher Zha personally prefers this one, Map collection is always so “versatile”.

First, we add a method to the Mapper interface.

public interface UserMapper {

    /** * Query by username and age *@paramParams conditional argument *@returnUser list */
    List<User> selectByMap(Map<String, Object> params);

}
Copy the code

We then add a query element corresponding to this method to the SQL mapping file.

<! -- List<User> selectByMap(Map<String, Object> params); -->
<! When parameterType is set to Map, #{Map key} can retrieve the value of the set.
<select id="selectByMap" parameterType="map" resultType="User">
    select
        * 
    from
        user 
    where 
        name like concat('%', #{name}, '%')
        and age = #{age}
</select>
Copy the code

Test it out:

class TestMyBatis {

    @Test
    void testSelectByMap(a) throws IOException {
        // Get the SqlSession object
        try (SqlSession sqlSession = MyBatisUtils.openSession()) {

            // Get the Mapper interface
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            / / SQL execution
            Map<String, Object> params = new HashMap<>();
            params.put("name"."J");
            params.put("age".20);
            List<User> userList = userMapper.selectByMap(params);

            // Iterate over the data
            userList.forEach(System.out::println);
        } catch(Exception e) { e.printStackTrace(); }}}Copy the code

Console output:

-- Output SQL statement
select * from user where name like concat(The '%',? .The '%') and age = ?
Copy the code

User [id=2, name=Jack, age=20, [email protected]]
Copy the code

@ Param annotations

The @param annotation is a little different from the above two methods. If you use this annotation, parameterType is no longer required to be specified manually. It uses ordinary type parameters (int, double, String… These are common type parameters, whereas objects and collections are not) methods are generally recommended.

First, we add a method to the Mapper interface.

public interface UserMapper {

    /** * Query by username and age *@paramName User name *@paramThe age age *@returnUser list */
    // @param (" parameter name ") the parameter name passed in the annotation is the placeholder name to use
    List<User> selectByParam(@Param("name") String name, @Param("age") Integer age);

}
Copy the code

We then add a query element corresponding to this method to the SQL mapping file.

<! -- List<User> selectByParam(@Param("name") String name, @Param("age") Integer age); -->
<! #{@param} can be used to retrieve the value of the corresponding parameter -->
<select id="selectByParam" resultType="User">
    select
        * 
    from
        user 
    where 
        name like concat('%', #{name}, '%')
        and age = #{age}
</select>
Copy the code

Test it out:

class TestMyBatis {

    @Test
    void testSelectByParam(a) throws IOException {
        // Get the SqlSession object
        try (SqlSession sqlSession = MyBatisUtils.openSession()) {

            // Get the Mapper interface
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            / / SQL execution
            List<User> userList = userMapper.selectByParam("j".20);

            // Iterate over the data
            userList.forEach(System.out::println);
        } catch(Exception e) { e.printStackTrace(); }}}Copy the code

Console output:

-- Output SQL statement
select * from user where name like concat(The '%',? .The '%') and age = ?
Copy the code

User [id=2, name=Jack, age=20, [email protected]]
Copy the code

Use attention

Teacher Zha said: More than three common type parameters are best encapsulated as objects or Map input parameters (especially in general add and modify operations, the field is more convenient to encapsulate into objects), while fixed parameter business methods, best use @param input parameters (this method is more flexible, code readability is high, You can clearly see what the parameters are for this interface method, and for fixed interface methods, the parameters are usually fixed, so you can use direct parameters instead of encapsulating the object. For example, the method of changing personal password, deleting users according to ID, and viewing user details according to ID can all be adopted in this way.)

SQL elements

You often face the problem of duplicate SQL, especially query-type SQL, in the same SQL mapping file. The effect is as follows:

<select id="selectByName" resultType="User">
    select 
        id, name, age, email
    from
        user
    where name like concat('%', #{name}, '%')
</select>
Copy the code

<select id="selectById" resultType="User">
    select 
        id, name, age, email
    from
        user
    where id = #{id}
</select>
Copy the code

Using SQL elements allows us to reuse some SQL statements.

<! SQL > define a reusable SQL segment
<sql id="selectUser">
    select 
        id, name, age, email
    from
        user
</sql>

<select id="selectByName" resultType="User">
    <! SQL > select * from 'SQL';
    <include refid="selectUser"/>
    where name like concat('%', #{name}, '%')
</select>

<select id="selectById" resultType="User">
    <include refid="selectUser"/>
    where id = #{id}
</select>
Copy the code

reference

[1] MyBatis website. XML mapping file [EB/OL]. mybatis.org/mybatis-3/z… . 2020-12-26

Afterword.

In this article, the select element is the focus, Cha teacher lists a lot of examples, you must sample code complete “copy” + “think” again, so as to achieve the second and third step of technical learning cha teacher said.

Teacher Zha said: For the learning of technology, teacher Zha has always followed the following steps: With a simple demo to let it run first, and then learn it the most commonly used API and configuration can let yourself up, finally, on the basis of proficiency in the spare time reading the source to try to make myself to be able to see clearly its running mechanism, part of the cause of the problem, at the same time, draw lessons from these technology to enhance your own code level.

So in the teacher’s article, the early basic are small white, only interspersed with a very small amount of source research. Of course, such as the small white update, you still like, the late will not regularly dedicated to part of the technology of the source code analysis.

New MyBatis | SQL query element mapping file

preface

Mapper elements

The select element

User name Query

The difference between #{} and ${}

Multi-parameter query

Encapsulated into a POJO

Encapsulate to the Map collection

@ Param annotations

Use attention

SQL elements

reference

Afterword.

Related Posts

How to implement RPC call protection

Go Tutorial -Goroutines | Go Theme month

Actual combat: ten minutes to achieve the authority framework based on the separation of the front and back ends of JWT