ORM Framework

Object Relational Mapping (ORM) is a technology to solve the mismatch between object-oriented and relational database. In short, ORM automatically persists objects in programs to relational databases by using metadata that describes the mapping between objects and databases.

  • Why ORM?

When you develop an application (without O/R Mapping), you may write a lot of data access layer code to save, delete, read object information from the database, etc. In DAL, you write a lot of methods to read object data, change state objects and other tasks, which are always repetitive. ORM provides a solution to these problems, which simplifies the operation of persistence of objects in programs to relational databases.

The essence of ORM framework is to simplify the encoding of operating database in programming. There are basically two most popular frameworks in the Java field. One is Hibernate, which claims to be able to write without a single sentence of SQL, and the other is MyBatis, which is good at dynamic SQL. Both of them have their own characteristics. An interesting phenomenon is that traditional enterprises like to use Hibernate, while the Internet industry usually uses MyBatis.

Introduction to MyBatis

MyBatis is a standard ORM framework, which is widely used in enterprise development. MyBatis was originally an open source project of Apache, iBatis, which was moved from Apache Software Foundation to Google Code in 2010 and renamed MyBatis, and then to Github in November 2013. From the migration history of MyBatis, we can also see the history of the development of source hosting platform. GitHub has become the largest open source software hosting platform in the world. It is suggested that people pay more attention to this world's largest same-sex social networking site.

MyBatis supports common SQL queries, stored procedures and excellent persistence framework for advanced mapping. MyBatis eliminates manual settings of almost all JDBC code and parameters and encapsulation of search results. MaBatis can use simple XML or annotations to configure and map interfaces and Java POJO s (Plain Old Java Objects) into records in the database.

1. Advantages of MyBatis

  • SQL is extracted in a unified way to facilitate unified management and optimization.
  • The decoupling of SQL and code separates business logic from data access logic, which makes the design of the system clearer, easier to maintain and easier to unit test.
  • Provide mapping labels to support object-database ORM field mapping
  • Provide object relational mapping labels to support object relational component maintenance
  • Flexible writing dynamic SQL, supporting various conditions to generate different SQL dynamically

2. Disadvantages of MyBatis

  • It takes a lot of work to write SQL statements, especially when there are many fields and associated tables.
  • The dependence of SQL statements on databases leads to poor database portability

3. Several important concepts of MyBatis

  • Mapper configuration can be implemented using XML-based Mapper configuration file, Java-based MyBatis annotation, or even directly using the API provided by MyBatis.
  • Mapper interface is a self-defined data operation interface, similar to the DAO interface. Early Mapper interfaces needed to be customized to implement, and now MyBatis automatically creates dynamic proxy objects for Mapper interfaces. The method of Mapper interface usually has a one-to-one correspondence with XML nodes such as select, insert, update, delete in Mapper configuration file.
  • Executor, the execution of all Mapper statements in MyBatis is done through Executor, which is a core interface of MyBatis.
  • SqlSession, the key object of MyBatis, is the exclusive use of persistence operations. Similar to Connection in JDBC, SqlSession object contains all the methods of executing SQL operations in the background of database. Its underlying layer encapsulates JDBC connections and can directly execute mapped SQL operations with SqlSession instances. Sentence.
  • SqlSession Factory is the key object of MyBatis. It is a compiled memory mirror of a single database mapping relationship. Instances of SqlSessionFactory objects can be obtained through the SqlSessionFactory Builder object class, while SqlSessionFactory Builder can be built from an XML configuration file or a pre-customized Configuration instance.

4. MyBatis's workflow is as follows:

  • First, load the SQL mapping file of the Mapper configuration, or the related SQL content of the annotations.
  • To create a session factory, MyBatis constructs a session factory (SqlSession Factory) by reading the configuration file information.
  • Create a session. According to the session factory, MyBatis can create a session object (SqlSession) through it. The session object is an interface which contains the methods of adding, deleting, modifying and searching database operations.
  • Create an executor. Because the session object itself cannot directly manipulate the database, it uses an interface called Executor to help it perform operations.
  • Encapsulate SQL objects. In this step, the executor encapsulates the SQL information to be processed into an object (MappedStatement), which includes the SQL statement, input parameter mapping information (Java simple type, HashMap or POJO) and output result mapping information (Java simple type, HashMap or POJO).
  • Operate the database. With the executor and the SQL information encapsulation object, we can use them to access the database, and finally return the operation results and end the process.

3. What is MyBatis-Spring-Boot-Starter

  1. mybatis-spring-boot-starter is a component package provided by MyBatis to help us quickly integrate Spring Boot. Using this component, we can do the following:
  • Building independent applications
  • Almost zero configuration
  • Very few XML configurations are required

mybatis-spring-boot-starter relies on MyBatis-Spring and Spring Boot. The latest version 1.3.2 requires more than MyBatis-Spring 1.3 and Spring Boot version 1.5.

Note that mybatis-spring-boot-starter is the starter officially developed by MyBatis, not the starter package officially developed by Spring Boot. In fact, MyBatis sees that Spring Boot market is very popular, so it actively develops Starter package for integration, but this integration really solves many problems and uses it more than before. It's much simpler. Mybatis-spring-boot-starter provides two main solutions, one is the simplified XML configuration version, the other is the use of annotations to solve all problems.

MyBatis used to use only XML configuration, but later annotations were widely used. MyBatis also provided annotations to support the trend. It can be seen from this that MyBatis has been following the changes of mainstream technology to improve itself. Next, I'll show you how to use the XML version.

XML version maintains the way of mapping file. The use of the latest version is mainly embodied in the implementation layer that does not need to implement Dao. The system will automatically find the corresponding SQL in the mapping file according to the method name.

4. MyBatis Single Data Source Operation

  1. Initialization script
  • To facilitate project demonstration, users tables need to be created in the test repository with the following scripts:
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  `userName` varchar(32) DEFAULT NULL COMMENT 'User name',
  `passWord` varchar(32) DEFAULT NULL COMMENT 'Password',
  `user_sex` varchar(32) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  1. Critical dependency packages
  • Of course, any mode needs to first introduce the pom file of mybatis-spring-boot-starter, and the latest version is 1.3.2.
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
  1. application configuration
  • application.properties to add configuration
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
mybatis.type-aliases-package=com.neo.model

spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  • mybatis.config-location, configure mybatis-config.xml path, configure MyBatis basic properties in mybatis-config.xml;
  • mybatis.mapper-locations, configure the corresponding XML file path of Mapper;
  • mybatis.type-aliases-package, configuring the entity package path in the project;
  • spring.datasource. *, data source configuration.

When Spring Boot starts, the data source is automatically injected into SqlSession Factory, SqlSession Factory is built with SqlSession Factory, and then automatically injected into Mapper. Finally, we can use Mapper directly.

  1. Startup class
  • Add Mapper package scan @MapperScan to the startup class, and Spring Boot automatically loads Mapper under the package path when it starts.
@Spring BootApplication
@MapperScan("com.neo.mapper")
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Or add annotations directly to the Mapper class @Mapper It is recommended to use the above one, otherwise it would be troublesome to add a comment to each mapper.

  1. Demonstration of examples
  • MyBatis Public Attributes
    • mybatis-config.xml configures typeAliases commonly used, sets type aliases, and sets a short name for Java types. It's only about XML configuration, and it's only about reducing redundancy in class fully qualified names.
<configuration>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
    </typeAliases>
</configuration>
resultType="Integer" 
//perhaps
parameterType="Long"
  • Add User's mapping file

    • The first step is to specify the Mapper class address of the corresponding file:
    	<mapper namespace="com.neo.mapper.UserMapper" >
    
    • The second step is the corresponding relationship between the configuration table structure and the class.
    	<resultMap id="BaseResultMap" type="com.neo.model.User" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="userName" property="userName" jdbcType="VARCHAR" />
    <result column="passWord" property="passWord" jdbcType="VARCHAR" />
    <result column="user_sex" property="userSex" javaType="com.neo.enums.UserSexEnum"/>
    <result column="nick_name" property="nickName" jdbcType="VARCHAR" /></resultMap>
    

    In order to get closer to the working situation, two fields of the class and database fields are set to be inconsistent, one of which uses enumeration. Using enumeration has a very big advantage, data inserted into this property will be automatically checked, if not enumerated content will report errors.

    • The third step is to write specific SQL statements, such as:
    	<select id="getAll" resultMap="BaseResultMap"  >
    SELECT 
    *
    FROM users </select>
    
  • MyBatis XML has a feature that it can reuse XML, such as some of our common XML fragments can be extracted and referenced in other SQL. For example:

    	<sql id="Base_Column_List" >
    		id, userName, passWord, user_sex, nick_name
    	</sql>
    
    	<select id="getAll" resultMap="BaseResultMap"  >
    	   SELECT 
    	   <include refid="Base_Column_List" />
    	   FROM users
    	</select>  
    

    This example is that the table fields that need to be queried are defined above, and the following SQL is introduced using include to avoid writing too many duplicate configuration contents.

  • Following are examples of common additions, deletions, modifications and checks:

<select id="getOne" parameterType="Long" resultMap="BaseResultMap" >
    SELECT 
   <include refid="Base_Column_List" />
   FROM users
   WHERE id = #{id}
</select>

<insert id="insert" parameterType="com.neo.model.User" >
   INSERT INTO users (userName,passWord,user_sex) 
   VALUES (#{userName}, #{passWord}, #{userSex})
</insert>

<update id="update" parameterType="com.neo.model.User" >
   UPDATE  users SET
       <if test="userName != null">userName = #{userName},</if>
       <if test="passWord != null">passWord = #{passWord},</if>
       nick_name = #{nickName}
   WHERE id = #{id}
</update>

<delete id="delete" parameterType="Long" >
   DELETE FROM users 
   WHERE id =#{id}
</delete>

The update's SQL above uses the if tag to produce dynamic SQL according to different conditions, which is the biggest feature of MyBatis.

  • Writing Dao Layer Code
public interface UserMapper {

    List<UserEntity> getAll();

    UserEntity getOne(Long id);

    void insert(UserEntity user);

    void update(UserEntity user);

    void delete(Long id);
}

Note: The method names here need to be consistent with the id attributes in the XML configuration, otherwise no method can be found to correspond to the execution of the SQL.

  1. Test use
  • According to Spring's consistent usage, you can inject the corresponding Mapper directly.
@Resource
private UserMapper userMapper;
  • If you use Idea, this comment often says "could not autowire", but Eclipse has no problem. In fact, the code is correct, which is Idea's mistake. You can choose to lower the level of Autowire detection without prompting. Use the search function to find Autowiring for Bean Class in the File | Settings | Editor | Inspections option, and change the Severity level from the previous error to warning.

  • Next, use userMapper directly for database operation.

@Test
public void testUser()  {
    //increase
    userMapper.insert(new User("aa", "a123456", UserSexEnum.MAN));
    //delete
    int count=userMapper.delete(2l);
    User user = userMapper.getOne(1l);
    user.setNickName("smile");
    //modify
    userMapper.update(user);
    //query
    List<User> users = userMapper.getAll();
}
  1. Paging query

Multi-conditional paging query is one of the most commonly used functions in practical work, and MyBatis is particularly good at dealing with such problems. In practical work, the paging will be simply encapsulated to facilitate the front-end use. In addition, in the use of Web development specifications, the parameters of the Web layer are passed by the object suffixed with param, and the returned data is encapsulated by the entity class ending with result.

  • Define a base class for paging (default 3 records per page, which can be modified according to the front-end parameters):
public class PageParam {
    private int beginLine;       //Start line
    private Integer pageSize = 3;
    private Integer currentPage=0;        // Current page
    //getter setter ellipsis
    public int getBeginLine() {
        return pageSize*currentPage;//Automatically compute the starting line
    }
}
  • The query condition parameter class of user inherits the paging base class:
public class UserParam extends PageParam{
    private String userName;
    private String userSex;
    //getter setter ellipsis
}
  • Next, configure the specific SQL, first extract the query conditions.
<sql id="Base_Where_List">
    <if test="userName != null  and userName != ''">
        and userName = #{userName}
    </if>
    <if test="userSex != null and userSex != ''">
        and user_sex = #{userSex}
    </if>
</sql>
  • Paging information and query conditions are obtained from the object UserParam, and then combined.
<select id="getList" resultMap="BaseResultMap" parameterType="com.neo.param.UserParam">
    select
    <include refid="Base_Column_List" />
    from users
    where 1=1
    <include refid="Base_Where_List" />
    order by id desc
    limit #{beginLine} , #{pageSize}
</select>
  • The front end needs to display the total number of pages, so it needs to count the total number of query results.
<select id="getCount" resultType="Integer" parameterType="com.neo.param.UserParam">
    select
    count(1)
    from users
    where 1=1
    <include refid="Base_Where_List" />
</select>
  • The two methods and configuration files defined in Mapper correspond to each other.
public interface UserMapper {
    List<UserEntity> getList(UserParam userParam);
    int getCount(UserParam userParam);
}
  • Specific use:
@Test
public void testPage() {
    UserParam userParam=new UserParam();
    userParam.setUserSex("WOMAN");
    userParam.setCurrentPage(1);
    List<UserEntity> users=userMapper.getList(userParam);
    long count=userMapper.getCount(userParam);
    Page page = new Page(userParam,count,users);
    System.out.println(page);
}
  • In practical use, you just need to pass in the CurrentPage parameter, default 0 is the first page, pass 1 is the content of the second page, and finally encapsulate the results as Page back to the front end.
public class Page<E> implements Serializable {
    private int currentPage = 0; //page
    private long totalPage;       //PageCount
    private long totalNumber;    //Total number of records
    private List<E> list;        //data set
}

Page encapsulates paging information and data information to facilitate the front-end display of the number of pages, total entries and data, so that the paging function is completed.

5. MyBatis Multi-Data Source Operation

  1. configuration file
  • First, we need to configure two different data sources. Note that we need to create the User table structure in the test1 and test2 libraries in advance.
mybatis.config-location=classpath:mybatis/mybatis-config.xml

spring.datasource.one.jdbc-url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.one.username=root
spring.datasource.one.password=root
spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.two.jdbc-url=jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.two.username=root
spring.datasource.two.password=root
spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver

The first data source uses spring.datasource.one. * as prefix to connect database test1, and the second data source uses spring.datasource.two. * as prefix to connect database test2. The first data source uses spring.datasource.one. * as prefix to connect database test1, and the second data source uses spring.datasource.two. * as prefix to connect database test2.

  1. Data source configuration

Create different Mapper package paths for two data sources, copy the previous User Mapper to the package com.neo.mapper.one and com.neo.mapper.two paths, and rename them User1 Mapper and User2 Mapper, respectively.

  • Configure the first data source, create a new DataSource1Config, and load the configured data source first.
@Bean(name = "oneDataSource")
@ConfigurationProperties(prefix = "spring.datasource.one")
@Primary
public DataSource testDataSource() {
    return DataSourceBuilder.create().build();
}
  • According to the created data source, the corresponding SqlSessionFactory is constructed.
@Bean(name = "oneSqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("oneDataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/one/*.xml"));
    return bean.getObject();
}

The code needs to specify the loaded Mapper xml file.

  • At the same time, the data source is added to the transaction.
@Bean(name = "oneTransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("oneDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}
  • Next, inject the SqlSession Factory created above to create the SqlSession Template that we need to use in Mapper.
@Bean(name = "oneSqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("oneSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
}
  • Finally, the SqlSession Template created above is injected into the corresponding Mapper package path, so that the Mapper under this package will use the first data source for database operations.
@Configuration
@MapperScan(basePackages = "com.neo.mapper.one", sqlSessionTemplateRef  = "oneSqlSessionTemplate")
public class OneDataSourceConfig {
 ...
}
  • basePackages specify the Mapper address.
  • SqlSession Template Ref specifies the sqlSession Template injected under the Mapper path.
  1. Second Data Source Configuration
  • The configuration of DataSource2Config is similar to that above and needs to be removed in method. @Primary Annotation, replace the corresponding data source and Mapper path. The following is a complete example of DataSource2Config:
@Configuration
@MapperScan(basePackages = "com.neo.mapper.two", sqlSessionTemplateRef  = "twoSqlSessionTemplate")
public class DataSource2Config {

    @Bean(name = "twoDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.two")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "twoSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("twoDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/two/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "twoTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("twoDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "twoSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("twoSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

From the above steps, we can conclude that the process of creating multiple data sources is to create a DataSource, inject it into SqlSession Factory, create a transaction, inject SqlSession Factory into the created SqlSession Template, and finally inject SqlSession Template into the corresponding Mapper package path. The Mapper package path of the sublibrary needs to be specified.

Note that in the case of multiple data sources, we do not need to add a @MapperScan("com.xxx.mapper") annotation to the startup class.

  • This completes the configuration of MyBatis multiple data sources. If there are more data sources, please refer to the configuration of the second data source.
  1. test
  • After configuring many data sources, you can inject the corresponding data source Mapper into the class to use which data source you want to use in the project.
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {
    @Autowired
    private User1Mapper user1Mapper;
    @Autowired
    private User2Mapper user2Mapper;

    @Test
    public void testInsert() throws Exception {
        user1Mapper.insert(new User("aa111", "a123456", UserSexEnum.MAN));
        user1Mapper.insert(new User("bb111", "b123456", UserSexEnum.WOMAN));
        user2Mapper.insert(new User("cc222", "b123456", UserSexEnum.MAN));
    }
}

Two different Mapper s are injected into the test class above, corresponding to different data sources. Two pieces of data were inserted into the first data source, and one piece of information was inserted into the second data source. After running the test method, two pieces of data were found in the database 1 and one piece of data in the database 2, which proved that the multi-data source test was successful.

Six, summary

This paper introduces the concepts of ORM framework and MyBatis framework, and demonstrates the common scenarios of MyBatis such as adding, deleting, modifying, checking, paging query and multi-data source processing with user data as an example. Through the above examples, we can find that MyBatis isolates the execution of SQL from the code, guarantees the relative independence of code processing and SQL, and has clear hierarchical division. MyBatis is very friendly to dynamic SQL support, and can reuse code to write dynamic SQL efficiently in XML files.