Spring Boot JDBCTemplate

最新推荐文章于 2024-09-28 03:39:03 发布

蓝黑2020

最新推荐文章于 2024-09-28 03:39:03 发布

阅读量292

点赞数

分类专栏： DB spring boot 文章标签： mysql spring boot jdbc

本文链接：https://blog.csdn.net/duke_ding2/article/details/107417062

版权

DB 同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

spring boot

14 篇文章 0 订阅

订阅专栏

环境

Ubuntu 20.04
MySQL 8.0

简介

JDBCTemplate是Spring MVC内置的对JDBC的一个封装。

准备工作

安装MySQL，并创建所需的DB（ repo ）和table（ job ）。

mysql> use repo;
Database changed

mysql> select * from job;
+-------+-------------+
| jobid | jobname     |
+-------+-------------+
| aaa   | this is aaa |
| bbb   | xxx         |
| ccc   | yyy         |
| ccc   | zzz         |
+-------+-------------+
4 rows in set (0.00 sec)

编码

按照下图所示，创建project（File -> New -> Project），并添加所需依赖：

在这里插入图片描述

打开 application.properties ，填充以下内容：

spring.datasource.url=jdbc:mysql://127.0.0.1/repo?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

创建POJO Job ：

package com.example.test20200717;

public class Job {
    private String jobId;
    private String jobName;

    public String getJobName() {
        return jobName;
    }

    public void setJobName(String jobName) {
        this.jobName = jobName;
    }

    public String getJobId() {
        return jobId;
    }

    public void setJobId(String jobId) {
        this.jobId = jobId;
    }

    public Job(String jobId, String jobName) {
        this.jobId = jobId;
        this.jobName = jobName;
    }

    public Job() {
    }
}

创建Controller Test20200717 ：

package com.example.test20200717;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
public class Test20200717 {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // 获取所有的row
    // 使用query()，无论table里有无记录，都适用
    @RequestMapping(value = "/jobs", method = RequestMethod.GET)
    public List<Job> getJobs() {
        String sql = "SELECT JOBID, JOBNAME FROM job";
        List<Job> jobs = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Job.class));
        return jobs;
    }

    // 获取单条row
    // 方法1：使用queryForMap()，返回的Map里，key是column名字，value是值
    // 只适用返回1条记录，如果返回0条记录或者多条记录，会抛异常
    @RequestMapping(value = "/job1/{jobid}", method = RequestMethod.GET)
    public Map<String, Object> getJob1(@PathVariable String jobid) {
        String sql = "SELECT JOBID, JOBNAME FROM job WHERE JOBID = ?";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, jobid);
        return map;
    }

    // 获取单条row
    // 方法2：使用queryForObject()，跟上面查询所有记录的方法非常类似
    // 只适用返回1条记录，如果返回0条记录或者多条记录，会抛异常
    @RequestMapping(value = "/job2/{jobid}", method = RequestMethod.GET)
    public Job getJob2(@PathVariable String jobid) {
        String sql = "SELECT JOBID, JOBNAME FROM job WHERE JOBID = ?";
        Job job = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Job.class), jobid);
        return job;
    }

    // 获取单条row
    // 方法3：使用queryForObject()，但是自定义字段的映射关系，显然更加灵活
    // 只适用返回1条记录，如果返回0条记录或者多条记录，会抛异常
    @RequestMapping(value = "/job3/{jobid}", method = RequestMethod.GET)
    public Job getJob3(@PathVariable String jobid) {
        String sql = "SELECT JOBID, JOBNAME FROM job WHERE JOBID = ?";
        Job result = jdbcTemplate.queryForObject(sql, (rs, i) -> new Job(rs.getString(1), rs.getString(2)), jobid);
        return result;
    }

    // 插入单条row，本例中只提供了jobname，jobid由后台自动生成
    @RequestMapping(value = "/jobname/{jobname}", method = RequestMethod.POST)
    public void insertJob(@PathVariable String jobname) {
        String sql = "INSERT INTO job (JOBID, JOBNAME) VALUES (?, ?)";
        jdbcTemplate.update(sql, UUID.randomUUID().toString().replace("-", ""), jobname);
    }

    // 删除row，本例中是按jobname删除
    @RequestMapping(value = "/jobname/{jobname}", method = RequestMethod.DELETE)
    public void deleteJob(@PathVariable String jobname) {
        String sql = "DELETE FROM job WHERE JOBNAME = ?";
        jdbcTemplate.update(sql, jobname);
    }

    // 更新row，本例中是按照jobid来更新
    @RequestMapping(value = "/job/{jobid}/jobname/{jobname}", method = RequestMethod.PUT)
    public void updateJob(@PathVariable String jobid, @PathVariable String jobname) {
        String sql = "UPDATE job SET JOBNAME = ? WHERE JOBID = ?";
        jdbcTemplate.update(sql, jobname, jobid);
    }

    // 批处理，插入多条row
    // 本例中jobid和jobname都由前台提供，这是为了方便后台直接将JSON字符串转化为Java对象
    // job信息是JSON格式，放在request body里
    // 注意request需要在header里加上“Content-Type: application/json”
    // 否则后台会返回415错误：“Unsupported Media Type”
    @RequestMapping(value = "/batchjob1", method = RequestMethod.POST)
    public void batchInsertJob1(@RequestBody List<Job> body) {
        String sql = "INSERT INTO job (JOBID, JOBNAME) VALUES (?, ?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, body.get(i).getJobId());
                ps.setString(2, body.get(i).getJobName());
            }

            @Override
            public int getBatchSize() {
                return body.size();
            }
        });
    }

    // 批处理，插入多条row
    // 跟上例类似，不同之处在于可定制化batch的size大小
    // 本例中batchSize为2
    @RequestMapping(value = "/batchjob2", method = RequestMethod.POST)
    public void batchInsertJob2(@RequestBody List<Job> body) {
        String sql = "INSERT INTO job (JOBID, JOBNAME) VALUES (?, ?)";
        jdbcTemplate.batchUpdate(sql, body, 2, (ps, job) -> {
                    ps.setString(1, job.getJobId());
                    ps.setString(2, job.getJobName());
                }
        );
    }
}

注： @RestController 相当于 @ResponseBody 加 @Controller ，所以每个controller不用再单独添加 @ResponseBody 了。
注： @ResponseBody 的作用在于略过视图解析器，将返回值转化为JSON格式的字符串。
注：可以在 @RequestMapping 中加 produces="application/json;charset=utf-8" ，防止出现乱码。

整个project如下图所示：
在这里插入图片描述

运行

运行程序，现在就可以通过API来访问DB了：

查询所有记录

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/jobs" | jq .       
[
  {
    "jobId": "aaa",
    "jobName": "this is aaa"
  },
  {
    "jobId": "bbb",
    "jobName": "xxx"
  },
  {
    "jobId": "ccc",
    "jobName": "yyy"
  },
  {
    "jobId": "ccc",
    "jobName": "zzz"
  }
]
200

查询jobid为 aaa 的记录，方法1

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job1/aaa" | jq .   
{
  "JOBID": "aaa",
  "JOBNAME": "this is aaa"
}
200

查询jobid为 ccc 的记录，方法1（查询结果不唯一，报错）

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job1/ccc" | jq .   
{
  "timestamp": "2020-07-17T14:46:07.932+00:00",
  "status": 500,
  "error": "Internal Server Error",
  "message": "",
  "path": "/job1/ccc"
}
500

查询jobid为 nonono 的记录，方法1（查询结果为0，报错）

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job1/nonono" | jq .
{
  "timestamp": "2020-07-17T14:46:17.737+00:00",
  "status": 500,
  "error": "Internal Server Error",
  "message": "",
  "path": "/job1/nonono"
}
500

查询jobid为 aaa 的记录，方法2

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job2/aaa" | jq .   
{
  "jobId": "aaa",
  "jobName": "this is aaa"
}
200

查询jobid为 ccc 的记录，方法2（查询结果不唯一，报错）

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job2/ccc" | jq .   
{
  "timestamp": "2020-07-17T14:46:37.832+00:00",
  "status": 500,
  "error": "Internal Server Error",
  "message": "",
  "path": "/job2/ccc"
}
500

查询jobid为 nonono 的记录，方法2（查询结果为0，报错）

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job2/nonono" | jq .
{
  "timestamp": "2020-07-17T14:46:41.661+00:00",
  "status": 500,
  "error": "Internal Server Error",
  "message": "",
  "path": "/job2/nonono"
}
500

查询jobid为 aaa 的记录，方法3

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job3/aaa" | jq .   
{
  "jobId": "aaa",
  "jobName": "this is aaa"
}
200

查询jobid为 ccc 的记录，方法3（查询结果不唯一，报错）

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job3/ccc" | jq .   
{
  "timestamp": "2020-07-17T14:46:54.677+00:00",
  "status": 500,
  "error": "Internal Server Error",
  "message": "",
  "path": "/job3/ccc"
}
500

查询jobid为 nonono 的记录，方法3（查询结果为0，报错）

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XGET "http://localhost:8080/job3/nonono" | jq .
{
  "timestamp": "2020-07-17T14:46:57.913+00:00",
  "status": 500,
  "error": "Internal Server Error",
  "message": "",
  "path": "/job3/nonono"
}
500

插入一条记录

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XPOST "http://localhost:8080/jobname/bbb" | jq .
200

更新记录

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XPUT "http://localhost:8080/job/3cc1da161e76496fb92f9ddae5f6e684/jobname/ddd" | jq .
200

删除记录

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XDELETE "http://localhost:8080/jobname/ddd" | jq .
200

批量插入记录，方法1

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XPOST "http://localhost:8080/batchjob1" -H "Content-Type: application/json" -d '[{"jobId": "hhh", "jobName": "hello hhh"}, {"jobId": "iii", "jobName": "hello iii"}, {"jobId": "jjj", "jobName": "hello jjj"}]' | jq .
200

批量插入记录，方法2

➜  ~ curl --silent --write-out '\n%{http_code}\n' -XPOST "http://localhost:8080/batchjob2" -H "Content-Type: application/json" -d '[{"jobId": "ooo", "jobName": "hello ooo"}, {"jobId": "ppp", "jobName": "hello ppp"}, {"jobId": "qqq", "jobName": "hello qqq"}]' | jq .
200