Spring JdbcTemplate查詢範例

這裡有幾個例子向您展示如何使用JdbcTemplate的query()方法來查詢或從資料庫提取資料。整個專案的目錄結構如下：

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;
	}
	
}

它傳遞給 queryForObject()方法，返回的結果將呼叫自定義 mapRow()方法的值匹配到屬性。

public Customer findByCustomerId(int custId){
		 
	String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
 
	Customer customer = (Customer)getJdbcTemplate().queryForObject(
			sql, new Object[] { custId }, new CustomerRowMapper());
		
	return customer;
}

1.2 BeanPropertyRowMapper

在Spring2.5中，帶有一個方便 RowMapper 實現所謂「BeanPropertyRowMapper」，它可以通過匹配行的名字的列值對映到一個屬性。只要確保這兩個屬性和列具有相同的名稱，如屬性「CUSTID'將匹配到列名為：」CUSTID'或下劃線「CUST_ID」。

public Customer findByCustomerId2(int custId){
		 
	String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
 
	Customer customer = (Customer)getJdbcTemplate().queryForObject(
			sql, new Object[] { custId }, 
			new BeanPropertyRowMapper(Customer.class));
	
	return customer;
}

2，查詢多行

現在，查詢或從資料庫中提取多行，並且將它轉換成一個列表。

2.1手動對映它

返回多行，RowMapper 不支援 queryForList()方法，需要手動對映它。

public List<Customer> findAll(){
		
	String sql = "SELECT * FROM CUSTOMER";
		 
	List<Customer> customers = new ArrayList<Customer>();
	
	List<Map> rows = getJdbcTemplate().queryForList(sql);
	for (Map row : rows) {
		Customer customer = new Customer();
		customer.setCustId((Long)(row.get("CUST_ID")));
		customer.setName((String)row.get("NAME"));
		customer.setAge((Integer)row.get("AGE"));
		customers.add(customer);
	}
		
	return customers;
}

2.2 BeanPropertyRowMapper

最簡單的解決方案是使用 BeanPropertyRowMapper 類。

public List<Customer> findAll(){
		
	String sql = "SELECT * FROM CUSTOMER";
		
	List<Customer> customers  = getJdbcTemplate().query(sql,
			new BeanPropertyRowMapper(Customer.class));
		
	return customers;
}

3.查詢單值

在這個例子中，展示了如何從資料庫中查詢或提取單個列值。

3.1單列名

它顯示了如何查詢單個列名作為字串。

public String findCustomerNameById(int custId){
		
	String sql = "SELECT NAME FROM CUSTOMER WHERE CUST_ID = ?";
		 
	String name = (String)getJdbcTemplate().queryForObject(
			sql, new Object[] { custId }, String.class);
	
	return name;
		
}

3.2、行總數

它展示了如何從資料庫中查詢行的總數。

public int findTotalCustomer(){
		
	String sql = "SELECT COUNT(*) FROM CUSTOMER";
		 
	int total = getJdbcTemplate().queryForInt(sql);
				
	return total;
}

執行它

package com.tw511.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 JdbcTemplateApp 
{
    public static void main( String[] args )
    {
    	 ApplicationContext context = 
    		new ClassPathXmlApplicationContext("Spring-Customer.xml");
    	 
         CustomerDAO customerDAO = (CustomerDAO) context.getBean("customerDAO");

         Customer customerA = customerDAO.findByCustomerId(1);
         System.out.println("Customer A : " + customerA);
         
         Customer customerB = customerDAO.findByCustomerId2(1);
         System.out.println("Customer B : " + customerB);
         
         List<Customer> customerAs = customerDAO.findAll();
         for(Customer cust: customerAs){
         	 System.out.println("Customer As : " + customerAs);
         }
        
         List<Customer> customerBs = customerDAO.findAll2();
         for(Customer cust: customerBs){
         	 System.out.println("Customer Bs : " + customerBs);
         }
         
         String customerName = customerDAO.findCustomerNameById(1);
         System.out.println("Customer Name : " + customerName);
         
         int total = customerDAO.findTotalCustomer();
         System.out.println("Total : " + total);
         
    }
}

總結

JdbcTemplate類，附帶了很多有用的過載查詢方法。它提醒參考現有的查詢方法在建立自己的自定義查詢方法之前，因為 Spring 已經做給你了。

下載程式碼 – http://pan.baidu.com/s/1gecQHmN