概述和准备工作
1、什么是 JdbcTemplate
Spring 框架对 JDBC 进行封装,使用 JdbcTemplate 方便实现对数据库操作
2、准备工作
(1)引入相关 jar 包
(2)在 spring 配置文件配置数据库连接池
1、在src
目录下创建jdbc.properties
prop.driverClass=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/user_db
prop.username=root
prop.password=123456
2、bean1.xml
中
<!--引入外部属性文件-->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--配置连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${prop.driverClass}"/>
<property name="url" value="${prop.url}"/>
<property name="username" value="${prop.username}"/>
<property name="password" value="${prop.password}"/>
</bean>
(3)配置 JdbcTemplate 对象,注入 DataSource
bean1.xml
<!--JdbcTemplate对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"/>
</bean>
(4)创建 service 类,创建 dao 类,在 dao 注入 jdbcTemplate 对象
1、开启组件扫描,bean1.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.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!--开启组件扫描-->
<context:component-scan base-package="com.spring5"/>
<!--引入外部属性文件-->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--配置连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${prop.driverClass}"/>
<property name="url" value="${prop.url}"/>
<property name="username" value="${prop.username}"/>
<property name="password" value="${prop.password}"/>
</bean>
<!--JdbcTemplate对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
2、编写service
层和dao
层
package com.spring5.service;
import com.spring5.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class BookService {
// 注入dao
@Autowired
private BookDao bookDao;
}
package com.spring5.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao {
// 注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
JdbcTemplate 操作数据库
添加
1、对应数据库创建实体类
package com.spring5.entity;
public class Book {
private int bid;
private String bname;
private String bstatus;
public Book() {
}
public Book(int bid, String bname, String bstatus) {
this.bid = bid;
this.bname = bname;
this.bstatus = bstatus;
}
public int getBid() {
return bid;
}
public String getBname() {
return bname;
}
public String getBstatus() {
return bstatus;
}
public void setBid(int bid) {
this.bid = bid;
}
public void setBname(String bname) {
this.bname = bname;
}
public void setBstatus(String bstatus) {
this.bstatus = bstatus;
}
}
2、编写 service 和 dao
(1)在 dao 进行数据库添加操作
(2)调用 JdbcTemplate 对象里面 update 方法实现添加操作
package com.spring5.dao;
import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao {
// 注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int add(Book book) {
// 创建sql语句
String sql = "insert into book values(?,?,?)";
int row = jdbcTemplate.update(sql, book.getBid(), book.getBname(), book.getBstatus());
return row;
}
}
package com.spring5.service;
import com.spring5.dao.BookDao;
import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class BookService {
// 注入dao
@Autowired
private BookDao bookDao;
//添加的方法
public void addBook(Book book) {
int row = bookDao.add(book);
System.out.println(row);
}
}
3、测试类
package com.spring5.test;
import com.spring5.entity.Book;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
bookService.addBook(new Book(101, "大主宰", "完结"));
}
}
4、运行结果
5、数据库查看
修改和删除
BookDaoImpl
类
package com.spring5.dao;
import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao {
// 注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int add(Book book) {
// 创建sql语句
String sql = "insert into book values(?,?,?)";
return jdbcTemplate.update(sql, book.getBid(), book.getBname(), book.getBstatus());
}
@Override
public int update(Book book) {
String sql = "update book set bname=?,bstatus=? where bid=?";
return jdbcTemplate.update(sql, book.getBname(), book.getBstatus(), book.getBid());
}
@Override
public int delete(int bid) {
String sql = "delete from book where bid=?";
return jdbcTemplate.update(sql, bid);
}
}
BookService
类
package com.spring5.service;
import com.spring5.dao.BookDao;
import com.spring5.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class BookService {
// 注入dao
@Autowired
private BookDao bookDao;
//添加的方法
public void addBook(Book book) {
int row = bookDao.add(book);
System.out.println(row);
}
//修改的方法
public void updateBook(Book book) {
int row = bookDao.update(book);
System.out.println(row);
}
//删除的方法
public void deleteBook(int bid) {
int row = bookDao.delete(bid);
System.out.println(row);
}
}
测试
修改
package com.spring5.test;
import com.spring5.entity.Book;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//bookService.addBook(new Book(101, "大主宰", "完结"));
bookService.updateBook(new Book(101, "圣墟", "连载"));
}
}
删除
package com.spring5.test;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//bookService.addBook(new Book(101, "大主宰", "完结"));
//bookService.updateBook(new Book(101, "圣墟", "连载"));
bookService.deleteBook(101);
}
}
查询
查询返回某个值
第一个参数:sql 语句;第二个参数:返回类型 Class
第一个参数:sql 语句;第二个参数:sql 语句?参数值;第三个参数:返回类型 Class
往BookDao
接口新增一个int count();
方法,在BookDaoImpl
中重写该方法:
@Override
public int count() {
String sql = "select count(*) from book";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
若带参数,修改如下;
String[] args = {...}; return jdbcTemplate.queryForObject(sql, args, Integer.class);
在BookService
新增方法:
//查询表中记录数
public void findCount() {
int count = bookDao.count();
System.out.println(count);
}
手动在数据库添加3条记录
修改测试类:
package com.spring5.test;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//bookService.addBook(new Book(101, "大主宰", "完结"));
//bookService.updateBook(new Book(101, "圣墟", "连载"));
//bookService.deleteBook(101);
bookService.findCount();
}
}
运行结果:
查询返回对象
第一个参数:sql 语句
第二个参数:RowMapper 是接口,针对返回不同类型数据,使用这个接口里面实现类完成 数据封装
第三个参数:sql 语句?参数值
往BookDao
接口新增一个Book findBookById(int bid);
方法,在BookDaoImpl
中重写该方法:
@Override
public Book findBookById(int bid) {
String sql = "select * from book where bid=?";
return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), bid);
}
在BookService
新增方法:
//查询返回对象
public void findBookbyId(int bid) {
Book book = bookDao.findBookById(bid);
System.out.println(book);
}
修改测试类:
package com.spring5.test;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//bookService.addBook(new Book(101, "大主宰", "完结"));
//bookService.updateBook(new Book(101, "圣墟", "连载"));
//bookService.deleteBook(101);
//bookService.findCount();
bookService.findBookbyId(101);
}
}
运行结果:
查询返回集合
往BookDao
接口新增一个List<Book> findAll();
方法,在BookDaoImpl
中重写该方法:
@Override
public List<Book> findAll() {
String sql = "select * from book";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
}
在BookService
新增方法:
//查询返回集合
public void findBookAll() {
List<Book> bookList = bookDao.findAll();
System.out.println(bookList);
}
修改测试类:
package com.spring5.test;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//bookService.addBook(new Book(101, "大主宰", "完结"));
//bookService.updateBook(new Book(101, "圣墟", "连载"));
//bookService.deleteBook(101);
//bookService.findCount();
//bookService.findBookbyId(101);
bookService.findBookAll();
}
}
运行结果:
批量操作
操作表里面多条记录
添加
第一个参数:sql 语句
第二个参数:List 集合,添加多条记录数据
往BookDao
接口新增一个int[] batchAdd(List<Object[]> list);
方法,在BookDaoImpl
中重写该方法:
@Override
public int[] batchAdd(List<Object[]> list) {
String sql = "insert into book values(?,?,?)";
return jdbcTemplate.batchUpdate(sql, list); // 返回影响行数
}
在BookService
新增方法:
//批量添加
public void batchAddBook(List<Object[]> list) {
int[] rows = bookDao.batchAdd(list);
System.out.println(Arrays.toString(rows));
}
修改测试类:
package com.spring5.test;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
// 批量添加
List<Object[]> list = new ArrayList<>();
Object[] o1 = {104, "完美世界", "完结"};
Object[] o2 = {105, "冰火魔厨", "完结"};
Object[] o3 = {106, "斗罗大陆", "完结"};
list.add(o1);
list.add(o2);
list.add(o3);
bookService.batchAddBook(list);
}
}
运行结果:
查看数据库:
修改
往BookDao
接口新增一个int[] batchUpdate(List<Object[]> list);
方法,在BookDaoImpl
中重写该方法:
@Override
public int[] batchUpdate(List<Object[]> list) {
String sql = "update book set bname=?,bstatus=? where bid=?";
return jdbcTemplate.batchUpdate(sql, list); // 返回影响行数
}
在BookService
新增方法:
//批量修改
public void batchUpdateBook(List<Object[]> list) {
int[] rows = bookDao.batchUpdate(list);
System.out.println(Arrays.toString(rows));
}
修改测试类:
package com.spring5.test;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
// 批量修改
List<Object[]> list = new ArrayList<>();
Object[] o1 = {"星门", "完结", 104}; // 根据sql语句参数位置来
Object[] o2 = {"超凡世界", "完结", 105};
Object[] o3 = {"遮天", "完结", 106};
list.add(o1);
list.add(o2);
list.add(o3);
bookService.batchUpdateBook(list);
}
}
运行结果:
查看数据库:
删除
往BookDao
接口新增一个int[] batchDelete(List<Object[]> list);
方法,在BookDaoImpl
中重写该方法:
@Override
public int[] batchDelete(List<Object[]> list) {
String sql = "delete from book where bid=?";
return jdbcTemplate.batchUpdate(sql, list); // 返回影响行数
}
在BookService
新增方法:
//批量删除
public void batchDeleteBook(List<Object[]> list) {
int[] rows = bookDao.batchDelete(list);
System.out.println(Arrays.toString(rows));
}
修改测试类:
package com.spring5.test;
import com.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestBook {
@Test
public void testJdbcTemplate() {
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
// 批量删除
List<Object[]> list = new ArrayList<>();
Object[] o1 = {104};
Object[] o2 = {105};
list.add(o1);
list.add(o2);
bookService.batchDeleteBook(list);
}
}