目录
3. 配置JDBCTemplate对象,注入DataSource
4.创建service类,创建dao类,在dao注入JDBCTemplate对象
一、概念
Spring框架对JDBC进行封装,使用JDBCTemplate方便实现对数据库的操作
二、使用
(一)准备工作
1.引入相关jar包
2.在spring配置文件中配置数据库连接池
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/user_db?characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="123456" />
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
</bean>
3. 配置JDBCTemplate对象,注入DataSource
<!-- JDBCTemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 注入dataSource -->
<property name="dataSource" ref="dataSource"></property>
</bean>
4.创建service类,创建dao类,在dao注入JDBCTemplate对象
①在配置文件中开启组件扫描
<!-- 开启组件扫描 -->
<context:component-scan base-package="com.atguigu"></context:component-scan>
②Service
import com.atguigu.spring5.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class BookService {
//注入dao
@Autowired
private BookDao bookDao;
}
③dao
public interface BookDao {
}
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.atguigu.spring5.entity;
public class Book {
private String user_id;
private String user_name;
private String user_status;
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getUser_status() {
return user_status;
}
public void setUser_status(String user_status) {
this.user_status = user_status;
}
}
2.编写service和dao
(1)在dao进行数据库添加操作
(2)调用JDBCTemplate对象里面的update方法实现添加操作
- 两个参数:
- String sql:sql语句
- Object... args:可变参数,设置sql语句中的值
import com.atguigu.spring5.dao.BookDao;
import com.atguigu.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){
bookDao.add(book);
}
}
public interface BookDao {
//添加的方法
void add(Book book);
}
import com.atguigu.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 void add(Book book) {
//1.创建sql语句
String sql = "insert into t_book values(?,?,?)";
//2.调用方法实现
int update = jdbcTemplate.update(sql, book.getUser_id(), book.getUser_name(), book.getUser_status());
System.out.println(update);//输出影响行数
}
}
(3)测试
import com.atguigu.spring5.entity.Book;
import com.atguigu.spring5.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class testBook {
@Test
public void test1(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setUser_id("1");
book.setUser_name("java");
book.setUser_status("20");
bookService.addBook(book);
}
}
(三)JDBCTemplate操作数据库(修改&删除功能)
BookService类
package com.atguigu.spring5.service;
import com.atguigu.spring5.dao.BookDao;
import com.atguigu.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){
bookDao.add(book);
}
//修改
public void updateBook(Book book){
bookDao.updateBook(book);
}
//删除
public void deleteBook(String id){
bookDao.deleteBook(id);
}
}
BookDao
package com.atguigu.spring5.dao;
import com.atguigu.spring5.entity.Book;
public interface BookDao {
//添加的方法
void add(Book book);
//修改的方法
void updateBook(Book book);
//删除的方法
void deleteBook(String id);
}
BookDaoImpl
package com.atguigu.spring5.dao;
import com.atguigu.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 void updateBook(Book book) {
String sql = "update t_book set user_name=?,user_status=? where id=?";
int update = jdbcTemplate.update(sql,book.getUser_name(), book.getUser_status(),book.getUser_id());
System.out.println(update);
}
//删除
@Override
public void deleteBook(String id) {
String sql = "delete from t_book where user_id=?";
int update = jdbcTemplate.update(sql, id);
System.out.println(update);
}
}
(四)JDBCTemplate操作数据库(查询返回某个值)
1.查询表里有多少条记录,返回某个值
#查询表里有多少条记录
SELECT COUNT(*) FROM t_book;
2. 使用JDBCTemplate
//查询表中记录数
public int findCount(){
return bookDao.selectCount();
}
- String sql:sql语句
- Class<T> requiredType:返回类型的Class
方法实现
//查询表中记录数
@Override
public int selectCount() {
String sql = "select count(*) from t_book";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
测试
@Test
public void test2(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//查询
int count = bookService.findCount();
System.out.println(count);
}
(五)JDBCTemplate操作数据库(查询返回对象)
1.场景:查询图书详情
根据id查询图书详细信息
2.JDBCTemplate实现
//查询返回对象
public Book findOne(String id)
{
return bookDao.findBookInfo(id);
}
- String sql:sql语句
- RowMapper<T> rowmapper:是接口,返回不同类型的数据,使用这个接口里的实现类完成数据封装
- Object...args: sql语句中问号的值
//查询返回对象
@Override
public Book findBookInfo(String id) {
String sql = "select * from t_book where user_id=?";
//调用方法
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
测试
@Test
public void test3(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//查询返回对象
Book book = bookService.findOne("1");
System.out.println(book);
}
(六)JDBCTemplate操作数据库(查询返回集合)
场景:查询图书列表分页
//JDBCTemplate操作数据库(查询返回集合)
public List<Book> findAll(){
return bookDao.finAllBook();
}
- String sql:sql语句
- RowMapper<T> rowmapper:是接口,返回不同类型的数据,使用这个接口里的实现类完成数据封装
- Object...args: sql语句中问号的值
//JDBCTemplate操作数据库(查询返回集合)
@Override
public List<Book> finAllBook() {
String sql = "select * from t_book";
//调用方法
List<Book> book = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return book;
}
测试
@Test
public void test4(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//查询返回集合
List<Book> book = bookService.findAll();
System.out.println(book);
}
(七)JDBCTemplate操作数据库(批量操作)
- String sql:sql语句
- List<Object[]> batchArgs:List集合,表示添加多条记录的数据
1.批量添加
service:
//批量添加
public void batchAdd(List<Object[]> batchArgs){
bookDao.batchAddBook(batchArgs);
}
daoImpl:
//批量添加
@Override
public void batchAddBook(List<Object[]> batchArgs) {
String sql = "insert into t_book values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试:
//批量添加
@Test
public void test5(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"2","数据结构",30};
Object[] o2 = {"3","概率论","Y"};
Object[] o3 = {"4","JVM虚拟机","6"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
//调用批量添加
bookService.batchAdd(batchArgs);
}
2.批量修改
service:
//批量修改
public void batchUpdate(List<Object[]> batchArgs){
bookDao.batchUpdateBook(batchArgs);
}
daoImpl:
//批量修改
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
String sql = "update t_book set user_name=?,user_status=? where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(ints);
}
测试:
//批量修改
@Test
public void test6(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"数据结构与算法","30","2"};
Object[] o2 = {"概率论与数理统计","Y","3"};
Object[] o3 = {"JVM虚拟机(一)","6","4"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
//调用方法批量修改
bookService.batchUpdate(batchArgs);
}