04.JdbcTemplate

Spring 对 JDBC 进行封装，使用 JdbcTemplate 方便对数据库的操作。

1.增删改操作

int update(String sql, Object... args);

2.查询：返回某个值

T queryForObject(String sql,Class<T> requiredType);

3.查询：返回某个对象

T queryForObject(String sql,RowMapper<T> rowMapper,Object ... args);

4.查询：返回集合

List<T> query(String sql,RowMapper<T> rowMapper,Object... args);

5.批量增删改

int[] batchUpdate(String sql,List<Object[]> batchArgs);

举例：

1. 引入相关 jar 包
2. 配置数据库连接池；配置 JdbcTemplate 对象

<context:component-scan base-package="com.oymn"></context:component-scan>

<!--配置数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    <property name="url" value="jdbc:mysql://localhost:3306/book" />
    <property name="username" value="root" />
    <property name="password" value="000000" />
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>

<!--创建JdbcTemplate对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <!--注入数据库连接池-->
    <property name="dataSource" ref="dataSource"></property>
</bean>

3. 创建 Service 类和 Dao 类，在 Dao 类中注入 JdbcTemplate 对象

public interface BookDao {

    public void add(Book book);  //添加图书

    public void update(Book book);  //修改图书

    public void delete(int id);  //删除图书

    public int queryCount();   //查询数量

    public Book queryBookById(int id);  //查询某本书

    public List<Book> queryBooks();   //查询所有书

    public void batchAddBook(List<Object[]> books);  //批量添加图书

    public void batchUpdateBook(List<Object[]> books);  //批量修改图书

    public void batchDeleteBook(List<Object[]> args);  //批量删除图书
}

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(Book book) {
        String sql = "insert into t_book set name=?,price=?";
        Object[] args = {book.getBookName(),book.getBookPrice()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

    @Override
    public void update(Book book) {
        String sql = "update t_book set name=?,price=? where id=?";
        Object[] args = {book.getBookName(),book.getBookPrice(),book.getBookId()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

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

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

    @Override
    public Book queryBookById(int id) {
        String sql = "select id bookId,name bookName,price bookPrice from t_book where id=?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

    @Override
    public List<Book> queryBooks() {
        String sql = "select id bookId,name bookName,price bookPrice from t_book";
        List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return bookList;
    }

    @Override
    public void batchAddBook(List<Object[]> books) {
        String sql = "insert into t_book set id=?,name=?,price=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, books);
        System.out.println(ints);
    }

    @Override
    public void batchUpdateBook(List<Object[]> books) {
        String sql = "update t_book set name=?,price=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, books);
        System.out.println(ints);
    }

    @Override
    public void batchDeleteBook(List<Object[]> args) {
        String sql = "delete from t_book where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(ints);
    }
}

@Service
public class BookService {
    @Autowired
    private BookDao bookDao = new BookDaoImpl();
    //添加图书
    public void add(Book book){
        bookDao.add(book);
    }
    //修改图书
    public void update(Book book){
        bookDao.update(book);
    }
    //删除图书
    public void delete(Integer id){
        bookDao.delete(id);
    }
    //查询数量
    public int queryCount(){
        return bookDao.queryCount();
    }
    //查询图书
    public Book queryBookById(Integer id){
        return bookDao.queryBookById(id);
    }
    //查询所有图书
    public List<Book> queryBooks(){
        return bookDao.queryBooks();
    }
    //批量添加图书
    public void batchAddBook(List<Object[]> books){
        bookDao.batchAddBook(books);
    }
    //批量修改图书
    public void batchUpdateBook(List<Object[]> books){
        bookDao.batchUpdateBook(books);
    }
    //批量删除图书
    public void batchDeleteBook(List<Object[]> args){
        bookDao.batchDeleteBook(args);
    }
}