SpringBoot中使用JDBC

JDBC基础使用：

JDBC基础使用_做测试的喵酱的博客-CSDN博客

一、SpringBoot使用JDBC，依赖

依赖：

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>

二、在springboot 配置文件application.yml中，配置数据库信息

spring:
  datasource:
    url: jdbc:mysql://124.70.7.16:3306/chen?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

三、JdbcTemplate

这里演示一下spring boot jdbc的使用。对Student表进行增删改查的操作。

3.1 数据库中已经存在Student表

mysql数据库，chen库下，存在Student表。

创建Student表的SQL

CREATE TABLE `Student` (
  `Sno` char(10) DEFAULT NULL,
  `Sname` varchar(20) DEFAULT NULL,
  `Ssex` char(2) DEFAULT NULL,
  `Sage` tinyint(4) DEFAULT NULL,
  `Sdept` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.2 创建与Student表对应的类

创建BTStudent类

package com.example.com_chenshuai.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class BTStudent {
    private String Sno;
    private String Sname;
    private String  Ssex;
    private int Sage;
    private String Sdept;
}

3.3 编写DAO层

接口StudentDao，有3个抽象方法。分别为

增加addStudent(BTStudent btStudent);

查询全部findAll()

根据学生ID进行查询 findById(String id)

package com.example.com_chenshuai.dao;

import com.example.com_chenshuai.entity.BTStudent;

import java.util.List;

public interface StudentDao {
    boolean addStudent(BTStudent btStudent);
    List<BTStudent> findAll();
    BTStudent findById(String id);
}

实现类StudentDaoImpl

package com.example.com_chenshuai.dao.Impl;

import com.example.com_chenshuai.dao.StudentDao;
import com.example.com_chenshuai.entity.BTStudent;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class StudentDaoImpl implements StudentDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public boolean addStudent(BTStudent btStudent) {
        String sql = "insert into Student values (?,?,?,?,?)";
        int n= jdbcTemplate.update(sql,btStudent.getSno(), btStudent.getSname(), btStudent.getSsex(), btStudent.getSage(), btStudent.getSdept());

        return 1==n;
    }

    @Override
    public List<BTStudent> findAll() {
        String sql = "select * from Student";
        RowMapper<BTStudent> rowMapper = new BeanPropertyRowMapper<>(BTStudent.class);
        List<BTStudent> list = jdbcTemplate.query(sql, rowMapper);
        return list;
    }

    @Override
    public BTStudent findById(String id) {
        String sql = "select * from Student where Sno = ?";
        RowMapper<BTStudent> rowMapper = new BeanPropertyRowMapper<>(BTStudent.class);
        BTStudent btStudent=jdbcTemplate.queryForObject(sql,new Object[]{id},rowMapper);
        return btStudent;
    }
}

3.4 调用

这里使用单元测试调用一下

package com.example.com_chenshuai;

import com.example.com_chenshuai.Controller.HiController;
import com.example.com_chenshuai.dao.StudentDao;
import com.example.com_chenshuai.entity.BTStudent;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class ComChenshuaiApplicationTests {
    @Autowired
    private HiController hiController;

    @Autowired
    private StudentDao studentDao;

    @Test
    void test1() {
        String hi = hiController.hi();
        System.out.println("hi===="+hi);
    }

    @Test
    void tes2(){
        BTStudent btStudent = new BTStudent("201215128","王五","女",20,"LOVE");
        boolean b = studentDao.addStudent(btStudent);
        System.out.println("b = " + b);

    }

    @Test
    void tes3(){
        List<BTStudent> list = studentDao.findAll();
        System.out.println("list = " + list);
    }

    @Test
    void tes4(){
        BTStudent byId = studentDao.findById("201215125");
        System.out.println("byId = " + byId);
    }

}

四、什么是rowmapper

row，行。数据库的中一行数据，也就是一条数据。

mapper，映射。将一条数据，组装成一个实体类。

我们在执行查询sql的时候，返回结果是以什么形式存在的呢？我们将返回结果组装成了我们自定义类的实例。

rowmapper里面，就是写的我们具体组装的逻辑。如下

        List<TbUser> tbUsers1 = testRowMapper(sql, null, new RowMapper<TbUser>() {

            // 重写map
            @Override
            public TbUser map(ResultSet rs) throws SQLException {
                TbUser tbUser = new TbUser();
                // 给tbUser赋值
                tbUser.setId(rs.getLong(1));
                tbUser.setName(rs.getString(2));
                return tbUser;
            }
        });

传参中，需要传一个rowmapper，mapper就是我们具体将一条数据封装到我们自定义类里的详细逻辑。（我们将返回结果/一条数据，）组装到了我们自定义类里。

return的时候，不是return的一大堆字符串，而是一个对象。