Spring SimpleJdbcTemplate查詢示例

這裏有幾個例子來說明如何使用SimpleJdbcTemplate query()方法來查詢或從數據庫中提取數據。在 JdbcTemplate query() 方法，需要手動轉換返回的結果轉換爲一個目標對象類型，並傳遞一個對象數組作爲參數。在SimpleJdbcTemplate類，它是更加人性化和簡單。

jdbctemplate VS simplejdbctemplate
請比較 JdbcTemplate類的示例和SimpleJdbcTemplate類的示例。

1.查詢單行

---

這裏有向你展示瞭如何查詢或從數據庫中提取單行的兩種方式，並將其轉換成一個模型類。

1.1 自定義RowMapper

在一般情況下，它總是建議來實現 RowMapper 接口來創建自定義的RowMapper，以滿足您的需求。

package com.yiibai.customer.model;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class CustomerRowMapper implements RowMapper
{
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Customer customer = new Customer();
customer.setCustId(rs.getInt("CUST_ID"));
customer.setName(rs.getString("NAME"));
customer.setAge(rs.getInt("AGE"));
return customer;
}

}

public Customer findByCustomerId(int custId){

String sql = "SELECT \* FROM CUSTOMER WHERE CUST\_ID = ?";

Customer customer = getSimpleJdbcTemplate().queryForObject(
        sql,  new CustomerParameterizedRowMapper(), custId);

return customer;

}

1.2 BeanPropertyRowMapper

在SimpleJdbcTemplate類，需要使用「ParameterizedBeanPropertyRowMapper' 代替 'BeanPropertyRowMapper」。

public Customer findByCustomerId2(int custId){

String sql = "SELECT \* FROM CUSTOMER WHERE CUST\_ID = ?";

Customer customer = getSimpleJdbcTemplate().queryForObject(sql,
      ParameterizedBeanPropertyRowMapper.newInstance(Customer.class), custId);

return customer;

}

2，查詢多行

---

從數據庫查詢或提取多行記錄，並將其轉換成一個列表。

2.1 ParameterizedBeanPropertyRowMapper

public List findAll(){

String sql = "SELECT \* FROM CUSTOMER";

List<Customer> customers = 
    getSimpleJdbcTemplate().query(sql, 
       ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));

return customers;

}

3.查詢單值

---

查詢或提取數據庫中的單個列的值。

3.1單列名

---

它顯示瞭如何查詢單個列名作爲字符串。

public String findCustomerNameById(int custId){

String sql = "SELECT NAME FROM CUSTOMER WHERE CUST\_ID = ?";

String name = getSimpleJdbcTemplate().queryForObject(
    sql, String.class, custId);

return name;

}

3.2、行總數

---

它展示瞭如何從數據庫中查詢行的總數。

public int findTotalCustomer(){

String sql = "SELECT COUNT(\*) FROM CUSTOMER";

int total = getSimpleJdbcTemplate().queryForInt(sql);

return total;

}

運行它

package com.yiibai.common;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.yiibai.customer.dao.CustomerDAO;
import com.yiibai.customer.model.Customer;

public class SimpleJdbcTemplateApp
{
public static void main( String[] args )
{
ApplicationContext context =
new ClassPathXmlApplicationContext("Spring-Customer.xml");

     CustomerDAO customerSimpleDAO = 
            (CustomerDAO) context.getBean("customerSimpleDAO");

     Customer customerA = customerSimpleDAO.findByCustomerId(1);
     System.out.println("Customer A : " + customerA);

     Customer customerB = customerSimpleDAO.findByCustomerId2(1);
     System.out.println("Customer B : " + customerB);

     List<Customer> customerAs = customerSimpleDAO.findAll();
     for(Customer cust: customerAs){
          System.out.println("Customer As : " + customerAs);
     }

     List<Customer> customerBs = customerSimpleDAO.findAll2();
     for(Customer cust: customerBs){
          System.out.println("Customer Bs : " + customerBs);
     }

     String customerName = customerSimpleDAO.findCustomerNameById(1);
     System.out.println("Customer Name : " + customerName);

     int total = customerSimpleDAO.findTotalCustomer();
     System.out.println("Total : " + total);

}

}

總結

SimpleJdbcTemplate 是不能代替 JdbcTemplate 的，它只是一個Java5的友好補充它。

下載代碼 –  http://pan.baidu.com/s/1eRisz6M