Spring JDBC configuration

This post explains the Spring JDBC configuration for accessing database in any Spring application. One or the most commonly used framework in java Spring gives support for ORM and JDBC, in this part we will look into configuration of JDBC with Spring.

To achieve this we only need one configuration file and a class containing main method. Here, configuration file is most important as we need to configure all the properties of any database like database name, database URL, username, password etc.

Step 1: Create a Java Project in eclipse, and add the required dependencies to its build path. We are using Spring 4 jars and MySQL connector jar for this example. At the end, we will end up creating project structure like-

Step 2: Jars added in the project build path are listed in below image.

Step 3: Now create a configuration file, say configuration.xml. This file contains the bean creation for MySQL database connection object.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<context:component-scan base-package="com.code2java" />

	<!-- This is Data Base configuration for MySQL -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/code2java" />
		<property name="username" value="root" />
		<property name="password" value="root" />
	</bean>

</beans>

Step 4: Now lets create a Java Class containing main function. In this class first we need to load the configuration file so that we can use the beans created in configuration file. Here we have created a DataSource object with name “dataSource” in config file and now we will use this object to get the connection to MySQL database and then get the result set. Below is the example to fetch the records from table Students and print.

package com.code2java;

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

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringJDBCConfiguration
{
	public static void main(String[] args)
	{
		/* Loading the application context XML configuration file */
		ApplicationContext context = new ClassPathXmlApplicationContext("configuration.xml");
		
		/* Getting the bean for DataSource defined in xml file */
		DataSource dataSource = (DataSource) context.getBean("dataSource");
		try
		{
			/* Getting the Connection object from data source object */
			Connection conn = dataSource.getConnection();
			
			/* SQL query to fetch details from table */
			String sql = "Select * from students";

			/* Creating prepared statement object */
			PreparedStatement ps = conn.prepareStatement(sql);
			
			/* Getting the Result Set after executing the prepared statement query */
			ResultSet rs = ps.executeQuery();
			
			/* Iterating over the Result Set */
			while (rs.next())
			{
				System.out.println(rs.getString("id")+" | "+rs.getString("name")+" | "+rs.getString("email")
				+" | "+ rs.getString("city") + " | "+rs.getString("country"));
			}
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
	}

}

Execute the program. The output on console will be like-

Hope this helps.

Regards,

Nikhil Naoghare.