mybatis integration springboot

Guide Pack

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!-- alibaba Of druid Database Connection Pool -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>

To configure

spring:
    datasource:
      name: mysql_test
      type: com.alibaba.druid.pool.DruidDataSource
      druid:
        filters: stat
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=UTC
        username: root
        password: root
        initial-size: 1
        min-idle: 1
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 'x'
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: false
        max-pool-prepared-statement-per-connection-size: 20

mybatis:
  mapper-locations: classpath*:/mybaits/mappings/*.xml
  type-aliases-package: com.eximple.model
  config-location: #classpath*:/mybaits/mybatis-config.xml
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  type-handlers-package:                 #Scanning packages for typeHandlers
  check-config-location: true            #Check for profile existence
  executor-type: SIMPLE                  #Set execution mode (SIMPLE, REUSE, BATCH), default is SIMPLE

Add to Startup Class

@MapperScan("com.eximple.mapper")

--------------------------------------------------------

Paging Plugin for pagehelper

Guide Pack

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.5</version>
</dependency>

To configure

pagehelper:
  helperDialect: mysql
  reasonable: false
  supportMethodsArguments: true
  params: count=countSql

or

@Bean
public PageHelper pageHelper(){
 PageHelper pageHelper = new PageHelper();
 Properties properties = new Properties();
 properties.setProperty("helperDialect", "mysql");
 properties.setProperty("offsetAsPageNum", "true");
 properties.setProperty("rowBoundsWithCount", "true");
 properties.setProperty("reasonable", "false");
 pageHelper.setProperties(properties);
}

or

@Bean
public PageHelper pageHelper(){
 PageHelper pageHelper = new PageHelper();
 Properties properties = new Properties();
 properties.setProperty("dialect", "mysql");
 properties.setProperty("offsetAsPageNum", "true");
 properties.setProperty("rowBoundsWithCount", "true");
 properties.setProperty("reasonable", "true");
 pageHelper.setProperties(properties);
}

or

/**
 *   mybatis-plus jPaginate
 */
@Bean
public PaginationInterceptor paginationInterceptor() {
    PaginationInterceptor page = new PaginationInterceptor();
    page.setDialectType("mysql");
    return page;
}

Note: The plugins must be set before sqlSessionFactoryBean.getObject().SqlSessionFactory takes the plugins at the time of generation and sets them into Configuration, which will not be injected if set later.

Paging business code

public PageInfo<UserDomain> findUsers(int pageNum, int pageSize) {
    PageHelper.startPage(pageNum, pageSize);
    List<User> users = userDao.findUsers();
    PageInfo result = new PageInfo(users);
    return result;
}

--------------------------------------------------------

Automatically generate code plug-ins using mybatis generator

Join in pom

<plugin>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-maven-plugin</artifactId>
    <version>1.3.2</version>
    <configuration>
        <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
        <overwrite>true</overwrite>
        <verbose>true</verbose>
    </configuration>
</plugin>

Create file generatorConfig.xml under resources/generator

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!-- Database Driver:Select the database driver package on your local hard drive-->
    <classPathEntry  location="d:\MySQL\connector\mysql-connector-java-5.1.38-bin.jar"/>
    <context id="DB2Tables"  targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <!-- Whether to remove automatically generated comments true: Yes: false:no -->
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--Database Links URL，User name, password -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/test" userId="root" password="root">
        </jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!-- Package name and location where the model was generated-->
        <javaModelGenerator targetPackage="com.examaple.model" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!-- Package name and location of the generated mapping file-->
        <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources/mybatis/mappings">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!-- generate DAO Package name and location of-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.examaple.mapper" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!-- Table to be generated tableName Is the table name or view name in the database domainObjectName Is the entity class name-->
        <table tableName="t_user" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

Run after configuring run parameters

--------------------------------------------------------

mybatis inserts data and returns the id value of the new data

xml insert method plus useGeneratedKeys and keyProperty configuration

<insert id="insertUser" useGeneratedKeys="true" keyProperty="userId" parameterType="com.examaple.entity.User">  
　insert into t_User(user_name,password)  
　values(#{userName},#{password}) 
</insert>

--------------------------------------------------------

mybatis implements database table Association query with xml tag element

One-on-one
One-to-one association mapping using the <association>element is straightforward and requires only the following two sample configurations to reference

One-to-many
The <resultMap>element contains a <collection>child element through which MyBatis handles one-to-many associations
The <collection>child element has most of the same attributes as the <association>element, but it also contains a special attribute--ofType
The ofType attribute corresponds to the javaType attribute, which specifies the type of element contained in the collection class attribute in the entity object.

Many-to-many
Many-to-many Association queries can also be processed using the <collection > element described earlier (which is basically the same as one-to-many Association query statements)
--------------------------------------------------------

Writing greater than or equal to less than or equal in mybatis

First way of writing:
Original Symbol < <= > >= and'* '
Substitution symbol < <= > >= &&apos;"
For example, sql is as follows:
create_time &gt;= #{startTime} and  create_time &lt;= #{endTime}

Second way of writing:
Less than or equal to
<![CDATA[ <= ]]>
Greater than or equal to
<![CDATA[ >= ]]>
For example, sql is as follows:
create_time <![CDATA[ >= ]]> #{startTime} and  create_time <![CDATA[ <= ]]> #{endTime}

--------------------------------------------------------

The difference between'#{}'and'${}' in mybatis

1. #corresponds to double quotation marks on the data, $corresponds to displaying the data directly.
2. #{} is handled according to the type of parameter, for example, if a String type is passed in, double quotation marks will be added to the parameter.When #{} parameters are precompiled in SQL, they are replaced with a placeholder? To prevent SQL injection.
3. ${} Remove the parameter without any processing, and put it directly into the statement as a simple string substitution, which participates in the precompilation of SQL and requires manual filtering of parameters to prevent SQL injection.
4. #{} is therefore preferred in mybatis; consider using ${} when you need to dynamically pass in a table or column name, such as a sort field

--------------------------------------------------------

MyBatis calls stored procedures

For example, call procedure_getUserById stored procedure in UserMapper.xml
<!--Query users based on id-->
<select id="getUserById" parameterType="Integer" resultType="user" statementType="CALLABLE">
    {call procedure_getUserById(#{id,mode=IN})}
</select>

--------------------------------------------------------

Turn on global delayed loading in Mybatis configuration

<configuration>
    <settings>
<!--Turn on global lazy loading-->
        <setting name="lazyLoadingEnabled" value="true"/>
<!--Turn off immediate loading without actually configuring it, defau lt ing to false-->
        <setting name="aggressiveLazyLoading" value="false"/>
<!--Turn on sql for Mybatis to perform information printing-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
</configuration>

--------------------------------------------------------

Common configurations in the mybatis configuration file

Configuration in db.properties file:

myBatis.cache.enabled=true
myBatis.lazy.loading.enabled=true
myBatis.aggressive.lazy.loading=false

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=UTC
jdbc.username=root
jdbc.password=123456

mybatis-config.xml configuration:

<configuration>
    
    <!-- Quote db.properties configuration file -->
    <properties resource="db.properties"/>

    <settings>
        <setting name="cacheEnabled" value="${myBatis.cache.enabled}"/>
        <!--Turn on global lazy loading-->
        <setting name="lazyLoadingEnabled" value="${myBatis.lazy.loading.enabled}"/>
        <!--Turn off immediate loading without actually configuring it, defaulting to false-->
        <setting name="aggressiveLazyLoading" value="${myBatis.aggressive.lazy.loading}"/>
        <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"/>
        <!--open Mybatis Of sql Perform related information printing-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
    
    <environments default="mysql">
     <environment id="development1">
       <transactionManager type="JDBC"/>
       <dataSource type="POOLED">    
         <property name="driver" value="${jdbc.driver}"/>
         <property name="url" value="${jdbc.url}"/>
         <property name="username" value="${jdbc.username}"/>
         <property name="password" value="${jdbc.password}"/>
       </dataSource>
     </environment>
     <environment id="development2">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">    
          <property name="driver" value="${jdbc.driver}"/>
          <property name="url" value="${jdbc.url}"/>
          <property name="username" value="${jdbc.username}"/>
          <property name="password" value="${jdbc.password}"/>
        </dataSource>
      </environment> 
    </environments>
    
    <typeAliases>
        <package name="com.example.entity"/>
    </typeAliases>
    <mappers>
        <package name="com.yan.mapper"/>
    </mappers>
    
    <!-- Configure Paging Plugins -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!-- Set database type Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL Six databases-->        
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>

</configuration>