https://www.technicalkeeda.com/spring-tutorials/spring-jdbctemplate-in-clause-example
Useful to whom
This tutorial is useful for beginners and experience developers. It will helps you to learn step by step with the help of attached code.
Tools and Technologies
To execute sample Spring Hibernate program we use below technologies
- Maven 3.0.4
- JDK 1.6
- Spring 3.0.5.RELEASE
Create Database table
Create "trn_employee" table in Mysql database using below sql table script and insert below records.
- CREATE TABLE `trn_employee` (
- `employee_id` bigint(20) NOT NULL auto_increment,
- `first_name` varchar(255) collate latin1_general_ci default NULL,
- `last_name` varchar(255) collate latin1_general_ci default NULL,
- PRIMARY KEY (`employee_id`)
- ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
- );
- INSERT INTO `trn_employee` (`employee_id`, `first_name`, `last_name`) VALUES
- (1, 'Yashwant','Chavan'),
- (2, 'Mahesh', 'Patil'),
- (3, 'Jai','Kumar');
Define Dependancies in pom.xml
Create Maven Project and define Spring and other dependencies in pom.xml
- <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>SpringExamples</groupId>
- <artifactId>SpringExamples</artifactId>
- <packaging>war</packaging>
- <version>1.0</version>
- <name>SpringExamples</name>
- <description></description>
- <build>
- <finalName>SpringExamples</finalName>
- <plugins>
- <plugin>
- <artifactId>maven-compiler-plugin</artifactId>
- <configuration>
- <verbose>true</verbose>
- <compilerVersion>1.6</compilerVersion>
- <source>1.6</source>
- <target>1.6</target>
- </configuration>
- </plugin>
- </plugins>
- </build>
- <properties>
- <spring.version>3.0.5.RELEASE</spring.version>
- </properties>
- <dependencies>
- <dependency>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- <version>4.8.1</version>
- <scope>test</scope>
- </dependency>
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <version>5.1.9</version>
- </dependency>
- <dependency>
- <groupId>log4j</groupId>
- <artifactId>log4j</artifactId>
- <version>1.2.9</version>
- </dependency>
- <dependency>
- <groupId>dom4j</groupId>
- <artifactId>dom4j</artifactId>
- <version>1.6.1</version>
- </dependency>
- <dependency>
- <groupId>commons-logging</groupId>
- <artifactId>commons-logging</artifactId>
- <version>1.1.1</version>
- </dependency>
- <dependency>
- <groupId>commons-collections</groupId>
- <artifactId>commons-collections</artifactId>
- <version>3.2.1</version>
- </dependency>
- <dependency>
- <groupId>cglib</groupId>
- <artifactId>cglib</artifactId>
- <version>2.2</version>
- </dependency>
- <dependency>
- <groupId>asm</groupId>
- <artifactId>asm</artifactId>
- <version>3.1</version>
- </dependency>
- <dependency>
- <groupId>javax.transaction</groupId>
- <artifactId>jta</artifactId>
- <version>1.1</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring</artifactId>
- <version>2.5.6</version>
- </dependency>
- </dependencies>
- </project>
Spring Bean Defination (spring-beans.xml)
Define spring jdbc datasource and bean defination configuration in spring-beans.xml, Which is located under folder "/src/main/resources".
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:context="http://www.springframework.org/schema/context"
- xmlns:mvc="http://www.springframework.org/schema/mvc" 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-3.0.xsd
- http://www.springframework.org/schema/context
- http://www.springframework.org/schema/context/spring-context-3.0.xsd
- http://www.springframework.org/schema/mvc
- http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
- <bean id="dataSource"
- class="org.springframework.jdbc.datasource.DriverManagerDataSource">
- <property name="driverClassName">
- <value>com.mysql.jdbc.Driver</value>
- </property>
- <property name="url">
- <value>jdbc:mysql://localhost:3306/technicalkeeda</value>
- </property>
- <property name="username">
- <value>root</value>
- </property>
- <property name="password">
- <value></value>
- </property>
- </bean>
- <bean id="employeeDao" class="com.technicalkeeda.dao.EmployeeDaoImpl">
- <property name="dataSource" ref="dataSource" />
- </bean>
- </beans>
Employee.java
Employee class is simple pojo having getter setter methods.
- package com.technicalkeeda.bean;
- public class Employee {
- private int employeeId;
- private String firstName;
- private String lastName;
- public Employee() {
- }
- public Employee(int employeeId, String firstName, String lastName) {
- super();
- this.employeeId = employeeId;
- this.firstName = firstName;
- this.lastName = lastName;
- }
- public int getEmployeeId() {
- return employeeId;
- }
- public void setEmployeeId(int employeeId) {
- this.employeeId = employeeId;
- }
- public String getFirstName() {
- return firstName;
- }
- public void setFirstName(String firstName) {
- this.firstName = firstName;
- }
- public String getLastName() {
- return lastName;
- }
- public void setLastName(String lastName) {
- this.lastName = lastName;
- }
- }
EmployeeDao.java
EmployeeDao interface contains findByIds() method.
- package com.technicalkeeda.dao;
- import java.util.Collection;
- import java.util.List;
- import com.technicalkeeda.bean.Employee;
- public interface EmployeeDao {
- public Collection<Employee> findByIds(List<String> ids);
- }
EmployeeDaoImpl.java
This is implementation class of EmployeeDao interface. Basically we are using NamedParameterJdbcTemplate to perform the IN clause query in spring jdbc.
Here we pass simple Employee Id list to the IN clause query , Which internaly mapp each id with respected placeholder question mark sign. You don't have to worry about this because all this mapping happen behind the scene.But somehow there is limitation for IN CLAUSE parameters , It allows up-to 1000 parameters but this value is depends upon which database you are using.
- package com.technicalkeeda.dao;
- import java.util.Collection;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import javax.sql.DataSource;
- import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
- import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
- import com.technicalkeeda.bean.Employee;
- public class EmployeeDaoImpl implements EmployeeDao {
- private DataSource dataSource;
- private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
- public void setDataSource(DataSource dataSource) {
- this.dataSource = dataSource;
- this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
- this.dataSource);
- }
- @Override
- public Collection<Employee> findByIds(List<String> ids) {
- Map<String, Object> params = new HashMap<String, Object>();
- params.put("ids", ids);
- List<Employee> employees = namedParameterJdbcTemplate.query(
- "SELECT * FROM trn_employee where employee_id IN (:ids)",
- params,
- ParameterizedBeanPropertyRowMapper.newInstance(Employee.class));
- return employees;
- }
- }
AppTest.java
This is the Junit test class , which verify the implementation of findByIds() method.
- package com.technicalkeeda.test;
- import java.util.ArrayList;
- import java.util.Collection;
- import java.util.List;
- import org.junit.Test;
- import org.springframework.context.ApplicationContext;
- import org.springframework.context.support.ClassPathXmlApplicationContext;
- import com.technicalkeeda.bean.Employee;
- import com.technicalkeeda.dao.EmployeeDao;
- public class AppTest {
- @Test
- public void testRowMapper() {
- ApplicationContext context = new ClassPathXmlApplicationContext(
- "spring-beans.xml");
- EmployeeDao employeeDao = (EmployeeDao) context.getBean("employeeDao");
- List<String> ids = new ArrayList<String>();
- ids.add("1");
- ids.add("2");
- ids.add("3");
- Collection<Employee> employees = employeeDao.findByIds(ids);
- for (Employee employee : employees) {
- System.out.println("Employee Id:- " + employee.getEmployeeId());
- System.out.println("First Name:- " + employee.getFirstName());
- System.out.println("Last Name:- " + employee.getLastName());
- System.out.println("----------------------------------");
- }
- }
- }