springboot整合jdbctemplate教程

这篇文章介绍一下springboot项目整合jdbctemplate的步骤，以及通过jdbctemplate完成数据库的增删改查功能。

第一步：准备数据库

创建数据jdbctemplate，然后执行以下sql脚本

/*
 Navicat Premium Data Transfer

 Source Server         : MariaDB
 Source Server Type    : MariaDB
 Source Server Version : 100605 (10.6.5-MariaDB)
 Source Host           : 127.0.0.1:3306
 Source Schema         : jdbctemplate

 Target Server Type    : MariaDB
 Target Server Version : 100605 (10.6.5-MariaDB)
 File Encoding         : 65001

 Date: 26/08/2023 16:23:07
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for song
-- ----------------------------
DROP TABLE IF EXISTS `song`;
CREATE TABLE `song`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '歌曲编号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '歌曲名',
  `singer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '歌手',
  `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '歌曲文件URL',
  `uploaded` tinyint(4) NOT NULL DEFAULT 0 COMMENT '歌曲文件是否存在',
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述信息',
  `last_update_time` datetime NULL DEFAULT NULL COMMENT '最后一次修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of song
-- ----------------------------
INSERT INTO `song` VALUES ('20230802123058', '我超喜欢你', '欧阳朵', NULL, 0, '', '2023-08-02 12:30:58');
INSERT INTO `song` VALUES ('20230802123128', '恋爱的猫', '月小妞', NULL, 0, '', '2023-08-02 12:31:28');
INSERT INTO `song` VALUES ('20230802123256', '骄傲的你', '尹昔眠', NULL, 0, '', '2023-08-02 12:32:56');
INSERT INTO `song` VALUES ('20230802123341', '勉为其难', '王冕', NULL, 0, '', '2023-08-02 12:33:41');
INSERT INTO `song` VALUES ('20230822191716', '倒带', '蔡依林', NULL, 0, '', '2023-08-22 19:17:16');
INSERT INTO `song` VALUES ('20230822192055', '错的人', '萧亚轩', NULL, 0, '', '2023-08-22 19:20:55');
INSERT INTO `song` VALUES ('20230822193447', '阴天', '莫文蔚', NULL, 0, '', '2023-08-22 19:34:47');
INSERT INTO `song` VALUES ('20230822215824', '赤伶', '执素兮', NULL, 0, '', '2023-08-22 21:58:24');
INSERT INTO `song` VALUES ('20230822220348', '山楂树之恋', '大能人', NULL, 0, '', '2023-08-22 22:03:48');

SET FOREIGN_KEY_CHECKS = 1;

第二步：创建springboot项目

1、创建一个springboot项目并命名为jdbctemplate

2、添加spring-jdbc和项目要使用的依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.9</version>
        <relativePath/>
    </parent>

    <groupId>com.example</groupId>
    <artifactId>jdbctemplate</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>1.8</java.version>
        <mysql.version>8.0.28</mysql.version>
        <jdbc.version>5.0.2.RELEASE</jdbc.version>
    </properties>

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

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${jdbc.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

第三步：创建jdbctemplate配置类

在config包下创建配置类，配置数据源和JdbcTemplate的bean。

package com.example.jdbctemplate.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * @author heyunlin
 * @version 1.0
 */
@Configuration
public class JdbcTemplateConfig {

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

    @Bean
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();

        transactionManager.setDataSource(dataSource());

        return transactionManager;
    }

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setUsername("root");
        dataSource.setPassword("root");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/jdbctemplate?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai");

        return dataSource;
    }

}

第四步：使用jdbctemplate

根目录下创建service包，然后创建一个SongService接口

package com.example.jdbctemplate.service;

import java.util.Map;

/**
 * @author heyunlin
 * @version 1.0
 */
public interface SongService {

    Map<String, Object> selectByPage();
}

在service包下创建impl子包，创建一个SongService的实现类，然后调用jdbcTemplate的crud方法。

package com.example.jdbctemplate.service.impl;

import com.example.jdbctemplate.service.SongService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.Map;

/**
 * @author heyunlin
 * @version 1.0
 */
@Service
public class SongServiceImpl implements SongService {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public SongServiceImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Map<String, Object> selectByPage() {
        Map<String, Object> objectMap = jdbcTemplate.queryForMap("select * from song limit 0, 1");

        System.out.println(objectMap);

        return objectMap;
    }

}

jdbctemplate里的方法就不介绍了，有兴趣的可以自己去学习一下，毕竟jdbctemplate使用的不多，mybatis才是yyds。

文章涉及代码已经上传到了git，需要的可以通过以下链接获取，

springboot整合jdbctemplatehttps://gitee.com/he-yunlin/jdbctemplate.git好了，文章就分享到这里了，看完不要忘了点赞+收藏哦~