Review

AOP是在處理Cross-cutting concerns，將某段代碼(日誌)動態切入(不把日誌程式hardcode到業務邏輯方法中)到指定方法(加減乘除)的指定位置進行運行。

昨天我們學到了AOP中多個Aspect的Advice的運行順序

今日我們將進入JdbcTemplate作為 Declarative Transaction Management(聲明式事務)的準備，今日就來點JdbcTemplate CRUD吧!

JAR檔準備

• spring-jdbc-5.3.22.jar
• spring-orm-5.3.22.jar
• spring-tx-5.3.22.jar

H2 DB設定

登入H2 DB password：sa

執行SQL

CREATE TABLE PRODUCT(
    PRODUCT_ID NVARCHAR(10) NOT NULL,
    INVENTORY INT
);
INSERT INTO PRODUCT VALUES ('P001',100);
INSERT INTO PRODUCT VALUES ('P002',80);

Spring ioc 設定

連線池設定

先完成Day11 - Dependency Injection (6) 設定資料庫連線池吧

db連線資訊設定

設定檔加入jdbcTemplate

<?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 https://www.springframework.org/schema/context/spring-context.xsd">
    <!-- 加載外部文件   -->
    <context:property-placeholder location="classpath:dbconfig.properties"></context:property-placeholder>
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        <property name="driverClass" value="${jdbc.driverClass}"></property>
    </bean>
    <!-- 配置JdbcTemplate   -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
    </bean>
</beans>

何謂JdbcTempalte

Spring framework對JDBC進行的封裝，簡化原本JDBC對資料庫的操作

UPDATE

@Test
public void testDay23(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean23.xml");
    System.out.println("容器啟動完成....");
    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

    String sql = "UPDATE PRODUCT SET INVENTORY = ? WHERE PRODUCT_ID = ?";
    int num = jdbcTemplate.update(sql,99,"P001");
    System.out.println("update num："+num);
}

Result

BATCH INSERT

batchUpdate為增刪改3合一個方法

@Test
public void testDay23(){
    ApplicationContext ioc = new ClassPathXmlApplicationContext("bean23.xml");
    System.out.println("容器啟動完成....");
    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);

    List<Object[]> batchList = new ArrayList<>();
    batchList.add(new Object[]{"P003",20});
    batchList.add(new Object[]{"P004",10});
    String sql = "INSERT INTO PRODUCT (PRODUCT_ID,INVENTORY) VALUES (?,?)";
    int[] updateArr = jdbcTemplate.batchUpdate(sql,batchList);
    for (int num: updateArr) {
        System.out.println(num);
    }
}

Result

Reference

• Spring app connects to in-memory H2 db instead of file based - stackoverflow