Spring5入门到实战------12、使用JdbcTemplate操作数据库（增删改查）。具体代码+讲解 【上篇】
1、什么是 JdbcTemplate
Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作
2、前提准备
2.1 在项目中引入对应的jar包
官网下载jar包、然后加入的项目中（不知如何将jar包导入、可在我博客搜索查看、此处不在赘述）
2.2 在xml文件配置数据库连接池
友情提示：注意数据库的url以及登录名和账号。这里可以读取外部文件进行配置、前几篇博客有介绍使用。
<!-- 数据库连接池 --><bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"destroy-method="close"><property name="url" value="jdbc:mysql://localhost:3306/user_db" /><property name="username" value="root" /><property name="password" value="root" /><property name="driverClassName" value="com.mysql.jdbc.Driver" /></bean>
2.3 配置 JdbcTemplate 对象，注入 DataSource
<!--jdbcTemplate对象--><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><!--注入dataSource--><property name="dataSource" ref="dataSource"></property></bean>
2.4 创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象
这里使用注解开发
<!--开启组件扫描--><context:component-scan base-package="com.zyz"></context:component-scan>
/*** @author Lenovo* @version 1.0* @data 2022/10/23 15:53*/
@Service
public class BookService {/*** 注入dao*/@Autowiredprivate BookDao bookDao;}
/*** @author Lenovo* @version 1.0* @data 2022/10/23 15:53*/
@Repository
public class BookDaoImpl implements BookDao{/*** 注入jdbcTemplate*/@Autowiredprivate JdbcTemplate jdbcTemplate;}
2.5 数据库设计
2.6 创建实体类
package com.zyz.spring5.entity;/*** @author Lenovo* @version 1.0* @data 2022/10/23 16:10*/
public class Book {private int book_id;private String book_name;private String isSale;public int getBook_id() {return book_id;}public void setBook_id(int book_id) {this.book_id = book_id;}public String getBook_name() {return book_name;}public void setBook_name(String book_name) {this.book_name = book_name;}public String getIsSale() {return isSale;}public void setIsSale(String isSale) {this.isSale = isSale;}@Overridepublic String toString() {return "Book{" +"book_id=" + book_id +", book_name='" + book_name + '\'' +", isSale='" + isSale + '\'' +'}';}
}3、项目中的实际案例
3.1 项目结构
3.2 jdbcTemplate操作数据库 （添加操作）
这里只给出核心部分。具体代码在文章末尾给出。基本思路，dao中定义接口、daoImpl用来实现接口。service中进行调用
（1）在 dao 进行数据库添加操作
（2）调用 JdbcTemplate 对象里面 update 方法实现添加操作
@Overridepublic void addBook(Book book) {//1、创建sql语句String sql = "insert into t_book values(?,?,?)";//2、调用方法Object [] args ={book.getBook_id(),book.getBook_name(),book.getIsSale()};int update = jdbcTemplate.update(sql,args);System.out.println(update);}
测试
@Testpublic void testJdbcTemplate(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);Book book = new Book();book.setBook_id(1);book.setBook_name("Java");book.setIsSale("出售");bookService.addBook(book);}
测试结果
3.2 修改操作
核心部分
这里sql的更新顺序要和参数的顺序保持一致
@Overridepublic void updateBook(Book book) {//1、创建sql语句String sql = "update t_book set book_name=?,isSale=? where book_id=?";//2、调用方法Object [] args ={book.getBook_name(),book.getIsSale(),book.getBook_id()};int update = jdbcTemplate.update(sql,args);System.out.println(update);}
测试
@Testpublic void testJdbcTemplate1(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);Book book = new Book();book.setBook_id(1);book.setBook_name("数据结构");book.setIsSale("不出售");bookService.updateBook(book);}
测试结果
3.3 删除操作
核心部分
@Overridepublic void deleteBook(int id) {//1、创建sql语句String sql = "delete from t_book where book_id=?";//2、调用方法int update = jdbcTemplate.update(sql,id);System.out.println(update);}
测试
@Testpublic void testJdbcTemplate2(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);Book book = new Book();book.setBook_id(1);bookService.deleteBook(book.getBook_id());}
测试结果
3.4 查询操作（查询返回某个值）
1、查询表里面有多少条记录，返回是某个值
2、使用 JdbcTemplate 实现查询返回某个值代码
核心
第一个参数：sql 语句
第二个参数：返回类型 Class
@Overridepublic int selectCount() {String sql ="select count(*) from t_book";Integer count = jdbcTemplate.queryForObject(sql,Integer.class);return count;}
测试
@Testpublic void testJdbcTemplate3(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);System.out.println(bookService.selectCount());}
测试结果
3.5 测试返回查询对象
第一个参数：sql 语句
第二个参数：RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成
数据封装
第三个参数：sql 语句值
核心
@Overridepublic Book selectOneBook(int id) {String sql = "select * from t_book where book_id = ?";Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);return book;}
测试
@Testpublic void testJdbcTemplate3(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);System.out.println(bookService.selectBookOne(1));}
测试结果
3.6 查询返回集合
第一个参数：sql 语句
第二个参数：RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成
数据封装
第三个参数：sql 语句值
@Overridepublic List<Book> selectAllBook() {String sql = "select * from t_book";List<Book> bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));return bookList;}
测试
@Testpublic void testJdbcTemplate3(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);System.out.println(bookService.selectBookAll());}
测试结果
4、完整的代码
4.1 BookDao.java
/*** @author Lenovo* @version 1.0* @data 2022/10/23 15:53*/
public interface BookDao {/*** 添加* @param book*/void addBook(Book book);/*** 修改* @param book*/void updateBook(Book book);/*** 删除* @param id*/void deleteBook(int id);/*** 查询表记录数*/int selectCount();/*** 查询对象*/Book selectOneBook(int id);/*** 查询全部对象*/List<Book> selectAllBook();}4.2 BookDaoImpl.java
/*** @author Lenovo* @version 1.0* @data 2022/10/23 15:53*/
@Repository
public class BookDaoImpl implements BookDao{/*** 注入jdbcTemplate*/@Autowiredprivate JdbcTemplate jdbcTemplate;@Overridepublic void addBook(Book book) {//1、创建sql语句String sql = "insert into t_book values(?,?,?)";//2、调用方法Object [] args ={book.getBook_id(),book.getBook_name(),book.getIsSale()};int update = jdbcTemplate.update(sql,args);System.out.println(update);}@Overridepublic void updateBook(Book book) {//1、创建sql语句String sql = "update t_book set book_name=?,isSale=? where book_id=?";//2、调用方法Object [] args ={book.getBook_name(),book.getIsSale(),book.getBook_id()};int update = jdbcTemplate.update(sql,args);System.out.println(update);}@Overridepublic void deleteBook(int id) {//1、创建sql语句String sql = "delete from t_book where book_id=?";//2、调用方法int update = jdbcTemplate.update(sql,id);System.out.println(update);}@Overridepublic int selectCount() {String sql ="select count(*) from t_book";Integer count = jdbcTemplate.queryForObject(sql,Integer.class);return count;}@Overridepublic Book selectOneBook(int id) {String sql = "select * from t_book where book_id = ?";Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);return book;}@Overridepublic List<Book> selectAllBook() {String sql = "select * from t_book";List<Book> bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));return bookList;}}4.3 BookService.java
/*** @author Lenovo* @version 1.0* @data 2022/10/23 15:53*/
@Service
public class BookService {/*** 注入dao*/@Autowiredprivate BookDao bookDao;/*** 添加书* @param book*/public void addBook(Book book){bookDao.addBook(book);}/*** 修改书籍* @param book*/public void updateBook(Book book){bookDao.updateBook(book);}/*** 删除书籍* @param id*/public void deleteBook(int id){bookDao.deleteBook(id);}/*** 查询* @return*/public int selectCount(){return bookDao.selectCount();}/*** 查询对象* @return*/public Book selectBookOne(int id){return bookDao.selectOneBook(id);}/*** 查询全部对象* @return*/public List<Book> selectBookAll(){return bookDao.selectAllBook();}}4.4 bean.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xmlns:aop="http://www.springframework.org/schema/aop"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"><!--开启组件扫描--><context:component-scan base-package="com.zyz"></context:component-scan><!-- 数据库连接池 --><bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"destroy-method="close"><property name="url" value="jdbc:mysql://localhost:3306/user_db" /><property name="username" value="root" /><property name="password" value="root" /><property name="driverClassName" value="com.mysql.jdbc.Driver" /></bean><!--jdbcTemplate对象--><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><!--注入dataSource--><property name="dataSource" ref="dataSource"></property></bean></beans>
4.5 测试
/*** @author Lenovo* @version 1.0* @data 2022/10/23 16:26*/
public class TestDemo {@Testpublic void testJdbcTemplate(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);Book book = new Book();book.setBook_id(2);book.setBook_name("C语言");book.setIsSale("出售");bookService.addBook(book);}@Testpublic void testJdbcTemplate1(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);Book book = new Book();book.setBook_id(1);book.setBook_name("数据结构");book.setIsSale("不出售");bookService.updateBook(book);}@Testpublic void testJdbcTemplate2(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);Book book = new Book();book.setBook_id(1);bookService.deleteBook(book.getBook_id());}@Testpublic void testJdbcTemplate3(){ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");BookService bookService = context.getBean("bookService", BookService.class);
// System.out.println(bookService.selectCount());
// System.out.println(bookService.selectBookOne(1));System.out.println(bookService.selectBookAll());}}5、后语
学无止境。下一篇写增删改的批量操作
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！