Main Tutorials

Spring SimpleJdbcTemplate Querying examples

Here are few examples to show how to use SimpleJdbcTemplate query() methods to query or extract data from database. In JdbcTemplate query(), you need to manually cast the returned result to desire object type, and pass an Object array as parameters. In SimpleJdbcTemplate, it is more user friendly and simple.

jdbctemplate vesus simplejdbctemplate
Please compare this SimpleJdbcTemplate example with this JdbcTemplate example.

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 CustomerParameterizedRowMapper(), 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;
}

Run it


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

Conclusion

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

Download Source Code

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
6 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
jessy
4 years ago

ParameterizedRowMapper is deprecated rt?

George
7 years ago

Hello, I´m new with Spring and all the modules, i have a question, what should i do or whats the correct syntax for take 2 variables i.e:
select Name, MAIL, ADDRESS from user where id=? and mail=?;

thats my query, how i can catch or send those variables,
I´ve got this at first:
@Override
public List filterByClase(String claseId) {
log.debug(“consultando Plataformas en base a la clase…”);

List bibliotecafc = jdbcTemplate.query(
“select distinct BIBLIOTECAID from CONSULTA where CLASEID=?”,
new Object[]{claseId},
new RowMapper() {
public Biblioteca mapRow(ResultSet rs, int rowNum) throws SQLException {
Biblioteca bibliotecafbc = new Biblioteca(rs.getString(1));
return bibliotecafbc;
}
});
log.debug(“sistemas: {}”, bibliotecafc.toString());
return bibliotecafc;
}

But i´ve noticed that this querie doesnt have the correct filter, then i realized that my query requires another filter in my case i need to do a filter by the system too.
the query will end like this:
select distinct BIBLIOTECAID from CONSULTA where CLASEID=? and SISTEMAID=?;
Thanks for the help!

Sorry if my English is not so good.
Greetings.

stepriverfifty
10 years ago

SimpleJdbcTemplate is deprecated. Use JdbcTemplate with explicit parametrization instead.

e.g class StuffMapper implements RowMapper {…} should now be
class StuffMapper implements RowMapper<Stuff> {…}

Jagadeesh
10 years ago

In SimpleJdbcTemplate I think we should implement ParameterizedRowMapper… Thank u sir for providing this tutorial

Viktor
10 years ago

Hi what you say about this
SELECT cpn.id, cpn.name, cpn.type FROM cpe_parameter c, cpe_parameter_name cpn WHERE cpn.name ? REGEXP ‘^\?.[[:alnum:][.underscore.]]+[(.|[:alnum:][.underscore.])]$’ AND c.cpe_id = ? AND c.name_id =cpn.id

how to pass a parameter to a regular expression?

Daniel Capra
11 years ago

In the interests of being kind to the GC, would it be better to create a single RowMapper in your DAO at startup, as an anonymous inner class, and then reuse it; rather than creating a new object in every call? I believe this would still be thread-safe.