Spring MVC & Spring JDBC with H2 database example!

..with a few tricks.

Starting point of using Spring JDBC support is to define a DataSource bean.

JDBC does not completely abstract away the differences in SQL notations across different database vendors. In general, you need to write your SQL statements specific to your relational database vendor. However, JDBC provides a standardized API to connect, execute SQL operations, fetch and process query results and properly close up database resources, such as connections.

We start working with JDBC by obtaining a database connection using database connection URL and database authentication credentials. When a connection is obtained, if required, we start a transaction and create a statement object to execute our SQL.

Managing JDBC Connections

To start working with JDBC and utilizing Spring's JDBC support, you first need to obtain a database connection. There are basically 2 ways to obtain a database connection:
  • DriverManager
  • DataSource
DataSource is preferable because it is a generalized factory that enables the developer to hide database connection parameters, connection pooling and transaction management issues from the application.

Example
Find the org.h2.tools.Console class of the H2 database from the project classpath, and run it
as a Java application within your IDE. When it runs, the browser automatically appears on your screen with the database console.

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>biz.tugay</groupId>
    <artifactId>spring-hello</artifactId>
 
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
 
    <name>spring-hello</name>
    <url>http://maven.apache.org</url>
 
    <dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.0.5.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.0.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.192</version>
        </dependency>
 
    </dependencies>
 
    <build>
        <finalName>hello-spring-mvc</finalName>
        <plugins>
            <plugin>
                <groupId>org.eclipse.jetty</groupId>
                <artifactId>jetty-maven-plugin</artifactId>
                <version>9.2.1.v20140609</version>
                <configuration>
                    <scanIntervalSeconds>2</scanIntervalSeconds>
                    <webApp>
                        <contextPath>/springmvc</contextPath>
                    </webApp>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
                             http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
         version="3.0">
 
    <servlet>
        <servlet-name>springmvc</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    </servlet>
 
    <servlet-mapping>
        <servlet-name>springmvc</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
</web-app>

springmvc-servlet.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"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans.xsd 
                           http://www.springframework.org/schema/context 
                           http://www.springframework.org/schema/context/spring-context.xsd">

    <context:annotation-config/>
    <context:component-scan base-package="biz.tugay"/>
</beans>

DataSourceConfiguration.java
package biz.tugay;
 
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;
 
import javax.sql.DataSource;
 
/**
 * Koray Tugay
 * 6/21/2016
 */
 
@Service
public class DataSourceConfiguration {
 
    public DataSource dataSource() {
        final DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:tcp://localhost/~/test");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }
}

HelloWorldController.java
package biz.tugay;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
 
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
/**
 * Koray Tugay
 * 6/20/2016
 */
@Controller
@RequestMapping(value = "/hello")
public class HelloWorldController {
 
    @Autowired
    private DataSourceConfiguration dataSourceConfiguration;
 
    @RequestMapping(value = "/world", method = RequestMethod.GET)
    public void helloWorld() {
        final DataSource dataSource = dataSourceConfiguration.dataSource();
        try {
            final Connection connection = dataSource.getConnection();
            final Statement statement = connection.createStatement();
            final ResultSet resultSet = statement.executeQuery("SELECT * FROM ACCOUNT");
            while (resultSet.next()) {
                final String owner_name = resultSet.getString("OWNER_NAME");
                System.out.println("Owner name is: " + owner_name);
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}