spring使用JdbcDaoSupport中封装的JdbcTemplate进行query

1.Dept

package cn.hxex.springcore.jdbc;

public class Dept {

    private Integer deptNo;
    private String dName;
    private String loc;
    
    public Integer getDeptNo() {
        return deptNo;
    }
    public void setDeptNo(Integer deptNo) {
        this.deptNo = deptNo;
    }
    public String getDName() {
        return dName;
    }
    public void setDName(String name) {
        dName = name;
    }
    public String getLoc() {
        return loc;
    }
    public void setLoc(String loc) {
        this.loc = loc;
    }
    
    public String toString() {
        StringBuffer buf = new StringBuffer();
        
        buf.append( "deptNo:\t" ).append( getDeptNo() ).append( "\t" );
        buf.append( "dName:\t" ).append( getDName() ).append( "\t" );
        buf.append( "loc:\t" ).append( getLoc() ).append( "\t" );
        
        return buf.toString();
    }
}

2.IDeptDao

package cn.hxex.springcore.jdbc;

import java.util.List;

public interface IDeptDao {

    public List getDepts();
    
    public List getDeptsByNo(int deptno);
}

3.DeptDao

package cn.hxex.springcore.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class DeptDao extends JdbcDaoSupport implements IDeptDao {

    public List getDepts() {
        String sql = "SELECT deptno, dname, loc FROM dept";
        
        return super.getJdbcTemplate().query( sql, new DeptRowMapper() );
    }
    
    public List getDeptsByNo(final int deptno){
        final String sql = "SELECT deptno, dname, loc FROM dept where deptno=?";
        
        return super.getJdbcTemplate().query(new PreparedStatementCreator(){

            @Override
            public PreparedStatement createPreparedStatement(Connection connection)
                    throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setInt(1, deptno);
                return ps;
            }
            
            
        }, new RowMapper(){

            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Dept dept = new Dept();
                dept.setDeptNo( new Integer( rs.getString(1) ) );
                dept.setDName( rs.getString( 2 ) );
                dept.setLoc( rs.getString( 3 ) );
                return dept;
            }
            
        });
        
        
        
    }
    
    class DeptRowMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Dept dept = new Dept();
            dept.setDeptNo( new Integer( rs.getString(1) ) );
            dept.setDName( rs.getString( 2 ) );
            dept.setLoc( rs.getString( 3 ) );
            return dept;
        }
        
    }
    
    
}

4.JdbcDaoSupportMain

package cn.hxex.springcore.jdbc;

import java.util.List;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;

public class JdbcDaoSupportMain {

    public static void main(String[] args) {
        
        ClassPathResource resource = new ClassPathResource("cn/hxex/springcore/jdbc/JdbcDaoSupportBeans.xml");
        BeanFactory factory = new XmlBeanFactory(resource);

        IDeptDao dao = (IDeptDao)factory.getBean( "deptDao" );
        //List depts = dao.getDepts();
        List depts = dao.getDeptsByNo(10);
        
        for( int i=0; i<depts.size(); i++ ) {
            System.out.println( depts.get( i ) );
        }
    }
}

5.JdbcDaoSupportBeans.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" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

    <!--  初始化数据源 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
      <property name="url">
        <value>jdbc:oracle:thin:@localhost:1521:XE</value>
      </property>
      <property name="username"><value>jeffrey</value></property>
      <property name="password"><value>jeffrey</value></property>
    </bean>
    
    <bean id="deptDao" class="cn.hxex.springcore.jdbc.DeptDao">
        <property name="dataSource" ref="dataSource" />    
    </bean>
</beans>

 

备注：以上部分代码来自陈天河编著的某书