spring JDBC記述

 、Spring JDBC   

     Spring             JdbcTemplate  JDBC  ，DataSource,JdbcTemplate    Bean       xml    ，JdbcTemplate        DataSource，    Dao      JdbcDaoSupport,     JdbcTemplate，     JdbcTemplate，JdbcTemplate         ，  Dao      JdbcTemplate；

 

Xml   
<!--         Oracle              -->  
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>  
        <property name="url" value="jdbc:oracle:thin:@oracle.devcake.co.uk:1521:INTL"/>  
        <property name="username" value="sa"/>  
        <property name="password" value=""/>  
</bean>  
  
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
        <property name="dataSource" ref="dataSource"/>  
 </bean>  
  
<bean id="customerDao" class="JdbcCustomerDao" >  
         <property name="dataSource" ref="dataSource"/>  
</bean>  

   jdbcTemplate        Dao、    JdbcDaoSupport，  ：

Java   
public class JdbcCustomerDao extends JdbcDaoSupport implements CustomerDao {   
}   
  
public class JdbcCustomerDao implements CustomerDao {   
  
          private JdbcTemplate jdbcTemplate   
  
         public void setJdbcTemplate()JdbcTemplate jdbcTemplate{   
               this.jdbcTemplate=jdbcTemplate   
         }   
}  

public class JdbcCustomerDao extends JdbcDaoSupport implements CustomerDao {
}

public class JdbcCustomerDao implements CustomerDao {

          private JdbcTemplate jdbcTemplate

         public void setJdbcTemplate()JdbcTemplate jdbcTemplate{
               this.jdbcTemplate=jdbcTemplate
         }
} 


 、 JdbcTemplate           JDBC  ：





2.1、List query(String sql,Ojbect[] args,RowMapper rowMapper)

       ：     ，sql    sql  ，args sql     ，rowMapper           java     list，     ，  ：

Java   
public List<Book> queryByAuthor(String author) {   
        String sql = "select * from book where author=?";   
        Collection c = getJdoTemplate().find(sql,   
                new Object[] { author },new BookRowMapper());   
        List<Book> books = new ArrayList<Book>();   
        books.addAll(c);   
        return books;   
}   
  
class BookRowMapper implements RowMapper{   
     public Object mapRow(ResultSet res, int index) throws SQLException {   
          Book book = new Book();   
          book.setId(rs.getInt("id"));   
          //  set   
       return book；   
    }   
}  

public List<Book> queryByAuthor(String author) {
        String sql = "select * from book where author=?";
        Collection c = getJdoTemplate().find(sql,
                new Object[] { author },new BookRowMapper());
        List<Book> books = new ArrayList<Book>();
        books.addAll(c);
        return books;
}

class BookRowMapper implements RowMapper{
     public Object mapRow(ResultSet res, int index) throws SQLException {
          Book book = new Book();
          book.setId(rs.getInt("id"));
          //  set
       return book；
    }
} 

     、  、        ，    ，       spring api：

 

Java   
//            
public long getAverageAge() {   
    return getJdbcTemplate().queryForLong("SELECT AVG(age) FROM employee");   
  }   
//         
public int getTotalNumberOfEmployees() {   
    return getJdbcTemplate().queryForInt("SELECT COUNT(0) FROM employees");   
  }   
  
//       
this.jdbcTemplate.update(   
        "insert into t_actor (first_name, surname) values (?, ?)",    
        new Object[] {"Leonor", "Watling"});  

//         
public long getAverageAge() {
    return getJdbcTemplate().queryForLong("SELECT AVG(age) FROM employee");
  }
//      
public int getTotalNumberOfEmployees() {
    return getJdbcTemplate().queryForInt("SELECT COUNT(0) FROM employees");
  }

//    
this.jdbcTemplate.update(
        "insert into t_actor (first_name, surname) values (?, ?)", 
        new Object[] {"Leonor", "Watling"});
 

 

 2.2、spring 2.5   ，   jdbc ORM：BeanPropertyRowMapper

 

                 RowMapper，       java  。Spring2.5        ，         RowMapper，              ：ParameterizedRowMapper，ParameterizedBeanPropertyRowMapper，    java        resultset     java  ，          java       ，      ，     apache  BeanUtil，        spring        ，      。

 

 

Java   
//  ParameterizedBeanPropertyRowMapper   
@SuppressWarnings({"unchecked"})   
  public List<Customer> getAll() {   
      return getJdbcTemplate().query("select * from t_customer", ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));   
  }   
  
//  BeanPropertyRowMapper   
@SuppressWarnings({"unchecked"})   
  public List<Customer> getAll() {   
      return getJdbcTemplate().query("select * from t_customer", new BeanPropertyRowMapper(Customer.class));   
  }  

//  ParameterizedBeanPropertyRowMapper
@SuppressWarnings({"unchecked"})
  public List<Customer> getAll() {
      return getJdbcTemplate().query("select * from t_customer", ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));
  }

//  BeanPropertyRowMapper
@SuppressWarnings({"unchecked"})
  public List<Customer> getAll() {
      return getJdbcTemplate().query("select * from t_customer", new BeanPropertyRowMapper(Customer.class));
  }
 

 

  ：ParameterizedBeanPropertyRowMapper BeanPropertyRowMapper  。                       ；

 

2.3、spring JDBC    

      jdbcTemplate.batchUpdate(final String[] sql) ，API  ：Issue multiple SQL updates on a single JDBC Statement using batching，       ：    sql   、  、       Statement 。    。

   jdbcTemplate.batchUpdate(String sql, final BatchPreparedStatementSetter pss),   JDBC PreparedStatement，         。

             ，    :

Java   
final int count = 2000;   
    final List<String> firstNames = new ArrayList<String>(count);   
    final List<String> lastNames = new ArrayList<String>(count);   
    for (int i = 0; i < count; i++) {   
      firstNames.add("First Name " + i);   
      lastNames.add("Last Name " + i);   
    }   
    jdbcTemplate.batchUpdate(   
            "insert into customer (id, first_name, last_name, last_login, comments) values (?, ?, ?, ?, ?)",   
            new BatchPreparedStatementSetter() {   
           // prepared statement    。                    
        public void setValues(PreparedStatement ps, int i) throws SQLException {   
                ps.setLong(1, i + 10);   
                ps.setString(2, firstNames.get(i));   
                ps.setString(3, lastNames.get(i));   
                ps.setNull(4, Types.TIMESTAMP);   
                ps.setNull(5, Types.CLOB);   
              }   
              //             
          public int getBatchSize() {   
                   return count;   
              }   
            });   
  }  

final int count = 2000;
    final List<String> firstNames = new ArrayList<String>(count);
    final List<String> lastNames = new ArrayList<String>(count);
    for (int i = 0; i < count; i++) {
      firstNames.add("First Name " + i);
      lastNames.add("Last Name " + i);
    }
    jdbcTemplate.batchUpdate(
            "insert into customer (id, first_name, last_name, last_login, comments) values (?, ?, ?, ?, ?)",
            new BatchPreparedStatementSetter() {
           // prepared statement    。                 
        public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setLong(1, i + 10);
                ps.setString(2, firstNames.get(i));
                ps.setString(3, lastNames.get(i));
                ps.setNull(4, Types.TIMESTAMP);
                ps.setNull(5, Types.CLOB);
              }
              //          
          public int getBatchSize() {
                   return count;
              }
            });
  }
 

 

  BatchSqlUpdate  SqlUpdate    ，     、  、      ，    PreparedStatement，      ，         batchSize，      flush        。  ：         ，              ，              reset。

               BatchSqlUpdate，       。    ：

Java   
class BatchInsert extends BatchSqlUpdate {   
  private static final String SQL = "insert into t_customer (id, first_name, last_name, last_login, "  
      + "comments) values (?, ?, ?, ?, null)";   
  
  BatchInsert(DataSource dataSource) {   
    super(dataSource, SQL);   
    declareParameter(new SqlParameter(Types.INTEGER));   
    declareParameter(new SqlParameter(Types.VARCHAR));   
    declareParameter(new SqlParameter(Types.VARCHAR));   
    declareParameter(new SqlParameter(Types.TIMESTAMP));   
  
    setBatchSize(10);   
  }   
  
}  

class BatchInsert extends BatchSqlUpdate {
  private static final String SQL = "insert into t_customer (id, first_name, last_name, last_login, "
      + "comments) values (?, ?, ?, ?, null)";

  BatchInsert(DataSource dataSource) {
    super(dataSource, SQL);
    declareParameter(new SqlParameter(Types.INTEGER));
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.TIMESTAMP));

    setBatchSize(10);
  }

}
 

Java   
int count = 5000;   
    for (int i = 0; i < count; i++) {   
      batchInsert.update(new Object[] { i + 100L, "a" + i, "b" + i, null });   
    }  

int count = 5000;
    for (int i = 0; i < count; i++) {
      batchInsert.update(new Object[] { i + 100L, "a" + i, "b" + i, null });
    }