Data Access Using Spring Framework JDBC

Persisting and accessing data forms one of the most routine yet core functionalities of any application. In the world of JEE, there are many APIs as well as frameworks to achieve to achieve the same. The Spring Framework is no exception. This article will explain how to use this framework for persisting and accessing data in your applications.

The beauty of the Spring Framework is the flexibility it provides. Using the Spring Framework, one can integrate low-level APIs such as JDBC or high-level frameworks such as Hibernate into an application using the same technique – Dependency Injection. In this discussion, the focus will be on using the Spring Framework’s data access functionality with JDBC, which forms the basis of all other frameworks.

The first section will focus on the Data Access module (or DAO module) and the services it provides for using JDBC. The second and third sections will detail the steps required to use the DAO module of the Spring Framework. In the last section, an application will be developed that will make use of the steps detailed in the second section to access and persist data.

Using JDBC with Spring

JDBC support in the Spring Framework is part of the data access/persistence (also known as DAO) support provided by the framework. Hence, it is better to know about DAO support before going into the JDBC aspects. The most important feature of DAO support is consistency. The following are the two ways in which the Spring Framework provides consistency:

1. Consistency in Exception Hierarchy

2. Consistency in Abstract classes


The latter of the two is more helpful when using different data persistence frameworks such as Hibernate, JPA etc. Here are the details.

Consistency in Exception Hierarchy: The Spring Framework provides a translation from technology-specific exceptions such as Hibernate-related exceptions or JDBC-related exceptions by having its own hierarchy of data access-related exceptions. The DataAcessException class forms the root of this hierarchy. These exceptions are runtime exceptions instead of checked exceptions (such as Hibernate’s proprietary exceptions). This helps the developer to handle the non-recoverable errors in the appropriate layer instead of handling them at DAO level.

Consistency in Abstract classes: The Spring Framework provides a set of abstract classes that one can extend (not implement). There is one abstract class each for various data persistence technologies. These abstract classes have methods that can be used to set the required configurable properties that are specific to each technology. The main abstract classes are:

  • JdbcDaoSupport, which is the super class for accessing data using JDBC. You must provide a DataSource instance to it. This class, in turn, provides the JdbcTemplate instance, created from the supplied DataSource instance, to the sub-class.
  • HibernateDaoSupport, which is the super class for Hibernate data access objects. One has to supply an  instance of SessionFactory and in turn, it provides an instance of the HibernateTemplate class.

Apart from these, there are abstract classes for JDO, iBatis as well as JPA.

Now that the DAO support of the Spring Framework has been introduced, let us move on to the focus of this discussion – Spring’s support for JDBC. Spring supports JDBC through the JdbcTemplate class. However, before using the services of this class you need to understand the different "approaches" you can take in using the JdbcTemplate.

These approaches are based on the types of JdbcTemplate. The main types or sub-classes of JdbcTemplate are JdbcTemplate, NamedParameterJdbcTemplate and SimpleJdbcTemplate. Of the JdbcTemplate is the most commonly used type. Here are the details.

  • JdbcTemplate is the most widely used of all the types or flavors. It is the ‘lowest-level’ type. In other words, all other flavors make use of JdbcTemplate as their basis.
  • NamedParameterJdbcTemplate provides a wrapper for Named Parameter queries of JDBC. It wraps the JdbcTemplate to provide a more convenient way of handling parameterized queries instead of the conventional “?” placeholder provided by JDBC, thus making it easier to use multiple parameters. 
  • SimpleJdbcTemplate combines the most commonly used features of JdbcTemplate and NamedParameterJdbcTemplate. It also makes use of new features of Java 5 such as varargs, generics etc.


Apart from these, there are other flavors, including SimpleJdbcCall and SimpleJdbcInsert, that reduce the configuration required. That completes the overview of Spring Framework’s DAO support and JDBC support.

{mospagebreak title=Using JdbcTemplate Step-by-Step}

Next let us see how to use Spring’s JDBC support. Since JdbcTemplate is the ‘lowest-level’ of all the types, the steps required for JdbcTemplate become part of the steps for all other types. The following are the steps required to make use of JdbcTemplate:

1. Develop the bean

2. Configure the bean and DataSource

3. Develop the client

Of these, the second and third steps can be divided into sub-steps. Here are the details.


Develop the bean: The bean or POJO is similar to any other POJO used with the Spring Framework except for one difference. The POJO developed to be used with JdbcTemplate requires a setter for the DataSource object. The following is an example of a POJO that can be used with JdbcTemplate. The setter will pass the DataSource object to the instance of JdbcTemplate.

 

public class JdbcEventDao

{

private DataSource dataSource;

public void setDataSource(DataSource dataSource)

{

this.dataSource = dataSource;

}

 

public DataSource getDataSource()

{

return dataSource;

}

}


The POJO can contain other methods that can work with the DataSource.

Configure the DataSource and the bean: The configuration is done using the XML file. Lets call it beans.xml. This step can be further divided into configuring the DataSource, and configuring the bean. We will look at the DataSource first. 

<>

A DataSource is configured by declaring it as a bean and providing the required information as the child nodes of the bean declaration. The configuration is done as follows:

First, a bean is declared whose class is mapped to an implementation of
DataSource. One of the commonly-used implementations is org.apache.commons.dbcp.BasicDataSource. For example, to declare a bean with its class mapped to BasicDataSource class as the DataSource implementation class, the statement will be


<bean id="dataSource" destroy-method="close"

class="org.apache.commons.dbcp.BasicDataSource">

:

:

</bean>


Second, the required details for the DataSource such as driver name, URL of the DataSource, credentials etc. can be passed onto the DataSource through property tags. The property tags are the children of the <bean> tag. In versions prior to 2.5, the property tags had the <value> tag as the child tag. For example, to pass “com.mysql.jdbc.Driver” as the value of a property named “driverClassName,” the statement in version 2.5 and above will be


<bean id="dataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">

<property name="driverClassName"
value=”com.mysql.jdbc.Driver”/>

</bean>


and in previous versions it will be


<bean id="dataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">

<property name="driverClassName">

<value> com.mysql.jdbc.Driver</value>

</property>

</bean>


The next step is configuring the bean.

{mospagebreak title=Configuring the Bean}

Configuring the bean means providing a declaration for injecting the DataSource to the POJO or bean. It is done using the <ref> child tag of the <property> tag. The <ref> tag tells the Spring Framework that the current bean or POJO is referencing another bean. And the <property> tag tells the instance variable that it will receive the reference.

For example, the following statements tell the Spring Framework that the bean with the ID “dao” refers the bean with the of ID”dataSource.” The implementation of DataSource will be received by the DataSource instance variable within the JdbcEventDao class. The following is the code for the same:


<bean id="dao" class=" JdbcEventDao ">

<property name="dataSource">

<ref local="dataSource"/>

</property>

</bean>


Finally, we are up to the third step, developing the client. To develop the client, you must do two things. First, you must retrieve an instance of the JdbcTemplate class. To do so, an implementation of DataSource needs to be retrieved using JdbcEventDao POJO. The following statements will retrieve the DataSource implementation as well as a JdbcTemplate instance:


Resource res = new ClassPathResource("beans.xml");

BeanFactory factory = new XmlBeanFactory(res);

JdbcEventDao bean = (datacon)factory.getBean("dao");

DataSource ds=bean.getDataSource();

JdbcTemplate template = new JdbcTemplate(ds);

 

Second, the instance of the JdbcTemplate can be used to perform a CRUD (Insert, Select, Update, Delete) operation. For example, to retrieve all the records from “emp” table, the statement will be


List list;

list = template.queryForList(“select * from emp”);


The queryForList returns a list containing the values of the resultset.


That brings us to the end of this section. In the next section, an application will be developed based on these steps.

{mospagebreak title=JdbcTemplate in the Real World}

The application being developed will perform two functions. First, it will retrieve data related to a given ID. Second, it will delete the data related to a given ID. It is a simple application, but it will give you an idea of how JDBC and the Spring Framework work together. The application will have three main files:


  1. JdbcDataSource.java – The POJO having getter and setter for the DataSource reference.
  2. beans.xml – The configuration file for the Spring Framework.
  3. JdbcClient.java – A client that makes use of JdbcTemplate for retrieval and deletion functionalities.

Let us start with JdbcDataSource.java. It is same as the JdbcEventDao class. The following is the code:


public class JdbcDataSource

{

private DataSource dataSource;

public void setDataSource(DataSource dataSource)

{

this.dataSource = dataSource;

}

 

public DataSource getDataSource()

{

return dataSource;

}

}


Next comes the beans.xml. It contains the DataSource configuration as well as the bean configuration.


<bean id="dataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">

 

<property name="driverClassName">

<value> com.mysql.jdbc.Driver</value>

</property>


<property name="url">

<value>jdbc:mysql://localhost:3306/requisition</value>

</property>


<property name="username">

<value>root</value>

</property>


<property name="password">

<value>sql</value>

</property>


</bean>


<bean id="dao" class="JdbcDataSource">

<property name="dataSource">

<ref local="dataSource"/>

</property>

</bean>


And at last we’ve reached the client. It takes command line arguments and, according to the first argument, either retrieves or deletes using the second argument as the ID. Here is the code.


import java.io.*;

import org.springframework.beans.factory.*;

import org.springframework.beans.factory.xml.*;

import org.springframework.core.io.*;

import org.springframework.jdbc.core.*;

import org.springframework.jdbc.datasource.*;

import org.springframework.jdbc.object.*;

import org.springframework.jdbc.support.*;


public class JdbcClient

{

public static void main(String args[]) throws Exception

{

try

{

Resource res = new ClassPathResource("beans.xml");

BeanFactory factory = new XmlBeanFactory(res);

JdbcDataSource bean = (JdbcDataSource)factory.getbean(“dao”);


JdbcTemplate template = new JdbcTemplate(bean.getDataSource());

 

if(“select”.equalsIgnoreCase(args[0]))

{

List list;

list = template.queryForList(“select * from indents where”+
” indent_id ='”+args[1]+”‘”);

Iterator i=list.iterator();


while(i.hasNext())

{

Object ob = i.next();

out.println(ob.toString());

}

}

 

if(“delete”.equalsIgnoreCase(args[0]))

{

template.execute(“Delete from indents where indent_id ='” +
args[1]+”‘”);

}

 

}catch(Exception e)

{

}

}

}


The delete functionality makes use of the execute method of the JdbcTemplate class. This method can be used to execute other SQL statements including inserts and updates.

That completes this section as well as brings us to the end of this discussion. The approach discussed here is just one of the many approaches to using the Spring Framework’s JDBC support. There are other approaches for the same. Those will be discussed in the future. Till then…

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye