Of Stack

Spring Boot sample code for actual database operations

  • 2020-12-10 00:45:02
  • OfStack

In the previous article, I explained the basic principles and usage of Spring boot through a simple HelloWorld program. This article focuses on how to access the database through spring boot. This article will demonstrate three ways to access the database: JdbcTemplate, JPA, and Mybatis. Have already mentioned before, this series with a blog system as the basis of explaining, so this article will explain the article storage and access (but not including the details of the article), because the final implementation is done through MyBatis, therefore, do a simple demo for JdbcTemplate and JPA, MyBatis part will complete implementation of articles to add and delete.

1. Preparation

Before you can demonstrate these approaches, you need to prepare a few things. The first is the database. This system is implemented by MySQL. We need to create a table of tb_article first: 


DROP TABLE IF EXISTS `tb_article`;

CREATE TABLE `tb_article` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL DEFAULT '',
 `summary` varchar(1024) NOT NULL DEFAULT '',
 `status` int(11) NOT NULL DEFAULT '0',
 `type` int(11) NOT NULL,
 `user_id` bigint(20) NOT NULL DEFAULT '0',
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `public_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The following demonstration will add, delete, modify and check this table. You should see that there is no details of the article in this table. The reason is that the details of the article are relatively long, and if placed in this table, it will easily affect the efficiency of query article list. In addition, we need to configure the database connection pool, here we use druid connection pool, and the configuration file uses yaml configuration, namely ES25en.yml (you can also use application.properties configuration file, there is no big difference, if you are not familiar with ymal, you can also check 1 if you are interested, it is easier). The connection pool configuration is as follows: 


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

Finally, we also need to create the POJO class corresponding to the database, the code is as follows: 


public class Article {
  private Long id;
  private String title;
  private String summary;
  private Date createTime;
  private Date publicTime;
  private Date updateTime;
  private Long userId;
  private Integer status;
  private Integer type;

}

Ok, that's all you need to do, now you're ready to implement the database operation.

2. Integration with JdbcTemplate

First of all, we will access the database through JdbcTemplate, here only to demonstrate the insertion of data. As mentioned in the previous article, Spring boot provides many starter to support different functions. To support JdbcTemplate, we only need to introduce the following starter: 


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

Now we can use JdbcTemplate for data insertion: 


public interface ArticleDao {
  Long insertArticle(Article article);
}

@Repository
public class ArticleDaoJdbcTemplateImpl implements ArticleDao {

  @Autowired
  private NamedParameterJdbcTemplate jdbcTemplate;

  @Override
  public Long insertArticle(Article article) {
    String sql = "insert into tb_article(title,summary,user_id,create_time,public_time,update_time,status) " +
        "values(:title,:summary,:userId,:createTime,:publicTime,:updateTime,:status)";
    Map<String, Object> param = new HashMap<>();
    param.put("title", article.getTitle());
    param.put("summary", article.getSummary());
    param.put("userId", article.getUserId());
    param.put("status", article.getStatus());
    param.put("createTime", article.getCreateTime());
    param.put("publicTime", article.getPublicTime());
    param.put("updateTime", article.getUpdateTime());
    return (long) jdbcTemplate.update(sql, param);
  }
}

We tested the above code with JUnit: 


@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = Application.class)
public class ArticleDaoTest {

  @Autowired
  private ArticleDao articleDao;

  @Test
  public void testInsert() {
    Article article = new Article();
    article.setTitle(" Test the title ");
    article.setSummary(" The test paper ");
    article.setUserId(1L);
    article.setStatus(1);
    article.setCreateTime(new Date());
    article.setUpdateTime(new Date());
    article.setPublicTime(new Date());
    articleDao.insertArticle(article);
  }
}

To support the above test program, you also need to introduce an starter: 


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

As can be seen from the above code, in fact, there is no configuration except start introduced into jdbc, which is all the automatic help of spring boot to complete the configuration process. Need to be aware of the code above Application the location of the class, the class must be in Dao class parent package, such as Dao here are in com. pandy. blog. dao under this package, now we put the Application. java. This class from com pandy. blog move this package to com. The pandy. blog. app this package, will appear the following error: 


Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'com.pandy.blog.dao.ArticleDao' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.raiseNoMatchingBeanFound(DefaultListableBeanFactory.java:1493)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1104)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066)
 at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:585)
 ... 28 more

In other words, the implementation of ArticleDao cannot be found. What is the reason? As we saw in the previous post, the @SpringBootApplication annotation inherits from @ComponentScan and by default scans only the packages and subpackages in which the Application class resides. Therefore, for the above error, in addition to keeping the Application class in the parent package of Dao, you can also specify the scanned package to resolve: 


@SpringBootApplication
@ComponentScan({"com.pandy.blog"})
public class Application {
  public static void main(String[] args) throws Exception {
    SpringApplication.run(Application.class, args);
  }
}

3. Integration with JPA

Now let's look at how to implement database operations in JPA's way. Again, similar to JdbcTemplate, we need to introduce the corresponding starter: 


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

Then we need to add Entity annotations to POJO class, and specify the table name (if not, the default table name is article), and then specify ID and its generation strategy. These are all JPA knowledge, and have nothing to do with Spring boot. If you are not familiar with the JPA knowledge, please read: 


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

Finally, we need to inherit the JpaRepository class, here we implement two query methods, the first is the JPA naming convention, JPA will automatically help us complete the query statement generation, and the other is our own implementation of JPQL (JPA support 1 kind of SQL query). 


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

Ok, we can test the above code again in 1: 


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

Note that there is still a similar problem with JdbcTemplate. The startup class of Application needs to be removed from the parent package of Respository and Entity classes, otherwise the following error will occur: 


Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'com.pandy.blog.dao.ArticleRepository' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.raiseNoMatchingBeanFound(DefaultListableBeanFactory.java:1493)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1104)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066)
 at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:585)
 ... 28 more

Of course, it is also possible to specify the scanned JPA package by annotating @EnableJpaRepositories, but this still does not work, and the following error occurs: 


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

This error indicates that Entity cannot be recognized, so you also need to specify the package of Entity by annotating @EntityScan. The final configuration is as follows: 


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

4. Integration with MyBatis

Finally, let's look at how to achieve database access through MyBatis. Again, we will introduce starter: 


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

Since the starter is not officially provided by spring boot, the version number is different from Spring boot and needs to be specified manually.

MyBatis1 can generally specify SQL for database operation through XML or annotations. Personally, I prefer XML, so this article only demonstrates access to the database through XML. First, we need to configure the mapper directory. We configured in ES166en. yml: 


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

The configuration consists of three parts, one of which is mybatis itself, and one of which is aliases for basic types. The second is to specify the location of the mapper file, and the third is an alias for the POJO class. This configuration can also be achieved through Java configuration, due to the length of the problem, I will not elaborate here, interested friends can implement 1.

After the configuration, we first wrote the interface corresponding to mapper: 


public interface ArticleMapper {

  public Long insertArticle(Article article);

  public void updateArticle(Article article);

  public Article queryById(Long id);

  public List<Article> queryArticlesByPage(@Param("article") Article article, @Param("pageSize") int pageSize,
                       @Param("offset") int offset);

}

For the time being, the interface defines only four methods: add, update, and query by ID and paging. This is an interface and, like JPA, does not require an implementation class. Next we write the XML file: 


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

Finally, we need to manually specify the packages scanned by mapper: 


@SpringBootApplication
@MapperScan("com.pandy.blog.dao")
public class Application {
  public static void main(String[] args) throws Exception {
    SpringApplication.run(Application.class, args);
  }
}

Now that the integration with MyBatis is complete, let's test 1 again: 


@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = Application.class)
public class ArticleMapperTest {

  @Autowired
  private ArticleMapper mapper;

  @Test
  public void testInsert() {
    Article article = new Article();
    article.setTitle(" Test the title 2");
    article.setSummary(" The test paper 2");
    article.setUserId(1L);
    article.setStatus(1);
    article.setCreateTime(new Date());
    article.setUpdateTime(new Date());
    article.setPublicTime(new Date());
    mapper.insertArticle(article);
  }

  @Test
  public void testMybatisQuery() {
    Article article = mapper.queryById(1L);
    assertNotNull(article);
  }

  @Test
  public void testUpdate() {
    Article article = mapper.queryById(1L);
    article.setPublicTime(new Date());
    article.setUpdateTime(new Date());
    article.setStatus(2);
    mapper.updateArticle(article);
  }

  @Test
  public void testQueryByPage(){
    Article article = new Article();
    article.setUserId(1L);
    List<Article> list = mapper.queryArticlesByPage(article,10,0);
    assertTrue(list.size()>0);
  }
}

5. To summarize

This paper demonstrates the integration of Spring boot, JdbcTemplate, JPA and MyBatis. On the whole, the configuration is relatively simple, which should be obvious to students who have done relevant configuration before. Spring boot really provides us with great help in this aspect. In subsequent articles, we can only use MyBatis 1 this kind of way to proceed with the operation of the database, there is one point to note is under 1, MyBatis paging query here is handwritten, this page can be finished through plug-ins, in the formal development but this with Spring boot has nothing to do, so in this paper, a temporary paging through the way of manual processing.

Related articles: