How to connect to database using DriverManagerDataSource in Spring

DriverManagerDataSouce 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 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, in order to create a bean for DriverManagerDataSource, it is 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
DriverManagerDataSource has a getConnection() method which returns a connection of type java.sql.Connection.
This connection can then be used to create a java.sql.Statement or java.sql.PreparedStatement which then executes an SQL query in the same way as in JDBC (Java DataBase Connectivity).
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 class DriverManagerDataSourceExample {

   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 DriverManagerDataSource.

Dependencies
The above example would need dependencies of Spring framework and the database used.
This example has been tested on Spring framework v5.2.5 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.gradle file to be used :

apply plugin:'java' 
apply plugin:'eclipse' 
repositories { 
  mavenCentral() 
} 
sourceCompatibility = 1.8 
targetCompatibility = 1.8 
dependencies { 
  implementation 'org.springframework:spring-jdbc:6.0.11'
  implementation 'org.springframework:spring-beans:6.0.11'
  implementation 'com.mysql:mysql-connector-j:8.1.0'
}

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 @Autowired annotation 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.DataSource and hence is a part of JDBC specification.
  4. For configuring 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 AbstractDriverBasedDataSource.
    This is the super class of DriverManagerDataSource.
    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.
    For more advanced purposes, use JdbcTemplate.

Hope the article was useful.

Leave a Reply