Spring GenericQuerySql Example

0

GenericQuerySql represents a generic SQL Query object. We need to set the SQL and add parameters in the spring context XML to represent a specific SQL Query. Make sure the order in which parameters are added are same as the order in which placeholders appear in the SQL. When you execute the query, internally it delegates the call to JdbcTemplate().query() for the actual execution.

This example uses the following frameworks:

  1. Maven 3.2.3
  2. Spring 4.1.5.RELEASE
  3. Eclipse  as the IDE, version Luna 4.4.1.

Dependencies

Add the following dependencies:

  1. spring-core
  2. spring-context
  3. spring-beans
  4. mysql-connector-java for database driver

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>com.javarticles.jdbc.datasource</groupId>
	<artifactId>springdatasource</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
	
	</dependencies>

	<properties>
		<spring.version>3.2.3.RELEASE</spring.version>
	</properties>

</project>

Schema and sample data

Let’s create a simple ‘Employee’ table and sample data. Our goal would be to find employees joined between two specific dates.

db-schema.sql:

drop table if exists `employee`;
CREATE TABLE `employee` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(100) NOT NULL,
`AGE` INT(3),
`JOINING_DATE` DATE,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

db-test-data.sql:

insert into employee(id, name, age, joining_date) values (1, "Sam", 45, '2000-7-04');
insert into employee(id, name, age, joining_date) values (2, "John", 30, '2010-2-06');
insert into employee(id, name, age, joining_date) values (3, "Ricky", 23, '2012-5-09');
insert into employee(id, name, age, joining_date) values (4, "Martin", 50, '2012-3-24');
insert into employee(id, name, age, joining_date) values (5, "Vijay", 22,'2015-1-14');
insert into employee(id, name, age, joining_date) values (6, "Ram", 35, '2014-2-02');

Define GeneralSqlQuery bean

In spring context XML, you will define a bean of class org.springframework.jdbc.object.GenericSqlQuery.
The bean expects SQL, the sql parameters and a RowMapper class that maps the result set to a POJO class.

Before we start with the configuration let’s start with defining the POJO class and the row mapper class.

Employee:

package com.javarticles.spring.jdbc;

import java.util.Date;

public class Employee {
    private int id;
    private String name;
    private int age;
    private Date joiningDate;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public Date getJoiningDate() {
        return joiningDate;
    }
    public void setJoiningDate(Date joiningDate) {
        this.joiningDate = joiningDate;
    }
    
    public String toString() {
        return "Employee(id " + id + ", name " + name + ", age " + age + ", joiningDate " + joiningDate + ")";
    }
}

In our RowMapper class, we create an Employee object for each employee row fetched.

EmployeeMapper:

package com.javarticles.spring.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class EmployeeMapper implements RowMapper {

    private static final String[] COLUMN_NAMES = new String[] { "id", "name",
            "age", "joining_date" };

    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee emp = new Employee();
        emp.setId(rs.getInt(COLUMN_NAMES[0]));
        emp.setName(rs.getString(COLUMN_NAMES[1]));
        emp.setAge(rs.getInt(COLUMN_NAMES[2]));
        emp.setJoiningDate(rs.getDate(COLUMN_NAMES[3]));
        return emp;
    }

}

We initialize the database in element <jdbc:initialize-database>. Since database is involved we must configure our DataSource. We have defined beans in two different styles.
The first one sqlWithPlaceHolders simply expects the SQL type parameters. In the second style sqlWithNamedParameters, we are using named parameters :fromDate and :toDate so you need to provide both the named parameter and the SQL type to the org.springframework.jdbc.core.SqlParameter constructor.

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:util="http://www.springframework.org/schema/util" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	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.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd">

	<jdbc:initialize-database data-source="dataSource"
		enabled="true">
		<jdbc:script location="classpath:db-schema.sql" />
		<jdbc:script location="classpath:db-test-data.sql" />
	</jdbc:initialize-database>

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="mnrpass" />
	</bean>

	<bean id="pooledDataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="mnrpass" />
	</bean>

	<bean id="sqlWithPlaceHolders" class="org.springframework.jdbc.object.GenericSqlQuery">
		<property name="dataSource" ref="dataSource" />
		<property name="sql"
			value="select id, name, age, joining_date from employee where (joining_date between ? and ?)" />
		<property name="parameters">
			<list>
				<bean class="org.springframework.jdbc.core.SqlParameter">
					<constructor-arg index="0" value="fromDate" />
					<constructor-arg index="1">
						<util:constant static-field="java.sql.Types.DATE" />
					</constructor-arg>
				</bean>
				<bean class="org.springframework.jdbc.core.SqlParameter">
					<constructor-arg index="0" value="toDate" />
					<constructor-arg index="1">
						<util:constant static-field="java.sql.Types.DATE" />
					</constructor-arg>
				</bean>
			</list>
		</property>
		<property name="rowMapperClass" value="com.javarticles.spring.jdbc.EmployeeMapper" />
	</bean>

	<bean id="sqlWithNamedParameters" class="org.springframework.jdbc.object.GenericSqlQuery">
		<property name="dataSource" ref="dataSource" />
		<property name="sql"
			value="select id, name, age, joining_date from employee where (joining_date between :fromDate and :toDate)" />
		<property name="parameters">
			<list>
				<bean class="org.springframework.jdbc.core.SqlParameter">
					<constructor-arg index="0" value="fromDate" />
					<constructor-arg index="1">
						<util:constant static-field="java.sql.Types.DATE" />
					</constructor-arg>
				</bean>
				<bean class="org.springframework.jdbc.core.SqlParameter">
					<constructor-arg index="0" value="toDate" />
					<constructor-arg index="1">
						<util:constant static-field="java.sql.Types.DATE" />
					</constructor-arg>
				</bean>
			</list>
		</property>
		<property name="rowMapperClass" value="com.javarticles.spring.jdbc.EmployeeMapper" />
	</bean>
</beans>

If you have noticed, even in the first style we provide fromDate and toDate. This is just optional. You may provide whatever name you want or you may even skip passing the first parameter. For example:

	<bean id="sqlWithPlaceHolders" class="org.springframework.jdbc.object.GenericSqlQuery">
		<property name="dataSource" ref="dataSource" />
		<property name="sql"
			value="select id, name, age, joining_date from employee where (joining_date between ? and ?)" />
		<property name="parameters">
			<list>
				<bean class="org.springframework.jdbc.core.SqlParameter">
					<constructor-arg index="0">
						<util:constant static-field="java.sql.Types.DATE" />
					</constructor-arg>
				</bean>
				<bean class="org.springframework.jdbc.core.SqlParameter">
					<constructor-arg index="0">
						<util:constant static-field="java.sql.Types.DATE" />
					</constructor-arg>
				</bean>
			</list>
		</property>
		<property name="rowMapperClass" value="com.javarticles.spring.jdbc.EmployeeMapper" />
	</bean>

In the main class below, we get the configured GenericSqlQuery bean and call GenericSqlQuery.execute(params).
In case of sqlWithPlaceHolders, we just need to pass two date objects to the execute() method and it will return a list of Employee objects.
In second case, we create a map of parameters, key being the name of the parameter and value the date object. Instead of calling GenericSqlQuery.execute(params), you need to call GenericSqlQuery.executeByNamedParam(params).

SpringGenericSQLExample:

package com.javarticles.spring.jdbc;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.object.GenericSqlQuery;

public class SpringGenericSQLExample {
    public static void main(String[] args) throws SQLException, ParseException {
        ConfigurableApplicationContext context = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        try {
            System.out.println("Generic SQL with placeholders");
            GenericSqlQuery queryWithPlaceholders = (GenericSqlQuery) context
                    .getBean("sqlWithPlaceHolders");

            SimpleDateFormat df = new SimpleDateFormat("mm-dd-yyyy");
            Date fromDate = df.parse("01-01-2014");
            Date toDate = df.parse("12-31-2015");
            Object[] params = new Object[] { fromDate, toDate };
            List empList = queryWithPlaceholders.execute(params);
            for (Employee emp : empList) {
                System.out.println(emp);
            }
            
            System.out.println("Generic SQL with named parameters");
            GenericSqlQuery queryWithNamedParameters = (GenericSqlQuery) context
                    .getBean("sqlWithNamedParameters");

            Map<String, Date> paramsAsMap = new HashMap<String, Date>(2);
            paramsAsMap.put("fromDate", fromDate);
            paramsAsMap.put("toDate", toDate);
            empList = queryWithNamedParameters.executeByNamedParam(paramsAsMap);
            for (Employee emp : empList) {
                System.out.println(emp);
            }
        } finally {
            context.close();
        }
    }
}

Output:

Generic SQL with placeholders
Employee(id 5, name Vijay, age 22, joiningDate 2015-01-14)
Employee(id 6, name Ram, age 35, joiningDate 2014-02-02)
Generic SQL with named parameters
Employee(id 5, name Vijay, age 22, joiningDate 2015-01-14)
Employee(id 6, name Ram, age 35, joiningDate 2014-02-02)

Download the source code

This was an example about spring GenericSqlQuery.

You can download the source code here: springGenericSQL.zip
Share.

Comments are closed.