How to connect to database using DriverManagerDataSource in Spring

Spring provides many ways to establish connection to a database and perform operations such as retrieval of records, insertion of new records and updating / deletion of existing records. The most basic of them is using class org.springframework.jdbc.datasource.DriverManagerDataSource. This post will detail it out in depth.

Declaration

For any class to be discovered by Spring, it needs to be declared in its configuration file as a bean. Hence the above mentioned class is also declared in Spring configuration file as :

<bean id=”dataSource”
        class=”org.springframework.jdbc.datasource.DriverManagerDataSource”></bean>

Initialization

In order to make connection to a database, some parameters such as database url, name, user name and password are required. These parameters are supplied as properties to the above bean declaration. Hence the complete bean declaration now becomes :

<bean id=”dataSource”
        class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
        <property name=”driverClassName” value=”com.mysql.jdbc.Driver” />
        <property name=”username” value=”root” />
        <property name=”password” value=”root” />
        <property name=”url” value=”jdbc:mysql://localhost:3306/codippa” />
</bean>

Logic

org.springframework.jdbc.datasource.DriverManagerDataSourcehas a method called getConnection()which returns a connection of type java.sql.Connection. This connection can then be used to create a java.sql.Statementor java.sql.PreparedStatementwhich then executes an SQL query just as done in traditional JDBC (Java DataBase Connectivity) method. Complete code follows :

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
 
public void fetchDataUsingDriverManagerDS() throws SQLException {
        // initialize Spring's Application context
	ApplicationContext context = new ClassPathXmlApplicationContext("spring-core-config.xml");
        // get bean declared with name "dataSource" in the configuration file
	DriverManagerDataSource dataSource = (DriverManagerDataSource) context.getBean("dataSource");
        // get connection
	Connection connection = dataSource.getConnection();
        // let's assume table name is student
	PreparedStatement preparedStatement = connection.prepareStatement("Select * from student");
	ResultSet resultSet = preparedStatement.executeQuery();
	while (resultSet.next()) {
                // student table has a column name
		String name = resultSet.getString("name");
                // student table also has a column rollno
		int rollNumber = resultSet.getInt("rollno");
		System.out.println("Name of student :: " + name);
		System.out.println("Roll Number of student ::" + rollNumber);
	}
        resultSet.close();
	connection.close();
}

In the above code spring-core-config.xml is the spring configuration file which lies on the classpath. Below is the screenshot of the file:

<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-4.2.xsd”>  
<bean id=”dataSource”
        class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
        <property name=”driverClassName” value=”com.mysql.jdbc.Driver” />
        <property name=”username” value=”root” />
        <property name=”url” value=”jdbc:mysql://localhost:3306/codippa” />
    </bean>
</beans>

The properties driverClassName, username, password defined in the bean declaration are the field names of the class  org.springframework.jdbc.datasource.DriverManagerDataSource.

Dependencies

The above example would need dependencies of Spring framework and the database used. This example has been tested on Spring framework v4.2.2 RELEASE and MySql database, so following would be the dependencies :

Maven

If you are using Maven as the build tool then the dependencies defined in the pom.xml are :

<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
  http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.codippa</groupId>
  <artifactId>JdbcExample</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>JdbcExample</name>
  <url>http://maven.apache.org</url>

 <dependencies>

        <!-- Spring framework -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring</artifactId>
		<version>4.2.2</version>
	</dependency>

        <!-- MySQL database driver -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.9</version>
	</dependency>

  </dependencies>
</project>

Gradle

If gradle is the build tool of your choice then below is the build.gradlefile to be used :

apply plugin: 'java'
apply plugin: 'eclipse'

repositories {
    mavenCentral()
}
sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
   compile group: 'org.springframework', name: 'spring-jdbc', version: '4.2.2.RELEASE'
   compile group: 'org.springframework', name: 'spring-beans', version: '4.2.2.RELEASE'
   compile 'mysql:mysql-connector-java:5.1.10'
}

Let’s tweak in :

  1. This method just changes the mechanism of retrieving connection as done in traditional jdbc way. You are no longer required to explicitly load the class using Class.forName()method, neither you are required to call DriverManager.getConnection()method. Spring creates a connection via its DriverManagerDataSource class.
  2. When using annotations, you can directly use @Autowiredannotation over the dataSourcefield and retrieving it using ApplicationContext is not required, which further cuts down a couple of lines of code.
  3. DriverManagerDataSource is an implementation of javax.sql.DataSourceand hence is a part of JDBC specification.
  4. For configuring org.springframework.jdbc.datasource.DriverManagerDataSourcebean using java.util.Properties object use the following declaration:

    <bean id=”dataSource”
            class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
            <property name=”url” value=”jdbc:mysql://localhost:3306/codippa” />
            <property name=”connectionProperties”>
                <props>
                    <prop key=”user”>root</prop>
                    <prop key=”password”>root</prop>
                    <prop key=”driverClassName”>com.mysql.jdbc.Driver</prop>
                </props>
            </property>
        </bean>

    Here connectionProperties is a field defined in class org.springframework.jdbc.datasource.AbstractDriverBasedDataSource which is the super class of DriverManagerDataSource class. The key names (user, password and driverClassName) of this properties object will be the field names of Connection implementation class in the appropriate database driver. In this case it would be MySql driver.

  5. This is a very basic method of connecting to a database using Spring as it does not provide connection pooling and as such can not handle multiple simultaneous connections. Hence it is not recommended to be used in production environment and should be used just for testing purposes.

Mark Your Impression

Close Menu