MyBaits - CRUD operations, alias configuration, attribute configuration, query result mapping, Mapper components, parameter processing, annotation development for MyBatis

Catalog

CRUD Operation of MyBatis
2. MyBatis Expansion
3. Development of MyBatis using annotations

MyBatis Documentation

CRUD Operation of MyBatis

Writing interfaces

public interface UserMapper {
    // Query all users
    List<User> getUserList();

    // Query users based on id
    User getUser(int id);

    // Update user information based on id
    int updateUser(User user);

    // Insert a user message
    int insertUser(User user);

    // Delete users based on id
    int deleteUser(int id);
}

Write mybatis mapping file/Mapper file

select element: SQL for queries
-id attribute: Unique identifier used to identify an SQL statement
-parameterType property: Indicates the type of parameter required to execute the SQL statement (not recommended), and MyBatis can deduce it by itself
-resultType property: what type of object to encapsulate each row of data in the result set

insert element:
Get the primary key for automatic growth
useGeneratedKeys property: Whether the autogenerated primary key needs to be returned
keyProperty: Set the autogenerated primary key value to which property of the object

<?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">
<!--Namespace, package-like concept: namespace: bind a corresponding Dao/Mapper interface-->
<mapper namespace="com.sunny.dao.UserMapper">
    <!--
        select element: SQL for queries
            -id attribute: Unique identifier used to identify an SQL statement
            -parameterType property: Indicates the type of parameter required to execute the SQL statement (not recommended), and MyBatis can deduce it by itself
            -resultType property: what type of object to encapsulate each row of data in the result set
    -->
    <select id="getUserList" resultType="com.sunny.domain.User">
        SELECT * FROM user;
    </select>

    <!-- #{id} corresponds to the value passed from selectOne in the test method-->
    <select id="getUser" parameterType="int" resultType="com.sunny.domain.User">
        SELECT * FROM user WHERE id = #{id};
    </select>

    <update id="updateUser" parameterType="com.sunny.domain.User">
        UPDATE user SET name = #{name}, pwd = #{pwd} WHERE id = #{id};
    </update>

    <!--
        Here parameterType can be written without
        insert element:
        	Get the primary key for automatic growth
            useGeneratedKeys property: Whether the autogenerated primary key needs to be returned
            keyProperty: Set the autogenerated primary key value to which property of the object
    -->

    <insert id="insertUser" parameterType="com.sunny.domain.User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user (id, name, pwd) VALUES (null , #{name}, #{pwd});
    </insert>

    <delete id="deleteUser" parameterType="int">
        DELETE FROM user WHERE id = #{id};
    </delete>

</mapper>

UserMapperTest

public class UserMapperTest {

    /**
     * Custom Log Operations
     */
    private static Logger logger = Logger.getLogger(UserMapperTest.class);

    @Test
    public void testLogger() throws Exception {
        // If the log output level is INFO, the output
        if (logger.isInfoEnabled()) {
            logger.info("Bank Transfer Operation");
        }
        if (logger.isDebugEnabled()) {
            logger.debug("query data base");
        }
        if (logger.isTraceEnabled()) {
            logger.trace("Connect to database");
        }
        if (logger.isTraceEnabled()) {
            logger.trace("implement SQL");
        }
        if (logger.isDebugEnabled()) {
            logger.debug("Transfer accounts");
        }
        if (logger.isInfoEnabled()) {
            logger.info("Bank Transfer Successful");
        }
    }


    /**
     * Query all users
     *
     * @throws IOException
     */
    @Test
    public void testQueryUserList() throws IOException {

        //1. Get the sqlSession object
        // SqlSession sqlSession = MybatisUtils.getSqlSession();

        //1. Load the MyBatis global configuration file from the classpath path path: mybatis-config.xml
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        //2. Create a sqlSessionFactory object, like a DataSource
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3. Create sqlSession objects, like Connection
        SqlSession sqlSession = factory.openSession();
        //4. Specific operation
        // Execute SQL (Mode 1)
        /*
         Execute SQL: namespace in UserMapper.xml: Binding a corresponding Dao/Mapper interface is equivalent to the implementation class of the UserMapper interface.
         Using UserMapper.class here is Interface-oriented programming, which is equivalent to getting the implementation class of UserMapper and calling the interface method through the implementation class
        */
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserList();

        // Mode 2
//         List<User> userList = sqlSession.selectList("com.sunny.dao.UserDao.getUserList");

        for (User user : userList) {
            System.out.println(user);
        }

        // Close sqlSession
        sqlSession.close();
    }

    /**
     * Query users with id 1
     *
     * @throws IOException
     */
    @Test
    public void testQueryOneUser() throws IOException {
        // Load Global Profile
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        // Building sqlSessionFactory factory class object
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        // Create SqlSession object from sqlSession class object
        SqlSession sqlSession = factory.openSession();
        // sqlSession is equivalent to Connection to execute SQL statements
        //User user = sqlSession.selectOne("com.sunny.dao.UserMapper.getUser", 1L);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUser(1);
        System.out.println(user);

        sqlSession.close();
    }

    /**
     * Modify the contents of a statement
     *
     * @throws Exception
     */
    @Test
    public void testUpdateUser() throws Exception {
        User user = new User();
        user.setId(4);
        user.setName("Soil and soil elegance");
        user.setPwd("10004");
        // Get SqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        // Mode 1
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int update = mapper.updateUser(user);

        // Mode 2 (Previously, writing was not recommended)
        // int update = sqlSession.update("com.sunny.dao.UserMapper.update",user);

        if (update > 0) {
            System.out.println("Successfully modified:" + update + " Bar statement!");
        }
        // Additions and deletions must commit transactions
        sqlSession.commit();
        // close resource
        sqlSession.close();
    }


    /**
     * Insert a piece of data
     * @throws Exception
     */
    @Test
    public void testInsertUser() throws Exception {
        // Get sqlSession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        /*
         Execute SQL: namespace in UserMapper.xml: Binding a corresponding Dao/Mapper interface is equivalent to the implementation class of the UserMapper interface.
         Using UserMapper.class here is Interface-oriented programming, which is equivalent to getting the implementation class of UserMapper and calling the interface method through the implementation class
        */
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User u = new User("coder", "10007");
        int count = mapper.insertUser(u);
        if (count > 0){
            System.out.println("Insert Successful!");
        }
        // Submit Transaction
        sqlSession.commit();
        // close resource
        sqlSession.close();

        System.out.println(u);
    }

    /**
     * Delete users based on id
     * @throws Exception
     */
    @Test
    public void testDeleteUser() throws Exception{
        // Load mybatis global configuration file
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        // Build SqlSessionFactory Factory Object
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        // Create SqlSession object from factory object
        SqlSession sqlSession = factory.openSession();
        // Get mapper from sqlSession object
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // Calling interface methods through mapper
        int count = mapper.deleteUser(5);
        if (count > 0){
            System.out.println("Delete succeeded");
        }
        // Submit Transaction
        sqlSession.commit();
        // close resource
        sqlSession.close();
    }
}

2. MyBatis Expansion

Jump to Directory

1. Alias Configuration Processing

Jump to Directory

Query operations in Mapper.xml

    <select id="getUserList" resultType="com.sunny.domain.User">
        SELECT * FROM user;
    </select>

    <!-- #{id} is equivalent to the value passed in from selectOne in the test method-->
    <select id="getUser" resultType="com.sunny.domain.User">
        SELECT * FROM user WHERE id = #{id};
    </select>

Note: When writing resultType result types, use the fully qualified class name com.sunny.domain.User, which is cumbersome and can use type aliases

Using aliases (typeAlias) is too long for a fully qualified class name. Alias in MyBatis are case insensitive

1.2, Custom Alias

Jump to Directory

Define aliases in the global configuration file mybatis-config.xml

Aliasing a class directly

<typeAlias type="com.sunny.domain.User" alias="User" />

Alias all classes in a package (including subpackages)
```
<package name="com.sunny.domain"/>
```
Write to a domain package and automatically alias the class names in the package. The default alias is the lowercase first letter of the class name (case insensitive).
The @Alias annotation can be used to set the alias of a class (highest priority)

    <!--Type Alias-->
    <!--
        Use the first option when there are fewer entity classes.
        If you have a large number of entity classes, the second option is recommended.
    -->
    <typeAliases>
    <!--<typeAlias type="com.sunny.domain.User" alias="User" />-->
    <!--
        Write to a domain package and automatically alias the class names in the package. The default alias is the lowercase first letter of the class name (case insensitive).
    -->
        <package name="com.sunny.domain"/>
    </typeAliases>

The first two sets the use of aliases:

Use the first option when there are fewer entity classes.
If you have a large number of entity classes, the second option is recommended.
Query operations in Mapper.xml (using aliases)

    <select id="getUserList" resultType="com.sunny.domain.User">
        SELECT * FROM user;
    </select>

    <!-- #{id} is equivalent to the value passed in from selectOne in the test method-->
    <select id="getUser" resultType="com.sunny.domain.User">
        SELECT * FROM user WHERE id = #{id};
    </select>

Note: Attribute settings, log settings, alias settings in the < configuration > tag in the global configuration file are in strict order and cannot be disrupted.

eg:

UserMapper.java

public interface UserMapper {
    // Query only the id and name columns of the User table
    List<User> queryUserByIdAndName();

    List<Map<Integer, Object>> queryUserByIdAndName2();
}

UserMapperTest

public class UserMapperTest {

    /**
     * Query only id and name columns in the User table
     * @throws Exception
     */
    @Test
    public void testQueryUserByIdAndName(){
        /*
            This queries some columns in the User table, and the other columns display null s, so you can
         */
        /*SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.queryUserByIdAndName();
        for (User user : users) {
            System.out.println(user);
        }*/

        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Map<Integer, Object>> maps = mapper.queryUserByIdAndName2();
        for (Map<Integer, Object> map : maps) {
            System.out.println(map);
        }
    }
}

UserMapper.xml

<?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">
<!--Namespace,Package-like concepts: namespace:Bind a corresponding Dao/Mapper Interface-->
<mapper namespace="com.sunny.dao.UserMapper">

    <!--Query only user Table id and name column-->
<!--    <select id="queryUserByIdAndName" resultType="User">-->
<!--        SELECT id,name FROM user;-->
<!--    </select>-->

    <select id="queryUserByIdAndName2" resultType="map">
        SELECT id, name FROM user;
    </select>

</mapper>

1.3. Aliases that come with the system

Jump to Directory

Typically used for interface method return value types, ResultType can be set to int (default) and _int (alias) in the Mapper.xml file when the interface method returns to int, which is case insensitive.

2. Attribute Configuration Processing

Jump to Directory

property element

Modify mybatis-config.xml file, add new

    <!--Property Configuration-->
    <properties>
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
        <property name="username" value="root"/>
        <property name="password" value="1111"/>
    </properties>

In the database configuration, use the $expression to get the property value

<!--Connection Pool-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>

db.properties file

Add db.properties file:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8
username=root
password=1111

Modify the mybatis-config.xml file:

<!--Property Configuration-->
    <properties resource="db.properties"/>

3. Query Result Mapping

Jump to Directory

The query method in CRUD sets MyBatis to package each row of data in the result set as a User object directly by setting the alias resultType="User". At this time, one-to-one correspondence must be required between the column name in the data table and the attribute name of the object.
If the column names in the table do not match the object property names, the solution is?

3.1. How to alias fields

Jump to Directory

Set column names in relational tables to have the same names as attributes in objects

<select id="queryUserList" resultMap="BaseResultMap">
    SELECT u_id AS id, u_name AS name, u_pwd AS pwd FROM user1;
</select>

3.2, resultMap element

Jump to Directory

Problem to solve: Column names in result sets (tables) do not match attribute names in objects
Solution: Use the resultMap element

resultMap element: mapping of result set objects
  id property: the unique name of the resultMap in the current Mapper file
  Typee property: What type of object encapsulates each row of data in the result set
  
  Child elements:
     id element: function is the same as result, if primary key, id element is recommended to improve performance
     result element: Which attribute in the matching object corresponds to which column in the table

<!--Namespace,Package-like concepts: namespace:Bind a corresponding Dao/Mapper Interface-->
<mapper namespace="com.sunny.dao.UserMapper">

    <!--Query total number of users-->
    <select id="queryUserCount" resultType="int">
        SELECT COUNT(id) FROM user1;
    </select>
    
    <resultMap id="BaseResultMap" type="User">
        <result column="u_id" property="id" />
        <result column="u_name" property="name" />
        <result column="u_pwd" property="pwd" />
    </resultMap>

    <select id="queryUserList" resultMap="BaseResultMap">
        select u_id, u_name, u_pwd from user1;
    </select>

</mapper>

4. Mapper Components

Jump to Directory

Mapper component: Mapper interface + Mapper file

There are two ways to get SQL and execute it through sqlSession. Here are the advantages and disadvantages of the two ways:

    /**
     * Query users with id 1
     *
     * @throws IOException
     */
    @Test
    public void testQueryOneUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        // Usage 1: Find SQL and execute it
        //User user = sqlSession.selectOne("com.sunny.dao.UserMapper.getUser", 1L);

        // Usage 2: Find SQL and execute it
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // Actually the underlying method is sqlSession, note that sqlSession calls the CRUD method and can only pass one parameter
        User user = mapper.getUser(1);

        System.out.println(user);
        sqlSession.close();
    }

There are several questions about how to find SQL elements using namespace.id (mode one):

namespace.id uses the String type, and once an error is written, it cannot be reported until the code is run.
The actual parameter type passed in cannot be checked.
The code templates for each operation are similar.

In contrast to Mode 2, I think the only advantage of this method is that there is no need to define an interface method in the interface, and it is directly related to the mapping file.

Solution: Use the Mapper interface, similar to the DAO interface, to define each method of operation in the Mapper interface. (Used above, summarized here)

4.1, Mapper interface

Jump to Directory

Using the Mapper component:

Create a Mapper interface (similar to the DAO interface), which requires:
(1) The fully qualified name of this interface corresponds to the namespace of the Mapper file;
(2) There is a one-to-one correspondence between the methods in this interface and the SQL elements in the Mapper file;

 The name of the method corresponds to the id of the SQL element;
 The parameter type of the method corresponds to the paramterType type defined in the SQL element (-generally not written);
 The return type of the method corresponds to the resultType/resultMap type defined in the SQL element.

Create SqlSession;
A Mapper object is obtained by the SqlSession.getMapper(XxxMapper.class) method;
Call a method on the Mapper object to complete the CURD of the object;

5. Parameter Processing

Jump to Directory
Note: CRUD methods in SqlSession can only pass one misery at most. Even in Mode 2 above, the underlying layer still uses SqlSession to call CRUD methods. How can I solve that only one parameter can be passed in CRUD methods?

Mode 1: Encapsulate parameters into a JavaBean and pass JavaBean objects as parameters

Mode 2: How Map encapsulates parameter_key pairs

Mode 3: Use Note @Param
The string in the Param comment is the key in the Map

ClientMapper.java

public interface ClientMapper {
    // Mode 1: Encapsulate multiple parameters into a JavaBean
    Client login1(LoginVO vo);

    // Mode 2: Encapsulate multiple parameters using Map objects
    Client login2(Map<String, Object> paramMap);

    // Mode 3: Using Param annotations, the principle is mode 2. The string in the Param annotation is the key in the Map
    Client login3(@Param("username") String username, @Param("password") String password);
}

ClientMapper.xml

<?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">
<!--Namespace,Package-like concepts: namespace:Bind a corresponding Dao/Mapper Interface-->
<mapper namespace="com.sunny.dao.ClientMapper">

    <select id="login1" parameterType="LoginVo" resultType="Client">
        SELECT id, username, password FROM client WHERE username = #{username} AND password = #{password};
    </select>

    <select id="login2" parameterType="map" resultType="Client">
         SELECT id, username, password FROM client WHERE username = #{username} AND password = #{password};
    </select>

    <select id="login3" resultType="Client">
        SELECT id, username, password FROM client WHERE username = #{username} AND password = #{password};
    </select>

</mapper>

ClientMapperTest.java

public class ClientMapperTest {
   /**
    * Mode 1: Encapsulate using JavaBean
    */
   @Test
   public void testLogin1(){
       LoginVO vo = new LoginVO("zy", "1111");

       SqlSession sqlSession = MybatisUtils.getSqlSession();
       ClientMapper mapper = sqlSession.getMapper(ClientMapper.class);
       // mapper calls the method, but the bottom level is still sqlSession to call the select method
       Client client = mapper.login1(vo);
       System.out.println(client);
       sqlSession.close();
   }

   /**
    * Encapsulate using Map
    */
   @Test
   public void testLogin2(){

       Map<String, Object> paramMap = new HashMap<String, Object>();
       paramMap.put("username", "zy");
       paramMap.put("password", "1111");

       SqlSession sqlSession = MybatisUtils.getSqlSession();
       ClientMapper mapper = sqlSession.getMapper(ClientMapper.class);
       // The mapper calls the method, but the bottom level is still sqlSession to call the select method.
       // Note: The method called by sqlSession can only pass one parameter.
       Client client = mapper.login2(paramMap);
       System.out.println(client);
       sqlSession.close();
   }

   /**
    * Mode 3: Use Param notation, principle is mode 2
    */
   /*
       HelloWorld
    */
   @Test
   public void testLogin3(){

       SqlSession sqlSession = MybatisUtils.getSqlSession();
       ClientMapper mapper = sqlSession.getMapper(ClientMapper.class);
       // The mapper calls the method, but the bottom level is still sqlSession to call the select method.
       // Note: The method called by sqlSession can only pass one parameter.
       Client client = mapper.login3("zy", "1111");
       System.out.println(client);
       sqlSession.close();
   }
}

6. #and $

Jump to Directory

Use #

Preparing: SELECT id, username, password FROM client WHERE username = ? AND password = ?; 
Parameters: zy(String), 1111(String)

Use $

Preparing: SELECT id, username, password FROM client WHERE username = root AND password = 1111; 
Parameters:

1. Differences and similarities between the two
Same: you can get information from objects through #and $

Different:
1. Will the parameters passed with #be converted to placeholders first? Set the value by setting the placeholder parameters (the values will be quoted in single quotes)
2. Use the parameter passed in $to directly parse the parsed data as part of the SQL statement

2. Inference

#: Like using PrepareStatement, it does not cause SQL injection problems and is relatively safe
$: Statement, for example, can cause SQL injection problems and is relatively unsafe.

3. How to Choose

Where do you need to set placeholder parameters, use # all, which is what might happen after SQL?
If what we write should be part of SQL, use $, such as sorting, grouping queries, and so on.

3. Development of MyBatis using annotations

Jump to Directory

Previously MyBatis was developed using XML to configure, or directly with annotations. When SQL is complex, XML is recommended for configuring

Note: If you configure with annotations, you do not need the Mapper.xml file, but instead register the map file in the main configuration file:

<mapper class="com/sunny/dao/UserMapper" />

Add annotations on interface methods

Insert comment
Jump to Directory

    // Insert a user message
    @Insert("INSERT INTO user (id, name, pwd) VALUES (null , #{name}, #{pwd})")
    @Options(useGeneratedKeys = true, keyProperty = "id") //Generate Primary Key
    int insertUser(User user);

Delete Notes
Jump to Directory

    // Delete users based on id
    @Delete("DELETE FROM user WHERE id = #{id}")
    int deleteUser(int id);

Update comment
Jump to Directory

    // Update user information based on id
    @Update("UPDATE user SET name = #{name}, pwd = #{pwd} WHERE id = #{id}")
    int updateUser(User user);

Select comment
Jump to Directory

    // Query a user
    @Select("SELECT id AS u_id, name AS u_name, pwd AS u_pwd FROM user WHERE id = ${id}")
    @ResultMap("BaseResultMap")
    User getOneUser(int id);

    // Query all users
    @Select("SELECT id AS u_id, name AS u_name, pwd AS u_pwd FROM user")
    @Results(id = "BaseResultMap", value = {
            @Result(column = "u_id", property = "id"),
            @Result(column = "u_name", property = "name"),
            @Result(column = "u_pwd", property = "pwd")
    })
    List<User> getUserList();

White Camel

175 original articles published, 107 praised, 140,000 visits+

Private letter follow

Posted by MorganM on Mon, 02 Mar 2020 17:34:33 -0800

Programmer Group

MyBaits - CRUD operations, alias configuration, attribute configuration, query result mapping, Mapper components, parameter processing, annotation development for MyBatis

Catalog

MyBatis Documentation

CRUD Operation of MyBatis

2. MyBatis Expansion

1. Alias Configuration Processing

1.2, Custom Alias

1.3. Aliases that come with the system

2. Attribute Configuration Processing

property element

db.properties file

3. Query Result Mapping

3.1. How to alias fields

3.2, resultMap element

4. Mapper Components

4.1, Mapper interface

5. Parameter Processing

6. #and $

3. Development of MyBatis using annotations

Hot Keywords