Wednesday, 30 November 2016

Select query using NamedParameterJDBCTemplate in Spring framework

In the post Insert\Update using NamedParameterJDBCTemplate in Spring framework I have already discussed how NamedParameterJDBCTemplate can be used for inserting and updating data in the DB. In this post we’ll see how to read data from DB using named parameters. Main intention to have it as a separate post is to discuss callback part in detail.

In the post Data access in Spring framework it has been discussed in detail how Spring framework provides templates to manage the fixed part and use call back to handle the variable part. Fetching data from DB using select query has, as usual, the fixed part like getting connection, cleaning up, handling exception but at the same time Spring framework does need help to map the fetched data to the model. That’s where callback comes into picture.

NamedParameterJdbcTemplate

Spring framework provides NamedParameterJdbcTemplate class which adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ('?') arguments.

Here one thing to note is NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.

NamedParameterJdbcTemplate with Select query example

Note that NamedParameterJdbcTemplate needs a DataSource in order to perform its management of fixed part like getting a DB connection, cleaning up resources.

In this post Apache DBCP is used which provides pooled datasource and MYSQL is used as the back end.

Technologies used

  • Spring 4.2.1
  • Apache DBCP2
  • MYSQL 5.1.39
  • Java 8
  • Apache Maven 3.3.3

Maven dependencies

If you are using maven then you can provide dependencies in your pom.xml.

With all the dependencies your pom.xml should look something like this -

<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>org.netjs.prog</groupId>
  <artifactId>maven-spring</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>maven-spring</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <spring.version>4.2.1.RELEASE</spring.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    
    <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>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>
    
    <!-- Spring JDBC Support -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
    
   <!-- MySQL Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.39</version>
    </dependency>
    
    <!--  Apache DBCP connection pool -->
    <dependency>
       <groupId>org.apache.commons</groupId>
       <artifactId>commons-dbcp2</artifactId>
       <version>2.1</version>
    </dependency>
  </dependencies>
</project>

Alternatively you can download the jars and add them to the class path.

Database table

For this example I have created a table called employee with the columns id, name and age in the MYSQL DB. Column id is configured as auto increment checked so no need to pass id from your query as DB will provide value for it.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Setting up dependencies

First thing is to set up DataSource as a bean. I have used properties file to configure datasource where all the properties are there in the db.properties file.

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value = "${db.driverClassName}" />
    <property name="url" value = "${db.url}" />
    <property name="username" value = "${db.username}" />
    <property name="password" value = "${db.password}" />
    <property name="initialSize" value = "${pool.initialSize}" />
</bean>

Where as db.properties file which is under the config folder has all the properties.

db.properties

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=
db.password=
pool.initialSize=5

Description for the properties used here is as -

driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.

Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is jdbc:mysql://localhost:3306/netjs.

Username and password for the DB.

IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.

To use properties file you need to put following configuration in your XML.

<context:property-placeholder location="classpath:config/db.properties" />

Configuring NamedParameterJDBCTemplate

DataSource bean has to be provided as a reference in NamedParameterJDBCTemplate.

<bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">  
    <constructor-arg ref="dataSource"></constructor-arg> 
</bean>

Java Classes

Since Spring always promotes to use interfaces and there is also a JEE design pattern for database layer called DAO which also says the same thing - Separate low level data access code from the business layers.

So we have a EmployeeDAO interface with insert, update and delete methods and its implementing class EmployeeDAOImpl. There is also a model class Employee with all the getters/setters.

Employee.java class

public class Employee {
 private int empId;
 private String empName;
 private int age;
 
 public int getEmpId() {
  return empId;
 }
 public void setEmpId(int empId) {
  this.empId = empId;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
}

EmployeeDAO interface

public interface EmployeeDAO {
    
    public List<Employee> findAllEmployees();
    
    public Employee findEmployee(int empId);
    
}

EmployeeDAOImpl class

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
    private NamedParameterJdbcTemplate namedJdbcTemplate; 
    
    final String SELECT_BY_ID_QUERY = "SELECT id, name, age from EMPLOYEE where id = :id";
    final String SELECT_ALL_QUERY = "SELECT id, name, age from EMPLOYEE";
    
    @Autowired
    public EmployeeDAOImpl1(NamedParameterJdbcTemplate namedJdbcTemplate){
        this.namedJdbcTemplate = namedJdbcTemplate;
    }

    @Override
    public List<Employee> findAllEmployees() {
        return this.namedJdbcTemplate.query(SELECT_ALL_QUERY, new EmployeeMapper());
    }

    @Override
    public Employee findEmployee(int empId) {
        return this.namedJdbcTemplate.queryForObject(SELECT_BY_ID_QUERY, new MapSqlParameterSource("id", empId), new EmployeeMapper());
    }
    
    private static final class EmployeeMapper implements RowMapper<Employee> {
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException         {
            Employee emp = new Employee();
            emp.setEmpId(rs.getInt("id"));
            emp.setEmpName(rs.getString("name"));
            emp.setAge(rs.getInt("age"));
            return emp;
        }
    }
}
If you have more than one named parameter you can also use a hashMap or create a chain of addValue() methods with a MapSqlParameterSource class object. Refer Insert\Update using NamedParameterJDBCTemplate in Spring framework to see an example.

Notice how you are not writing any code for getting or closing connection, exception handling. All that fixed part is managed by the template class.

If there is any SQLException thrown that is also caught by JDBCTemplate and translated to one of the DataAccessException and rethrown.

But the main thing to demonstrate here is how callback works. Here template callbacks are used to query the DB and then map the returned result set to the model (Employee) object(s).

If you have noticed in findEmployee(int empId) method queryForObject method of JDBCTemplate is used which takes 3 parameters -

  • SQL query String.
  • Object of type SQLParameterSource that is where MapSqlParameterSource object is passed which stores all the named parameters to be bound to the query.
  • RowMapper object that maps a single result row to a Java object via a RowMapper.

Whereas in findAllEmployees() method query method is used which takes only two parameters –

  • SQL query String
  • RowMapper object

Main thing here is RowMapper object which in this example is the object of class EmployeeMapper implementing the RowMapper interface.

RowMapper interface has a single method mapRow which takes two arguments -

  • ResultSet - A table of data representing a database result set.
  • int - the number of the current row
and this method returns the result object for the current row.

For every row in the result set, JDBCTemplate calls the mapRow() method of the RowMapper interface implementing class. Arguments passed are ResultSet and an integer which is the number of the current row in the result set. Using that row number cursor is moved to the given row in the result set.

Full XML Configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">
    
   <context:component-scan base-package="org.netjs.daoimpl" />
    <!--  For reading properties files --> 
    <context:property-placeholder location="classpath:config/db.properties" />
    
    <bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">  
        <constructor-arg ref="dataSource"></constructor-arg> 
    </bean> 
    <!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
        <property name="namedJdbcTemplate" ref="namedJdbcTemplate"></property>  
    </bean> -->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value = "${db.driverClassName}" />
        <property name="url" value = "${db.url}" />
        <property name="username" value = "${db.username}" />
        <property name="password" value = "${db.password}" />
        <property name="initialSize" value = "${pool.initialSize}" />
    </bean>

</beans>

If you are not using automatic configuration, then you can uncomment the bean definition for the EmployeeDAO.

Test class

You can use the following code in order to test the code -

import java.util.List;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class App {

    public static void main(String[] args) {
        
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("appcontext.xml");
        EmployeeDAO dao = (EmployeeDAO)context.getBean("employeeDAOImpl");  
               
        Employee emp = dao.findEmployee(5);
        System.out.println("Name - "+ emp.getEmpName() + " Age - " + emp.getAge());        
        List<Employee> empList = dao.findAllEmployees();
        System.out.println("Name - "+ empList.get(3).getEmpName() + " Age - " + empList.get(3).getAge());
    }
}

That's all for this topic Select query using NamedParameterJDBCTemplate in Spring framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Spring example program using automatic configuration
  2. Select query using JDBCTemplate in Spring framework
  3. Configuring DataSource in Spring Framework
  4. Autodiscovery of bean using componenent-scan in Spring
  5. How to read properties file in Spring Framework

You may also like -

>>>Go to Spring tutorial page

Monday, 28 November 2016

Insert\Update using NamedParameterJDBCTemplate in Spring framework

In the post Insert\Update using JDBCTemplate in Spring framework we have already seen how JDBCTemplate can be used in Spring framework for data access. If you have noticed the examples, in the SQL queries there indexed parameters are used with the place holder '?'.

With indexed parameter it is very important that you pass the parameters in the correct order in the query. Problem with indexed parameter is that any change in the query may require a change in order of the parameters too. If you want to avoid that another option is named parameters. As the name suggests here each parameter is given a name and you bind the parameters to the query using those names.

NamedParameterJdbcTemplate

Spring framework provides NamedParameterJdbcTemplate class which adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ('?') arguments.

Here one thing to note is NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.

NamedParameterJdbcTemplate Example

Note that NamedParameterJdbcTemplate needs a DataSource in order to perform its management of fixed part like getting a DB connection, cleaning up resources.

In this post Apache DBCP is used which provides pooled datasource and MYSQL is used as the back end.

Technologies used

  • Spring 4.2.1
  • Apache DBCP2
  • MYSQL 5.1.39
  • Java 8
  • Apache Maven 3.3.3

Maven dependencies

If you are using maven then you can provide dependencies in your pom.xml.

With all the dependencies your pom.xml should look something like this -

<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>org.netjs.prog</groupId> <artifactId>maven-spring</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>maven-spring</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <spring.version>4.2.1.RELEASE</spring.version> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> <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>javax.inject</groupId> <artifactId>javax.inject</artifactId> <version>1</version> </dependency> <!-- Spring JDBC Support --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <!-- MySQL Driver --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.39</version> </dependency> <!-- Apache DBCP connection pool --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1</version> </dependency> </dependencies> </project>

Alternatively you can download the jars and add them to the class path.

Database table

For this example I have created a table called employee with the columns id, name and age in the MYSQL DB. Column id is configured as auto increment checked so no need to pass id from your query as DB will provide value for it.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Setting up dependencies

First thing is to set up DataSource as a bean. I have used properties file to configure datasource where all the properties are there in the db.properties file.

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value = "${db.driverClassName}" />
    <property name="url" value = "${db.url}" />
    <property name="username" value = "${db.username}" />
    <property name="password" value = "${db.password}" />
    <property name="initialSize" value = "${pool.initialSize}" />
</bean>

Where as db.properties file which is under the config folder has all the properties.

db.properties

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=
db.password=
pool.initialSize=5

Description for the properties used here is as -

driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.

Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is jdbc:mysql://localhost:3306/netjs.

Username and password for the DB.

IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.

To use properties file you need to put following configuration in your XML.

<context:property-placeholder location="classpath:config/db.properties" />

Configuring NamedParameterJDBCTemplate

DataSource bean has to be provided as a reference in NamedParameterJDBCTemplate.

<bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">  
    <constructor-arg ref="dataSource"></constructor-arg> 
</bean>

Java Classes

Since Spring always promotes to use interfaces and there is also a JEE design pattern for database layer called DAO which also says the same thing - Separate low level data access code from the business layers.

So we have a EmployeeDAO interface with insert, update and delete methods and its implementing class EmployeeDAOImpl. There is also a model class Employee with all the getters/setters.

Employee.java class

public class Employee {
 private int empId;
 private String empName;
 private int age;
 
 public int getEmpId() {
  return empId;
 }
 public void setEmpId(int empId) {
  this.empId = empId;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
}

EmployeeDAO interface

public interface EmployeeDAO {
 public int save(Employee employee);
 
 public void update(Employee employee);
 
 public void deleteEmpById(int empId);
}

EmployeeDAOImpl class

import java.util.HashMap;

import java.util.Map;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

public class EmployeeDAOImpl implements EmployeeDAO {
    private NamedParameterJdbcTemplate namedJdbcTemplate; 
    
    final String INSERT_QUERY = "insert into employee (name, age) values (:name, :age)";
    final String UPDATE_QUERY = "update employee set age = :age where id = :id";
    final String DELETE_QUERY = "delete from employee where id = :id";
    
    public NamedParameterJdbcTemplate getNamedJdbcTemplate() {
        return namedJdbcTemplate;
    }

    public void setNamedJdbcTemplate(NamedParameterJdbcTemplate namedJdbcTemplate) {
        this.namedJdbcTemplate = namedJdbcTemplate;
    }

    @Override
    public int save(Employee employee) {
        // Creating map with all required params
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("name", employee.getEmpName());
        paramMap.put("age", employee.getAge());
        // Passing map containing named params
        return namedJdbcTemplate.update(INSERT_QUERY, paramMap);  
    }

    @Override
    public void update(Employee employee) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("age", employee.getAge()).addValue("id", employee.getEmpId());
        int status = namedJdbcTemplate.update(UPDATE_QUERY, namedParameters); 
        if(status != 0){
            System.out.println("Employee data updated for ID " + employee.getEmpId());
        }else{
            System.out.println("No Employee found with ID " + employee.getEmpId());
        }
    }

    @Override
    public void deleteEmpById(int empId) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource("id", empId);
        int status = namedJdbcTemplate.update(DELETE_QUERY, namedParameters);
        if(status != 0){
            System.out.println("Employee data deleted for ID " + empId);
        }else{
            System.out.println("No Employee found with ID " + empId);
        }
    }
}

This class contains namedJdbcTemplate property which will be wired by the Spring framework. In the save method named parameters are stored in a Map and that map is passed. In the update method another option MapSqlParameterSource class is used which has addValue method. Using the addValue method key, value pair is stored and later passed to the query.

Also notice how you are not writing any code for getting or closing connection, exception handling. All that fixed part is managed by the template class itself.
If there is any SQLException thrown that is also caught by JDBCTemplate and translated to one of the DataAccessException and rethrown.

Full XML configuration

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- For reading properties files --> <context:property-placeholder location="classpath:config/db.properties" /> <bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="dataSource"></constructor-arg> </bean> <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl"> <property name="namedJdbcTemplate" ref="namedJdbcTemplate"></property> </bean> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <property name="driverClassName" value = "${db.driverClassName}" /> <property name="url" value = "${db.url}" /> <property name="username" value = "${db.username}" /> <property name="password" value = "${db.password}" /> <property name="initialSize" value = "${pool.initialSize}" /> </bean> </beans>

Here note that in the bean definition for NamedParameterJdbcTemplate, data source property is passed as a constructor argument.

Test class

You can use the following code in order to test the insertion, update and deletion.

import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class App {

    public static void main(String[] args) {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("appcontext.xml");
        
        EmployeeDAO dao=(EmployeeDAO)context.getBean("employeeDAO");  
        Employee emp = new Employee();
        emp.setEmpName("John");
        emp.setAge(25);
        int status = dao.save(emp);  
        System.out.println(status);  
        // For update
        emp.setEmpId(9);
        emp.setAge(25);
        dao.update(emp);
        
        // For delete
        dao.deleteEmpById(10);
    }
}

Using automatic configuration with @Repository annotation

You can also use component scanning to automatically scan and wire the classes. For that you can use @Repository annotation with your DAO implementation classes and @Autowired annotation to automatically wire dependencies.

In that case your EmployeeDAOImpl will look like this -

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

import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
    private NamedParameterJdbcTemplate namedJdbcTemplate; 
    
    final String INSERT_QUERY = "insert into employee (name, age) values (:name, :age)";
    final String UPDATE_QUERY = "update employee set age = :age where id = :id";
    final String DELETE_QUERY = "delete from employee where id = :id";
    
    @Autowired
    public EmployeeDAOImpl(NamedParameterJdbcTemplate namedJdbcTemplate){
        this.namedJdbcTemplate = namedJdbcTemplate;
    }
    

    @Override
    public int save(Employee employee) {
        // Creating map with all required params
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("name", employee.getEmpName());
        paramMap.put("age", employee.getAge());
        // Passing map containing named params
        return namedJdbcTemplate.update(INSERT_QUERY, paramMap);  
    }

    @Override
    public void update(Employee employee) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("age", employee.getAge()).addValue("id", employee.getEmpId());
        int status = namedJdbcTemplate.update(UPDATE_QUERY, namedParameters); 
        if(status != 0){
            System.out.println("Employee data updated for ID " + employee.getEmpId());
        }else{
            System.out.println("No Employee found with ID " + employee.getEmpId());
        }
    }

    @Override
    public void deleteEmpById(int empId) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource("id", empId);
        int status = namedJdbcTemplate.update(DELETE_QUERY, namedParameters);
        if(status != 0){
            System.out.println("Employee data deleted for ID " + empId);
        }else{
            System.out.println("No Employee found with ID " + empId);
        }
    }
}

XML Configuration

XML configuration will also change as you have to provide the base package to scan and you can also comment the bean definition for EmployeeDAO as it will be done automatically.

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <context:component-scan base-package="org.netjs.daoimpl" /> <!-- For reading properties files --> <context:property-placeholder location="classpath:config/db.properties" /> <bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="dataSource"></constructor-arg> </bean> <!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl"> <property name="namedJdbcTemplate" ref="namedJdbcTemplate"></property> </bean> --> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <property name="driverClassName" value = "${db.driverClassName}" /> <property name="url" value = "${db.url}" /> <property name="username" value = "${db.username}" /> <property name="password" value = "${db.password}" /> <property name="initialSize" value = "${pool.initialSize}" /> </bean> </beans>

That's all for this topic Insert\Update using NamedParameterJDBCTemplate in Spring framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Select query using JDBCTemplate in Spring framework
  2. Data access in Spring framework
  3. Configuring DataSource in Spring Framework
  4. How to inject prototype scoped bean in singleton bean
  5. What is Dependency Injection in Spring

You may also like -

>>>Go to Spring tutorial page

Friday, 25 November 2016

How to reverse number - Java Program

How to reverse a number in Java without using any API is asked in many interviews in order to check the logic. Though how to reverse a string is applicable for numbers also by treating them as String but the point here is to have a number and then reverse it without any using any in-built method.

It can be done in two ways

  • By iterating and using mathematical operators like divide and multiply.
  • Using recursive function.

When iterating through each digit of the number idea is to divide the given number by 10 and adding that remainder to an integer (which is initially initialized to 0) and multiplied by 10 in each iteration. That is required to move place values in the reversed number. Also divide the original number by 10 to get the quotient.

As example – If original number is 189 then first iteration will give remainder as 9 and quotient as 18. In the second iteration remainder will be 8 and quotient 1. And every time it is also multiplied by 10 for place value. Thus after second iteration it will be (9 * 10) + 8 = 98. Same for third iteration where remainder will be 1 and quotient 0. Thus making it (98 * 10) + 1 = 981. Which is the reversed number.

In recursive method you call the same method with one less digit that is done by dividing the number by 10. You have to print modulo division in every recursive call. Using recursion this way will also print the zeroes which the other way of iteration will not do. Meaning using the first method will give you 2 if input is 200. Whereas recursive method will give 002.

import java.util.Scanner;

public class ReverseNumber {

 public static void main(String[] args) {
  System.out.println("Please enter a number : ");
  Scanner sc = new Scanner(System.in);
  int scanInput = sc.nextInt();
  // Using recursion
  reverseRec(scanInput);
  // Using while loop
  reverseNum(scanInput);
 }
 
 // Method for reversing number using recursion
 public static void reverseRec(int num){
  //System.out.println("num" + num);
  if(num == 0)
   return;
  System.out.print(num % 10);
  reverseRec(num/10);
 }
 
 // Method for reversing number 
 public static void reverseNum(int num){
  int reversedNum = 0;
  int mod = 0;
  while(num != 0){
   mod = num % 10;
   reversedNum = (reversedNum * 10) + mod;
   num = num/10;
  }
  System.out.println("reversedNum -- " + reversedNum);
 }

}

That's all for this topic How to reverse number - Java Program. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Displaying prime numbers - Java program
  2. Converting float to int - Java program
  3. How to run threads in sequence - Java Program
  4. Print odd-even numbers using threads and semaphore
  5. How to append to a file in Java
  6. How to run a shell script from Java program

You may also like -

>>>Go to Java Programs page

Monday, 14 November 2016

Stream API in Java 8

If we have to point out the most important inclusion in Java 8 apart from lambda expression that has to be Stream API.

Stream API works in conjunction with lambda expression and provide an easy yet efficient way to perform data manipulation operations like sort, filter, map, reduce etc.

Stream in Stream API

A stream can be visualized as a pipeline. A stream pipeline consists of a source (which might be an array, a collection, a generator function, an I/O channel, etc), zero or more intermediate operations (which transform a stream into another stream, such as filter(Predicate)), and a terminal operation (which produces a result or side-effect, such as count() or forEach(Consumer)).

Stream data flow

As shown in the figure beginning of the stream has a data source like an array, collection, file from there data moves through the stream where the stream operation like sorting, filtering etc. is performed on the data. Here note one important point that stream operations do not modify the data source in any way. Any stream operation results in a creation of a new stream.
As example if you are filtering a stream using some condition that will result in a creation of new stream that produces the filtered results.

Stream Example

At this point let’s see an example to actually see Stream API in action –

In this example objective is to take a list as an input and sort it taking only those elements of list which are greater than 5 and finally print it.

List<Integer> numList = Arrays.asList(34, 6, 3, 12, 65, 1, 8);
numList.stream().filter((n) -> n > 5).sorted().forEach(System.out::println);

Output

6
8
12
34
65

Here it can be seen that the list is the data source for the stream and there are two intermediate operations – filter and sorted. Filter condition here is; take only those elements of the list which are greater than 5, in next stream operation of the stream pipeline sort that filtered output of the last stream using sorted method of the Stream API. Terminal operation here is forEach statement (provided in Java 8) which iterates the sorted result and displays them.

How stream can be obtained

Streams can be obtained in a number of ways. Some examples include:

  • From a Collection via the stream() and parallelStream() methods;
  • From an array via Arrays.stream(Object[]);
  • From static factory methods on the stream classes, such as Stream.of(Object[]), IntStream.range(int, int) or Stream.iterate(Object, UnaryOperator);
  • The lines of a file can be obtained from BufferedReader.lines();
  • Streams of file paths can be obtained from methods in Files;
  • Streams of random numbers can be obtained from Random.ints();
  • Numerous other stream-bearing methods in the JDK, including BitSet.stream(), Pattern.splitAsStream(java.lang.CharSequence), and JarFile.stream().

Types of Stream operations

Stream operations are divided into intermediate and terminal operations, and are combined to form stream pipelines.

Intermediate operations return a new stream. They are always lazy; executing an intermediate operation such as filter() does not actually perform any filtering, but instead creates a new stream that, when traversed, contains the elements of the initial stream that match the given predicate. Traversal of the pipeline source does not begin until the terminal operation of the pipeline is executed.

Terminal operations such as Stream.forEach or IntStream.sum, may traverse the stream to produce a result or a side-effect. After the terminal operation is performed, the stream pipeline is considered consumed, and can no longer be used; if you need to traverse the same data source again, you must return to the data source to get a new stream.

Features of Stream

Some of the features of the stream are –

  • No storage - A stream is not a data structure that stores elements; instead, it conveys elements from a source such as a data structure, an array, a generator function, or an I/O channel, through a pipeline of computational operations.
  • Functional in nature - An operation on a stream produces a result, but does not modify the data source. As example filtering a Stream obtained from a collection produces a new Stream without the filtered elements, rather than removing elements from the source collection.
  • Lazy behavior - Intermediate operations are always lazy. These operations do not start as soon as you reach that intermediate operation, it’s only when stream hits the terminal operation that it start executing operations.

    As example – if you execute the following code you won’t get any output as only filter operation is there which is an intermediate operation and it won’t execute unless until there is a terminal operation.

    List<Integer> numList = Arrays.asList(34, 6, 3, 12);  
    numList.stream().filter((n) -> {
        System.out.println("While filtering - " + n);
        return true;
    });
    

    If you add a terminal operation like forEach in this code then only both the operation will get executed.

    List<Integer> numList = Arrays.asList(34, 6, 3, 12);  
    List<Integer> numList = Arrays.asList(34, 6, 3, 12);  
    numList.stream().filter((n) -> {
        System.out.println("While filtering - " + n);
        return true;
    }).forEach(n -> System.out.println("forEach iteration - " + n));
    
  • Output

    While filtering - 34
    forEach iteration - 34
    While filtering - 6
    forEach iteration - 6
    While filtering - 3
    forEach iteration - 3
    While filtering - 12
    forEach iteration - 12
    

    Besides lazy execution also provides opportunities for optimization. For example, "find the first String with three consecutive vowels" need not examine all the input strings.

  • Possibly unbounded - While collections have a finite size, streams need not. Short-circuiting operations such as limit(n) or findFirst() can allow computations on infinite streams to complete in finite time.
  • Consumable - The elements of a stream are only visited once during the life of a stream. Once terminal operation is executed that stream is deemed consumed and it can’t be used again. A new stream must be generated to revisit the same elements of the source.

Stateless and Stateful operations

Intermediate operations are further divided into stateless and stateful operations.

Stateless operations, such as filter and map, retain no state from previously seen element when processing a new element, each element can be processed independently of operations on other elements.

Stateful operations, such as distinct and sorted, may incorporate state from previously seen elements when processing new elements. Stateful operations may need to process the entire input before producing a result. For example, one cannot produce any results from sorting a stream until one has seen all elements of the stream.

That's all for this topic Stream API in Java 8. If you have any doubt or any suggestions to make please drop a comment. Thanks!

Reference - https://docs.oracle.com/javase/8/docs/api/java/util/stream/package-summary.html


Related Topics

  1. Functional interface annotation in Java 8
  2. Method reference in Java 8
  3. interface static methods in Java 8
  4. Optional class in Java 8
  5. String join() method in Java 8
  6. effectively final in Java 8

You may also like -

>>>Go to Java advance topics page

Friday, 11 November 2016

How to read properties file in Spring Framework

There are scenarios when you have to provide few configuration properties in order to configure the resource like in case of Database you need to provide driver class, DB location, user name and password or in case of sending mail through your application you need to provide properties like SMTP host, user name, password.

Many a times developers put all these details in the Spring XML configuration file itself which is not a good practice. It’s better to put them in a properties file that way you can have specific properties files like db.properties, mail.properties, app.properties etc. That way any change in any setting will require you to change the specific properties file only.

In Spring setting property values by reading a properties file can be done using -

  • XML configuration
  • Using @PropertySource Annotation

Using XML configuration

Suppose you have your Database properties in a properties file called db.properties residing under config folder under resources folder.

db.properties (for connecting to MYSQL DB)

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=root
db.password=password
pool.initialSize=5

Then you can use ${property key} placeholders in <bean> definitions. In order to resolve these placeholders you must register a PropertySourcesPlaceholderConfigurer. This happens automatically when using <context:property-placeholder> in XML.

Full XML configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">
    
   
     <!--  For reading properties files --> 
    <context:property-placeholder location="classpath:config/db.properties" />
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
        <property name="dataSource" ref="dataSource"></property>  
    </bean>  
    <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>  
    </bean>
    
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value = "${db.driverClassName}" />
        <property name="url" value = "${db.url}" />
        <property name="username" value = "${db.username}" />
        <property name="password" value = "${db.password}" />
        <property name="initialSize" value = "${pool.initialSize}" />
    </bean>

</beans>

Using @PropertySource Annotation

Spring also has @PropertySource annotation (added in Spring 3.1) for reading properties file. It can be used with @Value annotation to read the value of the given property.

Example Program

Given a file db.properties (as used above)containing the key/value pair, the following @Configuration class uses @PropertySource along with @Value annotation to read properties.

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;

@Configuration
@PropertySource("classpath:config/db.properties")
public class DBConfig {
    @Value("${db.driverClassName}")
    private String dbDriverClass;
    @Value("${db.url}")
    private String dbUrl;
    @Value("${db.username}")
    private String dbUser;
    @Value("${db.password}")
    private String dbPwd;
    
    @Bean
    public BasicDataSource dataSource() {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(dbDriverClass);
        ds.setUrl(dbUrl);
        ds.setUsername(dbUser);
        ds.setPassword(dbPwd);
        return ds;
    }
    
    //register PropertySourcesPlaceholderConfigurer
    //in order to resolve ${...} placeholders
    @Bean
    public static PropertySourcesPlaceholderConfigurer propertyConfigInDev() {
        return new PropertySourcesPlaceholderConfigurer();
    }
} 

Here you can see how fields are annotated with @Value(property key name) annotation in order to map them with the specific property in the .properties file.

In order to resolve ${...} placeholders in <bean> definitions or @Value annotations using properties from a PropertySource, one must register a PropertySourcesPlaceholderConfigurer. This happens automatically when using <context:property-placeholder> in XML (in above example), but must be explicitly registered using a static @Bean method when using @Configuration classes. That’s what is done in DBConfig class.

If you want to run this you can use the following class -

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;

public class App {

 public static void main(String[] args) {
  AbstractApplicationContext context = new AnnotationConfigApplicationContext(DBConfig.class);
  BasicDataSource ds = (BasicDataSource)context.getBean("dataSource");
  System.out.println("URL - " + ds.getUrl());
 }

}

Using with Spring’s Environment

Rather than using @Value annotation, Environment should be used to read properties file. Actually @PropertySource annotation adds a PropertySource to Spring's Environment so that can be used to make your code simpler.

Example with Environment

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;


import org.springframework.core.env.Environment;

@Configuration
@PropertySource("classpath:config/db.properties")
public class DBConfig {
 @Autowired
 private Environment env;
 
 @Bean
 public BasicDataSource dataSource() {
  BasicDataSource ds = new BasicDataSource();
  System.out.println("User " + env.getProperty("db.username"));
  ds.setDriverClassName(env.getProperty("db.driverClassName"));
  ds.setUrl(env.getProperty("db.url"));
  ds.setUsername(env.getProperty("db.username"));
  ds.setPassword(env.getProperty("db.password"));
  return ds;
 }
}

In that case you just need to Autowire (or Inject) Environment into your Config class and use Environment object to get property value.

property overriding with @PropertySource

In cases where a given property key exists in more than one .properties file, the last @PropertySource annotation processed will 'win' and override. For example, given two properties files a.properties and b.properties, consider the following two configuration classes that reference them with @PropertySource annotations:

 @Configuration
 @PropertySource("classpath:/com/myco/a.properties")
 public class ConfigA { }

 @Configuration
 @PropertySource("classpath:/com/myco/b.properties")
 public class ConfigB { }

The override ordering depends on the order in which these classes are registered with the application context.

Using ignoreResourceNotFound

In case your properties file is optional and not having it should not throw exception you can use ignoreResourceNotFound and set it as true. Default is false.

As example

If you want to load test.properties file and want to ignore the resource if not found then you can do it as -

@Configuration
@PropertySource(value="classpath:config/test.properties", ignoreResourceNotFound=true)
public class DBConfig {
 @Autowired
 private Environment env;
 ..........
 ..........

That's all for this topic How to read properties file in Spring Framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Data access in Spring framework
  2. Configuring DataSource in Spring Framework
  3. Select query using JDBCTemplate in Spring framework
  4. How to inject prototype scoped bean in singleton bean
  5. @Resource annotation in Spring autowiring

You may also like -

>>>Go to Spring tutorial page

Wednesday, 9 November 2016

Select query using JDBCTemplate in Spring framework

In the post Insert\Update using JDBCTemplate in Spring framework I have already discussed how JDBCTemplate can be used for inserting and updating data in the DB. I left behind the part to read from Database using Select query. Purpose for doing that to discuss in detail the callback part of the JDBCTemplate.

In the post Data access in Spring framework it has been discussed in detail how Spring framework provides templates to manage the fixed part and uses call back to handle the variable part. Fetching data from DB using select query has, as usual, the fixed part like getting connection, cleaning up, handling exception but at the same time Spring framework does need help to map the fetched data to the model. That’s where callback comes into picture.

In this post Apache DBCP is used for providing pooled datasource and MYSQL is used as the back end.

Technologies used

  • Spring 4.2.1
  • Apache DBCP2
  • MYSQL 5.1.39
  • Java 8
  • Apache Maven 3.3.3

Maven dependencies

If you are using maven then you can provide dependencies in your pom.xml.

With all the dependencies your pom.xml should look something like this -

<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>org.netjs.prog</groupId>
  <artifactId>maven-spring</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>maven-spring</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <spring.version>4.2.1.RELEASE</spring.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    
    <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>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>
    
    <!-- Spring JDBC Support -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
    
   <!-- MySQL Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.39</version>
    </dependency>
    
    <!--  Apache DBCP connection pool -->
    <dependency>
       <groupId>org.apache.commons</groupId>
       <artifactId>commons-dbcp2</artifactId>
       <version>2.1</version>
    </dependency>
  </dependencies>
</project>

Alternatively you can download the jars and add them to the class path.

Database table

For this example I have created a table called employee with the columns id, name and age in the MYSQL DB. Column id is configured as auto increment checked so no need to pass id from your query as DB will provide value for it.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Setting up dependencies

First thing is to set up DataSource as a bean. I have used properties file to configure datasource where all the properties are there in the db.properties file.

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value = "${db.driverClassName}" />
    <property name="url" value = "${db.url}" />
    <property name="username" value = "${db.username}" />
    <property name="password" value = "${db.password}" />
    <property name="initialSize" value = "${pool.initialSize}" />
</bean>

Where as db.properties file which is under the config folder has all the properties.

db.properties

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=
db.password=
pool.initialSize=5

Description for the properties used here is as -

driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.

Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is jdbc:mysql://localhost:3306/netjs.

Username and password for the DB.

IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.

To use properties file you need to put following configuration in your XML.

<context:property-placeholder location="classpath:config/db.properties" />

Configuring JDBC Template

DataSource bean has to be provided as a reference in JDBCTemplate.

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="dataSource"></property>  
</bean>

Java Classes

Since Spring always promotes to use interfaces and there is also a JEE design pattern for database layer called DAO which also says the same thing - Separate low level data access code from the business layers.

So we have a EmployeeDAO interface with find methods and its implementing class EmployeeDAOImpl. There is also a model class Employee with all the getters/setters.

Employee.java class

public class Employee {
 private int empId;
 private String empName;
 private int age;
 
 public int getEmpId() {
  return empId;
 }
 public void setEmpId(int empId) {
  this.empId = empId;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
}

EmployeeDAO interface

import org.netjs.model.Employee;

public interface EmployeeDAO {
    public List<Employee> findAllEmployees();
    
    public Employee findEmployee(int empId);
}

EmployeeDAOImpl class

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate; 
    
    final String SELECT_BY_ID_QUERY = "SELECT id, name, age from EMPLOYEE where id = ?";
    final String SELECT_ALL_QUERY = "SELECT id, name, age from EMPLOYEE";
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
        this.jdbcTemplate = jdbcTemplate;  
    }
    
    public Employee findEmployee(int empId) {
        return this.jdbcTemplate.queryForObject(SELECT_BY_ID_QUERY, new EmployeeMapper(), empId);
    }

    public List<Employee> findAllEmployees() {
        return this.jdbcTemplate.query(SELECT_ALL_QUERY, new EmployeeMapper());
    }

    private static final class EmployeeMapper implements RowMapper<Employee> {
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException         {
            Employee emp = new Employee();
            emp.setEmpId(rs.getInt("id"));
            emp.setEmpName(rs.getString("name"));
            emp.setAge(rs.getInt("age"));
            return emp;
        }
    }
    
}

Notice how you are not writing any code for getting or closing connection, exception handling. All that fixed part is managed by the JDBCTemplate class. Its the JDBCTemplate which is getting the connection using the DataSource provided to it, creating and executing the statement and closing the connection.

If there is any SQLException thrown that is also caught by JDBCTemplate and translated to one of the DataAccessException and rethrown.

But the main thing to demonstrate here is how callback works. Here template callbacks are used to query the DB and then map the returned result set to the model (Employee) object(s).

If you have noticed in

findEmployee(int empId)

method

queryForObject

method of JDBCTemplate is used which takes 3 parameters -

  • SQL query String
  • RowMapper object that maps a single result row to a Java object via a RowMapper
  • varargs to bind to the query

Whereas in findAllEmployees() method query method is used which takes only two parameters –

  • SQL query String
  • RowMapper object

as there are no parameters to be passed to the SQL so varargs are not needed in this case.

Main thing here is RowMapper object which in this example is the object of class EmployeeMapper implementing the RowMapper interface.
RowMapper interface has a single method mapRow which takes two arguments -

  1. ResultSet - A table of data representing a database result set
  2. int - the number of the current row
and this method returns the result object for the current row.

For every row in the result set, JDBCTemplate calls the mapRow() method of the RowMapper interface implementing class. Arguments passed are ResultSet and an integer which is the number of the current row in the result set. Using that row number cursor is moved to the given row in the result set.

XML Configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">
    
    <context:component-scan base-package="org.netjs.daoimpl" />
    <!--  For reading properties files --> 
    <context:property-placeholder location="classpath:config/db.properties" />
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
        <property name="dataSource" ref="dataSource"></property>  
    </bean>  
    <!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>  
    </bean> -->
    
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value = "${db.driverClassName}" />
        <property name="url" value = "${db.url}" />
        <property name="username" value = "${db.username}" />
        <property name="password" value = "${db.password}" />
        <property name="initialSize" value = "${pool.initialSize}" />
    </bean>

</beans>

If you are not using automatic configuration, then you can uncomment the bean definition for the EmployeeDAO.

Test class

You can use the following code in order to test the code -

import java.util.List;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class App {

    public static void main(String[] args) {
        
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("appcontext.xml");
        EmployeeDAO dao = (EmployeeDAO)context.getBean("employeeDAOImpl");  
        
        // Uncomment this to find employee by ID
        /*Employee emp = dao.findEmployee(5);
        System.out.println("Name - "+ emp.getEmpName() + " Age - " + emp.getAge());*/
        
        List<Employee> empList = dao.findAllEmployees();
        System.out.println("Name - "+ empList.get(1).getEmpName() + " Age - " + empList.get(1).getAge());
    }
}

RowMapper implementation as Lambda Expression

RowMapper interface has only single method mapRow which means it is a functional interface. Starting Java 8 it can be implemented as a lambda expression. Since same implementation is used by two methods findEmployee() and findAllEmployees() so it is better to implement it as a lambda block rather than as an inline lambda.

In that case findEmployee() and findAllEmployees() methods will change like this -

    public Employee findEmployee(int EmpId) {
        return this.jdbcTemplate.queryForObject(SELECT_BY_ID_QUERY, getMap(), EmpId);
    }

    public List<Employee> findAllEmployees() {
        return this.jdbcTemplate.query(SELECT_ALL_QUERY, getMap());
    }
    
    private RowMapper<Employee> getMap(){
        // Lambda block
        RowMapper<Employee> empMap = (rs, rowNum) -> {
            Employee emp = new Employee();
            emp.setEmpId(rs.getInt("id"));
            emp.setEmpName(rs.getString("name"));
            emp.setAge(rs.getInt("age"));
            return emp;
        };
        return empMap;
    }

Here it can be seen that lambda block is implemented inside method getMap(). Here lambda is assigned to a functional interface (RowMapper in this case) variable. It has two arguments rs and rowNum, since it is implementing mapRow() method of the RowMapper class so compiler will infer that rs and rowNum are of type ResultSet and int respectively.

That's all for this topic Select query using JDBCTemplate in Spring framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Data access in Spring framework
  2. Configuring DataSource in Spring Framework
  3. How to inject prototype scoped bean in singleton bean
  4. Bean definition inheritance in Spring
  5. Spring example program using automatic configuration

You may also like -

>>>Go to Spring tutorial page

Monday, 7 November 2016

Insert\Update using JDBCTemplate in Spring framework

In the post Data access in Spring framework we have already seen how Spring provides templates for various persistence methods and how templates divide the data access code into fixed part and variable part. Where Spring framework manages the fixed part and custom code which is provided by the user is handled through callbacks. In this post we’ll see how to use JDBCTemplate to insert/update data into the database.

Note that JDBCTemplate needs a DataSource in order to perform its management of fixed part like getting a DB connection, cleaning up resources.
In this post Apache DBCP is used for providing pooled datasource and MYSQL is used as the back end.

Technologies used

  • Spring 4.2.1
  • Apache DBCP2
  • MYSQL 5.1.39
  • Java 8
  • Apache Maven 3.3.3

Maven dependencies

If you are using maven then you can provide dependencies in your pom.xml.

With all the dependencies your pom.xml should look something like this -

<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>org.netjs.prog</groupId>
  <artifactId>maven-spring</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>maven-spring</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <spring.version>4.2.1.RELEASE</spring.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    
    <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>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>
    
    <!-- Spring JDBC Support -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
    
   <!-- MySQL Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.39</version>
    </dependency>
    
    <!--  Apache DBCP connection pool -->
    <dependency>
       <groupId>org.apache.commons</groupId>
       <artifactId>commons-dbcp2</artifactId>
       <version>2.1</version>
    </dependency>
  </dependencies>
</project>

Alternatively you can download the jars and add them to the class path.

Database table

For this example I have created a table called employee with the columns id, name and age in the MYSQL DB. Column id is configured as auto increment checked so no need to pass id from your query as DB will provide value for it.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Setting up dependencies

First thing is to set up DataSource as a bean. I have used properties file to configure datasource where all the properties are there in the db.properties file.

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value = "${db.driverClassName}" />
    <property name="url" value = "${db.url}" />
    <property name="username" value = "${db.username}" />
    <property name="password" value = "${db.password}" />
    <property name="initialSize" value = "${pool.initialSize}" />
</bean>

Where as db.properties file which is under the config folder has all the properties.

db.properties

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=
db.password=
pool.initialSize=5

Description for the properties used here is as -

driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.

Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is jdbc:mysql://localhost:3306/netjs.

Username and password for the DB.

IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.

To use properties file you need to put following configuration in your XML.

<context:property-placeholder location="classpath:config/db.properties" />

Configuring JDBC Template

DataSource bean has to be provided as a reference in JDBCTemplate.

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="dataSource"></property>  
</bean>

Java Classes

Since Spring always promotes to use interfaces and there is also a JEE design pattern for database layer called DAO which also says the same thing - Separate low level data access code from the business layers.

So we have a EmployeeDAO interface with insert, update and delete methods and its implementing class EmployeeDAOImpl. There is also a model class Employee with all the getters/setters.

Employee.java class

public class Employee {
 private int empId;
 private String empName;
 private int age;
 
 public int getEmpId() {
  return empId;
 }
 public void setEmpId(int empId) {
  this.empId = empId;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
}

EmployeeDAO interface

import org.netjs.model.Employee;

public interface EmployeeDAO {
 public int save(Employee employee);
 
 public void update(Employee employee);
 
 public void deleteEmpById(int empId);
}

EmployeeDAOImpl class

import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.jdbc.core.JdbcTemplate;

public class EmployeeDAOImpl implements EmployeeDAO {
    private JdbcTemplate jdbcTemplate;  
    final String INSERT_QUERY = "insert into employee (name, age) values (?, ?)";
    final String UPDATE_QUERY = "update employee set age = ? where id = ?";
    final String DELETE_QUERY = "delete from employee where id = ?";
 
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
        this.jdbcTemplate = jdbcTemplate;  
    }
    
    public int save(Employee employee) {
        return jdbcTemplate.update(INSERT_QUERY, employee.getEmpName(), employee.getAge());   
    }

    public void update(Employee employee) {
        int status = jdbcTemplate.update(UPDATE_QUERY, employee.getAge(), employee.getEmpId()); 
        if(status != 0){
            System.out.println("Employee data updated for ID " + employee.getEmpId());
        }else{
            System.out.println("No Employee found with ID " + employee.getEmpId());
        }
        
    }

    public void deleteEmpById(int empId) {
        int status = jdbcTemplate.update(DELETE_QUERY, empId);
        if(status != 0){
            System.out.println("Employee data deleted for ID " + empId);
        }else{
            System.out.println("No Employee found with ID " + empId);
        }
    }
}

This class contains jdbcTemplate property which will be injected by the Spring framework. In the save method insert query is executed and the parameters are provided to it. Note these are indexed parameters.

Also notice how you are not writing any code for getting or closing connection, exception handling. All that fixed part is managed by the JDBCTemplate class. Its the JDBCTemplate which is getting the connection using the DataSource provided to it, creating and executing the statement and closing the connection.

If there is any SQLException thrown that is also caught by JDBCTemplate and translated to one of the DataAccessException and rethrown.

XML configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">
    
    <context:property-placeholder location="classpath:config/db.properties" />
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
        <property name="dataSource" ref="dataSource"></property>  
    </bean>  
    <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>  
    </bean>
    
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value = "com.mysql.jdbc.Driver" />
        <property name="url" value = "jdbc:mysql://localhost:3306/netjs" />
        <property name="username" value = "root" />
        <property name="password" value = "admin" />
        <property name="initialSize" value = "5" />
    </bean>

</beans>

Test class

You can use the following code in order to test the insertion and update

import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class App {

 public static void main(String[] args) {
     ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("appcontext.xml");
  
     EmployeeDAO dao=(EmployeeDAO)context.getBean("employeeDAO");  
     Employee emp = new Employee();
     emp.setEmpName("John");
     emp.setAge(25);
     int status = dao.save(emp);  
     System.out.println(status);  
            
     // For update
     emp.setEmpId(12);
     emp.setAge(35);
     
     dao.update(emp);
     
     // For delete
     dao.deleteEmpById(10);
 }
}

Using automatic configuration with @Repository annotation

You can also use component scanning to automatically scan and wire the classes. For that you can use @Repository annotation with your DAO implementation classes and @Autowired annotation to automatically wire dependencies.

In that case your EmployeeDAOImpl will look like this -

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
 @Autowired
 private JdbcTemplate jdbcTemplate; 
 
 final String INSERT_QUERY = "insert into employee (name, age) values (?, ?)";
 final String UPDATE_QUERY = "update employee set age = ? where id = ?";
 final String DELETE_QUERY = "delete from employee where id = ?";
 
 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
     this.jdbcTemplate = jdbcTemplate;  
 }
 
 public int save(Employee employee) {
            return jdbcTemplate.update(INSERT_QUERY, employee.getEmpName(), employee.getAge());   
 }

 public void update(Employee employee) {
     int status = jdbcTemplate.update(UPDATE_QUERY, employee.getAge(), employee.getEmpId()); 
     if(status != 0){
  System.out.println("Employee data updated for ID " + employee.getEmpId());
     }else{
  System.out.println("No Employee found with ID " + employee.getEmpId());
     }
  
 }

 public void deleteEmpById(int empId) {
     int status = jdbcTemplate.update(DELETE_QUERY, empId);
     if(status != 0){
  System.out.println("Employee data deleted for ID " + empId);
     }else{
  System.out.println("No Employee found with ID " + empId);
     }
 }
}

XML Configuration

XML configuration will also change as you have to provide the base package to scan and you can also comment the bean definition for EmployeeDAO as it will be done automatically.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">
    
    <context:component-scan base-package="org.netjs.daoimpl" />
    
    <context:property-placeholder location="classpath:config/db.properties" />
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
        <property name="dataSource" ref="dataSource"></property>  
    </bean>  
    <!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>  
    </bean> -->
    
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value = "${db.driverClassName}" />
        <property name="url" value = "${db.url}" />
        <property name="username" value = "${db.username}" />
        <property name="password" value = "${db.password}" />
        <property name="initialSize" value = "${pool.initialSize}" />
    </bean>

</beans>

That's all for this topic Insert\Update using JDBCTemplate in Spring framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Data access in Spring framework
  2. Configuring DataSource in Spring Framework
  3. What is Dependency Injection in Spring
  4. Autowiring using annotations in Spring
  5. How to inject prototype scoped bean in singleton bean

You may also like -

>>>Go to Spring tutorial page