Home » Enterprise Java » spring » JDBC » Select records from database with JdbcTemplate

About Ilias Tsagklis

Ilias Tsagklis
Ilias is a software developer turned online entrepreneur. He is co-founder and Executive Editor at Java Code Geeks.

Select records from database with JdbcTemplate

With this example we are going to demonstrate how to select records from a database using the JdbcTemplate class provided by the Spring Framework. The JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions. In short, to select records from a database using the JdbcTemplate class you should:

  • Use the DataSource class, a utility class that provides connection to the database. It is part of the JDBC specification and allows a container or a framework to hide connection pooling and transaction management issues from the application code. We implement it using the org.springframework.jdbc.datasource.DriverManagerDataSource. Set the credentials needed to the datasource, using the inherited methods setPassword(String password), setUrl(String url) and setUsername(String username) API methods of AbstractDriverBasedDataSource class, as also the setDriverClassName(String driverClassName) API method of DriverManagerDataSource. Create a new Datasource object having the above configuration. Here in getDatasource() method we create a new Datasource and configure it.
  • Create a new JdbcTemplate object, with the given datasource to obtain connections from.
  • Use the queryForList(String sql) API method of JdbcTemplate class to execute a query for a result list, with the given static SQL select query. The results will be mapped to a List (one entry for each row) of result objects, each of them matching the specified element type. 
  • Iterate over the List of the results to get the values of each column per row.

Let’s take a look at the code snippet that follows:

package com.javacodegeeks.snippets.enterprise;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class SelectRecordsFromDatabaseWithJdbcTemplate {
	
	private static final String driverClassName = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost/companydb";
	private static final String dbUsername = "jcg";
	private static final String dbPassword = "jcg";

	private static final String selectSql = "SELECT * FROM employee";

	private static DataSource dataSource;
	
	public static void main(String[] args) throws Exception {
	
		dataSource = getDataSource();
		
		JdbcTemplate template = new JdbcTemplate(dataSource);
		
		List<Map<String, Object>> employees = template.queryForList(selectSql);
		
		if (employees!=null && !employees.isEmpty()) {
			
			for (Map<String, Object> employee : employees) {
				
				for (Iterator<Map.Entry<String, Object>> it = employee.entrySet().iterator(); it.hasNext();) {
					Map.Entry<String, Object> entry = it.next();
					String key = entry.getKey();
					Object value = entry.getValue();
					System.out.println(key + " = " + value);
				}
				
				System.out.println();
				
			}
			
		}
		
	}
	
	public static DriverManagerDataSource getDataSource() {

  DriverManagerDataSource dataSource = new DriverManagerDataSource();

  dataSource.setDriverClassName(driverClassName);

  dataSource.setUrl(url);

  dataSource.setUsername(dbUsername);

  dataSource.setPassword(dbPassword);

  return dataSource;
    }

}
CREATE TABLE `companydb`.`employee` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `surname` VARCHAR(45) NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

Output:

id = 1
name = John
surname = Black
title = Software developer
created = 2011-11-20 12:13:24.0

id = 2
name = Tom
surname = Green
title = Project Manager
created = 2011-11-20 12:13:24.0

 
This was an example of how to select records from a database using the JdbcTemplate class provided by the Spring Framework.

(+1 rating, 1 votes)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of