这里有几个例子向您展示如何使用JdbcTemplate的query()方法来查询或从数据库提取数据。整个项目的目录结构如下：
1.查询单行数据
这里有两种方法来查询或从数据库中提取单行记录，并将其转换成一个模型类。
1.1 自定义RowMapper
在一般情况下，它总是建议实现 RowMapper 接口来创建自定义的RowMapper，以满足您的需求。
package com.gp6.jdbc.template.rowMapper.bean;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
@SuppressWarnings("rawtypes")
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()方法的值匹配到属性。
package com.gp6.jdbc.template.rowMapper.dao.impl;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.gp6.jdbc.template.rowMapper.bean.Customer;
import com.gp6.jdbc.template.rowMapper.bean.CustomerRowMapper;
import com.gp6.jdbc.template.rowMapper.dao.CustomerDAO;
public class CustomerDAOImpl extends JdbcDaoSupport implements CustomerDAO {
//no need to set datasource here
public void insert(Customer customer){
String sql = "INSERT INTO CUSTOMER " +
"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
//使用DataSource不需要下面这行代码,使用Template需要
//jdbcTemplate = new JdbcTemplate(dataSource);
getJdbcTemplate().update(sql, new Object[] { customer.getCustId(),
customer.getName(),customer.getAge()
});
}
@SuppressWarnings("unchecked")
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<String, Object>> rows = getJdbcTemplate().queryForList(sql);
for (Map row : rows) {
Customer customer = new Customer();
customer.setCustId((Integer)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";
//1 手动映射它
/*List<Customer> customers = new ArrayList<Customer>();
List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
for (Map row : rows) {
Customer customer = new Customer();
customer.setCustId(Integer.valueOf(row.get("CUST_ID").toString()));
customer.setName((String)row.get("NAME"));
customer.setAge((Integer)row.get("AGE"));
customers.add(customer);
}*/
//2 使用 BeanPropertyRowMapper
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;
}
4 行总数, 它展示了如何从数据库中查询行的总数。
public int findTotalCustomer(){
String sql = "SELECT COUNT(*) FROM CUSTOMER";
int total = getJdbcTemplate().queryForInt(sql);
return total;
}
测试类
package com.gp6.jdbc.template.rowMapper;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.gp6.jdbc.template.rowMapper.bean.Customer;
import com.gp6.jdbc.template.rowMapper.dao.CustomerDAO;
public class Test {
public static void main( String[] args ) {
ApplicationContext context = new ClassPathXmlApplicationContext("com/gp6/jdbc/template/rowMapper/etc/applicationContext.xml");
CustomerDAO customerDAO = (CustomerDAO) context.getBean("customerDAO");
Customer customer = new Customer(4, "RowMapper",24);
customerDAO.insert(customer);
// 1.1 查询单行,手动映射
Customer customer1 = customerDAO.findByCustomerId(4);
System.out.println(customer1);
// 1.2查询单行,使用BeanPropertyRowMapper
Customer customer2 = customerDAO.findByCustomerId2(4);
System.out.println(customer2);
// 2 查询多条数据,转为List
List<Customer> list = customerDAO.findAll();
System.out.println(list);
// 3 查询单列的值
String name = customerDAO.findCustomerNameById(4);
System.out.println(name);
// 4 行总数, 它展示了如何从数据库中查询行的总数。
int total = customerDAO.findTotalCustomer();
System.out.println(total);
}
}
输出
com.gp6.jdbc.template.rowMapper.bean.Customer@8c3c315
com.gp6.jdbc.template.rowMapper.bean.Customer@54c01e99
[com.gp6.jdbc.template.rowMapper.bean.Customer@193936e1]
RowMapper
1
总结
JdbcTemplate类，附带了很多有用的重载查询方法。它提醒参考现有的查询方法在创建自己的自定义查询方法之前，因为 Spring 已经做给你了。
网友评论