Open in app

Sign in

Write

Sign in

Spring — Data Source and Connection Pool | Code Factory

Code Factory
9 min readJan 29, 2021

Donate : Link

WordPress Blog : Link

Applications… : Link

Spring Tutorial Index Page: Link

  • * Below are the implementations provider and implementations for connection pool.
    - Sun -> DataSource ( I )
    - Apache -> BasicDataSource ( C )
    - Mchange c3p0 -> ComboPooledDataSource ( C )
    - Spring -> DriverManagerDataSource ( C )
    - WebLogic -> WebLogic Data Source
  • For small enterprice application if you want to use connection pool then ComboPooledDataSource and BasicDataSource is recommended.

BasicDataSourceTest.java

package com.codeFactory;import java.sql.Connection;
import java.util.Date;import org.apache.tomcat.dbcp.dbcp.BasicDataSource;/**
 * @author code.factory
 *
 */
public class BasicDataSourceTest {
	public static void main(String[] args) {
		Date date1, date2;
		
		// Apache
		BasicDataSource bds = new BasicDataSource();
		bds.setDriverClassName("com.mysql.jdbc.Driver");
		bds.setUrl("jdbc:mysql://localhost:3306/tutorial");
		bds.setUsername("root");
		bds.setPassword("root");
		bds.setMaxActive(15);
		bds.setMinIdle(5);
		bds.setMaxWait(1000*5); // 5 sec
		
		date1 = new Date();
		for(int i=0; i<20; i++) {
			try {
				Connection con = bds.getConnection();
				System.out.println(con + " " + i);
				// If we comment below line then it will create only 15 connections 
				// and then it will give error after 5 seconds
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		date2 = new Date();
		System.out.println("Start time : " + date1);
		System.out.println("End time : " + date2);
	}
}

Output:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
jdbc:mysql://localhost:3306/tutorial, UserName=root@localhost, MySQL Connector/J 0
jdbc:mysql://localhost:3306/tutorial, UserName=root@localhost, MySQL Connector/J 1
jdbc:mysql://localhost:3306/tutorial, UserName=root@localhost, MySQL Connector/J 2
...
...
jdbc:mysql://localhost:3306/tutorial, UserName=root@localhost, MySQL Connector/J 997
jdbc:mysql://localhost:3306/tutorial, UserName=root@localhost, MySQL Connector/J 998
jdbc:mysql://localhost:3306/tutorial, UserName=root@localhost, MySQL Connector/J 999
Start time : Tue Dec 22 20:29:53 IST 2020
End time : Tue Dec 22 20:29:58 IST 2020

BasicDataSourceTest2.java

package com.codeFactory;import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Date;/**
 * @author code.factory
 *
 */
public class BasicDataSourceTest2 {
	public static void main(String[] args) {
		Date date1, date2;
		
		date1 = new Date();
		for(int i=0; i<1000; i++) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/tutorial", "root", "root");
				System.out.println(con + " " + i);
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		date2 = new Date();
		System.out.println("Start time : " + date1);
		System.out.println("End time : " + date2);
	}
}

Output:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
com.mysql.cj.jdbc.ConnectionImpl@22eeefeb 0
com.mysql.cj.jdbc.ConnectionImpl@78ac1102 1
com.mysql.cj.jdbc.ConnectionImpl@396e2f39 2
...
...
com.mysql.cj.jdbc.ConnectionImpl@12c8a2c0 997
com.mysql.cj.jdbc.ConnectionImpl@365185bd 998
com.mysql.cj.jdbc.ConnectionImpl@4fb64261 999
Start time : Tue Dec 22 18:34:32 IST 2020
End time : Tue Dec 22 18:34:59 IST 2020
  • Run BasicDataSourceTest.java and after completion run BasicDataSourceTest2.java and check time and console.
  • You can see that using Apache BasicDataSource to create 1000 connections it only takes 5 seconds but create connection using normal way as in BasicDataSourceTest2 class which takes 30 seconds.
  • We can create BasicDataSource object using bean also. Create DataSource object into StudentDaoImpl class.
<bean id="bds" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" >
	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
	<property name="url" value="jdbc:mysql://localhost:3306/tutorial" />
	<property name="username" value="root" />
	<property name="password" value="root" />
	<property name="maxActive" value="15" />
	<property name="minIdle" value="5" />
	<property name="maxWait" value="5000" />
</bean><bean id="dao" class="com.codeFactory.dao.StudentDaoImpl">
	<property name="dataSource" ref="bds" />
</bean>
  • * In StudentBoImpl class we are taking here StudentDao interface reference, into this interface reference we are trying to inject interface implementation. In future if you don’t want this implementation then you can change your implementation part but it will not affect to your business because business is not having directly any implementation dependency, it is having interface dependency. So into that interface any implementation we can inject, if it is plain jdbc, hibernate or jpa, any implementation. Any implementation business (BO) will accept. That is what IOC (Inversion Of Control).
  • IOC means getting capabilities to make changes at any time.

Create Java Project

  • Open Eclipse
  • Go to File -> New -> Other… -> Java Project
  • Create ConnectionPool-JDBC project
  • Right click on project -> Build Path -> Configure Build Path -> Libraries tab -> Add External JARs (Used 2.X jars)
    - commons-logging-X.X.jar
    - spring-beans-X.X.X.jar
    - spring-context-X.X.X.jar
    - spring-core-X.X.X.jar
    - spring-dao-X.X.X.jar
    - spring-jdbc-X.X.X.jar
    - mysql-connector-java-X.X.X.jar
    - tomcat-dbcp-X.X.X.jar
    - c3p0-X.X.X.X.jar
  • * You can find dtd information from spring-beans-X.X.X.jar -> org -> springframework -> beans -> factory -> xml -> spring-beans.dtd (line no 36 & 37)

spring.xml

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN"
	"http://www.springframework.org/dtd/spring-beans-2.0.dtd"><beans>
	<bean id="bds" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" >
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/tutorial" />
		<property name="username" value="root" />
		<property name="password" value="root" />
		<property name="maxActive" value="15" />
		<property name="minIdle" value="5" />
		<property name="maxWait" value="5000" />
	</bean>
	
	<bean id="dao" class="com.codeFactory.dao.StudentDaoImpl">
		<property name="dataSource" ref="bds" />
	</bean>
	
	<bean id="bo" class="com.codeFactory.business.StudentBoImpl">
		<property name="studentDao" ref="dao" />
	</bean>
</beans>

Student.java

package com.codeFactory.model;/**
 * @author code.factory
 *
 */
public class Student {	private int id;
	private String name;
	private String address;	public Student() {	}	public Student(int id, String name, String address) {
		this.id = id;
		this.name = name;
		this.address = address;
	}	public int getId() {
		return id;
	}	public void setId(int id) {
		this.id = id;
	}	public String getName() {
		return name;
	}	public void setName(String name) {
		this.name = name;
	}	public String getAddress() {
		return address;
	}	public void setAddress(String address) {
		this.address = address;
	}
}

StudentDao.java

package com.codeFactory.dao;import java.util.List;import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
public interface StudentDao {	public int save(Student student) throws Exception;
	public boolean update(Student student) throws Exception;
	public boolean delete(Student student) throws Exception;
	public Student findById(int id) throws Exception;
	public List<Student> findAll() throws Exception;
}

StudentDaoImpl.java

package com.codeFactory.dao;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.List;import javax.sql.DataSource;import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
public class StudentDaoImpl implements StudentDao {	/* Apache BasicDataSource (C) implements DataSource (I) */
	private DataSource dataSource;	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}	@Override
	public int save(Student student) throws Exception {
		Connection con = dataSource.getConnection();
		PreparedStatement ps = con.prepareStatement("insert into student values(?, ?, ?)");
		ps.setInt(1, student.getId());
		ps.setString(2, student.getName());
		ps.setString(3, student.getAddress());
		int i = ps.executeUpdate();
		con.close();
		return i;
	}	@Override
	public boolean update(Student student) throws Exception {
		Connection con = dataSource.getConnection();		con.close();
		return false;
	}	@Override
	public boolean delete(Student student) throws Exception {
		Connection con = dataSource.getConnection();		con.close();
		return false;
	}	@Override
	public Student findById(int id) throws Exception {
		Connection con = dataSource.getConnection();		con.close();
		return null;
	}	@Override
	public List<Student> findAll() throws Exception {
		Connection con = dataSource.getConnection();		con.close();
		return null;
	}
}

StudentBo.java

package com.codeFactory.business;import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
public interface StudentBo {
	public int createStudent(Student student) throws Exception;	public boolean updateStudent(Student student) throws Exception;	public boolean deleteStudent(Student student) throws Exception;
}

StudentBoImpl.java

package com.codeFactory.business;import com.codeFactory.dao.StudentDao;
import com.codeFactory.model.Student;public class StudentBoImpl implements StudentBo {	private StudentDao studentDao;
	
	public void setStudentDao(StudentDao studentDao) {
		this.studentDao = studentDao;
	}
	
	@Override
	public int createStudent(Student student) throws Exception {
		return studentDao.save(student);
	}	@Override
	public boolean updateStudent(Student student) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}	@Override
	public boolean deleteStudent(Student student) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}
}

Client.java

package com.codeFactory;import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import com.codeFactory.business.StudentBo;
import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
public class Client {	public static void main(String[] args) throws Exception {
		ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("com/codeFactory/resources/spring.xml");
		StudentBo studentBo = (StudentBo) context.getBean("bo");
		int i = studentBo.createStudent(new Student(1, "Code Factory", "INDIA"));
		System.out.println(i);
		context.close();
	}
}
  • Run Client.java and in console you can see value of i which is 1, means successfully inserted.

Create Java Project

  • Open Eclipse
  • Go to File -> New -> Other… -> Java Project
  • Create ORM-Spring-HibernateTemplate project
  • Right click on project -> Build Path -> Configure Build Path -> Libraries tab -> Add External JARs
    - see below screenshot
  • * You can find namespace information from spring-beans-X.X.X.jar -> META-INF -> spring.schemas

spring.xml

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd 
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd">
	
	<tx:annotation-driven/>	<bean id="bds" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" >
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/tutorial" />
		<property name="username" value="root" />
		<property name="password" value="root" />
		<property name="maxActive" value="15" />
		<property name="minIdle" value="5" />
		<property name="maxWait" value="5000" />
	</bean>
	
	<bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
		<property name="dataSource" ref="bds" />
		
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
				<!-- <prop key="hibernate.hbm2ddl.auto">update</prop> -->
				<prop key="hibernate.show_sql">true</prop>
			</props>
		</property>
		
		<property name="annotatedClasses">
			<list>
				<value>com.codeFactory.model.Student</value>
			</list>
		</property>
	</bean>
	
	<bean id="transactionManager" class="org.springframework.orm.hibernate5.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory"/>
    </bean>
	
	<bean id="ht" class="org.springframework.orm.hibernate5.HibernateTemplate">
		<property name="sessionFactory" ref="sessionFactory"></property>
		<property name="checkWriteOperations" value="true"></property>
	</bean>
	
	<bean id="dao" class="com.codeFactory.dao.StudentDaoImpl">
		<property name="template" ref="ht" />
	</bean>
	
	<bean id="bo" class="com.codeFactory.business.StudentBoImpl">
		<property name="studentDao" ref="dao" />
	</bean>
</beans>

Student.java

package com.codeFactory.model;import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;/**
 * @author code.factory
 *
 */
@Entity
@Table(name = "student", schema = "tutorial")
public class Student {	@Id
	private int id;
	private String name;
	private String address;	public Student() {	}	public Student(int id, String name, String address) {
		this.id = id;
		this.name = name;
		this.address = address;
	}	public int getId() {
		return id;
	}	public void setId(int id) {
		this.id = id;
	}	public String getName() {
		return name;
	}	public void setName(String name) {
		this.name = name;
	}	public String getAddress() {
		return address;
	}	public void setAddress(String address) {
		this.address = address;
	}	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", address=" + address + "]";
	}
}

StudentDao.java

package com.codeFactory.dao;import java.util.List;import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
public interface StudentDao {	public int save(Student student);
	public boolean update(Student student);
	public boolean delete(Student student);
	public Student findById(int id);
	public List<Student> findAllUsingHQL();
	public List<Student> findAllUsingCriteria();
}

StudentDaoImpl.java

package com.codeFactory.dao;import java.util.List;import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate5.HibernateTemplate;
import org.springframework.transaction.annotation.Transactional;import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
@Transactional
public class StudentDaoImpl implements StudentDao {	private HibernateTemplate template;	public void setTemplate(HibernateTemplate template) {
		this.template = template;
	}	@Override
	public int save(Student student) {
		int id = (Integer) template.save(student);
		return id;
	}	@Override
	public boolean update(Student student) {
		template.update(student);
		return true;
	}	@Override
	public boolean delete(Student student) {
		template.delete(student);
		return true;
	}	@Override
	public Student findById(int id) {
		Student student = (Student) template.get(Student.class, id);
		return student;
	}	@Override
	public List<Student> findAllUsingHQL() {
		List<Student> list = (List<Student>) template.find("from Student");
		return list;
	}	@Override
	public List<Student> findAllUsingCriteria() {
		DetachedCriteria criteria = DetachedCriteria.forClass(Student.class);
		List<Student> list = (List<Student>) template.findByCriteria(criteria);
		return list;
	}
}

StudentBo.java

package com.codeFactory.business;import java.util.List;import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
public interface StudentBo {
	public int createStudent(Student student);	public boolean updateStudent(Student student);	public boolean deleteStudent(Student student);	public Student findById(int id);	public List<Student> findAllUsingHQL();	public List<Student> findAllUsingCriteria();
}

StudentBoImpl.java

package com.codeFactory.business;import java.util.List;import com.codeFactory.dao.StudentDao;
import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
public class StudentBoImpl implements StudentBo {	private StudentDao studentDao;
	
	public void setStudentDao(StudentDao studentDao) {
		this.studentDao = studentDao;
	}
	
	@Override
	public int createStudent(Student student) {
		return studentDao.save(student);
	}	@Override
	public boolean updateStudent(Student student) {
		// TODO Auto-generated method stub
		return false;
	}	@Override
	public boolean deleteStudent(Student student) {
		// TODO Auto-generated method stub
		return false;
	}	@Override
	public Student findById(int id) {
		return studentDao.findById(id);
	}	@Override
	public List<Student> findAllUsingHQL() {
		return studentDao.findAllUsingHQL();
	}	@Override
	public List<Student> findAllUsingCriteria() {
		return studentDao.findAllUsingCriteria();
	}
}

Client.java

package com.codeFactory;import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.transaction.annotation.EnableTransactionManagement;import com.codeFactory.business.StudentBo;
import com.codeFactory.model.Student;/**
 * @author code.factory
 *
 */
@EnableTransactionManagement
public class Client {	public static void main(String[] args) throws Exception {
		ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("com/codeFactory/resources/spring.xml");
		StudentBo studentBo = (StudentBo) context.getBean("bo");
		int i = studentBo.createStudent(new Student(3, "Code Factory", "INDIA"));
		System.out.println(i);
		
		System.out.println(studentBo.findById(1));
		
		System.out.println(studentBo.findAllUsingHQL());
		
		System.out.println(studentBo.findAllUsingCriteria());
		
		context.close();
	}
}

Output.java

Hibernate: insert into student (address, name, id) values (?, ?, ?)
3Hibernate: select student0_.id as id1_0_0_, student0_.address as address2_0_0_, student0_.name as name3_0_0_ from student student0_ where student0_.id=?
Student [id=1, name=INDIA, address=Code Factory]Hibernate: select student0_.id as id1_0_, student0_.address as address2_0_, student0_.name as name3_0_ from student student0_
[Student [id=1, name=INDIA, address=Code Factory], Student [id=3, name=Code Factory, address=INDIA]]Hibernate: select this_.id as id1_0_0_, this_.address as address2_0_0_, this_.name as name3_0_0_ from student this_
[Student [id=1, name=INDIA, address=Code Factory], Student [id=3, name=Code Factory, address=INDIA]]
Code Factory
Spring Tutorial
Spring
Spring Mvc
Spring Aop

--

--

Code Factory

Written by Code Factory

51 Followers

Help

Status

About

Careers

Blog

Privacy

Terms

Text to speech

Teams