Spring — Data Source and Connection Pool | Code Factory

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.
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);
}
}
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
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);
}
}
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)
<!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>
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;
}
}
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;
}
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;
}
}
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;
}
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;
}
}
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
<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>
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 + "]";
}
}
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();
}
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;
}
}
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();
}
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();
}
}
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();
}
}
Hibernate: insert into student (address, name, id) values (?, ?, ?)
3
Hibernate: 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]]

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Vavr: Turns JAVA upside down — Part 1

CPS: Confidence Promoter Score

Attended vs. Unattended RPA Robots: How to Figure Out What You Need

Top apps — from a personal perspective

TextFieldBoxes

Singleton Design Pattern Behavior

CS371p Spring 2022 Week 4: Fazal Ali

Symbl.ai Integrate to Disrupt Hackathon Experience

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Code Factory

Code Factory

More from Medium

How to Check the Health of a Spring boot Application using Spring Actuator

Indexes in POSTGRESQL

Functional Interfaces in Java

Exceptions in Java