Mybatis of SSM framework -- detailed explanation of DAO layer, mapping file and core configuration file

catalogue

1, Implementation of DAO layer of MyBatis

1.1 traditional implementation mode

1.2 implementation mode of interface agent

1.2.1 introduction to agent development mode

1.2.2 proxy implementation

2, MyBatis mapping file

2.1 dynamic sql statements

2.1.1 overview of dynamic sql statements

2.1.2 disadvantages of static sql statements

2.1.3 if of dynamic sql

  2.1.4 foreach of dynamic sql

2.2SQL fragment extraction

3, MyBatis core profile drill down

3.1 typehandlers label

3.1.1 typehandlers overview

3.1.2 default processor type

3.1.3 custom type processor

3.2 plugins tab

1, Implementation of DAO layer of MyBatis

The previous database operations were written directly in the test class. In the development process, the direct operation code for the database should be placed in the Dao layer, so let's see how to implement the Dao layer in MyBatis.

We first create a new project, configure the web environment, and import the relevant jarb package coordinates (mysql, mybatis, junit, etc.) into pom.xml.

1.1 traditional implementation mode

The first step is to write the UserDao interface and the corresponding implementation class,

interface UserMapper {
    public List<User> findAll() throws IOException;
}

class UserMapperImpl implements UserMapper{
    public List<User> findAll() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");//Get core profile
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);//Get factory object
        SqlSession sqlSession = sqlSessionFactory.openSession();//Get sqlSession object
        List<User> userList = sqlSession.selectList("userMapper.findAll");//Find the executed statement by mapping the sql statement id of the file
        return userList;
    }
}

The second step is to write test functions in the Service layer,

class ServiceDemo {
    public static void main(String[] args) throws IOException {
        UserMapper userMapper=new UserMapperImpl();//Manually create dao layer objects
        List<User> all = userMapper.findAll();
        System.out.println(all);
    }
}

1.2 implementation mode of interface agent

1.2.1 introduction to agent development mode

The agent development method of Mybatis is used to realize the development of DAO layer, which is the mainstream used by enterprises.

Mapper interface development method only requires programmers to write mapper interface (equivalent to Dao interface). Mybatis framework creates the dynamic proxy object of the interface according to the interface definition. The method body of the proxy object is the same as that of the Dao interface above.

Mapper interface development needs to follow the following specifications:

  1. The namespace in the Mapper.xml file is the same as the fully qualified name of the mapper interface
  2. The Mapper interface method name is the same as the id of each statement defined in Mapper.xml
  3. The input parameter type of Mapper interface method is the same as the parameterType of each sql defined in mapper.xml
  4. The output parameter type of Mapper interface method is the same as the resultType of each sql defined in mapper.xml

That is, the relationship shown in the following figure:

1.2.2 proxy implementation

In the first step, we assign the fully qualified name of the mapper interface to the namespace in UserMapper.xml,

<!--appoint sql The namespace of the statement-->
<mapper namespace="Dao.UserMapper">
    ...
</mapper>

In the second step, we assign the method name of the mapper interface to the id of the sql statement defined in UserMapper.xml,

<mapper namespace="Dao.UserMapper">
        <!--according to id Make a query-->
        <select id="findById" parameterType="int" resultType="user">
                select * from user where id=#{id}
        </select>
</mapper>

In step 3, we assign the input parameter of the mapper interface to the parameterType type in UserMapper.xml,

<mapper namespace="Dao.UserMapper">
        <!--according to id Make a query-->
        <select id="findById" parameterType="int" resultType="user">
                select * from user where id=#{id}
        </select>
</mapper>

Step 4: we assign the output parameter of the mapper interface to the resultType in UserMapper.xml,

<mapper namespace="Dao.UserMapper">
        <!--according to id Make a query-->
        <select id="findById" parameterType="int" resultType="user">
                select * from user where id=#{id}
        </select>
</mapper>

Step five, we test,

public static void main(String[] args) throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);//Get UserMapper object
    User user = userMapper.findById(1);
    System.out.println(user);
}

In this way, we can only write the interface, then configure the mapper mapping file corresponding to the interface, and MyBatis generates dynamic proxy objects for us to use.

2, MyBatis mapping file

2.1 dynamic sql statements

2.1.1 overview of dynamic sql statements

In the mapping file of Mybatis, our SQL is relatively simple. Sometimes when the business logic is complex, our SQL changes dynamically. At this time, our SQL can not meet the requirements in the previous study.

Dynamic SQL is a programming method to generate and execute SQL statements at run time. Dynamic is relative to static. Static SQL refers to those well-defined fixed SQL statements that have been included in the code at the time of code compilation.

2.1.2 disadvantages of static sql statements

First, let's build a database environment, create entity classes and corresponding data tables, and import the corresponding jar package coordinates. Then, we create a Mapper mapping interface with a query method according to conditions,

public interface UserMapper {
    public List<User> findByCondition(User user);
}

Next, we configure the mapping file to correspond to the method parameters in our interface one by one. We don't write the conditions after where here,

<!--appoint sql The namespace of the statement-->
<mapper namespace="Mapper.UserMapper">
        <!--Query operation, for the current sql Statement specifies a id Name, the type of returned result set is User-->
        <select id="findByCondition" resultType="user" parameterType="user">
                select * from user where id=#{id} and username=#{username} and password=#{password}
        </select>
</mapper>

Then we write test classes,

@Test
public void test1() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = mapper.findByCondition(new User(1, "tz", "0203"));
    System.out.println(userList);
}

Operation results:

  However, often the user's data is entered interactively. It is possible that the user does not give a certain condition, for example, password is equal to anything, but at this time, if we write the condition dead, we will finally get such a statement,

The result of such a query must be empty, because no password is equal to null, and the user's intention is to let us execute such an sql statement,

select * from user where id=1 and username="tz"

That is, there is no limit on the password attribute, which can be equal to any. So we need to use our dynamic sql statement at this time.

2.1.3 if of dynamic sql

We can use different sql statements to query according to different values of entity classes.

For example, if the id is not empty, you can query according to the id. if the username is different, you should add the user name as a condition. This situation is often encountered in our multi condition combined query. We can write dynamic sql statements in the mapping file,

<mapper namespace="Mapper.UserMapper">
        <!--Query operation, for the current sql Statement specifies a id Name, the type of returned result set is User-->
        <select id="findByCondition" resultType="user" parameterType="user">
                select * from user
                <where>
                        <if test="id!=0">
                                and id=#{id}
                        </if>
                        <if test="username!=null">
                                and username=#{username}
                        </if>
                        <if test="password!=null">
                                and password=#{password}
                        </if>
                </where>
        </select>
</mapper>

In this way, the mapping file will dynamically modify the corresponding sql statements according to the existence of the values we give, so as to meet the needs of different users  

  2.1.4 foreach of dynamic sql

Perform sql splicing operations circularly, for example:

SELECT * FROM USER WHERE id IN (1,2,3)

The first step is to write a method in the interface to obtain the User collection according to the id array,

public List<User> findByIds(List<Integer> ids);

  Step 2: in the mapping file, we write query operations according to the interface,

<mapper>
        <!--According to the given id Collection to find users-->
        <select id="findByIds" resultType="user" parameterType="list">
                select * from user
                <where>
                        <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                                #{id}
                        </foreach>
                </where>
        </select>
</mapper>

There are several properties in the foreach tag,

  • collection: the object to be iterated. Here we iterate over the input parameter list, so fill in list
  • open: prefix at the beginning of foreach code
  • close: suffix to the end of the foreach code
  • item: alias of the element iteration in the collection
  • Separator: separator between elements. For example, the separator in in(1,2,3) is ","

Then we write test methods to test,

@Test//Test foreach
public void test2() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<Integer> ids=new ArrayList<Integer>();//Create id collection
    ids.add(1);
    ids.add(2);
    ids.add(3);
    List<User> userList = mapper.findByIds(ids);//Find users through ids
    System.out.println(userList);
}

2.2SQL fragment extraction

Duplicate sql can be extracted from sql and referenced with include when used, so as to achieve the purpose of sql reuse.

For example, the two sql statements we implemented above are selected from the user table. The repeated sql statements are:

select * from user

We can extract this sentence and give it an id. when using it, we can reference the id given by the include tag.

<mapper namespace="Mapper.UserMapper">
        <!--sql Statement extraction-->
        <sql id="selectUser">select * from user</sql>

        <!--Query operation, for the current sql Statement specifies a id Name, the type of returned result set is User-->
        <select id="findByCondition" resultType="user" parameterType="user">
                <include refid="selectUser"/>
                <where>
                        <if test="id!=0">
                                and id=#{id}
                        </if>
                </where>
        </select>

        <!--According to the given id Collection to find users-->
        <select id="findByIds" resultType="user" parameterType="list">
                <include refid="selectUser"/>
                <where>
                        <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                                #{id}
                        </foreach>
                </where>
        </select>
</mapper>

3, MyBatis core profile drill down

The core configuration file of MyBatis is configured through the configuration tag, in which the following properties can be configured:

  • configuration: configuration
    • Properties: properties
    • Settings: settings
    • typeAliases: type aliases
    • typeHandlers: type handlers
    • objectFactory: object factory
    • plugins: plug-ins
    • environments: Environment
    • Environment: environment variable
    • Transaction manager: transaction manager
    • dataSource: data source
    • databaseIdProvider: database vendor ID
    • mappers: mapper

Let's take a closer look at the typeHandlers tag and plugins tag.

3.1 typehandlers label

3.1.1 typehandlers overview

typeHandlers handle type conversions.

Whether MyBatis sets a parameter in the PreparedStatement or takes a value from the result set, it will use the type processor to convert the obtained value into Java type in an appropriate way.

3.1.2 default processor type

The following table describes some of the default processor types (interceptions).

Type processorJava typeJDBC type
BooleanTypeHandlerjava.lang.Boolean, booleanDatabase compatible BOOLEAN
ByteTypeHandlerjava.lang.Byte, byteDatabase compatible NUMERIC or BYTE
ShortTypeHandlerjava.lang.Short, shortDatabase compatible NUMERIC or SHORT INTEGER
IntegerTypeHandlerjava.lang.Integer, intDatabase compatible NUMERIC or INTEGER
LongTypeHandlerjava.lang.Long, longDatabase compatible NUMERIC or LONG INTEGER

3.1.3 custom type processor

In addition to the above default type processor, we can also customize the type processor.

We handle unsupported or nonstandard types by overriding the type processor or creating our own type processor.

The specific approach is to implement the org.apache.ibatis.type.TypeHandler interface, or inherit a convenient class org.apache.ibatis.type.BaseTypeHandler, and then selectively map it to a JDBC type.

Requirements: for a Date data type in Java, when we want to save it to the database, it will be saved as a number of milliseconds since 1970, and when we take it out, it will be converted into a Java Date, that is, the conversion between the Java Date and the database millisecond value.

Development steps:

  • Define a user-defined type conversion class and inherit basetypehandler < T >
  • Cover 4 unimplemented methods
    • setNonNullParameter() sets the callback method of data to database for java program (java - > database)
    • getNullableResult() is a method that converts the string Type of mysql into the Type type of Java during query (Database - > java) (there are three methods in this method, which are overloaded relationships)
  • Register in the MyBatis core configuration file
  • Test whether the conversion is correct

First, we need to add a birthday variable of Date type in the user entity class. Similarly, the database should also add a birthday attribute of bigint type in the user table.

Next, we start to create a custom converter. The first step is to create a conversion class and inherit the BaseTypeHandler class,

class DateTypeHandler extends BaseTypeHandler<Date> {//Define the type to convert
    /**
     * Convert the java type to the type required by the database
     * @param preparedStatement Execute sql statement
     * @param i Parameter subscript of the type to be converted of the sql statement at execution time
     * @param date Date Date of type
     * @param jdbcType
     * @throws SQLException
     */
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long dateTime = date.getTime();//Gets the millisecond value from 1970 to the present
        preparedStatement.setLong(i,dateTime);//Set the Long type parameter with subscript i to dateTime, that is, convert Date to Long type
    }

    /**
     * Convert types in the database to java types
     * @param resultSet Result set of query
     * @param s The name of the field to be converted, i.e. "birthday" here
     * @return Date Type date
     * @throws SQLException
     */
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        long birthday = resultSet.getLong(s);//Get the birthday data in the result set by the attribute name
        Date date=new Date(birthday);//Converts a millisecond value to a Date of type Date
        return date;//Return date object
    }

    //Convert the types in the database to java types and overload the previous methods (different parameters)
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long birthday = resultSet.getLong(i);//Obtain the birthday data in the result set by subscript
        Date date=new Date(birthday);//Converts a millisecond value to a Date of type Date
        return date;//Return date object
    }

    //Convert the types in the database to java types and overload the previous methods (different parameters)
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long birthday = callableStatement.getLong(i);//Get birthday data in CallableStatement
        Date date=new Date(birthday);//Converts a millisecond value to a Date of type Date
        return date;//Return date object
    }
}

The second step is to overload the methods inside, which we have also implemented above.

The third step is to register in the MyBatis core configuration file (note that the placement of labels should be in the order of the core configuration file).

<!--Register custom processor types-->
<typeHandlers>
    <typeHandler handler="Handler.DateTypeHandler"/>
</typeHandlers>

Step four, we test,

@Test
public void test1() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    User user=new User(4,"test","123",new Date());
    userMapper.save(user);

    sqlSession.commit();//Commit transaction
    sqlSession.close();//Close session connection
}

  You can see that the data has been successfully inserted into the database. Next, let's read the data from the database,

First, we define the query method in the interface,

public User findById(int id);

Then we configure the corresponding sql statements for this method in the mapping file,

<select id="findById" parameterType="int" resultType="user">
        select * from user where id=#{id}
</select>

Finally, we test it,

@Test//Query data
public void test2() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    User user = userMapper.findById(4);//Find user data with id 4
    System.out.println(user.getUsername()+"Birthday of:"+user.getBirthday());//Print user's birthday

    sqlSession.commit();//Commit transaction
    sqlSession.close();//Close session connection
}

3.2 plugins tab

The plugins tag, that is, the plug-in tag, is used to extend the MyBatsi function.

MyBatis can use third-party plug-ins to extend functions. For example, the paging assistant PageHelper encapsulates the complex operations of paging, and can obtain paging related data in a simple way. Let's take PageHelper as an example to see how to use the plugins tag.

If there are many user data in our database, there is a method to query all users,

We hope to display these data in pages. Next, let's see how to use PageHelper to page.

The development steps of using the paging assistant PageHelper are as follows:

  • Import the coordinates of the generic PageHelper
  • Configure the PageHelper plug-in in the mybatis core configuration file
  • Test paging data acquisition

The first step is to import the jar package coordinates,

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>3.7.5</version>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>0.9.1</version>
</dependency>

The second step is to configure the PageHelper plug-in in the mybatis core configuration file,

<!--Configure plug-ins-->
<plugins>
    <!--Configure paging assistant PageHelper plug-in unit-->
    <plugin interceptor="com.github.pagehelper.PageHelper">
        <!--Designated dialect-->
        <property name="dialect" value="mysql"/>
    </plugin>
</plugins>

The third step is to test,

@Test//Paging query all user information
public void test4() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);


    PageHelper.startPage(2,3);//Set paging related parameters (the current page and the number of entries displayed per page, here is page 2, so start with the fourth data and display three user informations 4, 5 and 6)
    List<User> userList = userMapper.findAll();//Find all users
    for (User user : userList) {
        System.out.println(user);
    }

    sqlSession.commit();//Commit transaction
    sqlSession.close();//Close session connection
}

  We can also obtain paging related information through the PageInfo object,

@Test//Get paging related parameters
public void test5() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);


    PageHelper.startPage(2,3);//Set paging related parameters (current page and number of entries displayed per page)
    List<User> userList = userMapper.findAll();//Find all users
    for (User user : userList) {
        System.out.println(user);
    }

    PageInfo<User> pageInfo=new PageInfo<User>(userList);//Use the queried data to reverse obtain the paging related information
    System.out.println("Current page:"+pageInfo.getPageNum());
    System.out.println("Number of users per page:"+pageInfo.getPageSize());
    System.out.println("Total users:"+pageInfo.getTotal());
    System.out.println("Total pages:"+pageInfo.getPages());
    System.out.println("previous page:"+pageInfo.getPrePage());
    System.out.println("Next page:"+pageInfo.getNextPage());
    System.out.println("First page:"+pageInfo.isIsFirstPage());
    System.out.println("Last page:"+pageInfo.isIsLastPage());

    sqlSession.commit();//Commit transaction
    sqlSession.close();//Close session connection
}

Tags: Mybatis

Posted on Fri, 26 Nov 2021 09:49:48 -0500 by Krik