15-Spring5 JdbcTemplate的增删改查操作

初始数据库表t_book

①查询一条记录

    @Override
    public Book findById(Integer id) {
        String sql = "select * from t_book where id = ?";  //"?"是占位符
        //因为Book是自定义的类型,所以第二个参数用new BeanPropertyRowMapper(Book.class), 而不是Book.class
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Book.class), id);
        return book;
    }

执行结果

System.out.println(bookDao.findById(2));

②查询返回某个值

    @Override
    public String findNameById(Integer id) {
        String sql = "select name from t_book where id = ?";  //"?"是占位符
        String name = jdbcTemplate.queryForObject(sql, String.class, id);
        return name;
    }

执行结果

System.out.println(bookDao.findNameById(1));

③添加一条记录
注意:添加、修改和删除一条记录都是用update函数

    @Override
    public int add(Book book) {
        String sql = "insert into t_book values(?, ? , ?)";
        Object[] values = {book.getId(), book.getName(), book.getPrice()};
        //添加,修改和删除都是用update
        int res = jdbcTemplate.update(sql, values);  //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99)
        return res;
    }

执行结果

System.out.println(bookDao.add(new Book(3, "python", 9.99)));

④修改一条记录

    @Override
    public int updateById(Book book) {
        String sql = "update t_book set name = ?, price = ? where id = ?";
        Object[] values = {book.getName(), book.getPrice(), book.getId()}; //注意顺序
        //添加,修改和删除都是用update
        int res = jdbcTemplate.update(sql, values);  //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99
        return res;
    }

执行结果

System.out.println(bookDao.updateById(new Book(3, "python_update", 6.88)));

⑤删除一条记录

    @Override
    public int deleteById(Integer id) {
        String sql = "delete from t_book where id = ?";
        //添加,修改和删除都是用update
        int res = jdbcTemplate.update(sql, id);
        return res;
    }

执行结果

System.out.println(bookDao.deleteById(1));

配置文件bean1.xml

Book

package com.limi.entity;

public class Book {

    private Integer id;

    String name;

    Double price;

    public Book(){}

    public Book(Integer id, String name, Double price) {
        this.id = id;
        this.name = name;
        this.price = price;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", price=" + price +
                '}';
    }
}

BookDao

package com.limi.dao;

import com.limi.entity.Book;

public interface BookDao {

    //根据id查找书籍
    Book findById(Integer id);

    //根据id查找书籍名称
    String findNameById(Integer id);

    //添加书籍
    int add(Book book);

    //修改书籍
    int updateById(Book book);

    //删除书籍
    int deleteById(Integer id);
}

BookDaoImpl

package com.limi.dao;

import com.limi.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Book findById(Integer id) {
        String sql = "select * from t_book where id = ?";  //"?"是占位符
        //因为Book是自定义的类型,所以第二个参数用new BeanPropertyRowMapper(Book.class), 而不是Book.class
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Book.class), id);
        return book;
    }

    @Override
    public String findNameById(Integer id) {
        String sql = "select name from t_book where id = ?";  //"?"是占位符
        String name = jdbcTemplate.queryForObject(sql, String.class, id);
        return name;
    }


    @Override
    public int add(Book book) {
        String sql = "insert into t_book values(?, ? , ?)";
        Object[] values = {book.getId(), book.getName(), book.getPrice()};
        //添加,修改和删除都是用update
        int res = jdbcTemplate.update(sql, values);  //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99)
        return res;
    }

    @Override
    public int updateById(Book book) {
        String sql = "update t_book set name = ?, price = ? where id = ?";
        Object[] values = {book.getName(), book.getPrice(), book.getId()}; //注意顺序
        //添加,修改和删除都是用update
        int res = jdbcTemplate.update(sql, values);  //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99
        return res;
    }

    @Override
    public int deleteById(Integer id) {
        String sql = "delete from t_book where id = ?";
        //添加,修改和删除都是用update
        int res = jdbcTemplate.update(sql, id);
        return res;
    }
}

测试类MyTest

package com.limi.test;
import com.limi.dao.BookDao;
import com.limi.dao.BookDaoImpl;
import com.limi.entity.Book;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MyTest {

    @Test
    public void test1(){
        //1.加载bean的xml文件, 以src为根目录
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");

        //2.获取配置的对象, 参数1:bean的id值, 参数2: 类名.class
        BookDao bookDao = context.getBean("bookDaoImpl", BookDaoImpl.class);

        //3.使用对象
        System.out.println(bookDao.findById(2));

    }
}

数据库表t_book的结构