[Java知识库]2021-8-16-Spring5 学习笔记(jdbcTemplate)

使用JdbcTemplate操作数据

引入jar包

• druid-1.2.6.jar
• ojdbc6-11.2.0.4.jar
• spring-jdbc-5.3.8.jar
• spring-tx-5.3.8.jar
• spring-orm-5.3.8.jar

配置文件

<?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:p="http://www.springframework.org/schema/p"
       xmlns:util="http://www.springframework.org/schema/util"
       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/util http://www.springframework.org/schema/util/spring-util.xsd
                            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
        <property name="username" value="STUDY_SPRING"></property>
        <property name="password" value="123456"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <context:component-scan base-package="com.study.spring5_2"></context:component-scan>
</beans>

创建Dao，service，entity

package com.study.spring5_2.dao;

import com.study.spring5_2.entity.Book;

public interface BookDao {
    public void add(Book book);
}

增 删 改

package com.study.spring5_2.dao;

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

@Repository
public class BookDaoImpl implements BookDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(Book book) {
        String sql = "insert into book values (?,?,?)";
        int update = jdbcTemplate.update(sql, book.getId(), book.getBookName(), book.getBookAddress());
        System.out.println(update);
    }
}

package com.study.spring5_2.service;

import com.study.spring5_2.dao.BookDao;
import com.study.spring5_2.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BookService {

    @Autowired
    private BookDao bookDao;

    public void add(Book book){

        bookDao.add(book);
    }
}

package testdemo;

import com.study.spring5_1.Dao.UserAsspectJ;
import com.study.spring5_1.proxy.JDBCProxy;
import com.study.spring5_2.entity.Book;
import com.study.spring5_2.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestJDBC {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext app = new ClassPathXmlApplicationContext("jdbcTemplate.xml");
        BookService service = (BookService)app.getBean("bookService", BookService.class);
        Book book = new Book();
        book.setId(1);
        book.setBookName("AAA");
        book.setBookAddress("123123");
        service.add(book);
    }
}

查

//返回对象，注意BeanPropertyRowMapper的用法，这个类是提供的一个实现类，
//是为了封装查询结果。将结果封装为自定义的bean
    @Override
    public Book getById(int id) {
        String sql = "select * from book where id = ?";
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }
//返回集合
    @Override
    public List<Book> getAll() {
        String sql = "select * from book";
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
        return query;
    }
//返回int类型，此时就不用BeanPropertyRowMapper了
    @Override
    public int getCount() {
        String sql = "select count(*) from book";
        int book = jdbcTemplate.queryForObject(sql, Integer.class);
        return book;
    }

批量增删改

    @Override
    public void batchAdd(List<Object[]> args) {
        String sql = "insert into book values (?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchUpdate(List<Object[]> args) {
        String sql = "update book set book_name = ? and book_address = ? where id = ?";
        int[] ints = jdbcTemplate.batchUpdate(sql, args);
        System.out.println(Arrays.toString(ints));
    }

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

事务

什么是事务?

事务时数据库操作最基本单元，逻辑上的一组操作，要么都成功，如果有一个失败所有操作都失败

事务的四个特性(ACID)

• 原子性
• 一致性
• 隔离性
• 持久性

事务操作

1、将事务添加到JavaEE三层结构里面的service层
2、在Spring进行事务管理操作
编程式事务管理(通过代码 try-catch的方式)
声明式事务管理(常用)
3、声明式事务管理
基于注解方式(经常使用)
基于xml配置文件方式
4、在Spring进行声明式事务管理，底层使用AOP原理
5、Spring事务管理API
提供一个接口，代表事务管理器，这个接口针对不同的框架提供不同的实现类PlatformTransactionManager

引入事务的操作

在配置文件中

• 引入命名空间tx
• 创建事务管理器
• 开启事务管理器

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                            http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd
                            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
                            http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
        <property name="username" value="STUDY_SPRING"></property>
        <property name="password" value="123456"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

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

    <!-- 创建事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!-- 开启事务管理器-->
    <tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
</beans>

在service中引用事务

如果将注解@Transactional放到类名上说明该类下所有的方法都开启事务，如果放到某一个方法上，就只针对该方法开启事务

@Service
@Transactional
public class AccountService {
    @Autowired
    private AccountDao accountDao;

    public void transfer(){
        accountDao.addMoney();
        int i = 10/0;
        accountDao.reduceMoney();
    }
}