Spring: Data access using JdbcDaoSupport

Spring: Data access using JdbcDaoSupport

Spring Data Access  

Similar to JDBCTemplate by extending JdbcDaoSupport classes we can achieve JBDC dataaccess and just need to inject data source into EmpDaoImpl Objects through configuration file. Rest will be taken care by Spring.

In our earlier tutorials we have seen about Spring JDBC and JDBCTemplate examples. Lets take same example class and will extend JdbcDaoSupport class.

pom.xml remains same

Employee.java remains same

EmpDao.java remains same

MyTestClass.java

package com.app;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.app.dao.EmpDao;
import com.app.model.Employee;

public class MyTestClass {

 public static void main(String[] args) {
  
  ClassPathXmlApplicationContext appContext = new ClassPathXmlApplicationContext("spring-jdbc.xml");

  EmpDao object = appContext.getBean(EmpDao.class);
  
  Employee emp = new Employee(143, "Bill", "Director");
  
  object.insert(emp);
  
  appContext.close();
 }
}

EmpDaoImpl.java

package com.app.dao;


import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.app.model.Employee;

public class EmpDaoImpl extends JdbcDaoSupport implements EmpDao {

 public void insert(Employee emp) {
  
  String query = "INSERT INTO employee (id, empname, designation) VALUES (?, ?, ?)";
  
  getJdbcTemplate().update(query, new Object[] { emp.getId(), emp.getEmpName(), emp.getDesignation() });
 }
}

OUTPUT:

Spring: Data access with JDBCTemplate

Spring: Data access with JDBCTemplate

Spring Data Access  

Spring provides a template class called JdbcTemplate that makes it easy to work with SQL relational databases and JDBC. Most JDBC code is mired in resource acquisition, connection management, exception handling, and general error checking that is wholly unrelated to what the code is meant to achieve. The JdbcTemplate takes care of all of that for you. All you have to do is focus on the task at hand.

In our earlier tutorial we have seen about Spring JDBC with simple example. Lets take same example for this tutorial and will implement separate method called insertByJDBCTemplate() which uses Spring JDBCTemplate to insert a record.

pom.xml remains same

Employee.java remains same

EmpDao.java

package com.app.dao;

import com.app.model.Employee;

public interface EmpDao {

 public void insert(Employee emp);
 public void insertByJDBCTemplate(Employee emp);
 
}

EmpDaoImpl.java

package com.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.app.model.Employee;

public class EmpDaoImpl implements EmpDao {

 
 private DataSource dataSource;
 private JdbcTemplate jdbcTemplate;
 
 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
 }
  
 public void insert(Employee emp) {
 
  String query = "INSERT INTO employee (id, empname, designation) VALUES (?, ?, ?)";
  Connection con = null;
 
  try {
   con = dataSource.getConnection();
   PreparedStatement ps = con.prepareStatement(query);
   ps.setInt(1, emp.getId());
   ps.setString(2, emp.getEmpName());
   ps.setString(3, emp.getDesignation());
   ps.executeUpdate();
   ps.close();
 
  } catch (SQLException e) {
   throw new RuntimeException(e);
 
  } finally {
   if (con != null) {
    try {
     con.close();
    } catch (SQLException e) {}
   }
  }  
 }
 
 
 
 public void insertByJDBCTemplate(Employee emp) {
  
  String query = "INSERT INTO employee (id, empname, designation) VALUES (?, ?, ?)";
  
  jdbcTemplate = new JdbcTemplate(dataSource);
   
  jdbcTemplate.update(query, new Object[] { emp.getId(), emp.getEmpName(), emp.getDesignation() });
 }
}

MyTestClass.java

package com.app;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.app.dao.EmpDao;
import com.app.model.Employee;

public class MyTestClass {

 public static void main(String[] args) {
  
  ClassPathXmlApplicationContext appContext = new ClassPathXmlApplicationContext("spring-jdbc.xml");

  EmpDao object = appContext.getBean(EmpDao.class);
  
  Employee emp = new Employee(10011, "Steve", "CEO");
  
  //object.insert(emp);
  object.insertByJDBCTemplate(emp);
  
  appContext.close();
 }
}

OUTPUT:

Spring: Data access with JDBC simple example

Spring: Data access with JDBC simple example

spring JDBC  

In our earlier tutorial we have seen about Spring + Hibernate with simple example. Now lets see sample example with Spring JDBC by using DataSource. For more notes on Spring JDBC you can refer to spring doc link.
Here we used MySql database to demonstrate this example.

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.app.spring.datasource</groupId>
  <artifactId>springdatasource</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  
  <dependencies>
  
   <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-context</artifactId>
  <version>4.1.6.RELEASE</version>
 </dependency>
   <dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.35</version>
 </dependency>
   <dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>4.1.6.RELEASE</version>
 </dependency>
   
  </dependencies>
  
</project>

Employee.java

package com.app.model;

public class Employee {
 
 private int id;
 
 private String empName;
 
 private String designation;
 
 public Employee(int id, String empName, String designation){
  this.id = id;
  this.empName = empName;
  this.designation = designation;
 }
 
 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public String getDesignation() {
  return designation;
 }
 public void setDesignation(String designation) {
  this.designation = designation;
 }  
}

EmpDao.java  

package com.app.dao;

import com.app.model.Employee;

public interface EmpDao {

 public void insert(Employee emp);
 
}

EmpDaoImpl.java

package com.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;

import com.app.model.Employee;

public class EmpDaoImpl implements EmpDao {

 
 private DataSource dataSource;
  
 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
 }
  
 public void insert(Employee emp) {
 
  String query = "INSERT INTO employee (id, empname, designation) VALUES (?, ?, ?)";
  Connection con = null;
 
  try {
   con = dataSource.getConnection();
   PreparedStatement ps = con.prepareStatement(query);
   ps.setInt(1, emp.getId());
   ps.setString(2, emp.getEmpName());
   ps.setString(3, emp.getDesignation());
   ps.executeUpdate();
   ps.close();
 
  } catch (SQLException e) {
   throw new RuntimeException(e);
 
  } finally {
   if (con != null) {
    try {
     con.close();
    } catch (SQLException e) {}
   }
  }  
 }
}

spring-jdbc.xml

<beans xmlns="http://www.springframework.org/schema/beans"
 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-4.1.xsd">

 
 <bean id="dataSource"
  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 
  <property name="driverClassName" value="com.mysql.jdbc.Driver" />
  <property name="url" value="jdbc:mysql://localhost:3306/roboticapp" />
  <property name="username" value="root" />
  <property name="password" value="root" />
 </bean>
 
 <bean id="empDS" class="com.app.dao.EmpDaoImpl">
  <property name="dataSource" ref="dataSource" />
 </bean>
 
</beans>

MyTestClass.java

package com.app;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.app.dao.EmpDao;
import com.app.model.Employee;

public class MyTestClass {

 public static void main(String[] args) {
  
  ClassPathXmlApplicationContext appContext = new ClassPathXmlApplicationContext("spring-jdbc.xml");

  EmpDao object = appContext.getBean(EmpDao.class);
  
  Employee emp = new Employee(100, "Mark", "Manager");
  
  object.insert(emp);
  
  appContext.close();
 }
}

OUTPUT: