Java Spring How to use JDBCTemplate Query

In this post, I will show you How to use JdbcTemplate query() method to query or extract data from database.

1. Query for Single Row
 There are two ways to query or extract single row record from database.

1.1 Custom RowMapper

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

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. Query for Multiple Rows
Now, query or extract multiple rows from database, and convert it into a List.

2.1. queryForList

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

public List<Customer> findAll(){

 String sql = "SELECT * FROM CUSTOMER";

 List<Customer> customers  = getJdbcTemplate().query(sql,
   new BeanPropertyRowMapper(Customer.class));

 return customers;
}

3. Query for a Single Value

Query or extract a single column value from database.

3.1. Single column name

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

Query a total number of rows from database

public int findTotalCustomer(){

 String sql = "SELECT COUNT(*) FROM CUSTOMER";

 int total = getJdbcTemplate().queryForInt(sql);

 return total;
}

Finally

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 : " + cust);
         }

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

         String customerName = customerDAO.findCustomerNameById(1);
         System.out.println("Customer Name : " + customerName);

         int total = customerDAO.findTotalCustomer();
         System.out.println("Total : " + total);

    }
}

Good luck!