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 the Author

author image
mkyong
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

avatar
7 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
jessyGeorgestepriverfiftyJagadeeshViktor Recent comment authors
newest oldest most voted
jessy
Guest
jessy

ParameterizedRowMapper is deprecated rt?

George
Guest
George

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; } });… Read more »

stepriverfifty
Guest
stepriverfifty

SimpleJdbcTemplate is deprecated. Use JdbcTemplate with explicit parametrization instead.

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

Jagadeesh
Guest
Jagadeesh

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

Viktor
Guest
Viktor

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
Guest
Daniel Capra

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.

trackback
Spring Tutorials | Tutorials

[…] SimpleJdbcTemplate Querying examples More user friendly and simple way to query or extract data from database. […]