SpringBoot使用JdbcTemplate实现增删改查
SpringBoot使用JdbcTemplate实现CRUD功能
SpringBoot2.x使用JdbcTemplate实现增删改查，非常适合初学者学习案例。
项目结构
项目结构
第一步：开发环境准备
JDK:1.8 Maven：apache-maven-3.6.0
第二步：配置pom.xml
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.4.RELEASE</version> <relativePath/> </parent> <properties> <java.version>1.8</java.version> <!-- 解决maven的pom.xml文件第一行报错 Unknown Error --> <maven-jar-plugin.version>3.1.1</maven-jar-plugin.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.46</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency> </dependencies>
第三步：编写application.yml文件
server: port: 8081 spring: datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC username: root password: 123456
第四步：创建t_student表
create table `t_student` (
sid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
sno VARCHAR(10) DEFAULT NULL ,
name VARCHAR(100) DEFAULT NULL ,
sex VARCHAR(10) DEFAULT NULL,
PRIMARY KEY(sid)
)ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into `t_student` (`sid`, `sno`, `name`, `sex`) values('1','001','小碗','女');
insert into `t_student` (`sid`, `sno`, `name`, `sex`) values('10','002','小婷','女');
insert into `t_student` (`sid`, `sno`, `name`, `sex`) values('11','003','小文','男');
insert into `t_student` (`sid`, `sno`, `name`, `sex`) values('12','004','小米','男');
第五步：编写启动类
package com.xiaowen;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class JdbcTemplateApplication {
public static void main(String[] args) {
SpringApplication.run(JdbcTemplateApplication.class, args);
}
}
SpringBoot2.x使用JdbcTemplate实现增删改查
第六步：编写POJO类
package com.xiaowen.model;
import java.io.Serializable;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 学生实体类
* @author xiaowen
*
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student implements Serializable {
private static final long serialVersionUID = 1L;
private Integer sid;
private String sno;
private String name;
private String sex;
}
第七步：编写Dao数据层
package com.xiaowen.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import com.xiaowen.model.Student;
/**
* Dao数据层
* @author xiaowen
*
*/
@Repository
public class StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 查询全部
* @return
*/
public List<Student> findAll(){
String sql="select *from t_student";
//将结果集数据映射成Student对象
RowMapper<Student> rm=new BeanPropertyRowMapper<>(Student.class);
return jdbcTemplate.query(sql, rm);
}
/**
* 更新
* @param student
*/
public void updateStudent(Student student) {
String sql="update t_student set sno=?,name=?,sex=? where sid=?";
jdbcTemplate.update(sql, new Object[] {student.getSno(),student.getName(),student.getSex(),student.getSid()});
}
/**
* 删除
* @param sid
*/
public void deleteStudent(Integer sid) {
String sql="delete from t_student where sid=?";
jdbcTemplate.update(sql,new Object[] {sid});
}
/**
* 新增
* @param student
* @return
*/
public Student addStudent(Student student) {
String sql="insert into t_student(sno,name,sex) values(?,?,?)";
//插入数据后获取主键对象
KeyHolder holder=new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
//插入数据后，将被插入数据的主键返回
PreparedStatement pst=con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
pst.setString(1, student.getSno());
pst.setString(2, student.getName());
pst.setString(3, student.getSex());
return pst;
}
},holder);
//获取被插入数据库的主键，注入到studet对象
int value = holder.getKey().intValue();
student.setSid(value);
return student;
}
}
第八步：编写service业务逻辑层
package com.xiaowen.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.xiaowen.dao.StudentDao;
import com.xiaowen.model.Student;
/**
* service业务逻辑处理层
* @author xiaowen
*
*/
@Service
public class StudentService {
@Autowired
private StudentDao studentDao;
public List<Student> findAll(){
return studentDao.findAll();
}
public void updateStudent(Student student) {
studentDao.updateStudent(student);
}
public void deleteStudent(Integer sid) {
studentDao.deleteStudent(sid);
}
public Student addStudent(Student student) {
return studentDao.addStudent(student);
}
}
SpringBoot2使用JdbcTemplate
第九步：编写Controller控制层
package com.xiaowen.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.xiaowen.model.Student;
import com.xiaowen.service.StudentService;
/**
* Student控制层
* @author xiaowen
*
*/
@RestController
@RequestMapping("/student")
public class StudentController {
@Autowired
private StudentService studentService;
/**
* 查询
* @return
*/
@RequestMapping("/findAll")
public List<Student> findAll(){
return studentService.findAll();
}
/**
* 新增
* @param student
* @return
*/
@RequestMapping("/add")
public Student add(Student student) {
return studentService.addStudent(student);
}
/**
* 更新
* @param student
*/
@RequestMapping("/update")
public void update(Student student) {
studentService.updateStudent(student);
}
/**
* 删除
* @param sid
*/
@RequestMapping("/delete")
public void delete(Integer sid) {
studentService.deleteStudent(sid);
}
}
第十步：运行JdbcTemplateApplication类
1、查询功能访问：http://localhost:8081/student/findAll，效果如下
查询功能
2、新增功能访问：http://localhost:8081/student/add?sno=006&name=婷婷&sex=女，效果如下
新增功能
3、更新功能访问：http://localhost:8081/student/update?sid=16&sno=007&name=婷婷001&sex=男，效果如下
更新功能
4、删除功能访问：http://localhost:8081/student/delete?sid=16，查询之后效果如下
删除后并查询