【JavaDay39】SpringJdbcTemplate操作数据库，以及批量操作

SpringJdbcTemplate

引入JAR包

spring封装的方法，查询语句输出的如果是对象的话，那么它会对比查询得到的表的列名比如查得后表有一列名为：bookName会和对象类的set方法名字比较，先去掉setBookName的set，然后首字母小写bookName，如果能找到这种set方法就赋值给该对象的bookName属性。

是根据属性的set方法名来决定是否把查询出来的值赋值。

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"
       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:property-placeholder location="classpath:com/sj/computer/www/myspringdemo10/jdbc.properties"/>
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" scope="prototype">
        <property name="driverClassName" value="${prop.driverClassName}"/>
        <property name="url" value="${prop.url}"/>
        <property name="username" value="${prop.username}"/>
        <property name="password" value="${prop.password}"/>
        <property name="initialSize" value="${prop.initialSize}"/>
        <property name="maxActive" value="${prop.maxActive}"/>
        <property name="maxWait" value="${prop.maxWait}"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <context:component-scan base-package="com.sj.computer.www.myspringdemo10"/>
</beans>

JDBCproperties配置文件

prop.driverClassName=com.mysql.cj.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/bookdb
prop.username=root
prop.password=123456
prop.initialSize=5
prop.maxActive=200
prop.maxWait=2000

测试类

@Controller
public class BookController {
    private BookService bookService;
    @Test
    public void test() {
        ApplicationContext context =
                new ClassPathXmlApplicationContext("com/sj/computer/www/myspringdemo10/demo10.xml");
        bookService =(BookService) context.getBean("bookService",BookService.class);
        Book book = new Book();
        book.setId(46);
        System.out.println(bookService.getBook(book));
    }
}

增删改查BookdaoImpl类

package com.sj.computer.www.myspringdemo10.dao;

import com.sj.computer.www.myspringdemo10.pojo.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;

import java.util.List;

@Repository
public class BookdaoImpl implements Bookdao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void addBook(Book book) {
        int rows = jdbcTemplate.update("insert into bookdb.t_book(bookImg, bookName, price, author, saleCount, bookCount) VALUES (?,?,?,?,?,?)",
                book.getBookImg(),book.getBookName(),book.getPrice(),book.getAuthor(),book.getSaleCount(),book.getBookCount());
        if (rows > 0) {
            System.out.println("完成增加一条数据!");
            System.out.println(rows);
        }else {
            System.out.println("error!");
        }
    }

    @Override
    public void delBook(Book book) {
        int update = jdbcTemplate.update("delete from bookdb.t_book where id=?", book.getId());
        if(update>0){
            System.out.println("删除成功！");
        }else{
            System.out.println("error！");
        }
    }

    @Override
    public List<Book> selBook(Book book) {
        return jdbcTemplate.query("select * from bookdb.t_book", new BeanPropertyRowMapper<Book>(Book.class));
    }

    @Override
    public Integer selCountBook(Book book) {
        return jdbcTemplate.queryForObject("select count(*) from bookdb.t_book",Integer.class);
    }

    @Override
    public void updateBook(Book book) {
        int rows = jdbcTemplate.update("update bookdb.t_book set bookName = ? where id = ?",book.getBookName(),book.getId());
        if(rows > 0){
            System.out.println("修改成功！");
        }
    }

    @Override
    public Book getBook(Book book) {
        return jdbcTemplate.queryForObject("select bookName from bookdb.t_book where id = ?",new BeanPropertyRowMapper<Book>(Book.class),book.getId());
    }
}

批量操作

@Override
public void batchAdd(List<Object[]> list) {
    int[] ints = jdbcTemplate.batchUpdate("insert into bookdb.t_book(bookImg, bookName, price, author, saleCount, bookCount) VALUES (?,?,?,?,?,?)"
            , list);
    System.out.println("影响得行数:"+ ints.length);
}

controller类：

public void test() {
    ApplicationContext context =
            new ClassPathXmlApplicationContext("com/sj/computer/www/myspringdemo10/demo10.xml");
    bookService =(BookService) context.getBean("bookService",BookService.class);
    List<Object[]> list=new ArrayList<>();
    Object [] o1 ={"a.jpg","java1",11.20,"w3",20,88};
    Object [] o2 ={"b.jpg","java2",11.20,"w3",20,88};
    Object [] o3 ={"c.jpg","java3",11.20,"w3",20,88};
    list.add(o1);
    list.add(o2);
    list.add(o3);
    bookService.batchAdd(list);
}

影响得行数:[1, 1, 1]