轉載:SpringBoot非官方教程 | 第三篇:SpringBoot用JdbcTemplates訪問Mysql
轉載:https://blog.csdn.net/forezp/article/details/70477821
本文介紹springboot通過jdbc訪問關係型mysql,通過spring的JdbcTemplate去訪問。
準備工作
- jdk 1.8
- maven 3.0
- idea
- mysql
初始化mysql:
-- create table `account`
DROP TABLE `account` IF EXISTS
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`money` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `account` VALUES ('1', 'aaa', '1000');
INSERT INTO `account` VALUES ('2', 'bbb', '1000');
INSERT INTO `account` VALUES ('3', 'ccc', '1000');
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
建立工程
引入依賴:
在pom檔案引入spring-boot-starter-jdbc的依賴:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
- 1
- 2
- 3
- 4
- 5
引入mysql連線類和連線池:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
開啟web:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
- 1
- 2
- 3
- 4
- 5
配置相關檔案
在application.properties檔案配置mysql的驅動類,資料庫地址,資料庫賬號、密碼資訊。
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456
- 1
- 2
- 3
- 4
- 5
通過引入這些依賴和配置一些基本資訊,springboot就可以訪問資料庫類。
具體編碼
實體類
public class Account {
private int id ;
private String name ;
private double money;
....省略了getter. setter
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
dao層
public interface IAccountDAO {
int add(Account account);
int update(Account account);
int delete(int id);
Account findAccountById(int id);
List<Account> findAccountList();
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
具體的實現類:
package com.forezp.dao.impl;
import com.forezp.dao.IAccountDAO;
import com.forezp.entity.Account;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* Created by fangzhipeng on 2017/4/20.
*/
@Repository
public class AccountDaoImpl implements IAccountDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int add(Account account) {
return jdbcTemplate.update("insert into account(name, money) values(?, ?)",
account.getName(),account.getMoney());
}
@Override
public int update(Account account) {
return jdbcTemplate.update("UPDATE account SET NAME=? ,money=? WHERE id=?",
account.getName(),account.getMoney(),account.getId());
}
@Override
public int delete(int id) {
return jdbcTemplate.update("DELETE from TABLE account where id=?",id);
}
@Override
public Account findAccountById(int id) {
List<Account> list = jdbcTemplate.query("select * from account where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Account.class));
if(list!=null && list.size()>0){
Account account = list.get(0);
return account;
}else{
return null;
}
}
@Override
public List<Account> findAccountList() {
List<Account> list = jdbcTemplate.query("select * from account", new Object[]{}, new BeanPropertyRowMapper(Account.class));
if(list!=null && list.size()>0){
return list;
}else{
return null;
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
service層
public interface IAccountService {
int add(Account account);
int update(Account account);
int delete(int id);
Account findAccountById(int id);
List<Account> findAccountList();
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
具體實現類:
@Service
public class AccountService implements IAccountService {
@Autowired
IAccountDAO accountDAO;
@Override
public int add(Account account) {
return accountDAO.add(account);
}
@Override
public int update(Account account) {
return accountDAO.update(account);
}
@Override
public int delete(int id) {
return accountDAO.delete(id);
}
@Override
public Account findAccountById(int id) {
return accountDAO.findAccountById(id);
}
@Override
public List<Account> findAccountList() {
return accountDAO.findAccountList();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
構建一組restful api來展示
package com.forezp.web;
import com.forezp.entity.Account;
import com.forezp.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
/**
* Created by fangzhipeng on 2017/4/20.
*/
@RestController
@RequestMapping("/account")
public class AccountController {
@Autowired
IAccountService accountService;
@RequestMapping(value = "/list",method = RequestMethod.GET)
public List<Account> getAccounts(){
return accountService.findAccountList();
}
@RequestMapping(value = "/{id}",method = RequestMethod.GET)
public Account getAccountById(@PathVariable("id") int id){
return accountService.findAccountById(id);
}
@RequestMapping(value = "/{id}",method = RequestMethod.PUT)
public String updateAccount(@PathVariable("id")int id , @RequestParam(value = "name",required = true)String name,
@RequestParam(value = "money" ,required = true)double money){
Account account=new Account();
account.setMoney(money);
account.setName(name);
account.setId(id);
int t=accountService.update(account);
if(t==1){
return account.toString();
}else {
return "fail";
}
}
@RequestMapping(value = "",method = RequestMethod.POST)
public String postAccount( @RequestParam(value = "name")String name,
@RequestParam(value = "money" )double money){
Account account=new Account();
account.setMoney(money);
account.setName(name);
int t= accountService.add(account);
if(t==1){
return account.toString();
}else {
return "fail";
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
可以通過postman來測試,具體的我已經全部測試通過,沒有任何問題。注意restful構建api的風格。
原始碼下載:https://github.com/forezp/SpringBootLearning
參考資料
優秀文章推薦:
- 更多springboot 教程:springBoot非官方教程 | 文章彙總
相關文章
- SpringBoot 實戰 (六) | 用 JdbcTemplates 訪問 MysqlSpring BootJDBCMySql
- JAVA springboot ssm b2b2c多使用者商城系統原始碼(三)SpringBoot用JdbcTemplates訪問MysqlJavaSpring BootSSM原始碼JDBCMySql
- 轉載:SpringBoot非官方教程 | 第一篇:構建第一個SpringBoot工程Spring Boot
- SpringBoot非官方教程 | 第六篇:SpringBoot整合mybatisSpring BootMyBatis
- SpringBoot非官方教程 | 第十三篇:springboot整合spring cacheSpring Boot
- 轉載:SpringBoot非官方教程 | 第二篇:Spring Boot配置檔案詳解Spring Boot
- SpringBoot非官方教程 | 第二篇:SpringBoot配置檔案詳解Spring Boot
- SpringBoot非官方教程 | 第七篇:SpringBoot開啟宣告式事務Spring Boot
- Java springcloud B2B2C o2o多使用者商城 springcloud架構(三)SpringBoot用JdbcTemplates訪問MysqlJavaGCCloud架構Spring BootJDBCMySql
- SpringBoot非官方教程 | 第十四篇:在springboot中用redis實現訊息佇列Spring BootRedis佇列
- SpringBoot非官方教程 | 第二十篇: 處理表單提交Spring Boot
- SpringBoot資料訪問(一) SpringBoot整合MybatisSpring BootMyBatis
- SpringBoot資料訪問(二) SpringBoot整合JPASpring Boot
- SpringBoot資料訪問(三) SpringBoot整合RedisSpring BootRedis
- SpringBoot非官方教程 | 第二十三篇: 非同步方法Spring Boot非同步
- SpringBoot靜態資源訪問Spring Boot
- 【SpringBoot實戰】資料訪問Spring Boot
- SpringBoot mysql驅動問題Spring BootMySql
- springboot無妨訪問html頁面Spring BootHTML
- 用perl訪問mysql資料庫(轉)MySql資料庫
- 採用ODBC介面訪問MySQL指南 (轉)MySql
- SpringBoot+MySQL+MyBatis的入門教程Spring BootMySqlMyBatis
- springBoot依賴下載問題Spring Boot
- Springboot中如何訪問靜態資源Spring Boot
- Holer實現外網訪問本地SpringBootSpring Boot
- springboot配置CORS允許跨域訪問Spring BootCORS跨域
- springboot新增靜態資源無法訪問Spring Boot
- PHP訪問MySQL篇(轉)PHPMySql
- 企業 SpringBoot 教程(六)springboot整合mybatisSpring BootMyBatis
- SpringBoot使用Redis提升天氣預報應用的併發訪問能力Spring BootRedis
- SpringBoot框架整合SpringSecurity實現安全訪問控制Spring Boot框架Gse
- SpringBoot資料訪問之整合Mybatis配置檔案Spring BootMyBatis
- SpringBoot資料訪問之整合mybatis註解版Spring BootMyBatis
- springboot 專案引入tk或者jpa 訪問報錯Spring Boot
- SpringBoot 教程之 profileSpring Boot
- SpringBoot簡明教程Spring Boot
- Springboot入門教程Spring Boot
- SpringBoot 整合 Quartz + MySQLSpring BootquartzMySql