1. typeHandlers label

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. The following table describes some of the default processor types (interceptions).

You can override the type processor or create your own type processor to handle unsupported or nonstandard types.

The specific approach is to realize org. Org apache. ibatis. type. Typehandler interface, or inherit a convenient class org apache. ibatis. type. Basetypehandler, which can then be optionally mapped to a JDBC type.

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

Development steps:

① define transformation class and inherit class BaseTypeHandler

② four unimplemented methods are covered, among which setNonNullParameter is the callback method for java program to set data to the database, and getNullableResult is the method for converting mysql string Type into java Type during query

③ register in the MyBatis core configuration file

④ test whether the conversion is correct

package com.spring.handler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class DateTypeHandler extends BaseTypeHandler<Date> {
    //Convert java type to database type
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);
    }

    /*
    * The following three
    * Convert database type to java type
    * s  Field name to convert
    * resultSet Found result set
    * */
    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        long aLong = resultSet.getLong(s);
        Date date = new Date(aLong);
        return date;
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long aLong = resultSet.getLong(i);
        Date date = new Date(aLong);
        return date;
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}

package com.spring.test;

import com.spring.domain.User;
import com.spring.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

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

        User user = new User();
        user.setUsername("Tom");
        user.setPassword("123");
        user.setBirthday(new Date());

        mapper.save(user);

        sqlSession.close();
    }
}

<!--Register type processor-->
    <typeHandlers>
        <typeHandler handler="com.spring.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>

Query:

2. plugins tab

MyBatis can use third-party plug-ins to expand its functions. The paging assistant PageHelper encapsulates the complex operations of paging and obtains the relevant data of paging in a simple way

Development steps:

① import coordinates of general PageHelper

② configure the PageHelper plug-in in the mybatis core configuration file

③ test paging data acquisition

<!--Register type processor-->
    <typeHandlers>
        <typeHandler handler="com.spring.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>

package com.spring.test;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.spring.domain.User;
import com.spring.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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

//        User user = new User();
//        user.setUsername("Tom");
//        user.setPassword("123");
//        user.setBirthday(new Date());
//
//        mapper.save(user);

        User user = mapper.findById(7);
        System.out.println(user);

        sqlSession.close();
    }

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

        //Page 2, 2 articles per page
        PageHelper.startPage(2,2);

        List<User> users = mapper.findAll();
        for(User user : users){
            System.out.println(user);
        }

        //Get paging related parameters
        PageInfo<User> info = new PageInfo<>(users);
        System.out.println("Current page:"+info.getPageNum());
        System.out.println("Number of items displayed per page:"+info.getPageSize());
        System.out.println("Total number of pieces:"+info.getTotal());
        System.out.println("Total pages:"+info.getPages());
        System.out.println("previous page"+info.getPrePage());
        System.out.println("next page"+info.getNextPage());
        System.out.println("First:"+info.isIsFirstPage());
        System.out.println("Is it the last one"+info.isIsLastPage());

        sqlSession.close();
    }
}

3. Multi table operation

1. One to one query

The relationship between user table and order table is that a user has multiple orders, and an order belongs to only one user

One to one query requirement: query an order and find out the user of the order at the same time

<resultMap id="orderMap" type="order">
        <!--Manually specify the relationship between fields and entity attributes-->
        <id column="id" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
        select * from orders o,user u where o.uid = u.id;
    </select>

 

Second configuration:

<resultMap id="orderMap" type="order">

        <!--Manually specify the relationship between fields and entity attributes-->
        <id column="id" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>

        <!--
            property: order Attribute name in user
            javaType: order The type of the property in the
        -->
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
        </association>

    </resultMap>

2. One to many query

The relationship between user table and order table is that a user has multiple orders, and an order belongs to only one user

One to many query requirements: query a user and find the orders that the user has at the same time

Establish the relationship between users and orders

3. Many to many query

The relationship between user table and role table is that a user has multiple roles and a role is used by multiple users

Many to many query requirements: query the user and query all roles of the user at the same time

<resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <collection property="roleList" ofType="role">
            <id column="rid" property="id"></id>
            <result column="roleName" property="roleName"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select * from user u,role r,user_role ur where u.id = ur.uid and r.id = ur.rid;
    </select>