How to use Named Query SQL in Spring Framework? Example Tutorial

Hello guys, many Java developers don't know that Spring Framework also provides support for NamedQuery where you can pass parameters in a much readable way by using named placeholders than the default placeholder of ? (question mark) which is used with PreparedStatement in Java. If you want to learn Spring JDBC then you have come to the right place. In the past, I have shared the best Spring Framework courses, books, and tutorials to learn Spring Framework and its different features, and in this example, I will show you how to use NamedQuery in Spring Framework. You can then use this technique to write much more readable queries on the Java layer for your application. 

The most important benefit of using NamedQuery is that it's more readable than classical placeholder ? especially if you have more than 3 placeholders. It also allowed you to pass parameters in any order because now they have names to map, but in the case of traditional placeholders used in PreparedStatement, arguments must be supplied in the same order.

Java program to run named query SQL in Spring Framework

Here is a complete code example of how to execute a named query in a Java application using the spring framework.

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

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

public class JdbcUtil {

        private static Logger logger = Logger.getLogger(JdbcUtil.class);
     
        private NamedParameterJdbcTemplate  namedJdbcTemplate;
     
        public void setDataSource(DataSource source){
                this.namedJdbcTemplate = new NamedParameterJdbcTemplate(source);
        }
     
        public void runNamedQuery(){
             
                // this is a named sql query, instead of classic placeholder ? 
                // we have used named placeholder
                // :dept_id to pass value of emp_id
                String query = "SELECT count() from employee where dept_id= :dept_id ";
                SqlParameterSource namedParameters 
                       = new MapSqlParameterSource("dept_id", 2);
                int count = namedJdbcTemplate.queryForInt(query, namedParameters);
                logger.info("Number of employee : " + count);
             
             
                // Another example of named query in Spring with more than one
                // named place holder
                String sql = "SELECT count() from employee 
                              where emp_id = :emp_id && dept_id= :dept_id 
                              && emp_name= :emp_name";

                Map params = new HashMap();
                params.put("emp_name" , "Jack");
                params.put("dept_id", "2");
                params.put("emp_id", "103");
                SqlParameterSource namedParams = new MapSqlParameterSource(params);
             
                count = namedJdbcTemplate.queryForInt(sql, namedParams);
                logger.info("Result : " + count);
             
        }

        public static void main(String args[]){
                ApplicationContext ctx 
                      = new ClassPathXmlApplicationContext("spring-config.xml");
                JdbcUtil namedQueryRunner = (JdbcUtil) ctx.getBean("jdbcUtil");
                namedQueryRunner.runNamedQuery();
        }

}

Output:

2013-01-03 22:52:45,463 0    [main] INFO  JdbcUtil  - Number of employee : 2
2013-01-03 22:52:45,463 0    [main] INFO  JdbcUtil  - Result : 1

ysql> select  from employee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|    103 | Jack     |       2 |   1400 |
|    104 | John     |       2 |   1450 |
|    105 | Johnny   |       3 |   1050 |
|    108 | Alan     |       3 |   1150 |
|    106 | Virat    |       4 |    850 |
|    107 | Vina     |       4 |    700 |
|    109 | joya     |       4 |    700 |
+--------+----------+---------+--------+
7 rows in set (0.00 sec)

Spring Configuration file

Here is the spring configuration file for this example. You can use this to configure your Spring Application Context:

<beans p=""
	xmlns="http://www.springframework.org/schema/beans">
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:jms="http://www.springframework.org/schema/jms"
	xmlns:context="http://www.springframework.org/schema/context"
	
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
      http://www.springframework.org/schema/jms 
      http://www.springframework.org/schema/jms/spring-jms-3.0.xsd 
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd">
	
	<bean id="propertyPlaceholderConfigurer" 
           class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations"></property>
		<list></list>
		<value>classpath:jdbc.properties</value>
	</bean>

<bean class="org.springframework.jdbc.datasource.SingleConnectionDataSource" 
 id="springDataSource">
  <property name="driverClassName" value="${db.driver}"></property>
  <property name="url" value="${db.url}"></property>
  <property name="username" value="root"></property>
  <property name="password" value="root"></property>
</bean>

<bean class="JdbcUtil" id="jdbcUtil"></bean>
  <property name="dataSource" ref="springDataSource"></property>
</bean>
</beans>

Important things about NamedQuery Support in Spring Framework

here are some important points which you should remember while using the Named query functionality of JdbcTemplate in Spring Framework.

1. Spring provides a convenient SqlParameterSource class to accumulate and pass named parameters to NamedParameterJdbcTemplate class for executing named SQL queries. This class can accept either a Map if there is more than one named parameter or simply the name and value of the named parameter in the case of single named parameters.

2. It's convenient to use a classical placeholder if the number of parameters is not very high e.g. less than 3 because I found that using a named query we are only gaining readability which is not a big issue if a number of placeholder in SQL queries is very less.

3. Spring Framework provides two implementations of the SqlParameterSource interface, MapSqlParameterSource which is Map style way of passing named parameters and the other is BeanPropertySqlParameterSource, which uses bean properties as named parameters and their value. This is quite convenient if the SQL query takes multiple parameters which can be found in a Java bean.

4. One more worth noting point is that NamedParameterJdbcTemplate is a wrapper over classical JdbcTemplate and delegate most of its functionality to the underlying class. If you need a reference of JdbcTemplate from NamedParameterJdbcTemplate you can use the getOperations() method.

That's all on how to execute named SQL queries in the Spring framework. named queries does improve the readability of SQL query by giving placeholder meaningful names but it is only worth doing if you reasonably high number of placeholder, for most common cases where you have just one or two placeholders, it's better to stick with JdbcTemplate and classical PreparedStatement placeholder ?. Nonetheless, Named Parameter is a nice concept to know in Spring Framework.

Other Java and Spring Articles you may like

• How Spring MVC works internally? (answer)
• Difference between @RequestParam and @PathVariable in Spring (answer)
• Spring Data JPA @Query Example (query example)
• Top 5 Spring Cloud annotations Java programmer should learn (cloud)
• 5 Courses to Learn Spring Security for Java programmers (courses)
• Spring Data JPA Repository (JpaReposistory example)
• Top 5 Spring Boot Annotations Java Developers should know (read)
• Top 7  Courses to learn Microservices in Java (courses)
• 10 Spring MVC annotations Java developer should learn (annotations)
• @SpringBootApplication vs @EnableAutoConfiguration? (answer)
• 15 Spring Boot Interview Questions for Java Developers (questions)
• Top 5 Courses to Learn and Master Spring Cloud (courses)
• 5 Free Courses to Learn Spring Framework (free courses)
• Top 5 Frameworks Java Developer Should Know (frameworks)
• 10 Advanced Spring Boot Courses for Java developers (courses)
• 5 Courses to learn Spring Cloud for Microservices (courses)
• Difference between @Component, @Service, and @Controller in Spring (answer)
• 20+ Spring MVC Interview Questions for Programmers (answer)
• Difference between @Autowired and @Inject in Spring? (answer)
• Difference between @Controller and @RestController in Spring (answer)

Thanks for reading this article so far. If you find this Spring Named Query tutorial then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.

P. S. - If you want to learn the Spring framework, particularly Spring Boot from scratch, and looking for some free resources then you can also check out these free core Spring and Spring MVC courses for beginners. This list contains free Udemy and Pluralsight courses to learn Spring Framework from scratch.