1、什么是JdbcTemplate
Spring框架对JDBC进行封装,使用JdbcTemplate,方便实现对数据库操作
2、使用前的配置
(1)导入相应的jar包
(2)、配置xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
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">
<!--开启注解扫描-->
<context:component-scan base-package="com.gongda.Spring5.JDBCTemplate"></context:component-scan>
<!--数据库连接池-->
<!--destroy-method="close",当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用.-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql:///test"></property>
<property name="username" value="root"></property>
<property name="password" value="password"></property>
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
</bean>
<!--配置JdbcTemplate,注入DataSource-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
(3)创建Book、Service、Dao等类并添加相应注解
(3)、然后就是编写代码
BookService
@Service
public class BookService {
//注入dao
@Autowired
private BookDao bookDao;
//添加
public void addBook(Book book){
bookDao.add(book);
}
//修改
public void updateBook(Book book){
bookDao.update(book);
}
//删除
public void deleteBook(int book_id){
bookDao.delete(book_id);
}
//返回所有记录数
public void findCount(){
bookDao.selectCount();
}
//返回某一记录
public void findOne(int book_id) {
Book bookInfo = bookDao.findBookInfo(book_id);
System.out.println(bookInfo);
}
//返回所有记录
public void findAll() {
List<Book> allBook = bookDao.findAllBook();
System.out.println(allBook);
}
//批量添加记录
public void batchAddBooks(List<Object[]> books){
bookDao.batchAllBook(books);
}
//批量修改
public void batchUpdate(List<Object[]> books){
bookDao.batchUpdateBook(books);
}
//批量删除
public void batchDelete(List<Object[]> book_id){
bookDao.batchDeleteBook(book_id);
}
}
BookDao
public interface BookDao {
//添加
public void add(Book book);
//修改
public void update(Book book);
//删除
public void delete(int book_id);
public void selectCount();
Book findBookInfo(int book_id);
List<Book> findAllBook();
void batchAllBook(List<Object[]> books);
void batchUpdateBook(List<Object[]> books);
void batchDeleteBook(List<Object[]> book_id);
}
BookDapImpl
@Repository
public class BookDaoImpl implements BookDao {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(Book book) {
//添加
String sql="insert into book values(?,?,?)";
Object args[]={book.getBook_id(),book.getBook_name(),book.getBook_value()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
public void update(Book book){
//修改
String sql="update book set book_name=?,book_value=? where book_id=?";
Object args[]={book.getBook_name(),book.getBook_value(),book.getBook_id()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
@Override
public void delete(int book_id) {
//删除
String sql="delete from book where book_id=?";
Object args[]={book_id};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
@Override
public void selectCount() {
//查询所有记录数
String sql="select count(*) from book";
//弟一个参数: sql语句
//第二个参数:返回类型Class
Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(integer);
}
@Override
public Book findBookInfo(int book_id) {
//查询返回某一对象
String sql="select * from book where book_id=? ";
Object args[]={book_id};
//第一个参数:sql语句
//第二个参数:RowMapper,是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
//第三个参数: sql语句值
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), args);
return book;
}
@Override
public List<Book> findAllBook() {
//查询所有记录
String sql="select * from book";
//第一个参数:sql语句
//第二个参数:RowMapper,是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
//第三个参数: sql语句值
List<Book> books = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return books;
}
@Override
public void batchAllBook(List<Object[]> books) {
//批量添加记录
String sql="insert into book values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, books);
System.out.println(Arrays.toString(ints));
}
@Override
public void batchUpdateBook(List<Object[]> books) {
//批量修改记录
String sql="update book set book_name=?,book_value=? where book_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, books);
System.out.println(Arrays.toString(ints));
}
@Override
public void batchDeleteBook(List<Object[]> book_id) {
String sql="delete from book where book_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, book_id);
System.out.println(Arrays.toString(ints));
}
}
TestJdbcTemplate
public class TestJdbcTemplate {
@Test
public void testJdbcTemplate(){
ApplicationContext context=new ClassPathXmlApplicationContext("bean2.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//添加
/* Book book=new Book();
book.setBook_id(1);
book.setBook_name("java框架");
book.setBook_value(20);
bookService.addBook(book);*/
//修改
/*Book book=new Book();
book.setBook_id(1);
book.setBook_name("java框架");
book.setBook_value(30);
bookService.updateBook(book);*/
//删除
/*bookService.deleteBook(1);*/
//查询所有记录数
/*bookService.findCount();*/
//查询返回对象
/*bookService.findOne(2);*/
//查询返回所有对象
/*bookService.findAll();*/
//批量插入多条数据
/* List<Object[]> books=new ArrayList<>();
Object[] o1={3,"ccc", 33};
Object[] o2={4,"ddd",44};
Object[] o3={5,"eee",55};
books.add(o1);
books.add(o2);
books.add(o3);
bookService.batchAddBooks(books);*/
//批量修改多条记录
/*List<Object[]> book_list=new ArrayList<>();
Object[] o1={"ccc1", 33,3};
Object[] o2={"ddd2",44,4};
Object[] o3={"eee3",55,5};
book_list.add(o1);
book_list.add(o2);
book_list.add(o3);
bookService.batchUpdate(book_list);*/
//批量删除多条记录
List<Object[]> list=new ArrayList<>();
Object[] o1={3};
Object[] o2={4};
list.add(o1);
list.add(o2);
bookService.batchDelete(list);
}
}