SpringBoot 实战 | 用 JdbcTemplates 访问 Mysql

栏目: Java · 发布时间: 5年前

内容简介:微信公众号:一个优秀的废人如题,今天介绍 springboot 通过jdbc访问关系型mysql,通过 spring 的 JdbcTemplate 去访问。构建 SpringBoot 项目,不会的朋友参考旧文章:

微信公众号:一个优秀的废人

前言

如题,今天介绍 springboot 通过jdbc访问关系型mysql,通过 spring 的 JdbcTemplate 去访问。

准备工作

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

构建 SpringBoot 项目,不会的朋友参考旧文章: 如何使用 IDEA 构建 Spring Boot 工程

项目目录结构

SpringBoot 实战 | 用 JdbcTemplates 访问 Mysql

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="turodog@foxmail.com">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="turodog@foxmail.com">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="turodog@foxmail.com">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("UPDATE  student 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="turodog@foxmail.com">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="turodog@foxmail.com">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="turodog@foxmail.com">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);
    }
}

演示结果

SpringBoot 实战 | 用 JdbcTemplates 访问 Mysql

其他的 api 测试可以通过 postman 测试。我这里已经全部测试通过,请放心使用。

源码下载: https://github.com/turoDog/De...

后语

以上SpringBoot 用 JdbcTemplates 访问 Mysql 的教程。最后,对 PythonJava 感兴趣请长按二维码关注一波,我会努力带给你们价值,如果觉得本文对你哪怕有一丁点帮助,请帮忙点好看,让更多人知道。

另外,关注之后在发送 1024 可领取免费学习资料。资料内容详情请看这篇旧文: Python、C++、Java、 LinuxGo 、前端、算法资料分享

SpringBoot 实战 | 用 JdbcTemplates 访问 Mysql


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Web Design Handbook

Web Design Handbook

Baeck, Philippe de 编 / 2009-12 / $ 22.54

This non-technical book brings together contemporary web design's latest and most original creative examples in the areas of services, media, blogs, contacts, links and jobs. It also traces the latest......一起来看看 《Web Design Handbook》 这本书的介绍吧!

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

MD5 加密
MD5 加密

MD5 加密工具

html转js在线工具
html转js在线工具

html转js在线工具