springboot使用JdbcTemplate实现简单的增删改查功能

springboot使用jdbctemplate简化了jdbc的使用步骤，让我们可以很容易的使用它，下面的例子简单的列举了jdbctemplate一些方法的使用，实现了对用户的增删改查操作，话不多说，我们开始。

0、创建用户表，建表语句如下，越简单越好

create table users(
    id bigint primary key auto_increment comment '主键id',
    username varchar(128) comment  '用户名',
    password varchar(128) comment '密码'
) comment '用户名';

1、新建一个springboot工程

2、引入maven依赖

<?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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.9.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.llg</groupId>
    <artifactId>jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>jdbc</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.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-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3、数据源的配置，这里使用yml的方式进行配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8
    username: root
    password: 12345678
    driver-class-name: com.mysql.cj.jdbc.Driver

4、实体类，注意这里使用了lombok插件

package com.llg.jdbc.entity;

import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
public class Users {
    private Integer id;
    private String username;
    private String password;

}

5、controller类，包括增、删、改、查方法

package com.llg.jdbc.controller;

import com.llg.jdbc.entity.Users;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

@RestController
public class UserController {

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 新增用户
     * @param users
     * @return
     */
    @PostMapping("addUser")
    public String addUser(Users users){
        String sql = "insert into users (username, password) values (?, ?)";
        jdbcTemplate.update(sql, users.getUsername(), users.getPassword());
        return "success";
    }

    /**
     * 删除用户
     * @param id
     * @return
     */
    @GetMapping("deleteUser")
    public String deleteUser(Integer id){
        String sql = "delete from users where id = ?";
        jdbcTemplate.update(sql, id);
        return "success";
    }

    /**
     * 修改用户
     * @param users
     * @return
     */
    @PostMapping("updateUser")
    public String updateUser(Users users){
        String sql = "update users set username = ?, password = ? where id = ?";
        jdbcTemplate.update(sql, users.getUsername(), users.getPassword(), users.getId());
        return "success";
    }


    /**
     * 查询用户
     * @return
     */
    @GetMapping("listUsers")
    public List<Users> listUsers(){
        String sql = "select id, username, password from users";
        return jdbcTemplate.query(sql, (a, b) -> new Users(a.getInt("id"),
                a.getString("username"),
                a.getString("password")));

    }

}