Spring Boot使用JdbcTemplate操作mysql数据库实战（附源码超详细）

￡神魔★判官ぃ 2023-09-30 16:13 39阅读 0赞

> **觉得有帮助或需要源码请点赞关注收藏后评论区留言或者私信**

**JDBC模板是Spring对数据库的操作再JDBC基础上做了封装，建立了一个JDBC存取框架，在Spring Boot应用中，如果使用JdbcTemplate操作数据库，那么只需要在pom.xml文件中添加spring-boot-starter-jdbc模块，即可通过@Autowired注解依赖注入JdbcTemplate对象，然后调用JdbcTemplate提供的方法操作数据库**

下面通过实例讲解如何在Spring Boot应用中使用JdbcTemplate操作数据库

### 1：创建Spring Boot Web应用 ###

此处不再赘述 详情可参考我这篇博客[快速构建Spring Boot应用][Spring Boot]

### **2：修改pom.xml文件** ###

在文件中添加MYSQL连接器和spring-boot-starter-jdbc模块 具体代码如下

<?xml version="1.0" encoding="UTF-8"?>
    
    -<project xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0">
    
    <modelVersion>4.0.0</modelVersion>
    
    
    -<parent>
    
    <groupId>org.springframework.boot</groupId>
    
    <artifactId>spring-boot-starter-parent</artifactId>
    
    <version>2.1.7.RELEASE</version>
    
    <relativePath/>
    
    
    
    
    </parent>
    
    <groupId>com.ch</groupId>
    
    <artifactId>ch6_5</artifactId>
    
    <version>0.0.1-SNAPSHOT</version>
    
    <name>ch6_5</name>
    
    <description>Demo project for Spring Boot</description>
    
    
    -<properties>
    
    <java.version>11</java.version>
    
    </properties>
    
    
    -<dependencies>
    
    
    -<dependency>
    
    <groupId>org.springframework.boot</groupId>
    
    <artifactId>spring-boot-starter-web</artifactId>
    
    </dependency>
    
    
    
    
    
    -<dependency>
    
    <groupId>mysql</groupId>
    
    <artifactId>mysql-connector-java</artifactId>
    
    <version>5.1.45</version>
    
    
    
    
    </dependency>
    
    
    
    
    
    -<dependency>
    
    <groupId>org.springframework.boot</groupId>
    
    <artifactId>spring-boot-starter-jdbc</artifactId>
    
    </dependency>
    
    
    -<dependency>
    
    <groupId>org.springframework.boot</groupId>
    
    <artifactId>spring-boot-starter-test</artifactId>
    
    <scope>test</scope>
    
    </dependency>
    
    </dependencies>
    
    
    -<build>
    
    
    -<plugins>
    
    
    -<plugin>
    
    <groupId>org.springframework.boot</groupId>
    
    <artifactId>spring-boot-maven-plugin</artifactId>
    
    </plugin>
    
    </plugins>
    
    </build>
    
    </project>

### 3：设置Web应用的上下文路径以及数据源配置信息 ###

在application.properties文件中配置如下内容

server.servlet.context-path=/ch6_5
    ###
    ##数据源信息配置
    ###
    #数据库地址
    spring.datasource.url=jdbc:mysql://localhost:3306/springbootjpa?characterEncoding=utf8
    #数据库MySQL为8.x时，url为jdbc:mysql://localhost:3306/springbootjpa?useSSL=false&serverTimezone=Asia/Beijing&characterEncoding=utf-8
    #数据库用户名
    spring.datasource.username=root
    #数据库密码
    spring.datasource.password=root
    #数据库驱动
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    #数据库MySQL为8.x时，驱动类为com.mysql.cj.jdbc.Driver
    spring.jackson.serialization.indent-output=true  
    #让控制器输出的JSON字符串格式更美观

### 4：创建实体类 ###

创建名为com.ch.ch6\_5.entity的包 并在该包中出啊关键MyUser实体类 代码如下

package com.ch.ch6_5.entity;
    public class MyUser {
    	private Integer id;
    	private String username;
    	private String password;
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	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;
    	}
    }

### 5：创建数据访问层 ###

创建名为com.ch.ch6\_5.repository的包 并创建接口和其实现类

接口代码如下

package com.ch.ch6_5.repository;
    import java.util.List;
    import com.ch.ch6_5.entity.MyUser;
    public interface MyUserRepository {
    	public int saveUser(MyUser myUser);
    	public int deleteUser(Integer id);
    	public int updateUser(MyUser myUser);
    	public List<MyUser> findAll();
    	public MyUser findUserById(Integer id);
    }

实现类代码如下

package com.ch.ch6_5.repository;
    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.RowMapper;
    import org.springframework.stereotype.Repository;
    import com.ch.ch6_5.entity.MyUser;
    @Repository
    public class MyUserRepositoryImpl implements MyUserRepository{
    	@Autowired
    	private JdbcTemplate jdbcTemplate;
    	@Override
    	public int saveUser(MyUser myUser) {
    		String sql = "insert into user (username, password) values (?,?)";
    		Object args[] = {
    				myUser.getUsername(),
    				myUser.getPassword()
    		};
    		return jdbcTemplate.update(sql, args);
    	}
    	@Override
    	public int deleteUser(Integer id) {
    		String sql = "delete from user where id = ? ";
    		Object args[] = {
    				id
    		};
    		return jdbcTemplate.update(sql, args);
    	}
    	@Override
    	public int updateUser(MyUser myUser) {
    		String sql = "update user set username = ?, password = ? where id = ? ";
    		Object args[] = {
    				myUser.getUsername(),
    				myUser.getPassword(),
    				myUser.getId()
    		};
    		return jdbcTemplate.update(sql, args);

### 6：创建业务层 ###

创建名为com.ch.ch6\_5.service的包 并创建接口和实现类

接口代码如下

package com.ch.ch6_5.service;
    import java.util.List;
    import com.ch.ch6_5.entity.MyUser;
    public interface MyUserService {
    	public int saveUser(MyUser myUser);
    	public int deleteUser(Integer id);
    	public int updateUser(MyUser myUser);
    	public List<MyUser> findAll();
    	public MyUser findUserById(Integer id);
    }

实现类代码如下

package com.ch.ch6_5.service;
    import java.util.List;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import com.ch.ch6_5.entity.MyUser;
    import com.ch.ch6_5.repository.MyUserRepository;
    @Service
    public class MyUserServiceImpl implements MyUserService{
    	@Autowired
    	private MyUserRepository myUserRepository;
    	@Override
    	public int saveUser(MyUser myUser) {
    
    	public List<MyUser> findAll() {
    		return myUserRepository.findAll();
    	}
    	@Override
    	public MyUser findUserById(Integer id) {
    		return myUserRepository.findUserById(id);
    	}
    }

### 7：创建控制器类 ###

创建名为com.ch.ch6\_5.controller的包 并创建MyUserController控制器类

代码如下

package com.ch.ch6_5.controller;
    import java.util.List;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    im
    	public int updateUser(MyUser myUser) {
    		return myUserService.updateUser(myUser);
    	}
    	@RequestMapping("/findAll")
    	public List<MyUser> findAll(){
    		return myUserService.findAll();
    	}
    	@RequestMapping("/findUserById")
    	public MyUser findUserById(Integer id) {
    		return myUserService.findUserById(id);
    	}
    }

接下来运行Ch65Application主类然后访问http://localhost:8080/ch6\_5即可

[Spring Boot]: https://blog.csdn.net/jiebaoshayebuhui/article/details/127376886?spm=1001.2014.3001.5502