Spring DAO Module

Every Jdbc Data Source object represents one JDBC Connection pool.

JDBC Connection pool is a factory that contains set of readily available JDBC Connection object before actually being used.

In Spring we can work with 3 types of connection pool.

1.Spring Built-in JDBC Connection pool. (org.springfw.jdbc.datasource.DriverManagerDataSource)

2.By Using 3rd party Connection pool s/w (Apache,DC3P,C3PO)   

3.By Using Web Server/ Application Server managed JDBC Connection Pool.

A Connection pool is a factory that contains set of readily available jdbc connection object before actually being used.

Jdbc datasource object always represents one jdbc connection pool. To access each connection object of connection pool we need to use jdbc datasource object

Org.springframework.jdbc.datasource.DriverManagerDataSource represents spring built-in dummy jdbc connection pool by giving datasource object.

Note: this is called dummy connection pool because it does not actually pools it actually creates connection object in the pool on demand so it is not recommended to use.

Sample Application By Using Spring Built-in JDBC Connection pool.

Interface(select.java)

public interface select {

String fetchEmpName(int e);

}

Implementation class(SelectBean.java)

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.DataSource;

public class SelectBean implements select {

DataSource ds;

public DataSource getDs() {

return ds;

}

  public void setDs(DataSource ds) {

this.ds = ds;

}

@Override

public String fetchEmpName(int e)  {

// TODO Auto-generated method stub

try {

Connection con=ds.getConnection();

Statement s=con.createStatement();

ResultSet rs=s.executeQuery("select ename from emp where empno="+e);

if(rs.next())

{

return rs.getString(1);

}

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

return "Not Found";

}

}

Configuration file(selectcfg.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.xsd">

<bean id="ds1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>

<property name="url"><value>jdbc:oracle:thin:@localhost:1521:xe</value></property>

<property name="username"><value>system</value></property>

<property name="password"><value>system123</value></property>

</bean>

<bean id="sb" class="SelectBean"><property name="ds"><ref bean="ds1"></ref></property></bean>

</beans>

Client Application(SelectClient.java)

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

import org.springframework.core.io.ClassPathResource;

public class SelectClient {

public static void main(String[] args) {

// TODO Auto-generated method stub

XmlBeanFactory fact=new XmlBeanFactory(new ClassPathResource("selectcfg.xml"));

select sb=(select)fact.getBean("sb");

System.out.println("empname is "+sb.fetchEmpName(7839));

}

}

Note: In Above application we need keep ojdbc14.jar file. and all files should be in source folder

In DAO Module we can use 3rd Party JDBC Connection Pool.

Type: 3rd party vendor supplied jdbc connection pool s/w

Jar file: C3P0-0.9.1.jar (Comes with hibernate s/w)

Bean properties:

driverClass, jdbcUrl, user, password 

Sample Application By Using  3rd Party  JDBC Connection pool.

Select.java

public interface select {

String fetchEmpName(int e);

}

SelectBean.java

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.DataSource;

public class SelectBean implements select {

DataSource ds;

public DataSource getDs() {

return ds;

}

public void setDs(DataSource ds) {

this.ds = ds;

}

@Override

public String fetchEmpName(int e)  {

try {

Connection con=ds.getConnection();

Statement s=con.createStatement();

ResultSet rs=s.executeQuery("select ename from emp where empno="+e);

if(rs.next())

{

return rs.getString(1);

}

} catch (SQLException e1) {

e1.printStackTrace();

}

return "Not Found";

}

}

Configuration File(selectcfg.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.xsd">

<bean id="ds1" class="com.mchange.v2.c3p0.ComboPooledDataSource">

<property name="driverClass"><value>oracle.jdbc.driver.OracleDriver</value></property>

<property name="jdbcUrl"><value>jdbc:oracle:thin:@localhost:1521:xe</value></property>

<property name="user"><value>system</value></property>

<property name="password"><value>cgvak123</value></property>

<property name="acquireIncrement"><value>2</value></property>

    <property name="initialPoolSize"><value>3</value></property>

    <property name="minPoolSize"><value>4</value></property>

    <property name="maxPoolSize"><value>50</value></property>

</bean>

<bean id="sb" class="SelectBean"><property name="ds"><ref bean="ds1"></ref></property></bean>

</beans>

Client Application(SelectClient.java) 

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

import org.springframework.core.io.ClassPathResource;

public class SelectClient {

public static void main(String[] args) {

// TODO Auto-generated method stub

XmlBeanFactory fact=new XmlBeanFactory(new ClassPathResource("selectcfg.xml"));

select sb=(select)fact.getBean("sb");

System.out.println("empname is "+sb.fetchEmpName(7934));

}

}