首页 > 代码库 > Spring JdbcTemplate查询实例

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.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 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

Spring JdbcTemplate查询实例