基于Spring框架和JDBCTemplate对数据库的操作

---

1.先进行相关的准备工作：相关jar包的加载，以及xml文件的配置：

 （由于我做的项目中还有其他的内容所以有需要可以拿自己需要的jar包就行）

相关jar包链接

链接：https://pan.baidu.com/s/1vFAxOvkTUz1T7sjvi8pnjA?pwd=24sn 
提取码：24sn

XML文件的具体配置：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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
                            http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!--开启组件扫描-->
    <context:component-scan base-package="com.chenry.*"></context:component-scan>
    <!--数据库连接池-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value=" jdbc:mysql://localhost:3306/shujukumingcheng?characterEncoding=UTF-8&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;allowMultiQueries=true&amp;zeroDateTimeBehavior=CONVERT_TO_NULL"/>
        <property name="username" value="yonghu" />
        <property name="password" value="mima" />
    </bean>

<!--创建JDBCTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入datasource-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

2.创建相关的package进行代码的编写：

在Dao包中进行数据库的相关操作：

声明一个Dao接口，里面声明对数据操作的方法；增删改查，以及批量增删改查；

public interface BookDao {
    //添加
    public void add(Book book);
    //修改
    public void delete(int id);
    //删除
    public void update(Book book);
    //查询数据库中有几条数据
    public int selectcount();
    //根据Book的名称来查找相关的book
    //输出的是一个book的对象
    public void findbookinfo(String args);
    //以list数组的形式来返回
    public  List<Book> findbookArray();
    //批量添加
    public void addAll(List<Object[]> booklist);
    //批量修改
    public void updateAll(List<Object[]> booklist);
    //批量删除
    public void deleteAll(List<Object[]> Booklist);
}

声明DaoImp类，重写Dao里面的各种方法进行对数据库的操作；

public class BookDaoImp implements BookDao{
    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void add(Book book) {
        //添加：
        //创建SQL语句：
        String sql="insert into book values(?,?,?)";
        //调用方法实现添加：
        Object[] args={ book.getId(), book.getName(), book.getAuthor()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update+"行数据添加成功");
    }

    @Override
    public void delete(int id) {
        String sql="delete from book where id=?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println(update+"已经删除成功");
    }
    @Override
    public void update(Book book) {
        //修改：
        String sql="update book set name=?,author=? where id=?";
        Object[] args={ book.getName(), book.getAuthor(), book.getId()};
        int update = jdbcTemplate.update(sql, args);
        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 void findbookinfo(String args) {
        String sql="select * from book where name=?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), args);
        System.out.println(book);
    }
    @Override
    public List<Book> findbookArray() {
        String sql="select * from book";
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return query;
    }
    @Override
    public void addAll(List<Object[]> booklist) {
        String sql="insert into book values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, booklist);
        System.out.println(Arrays.toString(ints));
    }
    @Override
    public void updateAll(List<Object[]> booklist) {
        String sql="update book set name=?,author=? where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, booklist);
        System.out.println(Arrays.toString(ints));
    }
    @Override
    public void deleteAll(List<Object[]> Booklist) {
        String sql="delete from book where id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, Booklist);
        System.out.println(Arrays.toString(ints));
    }
}

 3.创建service包里面对方法进行简单的调用（具体代码后续可以编写）

@Service
public class BookService {
    //注入Dao
    @Autowired
    private BookDao bookDao;
    public void addBook(Book book){
       bookDao.add(book);
    }
    //修改：
    public void update(Book book){
        bookDao.update(book);
    }
    //删除：
    public void delete(int i){
        bookDao.delete(i);
    }
    //查询有多少条记录：
    public int findcount(){
        int selectcount = bookDao.selectcount();
        return selectcount;
    }
    //查询返回对象：
    public void  findentry(String args){
        bookDao.findbookinfo(args);
    }
    //查询返回集合：
    public List<Book> findArray(){
        return bookDao.findbookArray();
    }
    //批量操作数据库：
    //批量添加：
    public void addAllBook(List<Object[]> Booklist){
        bookDao.addAll(Booklist);
    }
    //批量修改：
    public void updateAllBook(List<Object[]> Booklist){
        bookDao.updateAll(Booklist);
    }
    //批量删除：
    public void deleteAllBook(List<Object[]> Booklist){
        bookDao.deleteAll(Booklist);
    }
}

这样基本上就完成了增删改查的基本操作：

下边是一些测试：

public class BookTest {
    @Test
    public void testadd(){
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        Book book=new Book();
        book.setId(6);
        book.setName("大学");
        book.setAuthor("巴金");
        bookService.addBook(book);

    }
    @Test
    public void testupdate(){
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        Book book=new Book();
        book.setId(1);
        book.setName("数据库");
        book.setAuthor("李长青");
        bookService.update(book);

    }
    @Test
    public void testdelete(){
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        Book book=new Book();
        int i=6;
       bookService.delete(i);

    }
    @Test
    public void testSelect(){
        //查询有多少条数据
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        int findcount = bookService.findcount();
        System.out.println(findcount);
    }
    @Test
    public void testSelectOne(){
        //查询返回对象：
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        bookService.findentry("三国演义");

    }
    @Test
    public void testBookList(){
        //查询返回数组：
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        List<Book> array = bookService.findArray();
        System.out.println(array);

    }
    @Test
    public void testAddAll(){
        //批量添加的测试：
        ArrayList<Object[]> books = new ArrayList<>();
        Object[] b1={6,"论语","孔子"};
        Object[] b2={7,"中庸","孔子"};
        Object[] b3={8,"史记","司马迁"};
        books.add(b1);
        books.add(b2);
        books.add(b3);
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        bookService.addAllBook(books);
    }
    //批量更改：
    @Test
    public void testupdateAll(){
        ArrayList<Object[]> books = new ArrayList<>();
        Object[] b1={"百万英镑","马克吐温",1};
        Object[] b2={"朝花夕拾","鲁迅",2};
        Object[] b3={"孙子兵法","孙膑",3};
        books.add(b1);
        books.add(b2);
        books.add(b3);
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        bookService.updateAllBook(books);
    }
    @Test
    public void testDeleteAll(){
        ArrayList<Object[]> books = new ArrayList<>();
        Object[] b1={6};
        Object[] b2={7};
        Object[] b3={8};
        books.add(b1);
        books.add(b2);
        books.add(b3);
        ApplicationContext acct
                =new ClassPathXmlApplicationContext("Bean1.xml");
        BookService bookService = acct.getBean("bookService", BookService.class);
        bookService.deleteAllBook(books);

    }
}