In the previous article, the basic principles and use of Spring boot have been explained through a simple HelloWorld program. This article mainly explains how to access the database through spring boot. This article will demonstrate three ways to access the database. The first is JdbcTemplate, the second is JPA, and the third is Mybatis. As mentioned before, this series will use a blog system as the basis for explanation, so this article will explain the storage and access of the article (but does not include the details of the article). Because the final implementation is completed through MyBatis, only a simple demonstration of JdbcTemplate and JPA will be made, and the MyBatis part will fully implement the addition, deletion, modification and search of the article.
1. Preparation
Before demonstrating these methods, you need to prepare something first. The first is the database. This system is implemented using MySQL. We need to create a tb_article table 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 subsequent demonstration will add, delete, modify and check this table. You should see that there are no details of the article in this table. The reason is that the details of the article are relatively long. If placed in this table, it will easily affect the efficiency of querying the article list, so the details of the article will be stored in another table separately. In addition, we need to configure the database connection pool. Here we use the druid connection pool. In addition, the configuration file is configured using yaml, that is, application.yml (you can also use the application.properties configuration file. There is no big difference. If you are not familiar with ymal and are interested, you can check it out, which is relatively simple). 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 establish a 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 the work you need to prepare, and now start implementing the database operation.
2. Integration with JdbcTemplate
First, we first access the database through JdbcTemplate. Here we only demonstrate the insertion of data. As mentioned in the previous article, Spring boot provides many starters 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 implement data insertion through JdbcTemplate:
public interface ArticleDao { Long insertArticle(Article article);}@Repositorypublic 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("createTime", article.getCreateTime()); param.put("publicTime", article.getPublicTime()); param.put("updateTime", article.getUpdateTime()); return (long) jdbcTemplate.update(sql, param); }}We use JUnit to test the above code:
@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 title"); article.setSummary("test summary"); 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 a starter:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency>
From the above code, we can see that in fact, there is basically no configuration except for the introduction of jdbc start. This is the automatic configuration process of spring boot. The above code needs to pay attention to the location of the Application class. This class must be located in the parent package of the Dao class. For example, Dao is located in the com.pandy.blog.dao package. Now we move the Application.java class from the com.pandy.blog package to the com.pandy.blog package, and the following error will appear:
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 moreIn other words, the implementation of ArticleDao cannot be found. What is the reason? In the previous blog post, we have seen that the annotation @SpringBootApplication inherits @ComponentScan, which only scans the packages and subpackages where the Application class is located by default. Therefore, for the above error, in addition to keeping the Application class in Dao's parent package, you can also specify the scanned package to solve:
@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 we will start to explain how to implement database operations through JPA. It's still similar to JdbcTemplate. First, 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 annotation to the POJO class and specify the table name (if not specified, the default table name is article), and then we need to specify the ID and its generation strategy. These are all JPA knowledge and have nothing to do with Spring boot. If you are not familiar with it, you can look at the JPA knowledge points:
@Entity(name = "tb_article")public class Article { @Id @GeneratedValue private Long id; private String title; private String summary; private Date createTime; private Date publicTime; private Date updateTime; private Long userId; private Integer status;}Finally, we need to inherit the JpaRepository class. Here we implement two query methods. The first is a query that complies with the JPA naming specification. JPA will automatically help us complete the generation of query statements. The other way is to implement JPQL (a SQL-like query supported by JPA).
public interface ArticleRepository extends JpaRepository<Article, Long> { public List<Article> findByUserId(Long userId); @Query("select art from com.pandy.blog.po.Article art where title=:title") public List<Article> queryByTitle(@Param("title") String title);}OK, we can test the above code again:
@RunWith(SpringJUnit4ClassRunner.class)@SpringBootTest(classes = Application.class)public class ArticleRepositoryTest { @Autowired private ArticleRepository articleRepository; @Test public void testQuery(){ List<Article> articleList = articleRepository.queryByTitle("test title"); assertTrue(articleList.size()>0); }}Note that there are still problems similar to JdbcTemplate here. You need to make the Application startup class not in the parent package of Respository and Entity classes, otherwise the following error will appear:
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 moreOf course, you can also specify the scanned JPA package by annotating @EnableJpaRepositories, but it still doesn't work, and the following error will appear:
Caused by: java.lang.IllegalArgumentException: Not a managed type: class com.pandy.blog.po.Article at org.hibernate.jpa.internal.metamodel.MetamodelImpl.managedType(MetamodelImpl.java:210) at org.springframework.data.jpa.repository.support.JpaMetamodelEntityInformation.<init>(JpaMetamodelEntityInformation.java:70) at org.springframework.data.jpa.repository.support.JpaEntityInformationSupport.getEntityInformation(JpaEntityInformationSupport.java:68) at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getEntityInformation(JpaRepositoryFactory.java:153) at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getTargetRepository(JpaRepositoryFactory.java:100) at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getTargetRepository(JpaRepositoryFactory.java:82) at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:199) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:277) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:263) at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:101) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ... 39 more
This error indicates that Entity cannot be recognized, so you also need to specify the Entity package by annotating @EntityScan. The final configuration is as follows:
@SpringBootApplication@ComponentScan({"com.pandy.blog"})@EnableJpaRepositories(basePackages="com.pandy.blog")@EntityScan("com.pandy.blog")public class Application { public static void main(String[] args) throws Exception { SpringApplication.run(Application.class, args); }}4. Integration with MyBatis
Finally, let’s take a look at how to achieve database access through MyBatis. Similarly, we still need to introduce starter:
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.1.1</version></dependency>
Since the starter is not officially provided by spring boot, the version number is inconsistent with Spring boot and needs to be specified manually.
MyBatis can generally specify SQL that operates databases through XML or annotations. Personally, I prefer XML, so this article only demonstrates accessing databases through XML. First, we need to configure the mapper directory. We configure it in application.yml:
mybatis: config-locations: mybatis/mybatis-config.xml mapper-locations: mybatis/mapper/*.xml type-aliases-package: com.pandy.blog.po
The configuration here mainly includes three parts, one is some configurations of mybatis itself, such as alias for basic types. The second is to specify the location of the mapper file, and the third is the alias for the POJO class. This configuration can also be implemented through Java configuration. Due to space issues, I will not explain in detail here. Interested friends can implement it by themselves.
After configuration, we first write the mapper interface:
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);}This interface temporarily defines only four methods, namely, adding, updating, and querying based on ID and pagination. This is an interface, and it is similar to JPA, so you can do not need to implement classes. Next we write an XML file:
<?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.pandy.blog.dao.ArticleMapper"> <resultMap id="articleMap" type="com.pandy.blog.po.Article"> <id column="id" property="id" jdbcType="INTEGER"/> <result column="title" property="title" jdbcType="VARCHAR"/> <result column="summary" property="summary" jdbcType="VARCHAR"/> <result column="user_id" property="userId" jdbcType="INTEGER"/> <result column="status" property="status" jdbcType="INTEGER"/> <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/> <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/> <result column="public_time" property="publicTime" jdbcType="TIMESTAMP"/> </resultMap> <sql id="base_column"> title,summary,user_id,status,create_time,update_time,public_time </sql> <insert id="insertArticle" parameterType="Article"> INSERT INTO tb_article(<include refid="base_column"/>) VALUE (#{title},#{summary},#{userId},#{status},#{createTime},#{updateTime},#{publicTime}) </insert> <update id="updateArticle" parameterType="Article"> UPDATE tb_article <set> <if test="title != null"> title = #{title}, </if> <if test="summary != null"> summary = #{summary}, </if> <if test="status!= null"> status = #{status}, </if> <if test="publicTime !=null "> public_time = #{publicTime}, </if> <if test="updateTime !=null "> update_time = #{updateTime}, </if> </set> WHERE id = #{id} </update> <select id="queryById" parameterType="Long" resultMap="articleMap"> SELECT id,<include refid="base_column"></include> FROM tb_article WHERE id = #{id} </select> <select id="queryArticlesByPage" resultMap="articleMap"> SELECT id,<include refid="base_column"></include> FROM tb_article <where> <if test="article.title != null"> title like CONCAT('%',${article.title},'%') </if> <if test="article.userId != null"> user_id = #{article.userId} </if> </where> limit #{offset},#{pageSize} </select></mapper>Finally, we need to manually specify the packages scanned by the mapper:
@SpringBootApplication@MapperScan("com.pandy.blog.dao")public class Application { public static void main(String[] args) throws Exception { SpringApplication.run(Application.class, args); }}OK, the integration with MyBatis is also completed, let's test it 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 Title 2"); article.setSummary("Test Summary 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 = mappper.queryById(1L); assertNotNull(article); } @Test public void testUpdate() { Article article = mappper.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. Summary
This article demonstrates the integration of Spring boot with JdbcTemplate, JPA and MyBatis. Overall, the configuration is relatively simple. Students who have done related configurations before should feel that it is obvious. Spring boot has indeed provided us with great help in this regard. In subsequent articles, we will only use MyBatis to perform database operations. Another thing to note here is that MyBatis' pagination query is handwritten here. This pagination can be completed through plug-ins during formal development, but this has nothing to do with Spring boot, so this article temporarily uses this manual method to perform pagination processing.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.