Spring Boot Combat Database operation

The basic principles and uses of spring boot have been explained in the previous article 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, the third is mybatis. As mentioned earlier, this series will be based on a blog system, so this article will explain the storage and access to the article (but not the details of the article), because the final implementation is done by MyBatis, so, for JdbcTemplate and JPA only to do a simple demonstration, MyBatis part will complete the deletion of the article to change the search.

First, the preparatory work

There are a few things you need to prepare before you can demonstrate these ways. The first is the database, the system is implemented using MySQL, we need to first create a tb_article table:

DROP TABLE IF EXISTS ' tb_article '; CREATE TABLE ' tb_article ' (  ' id ' bigint () not NULL auto_increment,  ' title ' varchar (255) is not null DEFAULT ' ', 
   
     ' summary ' varchar (1024x768) NOT null default ' ',  ' status ' int (one) not null default ' 0 ',  ' type ' int (one) ' NOT NULL,  ' user_id ' bigint (a) NOT null default ' 0 ',  ' create_time ' timestamp not null default Current_timestamp,  ' Update_ Time ' timestamp not NULL for default current_timestamp,  ' public_time ' timestamp not null default CURRENT_TIMESTAMP,  PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8;
   

Follow-up demonstrations will be added to this table to check, you should see this table does not have the details of the article, because the details of the article is relatively long, if placed in this table is easy to affect the efficiency of the query list, so the details of the article will exist separately in the other table. In addition we need to configure the database connection pool, where we use the Druid connection pool, and the configuration file using Yaml configuration, That is application.yml (you can also use the Application.properties configuration file, there is no big difference, if the ymal unfamiliar, interested can also check, relatively simple). The connection pool is configured 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 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;

}

Well, that's all the work that needs to be prepared, and now it's time to implement database operations.

Second, integration with JdbcTemplate

First, we use JdbcTemplate to access the database, only to demonstrate the insertion of data, as we have mentioned in the previous article, Spring Boot provides many starter to support different functions, To support jdbctemplate we just need to introduce the following starter:

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

Now we can implement the data insertion by JdbcTemplate:

Public interface Articledao {Long insertarticle (article article);} @Repositorypublic class Articledaojdbctemplateimpl implements Articledao {@Autowired private namedparameterjdbctemp    Late JdbcTemplate; @Override Public Long insertarticle (article article) {String sql = ' INSERT INTO tb_article (Title,summary,user_i D,create_time,public_time,update_time,status) "+" VALUES (: Title,:summary,:userid,:createtime,:p ublictime,:        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 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 can be seen, in fact, in addition to the introduction of JDBC start, basically no configuration, this is the spring boot automatically help us to complete the configuration process. The above code needs to be aware of the location of the application class, which must be in the package of the parent of the DAO class. For example, here DAO is located under the package of Com.pandy.blog.dao, and now we move the Application.java class from Com.pandy.blog to Com.pandy.blog.app this package, 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) ... + More 

That is to say, can't find the realization of Articledao, what reason is this? As we've seen in the previous blog post, @springbootapplication has inherited @componentscan, which by default only scans packages and sub-packages where the application class is located. Therefore, for the above error, in addition to keeping the application class in the DAO's parent package, you can also specify a 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);}    }

　　　　

Third, integration with JPA

Now we are going to explain how to implement the database operation in JPA way. or 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 an entity annotation to the Pojo class and specify the table name (if not specified, the default table name is article), and then you need to specify the ID and its generation strategy, which is the knowledge of JPA, which is not related to spring boot, If you are unfamiliar, you can look at the knowledge points of JPA:

@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, where we implement two query methods, the first is a query that conforms to the JPA naming specification, and JPA will automatically help us with the generation of query statements, and 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);

Well, 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 is a similar problem with jdbctemplate, which needs to be application This startup class is not in the parent package of Respository and the entity class, 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 a Utowire 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) ... + More 

Of course, it is also possible to specify the scanned JPA package via the annotation @enablejparepositories, but still not, the following error will appear:

caused By:java.lang.IllegalArgumentException:Not a managed Type:class com.pandy.blog.po.Articleat 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) ... More

This error indicates that the entity cannot be identified, so it is also necessary to specify the entity's package through the annotation @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);}    }

Iv. Integration with MyBatis

Finally, let's look at how to implement database access through MyBatis. Again, we're going 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 generally can be used in XML or annotated way to specify the operation of the database SQL, personal preference for XML, so this article only demonstrates the way to access the database through XML. First, we need to configure the Mapper directory. We are configuring in APPLICATION.YML:

MyBatis:  config-locations:mybatis/mybatis-config.xml  mapper-locations:mybatis/mapper/*.xml  Type-aliases-package:com.pandy.blog.po

Here the configuration consists mainly of three parts, one of which is the configuration of the mybatis itself, such as the alias of the base type. The second is to specify the location of the mapper file, and the alias of the third Pojo class. This configuration can also be implemented by Java configuration, because of the length of the problem, I do not detail here, interested friends can achieve their own.

After the configuration, we first write the mapper corresponding 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);}

The interface temporarily defines only four methods, that is, add, update, and query by ID and paged. This is an interface, and like JPA, you can not implement classes. Next we write the 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=" t Itle "property=" title "jdbctype=" varchar "/> <result column=" Summary "property=" summary "jdbctype=" varchar "/&G        T <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" Propert Y= "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> &LT;/SET&G        T WHERE id = #{id}    </update> <select id= "Querybyid" parametertype= "Long" resultmap= "Articlemap" > select Id,<incl Ude refid= "Base_column" ></include> from tb_article WHERE id = #{id} </select> <select id= "q Ueryarticlesbypage "resultmap=" Articlemap "> select Id,<include refid=" Base_column "></include> from T B_article <where> <if test= "Article.title! = null" > title like CONCAT ('% ', ${a Rticle.title}, '% ') </if> <if test= "Article.userid! = null" > user_id = #{a Rticle.userid} </if> </where> limit #{offset},#{pagesize} </select></mapp Er>

Finally, we need to manually specify the Mapper scanned package:

@SpringBootApplication @mapperscan ("Com.pandy.blog.dao") public class Application {public    static void main (String [] args) throws Exception {        springapplication.run (application.class, args);    }}

Well, the integration with MyBatis is done, and we'll 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 = 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); }}

　　

V. Summary

This article demonstrates the integration of Spring boot with JdbcTemplate, JPA, and MyBatis, the overall configuration is relatively simple, before the relevant configuration of the students should feel more obvious, Spring Boot does provide us with a lot of help. Later in the article we will only use mybatis this way to do the database operation, here is also a point to explain is that the MyBatis page query is handwritten here, this page in the formal development can be done through the plug-in, but this is not related to spring boot, So this article for the time being a manual way to do pagination processing.

Spring Boot Combat Database operation