Problem

How to connect to MySQL database using Spring’s JDBC in a Spring’s MVC web application, and query database using Spring’s JdbcTemplate.

Solution

The simplest solution is to generate Spring MVC web application using Eclipse, and then utilize Spring’s JDBC dependency.

Technologies
  • Sprint Tool Suite IDE: Version: 3.7.1.RELEASE
  • Java 8
  • Tomcat 8
  • MySQL Server: 5.5.44
  • (Optional) MySQL Workbench: 6.2

MySQL database setup

Run below script to create a schema (database), and table.

CREATE DATABASE  IF NOT EXISTS `test_schema`;
USE `test_schema`;

DROP TABLE IF EXISTS test_tbl;
CREATE TABLE test_tbl (
  id int(11) NOT NULL,
  some_text varchar(45) NOT NULL,
  PRIMARY KEY (id)
);


INSERT INTO test_tbl VALUES (1,'hello world');

Project structure

In Eclipse, create a new Spring MVC project, the make following changes to see project structure as below.

project structure

Start off by importing below two dependencies

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		<!-- java mysql connector -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql-connector-java.version}</version>
		</dependency>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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.inforick.dbconn</groupId>
	<artifactId>inforick-dbconn</artifactId>
	<name>ProjectSpringMVC</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>1.8</java-version>
		<org.springframework-version>4.2.3.RELEASE</org.springframework-version>
		<mysql-connector-java.version>5.1.37</mysql-connector-java.version>
	</properties>
	<dependencies>
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		<!-- java mysql connector -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql-connector-java.version}</version>
		</dependency>

	<!-- NOTE: Remove these dependencies as they are provided by Tomcat when 
		deployed to production server. OR on local dev environment: right click the 
		project > targeted runtime > select tomcat server -->
	<!-- Servlet -->
	<dependency>
		<groupId>javax.servlet</groupId>
		<artifactId>servlet-api</artifactId>
		<version>2.5</version>
		<scope>provided</scope>
	</dependency>
	<dependency>
		<groupId>javax.servlet.jsp</groupId>
		<artifactId>jsp-api</artifactId>
		<version>2.1</version>
		<scope>provided</scope>
	</dependency>
	<dependency>
		<groupId>javax.servlet</groupId>
		<artifactId>jstl</artifactId>
		<version>1.2</version>
	</dependency>

	</dependencies>
	<build>
		<!-- Eclipse plugins. These are not required/used on production servers. -->
		<plugins>
			<plugin>
				<artifactId>maven-eclipse-plugin</artifactId>
				<version>2.9</version>
				<configuration>
					<additionalProjectnatures>
						<projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
					</additionalProjectnatures>
					<additionalBuildcommands>
						<buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
					</additionalBuildcommands>
					<downloadSources>true</downloadSources>
					<downloadJavadocs>true</downloadJavadocs>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.5.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
					<compilerArgument>-Xlint:all</compilerArgument>
					<showWarnings>true</showWarnings>
					<showDeprecation>true</showDeprecation>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.codehaus.mojo</groupId>
				<artifactId>exec-maven-plugin</artifactId>
				<version>1.2.1</version>
				<configuration>
					<mainClass>org.test.int1.Main</mainClass>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

root-content.xml is where we utilize the com.mysql.jdbc.Driver to connect to the database. The reason we use Spring’s JdbcTemplate is that it take cares of opening, and closing of Statements, connections, and exception handling. This way we can just focus on database query we are interested in.

root-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<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.xsd">

	<!-- Root Context: defines shared resources visible to all other web components -->

	<!-- Spring connecting to mysql database using com.mysql.jdbc.drivers -->
	<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/test_schema" />
		<property name="username" value="root" />
		<property name="password" value="root" />
	</bean>

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>

</beans>

In HomeController.java as you can see that there is no mention on connection, Statements etc. This is because Spring’s JdbcTemplate has taken care of all that.

Note: In production project, avoid using business logic i.e., database queries, java calculations etc in @Controllers.

HomeController.java

package com.inforick.myapp;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

/**
 * Handles requests for the application home page.
 */
@Controller
public class HomeController {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@RequestMapping(value = "/", method = RequestMethod.GET)
	public String home(Model model) {

	final String selectQuery = "select * from test_tbl where id=1";
	
	Map<?, ?> map = jdbcTemplate.queryForMap(selectQuery);
	model.addAttribute("data", map.toString());
	return "home";
	}
}

home.jsp

<html>
<head>
<title>Home</title>
</head>
<body>
	<h1>inforick.com</h1>

	<P>data from mysql database: ${data}.</P>
</body>
</html>

Ouput

Right click the project and run on server

output