Spring JdbcTemplate IN CLAUSE Example

最新推荐文章于 2020-07-14 20:43:10 发布

转载最新推荐文章于 2020-07-14 20:43:10 发布 · 462 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://www.technicalkeeda.com/spring-tutorials/spring-jdbctemplate-in-clause-example

JdbcTemplate 专栏收录该内容

1 篇文章

订阅专栏

本教程通过实例代码，详细介绍了如何使用Spring JDBC的NamedParameterJdbcTemplate执行IN子句查询，适合初学者和有经验的开发者。我们将展示如何创建数据库表、定义依赖项以及实现EmployeeDao接口的findByIds()方法。

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.