Spring 管理 JdbcTemplate、DBCP、C3P0、JdbcDaoSupport (Spring3.x 二十三)

將上一篇的程式碼交給Spring管理

applicationContext.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:p="http://www.springframework.org/schema/p" xmlns:c="http://www.springframework.org/schema/c"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">
    
    <context:annotation-config />
    <context:component-scan base-package="dao.impl" />
    
    <bean id="ds"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource"
        p:driverClassName="oracle.jdbc.driver.OracleDriver" p:url="jdbc:oracle:thin:@127.0.0.1:1521:orcl"
        p:username="username" p:password="password" />
    
    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="ds" />
</beans>

IDepDAO.java

public interface IDeptDAO {
    public static final String PK_SQL = "SELECT DEPT_SEQ.NEXTVAL FROM DUAL";
    public Long get();
    public int save(Long deptno, String dname, String loc);
}

DeptDAOImpl.java

@Repository
public class DeptDAOImpl implements IDeptDAO {
    @Autowired
    private JdbcTemplate jt;
    
    public DeptDAOImpl() {
    }
    
    public DeptDAOImpl(JdbcTemplate jt) {
        this.jt = jt;
    }
    
    @Override
    public Long get() {
        return jt.queryForObject(PK_SQL, Long.class);
    }
    
    @Override
    public int save(Long deptno, String dname, String loc) {
        String sql = "INSERT INTO DEPT VALUES (?, ?, ?)";
        return jt.update(sql, deptno, dname, loc);
    }
}

測試類

ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    
for (String s : ctx.getBeanDefinitionNames()) {
    System.out.println(s);
}
    
IDeptDAO dao = ctx.getBean("deptDAOImpl", IDeptDAO.class);
int success = dao.save(dao.get(), "業務部", "芝加哥");
System.out.println("成功新增" + success + "筆！");
    
((ClassPathXmlApplicationContext) ctx).close();

這次的專案圖

這邊有個小技巧，注意DeptDAOImpl.java的圖示，右上角有個「S」，表示使用getBean可以抓的到


官方的${}是在外面新增一個檔案，然後抓進去用的，如下：

xxx.properties

jdbc.d=oracle.jdbc.driver.OracleDriver
jdbc.u1=jdbc:oracle:thin:@127.0.0.1:1521:orcl
jdbc.u2=username
jdbc.p=password

applicationContext.xml

<context:property-placeholder location="classpath:xxx.properties" />
    
<bean id="ds"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource"
    p:driverClassName="${jdbc.d}" p:url="${jdbc.u1}" p:username="${jdbc.u2}"
    p:password="${jdbc.p}" />

※DBCP和C3P0的支援

官網可複製，快到14.3.2的上面兩張表

applicationContext.xml

<context:property-placeholder location="classpath:xxx.properties" />
    
<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="${jdbc.d}" />
    <property name="url" value="${jdbc.u1}" />
    <property name="username" value="${jdbc.u2}" />
    <property name="password" value="${jdbc.p}" />
</bean>
    
<bean id="ds2" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close">
    <property name="driverClass" value="${jdbc.d}" />
    <property name="jdbcUrl" value="${jdbc.u1}" />
    <property name="user" value="${jdbc.u2}" />
    <property name="password" value="${jdbc.p}" />
</bean>

pom.xml

<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>
    
<dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
</dependency>

※以上是基本設定，還有很多設定：
DBCP的文件可參考這裡，而API可參考這裡，
C3P0的文件可參考這裡，而API可參考這裡，package是com.mchange.v2.c3p0，class是ComboPooledDataSource

※JdbcDaoSupport

JdbcDaoSupport可以幫我們取得JdbcTemplate，所以原來的程式碼還可以再改成如下的寫法

@Repository
public class DeptDAOImpl extends JdbcDaoSupport implements IDeptDAO {
    public DeptDAOImpl() {}
    
    @Autowired
    public DeptDAOImpl(JdbcTemplate jt) {
        super.setJdbcTemplate(jt);
    }
    
    @Override
    public Long get() {
        return super.getJdbcTemplate().queryForObject(PK_SQL, Long.class);
    }
    
    @Override
    public int save(Long deptno, String dname, String loc) {
        String sql = "INSERT INTO DEPT VALUES (?, ?, ?)";
        return super.getJdbcTemplate().update(sql, deptno, dname, loc);
    }
}

※繼承了JdbcDaoSupport以後，原本的JdbcTemplate統統都用super取得了