Spring JdbcTemplate IN CLAUSE Example

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

lppl010_

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

阅读量354

点赞数

分类专栏： JdbcTemplate

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

版权

JdbcTemplate 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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("----------------------------------");
}
}
}

lppl010_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Spring JdbcTemplate IN CLAUSE Example

https://www.technicalkeeda.com/spring-tutorials/spring-jdbctemplate-in-clause-exampleUseful to whomThis tutorial is useful for beginners and experience developers. It will helps you to learn st...
复制链接

扫一扫

专栏目录