JdbcTemplate测试里面的方法

自己写了一个jdbcTemplate测试类，对他进行了一些应用。今天贴出来，以抛砖引玉！

package com.db.jdbc.template;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.briup.db.jdbc.MyTable;

public class JdbcTemplateTest {
    private BasicDataSource dataSource;
    
    private JdbcTemplate jdbcTemplate =new JdbcTemplate();
    
    public JdbcTemplateTest(){
        ApplicationContext ac=new ClassPathXmlApplicationContext("com/db/jdbc/template/dataSource.xml");
        dataSource=(BasicDataSource)ac.getBean("dataSource");
        jdbcTemplate.setDataSource(dataSource);
        
    }
    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        JdbcTemplateTest test =new JdbcTemplateTest();
//        MyTable mytable =(MyTable)test.queryForObject_RowMapper();
//        System.out.println("this is mytable'name : "+mytable.getName()+" the id is :"+mytable.getId());
//        
        test.queryForRowSet();
    }
    //Statement,ResultSet
    public Object executeConnectionCallback(){
        return jdbcTemplate.execute(new ConnectionCallback(){

            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                Statement stm=conn.createStatement();
                String sql="select id,name from mytable where id=1";
                ResultSet rs=stm.executeQuery(sql);
                MyTable mytable=new MyTable();
                while(rs.next()){
                    mytable.setId(rs.getInt("id"));
                    mytable.setName(rs.getString("name"));
                    break;
                }
                return mytable;
            }
            
        });
    }
    
    public Object executeStatementCallback(){
        return jdbcTemplate.execute(new StatementCallback(){

            public Object doInStatement(Statement stm) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                String sql="select id,name from mytable where id=1";
                ResultSet rs=stm.executeQuery(sql);
                MyTable mytable=new MyTable();
                while(rs.next()){
                    mytable.setId(rs.getInt("id"));
                    mytable.setName(rs.getString("name"));
                    break;
                }
                return mytable;
            }
            
        });
    }
    
    //execute中的方法就是 实现StatementCallback的接口
    public void execute(){
        String sql="select id,name from mytable where id=1";
        jdbcTemplate.execute("");
    }
    
    public Object queryResultSetExtractor(){
        String sql="select id,name from mytable where id=1";
        return jdbcTemplate.query(sql,new ResultSetExtractor(){

            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                MyTable mytable=new MyTable();
                while(rs.next()){
                    mytable.setId(rs.getInt("id"));
                    mytable.setName(rs.getString("name"));
                    break;
                }
                return mytable;
            }
            
        });
    }
    
    
    //for list
    public void queryRowCallbackHandler(){
        String sql="select id,name from mytable";
        final List<MyTable> list=new ArrayList();
        jdbcTemplate.query(sql, new RowCallbackHandler(){

            public void processRow(ResultSet rs) throws SQLException {
                // TODO Auto-generated method stub
                MyTable mytable=new MyTable();
                mytable.setId(rs.getInt("id"));
                mytable.setName(rs.getString("name"));
                list.add(mytable);
            }
            
        });
        
        for(int i=0;i<list.size();i++){
            MyTable t=(MyTable)list.get(i);
            System.out.println("id: "+t.getId()+"    name: "+t.getName());
        }
    }
    
    // for list
    public void queryRowMapper(){
        String sql="select id,name from mytable";
        List list=new ArrayList();
        list=jdbcTemplate.query(sql, new RowMapper(){
            
            public Object mapRow(ResultSet rs, int num) throws SQLException {
                // TODO Auto-generated method stub
                MyTable mytable=new MyTable();
                mytable.setId(rs.getInt("id"));
                mytable.setName(rs.getString("name"));
                return mytable;
            }
            
        });
        
        for(int i=0;i<list.size();i++){
            MyTable t=(MyTable)list.get(i);
            System.out.println("id: "+t.getId()+"    name: "+t.getName());
        }
    }
    
    public void queryForMap(){
        String sql="select id,name from mytable where id=1";
        Map map=jdbcTemplate.queryForMap(sql);
        System.out.println(map.size()+"  "+map.get("id"));
    }
    
    public Object queryForObject_RowMapper(){
        String sql="select id,name from mytable where id=1";
        return jdbcTemplate.queryForObject(sql, new RowMapper(){
            
            public Object mapRow(ResultSet rs, int num) throws SQLException {
                // TODO Auto-generated method stub
                MyTable mytable=new MyTable();
                mytable.setId(rs.getInt("id"));
                mytable.setName(rs.getString("name"));
                return mytable;
            }
            
        });
    }
    
    // for single column
    public void queryForObject_RequiredType(){
        String sql="select name from mytable where id=1";
        Object obj= jdbcTemplate.queryForObject(sql, String.class);
        System.out.println("this name is : "+obj);
        
    }
    
    // long ,int ,Integer都是可以转换的
    public void queryForLong(){
        String sql="select id from mytable where id=1";
        Long lg=jdbcTemplate.queryForLong(sql);
        System.out.println(lg);
    }
    
    public void queryForInt(){
        String sql="select id from mytable where id=1";
        Long lg=jdbcTemplate.queryForLong(sql);
        System.out.println(lg);
    }
    //===============
    
    //for single column
    public void queryForList(){
        String sql="select id from mytable";
        List list =new ArrayList();
        list=jdbcTemplate.queryForList(sql,Integer.class);
        for(int i=0;i<list.size();i++){
            Integer it=(Integer)list.get(i);
            System.out.println(it);
        }
    }
    
    //getColumnMapRowMapper()  the  column  map
    public void queryForList_SiP(){
        String sql="select id,name from mytable";
        List list =new ArrayList();
        list=jdbcTemplate.queryForList(sql);
        for(int i=0;i<list.size();i++){
            Map it=(Map)list.get(i);
            System.out.println("the id is : "+it.get("id")+
                    "   the name is : "+it.get("name"));
        }
    }
    
    // like the ResultSet of jdbc
    public void queryForRowSet(){
        
        
        String sql="select id,name from mytable";
        SqlRowSet rs=jdbcTemplate.queryForRowSet(sql);
        while(rs.next()){
            System.out.println("the id is : "+rs.getInt("id")+
                    "   the name is : "+rs.getString("name"));
        }
    }
    
    public void update(){
        String sql="update mytable set name='john2' where id=1";
        int num=jdbcTemplate.update(sql);
        System.out.println(num);
    }
    
    public void batchUpdate(){
        String sql="update mytable set name='john22' where id=1";
        String sql2="update mytable set name='john2' where id=1";
        int[] num=jdbcTemplate.batchUpdate(new String[]{sql,sql2});
        for(int i=0;i<num.length;i++){
            System.out.println(num[i]);
        }
    }
}

------------------------------------------------------------

dataSource.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd" >
<beans>
    <bean name="dataSource"
        class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName">
            <value>oracle.jdbc.driver.OracleDriver</value>
        </property>
        <property name="url">
            <value>jdbc:oracle:thin:@127.0.0.1:1521:orcl</value>
        </property>
        <property name="username">
            <value>test</value>
        </property>
        <property name="password">
            <value>test</value>
        </property>
    </bean>
</beans>

----------------------------------------------------------------

create-ticket-oracle.sql    测试所需的建表语句

#
# TICKET DATABASE for Oracle 9i or later
#

/* un-comment when re-creating the database
DROP TABLE Seat_Plan_Seat CASCADE CONSTRAINTS;
DROP TABLE Seat_Status CASCADE CONSTRAINTS;
DROP TABLE Seat CASCADE CONSTRAINTS;
DROP TABLE Performance CASCADE CONSTRAINTS;
DROP TABLE Booking CASCADE CONSTRAINTS;
DROP TABLE Shows CASCADE CONSTRAINTS;
DROP TABLE Seating_Plan CASCADE CONSTRAINTS;
DROP TABLE Genre CASCADE CONSTRAINTS;
DROP TABLE Price_Band CASCADE CONSTRAINTS;
DROP TABLE Price_Structure CASCADE CONSTRAINTS;
DROP TABLE Purchase CASCADE CONSTRAINTS;
DROP TABLE Registered_User CASCADE CONSTRAINTS;
DROP TABLE Seat_Class CASCADE CONSTRAINTS;
*/

CREATE TABLE Booking (
  id INTEGER NOT NULL,
  date_made DATE,
  reserved_until TIMESTAMP,
  price DECIMAL(15, 2) NOT NULL,
  Purchase_id INTEGER,
  PRIMARY KEY(id));

CREATE TABLE Genre (
  id INTEGER NOT NULL,
  name VARCHAR(50),
  PRIMARY KEY(id));

CREATE TABLE Performance (
  id INTEGER NOT NULL,
  date_and_time TIMESTAMP,
  Price_Structure_id INTEGER,
  Show_id INTEGER,
  PRIMARY KEY(id));

CREATE TABLE Price_Band (
  id INTEGER NOT NULL,
  price DECIMAL(15, 2),
  Price_Structure_id INTEGER,
  Seat_Class_id INTEGER,
  PRIMARY KEY(id));

CREATE TABLE Price_Structure (
  id INTEGER NOT NULL,
  name VARCHAR(80),
  PRIMARY KEY(id));

CREATE TABLE Purchase (
  id INTEGER NOT NULL,
  authorization_code VARCHAR(32) NOT NULL,
  purchase_date DATE NOT NULL,
  email VARCHAR(100) NOT NULL,
  card_street VARCHAR(40) NOT NULL,
  card_line2 VARCHAR(40),
  card_city VARCHAR(40) NOT NULL,
  card_postcode VARCHAR(10) NOT NULL,
  delivery_street VARCHAR(40),
  delivery_line2 VARCHAR(40),
  delivery_city VARCHAR(40),
  delivery_postcode VARCHAR(10),
  Registered_User_id INTEGER,
  PRIMARY KEY(id));

CREATE TABLE Registered_User (
  id INTEGER NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(40) NOT NULL,
  card_street VARCHAR(40) NOT NULL,
  card_line2 VARCHAR(40),
  card_city VARCHAR(40) NOT NULL,
  card_postcode VARCHAR(10) NOT NULL,
  delivery_street VARCHAR(40),
  delivery_line2 VARCHAR(40),
  delivery_city VARCHAR(40),
  delivery_postcode VARCHAR(10),
  PRIMARY KEY(id));

CREATE TABLE Seat (
  id INTEGER NOT NULL,
  name VARCHAR(50) NOT NULL,
  row_or_box NUMERIC,
  row_pos NUMERIC,
  left_Seat_id INTEGER,
  right_Seat_id INTEGER,
  PRIMARY KEY(id));

CREATE TABLE Seat_Class (
  id INTEGER NOT NULL,
  code VARCHAR(5),
  description VARCHAR(200),
  PRIMARY KEY(id));

CREATE TABLE Seat_Plan_Seat (
  Seat_id INTEGER NOT NULL,
  Seating_Plan_id INTEGER NOT NULL,
  Seat_Class_id INTEGER NOT NULL,
  PRIMARY KEY(Seat_Class_id, Seating_Plan_id, Seat_id));

CREATE TABLE Seat_Status (
  Performance_id INTEGER NOT NULL,
  Seat_id INTEGER NOT NULL,
  Booking_id INTEGER,
  Price_Band_id INTEGER,
  PRIMARY KEY(Seat_id, Performance_id));

CREATE TABLE Seating_Plan (
  id INTEGER NOT NULL,
  name VARCHAR(50),
  PRIMARY KEY(id));

CREATE TABLE Shows (
  id INTEGER NOT NULL,
  name VARCHAR(50),
  Genre_id INTEGER,
  Seating_Plan_id INTEGER,
  PRIMARY KEY(id));

CREATE INDEX ix_Price_Structure
  ON Performance(Price_Structure_id);
ALTER TABLE Performance
  ADD CONSTRAINT fk_Price_Structure
  FOREIGN KEY (Price_Structure_id)
  REFERENCES Price_Structure (id);

CREATE INDEX ix_Show
  ON Performance(Show_id);
ALTER TABLE Performance
  ADD CONSTRAINT fk_Shows
  FOREIGN KEY (Show_id)
  REFERENCES Shows(id);

CREATE INDEX ix_Price_Structure2
  ON Price_Band(Price_Structure_id);
ALTER TABLE Price_Band
  ADD CONSTRAINT fk_Price_Structure2
  FOREIGN KEY (Price_Structure_id)
  REFERENCES Price_Structure (id);

CREATE INDEX ix_Seat_Class
  ON Price_Band(Seat_Class_id);
ALTER TABLE Price_Band
  ADD CONSTRAINT fk_Seat_Class
  FOREIGN KEY (Seat_Class_id)
  REFERENCES Seat_Class (id);

CREATE INDEX ix_Registered_User
  ON Purchase(Registered_User_id);
ALTER TABLE Purchase
  ADD CONSTRAINT fk_Registered_User
  FOREIGN KEY (Registered_User_id)
  REFERENCES Registered_User (id);

CREATE INDEX ix_left_Seat
  ON Seat(left_Seat_id);
ALTER TABLE Seat
  ADD CONSTRAINT fk_left_Seat
  FOREIGN KEY (left_Seat_id)
  REFERENCES Seat (id);

CREATE INDEX ix_right_Seat
  ON Seat(right_Seat_id);
ALTER TABLE Seat
  ADD CONSTRAINT fk_right_Seat
  FOREIGN KEY (right_Seat_id)
  REFERENCES Seat (id);

CREATE INDEX ix_Seat
  ON Seat_Plan_Seat(Seat_id);
ALTER TABLE Seat_Plan_Seat
  ADD CONSTRAINT fk_Seat
  FOREIGN KEY (Seat_id)
  REFERENCES Seat (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Seat_Class2
  ON Seat_Plan_Seat(Seat_Class_id);
ALTER TABLE Seat_Plan_Seat
  ADD CONSTRAINT fk_Seat_Class2
  FOREIGN KEY (Seat_Class_id)
  REFERENCES Seat_Class (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Seating_Plan
  ON Seat_Plan_Seat(Seating_Plan_id);
ALTER TABLE Seat_Plan_Seat
  ADD CONSTRAINT fk_Seating_Plan
  FOREIGN KEY (Seating_Plan_id)
  REFERENCES Seating_Plan (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Booking
  ON Seat_Status(Booking_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Booking
  FOREIGN KEY (Booking_id)
  REFERENCES Booking (id);

CREATE INDEX ix_Performance
  ON Seat_Status(Performance_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Performance
  FOREIGN KEY (Performance_id)
  REFERENCES Performance (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Price_Band
  ON Seat_Status(Price_Band_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Price_Band
  FOREIGN KEY (Price_Band_id)
  REFERENCES Price_Band (id);

CREATE INDEX ix_Seat2
  ON Seat_Status(Seat_id);
ALTER TABLE Seat_Status
  ADD CONSTRAINT fk_Seat2
  FOREIGN KEY (Seat_id)
  REFERENCES Seat (id)
  ON DELETE CASCADE;

CREATE INDEX ix_Genre
  ON Shows(Genre_id);
ALTER TABLE Shows
  ADD CONSTRAINT fk_Genre
  FOREIGN KEY (Genre_id)
  REFERENCES Genre (id);

CREATE INDEX ix_Seating_Plan2
  ON Shows(Seating_Plan_id);
ALTER TABLE Shows
  ADD CONSTRAINT fk_Seating_Plan2
  FOREIGN KEY (Seating_Plan_id)
  REFERENCES Seating_Plan (id);

CREATE INDEX ix_Purchase
  ON Booking(Purchase_id);
ALTER TABLE Booking
  ADD CONSTRAINT fk_Purchase
  FOREIGN KEY (Purchase_id)
  REFERENCES Purchase (id);