Spring boot + jdbcTemplate + MySQL实现增删改查

目录结构

数据库结构

1. 添加相关引用

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- 引入jdbc支持 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- 连接MySQL数据库 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- 生成api文档 -->

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.2.2</version>
        </dependency>
    </dependencies>

2. 在application.properties配置连接MySQL数据库

spring.datasource.url=jdbc:mysql://localhost:3306/test_db?serverTimezone=GMT%2B8&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

3. 创建实体对象

Student.java

package com.hsl.springbootjdbctemplate.entity;

import java.io.Serializable;

public class Student {
    private int id;
    private String name;
    private int sex;
    private int age;

    public Student() {

    }

    public Student(int id, String name, int sex, int age) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
    }

    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 int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

4. 定义接口

UserService.java

package com.hsl.springbootjdbctemplate.service;

import com.hsl.springbootjdbctemplate.entity.Student;

import java.util.List;
import java.util.Map;

public interface UserService {
    /**
     * 获取用户总量
     * @return
     */
    String getAllUsers();

    /**
     * 获取全部学生
     * @return
     */
    List<Map<String, Object>> findAll();

    /**
     * 根据id获取学生
     * @param id
     * @return
     */
    Student getById(int id);

    /**
     * 增加学生
     * @param student
     * @return
     */
    int addStu(Student student);

    /**
     * 根据id删除学生
     * @param id
     * @return
     */
    int deleteStu(int id);

    /**
     * 修改学生信息
     * @param student
     * @return
     */
    int updateStu(Student student);

    /**
     * 判断是否存在该学生
     * @param id
     * @return
     */
    int isHasStu(int id);
}

5. 通过jdbcTemplate实现接口中定义的数据访问操作

UserServiceImpt.java

package com.hsl.springbootjdbctemplate.service;

import com.hsl.springbootjdbctemplate.entity.Student;
import com.sun.org.apache.bcel.internal.generic.LSTORE;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.swing.plaf.basic.BasicTreeUI;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

@Service
public class UserServiceImpt implements UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public String getAllUsers(){
        return jdbcTemplate.queryForObject("select count(1) from STUDENT", String.class);
    }

    @Override
    public List<Map<String, Object>> findAll() {
        String sql = "select * from sTUDENT";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public Student getById(int id) {
        String sql = "select * from student where id = ? ";
        List<Student> stu = jdbcTemplate.query(sql,new Object[]{id}, new StudentRowMapper());
        Student student = null;
        if(!stu.isEmpty()){
            student = stu.get(0);
        }
        return student;
    }

    /**
     * 插入用户-防止sql注入-可以返回该条记录的主键
     * @param student
     * @return
     */
    @Override
    public int addStu(Student student) {
        String sql = "insert into student(id,name,sex,age) values(null,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int resRow = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql,new String[]{"id"});
                ps.setString(1,student.getName());
                ps.setInt(2,student.getSex());
                ps.setInt(3,student.getAge());
                return ps;
            }
        },keyHolder);
        System.out.println("操作记录数："+resRow+" 主键："+keyHolder.getKey());
        return Integer.parseInt(keyHolder.getKey().toString());
    }

    @Override
    public int deleteStu(int id) {
        String sql = "delete from student where id = ?";
        return jdbcTemplate.update(sql,id);
    }

    @Override
    public int updateStu(Student student) {
        String sql = "update student set name=?,sex=?,age=? where id=?";
        int res = jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1,student.getName());
                preparedStatement.setInt(2,student.getSex());
                preparedStatement.setInt(3,student.getAge());
                preparedStatement.setInt(4,student.getId());
            }
        });
        return res;
    }

    @Override
    public int isHasStu(int id) {
        String sql = "select * from student where id=?";
        List<Student> student = jdbcTemplate.query(sql, new Object[]{id}, new StudentRowMapper());
        if (student!=null && student.size()>0){
            return 1;
        } else {
            return 0;
        }
    }
}


class StudentRowMapper implements RowMapper<Student> {

    @Override
    public Student mapRow(ResultSet resultSet,int i) throws SQLException{
        Student stu = new Student();
        stu.setId(resultSet.getInt("id"));
        stu.setAge(resultSet.getInt("age"));
        stu.setSex(resultSet.getInt("sex"));
        stu.setName(resultSet.getString("name"));
        return stu;
    }
}

6. 定义controller

UserController.java

package com.hsl.springbootjdbctemplate.controller;

import com.hsl.springbootjdbctemplate.entity.Student;
import com.hsl.springbootjdbctemplate.service.UserService;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/users")
public class UserController {
    @Autowired
    private UserService service;

    @ApiOperation(value = "获取用户总数",notes = "")
    @RequestMapping(value = "/getAllUsers", method = RequestMethod.GET)
    public String getAllUsers(){
        return service.getAllUsers();
    }

    //http://127.0.0.1:8080/users/
    @ApiOperation(value = "获取用户列表",notes = "")
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public List<Map<String, Object>> findAll(){
        List<Map<String, Object>> list = service.findAll();
        return list;
    }

    //http://127.0.0.1:8080/users/1
    @ApiOperation(value = "获取用户",notes = "根据用户id获取用户")
    @RequestMapping(value = "/{id}", method = RequestMethod.GET)
    public Student getStuById(@PathVariable int id){
        Student student = service.getById(id);
        return student;
    }

    //http://127.0.0.1:8080/users/
    @ApiOperation(value = "添加用户",notes = "添加用户")
    @RequestMapping(value = "/", method = RequestMethod.POST)
    public int addStu(Student student){
        //System.out.println(student.getName());
        int res = service.addStu(student);
        return res;
    }

    @ApiOperation(value = "删除用户",notes = "根据用户Id删除用户")
    @RequestMapping(value = "/{id}", method = RequestMethod.DELETE)
    public int deleteStu(@PathVariable int id){
        System.out.println(id);
        int res = service.deleteStu(id);
        return res;
    }

    @ApiOperation(value = "修改用户信息",notes = "根据用户Id修改用户信息")
    @RequestMapping(value = "/", method = RequestMethod.PUT)
    public int updateStu(Student student){
        System.out.println(student.getId());
        int isHas = service.isHasStu(student.getId());
        int res = 0;
        if (isHas==1){
            res = service.updateStu(student);
        }
        return res;
    }

}

7. 生成文档

在controller层新建Swagger2.java，并在UserController.java中添加注解@ApiOpeation(value="", notets="")

package com.hsl.springbootjdbctemplate.controller;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class Swagger2 {
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.hsl"))
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("使用jdbcTemplate的增删该查")
                .description("第一个jdbcTemplate")
                .termsOfServiceUrl("http://blog.didispace.com/")
                .contact("LevineHua")
                .version("1.0")
                .build();
    }
}