Introduction to Mybatis CRUD

preface

  • As a senior back-end coder, he has to deal with the database every day. Hibernate, a highly encapsulated persistence framework, was first used. Since I came into contact with Mybatis, I have been impressed by its flexibility. I can write SQL myself. Although it is lightweight, although the sparrow is small, it has all kinds of internal organs. This article is about what is Mybatis and how to use Mybatis simply.

What is Mybatis

  • MyBatis is an excellent persistence layer framework, which supports custom SQL, stored procedures and advanced mapping. MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets. MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.

Environment construction

  • The environment used in this article is SpringBoot+Mybatis+Mysql

Maven dependency

  • MySQL driver dependency and Druid connection pool dependency
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
            <scope>runtime</scope>
        </dependency>

        <!--druid Connection pool-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
  • After you click on the package of myboot, you can only see that the package of myspring boot depends on the package of myboot, which you need to import.
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
  • After the above two dependencies are successfully added, the Maven environment has been configured.

Database connection pool configuration (Druid)

  • This is not the focus of this article, and there are many online tutorials. I will simply configure it in SpringBoot application Properties.
##Single data source
spring.datasource.url=jdbc\:mysql\://127.0.0.1\:3306/vivachekcloud_pzhdermyy?useUnicode\=true&characterEncoding\=UTF-8&zeroDateTimeBehavior\=convertToNull&useSSL\=false
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#Initialize connection size
spring.datasource.druid.initial-size=0
#Maximum number of connections used in connection pool
spring.datasource.druid.max-active=20
#Connection pool minimum idle
spring.datasource.druid.min-idle=0
#Get the maximum connection wait time
spring.datasource.druid.max-wait=6000
spring.datasource.druid.validation-query=SELECT 1
#spring.datasource.druid.validation-query-timeout=6000
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
#How often is the configuration interval detected? Idle connections that need to be closed are detected in milliseconds
spring.datasource.druid.time-between-eviction-runs-millis=60000
#Sets the minimum lifetime of a connection in the pool, in milliseconds
spring.datasource.druid.min-evictable-idle-time-millis=25200000
#spring.datasource.druid.max-evictable-idle-time-millis=
#When the removeAbandoned function is enabled, how long must the connection be closed
spring.datasource.druid.removeAbandoned=true
#1800 seconds, or 30 minutes
spring.datasource.druid.remove-abandoned-timeout=1800
#<!--  1800 seconds, or 30 minutes -- >
spring.datasource.druid.log-abandoned=true
spring.datasource.druid.filters=mergeStat
#spring.datasource.druid.verifyServerCertificate
#spring.datasource.filters=stat,wall,log4j
# Open the mergeSql function through the connectProperties property; Slow SQL record
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

Basic concepts

  • dao layer: used to store files interacting with the database. Mybatis interface s are placed in this layer
  • service layer: the file used to store business logic.

Location of configuration xml file

  • By default, the xml file in Mybatis should be placed under the same package as the interface, and the name of the file should be the same.
  • After integration with SpringBoot, there are two configuration methods, which are described in detail below.

application. Set in properties

  • Since it is integrated with SpringBoot, the configuration class of xxxAutoConfiguration is indispensable. The configuration class of Mybatis is MybatisAutoConfiguration, as follows:
@org.springframework.context.annotation.Configuration
@ConditionalOnClass({ SqlSessionFactory.class, SqlSessionFactoryBean.class })
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties(MybatisProperties.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
public class MybatisAutoConfiguration implements InitializingBean {}
  • You can see the line @ EnableConfigurationProperties(MybatisProperties.class), which maps the properties in properties to the member property of MybatisProperties. Therefore, the setting method depends on the properties.
public class MybatisProperties {
  //prefix
  public static final String MYBATIS_PREFIX = "mybatis";

  /**
   * Mybatis Location of the configuration file
   */
  private String configLocation;

  /**
   * Mybatis Location of Mapper's xml file
   */
  private String[] mapperLocations;
  • Therefore, the setting method is very simple, as follows:
## The xml file is placed in the / src/main/resource/mapper / folder
mybatis.mapper-locations=classpath*:/mapper/**/*.xml

Settings in configuration class

  • Not the focus of this chapter, which will be covered in the article on the integration of Mybatis and SpringBoot later.

Configure interface for scanning Mybatis

  • After integration with SpringBoot, it is very simple to scan the interface of Mybatis and generate proxy objects. You only need an annotation.

@Mapper

  • This annotation is marked on the interface class of Mybatis. After SpringBoot is started, the proxy object will be automatically generated after scanning. Examples are as follows:
@Mapper
public interface UserInfoMapper {

    int insert(UserInfo record);

    int insertSelective(UserInfo record);
    }
  • Disadvantages: each interface should be marked with one, which is very chicken ribs. There are at least hundreds of interfaces in a project.

@MapperScan

  • @The upgraded version of Mapper annotation, marked on the configuration class, is used to scan the interface of Mybatis with one click.
  • It is also very simple to use. You can directly specify the package where the interface is located, as follows:
@MapperScan({"com.xxx.dao"})
public class ApiApplication {}
  • @Never reuse MapperScan and @ Mapper annotations.
  • Advantages: one click scanning without configuring each interface.

Basic crud

  • Since you can't avoid crud operation by interacting with the database, feel free to make a proper crud boy.
  • There are actually two sets of method mapping for Mybatis, one is XML file, and the other is annotation. But today, we only talk about the way of XML files. The reason is very simple. The way of annotation is not used by enterprises. Whoever uses it will be unlucky. Ha ha.

query

  • Query statement is one of the most commonly used elements in MyBatis - it is not valuable to store data in the database, but also to retrieve it. Most applications also query more frequently than modify. One of the basic principles of MyBatis is that multiple query operations are usually performed between each insert, update, or delete operation. Therefore, MyBatis has made considerable improvements in query and result mapping. The select element of a simple query is very simple.
<select id="selectPersonById" parameterType="int" resultType="com.myjszl.domain.Person">
  SELECT name,age,id FROM PERSON WHERE ID = #{id}
</select>
  • The corresponding interface method is as follows:
Person selectPersonById(int id);
  • The < Select > tab has many attributes. The common attributes are as follows:

    • id (required): a unique identifier in the namespace that can be used to reference this statement. The method names in and interface should be consistent.
    • parameterType (optional): the fully qualified name or alias of the class that will pass in the parameters of this statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset.
    • resultType: the fully qualified name or alias of the class from which the result is expected to be returned from this statement. Note that if a collection is returned, it should be set to the type contained in the collection, not the type of the collection itself. Only one can be used between resultType and resultMap at the same time.
    • resultMap: a named reference to an external resultMap. Result mapping is the most powerful feature of MyBatis. If you understand it thoroughly, many complex mapping problems can be solved. Only one can be used between resultType and resultMap at the same time.

change

  • The implementation of data change statements insert, update and delete is very close.
  • The following are examples of insert, update and delete statements:
<insert id="insertAuthor">
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

<update id="updateAuthor">
  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>

<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>

{} and & dollar; The difference between {}

  • In the above examples, we can see that #{} is used. The difference between #{} and ${} is also most often asked in the interview of many junior engineers. Now we only need to remember that the difference is that #{} uses JDBC precompiling, which can prevent SQL injection and improve security. ${} is not precompiled, so the security is not enough. Later, the source code explanation of Mybatis will involve why one uses precompiled and the other is useless.

Return of self incrementing ID

  • It is mentioned in the article on Mysql that it is best to design a table with a self increasing ID. whether you use this ID or not, the specific reason is not explained. You can read the previous article.
  • With self incrementing ID, it cannot be returned automatically after insertion, but we need this ID value, so how to return it?
  • The < Insert > tag provides two attributes to solve this problem, as follows:

    • useGeneratedKeys: set to true, which means to return using self incrementing primary key
    • keyProperty: specifies which property of the parameterType the returned self incrementing primary key is mapped to.
  • Suppose person is inserted and the self incrementing primary key id in the person table needs to be returned. The XML file is as follows:
<?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.xxx.dao.PersonMapper">
  <insert id='addPerson' parameterType='com.xxx.domain.Person' useGeneratedKeys="true"
    keyProperty="id" >
    insert into person(name,age)
    values(#{name},#{age});
  </insert>
</mapper>

SQL code snippet

  • This element can be used to define reusable SQL code fragments for use in other statements. Parameters can be determined statically (when loading), and different parameter values can be defined in different include elements. For example:
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
  • This SQL fragment can be used in other statements, for example:
<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>,
    <include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

Turn on hump mapping

  • DBA s often use underscores () when designing databases For example, user_id. However, Java is not standardized. We usually convert it to userId, which is the hump naming method.
  • But when using Mybatis query, for example:
<select id='selectById' resultType='com.xxx.doamin.User'>
  select user_id from user_info
</select>
  • User above_ ID does not correspond to userid in user at all, so it cannot be mapped. At this time, the result of query is that userid is null. Of course, we can use alias, and SQL can be rewritten as select user_id as userId from user_info
  • Another way is to directly open the hump mapping rule of Mybatis without alias, which will be mapped automatically. The opening method is very simple, that is, in application The properties file is configured as follows:
mybatis.configuration.map-underscore-to-camel-case=true

summary

  • This article mainly talks about the integration process of Mybatis and SpringBoot, the basic crud, the attributes of various tags and other contents. It belongs to an entry-level tutorial, and the subsequent contents will be gradually deepened.
  • In addition, the advanced tutorial of MySQL has written five articles, each of which is a classic. An album has been released, and those who are interested can collect it Advanced MySQL.
  • Thank you for your reading. The author will regularly update the original articles. If you think it is good, you can pay attention to this official account.

Tags: Java Mybatis SSM

Posted by Maq on Thu, 19 May 2022 06:14:33 +0300