使用數據庫是開發應用的基本基礎,那麼,使用Spring Boot如何連接數據庫呢?
前提,需要知道如何建一個Spring Boot項目,可參照:https://www.jianshu.com/p/d6e6c84cd190
一、準備工作:
1、建一個簡單的數據庫,名為springboot_db,在其下建一個表,名為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配置文件,沒什麼太大的區別)連接池的配置如下:
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、需要建立與數據庫對應的POJO類,代碼如下:
public class Author {
private Long id;
private String realName;
private String nickName;
// SET和GET方法略
}
二、方式一:與JdbcTemplate集成
通過JdbcTemplate來訪問數據庫,Spring boot提供瞭如下的starter來支撐:
<dependency>
<groupid>org.springframework.boot/<groupid>
<artifactid>spring-boot-starter-jdbc/<artifactid>
再引入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();
}
/<author>
實現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> param = new HashMap<>();/<string>
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() { /<author>
return null;
}
}
通過JUnit來測試上面的代碼(需根據自己的實際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這個註解繼承了@ComponentScan,其默認情況下只會掃描Application類所在的包及子包,結構圖:
目錄結構圖.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:
<dependency>
<groupid>org.springframework.boot/<groupid>
<artifactid>spring-boot-starter-data-jpa/<artifactid>
對POJO類增加Entity的註解,並指定表名(如果不指定,默認的表名為author),然後指定ID的及其生成策略,這些都是JPA的知識,與Spring boot無關,代碼:
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這個類,這裡我們實現了兩個查詢方法,第一個是符合JPA命名規範的查詢,JPA會自動幫我們完成查詢語句的生成,另一種方式是我們自己實現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> {/<author>
public Optional<author> findById(Long userId);/<author>
@Query("select au from com.guxf.domain.Author au where nick_name=:nickName")
public List<author> queryByNickName(@Param("nickName") String nickName);/<author>
}
測試代碼:
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("成功!");
}
}
/<author>
四、方式三:與MyBatis集成
引入starter:
<dependency>
<groupid>org.mybatis.spring.boot/<groupid>
<artifactid>mybatis-spring-boot-starter/<artifactid>
<version>1.1.1/<version>
MyBatis一般可以通過XML或者註解的方式來指定操作數據庫的SQL,首先,我們需要配置mapper的目錄。我們在application.yml中進行配置:
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對應的接口:
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);
}
/<author>
配置Mapper的XML文件:
<mapper>
<resultmap>
<result>
<result>
/<resultmap>
id,real_name,nick_name
<insert>
INSERT INTO
t_author(
<include>
)
VALUE
(#{id},#{realName},#{nickName})
/<insert>
<update>
UPDATE t_author
real_name = #{realName},
nick_name = #{nickName},
WHERE id = #{id}
/<update>
<select>
SELECT id,
<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);
}
}
我們看測試結果:
測試結果.png
配置掃描,需要根據自己項目結構實際修改,下面貼上我的項目結構圖:
閱讀更多 養碼場007 的文章