Spring SimpleJdbcTemplate查询例子

1. Querying for Single Row

Here’s two ways to show you how to query or extract a single row from database, and convert it into a model class.

1.1 Custom RowMapper

In general, It’s always recommend to implement the RowMapper interface to create a custom RowMapper to suit your needs.

package com.mkyong.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 CustomerRowMapper(), custId);
 
	return customer;
}

1.2 BeanPropertyRowMapper
In SimpleJdbcTemplate, you need to use ‘ParameterizedBeanPropertyRowMapper’ instead of ‘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. Querying for Multiple Rows

Query or extract multiple rows from database, and convert it into a List.
2.1 ParameterizedBeanPropertyRowMapper

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

3. Querying for a Single Value

Query or extract a single column value from database.
3.1 Single column name

It shows how to query a single column name as String.

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 Total number of rows

It shows how to query a total number of rows from database.

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

下面是运行例子.

package com.mkyong.common;
 
import java.util.ArrayList;
import java.util.List;
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.mkyong.customer.dao.CustomerDAO;
import com.mkyong.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);
 
    }
}

query object list

	public List<CitiriskApp> getCitiriskApp(){
        SimpleJdbcTemplate simpleJdbc = new SimpleJdbcTemplate(getJdbcTemplate().getDataSource()); 
        String querySQL = "select * from citirisk_application";
        return simpleJdbc.query(querySQL, new ParameterizedRowMapper<CitiriskApp>() {

			@Override
			public CitiriskApp mapRow(ResultSet rs, int i) throws SQLException {
				CitiriskApp citiriskApp = new CitiriskApp();
				citiriskApp.setAppId(rs.getInt("app_id"));
				citiriskApp.setCsiId(rs.getInt("csi_id"));
				citiriskApp.setShortName(StringUtils.cleanSpace(rs.getString("short_name")));
				citiriskApp.setLongName(StringUtils.cleanSpace(rs.getString("long_name")));
				citiriskApp.setSmtSOEID(StringUtils.cleanSpace(rs.getString("smt_soe_id")));
				citiriskApp.setTechOwnerSOEID(StringUtils.cleanSpace(rs.getString("tech_owner_soe_id")));
				citiriskApp.setCitiriskContactSOEID(StringUtils.cleanSpace(rs.getString("citirisk_contact_soe_id")));
				citiriskApp.setRiskArchOwnerSOEID(StringUtils.cleanSpace(rs.getString("risk_arch_owner_soe_id")));
				citiriskApp.setBizOwnerSOEID(StringUtils.cleanSpace(rs.getString("biz_owner_soe_id")));				
				return citiriskApp;
			}
		});
	}

insert exmaple

	public void insert(Customer customer){
 
		String sql = "INSERT INTO CUSTOMER " +
			"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
 
		jdbcTemplate = new JdbcTemplate(dataSource);
 
		jdbcTemplate.update(sql, new Object[] { customer.getCustId(),
			customer.getName(),customer.getAge()  
		});
 
	}

Conclusion

The SimpleJdbcTemplate isn’t a replacement for JdbcTemplate, it’s just a java5-friendly supplement to it.