Problem

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

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.

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>
</beans>
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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
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 DataSource dataSource;

	/**
	 * Simply selects the home view to render by returning its name.
	 */
	@RequestMapping(value = "/", method = RequestMethod.GET)
	public String home(Model model) {

		String sql = "select * from test_tbl where id=?";
		Connection conn = null;
		HashMap<Integer, String> map = new HashMap<Integer, String>();
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, 1);
			ResultSet rs = ps.executeQuery();

			// get the first result (if any) and put it in map.
			if (rs.next()) {
				Integer key = rs.getInt("id");
				String value = rs.getString("some_text");
				map.put(key, value);
			}
			rs.close();
			ps.close();

		} catch (SQLException e) {
			throw new RuntimeException(e);

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		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