Spring 3 MVC - JDBC Integration Tutorial

In this tutorial we will integrate database access using JDBC with a simple Spring 3 MVC application. We will manage a list of persons and provide a simple CRUD system for viewing, adding, editing, and deleting. We will use HyperSQL as our database, though the application is flexible enough to utilize a different database. The goal of this tutorial is to show how to integrate JDBC with Spring using annotations. I assume my readers have a knowledge of Spring MVC and relational databases. It would be beneficial to my readers to compare this tutorial with my other tutorial Spring 3 MVC - Hibernate 3: Using Annotations Integration Tutorial.

What is JDBC?

The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases – SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access.

Source: http://www.oracle.com/technetwork/java/javase/tech/index-jsp-136101.html  

What is HyperSQL?

HSQLDB (HyperSQL DataBase) is the leading SQL relational database engine written in Java. It has a JDBC driver and supports nearly full ANSI-92 SQL (BNF format) plus many SQL:2008 enhancements. It offers a small, fast multithreaded and transactional database engine which offers in-memory and disk-based tables and supports embedded and server modes. Additionally, it includes tools such as a command line SQL tool and GUI query tools.

Source: http://hsqldb.org/

Here's the folder structure of our application:

Let's begin by defining our Person object.

This is a simple POJO with four fields:

id
firstName
lastName
money

Since we will manipulate a list of persons, let's declare a service that manipulates a list of Persons.

PersonService

We've declared a simple CRUD system with the following methods:

getAll
add
delete
edit

In each method we prepared a sql String (for example):

String sql = "delete from person where id = ?";

If you have worked with a relational database before, that statement should be familiar already to you.

We also assigned parameters in each statement if required (for example):

Map parameters = new HashMap();
  parameters.put("firstName", firstName);
  parameters.put("lastName", lastName);
  parameters.put("money", money);

Here we're using Named Parameters so that we easily interchange the order of the parameters.

Notice in each method the actual database action is delegated to an instance of SimpleJdbcTemplate

What is a SimpleJdbcTemplate?

The SimpleJdbcTemplate class wraps the classic JdbcTemplate and leverages Java 5 language features such as varargs and autoboxing.

Source: http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html

What is a JdbcTemplate?

The JdbcTemplate class is the central class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors such as forgetting to close the connection. It performs the basic tasks of the core JDBC workflow such as statement creation and execution, leaving application code to provide SQL and extract results. The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

Source: http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html

We're done with the Person object and the Service layer. Let's move to the Spring controller.

MainController

This controller declares four mappings:

/persons
/persons/add?firstname=''&lastname=''&money='' 
/persons/delete?id='' 
/persons/edit?id=''&firstname=''&lastname=''&money=''

Each mapping delegates the call to the PersonService. When the PersonService is done processing, the controller then forwards the request to a JSP page that displays a confirmation message.

Added Page

addedpage.jsp

Edited Page

editedpage.jsp

Deleted Page

deletedpage.jsp

Main Page


To finish our Spring MVC application, we need to declare a couple of required XML configurations.

To enable Spring MVC we need to add it in the web.xml

web.xml

Take note of the URL pattern. When accessing any pages in our MVC application, the host name must be appended with

/krams

In the web.xml we declared a servlet-name spring. By convention, we must declare a spring-servlet.xml as well.

spring-servlet.xml

By convention, we must declare an applicationContext.xml as well.

applicationContext.xml

If you're following my previous tutorials, at this point, our application should now be finished. But we're not done yet. Notice in the applicationContext.xml, we declared the following import:

jdbc-context.xml

We basically encapsulated all JDBC and Spring related configurations in this one XML file. Here's what happening within the config:

1. Enable transaction support through Spring annotations:

2. Declare a datasource:

Our datasource uses C3P0 for pooling to allow efficient access to our database. Why do we need to wrap our datasource with a connection pool?

JDBC connections are often managed via a connection pool rather than obtained directly from the driver. Examples of connection pools include BoneCP, C3P0 and DBCP.

Source: http://en.wikipedia.org/wiki/Java_Database_Connectivity

What is Pooling?

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established.

Source: http://en.wikipedia.org/wiki/Connection_pool

There are different providers of pooling. C3P0 is one of the good ones.
For more info on configuring C3P0, you can check this reference from JBoss: HowTo configure the C3P0 connection pool. For a list of other pooling providers, see Open Source Database Connection Pools

The database-specific configuration are contained within a properties file.

spring.properties

As an alternative, we can enter these properties directly within the jdbc-context.xml

This is exactly similar to the following:

The benefit of using a separate properties file is we encapsulate all database-specific configs within a separate file. The jdbc-context.xml purpose is to encapsulate JDBC-related config not database properties.

Our application is now finished. We've managed to setup a simple Spring 3 MVC application with JDBC support. We've managed to build a CRUD system using JDBC. We've also leveraged Spring's MVC programming model via annotation.

To access the main page, enter the following URL:

http://localhost:8080/spring-jdbc/krams/main/persons

To add a new user, enter the following URL:

http://localhost:8080/spring-jdbc/krams/main/persons/add?firstname=John&lastname=Smith&money=1000

To delete a user, enter the following URL:

http://localhost:8080/spring-jdbc/krams/main/persons/delete?id=1

To edit a user, enter the following URL:

http://localhost:8080/spring-jdbc/krams/main//persons/edit?id=1&firstname=Johnny&lastname=Smith&money=2000

Just change the URL parameters to match the id that you're editing. If the id doesn't exist or the format is incorrect, expect an error to be thrown.

In order to make the application run, you need to run an instance of HSQLDB. If you're using Eclipse, all you need to do is import the whole project. Then find the hsqldb-2.0.0.jar on under the Libraries then do the following:
1. Right-click on the hsqldb-2.0.0.jar.
2. Select Run As.
3. Choose Server - org.hsqldb.server. This will run an instance of HSQLDB.

If you need a GUI, you can run the built-in Swing interface:

Here's how it looks like:

If you need further help, consult the HSQLDB documentation. If you want to use a different database, like MySQL, make sure to include the Java drivers first. Then update the spring.properties and hibernate.cfg.xml accordingly.

Because we're using JDBC here, we need to create the database schema manually. I have included the database sql_script within the project itself. You can import the schema from HSQLDB's Swing interface. The actual sql_script is really short:

The best way to learn further is to try the actual application.

Download the project
You can access the project site at Google's Project Hosting at http://code.google.com/p/spring-mvc-hibernate-annotations-integration-tutorial/

You can download the project as a Maven build. Look for the spring.jdbc.zip in the Download sections.

You can run the project directly using an embedded server via Maven.
For Tomcat: mvn tomcat:run
For Jetty: mvn jetty:run

If you want to learn more about Spring MVC and integration with other technologies, feel free to read my other tutorials in the Tutorials section.

---

Share the joy: blogger tutorials Social Bookmarking Blogger Widget I'm reading: Spring 3 MVC - JDBC Integration Tutorial ~

Subscribe by reader Subscribe by email Share

Spring MVC 3, Hibernate Annotations, HSQLDB Integration Tutorial

In this tutorial we will build a simple Spring MVC 3 application for managing a list of persons. We will provide a simple CRUD system for viewing, adding, editing, and deleting of persons. For the persistence layer, we will use Hibernate 3 annotations and use HSQLDB as our database, though the application is flexible enough to utilize different databases. A prior knowledge of MVC, ORM, and SQL is assumed in this tutorial

Spring MVC 3 and Hibernate Tutorials Series
Spring - Hibernate: Many-To-One Association - Explicitly Specify Join Table, Cascade, and Fetch
Spring - Hibernate: One-To-Many Association - Explicitly Specify Join Table, Cascade, and Fetch
Spring - Hibernate: Many-To-One Association
Spring - Hibernate: One-To-Many Association
Spring MVC 3, Hibernate Annotations, MySQL Integration Tutorial
Spring MVC 3, Hibernate Annotations, HSQLDB Integration Tutorial

What is Hibernate?

Hibernate is an object-relational mapping (ORM) library for the Java language, providing a framework for mapping an object-oriented domain model to a traditional relational database. Hibernate solves object-relational impedance mismatch problems by replacing direct persistence-related database accesses with high-level object handling functions

Source: http://en.wikipedia.org/wiki/Hibernate_(Java)

What is HyperSQL?

HSQLDB (HyperSQL DataBase) is the leading SQL relational database engine written in Java. It has a JDBC driver and supports nearly full ANSI-92 SQL (BNF format) plus many SQL:2008 enhancements. It offers a small, fast multithreaded and transactional database engine which offers in-memory and disk-based tables and supports embedded and server modes. Additionally, it includes tools such as a command line SQL tool and GUI query tools.

Source: http://hsqldb.org/

Let's preview first the final structure of our project.


We start by defining our domain object Person

Person

Person is a simple POJO containing four private fields:

id
firstName
lastName
money

Each of these fields have been annotated with @Column and assigned with corresponding names

ID
FIRST_NAME
LAST_NAME
MONEY

These column names are database column names. You don't deal with them. Instead, Hibernate is the one responsible for managing your database. However, you are responsible for declaring the column names in the POJO. You don't declare them in your database. Remember your database doesn't exist yet.

The POJO has been annotated to map to a database table. If you look at the declaration of the Person class we see the annotation @Table and the name of the actual table:

Notice the annotation @Entity before the @Table. This tells Hibernate that this POJO should be mapped to a database table.

Since we will manipulate a list of persons, let's declare a service that manipulates a list of Persons.

PersonService

We've declared a simple CRUD system with the following methods:

getAll()
add()
delete()
edit()

In each method we retrieve the session:

Session session = sessionFactory.getCurrentSession();

This is similar to retrieving a connection from the database so that we can do our work. The Session object provides numerous methods for persisting objects. For this tutorial, we use the following Session methods:

session.createQuery()
session.save()
session.delete()

We're done with the domain and the service layer. Let's move to the Spring controller.

MainController

This controller declares the following mappings:

/persons
/persons/add?firstname=''&lastname=''&money='' 
/persons/delete?id='' 
/persons/edit?id=''&firstname=''&lastname=''&money=''

Each mapping delegates the call to the PersonService. When the PersonService is done processing, the controller then forwards the request to a JSP page that displays a confirmation message. Here are the JSP pages.

Added Page


addedpage.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page import="java.util.Date" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>Persons</h1>

<p>You have added a new person at</p>
<%= new java.util.Date() %>

</body>
</html>

Edited Page


editedpage.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page import="java.util.Date" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>Persons</h1>

<p>You have edited a person with id ${id} at</p>
<%= new java.util.Date() %>

</body>
</html>

Deleted Page


deletedpage.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page import="java.util.Date" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>Persons</h1>

<p>You have deleted a person with id ${id} at</p>
<%= new java.util.Date() %>

</body>
</html>

Main Page


personspage.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>Persons</h1>

<table>
 <tr>
  <td width="50">Id</td>
  <td width="150">First Name</td>
  <td width="150">Last Name</td>
  <td width="50">Money</td>
 </tr>
 <c:forEach items="${persons}" var="person">
  <tr>
   <td><c:out value="${person.id}" /></td>
   <td><c:out value="${person.firstName}" /></td>
   <td><c:out value="${person.lastName}" /></td>
   <td><c:out value="${person.money}" /></td>
  </tr>
 </c:forEach>
</table>

</body>
</html>

Let's complete our Spring MVC application by declaring the required configurations.

To enable Spring MVC we need to add in the web.xml

web.xml

Take note of the URL pattern. When accessing any pages in our MVC application, the host name must be appended with

/krams

In the web.xml we declared a servlet-name spring. By convention, we must declare a spring-servlet.xml as well.

spring-servlet.xml

By convention, we must declare an applicationContext.xml as well.

applicationContext.xml

If you're following my previous tutorials, at this point, our application should now be finished. But we're not done yet. Notice in the applicationContext.xml, we declared the following import:

hibernate-context.xml

We basically encapsulated all Hibernate and Spring related configurations in this one XML file. Here's what happening within the config:

1. Enable transaction support through Spring annotations:

2. Declare the Hibernate SessionFactory:

A SessionFactory is a factory that produces Session objects. It's analogous to a real Car Factory or Car Assemblies where its job is to make cars for humans.

What is a Session?

The main function of the Session is to offer create, read and delete operations for instances of mapped entity classes.

Source: http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/Session.html

A SessionFactory requires a datasource. The datasource in this tutorial is the database.

A SessionFactory requires a configLocation. The configLocation contains Hibernate-specific configurations. Here's our Hibernate-specific config file:

hibernate.cfg.xml

Here we declared the type of database to be used. We are using HSQL so we use the HSQLDialect. If you're using MySQL, use the following dialect org.hibernate.dialect.MySQL5InnoDBDialect instead. Everything else is the same inside this hibernate config file. As mentioned earlier, it's easy to switch databases with Hibernate.

Returning back to the SessionFactory bean declaration, it also requires the property packagesToScan. This is to indicate where our annotated entities are located. In this tutorial, it's under the org.krams.tutorial

3. Declare a datasource:

Our datasource uses C3P0 for pooling to allow efficient access to our database. Why do we need to wrap our datasource with a connection pool?

JDBC connections are often managed via a connection pool rather than obtained directly from the driver. Examples of connection pools include BoneCP, C3P0 and DBCP.

Source: http://en.wikipedia.org/wiki/Java_Database_Connectivity

What is Pooling?

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established.

Source: http://en.wikipedia.org/wiki/Connection_pool

For more info on configuring C3P0, you can check this reference from JBoss: HowTo configure the C3P0 connection pool. For a list of other pooling providers, see Open Source Database Connection Pools

The database-specific configuration are contained within a properties file.

spring.properties

As an alternative, we can enter these properties directly within the hibernate-context.xml

This is exactly similar to the following:


The benefit of using a separate properties file is we encapsulate all database-specific configs within a separate file. The hibernate-context.xml purpose is to encapsulate Hibernate-related config not database properties.

That's it. We've completed our application. We've managed to setup a simple Spring MVC 3 application that uses Hibernate Annotations to encapsulate persistence access to a HQSLDB database. We've also leveraged Spring's simple MVC programming model.

To access the main page, enter the following URL:

http://localhost:8080/spring-hibernate/krams/main/persons

To add a new user, enter the following URL:

http://localhost:8080/spring-hibernate/krams/main/persons/add?firstname=John&lastname=Smith&money=1000

To delete a user, enter the following URL:

http://localhost:8080/spring-hibernate/krams/main/persons/delete?id=1

To edit a user, enter the following URL:

http://localhost:8080/spring-hibernate/krams/main//persons/edit?id=1&firstname=Johnny&lastname=Smith&money=2000

Just change the URL parameters to match the id that you're editing. If the id doesn't exist or the format is incorrect, expect an error to be thrown.

In order to make the application run, you need to run an instance of HSQLDB. If you're using Eclipse, all you need to do is import the whole project. Then find the hsqldb-2.0.0.jar on under the Libraries then do the following:

1. Right-click on the hsqldb-2.0.0.jar.

2. Select Run As. Select Java Application

3. Choose Server - org.hsqldb.server. This will run an instance of HSQLDB.

If you need a GUI, you can run the built-in Swing interface:

Here's how it looks like:

If you need further help, consult the HSQLDB documentation. If you prefer to use a MySQL database, please read my other tutorial Spring MVC 3, Hibernate Annotations, MySQL Integration Tutorial

The best way to learn further is to try the actual application.

Download the project
You can access the project site at Google's Project Hosting at http://code.google.com/p/spring-mvc-hibernate-annotations-integration-tutorial/

You can download the project as a Maven build. Look for the spring-hibernate.zip in the Download sections.

You can run the project directly using an embedded server via Maven.
For Tomcat: mvn tomcat:run
For Jetty: mvn jetty:run

If you want to learn more about Spring MVC and integration with other technologies, feel free to read my other tutorials in the Tutorials section.

---

Share the joy: blogger tutorials Social Bookmarking Blogger Widget I'm reading: Spring MVC 3, Hibernate Annotations, HSQLDB Integration Tutorial ~

Subscribe by reader Subscribe by email Share