SpringBoot 實戰 | 用 JdbcTemplates 訪問 Mysql

摘要： 微信公眾號：一個優秀的廢人 前言 如題，今天介紹 springboot 通過jdbc訪問關係型mysql,通過 spring 的 JdbcTemplate 去訪問。 準備工作 SpringBoot 2.x jdk 1.8 maven 3.0 idea ...

微信公眾號：一個優秀的廢人

前言

如題，今天介紹 springboot 通過jdbc訪問關係型mysql,通過 spring 的 JdbcTemplate 去訪問。

準備工作

• SpringBoot 2.x
• jdk 1.8
• maven 3.0
• idea
• mysql

構建 SpringBoot 專案，不會的朋友參考舊文章： 如何使用 IDEA 構建 Spring Boot 工程

專案目錄結構

pom 檔案引入依賴

<dependencies>xml
<!-- jdbcTemplate 依賴 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 開啟web: -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!-- mysql連線類 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<!-- druid 連線池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.13</version>
</dependency>

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

application.yaml 配置資料庫資訊

spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=true
username: 資料庫使用者名稱
password: 資料庫密碼

實體類

package com.nasus.domain;

/**
 * Project Name:jdbctemplate_demo <br/>
 * Package Name:com.nasus.domain <br/>
 * Date:2019/2/3 10:55 <br/>
 * <b>Description:</b> TODO: 描述該類的作用 <br/>
 *
 * @author <a href="[email protected]">nasus</a><br/>
 * Copyright Notice =========================================================
 * This file contains proprietary information of Eastcom Technologies Co. Ltd.
 * Copying or reproduction without prior written approval is prohibited.
 * Copyright (c) 2019 =======================================================
 */
public class Student {

private Integer id;

private String name;

private Integer age;

public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Integer getAge() {
return age;
}

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

@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}

dao 層

package com.nasus.dao;

import com.nasus.domain.Student;
import java.util.List;

/**
 * Project Name:jdbctemplate_demo <br/>
 * Package Name:com.nasus.dao <br/>
 * Date:2019/2/3 10:59 <br/>
 * <b>Description:</b> TODO: 描述該類的作用 <br/>
 * @author <a href="[email protected]">nasus</a><br/>
 */
public interface IStudentDao {

int add(Student student);

int update(Student student);

int delete(int id);

Student findStudentById(int id);

List<Student> findStudentList();

}

具體實現類：

package com.nasus.dao.impl;

import com.nasus.dao.IStudentDao;
import com.nasus.domain.Student;
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.stereotype.Repository;

/**
 * Project Name:jdbctemplate_demo <br/>
 * Package Name:com.nasus.dao.impl <br/>
 * Date:2019/2/3 11:00 <br/>
 * <b>Description:</b> TODO: 描述該類的作用 <br/>
 *
 * @author <a href="[email protected]">nasus</a><br/>
 */
@Repository
public class IStudentDaoImpl implements IStudentDao{

@Autowired
private JdbcTemplate jdbcTemplate;

@Override
public int add(Student student) {
return jdbcTemplate.update("insert into student(name, age) values(?, ?)",
student.getName(),student.getAge());
}

@Override
public int update(Student student) {
return jdbcTemplate.update("UPDATEstudent SET NAME=? ,age=? WHERE id=?",
student.getName(),student.getAge(),student.getId());
}

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

@Override
public Student findStudentById(int id) {
// BeanPropertyRowMapper 使獲取的 List 結果列表的資料庫欄位和實體類自動對應
List<Student> list = jdbcTemplate.query("select * from student where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Student.class));
if(list!=null && list.size()>0){
Student student = list.get(0);
return student;
}else{
return null;
}
}

@Override
public List<Student> findStudentList() {
// 使用Spring的JdbcTemplate查詢資料庫，獲取List結果列表，資料庫表字段和實體類自動對應，可以使用BeanPropertyRowMapper
List<Student> list = jdbcTemplate.query("select * from student", new Object[]{}, new BeanPropertyRowMapper(Student.class));
if(list!=null && list.size()>0){
return list;
}else{
return null;
}
}

}

service 層

package com.nasus.service;

import com.nasus.domain.Student;
import java.util.List;

/**
 * Project Name:jdbctemplate_demo <br/>
 * Package Name:com.nasus.service <br/>
 * Date:2019/2/3 11:17 <br/>
 * <b>Description:</b> TODO: 描述該類的作用 <br/>
 *
 * @author <a href="[email protected]">nasus</a><br/>
 */
public interface IStudentService {

int add(Student student);

int update(Student student);

int delete(int id);

Student findStudentById(int id);

List<Student> findStudentList();

}

實現類：

package com.nasus.service.impl;

import com.nasus.dao.IStudentDao;
import com.nasus.domain.Student;
import com.nasus.service.IStudentService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

/**
 * Project Name:jdbctemplate_demo <br/>
 * Package Name:com.nasus.service.impl <br/>
 * Date:2019/2/3 11:18 <br/>
 * <b>Description:</b> TODO: 描述該類的作用 <br/>
 *
 * @author <a href="[email protected]">nasus</a><br/>
 * Copyright Notice =========================================================
 * This file contains proprietary information of Eastcom Technologies Co. Ltd.
 * Copying or reproduction without prior written approval is prohibited.
 * Copyright (c) 2019 =======================================================
 */
@Repository
public class IStudentServiceImpl implements IStudentService {

@Autowired
private IStudentDao iStudentDao;

@Override
public int add(Student student) {
return iStudentDao.add(student);
}

@Override
public int update(Student student) {
return iStudentDao.update(student);
}

@Override
public int delete(int id) {
return iStudentDao.delete(id);
}

@Override
public Student findStudentById(int id) {
return iStudentDao.findStudentById(id);
}

@Override
public List<Student> findStudentList() {
return iStudentDao.findStudentList();
}

}

controller 構建 restful api

package com.nasus.controller;

import com.nasus.domain.Student;
import com.nasus.service.IStudentService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * Project Name:jdbctemplate_demo <br/>
 * Package Name:com.nasus.controller <br/>
 * Date:2019/2/3 11:21 <br/>
 * <b>Description:</b> TODO: 描述該類的作用 <br/>
 *
 * @author <a href="[email protected]">nasus</a><br/>
 */
@RestController
@RequestMapping("/student")
public class StudentController {

@Autowired
private IStudentService iStudentService;

@PostMapping("")
public int addStudent(@RequestBody Student student){
return iStudentService.add(student);
}

@PutMapping("/{id}")
public String updateStudent(@PathVariable Integer id, @RequestBody Student student){
Student oldStudent = new Student();
oldStudent.setId(id);
oldStudent.setName(student.getName());
oldStudent.setAge(student.getAge());
int t = iStudentService.update(oldStudent);
if (t == 1){
return student.toString();
}else {
return "更新學生資訊錯誤";
}
}

@GetMapping("/{id}")
public Student findStudentById(@PathVariable Integer id){
return iStudentService.findStudentById(id);
}

@GetMapping("/list")
public List<Student> findStudentList(){
return iStudentService.findStudentList();
}

@DeleteMapping("/{id}")
public int deleteStudentById(@PathVariable Integer id){
return iStudentService.delete(id);
}
}

演示結果

其他的 api 測試可以通過 postman 測試。我這裡已經全部測試通過，請放心使用。

原始碼下載： https://github.com/turoDog/De...

後語

以上SpringBoot 用 JdbcTemplates 訪問Mysql 的教程。最後，對 Python 、Java 感興趣請長按二維碼關注一波，我會努力帶給你們價值，如果覺得本文對你哪怕有一丁點幫助，請幫忙點好看，讓更多人知道。

另外，關注之後在傳送 1024 可領取免費學習資料。資料內容詳情請看這篇舊文： Python、C++、Java、Linux、Go、前端、演算法資料分享