Spring JdbcTemplate查询实例

package com.h3.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.h3.common;

import java.util.arraylist;
import java.util.list;

import org.springframework.context.applicationcontext;
import org.springframework.context.support.classpathxmlapplicationcontext;
import com.h3.customer.dao.customerdao;
import com.h3.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