本章介绍springBoot整合JDBC访问关系型数据库,然后通过spring的jdbcTemplate去操作
第一步、创建数据库
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');
第二步、引入相关依赖
<dependencies>
<!-- 1、支持全栈式web开发,包括tomcat和spring-webmvc -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- 2、支持常规的测试依赖,包括junit、Hamcrest、Mockito以及spring-test模块 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- 3、支持jdbc数据库 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 4、mysql数据库连接类 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- 5、msql数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
</dependencies>
第三步、配置相关文件application.properties
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root
第四步、根据数据库创建实体类Account
package com.cn.pojo;
/**
* 账号实体类
* */
public class Account {
private int id; //id
private String name; //账户名
private double money; //账户金额
//get和set方法
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
}
第五步、创建数据库持久层接口和实现类IAccountDao
package com.cn.dao;
import com.cn.pojo.Account;
import java.util.List;
/**
* 账户持久层接口
* */
public interface IAccountDao {
//1.新增
int addAccount(Account account);
//2.删除
int deleteAccountById(int id);
//3.修改
int updateAccount(Account account);
//4.通过id查询
Account findAccountById(int id);
//5.查询所有
List<Account> findAllAccount();
}
实现类:AccountDaoImpl
package com.cn.dao.impl;
import com.cn.dao.IAccountDao;
import com.cn.pojo.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;
@Repository //声明为数据层
public class AccountImpl implements IAccountDao {
@Autowired
private JdbcTemplate jdbcTemplate; //注入jdbcTemplate
//新增
@Override
public int addAccount(Account account) {
return jdbcTemplate.update("insert into t_account(name,money) values(?,?)"
,account.getName(),account.getMoney());
}
//删除
@Override
public int deleteAccountById(int id) {
return jdbcTemplate.update("delete from table t_account where id=?",id);
}
//修改
@Override
public int updateAccount(Account account) {
return jdbcTemplate.update("update t_account set name=?,money=? where id=?",account.getName()
,account.getMoney(),account.getId());
}
//通过id查询
@Override
public Account findAccountById(int id) {
List<Account> list=jdbcTemplate.query("select * from t_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> findAllAccount() {
List<Account> list=jdbcTemplate.query("select * from t_account",new Object[]{},new BeanPropertyRowMapper(Account.class));
if(list!=null&list.size()>0){
return list;
}else{
return null;
}
}
}
第六步、创建service服务层接口和实现类
Service层接口IAccountService
package com.cn.service;
import com.cn.pojo.Account;
import java.util.List;
/**
* 服务层接口
* */
public interface IAccountService {
//1.新增
int addAccount(Account account);
//2.删除
int deleteAccountById(int id);
//3.修改
int updateAccount(Account account);
//4.通过id查询
Account findAccountById(int id);
//5.查询所有
List<Account> findAllAccount();
}
Service层实现类AccountServiceImpl
package com.cn.service.impl;
import com.cn.dao.IAccountDao;
import com.cn.pojo.Account;
import com.cn.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service("accountService") //注册为service层
public class AccountServiceImpl implements IAccountService{
@Autowired
private IAccountDao accountDao; //将accountDao注入到AccountServiceImpl中
//新增
@Override
public int addAccount(Account account) {
return accountDao.addAccount(account);
}
//通过id删除
@Override
public int deleteAccountById(int id) {
return accountDao.deleteAccountById(id);
}
//修改
@Override
public int updateAccount(Account account) {
return accountDao.updateAccount(account);
}
//通过id查询
@Override
public Account findAccountById(int id) {
return accountDao.findAccountById(id);
}
//查询所有
@Override
public List<Account> findAllAccount() {
return accountDao.findAllAccount();
}
}
第七步、创建controller层
package com.cn.controller;
import com.cn.pojo.Account;
import com.cn.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController //@Controller+@ResponseBody的组合
@RequestMapping("/account")
public class AccountController {
@Autowired
private IAccountService accountService; //accountService注入到AccountController中
/**
* 查询所有
* */
@RequestMapping(value = "accountList",method= RequestMethod.GET)
public List<Account> getAllAccount(){
return accountService.findAllAccount();
}
}
第八步、测试