Recent Posts

Wednesday, 31 July 2019

Spring JDBC/DAO

Spring JDBC/DAO
☀ DAO [Data Access Object], it is a design pattern, it able to provide very good environment to separate Data Access logic from Business Processing logic.
☀ In enterprise Applications, to prepare Data Access Layer we will use DAO Design pattern.
Advantages of DAOs in Enterprise Applications
☀ We are able to hide all data access implementation from Business/ Service Layer.
☀ It is very simple to switch data access layer from one data access tech to anther data access tech. without giving any effect to Service/Business Layer, that is from JDBC to Hibernate etc.
☀ DAOs are able to provide centralized Data Access in enterprise Application, it simplifies the maintenance of Enterprise Applications.
☀ While preparing Service/Business layer Code Simplification is possible, that is, it is very simple to prepare Service/Business layer.
☀ We are able get Standardization to prepare Data Access layer with DAOs.
Drawbacks with DAOs
☀ It adds one more layer to enterprise Application, may get maintenance problems.
☀ Along with DAOs, we have to implement some other Design patterns like Factory Classes, DTO [Data Transfer Objects] etc. in enterprise applications.

Guidelines to prepare DAOs in Enterprise Applications
1. Prepare a separate DAO interface: Prepare a separate DAO interface with the required DAO methods, which must represent CRUD operations.
2. Provide an implementation to DAO interface
3. Prepare DTOs as per the requirement
4. Create Factory Methods/ Factory Classes to generate DAOs
5. We must not cache DAO references, because, Factory classes/ Factory methods are providing single instances of DAO to the service layer, if DAO is required in multiple modules then it is requyired to create more than one DAO reference.
6. In case of DAOs, it is suggestible to interact with Databases by using Connection Pooling mechanisms, not by using DriverManager approach.
7. DAO is not thread safe; we must not use DAOs in multi-threaded environment.
8. In DAOs we can access close() method in order to close the resources like connections,.... , so here, before calling close() method we must ensure that whether the resources are going to be released or not with our close() method call.
9. We have make sure that all the objects which are used by DAOs are following Java bean conventions or not.
     To provide support for DAOs kind of implementations in Spring Applications, Spring has provided a separate module called as “Spring DAO”. Spring DAO modules has provided a set of predefined classes and interfaces in order to provide DAO support in the form of “org.springframework.dao” package.
     In Enterprise Applications, to prepare Data Access Layer or DAO layer Spring has provided Modules in the form of JDBC and ORM. IN ORM we may use no of ORM implementation tools like Hibernate, JPA etc.

In Enterprise Applications, to prepare Data Access Layer we have already Plain JDBC tech. then what is the requirement to go for Spring JDBC Module?
1. To prepare Data Access Layer in enterprise applications, if we use JDBC then we must take explicit responsibility to prepare the steps load and register the driver, Establish Connection, creating Statement, executing SQl Queries and closing the resources like ResultSet, Statement and Connection.
     If we use Spring JDBC module to prepare Data Access Layer, we must take explicit responsibility to write and execute SQL Queries only, not to take any responsibility to load and register driver, connection establishment, creating Statement and closing the resources.

2. In case of Plain JDBC, almost all the exceptions are checked exceptions, we have to handle them explicitly by providing some java code.
     In case of Spring JDBC module, all the internal checked exceptions are converted into Unchecked Exceptions which are defined by Spring DAO module, it is very simple to handle these unchecked Exceptions.

3. In Plain JDBC, limited support is available for Transactions.
     In Spring JDBC Module, very good support is available for transactions, we may use Transaction module also to provide transactions.

4. In Plain JDBC, to hold the results we are able to use only ResultSet object, which is not implementing java.io.Serializable interface, which is not transferable in network.
     In Spring JDBC, we are able to get results of SQL Queries in our required form like in the form of RowSet, Collections etc. which are implementing java.io.Serializable interface and which are transferable in Network.

5. In plain JDBC, we are able to get Connections either by using DriverManager or by using Datasource.
     In Spring JDBC, we are able to get Connection internally by using Datasource only, that is through Connection Pooling only.

6. In plain JDBC, to map records to Bean objects in the form of Collection Object we have to write java code explicitly, no predefined support is provided by JDBC tech.
     In case of Spring JDBC, to map Database records to Bean objects in the form of Collection Spring JDBC has provided predefined support in the form of "RowMapper".

7. In Plain JDBC, no callback interfaces support is available to create and execute the sql queries in PrfeparedStatement style.
     In Spring JDBC, callback interfaces support is available to create and execute sql queries in PreparedStatement style.
     To prepare Data Access Layer in enterprise applications, Spring JDBC module has provided the complete predefined library in the form of the following classes and interfaces in "org.springframework.jdbc" and its sub packages.
☀ JdbcTemplate
☀ NamedParameterJdbcTemplate
☀ SimpleJdbcTemplate
☀ SimpleJdbcInsert
☀ SimpleJdbcCall
     In Spring Applications, if we want to JdbcTemplate [JDBC Module] then we have to use the following steps.
1. Create DAO interface with the required methods.
2. Create DAO implementation class with implementation for DAO interface methods.
3. In Configuration File provide configuration for DataSource class, JdbcTemplate class and DAO implementation class.
4. Prepare Test Application to access Dao methods.
In Spring configuration file we have to configure DataSource with the following properties.
☀ driverClassName
☀ url
☀ username
☀ Password

     In Spring applications, to configure DataSource Spring has provided a separate a predefined Datasource class in the form of "org.springframework.jdbc.datasource.DriverManagerDataSource", it is not suggestible for production mode, it is suggestible for testing mode of our applications , In spring applications, it is always suggestible to use third party Connection Pooling mechanisms like dbcp, C3P0, Proxool etc.
     JdbcTemplate class is providing basic environment to interact with Database like Loading Driver class, Getting Connection between Java application and DB, Creating Statement, PreparedStatement and CallableStatement and closing the connection with the help of the provided Datasource and JdbcTemplate class has provided the following methods to execute SQL Queries.

1. For Non-Select SQL queries and DML SQL queries
public int update(String query)

2. For DDL SQL Queries
public void execute(String query)

3. For Select SQL queries
public int queryForInt(String query)
public long queryForLong(String query)
public String queryForString(String query)
public Object queryForObject(String query)
public List query(String query)
public List queryForList(String query)
public Map queryForMap(String query)
public RowSet queryForRowSet(String query)
     While performing retrieval operations to convert data from ResultSet object[records] to Bean objects Spring Framework has provided a predefined interface in the form of "org.springframework.jdbc.core.RowMapper" which contains the following method.
public Object mapRow(ResultSet rs, int rowCount)
package com.ashok.spring.dto;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Student {
   private String sid;
   private String sname;
   private String saddr;

   public String getSid() {
      return sid;
   }

   public void setSid(String sid) {
      this.sid = sid;
   }

   public String getSname() {
      return sname;
   }

   public void setSname(String sname) {
      this.sname = sname;
   }

   public String getSaddr() {
      return saddr;
   }

   public void setSaddr(String saddr) {
      this.saddr = saddr;
   }
}
package com.ashok.spring.dao;

import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;

/**
 * 
 * @author ashok.mariyala
 *
 */
public interface StudentDao {
   public void setJdbcTemplate(JdbcTemplate jdbcTemplate);

   public String add(Student std);

   public Student search(String sid);

   public String update(Student std);

   public String delete(String sid);
}
package com.ashok.spring.dao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentDaoImpl implements StudentDao {
   private static Logger logger = LoggerFactory.getLogger(StudentDaoImpl.class.getName());

   private JdbcTemplate jdbcTemplate;
   String status = "";

   @Override
   public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
      this.jdbcTemplate = jdbcTemplate;
   }

   @Override
   public String add(Student std) {
      try {
         jdbcTemplate.update("insert into student values('" + std.getSid() + "','" + std.getSname() + "','" + std.getSaddr() + "')");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }

   @Override
   public Student search(String sid) {
      Student std = null;
      try {
         std = jdbcTemplate.queryForObject("select * from student where sid='" + sid + "'", new StudentMapper());
      } catch (Exception e) {
         logger.error(e.getMessage(), e);
      }
      return std;
   }

   @Override
   public String update(Student std) {
      try {
         jdbcTemplate.update("update student set sname='" + std.getSname() + "',saddr='" + std.getSaddr() + "' where sid='" + std.getSid() + "'");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }

   @Override
   public String delete(String sid) {
      try {
         jdbcTemplate.update("delete from student where sid='" + sid + "'");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }
}
package com.ashok.spring.dao;

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

import org.springframework.jdbc.core.RowMapper;

import com.ashok.spring.dto.Student;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentMapper implements RowMapper<Student> {
   @Override
   public Student mapRow(ResultSet rs, int rowNo) throws SQLException {
      Student std = new Student();
      std.setSid(rs.getString("SID"));
      std.setSname(rs.getString("SNAME"));
      std.setSaddr(rs.getString("SADDR"));
      return std;
   }
}
<?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:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
   <bean id="studentDao" class="com.ashok.spring.dao.StudentDaoImpl">
      <property name="jdbcTemplate" ref="jdbcTemplate" />
   </bean>
   <bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate">
      <property name="dataSource" ref="dataSource" />
   </bean>
   <bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://localhost:3306/student" />
      <property name="username" value="root" />
      <property name="password" value="ashok" />
   </bean>
</beans>
package com.ashok.spring.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ashok.spring.dto.Student;

public class TestSpringDaoApplication {
   public static void main(String[] args) {
      String configFile = "/com/ashok/spring/dao/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      StudentDao dao = (StudentDao) context.getBean("studentDao");
      
      // ----Inserting Records------
      Student std = new Student();
      std.setSid("S-111");
      std.setSname("Ashok");
      std.setSaddr("Bhimavaram");
      String status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      std.setSid("S-222");
      std.setSname("Vinod Kumar");
      std.setSaddr("Banglore");
      status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      System.out.println();
      
      // ----Retriving Record-----
      Student std1 = dao.search("S-111");
      if (std1 == null) {
         System.out.println("Student Search Status :NotExisted");
      } else {
         System.out.println("Student Details");
         System.out.println("--------------------");
         System.out.println("Student Id :" + std1.getSid());
         System.out.println("Student Name :" + std1.getSname());
         System.out.println("Student Address :" + std1.getSaddr());
      }
      System.out.println();
      
      // ----Updating a Record------
      std.setSid("S-111");
      std.setSname("Ashok Kumar");
      std.setSaddr("Bhimavaram");
      status = dao.update(std);
      System.out.println("Student Updation :" + status);
      System.out.println();
      
      //----Deleting a record-----
      status = dao.delete("S-111");
      System.out.println("Student Deletion :"+status);
   }
}
Output
Student Insertion :success
Student Insertion :success
Student Details
--------------------
Student Id :S-111
Student Name :Ashok
Student Address :Bhimavaram
Student Updation :success
Student Deletion: success

     In Spring JDBC Applications, we will use positional parameters [?] also in SQL queries which we are providing along with JdbcTemplate class provided query execution methods. If we provide positional parameters in SQL queries then JdbcTemplate class will use "PreparedStatement" internally to execute SQL query instead of Statement.
     To provide values to the Positional parameters in SQL Queries we have to use Object [] with values as parameter to all JdbcTemplate class provided query execution methods.
public int update(String query, Object[] paramValues)
public int queryForInt(String query, Object[] paramValues)
public long queryForLong(String query, Object[] paramValues)
public Object queryForObject(String query, Object[] paramValues, RowMapper rm)
E.g.
String query = "insert into student values(?, ?, ?)";
int rowCount = jdbcTemplate.update(query, new Object[]{"S-111", "Ashok Kumar", "Hyd"});
E.g.
package com.ashok.spring.dto;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Student {
   private String sid;
   private String sname;
   private String saddr;

   public String getSid() {
      return sid;
   }

   public void setSid(String sid) {
      this.sid = sid;
   }

   public String getSname() {
      return sname;
   }

   public void setSname(String sname) {
      this.sname = sname;
   }

   public String getSaddr() {
      return saddr;
   }

   public void setSaddr(String saddr) {
      this.saddr = saddr;
   }
}
package com.ashok.spring.dao.preparedstatement;

import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public interface StudentDao {
   public void setJdbcTemplate(JdbcTemplate jdbcTemplate);

   public String add(Student std);

   public Student search(String sid);

   public String update(Student std);

   public String delete(String sid);
}
package com.ashok.spring.dao.preparedstatement;

import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentDaoImpl implements StudentDao {
   private JdbcTemplate jdbcTemplate;
   String status = "";

   @Override
   public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
      this.jdbcTemplate = jdbcTemplate;
   }

   @Override
   public String add(Student std) {
      try {
         String query = "insert into student values(?,?,?)";
         jdbcTemplate.update(query, new Object[] { std.getSid(), std.getSname(), std.getSaddr() });
         status = "success";
      } catch (Exception e) {
         status = "failure";
         e.printStackTrace();
      }
      return status;
   }

   @Override
   public Student search(String sid) {
      Student std = null;
      try {
         std = jdbcTemplate.queryForObject("select * from student where sid=?", new Object[] { sid },
               new StudentMapper());
      } catch (Exception e) {
         e.printStackTrace();
      }
      return std;
   }

   @Override
   public String update(Student std) {
      try {
         jdbcTemplate.update("update student set sname=?, saddr=? where sid=?",
               new Object[] { std.getSname(), std.getSaddr(), std.getSid() });
         status = "success";
      } catch (Exception e) {
         status = "failure";
         e.printStackTrace();
      }
      return status;
   }

   @Override
   public String delete(String sid) {
      try {
         jdbcTemplate.update("delete from student where sid=?", new Object[] { sid });
         status = "success";
      } catch (Exception e) {
         status = "failure";
         e.printStackTrace();
      }
      return status;
   }
}
package com.ashok.spring.dao.preparedstatement;

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

import org.springframework.jdbc.core.RowMapper;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentMapper implements RowMapper {
   @Override
   public Student mapRow(ResultSet rs, int rowNo) throws SQLException {
      Student std = new Student();
      std.setSid(rs.getString("SID"));
      std.setSname(rs.getString("SNAME"));
      std.setSaddr(rs.getString("SADDR"));
      return std;
   }
}
<?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:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
   <bean id="studentDao" class="com.ashok.spring.dao.preparedstatement.StudentDaoImpl">
      <property name="jdbcTemplate" ref="jdbcTemplate" />
   </bean>
   <bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate">
      <property name="dataSource" ref="dataSource" />
   </bean>
   <bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://localhost:3306/student" />
      <property name="username" value="root" />
      <property name="password" value="ashok" />
   </bean>
</beans>
package com.ashok.spring.dao.preparedstatement;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class TestSpringDaoApplication {
   public static void main(String[] args) {
      String configFile = "/com/ashok/spring/dao/preparedstatement/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      StudentDao dao = (StudentDao) context.getBean("studentDao");
      
      // ----Inserting Records------
      Student std = new Student();
      std.setSid("S-1111");
      std.setSname("Ashok");
      std.setSaddr("Bhimavaram");
      String status = dao.add(std);
      System.out.println("Student Insertion :" + status);
   
      std.setSid("S-2222");
      std.setSname("Vinod Kumar");
      std.setSaddr("Banglore");
      status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      System.out.println();
      
      // ----Retriving Record-----
      Student std1 = dao.search("S-1111");
      if (std1 == null) {
         System.out.println("Student Search Status :NotExisted");
      } else {
         System.out.println("Student Details");
         System.out.println("--------------------");
         System.out.println("Student Id :" + std1.getSid());
         System.out.println("Student Name :" + std1.getSname());
         System.out.println("Student Address :" + std1.getSaddr());
      }
      System.out.println();
      
      // ----Updating a Record------
      std.setSid("S-1111");
      std.setSname("Ashok Kumar");
      std.setSaddr("Bhimavaram");
      status = dao.update(std);
      System.out.println("Student Updation :" + status);
      System.out.println();
      
      //----Deleting a record-----
      status = dao.delete("S-1111");
      System.out.println("Student Deletion :"+status);
   }
}
Output
Student Insertion: success
Student Insertion: success
Student Details
--------------------------
Student Id :S-1111
Student Name :Ashok
Student Address :Bhimavaram
Student Updation :success
Student Deletion :success
NamedParameterJdbcTemplate
     NamedParameterJdbcTemplate class is same as JdbcTemplate class, but, NamedParameterJdbcTemplate class is able to define and run sql queries with Named Parameters instead of positional parameters.
E.g.
String query = "insert into student values(:sid, :sname, :saddr)";
     Where :sid, :sname, :saddr are named parameters for which we have to provide values.
In case of NamedParameterJdbcTemplate , we are able to provide values to the named parameters in the following two approaches.
1.By Using Map directly.
2.By using SqlParameterSource interface.

1. By Using Map directly.
String query = "insert into student values(:sid, :sname, :saddr)";
Map map = new HashMap();
map.put("sid", "S-111");
map.put("sname", "Ashok Kumar");
map.put("saddr", "Bhimavaram");
namedParameterJdbcTemplate.update(query, map);

2. By using SqlParameterSource interface.
To provide values to the Named parameters Spring has provided the following two implementation classes for SqlParameterSoure interface.
a) MapSqlParameterSource
b) BeanPropertySqlParameterSource
     To provide values to the named parameters if we want to use MapSqlParameterSource then first we have to create object for MapSqlParameterSource and we have to use the following method to add values to the named parameters.
public MapSqlParameterSource addValue(String name, Object val)
String query = "insert into student values(:sid, :sname, :saddr)";
SqlParameterSource paramSource = new MapSqlParameterSource("sid", "S-111");
paramSource = paramSource.addValue("sname", "Ashok Kumar");
paramSource = paramSource.addValue("saddr", "Bhimavaram");
namedParameterJdbcTemplate.update(query, paramSource);

     To provide values to the named parameters if we want to use BeanPropertySqlParameterSource then first we have to create bean object with data then we have to create Object for BeanPropertySqlParameterSource with the generated Bean reference then provide BeanPropertySqlParameterSource object to query methods.
E.g.
String query = "insert into student values(:sid, :sname, :saddr)";
Student std = new Student();
std.setSid("S-111");
std.setSname("Ashok Kumar");
std.setSaddr("Bhimavaram ");
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(std );
namedParameterJdbcTemplate.update(query, paramSource);
 
Note
     JdbcTemplate is allowing DataSource object injection through setter method, but, NamedParameterJdbcTemplate class is allowing DataSource object injection through Constructor Dependency Injection.
E.g.
package com.ashok.spring.dao.namedparameter;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class Customer {
   private String cid;
   private String cname;
   private String caddr;

   public String getCid() {
      return cid;
   }

   public void setCid(String cid) {
      this.cid = cid;
   }

   public String getCname() {
      return cname;
   }

   public void setCname(String cname) {
      this.cname = cname;
   }

   public String getCaddr() {
      return caddr;
   }

   public void setCaddr(String caddr) {
      this.caddr = caddr;
   }
}
package com.ashok.spring.dao.namedparameter;

/**
 * 
 * @author ashok.mariyala
 *
 */
public interface CustomerDao {
   public String add(Customer c);
   public Customer search(String cid);
   public String update(Customer c);
   public String delete(String cid);
}
package com.ashok.spring.dao.namedparameter;

import java.util.HashMap;
import java.util.Map;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class CustomerDaoImpl implements CustomerDao {
   String status = "";
   private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

   public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
      this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;

   }

   @Override
   public String add(Customer c) {
      String query = "insert into customer values(:cid, :cname, :caddr)";
      Map<String, Object> map = new HashMap<>();
      map.put("cid", c.getCid());
      map.put("cname", c.getCname());
      map.put("caddr", c.getCaddr());
      namedParameterJdbcTemplate.update(query, map);
      return "SUCCESS";
   }

   @Override
   public Customer search(String cid) {
      String query = "select * from customer where cid=:cid";
      Map<String, Object> map = new HashMap<>();
      map.put("cid", cid);
      Customer c = namedParameterJdbcTemplate.queryForObject(query, map, new CustomerMapper());
      return c;
   }

   @Override
   public String update(Customer c) {
      String query = "update customer set CNAME=:cname, CADDR=:caddr where CID=:cid";
      SqlParameterSource paramSource = new BeanPropertySqlParameterSource(c);
      namedParameterJdbcTemplate.update(query, paramSource);
      return "SUCCESS";
   }

   @Override
   public String delete(String cid) {
      String query = "delete from customer where cid=:cid";
      SqlParameterSource paramSource = new MapSqlParameterSource("cid", cid);
      namedParameterJdbcTemplate.update(query, paramSource);
      return "SUCCESS";
   }
}
package com.ashok.spring.dao.namedparameter;

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

import org.springframework.jdbc.core.RowMapper;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class CustomerMapper implements RowMapper {
   @Override
   public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
      Customer c = new Customer();
      c.setCid(rs.getString("CID"));
      c.setCname(rs.getString("CNAME"));
      c.setCaddr(rs.getString("CADDR"));
      return c;
   }
}
<?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:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
   <bean id="customerDao" class="com.ashok.spring.dao.namedparameter.CustomerDaoImpl">
      <property name="namedParameterJdbcTemplate"   ref="namedParameterJdbcTemplate" />
   </bean>
   <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
      <constructor-arg ref="dataSource" />
   </bean>
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://localhost:3306/student" />
      <property name="username" value="root" />
      <property name="password" value="ashok" />
   </bean>
</beans>
package com.ashok.spring.dao.namedparameter;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class TestDaoApplication {
   public static void main(String[] args) throws Exception {
      String configFile = "/com/ashok/spring/dao/namedparameter/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      CustomerDao dao = (CustomerDao) context.getBean("customerDao");
      Customer c = new Customer();
      c.setCid("C-111");
      c.setCname("Ashok Kumar");
      c.setCaddr("Bhimavaram");
      String status = dao.add(c);
      System.out.println("Customer Insertion :" + status);
      c.setCid("C-222");
      c.setCname("Vinod Kumar");
      c.setCaddr("Banglore");
      status = dao.add(c);
      System.out.println("Customer Insertion :" + status);
      
      Customer c1 = dao.search("C-111");
      System.out.println("Customer Details");
      System.out.println("--------------------");
      System.out.println("Customer Id :" + c1.getCid());
      System.out.println("Customer Name :" + c1.getCname());
      System.out.println("Customer Address :" + c1.getCaddr());
      System.out.println();
      
      Customer c2 = new Customer();
      c2.setCid("C-111");
      c2.setCname("Ashok");
      c2.setCaddr("Bhimavaram");
      status = dao.update(c2);
      System.out.println("Student Updation Status :" + status);
      
      Customer c3 = dao.search("C-111");
      System.out.println("Customer Updated Details");
      System.out.println("--------------------");
      System.out.println("Customer Id :" + c3.getCid());
      System.out.println("Customer Name :" + c3.getCname());
      System.out.println("Customer Address :" + c3.getCaddr());
      System.out.println();
      
      status = dao.delete("C-111");
      System.out.println("Student Deletion Status :" + status);
   }
}
Output
Customer Insertion :SUCCESS
Customer Insertion :SUCCESS
Customer Details
--------------------
Customer Id :C-111
Customer Name :Ashok Kumar
Customer Address :Bhimavaram
Student Updation Status: SUCCESS
Customer Updated Details
-------------------------------------
Customer Id: C-111
Customer Name: Ashok
Customer Address: Bhimavaram
Student Deletion Status: SUCCESS
SimpleJdbcTemplate
     In Spring JDBC module, the main intention of SimpleJdbcTemplate class is to provide support for JDK5.0 version features like Auto Boxing, Auto Unboxing, Var-Arg methods.
     SimpleJdbcTemplate class was provided in Spring2.5 version only and it was deprecated in the later versions Spring3.x and Spring4.x , in Spring5.x version SimpleJdbcTemplate class was removed.
     If we want to use SimpleJdbcTemplate class we have to use Spring2.5 version jar files in Spring applications. To execute SQL queries, SimpleJdbcTemplate class has provided the following methods.
public Object execute(String sqlQuery)
Note
     To use this method we have to get JdbcOperations class by using getJdbcOperations() method.
public int update(String query, Object ... params)
public Object queryForInt(String query, Object ... params)
public Object queryForLong(String query, Object ... params)
public Object query(String query, Object ... params)
public Object queryForObject(String query,Object ... params)
Note
     In case of SimpleJdbcTemplate class, to perform retrival operations, we have to use "ParameterizedRowMapper" in place of RowMapper interface.

DAO Support Classes
     In Spring JDBC, we have to prepare DAO implementation classes with XXXTemplate property and the corresponding setXXX() method in order to inject XXXTemplate class. In Spring JDBC applications, if we want to get XXXTemplate classes without declaring Template properties and corresponding setXXX() methods we have to use DAO Support classes provided Spring JDBC module.
There are three types of DAOSupport classes in order to get Template object in DAO classes.
1. JdbcDaoSupport
2. NamedParameterJdbcDaoSupport
3. SimpleJdbcDaoSupport
     Where JdbcDaoSupport class will provide JdbcTemplate reference in DAO classes by using the following method.
public JdbcTemplate getJdbcTemplate()
   Where NamesParameterJdbcDaoSupport class will provide NamedParameterJdbcTempate reference in DAO classes by using the following method.
public NamedParameterJdbcTemplate getNamedparameterJdbctemplate()
     Where SimpleJdbcDaoSupport class is able to provide SimpleJdbctemplate reference in Dao class by using the following method.
public SimpleJdbcTemplate getSimpleJdbcTemplate()
E.g.
package com.ashok.spring.dao.jdbcdaosupport;

import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public interface StudentDao {
   public void setJdbcTemplate(JdbcTemplate jdbcTemplate);
   public String add(Student std);
   public Student search(String sid);
   public String update(Student std);
   public String delete(String sid);
}
package com.ashok.spring.dto;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Student {
   private String sid;
   private String sname;
   private String saddr;

   public String getSid() {
      return sid;
   }

   public void setSid(String sid) {
      this.sid = sid;
   }

   public String getSname() {
      return sname;
   }

   public void setSname(String sname) {
      this.sname = sname;
   }

   public String getSaddr() {
      return saddr;
   }

   public void setSaddr(String saddr) {
      this.saddr = saddr;
   }
}
package com.ashok.spring.dao.jdbcdaosupport;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.ashok.kafka.ConsumerDemoAssignSeek;
import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentDaoImpl extends JdbcDaoSupport implements StudentDao {
   private static Logger logger = LoggerFactory.getLogger(ConsumerDemoAssignSeek.class.getName());

   String status = "";

   @Override
   public String add(Student std) {
      try {
         getJdbcTemplate().update("insert into student values('" + std.getSid() + "','" + std.getSname() + "','" + std.getSaddr() + "')");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }

   @Override
   public Student search(String sid) {
      Student std = null;
      try {
         std = getJdbcTemplate().queryForObject("select * from student where sid='" + sid + "'", new StudentMapper());
      } catch (Exception e) {
         logger.error(e.getMessage(), e);
      }
      return std;
   }

   @Override
   public String update(Student std) {
      try {
         getJdbcTemplate().update("update student set sname='" + std.getSname() + "',saddr='" + std.getSaddr() + "' where sid='" + std.getSid() + "'");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }

   @Override
   public String delete(String sid) {
      try {
         getJdbcTemplate().update("delete from student where sid='" + sid + "'");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }

}
<?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:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
   <bean id="studentDao" class="com.ashok.spring.dao.jdbcdaosupport.StudentDaoImpl">
      <property name="jdbcTemplate" ref="jdbcTemplate" />
   </bean>
   <bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate">
      <property name="dataSource" ref="dataSource" />
   </bean>
   <bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://localhost:3306/student" />
      <property name="username" value="root" />
      <property name="password" value="ashok" />
   </bean>
</beans>
package com.ashok.spring.dao.jdbcdaosupport;

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

import org.springframework.jdbc.core.RowMapper;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentMapper implements RowMapper {
   @Override
   public Student mapRow(ResultSet rs, int rowNo) throws SQLException {
      Student std = new Student();
      std.setSid(rs.getString("SID"));
      std.setSname(rs.getString("SNAME"));
      std.setSaddr(rs.getString("SADDR"));
      return std;
   }
}
package com.ashok.spring.dao.jdbcdaosupport;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class TestSpringDaoApplication {
   public static void main(String[] args) {
      String configFile = "/com/ashok/spring/dao/jdbcdaosupport/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      StudentDao dao = (StudentDao) context.getBean("studentDao");
      
      // ----Inserting Records------
      Student std = new Student();
      std.setSid("S-555");
      std.setSname("Ashok");
      std.setSaddr("Bhimavaram");
      String status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      std.setSid("S-666");
      std.setSname("Vinod Kumar");
      std.setSaddr("Banglore");
      status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      System.out.println();
      
      // ----Retriving Record-----
      Student std1 = dao.search("S-555");
      if (std1 == null) {
         System.out.println("Student Search Status :NotExisted");
      } else {
         System.out.println("Student Details");
         System.out.println("--------------------");
         System.out.println("Student Id :" + std1.getSid());
         System.out.println("Student Name :" + std1.getSname());
         System.out.println("Student Address :" + std1.getSaddr());
      }
      System.out.println();
      
      // ----Updating a Record------
      std.setSid("S-555");
      std.setSname("Ashok Kumar");
      std.setSaddr("Bhimavaram");
      status = dao.update(std);
      System.out.println("Student Updation :" + status);
      System.out.println();
      
      //----Deleting a record-----
      status = dao.delete("S-555");
      System.out.println("Student Deletion :"+status);
   }
}
Output
Student Insertion :success
Student Insertion :success
Student Details
--------------------
Student Id :S-555
Student Name :Ashok
Student Address :Bhimavaram
Student Updation: success
Student Deletion: success
Spring Batch Updations
Batch Processing
     To perform Batch Updations in Spring JDBC we have to use the following method from JdbcTemplate class.
public int[] batchUpdate(String sql_prepared_Statement, BatchPreparedStatementSetter setter)
     Where BatchPreparedStatementSetter interface contains the following two methods
public void setValues(PreparedStatement ps, int index)
public int getBatchSize()
     Where setValues() method will be executed for each and every record to set values to the positional parameters existed in PreparedStatement object by getting values from the provided List.
E.g.
package com.ashok.spring.dao.batchprocessing.dto;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class Employee {
   private String empName;
   private String empId;
   private String empAddress;

   public String getEmpName() {
      return empName;
   }

   public void setEmpName(String empName) {
      this.empName = empName;
   }

   public String getEmpId() {
      return empId;
   }

   public void setEmpId(String empId) {
      this.empId = empId;
   }

   public String getEmpAddress() {
      return empAddress;
   }

   public void setEmpAddress(String empAddress) {
      this.empAddress = empAddress;
   }

   @Override
   public String toString() {
      return "Employee [empName=" + empName + ", empId=" + empId + ", empAddress=" + empAddress + "]";
   }
}
package com.ashok.spring.dao.batchprocessing;

import java.util.List;
import com.ashok.spring.dao.batchprocessing.dto.Employee;
/**
 * 
 * @author ashok.mariyala
 *
 */
public interface EmployeeDao {
   public int[] insert(List<Employee> list);
}
package com.ashok.spring.dao.batchprocessing;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dao.batchprocessing.dto.Employee;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class EmployeeDaoImpl implements EmployeeDao {
   JdbcTemplate jdbcTemplate;

   public void setDataSource(DataSource dataSource) {
      jdbcTemplate = new JdbcTemplate(dataSource);
   }

   @Override
   public int[] insert(List<Employee> list) {
      int[] rowCounts = null;
      try {
         String sql = "insert into employee values(?,?,?)";
         rowCounts = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
               ps.setString(1, list.get(i).getEmpId());
               ps.setString(2, list.get(i).getEmpName());
               ps.setString(3, list.get(i).getEmpAddress());
            }

            @Override
            public int getBatchSize() {
               return list.size();
            }
         });
      } catch (Exception e) {
         e.printStackTrace();
      }
      return rowCounts;
   }
}
package com.ashok.spring.dao.batchprocessing;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ashok.spring.dao.batchprocessing.dto.Employee;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class TestSpringBatchProcessing {
   public static void main(String[] args) {
      String configFile = "/com/ashok/spring/dao/batchprocessing/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      EmployeeDao dao = (EmployeeDao) context.getBean("empDao");
      List<Employee> list = new ArrayList<>();
      Employee e1 = new Employee();
      e1.setEmpId("Emp0087");
      e1.setEmpName("Ashok Kumar");
      e1.setEmpAddress("Bhimavaram");
      list.add(e1);
      Employee e2 = new Employee();
      e2.setEmpId("Emp65894");
      e2.setEmpName("Vinod Kumar");
      e2.setEmpAddress("Banglore");
      list.add(e2);
      Employee e3 = new Employee();
      e3.setEmpId("Emp85974");
      e3.setEmpName("Naresh Kumar");
      e3.setEmpAddress("Hyderabad");
      list.add(e3);
      int[] rowCounts = dao.insert(list);
      for (int i = 0; i < rowCounts.length; i++) {
         System.out.println(rowCounts[i]);
      }
   }
}
Output
1
1
1
Stored Procedure and Functions in Spring JDBC
     If we want to access stored procedures and functions ehich are available at database from Spring Jdbc application then we have to use "SimpleJdbcCall". To use SimpleJdbcCall in Spring Jdbc applications we have to use the following steps.
1. Create DAO interface and its implementation class.
2. In DAO implementation class, we have to declare DataSource and JdbcTemplate and its respective setter method.
3. Inside setter method we have to create SimpleJdbcCall object.
     SimpleJdbcCall jdbcCall = new SimpleJdbcCall();
     jdbcCall.withProcedureName("proc_Name");
4. Configure DataSource and DAO implementation class in beans configuration file.
5. Access "execute" method by passing IN type parameters values in the form of "SQLParameterSource".
public Map execute(Map m)
public Map execute(SqlParameterSource paramSource)
public Map execute(Object ... obj)

Using CURSOR Types in Procedures
     If we want to use CURSOR types in Stored Procedures in order to retrieve multiple Records data then we have to use the following method on
SimpleJdbcCall reference.
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource);
jdbcCall = jdbcCall.withProcedureName("getAllEmployees");
jdbcCall = jdbcCall.returningResultSet("emps",BeanPropertyRowMapper.newInstance(Employee.class));
     After adding returningResultSet(--,--) method, if we access execute() method on SimpleJdbcCall then execute() method will execute procedure, it will get all the results from CURSOR type variable and stored all records in the form of Employee objects in an ArrayList object with "emps"[CURSOR TYPE variable] key in a Map.

Blob and Clob processing in Spring JDBC
BLOB: It is a data type available at Databases to represent large volumes of binary data.
CLOB: It is a data type available at Database to represent large volumes of character data.
In Spring JDBC Applications, to process BLOB and CLOB Data, Spring JDBC has provided the following three interfaces mainly.

1. AbstractLobCreatingPreparedStatementCallback
     It will be used to store Blob and Clob related data in Database.
protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException
2. AbstractLobStreamingResultSetExtractor
    It will be used to retrive BLOB and CLOB data from database.
streamData(ResultSet rs)throws SQLException, IOException, DataAccessException
3. LobCreator
     It contains the following methods to prepare Binary stream and character streams to send blob and clob data to database.
setBlobAsBinaryStream()
setClobAsCharacterStream()
4. LobHolder
     It contains the following methods to get Binary stream and character stream to get blob and clob data.
getBlobAsBinaryStream()
getClobAsCharacterStream()
package com.ashok.spring.dao.lob.beans;

import java.io.File;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Employee {
   private String empId;
   private String empName;
   private File empImage;
   private File empResume;

   public String getEmpId() {
      return empId;
   }

   public void setEmpId(String empId) {
      this.empId = empId;
   }

   public String getEmpName() {
      return empName;
   }

   public void setEmpName(String empName) {
      this.empName = empName;
   }

   public File getEmpImage() {
      return empImage;
   }

   public void setEmpImage(File empImage) {
      this.empImage = empImage;
   }

   public File getEmpResume() {
      return empResume;
   }

   public void setEmpResume(File empResume) {
      this.empResume = empResume;
   }
}
package com.ashok.spring.dao.lob;

import com.ashok.spring.dao.lob.beans.Employee;

/**
 * 
 * @author ashok.mariyala
 *
 */
public interface EmployeeDao {
   public void insertEmployee(Employee emp);

   public Employee readEmployee();
}
package com.ashok.spring.dao.lob;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.util.FileCopyUtils;

import com.ashok.spring.dao.lob.beans.Employee;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class EmployeeDaoImpl implements EmployeeDao {
   private LobHandler lobHolder;
   private JdbcTemplate jdbcTemplate;

   public void setDataSource(DataSource dataSource) {
      jdbcTemplate = new JdbcTemplate(dataSource);
   }

   public void setLobHolder(LobHandler lobHolder) {
      this.lobHolder = lobHolder;
   }

   public LobHandler getLobHolder() {
      return lobHolder;
   }

   @Override
   public void insertEmployee(Employee emp) {
      String sql_Query = "insert into emp values(?,?,?,?)";
      jdbcTemplate.execute(sql_Query, new AbstractLobCreatingPreparedStatementCallback(lobHolder) {
         @Override
         protected void setValues(PreparedStatement ps, LobCreator lobCreator)
               throws SQLException, DataAccessException {
            FileInputStream fis = null;
            FileReader fr = null;
            try {
               ps.setString(1, emp.getEmpId());
               ps.setString(2, emp.getEmpName());
               fis = new FileInputStream(emp.getEmpImage());
               fr = new FileReader(emp.getEmpResume());
               lobCreator.setBlobAsBinaryStream(ps, 3, fis, (int) emp.getEmpImage().length());
               lobCreator.setClobAsCharacterStream(ps, 4, fr, (int) emp.getEmpResume().length());
            } catch (IOException e) {
               e.printStackTrace();
            }
         }
      });
   }

   @Override
   public Employee readEmployee() {
      Employee emp = new Employee();
      String sql = "select * from emp";
      jdbcTemplate.query(sql, new AbstractLobStreamingResultSetExtractor() {
         @Override
         protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
            emp.setEmpId(rs.getString(1));
            emp.setEmpName(rs.getString(2));
            File file1 = new File("E:/ashok/personal/ashokimage.jpg");
            FileOutputStream fos = new FileOutputStream(file1);
            FileCopyUtils.copy(lobHolder.getBlobAsBinaryStream(rs, 3), fos);
            emp.setEmpImage(file1);
            File file2 = new File("E:/ashok/personal/ashokresume.docx");
            FileWriter fw = new FileWriter(file2);
            FileCopyUtils.copy(lobHolder.getClobAsCharacterStream(rs, 4), fw);
            emp.setEmpResume(file2);
         }
      });
      return emp;
   }
}
<?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:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
   <bean id="empDao" class="com.ashok.spring.dao.lob.EmployeeDaoImpl">
      <property name="dataSource" ref="dataSource" />
      <property name="lobHolder" ref="lobHolder" />
   </bean>
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="${jdbc.driverClassName}" />
      <property name="url" value="${jdbc.url}" />
      <property name="username" value="${jdbc.username}" />
      <property name="password" value="${jdbc.password}" />
   </bean>
   <bean id="lobHolder" class="org.springframework.jdbc.support.lob.DefaultLobHandler">
   </bean>
   <context:property-placeholder location="com/ashok/spring/dao/batchprocessing/config/jdbc.properties" />
</beans>
jdbc.properties
jdbc.driverClassName = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/employee
jdbc.username = root
jdbc.password = ashok
package com.ashok.spring.dao.lob;

import java.io.File;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ashok.spring.dao.lob.beans.Employee;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class TestSpringLobApplication {
   public static void main(String[] args) throws Exception {
      String configFile = "/com/ashok/spring/dao/lob/conf/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      EmployeeDao dao = (EmployeeDao) context.getBean("empDao");
      File file1 = new File("E:/ashok/personal/myimage.jpg");
      File file2 = new File("E:/ashok/personal/myresume.docx");
      Employee emp1 = new Employee();
      emp1.setEmpId("E0087");
      emp1.setEmpName("Ashok Kumar");
      emp1.setEmpImage(file1);
      emp1.setEmpResume(file2);
      dao.insertEmployee(emp1);
      System.out.println("Employee Inserted Successfully");
      Employee emp2 = dao.readEmployee();
      System.out.println("Employee Retrived Successfully");
      System.out.println("Employee Details");
      System.out.println("---------------------");
      System.out.println("Employee Number :" + emp2.getEmpId());
      System.out.println("Employee Name :" + emp2.getEmpName());
      System.out.println("Employee Image :" + emp2.getEmpImage().getAbsolutePath());
      System.out.println("Employee Resume :" + emp2.getEmpResume().getAbsolutePath());
   }
}
Output
Employee Inserted Successfully
Employee Retrieved Successfully
Employee Details
---------------------
Employee Number :E0087
Employee Name :Ashok Kumar
Employee Image :E:\ashok\personal\ashokimage.jpg
Employee Resume :E:\ashok\personal\ashokresume.docx
Spring JDBC Connection Pooling Mechanism
     In Database related applications, if we want to perform database operations first, we have to create Connection object then we have to close connection object when the database operations are completed.
    In Database related applications, every time creating Connection object and every time destroying Connection object may reduce application performance, because, Creating Connection object and destroying Connection object are two expensive processes, which may reduce application performance.
    To overcome the above problem, we have to use Connection Pooling in applications. In Connection pooling we will create a set of Connection object in the form of a pool at the application startup time and we will reuse that Connection objects while executing applications, when database operations are completed then we will send back that connection objects to Pool object without destroying that connection objects. In Spring JDBC applications there are three approaches to provide connection pooling.
1. Default Connection Pooling Mech.
2. Third Party Connection Pooling Mechanisms
3. Application Servers provided Connection Pooling Mechanism

1. Default Connection Pooling Mech
     In Spring Framework, the Default Connection pooling mechanism is existed in the form of org.springframework.jdbc.datasource.DriverManagerDataSource, it is useful up to testing only, it is not useful for production environment of the application. If we want to use default Connection Pooling mechanism in Spring JDBC application then we have to configure org.springframework.jdbc.datasource.DriverManagerDataSource in beans configuration file with the following properties .
1. driverClassName
2. url
3. username
4. Password
E.g.
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
   <property name="url" value="jdbc:mysql://localhost:3306/employee"/>
   <property name="username" value="root"/>
   <property name="password" value="ashok"/>
</bean>

2. Third Party Connection Pooling Mechanisms
     In Spring JDBC applications we are able to use the following third-party connection pooling mechanisms
1. Apache DBCP
2. C3P0
3. Proxool
1. Apache DBCP
     To use Apcahe DBCP connection pooling mechanism then we have to configure
org.apache.commons.dbcp2.BasicDataSource class with the following properties in spring beans configuration file.
1. driverClassName
2. url
3. username
4. password
5. initialSize: It will take Initial pool size.
6. maxTotal: It will allow the specified no of max connections.
E.g.
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
   <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
   <property name="url" value="jdbc:mysql://localhost:3306/employee" />
   <property name="username" value="root" />
   <property name="password" value="ashok" />
   <property name="initialSize" value="20" />
   <property name="maxTotal" value="30" />
</bean>
Note: To use this mechanism in Spring JDBC Applications then we have to add the following two jar files to Library.
1.commons-dbcp2-2.2.0.jar
2.commons-pool2-2.5.0.jar

2. C3P0
To use C3P0 connection pooling mechanism then we have to configure
com.mchange.v2.c3p0.ComboPooledDataSource class with the following properties in spring beans configuration file.
1. driverClass
2. jdbcUrl
3. user
4. password
5. minPoolSize: It will take Initial pool size.
6. maxPoolSize: It will allow the specified no of max connections.
7. maxStatements: Max statements it allows.
8. testConnectionOnCheckOut:true/false for Checking Connection before use.
E.g.
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
   <property name="driverClass" value="com.mysql.cj.jdbc.Driver" />
   <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/employee" />
   <property name="user" value="root" />
   <property name="password" value="ashok" />
   <property name="maxPoolSize" value="30" />
   <property name="minPoolSize" value="10" />
   <property name="maxStatements" value="100" />
   <property name="testConnectionOnCheckout" value="true" />
</bean>
Note: To use this mechanism in Spring JDBC Applications then we have to add the following two jar files to Library.
1. c3p0-0.9.5.2.jar
2. mchange-commons-java-0.2.11.jar

3. Proxool
     To use Proxool connection pooling mechanism then we have to configure
org.logicalcobwebs.proxool.ProxoolDataSource class with the following properties in spring beans configuration file.
1.driver
2.driverUrl
3.user
4.password
5. minimumConnectionCount:It will take Initial pool size.
6.maximumConnectionCount: It will allow the specified no of max connections.
E.g.
<bean id="dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
   <property name="driver" value="com.mysql.cj.jdbc.Driver" />
   <property name="driverUrl" value="jdbc:mysql://localhost:3306/employee" />
   <property name="user" value="root" />
   <property name="password" value="ashok" />
   <property name="maximumConnectionCount" value="30" />
   <property name="minimumConnectionCount" value="10" />
</bean>
Note: To use this mechanism in Spring JDBC Applications then we have to add the following two jar files to Library.
1.proxool-0.9.1.jar
2.proxool-cglib.jar

3. Application Servers provided Connection Pooling Mechanism throw JNDI
     JNDI [Java Naming And Directory Interface]: JNDI is a Middleware Service or an abstraction provided by SUN Microsystems as part of J2EE and which is implemented by all the Application Servers vendors like Weblogic, JBOSS, Glassfish etc.
     JNDI is existed inside the application Servers to provide any resource with Global Scope, that is, JNDI will share any resource like "DataSource" to all the applications which are running in the present application server.
     In general, almost all the Application Servers are having their own Connection Pooling mechanisms, if we want to use Application Servers provided Connection pooling mechanisms, we have to use the following steps.
1. Install Application Server.
2. Configure Connection Pooling and Datasource in JNDI provided by Application Servers.
3. Add the required new JARs to Library.
4. Provide JNDI Setups in beans configuration File.

Next Tutorial : Spring ORM 

Previous Tutorial : Spring Expression Language
 

No comments:

Post a Comment