JdbcTemplate-Spring中操作数据库_springboot jdbctemplate操作数据库-CSDN博客

本文链接：https://blog.csdn.net/gs2436/article/details/128810688

文章介绍了如何在SpringBoot应用中使用JdbcTemplate简化JDBC操作，包括配置数据源、插入、更新、删除和查询数据，以及使用BeanPropertyRowMapper和ColumnMapRowMapper进行结果映射。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

介绍

JdbcTemplate是Spring中对JDBC的封装，简化JDBC中比较繁琐的地方：加载数据库驱动、创建连接、释放连接、直接将返回结果映射到实体类等。这样后端开发们，只需更加关注于提供sql和获取结果，操作数据库更加节省高效。

具体使用

这里我们讲解在SpringBoot中如何使用JdbcTemplate。
mysql建好库、表，表sql为：

CREATE TABLE `student` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Age` int NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
  `Birthday` datetime DEFAULT NULL,
  `Score` int DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

代码方面，首先引入依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.7.4</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.20</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <version>2.7.4</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
</dependency>

编写配置类，注册JdbcTemplate类型的bean：

package com.gs.spring_boot_demo.jdbcTemplate;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;

@Configuration
public class MysqlConfig {

    //@Bean不能少
    @Bean
    public DataSource dataSource() {
        // 配置数据源
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://ip:port/数据库名称?" + 
        "characterEncoding=utf8&useSSL=false&" + 
        "serverTimezone=Asia/Shanghai");
        //连接数据库的用户名
        dataSource.setUsername("xxx");
        //密码
        dataSource.setPassword("xxx");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(@Autowired DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

注入JdbcTemplate，操作db：

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class JdbcTemplateTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 增、删、改
     */
    @Test
    public void update() throws ParseException {
        Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").
        parse("1998-08-20 16:20:53");
        
        int i = jdbcTemplate.update(
        "insert into student(Age, Name, Birthday, Score) " + 
        "values(?, ?, ?, ?)", 18, "coder", date, 98);
        System.out.println(i);

        int j = jdbcTemplate.update(
        "update student set age = age + 2 where id = ?", 1);
        System.out.println(j);

        int k = jdbcTemplate.update(
        "delete from student where id = ?", 1);
        System.out.println(k);
    }

    /**
     * 查询
     */
    @Test
    public void query() {
        //查询单个值
        Integer count = jdbcTemplate.queryForObject(
        "select count(*) from student", Integer.class);
        System.out.println("总数：" + count);

        //查询某一行的值
        Map<String, Object> map = jdbcTemplate.queryForObject(
        "select * from student where id = ?", new ColumnMapRowMapper(),1);
        System.out.println(map);

        //查询某一行的值，用实体类来接收
        Student stu = jdbcTemplate.queryForObject(
        "select * from student where id = ?", 
        new BeanPropertyRowMapper<>(Student.class), 1);
        System.out.println(stu);

        //查询多行的值
        List<Map<String, Object>> list1 = jdbcTemplate.query(
        "select * from student", new ColumnMapRowMapper());
        System.out.println(list1);

        //查询多行的值，用实体类来接收
        List<Student> list2 = jdbcTemplate.query(
        "select * from student", 
        new BeanPropertyRowMapper<>(Student.class));
        System.out.println(list2);
    }

}

import lombok.Data;
import java.util.Date;

/**
 * 属性名和数据库的字段名一样，不区分大小写
 */
@Data
public class Student {

    private int id;

    private Integer age;

    private String name;

    private Date birthday;

    private int score;

}