Spring Boot实战:数据库操作

  上篇作品中曾经由此一个简约的HelloWorld程序讲解了Spring
boot的基本原理和利用。本文重要助教如何通过spring
boot来走访数据库,本文会以身作则二种方法来做客数据库,第一种是JdbcTemplate,第二种是JPA,第二种是Mybatis。从前曾经涉嫌过,本序列会以一个博客系统作为讲解的根基,所以本文会讲课文章的积存和访问(但不包括作品的详情),因为最终的贯彻是透过MyBatis来形成的,所以,对于JdbcTemplate和JPA只做简单演示,MyBatis部分会全体兑现对著作的增删改查。

一、准备干活

  在示范这两种艺术在此之前,需要先准备一些东西。第一个就是数据库,本系统是使用MySQL实现的,我们需要先创建一个tb_article的表:

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;

  后续的演示会对这些表展开增删改查,我们应该会看到那一个表里面并从未成文的详情,原因是随笔的详情相比长,如若放在这些表里面容易影响查询作品列表的效率,所以随笔的详情会独自存在此外的表里面。此外我们需要配备数据库连接池,这里大家应用druid连接池,此外配置文件使用yaml配置,即application.yml(你也可以采纳application.properties配置文件,没什么太大的区分,假诺对ymal不熟悉,有趣味也足以查一下,相比较简单)。连接池的安排如下:

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

  最终,大家还需要树立与数据库对应的POJO类,代码如下:

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;

}

  好了,需要准备的工作就这个,现在始于兑现数据库的操作。

 

 二、与JdbcTemplate集成

  首先,我们先经过JdbcTemplate来访问数据库,这里只演示数据的插入,上一篇小说中我们早已提到过,Spring
boot提供了众多的starter来支撑不同的效益,要扶助JdbcTemplate我们只需要引入上面的starter就足以了:

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

  现在大家就可以通过JdbcTemplate来贯彻多少的插入了:

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);
    }
}

  大家透过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("测试标题");
        article.setSummary("测试摘要");
        article.setUserId(1L);
        article.setStatus(1);
        article.setCreateTime(new Date());
        article.setUpdateTime(new Date());
        article.setPublicTime(new Date());
        articleDao.insertArticle(article);
    }
}

MyBatis,   要补助地方的测试程序,也急需引入一个starter:

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

  从上边的代码可以看看,其实不外乎引入jdbc的start之外,基本没有布置,那都是spring
boot的自行帮我们成功了安排的过程。上边的代码需要小心的Application类的职务,该类必须放在Dao类的父级的包中,比如这里Dao都置身com.pandy.blog.dao那多少个包下,现在大家把Application.java这一个类从com.pandy.blog这么些包活动到com.pandy.blog.app这么些包中,则汇合世如下错误:

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

  也就是说,找不到ArticleDao的实现,这是何等原因吗?上一篇博文中大家曾经见到@SpringBootApplication这么些讲明继承了@ComponentScan,其默认情况下只会扫描Application类所在的包及子包。因而,对于地点的失实,除了维持Application类在Dao的父包这种方法外,也足以指定扫描的包来解决:

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

    

三、与JPA集成

  现在大家起始上课咋样通过JPA的情势来实现数据库的操作。依旧跟JdbcTemplate类似,首先,大家需要引入对应的starter:

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

  然后大家需要对POJO类扩展Entity的注释,并点名表名(假如不点名,默认的表名为article),然后需要指定ID的及其变化策略,那些都是JPA的学识,与Spring
boot无关,假使不熟练的话可以看下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;
}

  最终,大家需要继续JpaRepository这些类,这里大家兑现了六个查询艺术,第一个是适合JPA命名规范的查询,JPA会自动帮大家完成查询语句的扭转,另一种方法是大家自己实现JPQL(JPA协理的一连串SQL的询问)。

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);
}

  好了,大家可以再测试一下地点的代码:

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = Application.class)
public class ArticleRepositoryTest {
    @Autowired
    private ArticleRepository articleRepository;

    @Test
    public void testQuery(){
        List<Article> articleList = articleRepository.queryByTitle("测试标题");
        assertTrue(articleList.size()>0);
    }
}

  注意,这里仍旧存在跟JdbcTemplate类似的题材,需要将Application这么些启动类未于Respository和Entity类的父级包中,否则会油可是生如下错误:

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

  当然,同样也得以经过注脚@EnableJpaRepositories指定扫描的JPA的包,不过仍旧非常,还会晤世如下错误:

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

  这多少个荒唐表达识别不了Entity,所以还索要经过注脚@EntityScan来指定Entity的包,最后的部署如下:

@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);
    }
}

 

四、与MyBatis集成

  最后,我们再看看怎么着通过MyBatis来兑现数据库的拜访。同样我们如故要引入starter:

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

  由于该starter不是spring boot官方提供的,所以版本号于Spring
boot不均等,需要手动指定。

  MyBatis一般可以由此XML或者讲明的主意来指定操作数据库的SQL,个人相比偏向于XML,所以,本文中也只演示了经过XML的章程来拜会数据库。首先,我们需要安排mapper的目录。大家在application.yml中举行配备:

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

  这里配置首要不外乎两个部分,一个是mybatis自身的一部分配备,例如基本类型的别名。第二个是指定mapper文件的职务,第六个POJO类的别名。这些布局也可以通过
Java
configuration来落实,由于篇幅的题目,我这边就不详述了,有趣味的朋友可以自己实现一下。

  配置完后,我们先编制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);

}

  该接口暂时只定义了三个主意,即添加、更新,以及遵照ID查询和分页查询。这是一个接口,并且和JPA类似,可以不要实现类。接下来我们编辑XML文件:

<?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>

  最终,大家需要手动指定mapper扫描的包:

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

  好了,与MyBatis的融会也落成了,我们再测试一下:

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

    @Autowired
    private ArticleMapper mapper;

    @Test
    public void testInsert() {
        Article article = new Article();
        article.setTitle("测试标题2");
        article.setSummary("测试摘要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);
    }
}

  

五、总结

    本文演示Spring
boot与JdbcTemplate、JPA以及MyBatis的集成,全部上的话配置都相比简单,从前做过相关部署的同校应该感觉相比较明确,Spring
boot确实在这方面给我们提供了很大的拉扯。后续的稿子中大家只会采取MyBatis这一种格局来展开数据库的操作,这里还有某些亟需评释一下的是,MyBatis的分页查询在这里是手写的,这么些分页在正儿八经开发中能够由此插件来形成,不过这些与Spring
boot没怎么关联,所以本文暂时通过这种手动的形式来拓展分页的拍卖。

 

网站地图xml地图