springboot整合jdbctemplate教程

沐雨橙风ιε

已于 2024-12-14 21:12:44 修改

阅读量2.5k

点赞数

分类专栏：常用开源框架、中间件的使用文章标签： spring boot java

于 2023-08-26 17:20:11 首次发布

本文链接：https://blog.csdn.net/heyl163_/article/details/132513247

版权

常用开源框架、中间件的使用专栏收录该内容

31 篇文章

订阅专栏

本文详细介绍了如何在SpringBoot项目中整合JdbcTemplate进行数据库操作，包括设置数据源、创建配置类、实体类定义、服务层接口实现和控制器层调用，以及使用execute()方法执行CRUD操作。

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

这篇文章介绍一下如何在springboot项目中整合jdbctemplate，并完成数据库的增、删、改、查功能。

一、准备数据

创建数据库jdbc，在jdbc数据库下执行以下SQL脚本。

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 '歌手',
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '歌曲信息',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '歌曲表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of song
-- ----------------------------
INSERT INTO `song` VALUES ('20210522153649', '贩卖日落', '蓝心羽', '');
INSERT INTO `song` VALUES ('20210522153812', '宠坏', '李俊佑、潘柚彤', '');
INSERT INTO `song` VALUES ('20210522153941', '爱的魔法', '金莎', '');
INSERT INTO `song` VALUES ('20210522154139', '多肉少女', '赵芷彤Cassie', '');
INSERT INTO `song` VALUES ('20210522154331', '爱一点', '王力宏、章子怡', '');
INSERT INTO `song` VALUES ('20210522154653', '换世奇恋', '排骨教主', '《画江湖之换世门生 原声带》');
INSERT INTO `song` VALUES ('20210522154751', '镜心之歌', '邵夷贝', '');
INSERT INTO `song` VALUES ('20210522154945', '诺言', '陈洁丽', '《百变机兽之洛洛历险记》动画ED');
INSERT INTO `song` VALUES ('20210522155118', '无别', '张信哲', '《天官赐福》动画OP');
INSERT INTO `song` VALUES ('20210522155349', '快乐星猫', '牛奶咖啡', '《快乐星猫》动画主题曲');
INSERT INTO `song` VALUES ('20210522160205', '起风了', '周深', '');
INSERT INTO `song` VALUES ('20210522161509', '星辰觉醒', '屠化冰', '');
INSERT INTO `song` VALUES ('20210522184153', '可不可以撩', '曹懵萌', '');
INSERT INTO `song` VALUES ('20210522184207', '心动的感觉', '醋醋', '');
INSERT INTO `song` VALUES ('20210522184449', '百花香', '魏新雨', '');
INSERT INTO `song` VALUES ('20210522184618', '陨落', '不是花火呀', '');
INSERT INTO `song` VALUES ('20210522185212', '花开半夏', '爱朵女孩', '');
INSERT INTO `song` VALUES ('20210522190138', '白月光', '张信哲', '');
INSERT INTO `song` VALUES ('20210522191452', '小幸运', '田馥甄', '');
INSERT INTO `song` VALUES ('20210522192302', '大天蓬', '李袁杰', '');
INSERT INTO `song` VALUES ('20210522192641', '飞鸟和蝉', '任然', '');
INSERT INTO `song` VALUES ('20210522193453', '爱，存在', '你的好上好佳', '');
INSERT INTO `song` VALUES ('20210525233923', '喜欢你', '邓紫棋', '');
INSERT INTO `song` VALUES ('20210525233936', '勇气', '梁静茹', '');
INSERT INTO `song` VALUES ('20210530193908', '一爱难求', '徐佳莹', '《扶摇》电视剧主题曲');
INSERT INTO `song` VALUES ('20210530194109', '桃花诺', '邓紫棋', '《上古情歌》电视剧主题曲');
INSERT INTO `song` VALUES ('20210530194410', '枕上书', '董贞', '《三生三世枕上书》网络剧主题曲');
INSERT INTO `song` VALUES ('20210530194546', '千年', '金志文、吉克隽逸', '《天乩之白蛇传说》网络剧插曲');
INSERT INTO `song` VALUES ('20210530194901', '古画', '鞠婧祎', '《如意芳霏》电视剧插曲');
INSERT INTO `song` VALUES ('20210530211557', '魔法城堡', 'TFBOYS', '');
INSERT INTO `song` VALUES ('20210601181447', '最美的期待', '周笔畅', '');
INSERT INTO `song` VALUES ('20210601195425', '晴天', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601195830', '稻香', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601195838', '七里香', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601195852', '告白气球', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601195903', '听妈妈的话', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601195918', '青花瓷', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601195931', '给我一首歌的时间', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601195941', '明明就', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200013', '搁浅', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200019', '珊瑚海', '周杰伦、Lara梁心颐', '《十一月的萧郎》');
INSERT INTO `song` VALUES ('20210601200042', '兰亭序', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200111', '说好的幸福呢', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200116', '发如雪', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200212', '烟花易冷', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200231', '简单爱', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200243', '美人鱼', '林俊杰', '《第二天堂》');
INSERT INTO `song` VALUES ('20210601200304', '东风破', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200312', '安静', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200328', '爱在西元前', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200342', '龙卷风', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200353', '甜甜的', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200407', '千里之外', '周杰伦、费玉清 ', '');
INSERT INTO `song` VALUES ('20210601200429', '菊花台', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200455', '蜗牛', '周杰伦', '');
INSERT INTO `song` VALUES ('20210601200823', '可惜没如果', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601200842', '修炼爱情', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601200912', 'Always Online', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201433', '等一分钟', '徐誉滕', '');
INSERT INTO `song` VALUES ('20210601201449', '醉赤壁', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201613', '背对背拥抱', '林俊杰', '《爱情睡醒了》电视剧插曲');
INSERT INTO `song` VALUES ('20210601201651', '她说', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201714', '不潮不用花钱', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201726', '一千年以后', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201742', '爱不会绝迹', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201752', '曹操', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201819', '江南', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601201902', '小酒窝', '蔡卓妍、林俊杰', '');
INSERT INTO `song` VALUES ('20210601201920', '爱要怎么说出口', '林俊杰', '');
INSERT INTO `song` VALUES ('20210601202232', '匆匆那年', '王菲', '');
INSERT INTO `song` VALUES ('20210601202251', '泡沫', '邓紫棋', '');
INSERT INTO `song` VALUES ('20210601202445', '爱丫爱丫', 'By2', '《爱情是从告白开始的》电视剧原声带');
INSERT INTO `song` VALUES ('20210601202557', '玫瑰花的葬礼', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202625', '断桥残雪', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202637', '庐州月', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202653', '清明雨上', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202659', '素颜', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202712', '城府', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202728', '幻听', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202740', '你若成风', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202751', '千百度', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202808', '天龙八部之宿敌', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202846', '燕归巢', '张靓颖、张杰', '');
INSERT INTO `song` VALUES ('20210601202900', '全球变冷', '许嵩', '');
INSERT INTO `song` VALUES ('20210601202912', '山水之间', '许嵩', '');
INSERT INTO `song` VALUES ('20210601203051', '破茧', '张韶涵', '《斗罗大陆》动画第107集ED');
INSERT INTO `song` VALUES ('20210601203136', '不舍', '徐佳莹', '《斗罗大陆》动画插曲');
INSERT INTO `song` VALUES ('20210601203307', '此时此刻与你', '大酥', '《妖怪名单 第二季》主题曲');
INSERT INTO `song` VALUES ('20210601203343', '光与信仰', '血纯茗雅', '《妖怪名单 第一季》动画OP');
INSERT INTO `song` VALUES ('20210601203422', '破茧而出的光芒', '血纯茗雅', '《妖怪名单 第一季》动画ED');
INSERT INTO `song` VALUES ('20210601203443', '爱在身边', 'MOMOKO', '《妖怪名单》');
INSERT INTO `song` VALUES ('20210601203530', '红', '任然', '《妖怪名单之苏九儿》电影主题曲');
INSERT INTO `song` VALUES ('20210601203815', '思无邪', '阿敏', '《妖怪名单之苏九儿》电影土·宣传曲');
INSERT INTO `song` VALUES ('20210601204033', '有你的未来', 'MOMOKO', '《妖怪名单》');
INSERT INTO `song` VALUES ('20210601204118', '沉香', '王贰浪', '《妖怪名单之苏九儿》电影宣传曲');
INSERT INTO `song` VALUES ('20210601204140', '星落', '周思涵', '《妖怪名单之苏九儿》电影宣传曲');
INSERT INTO `song` VALUES ('20210601204217', '酒醉三巡', '尹熙水', '《妖怪名单之苏九儿》电影木·宣传曲');
INSERT INTO `song` VALUES ('20210601204321', '悲别', '王贰浪', '《妖怪名单之苏九儿》电影概念宣传曲');
INSERT INTO `song` VALUES ('20210601204418', '她的故事', '毛若琼', '《妖怪名单之苏九儿》电影合欢人物主题曲');
INSERT INTO `song` VALUES ('20210601204503', '轮回 ·他', '邓鼓', '《妖怪名单之苏九儿》电影封无夜人物主题曲');
INSERT INTO `song` VALUES ('20210601204532', '毒药', 'Mars毒药', '《妖怪名单之苏九儿》电影妖王人物主题曲');
INSERT INTO `song` VALUES ('20210601204817', '等', '毛若琼', '《妖怪名单之苏九儿》电影苏九儿人物主题曲');
INSERT INTO `song` VALUES ('20210601204949', '长恨歌', '王瑞淇', '《妖怪名单之苏九儿》电影召唤曲');
INSERT INTO `song` VALUES ('20210601205025', '河童', '王金金', '《妖怪名单之苏九儿》电影金·宣传曲');
INSERT INTO `song` VALUES ('20210601205214', '幼安', '阿敏', '《妖怪名单之苏九儿》电影概念先行曲');
INSERT INTO `song` VALUES ('20210601205256', '远行', '张迁', '《妖怪名单 第二季》动画ED');
INSERT INTO `song` VALUES ('20210601205521', '不败的英雄', '唐俊迪', '《铠甲勇士刑天》电视剧主题曲');
INSERT INTO `song` VALUES ('20210601205925', '光的战士', '陈致逸', '《铠甲勇士》电视剧第52集插曲');
INSERT INTO `song` VALUES ('20210601205953', '穿越曙光', '唐丹', '《铠甲勇士刑天》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210601210159', '生生世世爱', '吴雨霏', '《仙剑奇侠传3》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210601210221', '忘记时间', '胡歌', '《仙剑奇侠传3》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210601210243', '偏爱', '张芸京', '《仙剑奇侠传3》电视剧插曲');
INSERT INTO `song` VALUES ('20210601210312', '六月的雨', '胡歌', '《仙剑奇侠传》电视剧插曲');
INSERT INTO `song` VALUES ('20210601210351', '一直很安静', '阿桑', '《仙剑奇侠传》电视剧插曲');
INSERT INTO `song` VALUES ('20210601210448', '终于明白', '动力火车', '《仙剑奇侠传》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210601210603', '仙剑问情', '萧人凤', '《仙剑奇侠传3外传·问情篇》游戏主题曲');
INSERT INTO `song` VALUES ('20210601210626', '花与剑', 'JS', '《仙剑奇侠传》电视剧插曲');
INSERT INTO `song` VALUES ('20210601210727', '千年缘', '心然', '《仙剑奇侠传4》游戏非官方同人曲');
INSERT INTO `song` VALUES ('20210601210758', '千年泪', '董贞', '');
INSERT INTO `song` VALUES ('20210601210846', '情醉', '董贞、盛威', '');
INSERT INTO `song` VALUES ('20210601211014', '繁花', '董真', '《三生三世十里桃花》电视剧原声带');
INSERT INTO `song` VALUES ('20210601211100', '流恋', '吴奇隆、严艺丹', '《新白发魔女传》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210601211306', '梦回还', '呦猫UNEKO', '《狐妖小红娘 王权篇》动画OP');
INSERT INTO `song` VALUES ('20210601211409', '若当来世', '冥月、Mario', '《狐妖小红娘 月红篇》动画主题曲');
INSERT INTO `song` VALUES ('20210601211619', '人间白首', '呦猫UNEKO', '《狐妖小红娘 竹业篇》动画插曲');
INSERT INTO `song` VALUES ('20210601211706', '落空', '落萱', '《狐妖小红娘 金晨曦篇》动画OP');
INSERT INTO `song` VALUES ('20210601211802', '此彼绘卷', '林和夜', '《狐妖小红娘王权篇》动画第20、27集插曲');
INSERT INTO `song` VALUES ('20210601211835', '铭记', '呦猫UNEKO', '《狐妖小红娘 千颜篇》动画OP');
INSERT INTO `song` VALUES ('20210601211942', '东流', '绯村柯北、灰老板', '《狐妖小红娘 下沙篇》动画ED');
INSERT INTO `song` VALUES ('20210601212221', '金色', 'Kinoko蘑菇', '《狐妖小红娘 沐天城篇》动画OP');
INSERT INTO `song` VALUES ('20210601212308', '满庭芳', 'Mr.mo', '《狐妖小红娘 竹业篇》动画OP');
INSERT INTO `song` VALUES ('20210601212338', '愿我', '呦猫UNEKO', '《狐妖小红娘 南国篇》动画插曲');
INSERT INTO `song` VALUES ('20210601212429', '我还记得', 'Shymie', '《狐妖小红娘 尾生篇》动画OP');
INSERT INTO `song` VALUES ('20210601212522', '铃舟', '匀子', '《狐妖小红娘 月红篇》动画第16集插曲');
INSERT INTO `song` VALUES ('20210601212551', '寄', 'JMJ', '《狐妖小红娘 竹业篇》动画ED');
INSERT INTO `song` VALUES ('20210601212655', '相聚万年树', '林和夜', '《狐妖小红娘 下沙篇》动画第6集插曲');
INSERT INTO `song` VALUES ('20210601212808', '相思树下', 'YNJ谢悦', '《狐妖小红娘》动画主题曲');
INSERT INTO `song` VALUES ('20210601213110', '万水依山', '叫ぶ獣', '《狐妖小红娘 月红篇》动画主题曲');
INSERT INTO `song` VALUES ('20210601213214', '下沙', '桂子油', '《狐妖小红娘 下沙篇》动画插曲');
INSERT INTO `song` VALUES ('20210601213316', '不易不移', 'Kinoko蘑菇', '《狐妖小红娘 金晨曦篇》动画ED');
INSERT INTO `song` VALUES ('20210601213446', '雪年轮', '苏尚卿', '《狐妖小红娘 千颜篇》动画第62集插曲');
INSERT INTO `song` VALUES ('20210601213520', '君路', '大酥', '《狐妖小红娘 北山妖帝篇》动画ED');
INSERT INTO `song` VALUES ('20210601213602', '岩心', '大帝', '《狐妖小红娘 月红篇》动画第46集插曲');
INSERT INTO `song` VALUES ('20210601213716', '未断', '陈爽朗、王志毅', '《狐妖小红娘 沐天城篇》动画ED');
INSERT INTO `song` VALUES ('20210601213829', '时之风', '方晓东', '《狐妖小红娘 尾生篇》动画概念主题曲');
INSERT INTO `song` VALUES ('20210601213923', '不忘', '张恋歌', '《狐妖小红娘 北山妖帝篇》动画第45集插曲');
INSERT INTO `song` VALUES ('20210601214021', '围城', 'Kinoko蘑菇、Mr.mo', '《狐妖小红娘 南国篇》动画插曲');
INSERT INTO `song` VALUES ('20210601214105', '盘根', '白止', '《狐妖小红娘 千颜篇》动画插曲');
INSERT INTO `song` VALUES ('20210601214150', '竹亭', '南偿', '《狐妖小红娘 竹业篇》动画插曲');
INSERT INTO `song` VALUES ('20210601214317', '瞳染', '蓮莉', '《狐妖小红娘 砂雪篇》动画OP');
INSERT INTO `song` VALUES ('20210601214424', '绝处风雪', '无问、D.rui、池年', '《狐妖小红娘 月红篇》动画插曲');
INSERT INTO `song` VALUES ('20210601214549', '刻印', '朱梓溶', '《狐妖小红娘 千颜篇》动画ED');
INSERT INTO `song` VALUES ('20210601214630', '雁归辞', '依平、无欢', '《狐妖小红娘》动画插曲');
INSERT INTO `song` VALUES ('20210601214724', '不醉', '余七趁', '《狐妖小红娘》动画插曲');
INSERT INTO `song` VALUES ('20210601214949', '思如雪', '董贞', '《画江湖之不良人3》动画第21集插曲');
INSERT INTO `song` VALUES ('20210601215118', '爱你让我像孩子一样', '俞灏明', '《因为爱情有奇迹》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210601215206', '忐忑的距离', '萌学园', '《萌学园4 时空战役》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210601215336', '月光', '胡彦斌', '《秦时明月·百步飞剑》动画主题曲');
INSERT INTO `song` VALUES ('20210601215451', '枫林残忆', '伍华、余文靖', '');
INSERT INTO `song` VALUES ('20210602181846', '远方', '郁可唯', '《古剑奇谭》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210602181910', '剑心', '张杰', '《古剑奇谭》电视剧插曲');
INSERT INTO `song` VALUES ('20210602181950', '剑伤', '李易峰', '《古剑奇谭》电视剧插曲');
INSERT INTO `song` VALUES ('20210602182019', '恋人歌歌', '胡彦斌', '');
INSERT INTO `song` VALUES ('20210602182119', '爱你没错', '张信哲', '');
INSERT INTO `song` VALUES ('20210602182633', '双骄', '金志文', '《绝代双骄 2020版》电视剧主题曲');
INSERT INTO `song` VALUES ('20210602182658', '拆心', '刘惜君', '《绝代双骄 2020版》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210602182822', '红尘不悔', '陆虎', '《绝代双骄 2020版》电视剧插曲');
INSERT INTO `song` VALUES ('20210602183035', '翅膀', '林俊杰', '');
INSERT INTO `song` VALUES ('20210602183228', '独白', '周深', '《天乩之白蛇传说》网络剧插曲');
INSERT INTO `song` VALUES ('20210602183255', '幸福在梦中', '赵艺', '《大话西游之爱你一万年》网络剧紫霞仙子角色曲');
INSERT INTO `song` VALUES ('20210602183412', '凉凉', '杨宗纬、张碧晨', '《三生三世十里桃花》电视剧插曲');
INSERT INTO `song` VALUES ('20210602183449', '爱河', '神马乐园', '');
INSERT INTO `song` VALUES ('20210602183752', '为爱追寻', '朱倩汐JING', '《梦幻西游2》游戏主题曲');
INSERT INTO `song` VALUES ('20210602183831', '恋西游', 'TFBOYS', '《梦幻西游2：化境飞升》动画主题曲');
INSERT INTO `song` VALUES ('20210602183910', '入梦', 'SNH48', '《梦幻西游3：雷怒危机》动画主题曲');
INSERT INTO `song` VALUES ('20210606174210', '留香', '吴奇隆', '《新白发魔女传》电视剧片尾曲');
INSERT INTO `song` VALUES ('20210606174229', '无常', '吴奇隆', '《新白发魔女传》电视剧插曲');
INSERT INTO `song` VALUES ('20210606174317', '无·果', '严艺丹', '《新白发魔女传》电视剧插曲');
INSERT INTO `song` VALUES ('20210606175139', '十年', '陈奕迅', '');
INSERT INTO `song` VALUES ('20210606175219', '十一年', '邱永传', '');

二、创建项目

在Intellj IDEA中创建一个springboot项目springboot-jdbctemplate

三、添加依赖

在pom.xml文件中添加maven依赖

<?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>cn.edu.sgu.www</groupId>
    <artifactId>springboot-jdbctemplate</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-jdbctemplate</name>
    <description>Spring Boot整合JdbcTemplate案例项目</description>

    <properties>
        <java.version>1.8</java.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>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

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

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

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

四、创建配置类

在项目根包下创建配置类config.JdbcTemplateConfig，配置数据源和事务管理器。

package cn.edu.sgu.www.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(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        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/jdbc?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai");

        return dataSource;
    }

}

五、使用jdbctemplate

entity实体类

在项目根包下创建实体类entity.Song，并生成getter、setter方法。

package cn.edu.sgu.www.jdbctemplate.entity;

import lombok.Data;

import java.io.Serializable;

/**
 * @author heyunlin
 * @version 1.0
 */
@Data
public class Song implements Serializable {
    private static final long serialVersionUID = 18L;

    private String id;

    /**
     * 歌曲名
     */
    private String name;

    /**
     * 歌曲信息
     */
    private String note;

    /**
     * 歌手
     */
    private String singer;
}

service层

在项目根包下创建service包，然后在service包下创建一个SongService接口，定义一个分页查询的方法selectByPage()。

SongService.java

package cn.edu.sgu.www.jdbctemplate.service;

import cn.edu.sgu.www.jdbctemplate.entity.Song;

import java.util.List;

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

    /**
     * 分页查询歌曲列表
     * @param pageNum 页数
     * @param pageSize 每页的记录数
     * @return List<Song> 查询结果
     */
    List<Song> selectByPage(Integer pageNum, Integer pageSize);
}

SongServiceImpl.java

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

package cn.edu.sgu.www.jdbctemplate.service.impl;

import cn.edu.sgu.www.jdbctemplate.entity.Song;
import cn.edu.sgu.www.jdbctemplate.service.SongService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @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 List<Song> selectByPage(Integer pageNum, Integer pageSize) {
        String sql = "select * from song limit " + (pageNum - 1) * pageSize + ", " + pageSize;

        return jdbcTemplate.query(sql, new RowMapper<Song>() {
            @Override
            public Song mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                Song song = new Song();

                song.setId(resultSet.getString("id"));
                song.setName(resultSet.getString("name"));
                song.setNote(resultSet.getString("note"));
                song.setSinger(resultSet.getString("singer"));

                return song;
            }
        });
    }

}

controller层

在项目根包下创建控制器类类controller.SongController

package cn.edu.sgu.www.jdbctemplate.controller;

import cn.edu.sgu.www.jdbctemplate.entity.Song;
import cn.edu.sgu.www.jdbctemplate.service.SongService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author heyunlin
 * @version 1.0
 */
@RestController
@RequestMapping(path = "/song", produces = "application/json;charset=utf-8")
public class SongController {

    private final SongService songService;

    @Autowired
    public SongController(SongService songService) {
        this.songService = songService;
    }

    @RequestMapping(value = "/selectByPage", method = RequestMethod.GET)
    public List<Song> selectByPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize) {
        return songService.selectByPage(pageNum, pageSize);
    }

}

启动项目，在浏览器地址栏输入以下网址并回车

http://localhost:8080/song/selectByPage?pageNum=1&pageSize=10

六、jdbctemplate方法介绍

jdbctemplate里有很多方法，这个部分简单介绍一下其中execute()方法的使用。

jdbctemplate中定义了3个重载的execute()方法，下面是这三个重载的方法的使用，很显然，后两个execute()方法给我们提供了一个回调方法，根据参数的类型以及使用的案例代码，ConnectionCallback和StatementCallback分别给我们提供了JDBC的Connection和Statement对象。

package cn.edu.sgu.www.jdbctemplate;

import cn.edu.sgu.www.jdbctemplate.entity.Song;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.StatementCallback;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author heyunlin
 * @version 1.0
 */
@SpringBootTest
class JdbcTemplateTests {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void testExecute() {
        jdbcTemplate.execute("create table if not exists song");

        List<Song> list = jdbcTemplate.execute(new ConnectionCallback<List<Song>>() {
            @Override
            public List<Song> doInConnection(Connection connection) throws SQLException, DataAccessException {
                List<Song> list = new ArrayList<>();

                try {
                    connection.setAutoCommit(false); // 关闭事务自动提交

                    PreparedStatement statement = connection.prepareStatement("select * from song limit 5");

                    ResultSet resultSet = statement.executeQuery();

                    while (resultSet.next()) {
                        // 封装结果集
                        Song song = new Song();

                        song.setId(resultSet.getString("id"));
                        song.setName(resultSet.getString("name"));
                        song.setNote(resultSet.getString("note"));
                        song.setSinger(resultSet.getString("singer"));

                        list.add(song);
                    }

                    connection.commit(); // DML需要手动提交事务
                } catch (Exception e) {
                    e.printStackTrace();

                    connection.rollback(); // 发生异常，回滚事务
                } finally {
                    connection.close();
                }

                return list;
            }
        });

        System.out.println(list);

        Song song = jdbcTemplate.execute(new StatementCallback<Song>() {
            @Override
            public Song doInStatement(Statement statement) throws SQLException, DataAccessException {
                ResultSet resultSet = statement.executeQuery("select * from song limit 1");

                if (resultSet.next()) {
                    // 封装结果集
                    Song song = new Song();

                    song.setId(resultSet.getString("id"));
                    song.setName(resultSet.getString("name"));
                    song.setNote(resultSet.getString("note"));
                    song.setSinger(resultSet.getString("singer"));

                    return song;
                }

                return null;
            }
        });

        System.out.println(song);
    }

}

其实看到这里，jdbcTemplate的方法已经没有必要继续学习了，这就是传统的JDBC操作数据库的代码，还是推荐使用mybatis持久层框架。

文章涉及代码已经上传到了gitee，可按需获取~

Spring Boot整合JdbcTemplate案例项目https://gitee.com/muyu-chengfeng/springboot-jdbctemplate.git