1. Preparation

1. Build a simple database called springboot_db and create a table under it named t_author, the script is as follows:

   CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
   USE `springboot_db`;
    
   DROP TABLE IF EXISTS `t_author`;
    
   CREATE TABLE `t_author` (
     `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user ID',
     `real_name` varchar(32) NOT NULL COMMENT 'User name',
     `nick_name` varchar(32) NOT NULL COMMENT 'User anonymity',
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
   

2. Add a configuration file and use yaml configuration, i.e. application The configuration of YML (not much different from the application.properties configuration file) connection pool is as follows:

   spring:
     datasource:
       url: jdbc:mysql://127.0.0.1:3306/springboot_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false
       driverClassName: com.mysql.jdbc.Driver
       username: root
       password: root
       type: com.alibaba.druid.pool.DruidDataSource
   

3. Create a pojo class corresponding to the database:

   public class Author {
       private Long id;
       private String realName;
       private String nickName;
   
       // SET and GET methods are omitted
   }
   

2. Mode 1: integration with JdbcTemplate

1. Introducing jdbc dependency

   Access the database through the JdbcTemplate. Spring boot provides the following starter s to support it:

   <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</artifactId>
   </dependency>
   

   Then introduce Junit test Starter:

   <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
   </dependency>
   

2. DAO interface class

   package com.guxf.dao;
   
   import java.util.List;
   
   import com.guxf.domain.Author;
   
   public interface AuthorDao {
   
       int add(Author author);
   
       int update(Author author);
   
       int delete(Long id);
   
       Author findAuthor(Long id);
   
       List<Author> findAuthorList();
   }
   

3. Implementation class of DAO

   package com.guxf.impl;
   
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
   import org.springframework.stereotype.Repository;
   
   import com.guxf.dao.AuthorDao;
   import com.guxf.domain.Author;
   
   @Repository
   public class AuthorDaoJdbcTemplateImpl implements AuthorDao{
       
       @Autowired
       private NamedParameterJdbcTemplate jdbcTemplate;
       
       @Override
       public int add(Author author) {
           String sql = "insert into t_author(id,real_name,nick_name) " +
                   "values(:id,:realName,:nickName)";
           Map<String, Object> param = new HashMap<>();
           param.put("id",author.getId());
           param.put("realName", author.getRealName());
           param.put("nickName", author.getNickName());
           
           return (int) jdbcTemplate.update(sql, param);
       }
   
       @Override
       public int update(Author author) {  
           return 0;
       }
   
       @Override
       public int delete(Long id) {    
           return 0;
       }
   
       @Override
       public Author findAuthor(Long id) {
           return null;
       }
   
       @Override
       public List<Author> findAuthorList() {  
             return null;
       }
   }
   

4. Test the above code through JUnit (slightly modify it according to your actual Application name):

   package com.guxf.boot;
   
   import org.junit.Test;
   import org.junit.runner.RunWith;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.test.context.SpringBootTest;
   import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
   
   import com.guxf.BootApplication;
   import com.guxf.dao.AuthorDao;
   import com.guxf.domain.Author;
   
   @RunWith(SpringJUnit4ClassRunner.class)
   @SpringBootTest(classes = BootApplication.class)
   public class AuthorDaoTest {
   
       @Autowired
       private AuthorDao authorDao;
   
       @Test
       public void testInsert() {
           Author author = new Author();
           author.setId(1L);
           author.setRealName("Mo Yan");
           author.setNickName("crazy");
           
           authorDao.add(author);
           System.out.println("Insert succeeded!");
       }
   }
   

5. Note that the package of the Application class must be the parent package of other packages. The @ SpringBootApplication annotation inherits @ ComponentScan. By default, only the package and sub package of the Application class will be scanned. Structure diagram:
   
   Application code example:

   	package com.guxf;
   	
   	import org.springframework.boot.SpringApplication;
   	import org.springframework.boot.autoconfigure.SpringBootApplication;
   	
   	@SpringBootApplication
   	public class BootApplication {
   	
   	    public static void main(String[] args) {
   	        SpringApplication.run(BootApplication.class, args);
   	    }
   	}
   

3. Mode 2: integration with JPA

1. Introduce dependency

   <!-- introduce JPA -->
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-data-jpa</artifactId>
           </dependency>
   

2. Entity class
   Add an Entity annotation to the POJO class, specify the table name (if not specified, the default table name is author), and then specify the ID and its generation strategy. These are JPA knowledge and have nothing to do with Spring boot. Code:

   package com.guxf.domain;
   
   import javax.persistence.Entity;
   import javax.persistence.GeneratedValue;
   import javax.persistence.Id;
   
   @Entity(name = "t_author")
   public class Author {
       @Id
       @GeneratedValue
       private Long id;
       private String realName;
       private String nickName;
   
       // SET and GET methods are omitted
   }
   

3. Implementation class
   We need to inherit the JpaRepository class. Here we implement two query methods. The first is the query that conforms to the JPA naming specification. JPA will automatically help us generate query statements. The other way is to implement JPQL (a kind of SQL query supported by JPA):

   package com.guxf.service;
   
   import java.util.List;
   import java.util.Optional;
   
   import org.springframework.data.jpa.repository.JpaRepository;
   import org.springframework.data.jpa.repository.Query;
   import org.springframework.data.repository.query.Param;
   
   import com.guxf.domain.Author;
   
   public interface AuthorRepository extends JpaRepository<Author, Long> {
   
       public Optional<Author> findById(Long userId);
   
       @Query("select au from com.guxf.domain.Author au where nick_name=:nickName")
       public List<Author> queryByNickName(@Param("nickName") String nickName);
   }
   

4. Test code

   package com.guxf.boot;
   
   import static org.junit.Assert.*;
   
   import java.util.List;
   
   import org.junit.Test;
   import org.junit.runner.RunWith;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.test.context.SpringBootTest;
   import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
   
   import com.guxf.BootApplication;
   import com.guxf.domain.Author;
   import com.guxf.service.AuthorRepository;
   
   @RunWith(SpringJUnit4ClassRunner.class)
   @SpringBootTest(classes = BootApplication.class)
   public class AuthorDaoTestJPA {
   
       @Autowired
       private AuthorRepository authorRepository;
   
       @Test
       public void testQuery() {
           List<Author> authorList = authorRepository.queryByNickName("crazy");
           assertTrue(authorList.size() > 0);
           System.out.println("success!");
       }
   }
   

4. Mode 3: integration with MyBatis

1. Dependency and configuration files

   <!-- introduce Mybatis -->
           <dependency>
               <groupId>org.mybatis.spring.boot</groupId>
               <artifactId>mybatis-spring-boot-starter</artifactId>
               <version>1.1.1</version>
           </dependency>
   

   MyBatis can generally specify the SQL to operate the database through XML or annotation. First, we need to configure the mapper directory. We are in application Configure in YML:

   spring:
     datasource:
       url: jdbc:mysql://127.0.0.1:3306/springboot_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false
       driverClassName: com.mysql.jdbc.Driver
       username: root
       password: root
       type: com.alibaba.druid.pool.DruidDataSource
   
   mybatis:
     #config-locations: mybatis/mybatis-config.xml
     mapper-locations: com/guxf/mapper/*.xml
     type-aliases-package: com.guxf.mapper.AuthorMapper
   

2. mapper interface and XML file

   package com.guxf.mapper;
   
   import org.apache.ibatis.annotations.Mapper;
   
   import com.baomidou.mybatisplus.mapper.BaseMapper;
   import com.guxf.domain.Author;
   @Mapper
   public interface AuthorMapper extends BaseMapper<Author> {
   
       public Long insertAuthor(Author author);
   
       public void updateAuthor(Author author);
   
       public Author queryById(Long id);
   }
   

   <?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.guxf.mapper.AuthorMapper">
       <!-- It should be noted here that due to our database definition id Storage type is intbig,But our Entity in Id yes Long -->
       <!-- There is no problem inserting in the previous two methods, and the database type exception is reported here -->
       <!-- So the database ID Type changed to Varchar -->
       <resultMap id="authorMap" type="com.guxf.domain.Author">
           <id column="id" property="id" jdbcType="VARCHAR" />
           <result column="real_name" property="realName" jdbcType="VARCHAR" />
           <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
       </resultMap>
   
       <sql id="base_column">
           id,real_name,nick_name
       </sql>
   
       <insert id="insertAuthor" parameterType="com.guxf.domain.Author">
           INSERT INTO
           t_author(
           <include refid="base_column" />
           )
           VALUE
           (#{id},#{realName},#{nickName})
       </insert>
   
       <update id="updateAuthor" parameterType="com.guxf.domain.Author">
           UPDATE t_author
           <set>
               <if test="realName != null">
                   real_name = #{realName},
               </if>
               <if test="nickName != null">
                   nick_name = #{nickName},
               </if>
           </set>
           WHERE id = #{id}
       </update>
   
       <select id="queryById" parameterType="Long" resultMap="authorMap">
           SELECT id,
           <include refid="base_column"></include>
           FROM t_author
           WHERE id = #{id}
       </select>
   
   </mapper>
   

3. Test class code

   package com.guxf;
   
   import static org.junit.Assert.*;
   
   import org.junit.Test;
   import org.junit.runner.RunWith;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.test.context.SpringBootTest;
   import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
   
   import com.guxf.BootApplication;
   import com.guxf.domain.Author;
   import com.guxf.mapper.AuthorMapper;
   
   @RunWith(SpringJUnit4ClassRunner.class)
   @SpringBootTest(classes = BootApplication.class)
   public class AuthorDaoTestMybatis {
   
       @Autowired
       private AuthorMapper mapper;
   
       @Test
       public void testInsert() {
           Author author = new Author();
           author.setId(4L);
           author.setRealName("Tang Yu");
           author.setNickName("Xiaobao");
           mapper.insertAuthor(author);
           System.out.println("success!");
       }
   
       @Test
       public void testMybatisQuery() {
           Author author = mapper.queryById(1L);
           assertNotNull(author);
           System.out.println(author);
       }
   
       @Test
       public void testUpdate() {
           Author author = mapper.queryById(2L);
           author.setNickName("Moon");
           author.setRealName("Lin Yueru");
           mapper.updateAuthor(author);
       }
   }
   

5. Mode 4: integration with mybatis plus

In fact, mybatis and mybatis plus come from the same vein, just like the relationship between spring and springboot. It is not innovation, but optimization. The most important interfaces in mybatis plus are BaseMapper and IService. In fact, the IService interface calls BaseMapper, and the latter is used to connect with the database.
Basically, general database operations can be written directly in the service without writing cumbersome XML files.