使用數(shù)據(jù)庫是開發(fā)應(yīng)用的基本基礎(chǔ)碴萧，那么葫隙，使用Spring Boot如何連接數(shù)據(jù)庫呢洞辣？
前提仑荐，需要知道如何建一個(gè)Spring Boot項(xiàng)目阎曹，可參照：http://www.reibang.com/p/d6e6c84cd190
一伪阶、準(zhǔn)備工作：
1、建一個(gè)簡單的數(shù)據(jù)庫处嫌，名為springboot_db栅贴，在其下建一個(gè)表，名為t_author熏迹，腳本如下：
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 '用戶ID',
`real_name` varchar(32) NOT NULL COMMENT '用戶名稱',
`nick_name` varchar(32) NOT NULL COMMENT '用戶匿名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2檐薯、添加配置文件，可用使用yaml配置注暗，即application.yml（與application.properties配置文件坛缕，沒什么太大的區(qū)別）連接池的配置如下：
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墓猎、需要建立與數(shù)據(jù)庫對應(yīng)的POJO類，代碼如下：
public class Author {
private Long id;
private String realName;
private String nickName;
// SET和GET方法略
}
二赚楚、方式一：與JdbcTemplate集成
通過JdbcTemplate來訪問數(shù)據(jù)庫毙沾，Spring boot提供了如下的starter來支撐：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
再引入Junit測試Starter:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
DAO接口：
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();
}
實(shí)現(xiàn)Dao接口代碼（此處只寫Add，其他方法略）：
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;
}
}
通過JUnit來測試上面的代碼(需根據(jù)自己的實(shí)際Application名稍作修改)：
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("莫言");
author.setNickName("瘋子");
authorDao.add(author);
System.out.println("插入成功宠页！");
}
}
插入成功：
成功.png
PS:需要注意的是搀军，Application類所在的包必須是其他包的父包，@SpringBootApplication這個(gè)注解繼承了@ComponentScan勇皇，其默認(rèn)情況下只會掃描Application類所在的包及子包，結(jié)構(gòu)圖：
目錄結(jié)構(gòu)圖.png
Application代碼示例：
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);
}
}
三焚刺、方式二：與JPA集成
引入Starter:
<!-- 引入JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
對POJO類增加Entity的注解敛摘，并指定表名（如果不指定，默認(rèn)的表名為author）乳愉，然后指定ID的及其生成策略兄淫，這些都是JPA的知識，與Spring boot無關(guān)蔓姚，代碼：
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和GET方法略
}
需要繼承JpaRepository這個(gè)類捕虽，這里我們實(shí)現(xiàn)了兩個(gè)查詢方法，第一個(gè)是符合JPA命名規(guī)范的查詢坡脐，JPA會自動幫我們完成查詢語句的生成泄私，另一種方式是我們自己實(shí)現(xiàn)JPQL（JPA支持的一種類SQL的查詢）：
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);
}
測試代碼：
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("瘋子");
assertTrue(authorList.size() > 0);
System.out.println("成功！");
}
}
四备闲、方式三：與MyBatis集成
引入starter：
<!-- 引入Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
MyBatis一般可以通過XML或者注解的方式來指定操作數(shù)據(jù)庫的SQL晌端，首先，我們需要配置mapper的目錄恬砂。我們在application.yml中進(jìn)行配置：
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
編寫mapper對應(yīng)的接口：
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);
}
配置Mapper的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.guxf.mapper.AuthorMapper">
<!-- 此處需要注意的是咧纠，由于我們數(shù)據(jù)庫定義的id存儲類型為intbig,但是我們的Entity中Id是Long -->
<!-- 前面的兩種方式插入沒問題，此處報(bào)了數(shù)據(jù)庫類型異常 -->
<!-- 所以數(shù)據(jù)庫的ID類型改為了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>
測試類代碼：
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("唐鈺");
author.setNickName("小寶");
mapper.insertAuthor(author);
System.out.println("成功!");
}
@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("月兒");
author.setRealName("林月如");
mapper.updateAuthor(author);
}
}
我們看測試結(jié)果：
測試結(jié)果.png
配置掃描泻骤，需要根據(jù)自己項(xiàng)目結(jié)構(gòu)實(shí)際修改漆羔，下面貼上我的項(xiàng)目結(jié)構(gòu)圖：
結(jié)構(gòu).png
