Java Persistence with MyBatis 3 reading notes

Time:2020-2-27

Chapter 1 Introduction to mybatis

The following contents are the important points that I think when I read this book. On the one hand, I’d like to make a note for myself and help other colleagues

brief introduction

mybatis: it is an open source framework that simplifies and implements the Java data persistence layer. It loves, abstracts and a large number of JDBC redundant codes, and provides simple API and database protection

Advantages of mybatis

  1. Eliminate a lot of JDBC redundant code

  2. Low learning curve

  3. Good domain traditional database collaboration

  4. Receive SQL statement

  5. Provide spring framework integration

  6. Provide third-party cache class library integration

  7. Introducing better performance

Mybatis easy to use

  1. Mybatis configuration

    <?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>
      <typeAliases>
    <typeAlias alias="Student" type="com.mybatis3.domain.Student" />
      </typeAliases>
      <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC" />
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/test" />
        <property name="username" value="root" />
        <property name="password" value="admin" />
      </dataSource>
    </environment>
      </environments>
      <mappers>
    <mapper resource="com/mybatis3/mappers/StudentMapper.xml" />
      </mappers>
    </configuration>
    

0.1. Create session factory class

package com.mybatis3.util;
import java.io.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class MyBatisSqlSessionFactory
{
private static SqlSessionFactory sqlSessionFactory;
public static SqlSessionFactory getSqlSessionFactory()
{
    if(sqlSessionFactory == null)
    {
        InputStream inputStream;
try
        {
            inputStream = Resources.
                          getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new
            SqlSessionFactoryBuilder().build(inputStream);
        }
        catch (IOException e)
        {
            throw new RuntimeException(e.getCause());
         }
    }
    return sqlSessionFactory;
}
public static SqlSession openSession()
{
    return getSqlSessionFactory().openSession();
}
}
  1. Create XML map

    <select id="findStudentById" parameterType="int" resultType="Student">
    SELECT STUD_ID AS studId, NAME, EMAIL, DOB
        FROM STUDENTS WHERE STUD_ID=#{Id}
    </select>
    <insert id="insertStudent" parameterType="Student">
    INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,DOB)
        VALUES(#{studId},#{name},#{email},#{dob})
    </insert>
  2. Create mapper interface

    public interface StudentMapper
    {
        Student findStudentById(Integer id);
        void insertStudent(Student student);
    }
  3. Create session usage interface

    SqlSession session = getSqlSessionFactory().openSession();
    StudentMapper mapper = session.getMapper(StudentMapper.class);
    // Select Student by Id
    Student student = mapper.selectStudentById(1);
    //To insert a Student record
    mapper.insertStudent(student);

Chapter 2 mybatis configuration

Using XML configuration

<?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>
    //Load properties file
  <properties resource="application.properties">
    <property name="username" value="db_user" />
    <property name="password" value="verysecurepwd" />
  </properties>
  //Open cache
  <settings>
    <setting name="cacheEnabled" value="true" />
  </settings>
  
  // alias
  <typeAliases>
    <typeAlias alias="Tutor" type="com.mybatis3.domain.Tutor" />
    <package name="com.mybatis3.domain" />
  </typeAliases>  
  
  //Type processor, registering custom types
  <typeHandlers>
    <typeHandler handler="com.mybatis3.typehandlers.PhoneTypeHandler" />
    <package name="com.mybatis3.typehandlers" />
  </typeHandlers>
  
  //Support to configure multiple data sources and set the default environment as development environment
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC" />
      <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
      </dataSource>
    </environment>
     <environment id="production">
      <transactionManager type="MANAGED" />
      <dataSource type="JNDI">
        <property name="data_source" value="java:comp/jdbc/MyBatisDemoDS" />
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/mybatis3/mappers/StudentMapper.xml" />
    <mapper url="file:///D:/mybatisdemo/mappers/TutorMapper.xml" />
    <mapper class="com.mybatis3.mappers.TutorMapper" />
  </mappers>
</configuration>

This is a simple XML based configuration
The above XML can be used for all configurations:

  1. Load properties file

    • Direct default

      <properties resource="application.properties">
            <property name="jdbc.username" value="db_user" />
            <property name="jdbc.password" value="verysecurepwd" />
      </properties>

      If defined in the filejdbc.username, the default values in the above configuration will be overwritten

    • Through placeholders

      <property name="driver" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
  2. Open cache

  3. To configureenvironments default="development"Implement the default environment and configure different environments

  4. Configuration multi database implementation

  5. Create different sessionfactories for different databases

    InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory defaultSqlSessionFactory = new SqlSessionFactoryBuilder().
    build(inputStream);
    SqlSessionFactory cartSqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStre
        am, "shoppingcart");
    reportSqlSessionFactory = new SqlSessionFactoryBuilder().
    build(inputStream, "reports");

    If you do not specify an environment ID, the default environment creation is used

  6. Database type configuration

    • UNPOOLED

      Mybatis creates a new connection for each database error and closes it. __Only applicable to the data concurrency of little girlfriends__
    • POOLED

      Mybatis creates a database connection pool, which is often used by the development environment__  
      The default database connection pool is implemented through 'org. Apache. Ibatis. Datasource. Pooled. Pooleddatasource`
    • JNDI

      Mybatis obtains database connection from JNDI data source configured by application server
  7. transaction management

    • JDBC

      Is managed by the JDBC transaction manager. Transaction management will be created internally through JDBC transactionfactory
    • MANAGED

      The application server is responsible for managing the database connection life cycle. Internally use the 'managedtranscationfactory' class to create a transaction manager
  8. Setting aliases
    Due to* mapper.xmlIn fileresultTypeandparameterTypeProperty settings to use fully qualified names, you can use the form of aliases to simplify

    <typeAliases>
          <typeAlias alias="Student" type="com.mybatis3.domain.Student" />
          <typeAlias alias="Tutor" type="com.mybatis3.domain.Tutor" />
          <package name="com.mybatis3.domain" />
    </typeAliases>

    You can also set the package without setting the alias directly, and then the system will automatically scan and set an alias with the lowercase initial of the class
    Implement by adding annotation form to entity class

    @Alias("")
    public class Student{
    
    }
  9. Type processor
    Mubatis implements the built-in processor for the following types:

    • All basic data types

    • Package type of basic type (packing operation object type)

    • byte[]

    • java.util.Date

    • java.sql.Date

    • java.sql.Time

    • java.sql.Timestamp

    • Java enumeration type

    Create a custom type processor, handle custom types

    //Phonenumber custom class
    public class PhoneTypeHandler extends BaseTypeHandler<PhoneNumber>
    {
         @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    PhoneNumber parameter, JdbcType jdbcType) throws SQLException
    {
        ps.setString(i, parameter.getAsString());
    }
    
    public PhoneNumber getNullableResult(ResultSet rs, String columnName)  throws SQLException
    {
         return new PhoneNumber(rs.getString(columnName));
    }
    
    public PhoneNumber getNullableResult(ResultSet rs, int columnIndex) throws SQLException
    {
         return new PhoneNumber(rs.getString(columnIndex));
    }
    }

    Don’t forget to sign up

  10. Global parameter setting (the following is the default)

    <settings>
               <setting name="cacheEnabled" value="true" />
               <setting name="lazyLoadingEnabled" value="true" />
               <setting name="multipleResultSetsEnabled" value="true" />
               <setting name="useColumnLabel" value="true" />
               <setting name="useGeneratedKeys" value="false" />
               <setting name="autoMappingBehavior" value="PARTIAL" />
               <setting name="defaultExecutorType" value="SIMPLE" />
               <setting name="defaultStatementTimeout" value="25000" />
               <setting name="safeRowBoundsEnabled" value="false" />
               <setting name="mapUnderscoreToCamelCase" value="false" />
               <setting name="localCacheScope" value="SESSION" />
                  <setting name="jdbcTypeForNull" value="OTHER" />
                  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode ,toString" />
    </settings>
  11. Configure mappers mapping XML file path
    Multiple configurations

    <mappers>
        <mapper resource="com/mybatis3/mappers/StudentMapper.xml" /> 
        <mapper url="file:///D:/mybatisdemo/app/mappers/TutorMapper.xml" /> 
        <mapper class="com.mybatis3.mappers.TutorMapper" />
        <package name="com.mybatis3.mappers" />
    </mappers>

Using java configuration

The configuration attributes in mybatis are understood through XML configuration form, which can also be realized through Java API configuration form

public class MybatisConfig{
    public static SqlSessionFactory getSqlSessionFactory()
    {
      SqlSessionFactory sqlSessionFactory = null;
    try
    {
        DataSource dataSource = DataSourceFactory.getDataSource();
        TransactionFactory transactionFactory = new
        JdbcTransactionFactory();
        Environment environment = new Environment("development",
                transactionFactory, dataSource);
        Configuration configuration = new Configuration(environment);
        configuration.getTypeAliasRegistry().registerAlias("student",
                Student.class);
        configuration.getTypeHandlerRegistry().register(PhoneNumber.
                class, PhoneTypeHandler.class);
        configuration.addMapper(StudentMapper.class);
        // create
        sqlSessionFactory = new SqlSessionFactoryBuilder().
        build(configuration);
    }
    catch (Exception e)
    {
        throw new RuntimeException(e);
    }
    return sqlSessionFactory;
}
}

Create datasource

public class DataSourceFactory
{
    public static DataSource getDataSource(){
       String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mybatisdemo";
        String username = "root";
        String password = "admin";
        PooledDataSource dataSource = new PooledDataSource(driver, url,
        username, password);
        return dataSource;
    }
    
    //Created by JNDI  
    public static DataSource getDataSource(){
        String jndiName = "java:comp/env/jdbc/MyBatisDemoDS";
        try{
                 InitialContext ctx = new InitialContext();
                DataSource dataSource = (DataSource) ctx.lookup(jndiName);
                return dataSource;
        }catch(NamingException e){
            throw new RuntimeException(e);
        }
    }
    
     
}

Custom mybatis log

Mybatis log support

Priority of log support in mybatis:

  • SLF4J

  • Apache Commons Logging

  • Log4j 2

  • Log4j

  • JDK logging

Mybatis setting log

Call the following method to implement

org.apache.ibatis.logging.LogFactory.useSlf4jLogging(); 
org.apache.ibatis.logging.LogFactory.useLog4JLogging();
org.apache.ibatis.logging.LogFactory.useLog4J2Logging(); 
org.apache.ibatis.logging.LogFactory.useJdkLogging();
org.apache.ibatis.logging.LogFactory.useCommonsLogging(); 
org.apache.ibatis.logging.LogFactory.useStdOutLogging();

Chapter 3 XML configuration SQL mapper

There are two forms of formal mapping through XML:

  • Only XML Mapping

    • Define XML Mapping File

        <?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.mybatis3.mappers.StudentMapper">
            <select id="findStudentById" parameterType="int" resultType="Student"> select stud_id as studId, name, email, dob
from Students where stud_id=#{studId}
              </select>
        </mapper>
  • call

    public Student = findStudentById(Integer id){
        SqlSession session = MybatisUtil.geSqlSession();
        try{
            //Called as a string
            Student student = sqlSession.selectOne("com.mybatis3.mappers.StudentMapper.findStudentById",id);
        }
    }
  • With mapper interface class form

    Note: the namespace should be one to the fully qualified name of the studentmapper interface, and the method name, parameter type and return type in the studentmapper interface should correspond to the method name, parameter type and return value respectively__

Mapping statement provided by mybatis

Mybatis provides many different mapping statements:

  • INSERT

    • useGeneratedKeys: set self growth

    • keyProperty: primary key properties

  • SELECT

  • UPDATE

  • DELETE

Mybatis different mapping statement instances:

  1. INSERT

    • Set primary key through self growth

      <insert id="insertStudent" parameterType="Student" useGeneratedKeys="true" keyProperty="studId">
          INSERT INTO STUDENTS(NAME, EMAIL, PHONE)
          VALUES(#{name},#{email},#{phone})
      </insert>
    • Generate primary key values for support sequences

      <insert id="insertStudent" parameterType="Student">
          <selectKey keyProperty="studId" resultType="int" order="BEFORE">
              SELECT ELEARNING.STUD_ID_SEQ.NEXTVAL FROM DUAL
          </selectKey>
          INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL, PHONE)
          VALUES(#{studId},#{name},#{email},#{phone})
      </insert>
      
      //Using triggers  
      <insert id="insertStudent" parameterType="Student">
          INSERT INTO STUDENTS(NAME,EMAIL, PHONE)
          VALUES(#{name},#{email},#{phone})
          <selectKey keyProperty="studId" resultType="int" order="AFTER">
              SELECT ELEARNING.STUD_ID_SEQ.CURRVAL FROM DUAL
          </selectKey>
      </insert>

Mybatis result set mapping rule:

  • For list, collection, Iterable type, return java.util.arraylist

  • For map type, return java.util.hashmap

  • For set type: return java.util.hashset

  • For sortedset type: returns java.util.treeset

Mybatis mapping

One to one mapping

  • Mode 1: use point syntax for mapping

public class Student
{
   private Integer studId;
   private String name;
   private String email;
   Private address address; // an address object. Each student corresponds to an address  
}

<resultMap type="Student" id="StudentWithAddressResult">
  <id property="studId" column="stud_id" />
  <result property="name" column="name" />
  <result property="email" column="email" />
  <result property="phone" column="phone" />
  <result property="address.addrId" column="addr_id" />
  <result property="address.street" column="street" />
  <result property="address.city" column="city" />
  <result property="address.state" column="state" />
  <result property="address.zip" column="zip" />
  <result property="address.country" column="country" />
</resultMap>
<select id="selectStudentWithAddress" parameterType="int"
resultMap="StudentWithAddressResult">
    SELECT STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE,
        ZIP, COUNTRY
FROM STUDENTS S LEFT OUTER JOIN ADDRESSES A ON S.ADDR_ID=A.ADDR_ID
    WHERE STUD_ID=#{studId}
</select>
//Left outer connection is used to query
  • Mode 2: use nested resultsResultMapMapping

    • Import other file definedResultMap

      __Use label 'Association' to import__
      <resultMap type="Address" id="AddressResult">
            <id property="addrId" column="addr_id" />
            <result property="street" column="street" />
            <result property="city" column="city" />
      </resultMap>
      
      <resultMap type="Student" id="StudentWithAddressResult">
            <id property="studId" column="stud_id" />
            <result property="name" column="name" />
            <result property="email" column="email" />
            <association property="address" resultMap="AddressResult" />
      </resultMap>
      Query statement
      <select id="findStudentWithAddress" parameterType="int"
      resultMap="StudentWithAddressResult">
          SELECT STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY
          FROM STUDENTS S LEFT OUTER JOIN ADDRESSES A ON S.ADDR_ID=A.ADDR_ID
          WHERE STUD_ID=#{studId}
      </select>
    • Nested within itself

      <resultMap type="Student" id="StudentWithAddressResult">
            <id property="studId" column="stud_id" />
            <result property="name" column="name" />
            <result property="email" column="email" />
            <association property="address" javaType="Address">
              <id property="addrId" column="addr_id" />
              <result property="street" column="street" />
              <result property="city" column="city" />
              <result property="state" column="state" />
              <result property="zip" column="zip" />
              <result property="country" column="country" />
            </association>
      </resultMap>
  • Method 3: use its set of select query statements
    Each corresponding object implements its own query statement

    <resultMap type="Address" id="AddressResult">
         <id property="addrId" column="addr_id" />
         <result property="street" column="street" />
    </resultMap>
    
    //Query statement
    <select id="findAddressById" parameterType="int"
    resultMap="AddressResult">
    SELECT * FROM ADDRESSES WHERE ADDR_ID=#{id}
    </select>
    
    <resultMap type="Student" id="StudentWithAddressResult">
         <id property="studId" column="stud_id" />
        <association property="address" column="addr_id" select="findAddressById" />
    </resultMap>
    <select id="findStudentWithAddress" parameterType="int"
       resultMap="StudentWithAddressResult">
           SELECT * FROM STUDENTS WHERE STUD_ID=#{Id}
    </select>

    Note nested queries: PassassociationNesting the query statement of another object, and it seems that this label can only be used for query

The internal objects in one-to-one query correspond to an ID or unique identification value in the database, so the values in the nested XML at the bottom of this place are all ID attributes

One to many mapping

Use<collection>Element maps one to many types of results to a collection of objects

  1. Display as nested objects

public class Tutor
{
    private Integer tutorId;
    private String name;
    private String email;
    private Address address;
    private List<Course> courses;
    / setters & getters
}
<resultMap type="Tutor" id="TutorResult">
  <id column="tutor_id" property="tutorId" />
  <result column="tutor_name" property="name" />
  <result column="email" property="email" />
  <collection property="courses" resultMap="CourseResult" />
</resultMap>


<select id="findTutorById" parameterType="int"
resultMap="TutorResult">
SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL, C.COURSE_ID,
C.NAME, DESCRIPTION, START_DATE, END_DATE
FROM TUTORS T LEFT OUTER JOIN ADDRESSES A ON T.ADDR_ID=A.ADDR_ID
LEFT OUTER JOIN COURSES C ON T.TUTOR_ID=C.TUTOR_ID
WHERE T.TUTOR_ID=#{tutorId}
</select>

  1. Using nested statements

<resultMap type="Tutor" id="TutorResult">
  <id column="tutor_id" property="tutorId" />
  <result column="tutor_name" property="name" />
  <result column="email" property="email" />
  <association property="address" resultMap="AddressResult" />
  <collection property="courses" column="tutor_id" select="findCoursesByTutor" />
</resultMap>



<select id="findTutorById" parameterType="int" resultMap="TutorResult">
    SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL
    FROM TUTORS T WHERE T.TUTOR_ID=#{tutorId}
</select>
<select id="findCoursesByTutor" parameterType="int" resultMap="CourseResult">
  SELECT * FROM COURSES WHERE TUTOR_ID=#{tutorId}
</select>

Be careful:Nested select statement query will result in N + 1 selection query. First, the main query will be executed (once). For each row returned by the main query, another query will be executed (for row n of the main query, this query will be executed n times). For large databases, this can lead to poor performance problems.

Dynamic SQL

Mybatis provides:<if>,<choose>,<where>,<foreach>,<trim>Construct dynamic SQL

1. if

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult"></select>
    SELECT * FROM COURSES
        WHERE TUTOR_ID= #{tutorId}
    <if test="courseName != null">
    AND NAME LIKE #{courseName}
    </if>
</select>

When the test condition in if is set, the content in if will be added to the SQL statement

choose, when, otherwise

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
    SELECT * FROM COURSES
    <choose>
        <when test="searchBy == 'Tutor'">
            WHERE TUTOR_ID= #{tutorId}
        </when>
        <when test="searchBy == 'CourseName'">
            WHERE name like #{courseName}
        </when>
        <otherwise>
            WHERE TUTOR start_date >= now()
        </otherwise>
    </choose>
</select>

__Mybatis calculation<choose>Test the value of the condition, and use the clause with the first value of true. If no condition is true, use the clause within < otherwise >.

where

<select id="searchCourses" parameterType="hashmap"
resultMap="CourseResult">
    SELECT * FROM COURSES
    <where>
        <if test=" tutorId != null ">
            TUTOR_ID= #{tutorId}
        </if>
        <if test="courseName != null">
            AND name like #{courseName}
        </if>
        <if test="startDate != null">
            AND start_date >= #{startDate}
        </if>
        <if test="endDate != null">
            AND end_date <= #{endDate}
        </if>
    </where>
</select>

trim

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
     SELECT * FROM COURSES
<trim prefix="WHERE" prefixOverrides="AND | OR"> 
     <if test=" tutorId != null ">
         TUTOR_ID= #{tutorId}
     </if>  
     <if test="courseName != null">
        AND name like #{courseName}
     </if>
    </trim>
</select>

foreach

<select id="searchCoursesByTutors" parameterType="map"
resultMap="CourseResult">
SELECT * FROM COURSES
<if test="tutorIds != null">
<where>
<foreach item="tutorId" collection="tutorIds">
OR tutor_id=#{tutorId}
</foreach>
</where>
</if>
</select>

mybatis Other

Processing enumeration

  1. Store enum name

    By default, mybatis supports the out of the box method to persist enumeration type properties. It processes the enumeration type corresponding to Java type through 'enumtypehandler'  
    By default, string is used for storage, and the corresponding string in data table is enumeration
  2. Store int type value corresponding to enumeration

    The handler corresponding to enumeration needs to be modified to the following class
    <typeHandler
    handler="org.apache.ibatis.type.EnumOrdinalTypeHandler"
    javaType="com.mybatis3.domain.Gender"/>
    __It is recommended to use the default form. If the order of use is changed, the median value of the database will not be mapped__

Handling blob types

By default, mybatis maps CLOB type columns tojava.lang.StringType,
Map blobs tobyte[]Type

Multiple parameters

  1. Using map form to introduce

    <select id="findAllStudentsByNameEmail" resultMap="StudentResult" parameterType="map">
    select stud_id, name,email, phone from Students
        where name=#{name} and email=#{email}
    </select>
    
  2. Use parameter override

    <select id="findAllStudentsByNameEmail" resultMap="StudentResult">
    select stud_id, name,email, phone from Students
        where name=#{param1} and email=#{param2}
    </select>

cache

By default, mybatis enables the first level cache to support the cache of select. Can pass<cache>Enable L2 cache
Problems caused by opening the headset cache:

  • All query results of < Select > statements defined in the mapping statement file will be cached

  • All < Insert >, < update > and < delete > statements defined in the mapping statement file will refresh the cache

  • The cache is managed according to the least recently used (LRU) algorithm

  • The cache will not be refreshed by any form of schedule based refresh (no refresh interval), that is, the scheduled refresh mechanism is not supported

  • The cache will store references to lists or objects returned by 1024 query methods

  • The cache is treated as a read / write cache. This means that the retrieved object will not be shared, and can be modified safely by the caller without interference from other potential callers or threads. (that is, caching is thread safe)

<cache eviction="FIFO" flushInterval="60000" size="512"
readOnly="true"/>

Explain:

  • eviction

    • LRU: rarely used recently

    • FIFO: first in first out

    • Soft: soft reference

    • Weak: if referenced

  • Flushinterval (define refresh time)

  • Size size

  • Readonly

Chapter 4 annotation configuration SQL mapper

  1. Some content based on annotation:

    • INSERT

    • UPDATE

    • SELECT

    • DELETE

  2. Basic use

    @Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)
        VALUES(#{name},#{email},#{address.addrId},#{phone})")
      //Implement the primary key with the following annotations 
      @Options(useGeneratedKeys = true, keyProperty = "studId")
    
    //Use this annotation to specify the primary key value for any SQL statement (use this annotation to generate the primary key)
    @SelectKey(statement="SELECT STUD_ID_SEQ.NEXTVAL FROM DUAL",
    keyProperty="studId", resultType=int.class, before=true)
    
    //Build with triggers
    @SelectKey(statement="SELECT STUD_ID_SEQ.CURRVAL FROM DUAL",
    keyProperty="studId", resultType=int.class, before=false)
    
    
    //Result set mapping  
    @Results(
    {
        @Result(id = true, column = "stud_id", property = "studId"),
        @Result(column = "name", property = "name"),
        @Result(column = "email", property = "email"),
        @Result(column = "addr_id", property = "address.addrId")
    })
    • One to one mapping

      • Nested SQL statement form

      //Implementation with nested select statement
      @Results(
      {
      @Result(id = true, column = "stud_id", property = "studId"),
      @Result(column = "name", property = "name"),
      @Result(column = "email", property = "email"),
      @Result(property = "address", column = "addr_id",
      one = @One(select = "com.mybatis3.mappers.StudentMapper.
      findAddressById"))
      })
      • nested object

        There is no annotation implementation in this way. You can define the mapping 'resultmap' set in XML, and then map it through '@ resultmap'
        @ResultMap("com.mybatis3.mappers.StudentMapper.
                       StudentWithAddressResult")
    • One to many mapping
      The same is only in the form of nested SQL

       @Result(property = "courses", column = "tutor_id",
      many = @Many(select = "com.mybatis3.mappers.TutorMapper.
      findCoursesByTutorId"))
  3. Problems encountered

    • The result set cannot be reused because the ID cannot be given, so it cannot be reused, even if it is the same, it must be rewritten
      Can be mapped through an XML

      <mapper namespace="com.mybatis3.mappers.StudentMapper">
        <resultMap type="Student" id="StudentResult">
          <id property="studId" column="stud_id" />
          <result property="name" column="name" />
          <result property="email" column="email" />
          <result property="phone" column="phone" />
        </resultMap>
      </mapper>
      
          //In this way, we can solve the problem that can't be reused
       @Select("SELECT * FROM STUDENTS WHERE STUD_ID=#{studId}")
            @ResultMap("com.mybatis3.mappers.StudentMapper.StudentResult")
    • Generate SQL statement by tool class for dynamic SQL

      return new SQL()
      {
          {
              SELECT("tutor_id as tutorId, name, email");
              FROM("tutors");
               WHERE("tutor_id=" + tutorId);
            }
      } .toString();
      }        
  4. Dynamic SQL mapping

    • Create a dynamic SQL mapping class

      public class TutorDynaSqlProvider
      {
          public String findTutorByIdSql(int tutorId)
          {
              return "SELECT TUTOR_ID AS tutorId, NAME, EMAIL FROM TUTORS
         WHERE TUTOR_ID=" + tutorId;
          } 
      }
      
      public String findTutorByNameAndEmailSql(Map<String, Object> map)
      {
          String name = (String) map.get("param1");
          String email = (String) map.get("param2");
          //you can also get those values using 0,1 keys
          //String name = (String) map.get("0");
          //String email = (String) map.get("1");
          return new SQL()
          {
              {
                  SELECT("tutor_id as tutorId, name, email");
                  FROM("tutors");
                  WHERE("name=#{name} AND email=#{email}");
              }
          } .toString();
      }
    • Add dynamic SQL annotation to method

      @SelectProvider(type=TutorDynaSqlProvider.class, method="findTutorByIdSql")
      Tutor findTutorById(int tutorId);

Chapter 5 spring integration

  1. Configure mybatis beans

    `Add in ApplicationContext. XML '

<beans>
  <bean id="dataSource" class="org.springframework.jdbc.datasource. DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/elearning" />
    <property name="username" value="root" />
    <property name="password" value="admin" />
  </bean>
  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="typeAliases" value="com.mybatis3.domain.Student, com.mybatis3.domain.Tutor" /> <property name="typeAliasesPackage" value="com.mybatis3.domain" />
<property name="typeHandlers" value="com.mybatis3.typehandlers.PhoneTypeHandler" />
<property name="typeHandlersPackage" value="com.mybatis3.typehandlers" />
<property name="mapperLocations" value="classpath*:com/mybatis3/**/*.xml" />
<property name="configLocation" value="WEB-INF/mybatisconfig.xml" />
  </bean>
</beans>

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
  <constructor-arg index="0" ref="sqlSessionFactory" />
</bean>

In this way, sqlsession can be referenced

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 
    <property name="basePackage" value="com.mybatis3.mappers" />
</bean>

Scan mapper interface of mapper in package and register automatically

Mybatis can scan mapper in two ways:

*Use XML form  
    ` <mybatis:scan base-package="com.mybatis3.mappers" />`
*Use the '@ mapperscan' annotation

Thank you very much for the translation of this book

Recommended Today

Incomplete delivery order log of SAP SD basic knowledge

Incomplete delivery order log of SAP SD basic knowledge   If we call the incomplete project log, the system checks whether the data in the outbound delivery is complete. From the generated list, we can directly jump to the screen of maintaining incomplete fields.   We can call log of incomplete items from delivery processing, […]