Home » Enterprise Java » spring » JDBC » Jdbc Named Parameters Example with Spring NamedParameterJdbcTemplate

About Ernő Gárday

Erno Garday has graduated from electrical engineering. Since then he has been working as a software engineer developing large scale distributed systems mainly in java. During his professional carrier he has gained significant experience in areas like performance optimisation creating resilient, maintainable software.

Jdbc Named Parameters Example with Spring NamedParameterJdbcTemplate

In this example we will present the usage of Spring NamedParameterJdbcTemplate.

1. Introduction

JDBC in general, acts as a communication layer between client applications and various databases hence relevant how we can utilise it. An introduction about plain JDBC is already available under the following link https://examples.javacodegeeks.com/enterprise-java/servlet/jdbc-servlet-example/#introduction. It is best reading that post as well to have a broader understanding of the topic.

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

NamedParameterJdbcTemplate is built upon JDBCTemplate which is provided by spring and used for lower level communication with databases. It makes possible to pass SQL query arguments as key value pairs. As a result the program code is  much more readable and therefore serves as better documentation compared to the indexed or the “?” placeholder approach. The latter is harder to follow specially if the number of parameters is huge.

2. NamedParameterJdbcTemplate example

2.1 Tools used

We will use MySQL as the database and MySQL Workbench as the client. Eclipse as IDE, maven for dependency management and finally spring-jdbc for DB access.

2.2 Database setup

First we will execute a query. It will return database records corresponding to the supplied parameters. We will use MySQL Workbench to start up the database, create the table and insert rows. If you haven’t done this already, download MySQL from here https://dev.mysql.com/downloads/mysql/ and install it. Having successfully installed MySQL you can start up the database and connect to it via Workbench.

Start up Workbench and once it is done click on MySQL connections to create a new connection. Make sure you are connecting to the locally installed instance hostname: localhost port: 3306 and then click OK.

MySQL setup connection

Having successfully connected the instance can be managed. Probably this is the first time you’re trying to connect hence the database needs to be started up. Go to the left menu and under INSTACE you will find Startup / Shutdown.

Startup MySQL server

As a next step create a schema.  The schema is a logical unit which groups database entities. To create one go to the empty area under SCHEMAS and right click on it. Name it arbitrarily e.g. jcg.

create schema

Now you need to create a table within that schema. We will create a table called engine. An engine in real life would have a lot more parameters. I wanted to keep the example simple therefore I listed only a few. Create a new SQL tab and execute the below DDL:

CREATE TABLE `jcg`.`engine` (
`id` INT NOT NULL,
`manufacturer` VARCHAR(45) NULL,
`power` INT NULL,
`torque` INT NULL,
`engine_capacity` INT NULL,
`engine_type` VARCHAR(45) NULL,
`fuel_type` VARCHAR(45) NULL,
PRIMARY KEY (`id`));

create table

Another necessary step is to populate data to the engine table. Execute the below insert commands:

insert into jcg.engine (id, manufacturer, power, torque, engine_capacity, engine_type, fuel_type) VALUES  (1, 'ford', 100, 80, 1600, 'straight', 'petrol');
insert into jcg.engine (id, manufacturer, power, torque, engine_capacity, engine_type, fuel_type) VALUES  (2, 'vw', 200, 120, 200, 'boxer', 'petrol');
insert into jcg.engine (id, manufacturer, power, torque, engine_capacity, engine_type, fuel_type) VALUES  (3, 'dodge', 300, 250, 6000, 'v_type', 'petrol');

Finally create a database user. Go to Users and Privileges. Click on Add Account. Create user: test, password: test.

2.3 Project setup

Download maven from here: https://maven.apache.org/download.cgi . Create a java project called NamedParameters and add a pom.xml to the project. The pom.xml describes the project dependencies therefore maven is able to manage them for you. The content of the pom.xml should be like this:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.jcg</groupId>
	<artifactId>JDBCNamedParameters</artifactId>
	<version>0.0.1</version>
	<packaging>jar</packaging>

	<name>JDBC named parameters</name>
	<url>http://maven.apache.org</url>

	<dependencies>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-core</artifactId>
		<version>4.3.8.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context-support</artifactId>
		<version>4.3.8.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>4.3.8.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.6</version>
	</dependency>
</dependencies>
</project>

Execute the mvn eclipse:eclipse command. As a result the dependencies are downloaded and added to your eclipse project.

get dependencies

After execution refresh the eclipse project

project setup

project setup

Create a file called spring-config.xml. Put to the file, the resources package. Below is the content of the file:

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<bean id="dataSource"
		class="com.mysql.jdbc.jdbc2.optional.MysqlDataSource">
		<property name="url" value="jdbc:mysql://localhost:3306/jcg" />
		<property name="user" value="test" />
		<property name="password" value="test" />
	</bean>

	<bean id="NamedParameters" class="jcg.db.NamedParameters">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>

With this spring configuration we are injecting a data source object to our class hence our class will be able to communicate with the database. The data source is passed to NamedParameterJdbcTemplate class. Corresponding to the database we use MysqlDataSource. First of all this defines the address of the database instance and it also defines the user and the password.

2.4 Source code and explanation

You can download java source code from here.

package jcg.db;

import java.util.HashMap;
import java.util.Map;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class NamedParameters {

	private MysqlDataSource dataSource;

	public static void main(String[] args) {

		ApplicationContext context = new ClassPathXmlApplicationContext("resources/spring-config.xml");
		NamedParameters np = (NamedParameters) context.getBean("NamedParameters");
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(np.getDataSource());
		String sql = "select * from jcg.engine where manufacturer = :manufacturer";
		Map parameters = new HashMap();
		parameters.put("manufacturer", "ford");
		SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, parameters);
		while (rowSet.next()) {
			System.out.println("RowNumber: " + rowSet.getRow());
			for (int i = 1; i < rowSet.getMetaData().getColumnCount(); i++) {
				System.out.println("ColumName: " + rowSet.getMetaData().getColumnName(i) + ", value: " + rowSet.getObject(i));
			}
			
		}
	}

	public MysqlDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(MysqlDataSource dataSource) {
		this.dataSource = dataSource;
	}
}

First of all we let spring open the configuration file. As a result spring instantiates the configured beans for us. We look up the NamedParameters bean and pass it over to NamedParameterJdbcTemplate.

The SQL query is specified with a single parameter “manufacturer” and passed to the NamedParameterJdbcTemplate.queryForRowSet as the first argument. Another parameter is a Map which contains the name value pairs. The names stored in the Map are matching with the SQL query parameter names. Having these two parameters properly specified the call to queryForRowSet executes our query. As a result we can display the matching rows. In this example we iterate over all the rows. And in each column within the row.  The getMetaData function delivers us relevant information about the underlying schema.

SQL Query result

SQL Query result

As expected the result is a single matching entry.

3. Summary

Frameworks like spring provide solution to recurring problems. We saw in this example how to utilise one of them. Consider the named parameter approach whenever you have lot of parameters and consequently hard readable code. Named parameters will make your code easier to read hence better maintainable.

(No Ratings Yet)
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