Spring DataSource Example

0

In this article, we will see how to establish a database connection using JDBC.

Connecting to a Database

We can establish database connection by calling DriverManagerobject’s getConnection( ) method but before that we must assure that the driver is registered. Once the driver is registered, DriverManager can derive the actual Driver implementation based on the database URL.

How to register a Driver

The most common approach to register a Driver is to use Java’s Class.forName( ) method to dynamically load the driver’s class file into memory, which automatically registers it. The following example uses Class.forName( ) to register the mysql driver:

try {
    Class.forName("com.mysql.jdbc.Driver");
}
catch(ClassNotFoundException e) {
    System.out.println("Can't find class com.mysql.jdbc.Driver");
    System.exit(1);
}

Example of DriverManager.getConnection()

In the below example, I show you how to get a database connection. Once the connection is established, the example, executes few statements. It creates a database table, inserts data and queries the inserted data. To make sure we can run this example more than once, I drop the table before the issue of ‘create table’ statement.

JdbcConnectionExample:

package com.javarticles.jdbc.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcConnectionExample {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost/test", "root", "mnrpass" );            
        Statement stmt = conn.createStatement();
        stmt.execute("DROP TABLE IF EXISTS `TEST`"); 
        stmt.execute("CREATE TABLE TEST (NAME varchar(50) not null)");      
        stmt.execute("INSERT into TEST(NAME) VALUES ('JDBC')");
        ResultSet rs = stmt.executeQuery("SELECT NAME FROM TEST");
        while(rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

Example of javax.sql.DataSource

In JDBC 2.0 or higher, you can obtain database connections from a preconfigured javax.sql.DataSource object without knowing about the connection details.

In the below example, we use DataSource instead of DriverManager to get the Connection object. We use spring provided DriverManagerDataSource to create and configure the DataSource. When we call dataSource.setDriverClassName, the driver gets registered if not yet. setDriverClassName loads the driver class name which in turn will either throw ClassNotFoundException if the class is not found else register the driver.

Note that the configuration details still are part of the program. We will next see how we can externalize the configuration details using spring.

DataSourceConnectionExample:

package com.javarticles.jdbc.datasource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class DataSourceConnectionExample {
    private static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/test";
    private static final String USER = "root";
    private static final String PASS = "mnrpass";

    public static void main(String[] args) throws SQLException {
        Connection conn = getDataSource().getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute("DROP TABLE IF EXISTS `TEST`");
        stmt.execute("CREATE TABLE TEST (NAME varchar(50) not null)");
        stmt.execute("INSERT into TEST(NAME) VALUES ('JDBC')");
        ResultSet rs = stmt.executeQuery("SELECT NAME FROM TEST");
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }

    public static DataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(DRIVER_CLASS_NAME);
        dataSource.setUrl(URL);
        dataSource.setUsername(USER);
        dataSource.setPassword(PASS);
        return dataSource;
    }
}

Configure DataSource using spring

We will now configure DataSource in applicationContext.xml. We will have to specify our driver class, url, user and password in the bean definition.
Class SpringDataSourceConnectionExample is an example where we inject DataSource. You can see in its main method, we get its instance from ApplicationContext and then execute the database statements.

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" 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
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="mnrpass" />
	</bean>
	
	<bean id="springDsConExample" class="com.javarticles.jdbc.datasource.SpringDataSourceConnectionExample">
		<property name="dataSource"><ref bean="dataSource"/></property>
	</bean>
</beans>

SpringDataSourceConnectionExample:

package com.javarticles.jdbc.datasource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class SpringDataSourceConnectionExample {
    private DataSource dataSource;
    public static void main(String[] args) throws SQLException {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        SpringDataSourceConnectionExample springDsExample = (SpringDataSourceConnectionExample) context.getBean("springDsConExample");
        springDsExample.executeStatements();
    }

    public void executeStatements() throws SQLException {
        Connection conn = getDataSource().getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute("DROP TABLE IF EXISTS `TEST`");
        stmt.execute("CREATE TABLE TEST (NAME varchar(50) not null)");
        stmt.execute("INSERT into TEST(NAME) VALUES ('JDBC')");
        ResultSet rs = stmt.executeQuery("SELECT NAME FROM TEST");
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }

    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
}

Output:

JDBC

Using a Pooled DataSource vs Spring provided Data Sources

In our example, we have used DriverManagerDataSource. It is nice to use it for development purpose because of its simplicity but we can’t use it for production application.
The problem with DriverManagerDataSource is that the connections are not pooled and it returns a new connection every time a connection is requested incurring a performance cost.

Spring provides one more data source called SingleConnectionDataSource, as the name suggests, it returns the same connection every time that a connection is requested. Although SingleConnectionDataSource can be thought of as a data source with a pool of exactly one connection. Since SingleConnectionDataSource has one and only one database connection to work with, it can’t be used in a multithreaded application.

If you’re unable to retrieve a data source from JNDI, the next best thing is to configure a pooled data source directly in Spring. Jakarta Commons Database Connection Pools (DBCP) project provides us pooled data sources. To add DBCP to your application, either download it and place the JAR file in your Ant’s build classpath or add the following to the Maven pom.xml

<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>

DBCP includes several data sources that provide pooling, but the BasicDataSource is one that’s often used because it’s quite simple to configure in Spring resembles Spring’s own DriverManagerDataSource

	<bean id="pooledDataSource"
		class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="mnrpass" />
	</bean>

 

Download the source code

In this example we have seen how we can configure DataSourcespringdatasource.zip

Share.

Leave A Reply