124_Java_Spring5_JdbcTemplate

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

1 导包
2 配置xml文件

prop.driverClass=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/t_spring?useSSL=false&characterEncoding=utf8
prop.userName=root
prop.password=xxx

<?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.alex"></context:component-scan>
    <!--引入外部属性文件-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!--配置连接池-->

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <!--        prop.driverClass=com.mysql.jdbc.Driver  ${key值}-->
        <property name="driverClassName" value="${prop.driverClass}"></property>
        <property name="url" value="${prop.url}"></property>
        <property name="username" value="${prop.userName}"></property>
        <property name="password" value="${prop.password}"></property>
    </bean>


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

public interface BookDao {
    void add(Book book);

    void deleteone(String id);

    void updateone(Book book);

    int querycount();

    Book queryBook(String id);

    List<Book> queryBooks();

    void batchAdd(List<Object []> args);

    void batchupdate(List<Object[]> args);

    void batchdelete(List<Object[]> args);
}

@Service
public class BookService {

    @Autowired
    private BookDao bookdao;

    //update 添加
    public void addBook(Book book){
        bookdao.add(book);
    }

//    update 删除
    public void deleteBook(String id){
        bookdao.deleteone(id);
    }

//    update 更新
    public void updateBook(Book book){
        bookdao.updateone(book);
    }

//    queryobject 查询

    public int queryCount(){
       return bookdao.querycount();
    }

    public Book queryone(String id ){
        return bookdao.queryBook(id);
    }

    public List<Book> querayall(){
        return bookdao.queryBooks();
    }


    public void batchaddBook(List<Object[]> args){
         bookdao.batchAdd(args);
    }

    public void batchupdateBook(List<Object[]> args){
        bookdao.batchupdate(args);
    }

    public void batchdeleteBook(List<Object[]> args){
        bookdao.batchdelete(args);
    }
}

1 添加/删除/更新

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // update 插入
    @Override
    public void add(Book book) {
        String sql = "insert into t_book values(?,?,?)";
        Object[] args = {book.getUserId(), book.getUsername(), book.getUstatus()};

        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }

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

    }

    // update 更新
    @Override
    public void updateone(Book book) {
        String sql = "update t_book set username=?, ustatus=? where userid = ?";
        String[] args = {book.getUsername(), book.getUstatus(), book.getUserId()};
        int update = jdbcTemplate.update(sql,args);
        System.out.println(update);

    }
}

   @Test
    public void test3(){
        ClassPathXmlApplicationContext context1 = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context1.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setUserId("1");
        book.setUsername("java_1");
        book.setUstatus("a_1");

        bookService.updateBook(book);
    }

2 查询
RowMapper 是接口，针对返回不同类型数据，使用这个接口里面实现类完成数据封装

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

//    queryforobject 查询返回特殊值

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

//    queryforobject 查询返回单条记录
    @Override
    public Book queryBook(String id) {
        String sql = "select *  from t_book where userid =?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }


//    query 返回一组bean 对象
    @Override
    public List<Book> queryBooks() {
        String sql = "select *  from t_book ";
        List<Book> books = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return books;
    }
}

3 批量添加 / 修改

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

//    批量添加
    @Override
    public void batchAdd(List<Object []> args) {
        String sql = "insert into t_book values(?,?,?)";
        // batchupdate 做了封装， 遍历 List<object []> 后 执行添加操作
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(Arrays.toString(ints));
    }


    @Override
    public void batchupdate(List<Object[]> args) {
        String sql = "update t_book set username=?,ustatus=? where user_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(Arrays.toString(ints));
    }

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

    }
}

   @Test
    public void test5(){
        ClassPathXmlApplicationContext context1 = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context1.getBean("bookService", BookService.class);

        List<Object []> args = new ArrayList<>();
        Object[] s1 = {"3", "java_3", "a"};
        Object[] s2 = {"4", "java_4", "b"};
        Object[] s3 = {"5", "java_5", "c"};
        args.add(s1);
        args.add(s2);
        args.add(s3);

        bookService.batchaddBook(args);

    }