SpringBoot + Mybatis series tutorial CURD basic use posture

This is the 15th day of my participation in the August More Text Challenge. For details, see:August is more challenging

【DB series 】Mybatis series tutorial CURD basic use posture

Mybatis, as the ORM framework of data, is still very considerable in the domestic application market. When I first started to work, I used Spring + Mybatis for development, and then I also used Hibernate, JDBCTemplate, JooQ. Mybatisplus and other frameworks,

In terms of personal use feeling, jOOQ’s use posture is similar to writing SQL, basically can write SQL without additional training, can immediately start;

Hibernate’s main feature is to map SQL statements with method names, which is very characteristic, but when the query conditions are more complex, it is not so friendly to small white;

Jdbctemplate, on the other hand, is nice and flexible to use in small, lightweight DB operations, but there are a few points that need to be taken care of. For example, queryForObject throws an exception instead of returning null when no data is found.

As for MyBatis and derivative myBatis – Plus, that is, the next protagonist, how its characteristics, why by a large number of domestic developers sought after, it as the db operation of the first ORM framework, let us see it after

I. Build the basic environment

The following project demonstration of Mybatis is mainly run in the SpringBoot environment, and the underlying database uses MySql. The corresponding version information is as follows

Springboot: 2.2.0. RELEASE
Mysql: 5.7.22

1. Configure the SpringBoot project

The project creation process for SpringBoot is omitted. Here are the core POM dependencies

<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>
Copy the code

The core depends on MyBatis -spring-boot-starter. For version selection, go to the MVN repository and find the latest one

Another thing that is not available is the DB configuration information, appliaction.yml

spring:
  datasource:
    url: JDBC: mysql: / / 127.0.0.1:3306 / story? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:
Copy the code

2. Prepare the database

In the local database, a new table has been added as follows

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT 'money',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time'.PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;
Copy the code

The curDs that follow in this article are all for this table

II. MyBatis CURD

Next, we will implement the whole process of mysql operation based on MyBatis from 0 to 1

1. Basic objects

As any frequent user of Mybatis may know, operating a DB usually comes with several indispensable things

Database entity classes: Can be understood as Java Bean objects to which database table locks are mapped
Mapper Interface: Interface class, which defines db operations
XML file: Corresponding to the above interface, the actual SQL is written in the XML file

Mybatis recommended gameplay is to write SQL using XML, but the official way to annotate is also provided, so XML file is not necessary; How annotations operate is described later; This article will focus on the traditional XML collocation posture

For this table, the first step is to define the entity class MoneyPo

@Data
@NoArgsConstructor
@AllArgsConstructor
public class MoneyPo {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Timestamp updateAt;
}
Copy the code

The above three annotations belong to lombok knowledge, there are unclear partners can search

Next up is the Mapper interface, which is MoneyMapper

// Note the @mapper annotation to indicate that the interface belongs to Mybatis's Mapper object
@Mapper
public interface MoneyMapper {}Copy the code

Then there is the XML file moneymapper.xml for the Mapper interface

Note that the XML file is placed under the resources file, and the directory structure of the XML file is exactly the same as the package path of the Mapper interface above. 【DB series 】SpringBoot Mybatis Mapper interface and Sql binding


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.git.hui.boot.mybatis.mapper.MoneyMapper">
</mapper>
Copy the code

2. Data insertion

In the first three steps, the entity class, interface object and XML file that we need are initialized. The next step is to enter our CURD link to realize the addition, deletion, change and check of the database. Here we mainly use the INSERT tag

For example, if we want to insert a piece of data, the first thing we need to do is define a method in the Mapper interface

int savePo(@Param("po") MoneyPo po);
Copy the code

Next comes the corresponding SQL in the XML file

<insert id="savePo" parameterType="com.git.hui.boot.mybatis.entity.MoneyPo" useGeneratedKeys="true"
        keyProperty="po.id">
  INSERT INTO `money` (`name`, `money`, `is_deleted`)
  VALUES
(#{po.name}, #{po.money}, #{po.isDeleted});
</insert>
Copy the code

2.1 Description

Notice the XML file above

ParameterType: specifies the parameterType
UseGenerateKeys + keyProperty: indicates that the primary key ID that needs to be inserted into the DB will be written to the ID field of the entity class
SQL statement parameters: such as# {}Enter the variable name in curly brackets, and the above ispo.nameThis means that the name member of the Po object is used as the name field of the DB

Here are the important points:

Pass parameters in addition to use# {}In addition, you can also useThe ${}, the difference is that the first is a parameter placeholder, followed by a string replacement, so there is a risk of SQL injection

For example

select * from money where id=${id}
select * from money where id=#{id}
Copy the code

When id =1 or id =1, the corresponding SQL becomes

The first SQL will return all the data
select * from money where id = 1 or 1 =1
The following throws an SQL exception
select * from money where id = '1 or 1=1'
Copy the code

2.2 Batch Insert

In addition to the single insert above, batch inserts are also ok, similar to the previous position

int batchSave(@Param("list") List<MoneyPo> list);
Copy the code

The corresponding SQL is as follows

<insert id="batchSave" parameterType="com.git.hui.boot.mybatis.entity.MoneyPo"  useGeneratedKeys="true" keyProperty="id">
    insert ignore into `money` (`name`, `money`, `is_deleted`)
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.name}, #{item.money}, #{item.isDeleted})
    </foreach>
</insert>
Copy the code

The foreach tag will be explained in a later blog post, but it can be simply interpreted as traversal

3. Data query

Query can be said to be the most common situation in our daily development, here first give a simple query demo, as for more complex query conditions (such as table, subquery, conditional query, etc.) in the following blog to introduce

For queries based on primary keys, the select label is used

MoneyPo findById(int id);
Copy the code

The corresponding SQL

  <resultMap id="BaseResultMap" type="com.git.hui.boot.mybatis.entity.MoneyPo">
    <id column="id" property="id" jdbcType="INTEGER"/>
    <result column="name" property="name" jdbcType="VARCHAR"/>
    <result column="money" property="money" jdbcType="INTEGER"/>
    <result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
    <result column="create_at" property="createAt" jdbcType="TIMESTAMP"/>
    <result column="update_at" property="updateAt" jdbcType="TIMESTAMP"/>
</resultMap>
<sql id="money_po">
  id, name, money, is_deleted, create_at, update_at
</sql>

<select id="findById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="money_po"/>
    from money where id=#{id}
</select>
Copy the code

Focusing on the above implementation, the content of the SELECT statement is relatively simple, but there are a few points to note

SQL tag: internally defines the DB fields to be queried. The biggest feature is for the following query statements to passincludeTo achieve code snippet reuse
ResullMap: From the comparison between DB field and MoneyPo entity class, we can know that some field names are not exactly the same, for example, db uses underline, Java uses hump, then how to map DB field and Java member variables? Used hereresultLabel to specify the mapping between the two, as well as the type

(I believe the above will always accompany you in your development career)

4. Data update

Updates are made with the update tag, which is less informative than the two above

int addMoney(@Param("id") int id, @Param("money") int money);
Copy the code

The corresponding SQL is as follows

<update id="addMoney" parameterType="java.util.Map">
    update money set money=money+#{money} where id=#{id}
</update>
Copy the code

instructions

The parameterType in the tag above is actually omitted here
@ParamNote: This is mainly used to specify parameter names. In XML, you can use internally defined names as parameter variables. Without this annotation, in XML, parameter variables are usedparam0.param1To replace the

5. Delete data

Delete using the delete tag

int delPo(@Param("id") int id);
Copy the code

The corresponding SQL is as follows

<delete id="delPo" parameterType="java.lang.Integer">
    delete from money where id = #{id,jdbcType=INTEGER}
</delete>
Copy the code

6. Use a demo

The mapper interface defines the complete CURD. The next step is to use the Mapper interface to implement the interaction. In Spring, it is very simple to use the mapper interface

@Repository
public class MoneyRepository {
    @Autowired
    private MoneyMapper moneyMapper;

    public void testBasic(a) {
        MoneyPo po = new MoneyPo();
        po.setName("mybatis user");
        po.setMoney((long) random.nextInt(12343));
        po.setIsDeleted(0);

        moneyMapper.savePo(po);
        System.out.println(po);
        MoneyPo out = moneyMapper.findById(po.getId());
        System.out.println("query:" + out);
        moneyMapper.addMoney(po.getId(), 100);
        System.out.println("after update:" + moneyMapper.findById(po.getId()));
        moneyMapper.delPo(po.getId());
        System.out.println("after del:"+ moneyMapper.findById(po.getId())); }}Copy the code

The following output is displayed

MoneyPo(id=552, name=mybatis user, money=7719, isDeleted=0, createAt=null, updateAt=null) query:MoneyPo(id=552, Name =mybatis user, money=7719, isDeleted=0, createAt=2021-08-01 11:47:23.0, UpdateAt =2021-08-01 11:47:23.0) after update:MoneyPo(id=552, name=mybatis user, money=7819, isDeleted=0, CreateAt =2021-08-01 11:47:23.0, updateAt=2021-08-01 11:47:23.0) after del:nullCopy the code

7. Summary

I believe you see here, build a CURD project of MyBatis database should be not a problem, the main knowledge points of this paper are as follows

Mybatis project three suites: entity class + Mapper interface + XML file
Database additions and deletions

Some of the knowledge points are more important, this article is just thrown out, interested partners can continue to pay attention to the follow-up updates

The following knowledge points will be explained in more detail later

How do I get the primary key ID of the inserted data
In batch scenariosforeachLabel use
Mapping of database table structures to Java entity classesresultMapThe label
How Mapper interfaces are associated with XML files
How can the Mapper interface be scanned and be used by Spring Bean objects
Mapper interface and XML parameter passing mode@Paramannotations
Two ways to write SQL parameter substitution# ${}, {}
Pass the parameter type and return the value type definition
Code reuse fragmentsqlThe label

III. Can’t miss the source code and related knowledge

0. Project

Project: github.com/liuyueyi/sp…
Source: github.com/liuyueyi/sp…

1. Wechat official account: a gray Blog

The above content is not as good as the letter, purely the words of a family, due to the limited personal ability, there are inevitably omissions and mistakes, such as found bugs or better suggestions, welcome criticism and correction, not grudging gratitude

Below a gray personal blog, record all study and work in the blog, welcome everyone to visit

A gray Blog personal Blog blog.hhui. Top
A Gray Blog-Spring feature Blog Spring.hhui. Top

SpringBoot + Mybatis series tutorial CURD basic use posture

I. Build the basic environment

1. Configure the SpringBoot project

2. Prepare the database

II. MyBatis CURD

1. Basic objects

2. Data insertion

2.1 Description

2.2 Batch Insert

3. Data query

4. Data update

5. Delete data

6. Use a demo

7. Summary

III. Can’t miss the source code and related knowledge

0. Project

1. Wechat official account: a gray Blog

Related Posts

Talk about the go-bank-Transfer project’s practice of Clean Architecture

Did you know that Redis can implement delay queuing?

Go+Python Hybrid Development Course part 01: Building a development environment