使用JdbcTemplate (Spring3.x 二十二)

JdbcTemplate就是將傳統的JDBC封裝起來，讓程式員只需管自行撰寫SQL語句和處理ResultSet，不用管什麼關閉連線、關閉ResultSet、Transaction…等

pom.xml

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>3.2.13.RELEASE</version>
</dependency>
    
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>3.2.13.RELEASE</version>
</dependency>

※除了context一定要下載外，還要有jdbc的jar，當然資料庫的Driver也要

取得Connection

import java.sql.SQLException;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
    
public class TestJDBCTemplate {
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";
    
    public static void main(String[] args) {
        // DriverManagerDataSource ds = new DriverManagerDataSource(DRIVER,
        // URL,
        // USERNAME, PASSWORD);
    
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(DRIVER);
        ds.setUrl(URL);
        // ds.setUsername(USERNAME);
        // ds.setPassword(PASSWORD);
    
        System.out.println(ds.getUrl());
        System.out.println(ds.getUsername());
        System.out.println(ds.getPassword());
    
        try {
            System.out.println(ds.getConnection(USERNAME, PASSWORD)
                    .isClosed());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    
        // try {
        // System.out.println(ds.getConnection().isClosed());
        // } catch (SQLException e) {
        // e.printStackTrace();
        // }
    }
}

※DriverManagerDataSource是overloading，下面的程式碼還會寫其他的方式

查詢一筆欄位

DriverManagerDataSource ds = new DriverManagerDataSource(URL, USERNAME, PASSWORD);
ds.setDriverClassName(DRIVER);
    
JdbcTemplate jt = new JdbcTemplate(ds);
String sql = "SELECT LOC FROM DEPT WHERE DEPTNO = ? AND DNAME = ?";
String loc = jt.queryForObject(sql, new Object[] { 10, "ACCOUNTING" }, String.class);
System.out.println(loc);
System.out.println(jt.queryForInt("SELECT COUNT(*) FROM DEPT"));

查詢多筆

@SuppressWarnings("deprecation")
DriverManagerDataSource ds = new DriverManagerDataSource(DRIVER, URL, USERNAME, PASSWORD);
JdbcTemplate jt = new JdbcTemplate(ds);
    
String sql = "SELECT * FROM DEPT WHERE DEPTNO < ?";
List<Map<String, Object>> rtn = jt.queryForList(sql, 50);
    
for (Map<String, Object> map : rtn) {
    System.out.println(map.get("DEPTNO"));
    System.out.println(map.get("DNAME"));
    System.out.println(map.get("LOC") + "\n");
}

查詢VO 

Dept.java

public class Dept {
    private Integer deptno;
    private String dname;
    private String loc;
    // setter/getter...
}

測試

DriverManagerDataSource ds = new DriverManagerDataSource(URL, USERNAME, PASSWORD);
ds.setDriverClassName(DRIVER);
JdbcTemplate jt = new JdbcTemplate(ds);
    
String sql = "SELECT * FROM DEPT WHERE DEPTNO >= ?";
List<Dept> list = jt.query(sql, new Object[] { 20 },
    new RowMapper<Dept>() {
        @Override
        public Dept mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            System.out.println("目前行數" + rowNum);
            Dept vo = new Dept();
            vo.setDeptno(rs.getInt(1));
            vo.setDname(rs.getString(2));
            vo.setLoc(rs.getString(3));
            return vo;
        }
    });
    
for (Iterator<Dept> it = list.iterator(); it.hasNext();) {
    Dept dept = it.next();
    System.out.println(dept.getDname());
}

※主要是RowMapper較複雜

新增

@SuppressWarnings("deprecation")
DriverManagerDataSource ds = new DriverManagerDataSource(DRIVER, URL, USERNAME, PASSWORD);
JdbcTemplate jt = new JdbcTemplate(ds);
    
String sql = "INSERT INTO DEPT VALUES (?, ?, ?)";
int success = jt.update(sql, 50, "業務部", "芝加哥");
System.out.println("成功新增" + success + "筆！");

※增刪改都是用update方法

PK為流水號

DriverManagerDataSource ds = new DriverManagerDataSource(URL, USERNAME, PASSWORD);
ds.setDriverClassName(DRIVER);
    
JdbcTemplate jt = new JdbcTemplate(ds);
KeyHolder pk = new GeneratedKeyHolder();
    
int success = jt.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con)
            throws SQLException {
        String sql = "INSERT INTO DEPT(DNAME, LOC) VALUES (?, ?)";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1, "業務部");
        pstmt.setString(2, "芝加哥");
        return pstmt;
    }
}, pk);
System.out.println("成功新增" + success + "筆！");
System.out.println("PK是" + pk.getKey());

※此方法不適用Oracle，我目前沒其他家的資料庫，所以沒試過成不成功

Oracle的PK為流水號

DriverManagerDataSource ds = new DriverManagerDataSource(URL, USERNAME, PASSWORD);
ds.setDriverClassName(DRIVER);
JdbcTemplate jt = new JdbcTemplate(ds);
    
String pkSql = "SELECT DEPT_SEQ.NEXTVAL FROM DUAL";
Long pk = jt.queryForObject(pkSql, Long.class);
    
String insSql = "INSERT INTO DEPT VALUES (?, ?, ?)";
int success = jt.update(insSql, pk, "業務部", "芝加哥");
System.out.println("成功新增" + success + "筆！");
System.out.println("PK是" + pk);

※Oracle先從資料庫把Sequence查詢出來存在一個變數，然後再做新增