Spring JdbcTemplate Querying examples

Here are few examples to show you how to use JdbcTemplate query() methods to query or extract data from database.

1. Querying for Single Row

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

1.1 Custom RowMapper

In general, It’s always recommended 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;
	}
 
}

Pass it to queryForObject() method, the returned result will call your custom mapRow() method to match the value into the properly.

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

In Spring 2.5, comes with a handy RowMapper implementation called ‘BeanPropertyRowMapper’, which can maps a row’s column value to a property by matching their names. Just make sure both the property and column has the same name, e.g property ‘custId’ will match to column name ‘CUSTID’ or with underscores ‘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. Querying for Multiple Rows

Now, query or extract multiple rows from database, and convert it into a List.

2.1 Map it manually

In mutiple return rows, RowMapper is not supported in queryForList() method, you need to map it manually.

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

The simplest solution is using the BeanPropertyRowMapper class.

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

3. Querying for a Single Value

In this example, it shows how to 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 = (String)getJdbcTemplate().queryForObject(
			sql, new Object[] { custId }, String.class);
 
	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 = getJdbcTemplate().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 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);
 
    }
}

Conclusion

The JdbcTemplate class, comes with many useful overloaded query methods. It’s advise to refer to the existing query method before you create own customize query method, because Spring may done it for you already.

Download Source Code

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • Pingback: pay day loans()

  • Pingback: water ionizer()

  • Pingback: bedste laan lige nu()

  • Pingback: parking()

  • Pingback: laan penge nu uden sikkerhed()

  • Pingback: lan penge online()

  • Pingback: mobile porn()

  • Pingback: stop parking()

  • Pingback: DIRECTV packages()

  • Pingback: She fellates your sausage while you her to()

  • Pingback: watch tv show episodes()

  • Pingback: tv online, online tv()

  • Pingback: Blue Coaster33()

  • Pingback: Java?RDB?Mapping-Framework????? Vol.5 | frenzy()

  • Shivaji

    You made mistake in JdbcTemplateApp.java file.

    Here is the code snippet:

    List customerAs = customerDAO.findAll();
    for(Customer cust: customerAs){
    System.out.println(“Customer As : ” + customerAs);
    }

    List customerBs = customerDAO.findAll2();
    for(Customer cust: customerBs){
    System.out.println(“Customer Bs : ” + customerBs);
    }

    Problem:
    List of customers will be printed multiple times(eqaul to total number of customers).

    Solution:
    There should be “cust” to printed instead of “customerAs” or “customerBs”.
    OR
    Simply remove the for loops.

  • Darin

    I am trying to set the Parent List in a ParameterizedRowMapper how is this written or approached. I have two Objects one for parent and one for children however children contains a ListThe parents for each child are stored in a separate table in the database and the mapping is 1 – many.

    The select for the records for the parents will be done in a separate ResultSet. Will the mapping have to be done separately (separate ParameterizedRowMapper), if so how will i have to write the ParameterizedRowMapper this is the major concern how ParameterizedRowMapper is written to accommodate a list items.

    public static class ChildrenMapper implements ParameterizedRowMapper{

    public Children mapRow(ResultSet rs, int rowNum) throws SQLException {
    Children child = new Children();
    child.setFirstName(rs.getString(“firstName”));
    child.setLastName(rs.getString(“lastName”));
    //a child can have many Parents or gaurdians
    child.setParent(List);

    return child;
    }
    }

  • Mike

    Interesting…it keeps removing the generics from the declaration from what is being posted.

  • Mike

    List<Map > rows = jdbcTemplate.queryForList(sql);

  • Mike

    I think there is an error in the findAll (Manually) method. Here is how I think ‘rows’ should be declared.

    List<Map > rows = jdbcTemplate.queryForList(sql);

  • Najum

    Nice thx

  • Me

    To map multiple rows you should take a look at the

    ResultSetExtractor

    interface.

  • Anubhab

    Hey do you know which form of jdbctemplate.query will not throw any exception when no rows are fetched from DB??

  • Pingback: Cannot run query without RowMapper()

  • Dipak C

    Hi, I am getting problem while reading the Resultset of queryForList

    jdbcQuery=”SELECT sp.rollNo rollno, sa.studentName stname, c.shortCode AS shortcode,CONCAT(SUM(sa.present) , ‘/’,COUNT(sa.present),’ (‘, ROUND(SUM(sa.present)*100/COUNT(sa.present),2),’%)’) AS percentage FROM studentattendance sa,studentprofile sp,course c,lecture l WHERE sa.studentId=sp.id AND c.type=’Theory’ AND sp.division_id=1 AND l.startDate >= ‘2012-01-01′ AND l.endDate <= '2012-02-16' AND sa.lectureId=l.id AND l.course_id=c.id GROUP BY sa.studentName,c.name"

    geting object address
    List<Map> resultSetList = jdbcTemplate.queryForList(jdbcQuery);

    Iterator<Map> qdataMapItr = resultSetList.iterator();
    String checkString=””;
    Map courseAttendMap=null;
    while(qdataItr.hasNext())
    {
    Map mapobj = qdataItr.next();
    System.out.println(” shortCode:”+mapobj.get(“shortcode”)+ ” percentage:”+mapobj.get(“percentage”));

    }

    OUTPUT:
    shortCode:APM(TH) percentage:[B@3435

    Why it is so? i dont understand. I am able read shortcode,rollno,stname but while getting percentage its giving Object address…

    please help!!!!!!!!!!!!!!!!!!

    • Dipak C

      on executing the above query on query browser giving following output. I am using mysql.

      rollno stname shortcode percentage
      21A01 ADHIKARI NILESH MADHUKAR APM(TH) 8/14 (57.14%)
      21A01 ADHIKARI NILESH MADHUKAR CT(TH) 13/16 (81.25%)
      21A01 ADHIKARI NILESH MADHUKAR FM-1(TH) 20/23 (86.96%)
      21A01 ADHIKARI NILESH MADHUKAR SA-1(TH) 23/27 (85.19%)
      21A01 ADHIKARI NILESH MADHUKAR SURVEYING(TH) 12/12 (100.00%)
      21A02 AHER AMIT ANNASAHEB BP(TH) 9/14 (64.29%)

      please help!!!

  • Priyank

    If my query is like :

    String sql = “SELECT * FROM CUSTOMER where name in (‘abc’,’xyz’…)”;

    what should be the code ?

  • Pingback: Spring JdbcTemplate Querying Example | Kaan Mutlu's Blog()

  • Anil

    2.1 Map it manually shows a nice manual way

    Alternative could be like this below and get a list of customers. Although we should use query instead of queryForObject

    List getJdbcTemplate().query(
    sql, new Object[] { custId }, new CustomerRowMapper());

  • Roger Pc

    Spring MVC DataSource

    Data base

    CREATE TABLE VEHICLE ( 
        VEHICLE_NO    VARCHAR(10)    NOT NULL, 
        COLOR         VARCHAR(10), 
        WHEEL         INT, 
        SEAT          INT, 
        PRIMARY KEY (VEHICLE_NO) 
    );

    Model

    public class Vehicle {
        private String vehicleNo;
        private String color; 
        private int wheel; 
        private int seat;
     
        public Vehicle(String vehicleNo, String color, int wheel, int seat) {
            this.vehicleNo = vehicleNo;
            this.color = color;
            this.wheel = wheel;
            this.seat = seat;
        }
        // get and set ...
    }

    DAO

    public interface VehicleDao {
     
        public void setDataSource(DataSource dataSource);    
        public void insert(Vehicle vehicle); 
        public void update(Vehicle vehicle); 
        public void delete(Vehicle vehicle); 
        public Vehicle findByVehicleNo(String vehicleNo); 
     
    }

    Dao Imp

    @Repository
    public class JdbcVehicleDao implements VehicleDao {
     
        private JdbcTemplate jdbcTemplate;
     
        @Autowired
        public void setDataSource(DataSource dataSource) {
            this.jdbcTemplate = new JdbcTemplate(dataSource);
        }
     
        public void insert(Vehicle vehicle) { 
            String sql = &quot;INSERT INTO VEHICLE (VEHICLE_NO, COLOR, WHEEL, SEAT) &quot; 
                    + &quot;VALUES (?, ?, ?, ?)&quot;; 
            jdbcTemplate.update(sql, new Object[] { vehicle.getVehicleNo(), 
                                 vehicle.getColor(), vehicle.getWheel(), vehicle.getSeat() });
        }
        // update, delate, findByVehicleNo....

    Controller

    @Controller
    public class VehicleController {
        @Autowired    
        VehicleDao vehicleDao;
     
        @RequestMapping(value=&quot;/jdbc.htm&quot;, method = RequestMethod.GET)
        public String jdbc(ModelMap model) {        
            Vehicle vehicle = new Vehicle(&quot;TEM0001&quot;, &quot;Red&quot;, 4, 4); 
            vehicleDao.insert(vehicle);
            model.addAttribute(&quot;vehicle&quot;, vehicle);
            return &quot;crud&quot;;
        }
    }
    &lt;bean id=&quot;propertyConfigurer&quot; 
       class=&quot;org.springframework.beans.factory.config.PropertyPlaceholderConfigurer&quot;
       p:location=&quot;/WEB-INF/jdbc.properties&quot; /&gt;
     
    &lt;bean id=&quot;dataSource&quot;
              class=&quot;org.springframework.jdbc.datasource.DriverManagerDataSource&quot;
              p:driverClassName=&quot;${jdbc.driverClassName}&quot;
              p:url=&quot;${jdbc.url}&quot;
              p:username=&quot;${jdbc.username}&quot;
              p:password=&quot;${jdbc.password}&quot; /&gt;
    &lt;context:component-scan base-package=&quot;x.D&quot; /&gt;
    &lt;mvc:annotation-driven /&gt;

    fuente: Spring Recipes A Problem-Solution Approach

    • Shiva Krishna K

      Awesome one but it would be gud if you could provide a link for what is tag and what is @RequestMapping annotation mean and what it does.

      Because all these tutorials are related to Spring Core but not to MVC but the tags that you have mentioned are related to MVC here.

      • Shiva Krishna K

        Tag that i am refering to is <mvc:annotation-driven /> in my above comment

  • Adeel

    Awesome tut mate…well done …saved my life eh …ta

  • Pingback: Spring SimpleJdbcTemplate Querying examples()

  • Pingback: Spring Tutorials()

  • mario

    Nice one. Thanks:)

    • me

      you welcom

  • dhananjay patil

    todays i got awasome knowldge from this website,this website really rocks .i thank to yong for delevering such a nice tutorials .

    • http://www.mkyong.com mkyong

      welcome, sharing is learning :)