Spring之JDBC基本操作及代码实现

• 1. JDBC相关概念
• 2. 代码实现

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作

jdbc相关的jar包：

数据库中常见操作（以数据表为图书表为例）：

1. 添加一本书
2. 根据id修改某本已有书的信息
3. 根据id删除某本书
4. 查询表中记录总数
5. 查询某本书信息
6. 查询多本书信息
7. 批量增加
8. 批量修改
9. 批量删除

bean.xml配置：

BookService类：

package cn.edu.xd.service;

import cn.edu.xd.dao.BookDao;
import cn.edu.xd.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookService {
    @Autowired
    private BookDao bookDao;
    public void add(Book book){
        bookDao.add(book);
    }
    public void update(Book book){
        bookDao.update(book);
    }
    public void delete(String id){
        bookDao.delete(id);
    }
    public int selectCount() {
        return bookDao.selectCount();
    }
    public Book selectBook(String id) {
        return bookDao.selectBook(id);
    }
    public List selectBookList() {
        return bookDao.selectBookList();
    }
    public void batchAddBook(List batchArgs) {
        bookDao.batchAddBook(batchArgs);
    }
    public void batchUpdateBook(List batchArgs) {
        bookDao.batchUpdateBook(batchArgs);
    }
    public void batchDeleteBook(List batchArgs) {
        bookDao.batchDeleteBook(batchArgs);
    }

}

BookDao接口：

package cn.edu.xd.dao;

import cn.edu.xd.entity.Book;
import java.util.List;


public interface BookDao {
    public void add(Book book);
    public void update(Book book);
    public void delete(String id);
    public int selectCount();
    public Book selectBook(String id);
    public List selectBookList();
    public void batchAddBook(List batchArgs);
    public void batchUpdateBook(List batchArgs);
    public void batchDeleteBook(List batchArgs);

}

BookDao接口实现类BookDaoImpl：

package cn.edu.xd.dao;

import cn.edu.xd.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.List;

@Repository
public class BookDaoImpl implements BookDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    
    @Override
    public void add(Book book) {
        String sql = "insert into book values(?,?,?)";
        Object[] args={book.getId(),book.getName(),book.getStatus()};
        int add=jdbcTemplate.update(sql,args);
        System.out.println(add);
    }

    
    @Override
    public void update(Book book) {
        String sql = "update book set name=?,status=? where id=?";
        Object[] args={book.getName(),book.getStatus(),book.getId()};
        int update=jdbcTemplate.update(sql,args);
        System.out.println(update);
    }

    
    @Override
    public void delete(String id) {
        String sql = "delete from book where id=?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println(update);
    }

    
    @Override
    public int selectCount() {
        String sql = "select count(*) from book";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    
    @Override
    public Book selectBook(String id) {
        String sql = "select * from book where id=?";
        Book book = jdbcTemplate.queryForObject(sql, new
                BeanPropertyRowMapper(Book.class), id);//使用BeanPropertyRowMapper包装
        return book;

    }

    
    @Override
    public List selectBookList() {
        String sql = "select * from book";
        List bookList = jdbcTemplate.query(sql, new
                BeanPropertyRowMapper(Book.class));
        return bookList;
    }

    
    @Override
    public void batchAddBook(List batchArgs) {
        String sql = "insert into book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    
    @Override
    public void batchUpdateBook(List batchArgs) {
        String sql = "update book set name=?,status=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }

    
    @Override
    public void batchDeleteBook(List batchArgs) {
        String sql = "delete from book where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println(Arrays.toString(ints));
    }
}

测试类：

package cn.edu.dao;


import cn.edu.xd.entity.Book;
import cn.edu.xd.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 TestUser {

    @Test
    public void testAdd(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        Book book=new Book();
        book.setId("1");
        book.setName("Java");
        book.setStatus("在库");
        BookService service=context.getBean("bookService",BookService.class);
        service.add(book);

    }
    @Test
    public void testUpdate(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        Book book=new Book();
        book.setId("1");
        book.setName("Go");
        book.setStatus("不在库");
        BookService service=context.getBean("bookService",BookService.class);
        service.update(book);

    }
    @Test
    public void testDelete(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        String id="1";
        BookService service=context.getBean("bookService",BookService.class);
        service.delete(id);

    }
    @Test
    public void testSelectCount(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        BookService service=context.getBean("bookService",BookService.class);
        int count=service.selectCount();
        System.out.println(count);

    }
    @Test
    public void testSelectBook(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        BookService service=context.getBean("bookService",BookService.class);
        Book book=service.selectBook("2");
        System.out.println(book);

    }
    @Test
    public void testSelectBookList(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        BookService service=context.getBean("bookService",BookService.class);
        List bookList=service.selectBookList();
        System.out.println(bookList);

    }
    @Test
    public void testBatchAddBook(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        BookService service=context.getBean("bookService",BookService.class);
        List batchArgs = new ArrayList<>();
        Object[] o1 = {"3","java","a"};
        Object[] o2 = {"4","c++","b"};
        Object[] o3 = {"5","MySQL","c"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        service.batchAddBook(batchArgs);

    }
    @Test
    public void testBatchUpdateBook(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        BookService service=context.getBean("bookService",BookService.class);
        List batchArgs = new ArrayList<>();
        Object[] o1 = {"java001","a","3"};
        Object[] o2 = {"c++001","b","4"};
        Object[] o3 = {"MySQL001","c","5"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        service.batchUpdateBook(batchArgs);

    }
    @Test
    public void testBatchDeleteBook(){
        ApplicationContext context
                = new ClassPathXmlApplicationContext("bean1.xml");
        BookService service=context.getBean("bookService",BookService.class);
        List batchArgs = new ArrayList<>();
        Object[] o1 = {"3"};
        Object[] o2 = {"4"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        service.batchDeleteBook(batchArgs);

    }
}