1. pom.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>springbootjdbctemplate</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springbootjdbctemplate</name>
<description>springbootjdbctemplate</description>
<properties>
<java.version>19</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.6.13</spring-boot.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-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>19</source>
<target>19</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>${spring-boot.version}</version>
<configuration>
<mainClass>com.example.springbootjdbctemplate.SpringbootjdbctemplateApplication</mainClass>
<skip>true</skip>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
2.application.properties文件
# 应用服务 WEB 访问端口
server.port=8080
########
#数据源信息配置
########
#数据库地址
spring.datasource.url=jdbc:mysql://localhost:3306/jdbctemplate?characterEncoding=utf8&useSSL=false
#用户名
spring.datasource.username=root
#密码
spring.datasource.password=root
#数据库驱动
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
3.bean层
User.java
package com.example.springbootjdbctemplate.bean;
import java.io.Serializable;
public class User implements Serializable{
private int id;
private String loginName;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getLoginName() {
return loginName;
}
public void setLoginName(String loginName) {
this.loginName = loginName;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
4.repository层
UserRepository.java
package com.example.springbootjdbctemplate.repository;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import javax.annotation.Resource;
import com.example.springbootjdbctemplate.bean.User;
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 org.springframework.transaction.annotation.Transactional;
/*
@Repository注解:标注这是一个持久化操作对象
*/
@Repository
public class UserRepository {
//注入JdbcTemplate模板对象
@Resource
private JdbcTemplate jdbcTemplate;
/*
插入数据
@return插入影响的行数
*/
public int insertUser(){
String sql = "insert into tb_user(login_name ,username ,passWord) "
+"values (?,?,?),(?,?,?),(?,?,?)";
Object[] args = new Object[]{"swk","孙悟空","123456","zbj","猪八戒","123456","ts","唐僧","123456"};
//参数一:插入数据的SQL语句 参数二:对应SQL语句中占位符?的参数
return jdbcTemplate.update(sql, args);
}
/*
根据userName查询数据
@param userName
@return User对象
*/
public User selectByUsername(String username){
//定义SQL语句
String sql = "select * from tb_user where username = ?";
//定义一个RowMapper
RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
//执行查询方法
User user=jdbcTemplate.queryForObject(sql,new Object[]{username},rowMapper);
return user;
}
/*
根据id查询数据
@return User对象
*/
public User findUserById(int id){
//定义SQL语句
String sql = "select * from tb_user where id = ?";
RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
//执行查询方法
return jdbcTemplate.queryForObject(sql, new Object[]{id}, rowMapper);
}
/*
查询所有数据
@return包含User对象的List集合
*/
public List<User> findAll(){
//定义SQL语句
String sql ="select * from tb_user";
//声明结果级的映射rowMapper,将结果级的数据映射成User对象数据
RowMapper<User> rowMapper=new BeanPropertyRowMapper<>(User.class);
return jdbcTemplate.query(sql,rowMapper);
}
/*
根据id删除数据
*/
public void delete(final Integer id){
//定义SQL语句
String sql = "delete from tb_user where id=?";
//执行
jdbcTemplate.update(sql, new Object[]{id});
}
/*
修改数据
*/
public void update(final User user){
//定义SQL语句
String sql="update tb_user set username=?, login_name=? where id=?";
//执行
jdbcTemplate.update(sql,new Object[]{user.getUsername(),user.getLoginName(),user.getId()});
}
/*
插入数据,获取被插入数据的主键
*/
public User insertGetKey(User user){
//1.声明插入的SQL语句
String sql="insert into tb_user(username,login_name,password) values(?,?,?)";
//2.定义插入数据后获取主键的对象
KeyHolder holder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException{
//3.插入数据后,将被插入数据的主键返回
PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getUsername());
ps.setString(2,user.getLoginName());
ps.setString(3, user.getPassword());
return ps;
}
},holder);
//4.获取被插入数据库的主键,注入到user对象
int newUserId = holder.getKey().intValue();
user.setId(newUserId);
return user;
}
}
5.service层(业务层)
UserService.java
package com.example.springbootjdbctemplate.service;
import java.util.List;
import javax.annotation.Resource;
import com.example.springbootjdbctemplate.bean.User;
import com.example.springbootjdbctemplate.repository.UserRepository;
import org.springframework.stereotype.Service;
@Service
public class UserService {
//注入UserRepository
@Resource
private UserRepository userRepository;
public int insertUser(){
return userRepository.insertUser();
}
public User selectByUsername(String username){
return userRepository.selectByUsername(username);
}
public List<User> findAll(){
return userRepository.findAll();
}
public User insertGetKey(User user){
return userRepository.insertGetKey(user);
}
public void update(User user){
userRepository.update(user);
}
public void delete(Integer id){
userRepository.delete(id);
}
}
6.控制器类controller
UserController.java
package com.example.springbootjdbctemplate.controller;
import java.util.List;
import javax.annotation.Resource;
import com.example.springbootjdbctemplate.bean.User;
import com.example.springbootjdbctemplate.service.UserService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private UserService userService;
@RequestMapping("/insertUser")
public String insertUser(){
return "插入数据["+userService.insertUser()+"]条";
}
@RequestMapping("/insertGetKey")
public User insertGetKey(User user){
return userService.insertGetKey(user);
}
@RequestMapping("/selectByUsername")
public User selectByUsername(String username){
return userService.selectByUsername(username);
}
@RequestMapping("/findAll")
public List<User> findAll(){
return userService.findAll();
}
@RequestMapping("/update")
public void update(User user){
userService.update(user);
}
@RequestMapping("/delete")
public void delete(Integer id){
userService.delete(id);
}
}
7.测试
启动MySQL数据库创建一个新的名为jdbctemplate的数据库,创建tb_user表。
在数据库运行脚本
脚本如下
CREATE DATABASE jdbctemplate;
USE jdbctemplate;
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
login_name VARCHAR(23),
PASSWORD VARCHAR(23),
username VARCHAR(23)
);