Articles

Spring is one of the most popular Java frameworks. With our Private JVM Java hosting packages you can use any framework, library and jar. Here we show an example of building and deploying database Spring application. This article will be extended by other Spring 2 and 3 related topics in the future.

For Spring Boot see this article.

Spring and JDBCTemplate

Aside from well-known classic JDBC usage scheme Spring includes JdbcTemplate class that facilitates database related tasks. Below we will show how to build simple web application (book library) that implements CRUD opeartions (Create, Read, Update, Delete) with the help of JdbcTemplate. To make this project fully portable H2 embedded database will be used. It is easy to change it to HSQL, Derby or any standalone database and add connection pooling like C3P0 by only editing applicationContext.xml. Already built WAR file can also be downloaded and deployed on your application server. It works out of the box. Java code is contained in 3 files: Book.java, BookDao.java and index.jsp.

Required Spring 3, Apache Commons and H2 jars

For the example to compile you will need to download the following jars to WEB-INF/lib of your Java web application:

commons-logging-1.1.3.jar
h2-1.3.174.jar
spring-beans-3.2.4.RELEASE.jar
spring-context-3.2.4.RELEASE.jar
spring-core-3.2.4.RELEASE.jar
spring-expression-3.2.4.RELEASE.jar
spring-jdbc-3.2.4.RELEASE.jar
spring-tx-3.2.4.RELEASE.jar

As you can see in Spring 3 classes have been split to subsets in separate jars. For Spring 2 the jar set will be different.

Define DAO (Data Access Objects) for Book class

In the example, read (search/select) operation is only coded for ID field but you can easily extend readBook method so that it recognizes if there are other attributes set and constructs proper query. Alternativley you can create dedicated methods for each attribute combination for example readBookByAuthorAndTitle and add code that determines what method to call depending on what search criteria have been provided in the form. Here goes the contents of BookDao.java. Note the usage of BeanPropertyRowMapper class that simplifies coding. The class is provided by Spring JDBC jar.

package com.jvmhost;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import java.util.List;
import java.util.ArrayList;

public class BookDao {
 private JdbcTemplate jdbcTemplate;

 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
  this.jdbcTemplate = jdbcTemplate;
 }

 public int createBook(Book b){
 String query="insert into book (author, title) values ('"+b.getAuthor()+"','"+b.getTitle()+"')";
 return jdbcTemplate.update(query);
 }

 public int updateBook(Book b){
 String query="update book set author='"+b.getAuthor()+"',title='"+b.getTitle()+"' where id="+b.getId()+" ";
 return jdbcTemplate.update(query);
 }

 public int deleteBook(Book b){
 String query="delete from book where id="+b.getId()+" ";
 return jdbcTemplate.update(query);
 }

 @SuppressWarnings("unchecked")
 public List<Book> readAllBooks(){
 String query = "SELECT * FROM book";
 List<Book> books = jdbcTemplate.query(query, new BeanPropertyRowMapper(Book.class));
 return books;
 }

 @SuppressWarnings("unchecked")
 public List<Book> readBook(Book b) {
 String query = "select * from book where ";
 List<Book> books = new ArrayList<Book>();
 if (b.getId() >0) {
  query = query + "id = ?";
  books = jdbcTemplate.query(query, new Object[] { b.getId() }, new BeanPropertyRowMapper(Book.class));
  } else {
   query = "SELECT * FROM book";
   books = jdbcTemplate.query(query, new BeanPropertyRowMapper(Book.class));
  }
 return books;
 }
}

Define Book class

The class is quite generic and consists of getter/setter methods for private variables and a few constructors. Different costructors are used depending on what data has been provided in the CRUD form. Contents of Book.java follows:

package com.jvmhost;

public class Book {
 private int id;
 private String title;
 private String author;

 // constructors

 public Book() {}

 public Book(int id) {
 super();
 this.id = id;
 }

 public Book(String author, String title) {
 super();
 this.title = title;
 this.author = author;
 }

 public Book(int id, String author, String title) {
 super();
 this.id = id;
 this.title = title;
 this.author = author;
 }

// getter setter methods

 public int getId() { return id; }
 public void setId(int id) { this.id = id; }
 public String getTitle() { return title; }
 public void setTitle(String title) { this.title = title; }
 public String getAuthor() { return author; }
 public void setAuthor(String author) { this.author = author; }
}

The index.jsp and CRUD form logic

Here we import needed classes, define the form and load beans from applicationContext.xml. Then we determine real path for our web application. As the bundled example database resides directly in web application’s db sudirectory we only append its name db/book when dynamically constructing JDBC URL. We then retrieve DriverManagerDataSource bean and update the URL with setUrl method. Finally we instantiate DAO object.

By using the webRootPath variable our application will always know where exactly book.h2.db file resides, for example on a shared Linux server it can be in
/home/username/apache-tomcat/7.0.42/webapps/spring-jdbc-example/db/book.h2.db and when we deploy it on our local Windows PC it can be d:\myprojects\tomcat\mywebapps\spring-jdbc-example\db\book.h2.db.

In further lines we get the request parameters. In production application you should test the input more thoroughly. Next there is IF logic that calls different DAO object methods depending on what button has been clicked on the form. Finally either a selection or all records are displayed. Each database operation causes its status displayed too.

<%@page import="com.jvmhost.*,java.util.*,org.springframework.context.*,org.springframework.context.support.*,org.springframework.jdbc.datasource.DriverManagerDataSource" %>

<form method=post><table>
<tr><td>ID (autoincrement)</td><td>Author</td><td>Title</td></tr>
<tr><td><input type=text name="id"></td><td><input type=text name="author"></td><td><input type=text name="title"></td></tr>
<tr><td colspan=3>CRUD operations:&nbsp;<input type=submit name="op" value="Create"><input type=submit name="op" value="Select"><input type=submit name="op" value="Update"><input type=submit name="op" value="Delete"></td></tr>
</table>
</form>

<%
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
DriverManagerDataSource dm = (DriverManagerDataSource) context.getBean("ds");
String webRootPath = application.getRealPath("").replace('\\', '/');
dm.setUrl("jdbc:h2:"+webRootPath+"/db/book;IFEXISTS=TRUE");
BookDao dao=(BookDao)context.getBean("bdao");

String op = (request.getParameter("op") == null?"":request.getParameter("op"));
int id = Integer.parseInt(request.getParameter("id") == null?"0":request.getParameter("id").equals("")?"0":request.getParameter("id"));
String author = (request.getParameter("author") == null?"":request.getParameter("author"));
String title = (request.getParameter("title") == null?"":request.getParameter("title"));

List<Book> books = new ArrayList<Book>();
%><hr><%
if (op.equals("Create")) {
 int status=dao.createBook(new Book(author,title));
 out.println("Create result: "+(status > 0?"OK":"FAIL")+"<br/>");
} else if (op.equals("Delete")) {
 int status=dao.deleteBook(new Book(id));
 out.println("Delete result: "+(status > 0?"OK":"FAIL")+"<br/>");
} else if (op.equals("Update")) {
 int status=dao.updateBook(new Book(id, author, title));
 out.println("Update result: "+(status > 0?"OK":"FAIL")+"<br/>");
} 

if (op.equals("Select")) {
 books=dao.readBook(new Book(id, author, title));
 int status = books.size();
 out.println("Select result: "+(status > 0?"OK":"FAIL")+"<br/>");
} else {
 books = dao.readAllBooks();
 int status = books.size();
 out.println("Select result: "+(status > 0?"OK":"FAIL")+"<br/>");
}
%><hr><%
for(Book b: books){
 out.println(b.getId()+", "+b.getAuthor()+", "+b.getTitle()+"<br/>");
}
%>

Alternatively index.jsp can be replaced with a servlet. Servlet mapping will need to be added in web.xml.

Spring beans in applicationContex.xml

applicationContext.xml (located in WEB-INF/classes) stores beans used by the application. Beans reference each other except ds that is a base bean. The problem that exists when storing JDBC URL in .xml file or .properties file is that usually variables cannot be used there and so we need to have the URL hardcoded. This makes embedded databases packaged in WAR files non-portable. The URL given is a generic one and we then dynamically modify it in index.jsp so that it matches real path to the database file in any specific deployment environment.

<bean id="ds">
 <property name="driverClassName" value="org.h2.Driver" />
 <property name="url" value="jdbc:h2:~/book;IFEXISTS=TRUE" />
 <property name="username" value="tomcat" />
 <property name="password" value="cphrEf4" />
</bean> 

<bean id="jdbcTemplate">
 <property name="dataSource" ref="ds"></property>
</bean> 

<bean id="bdao">
 <property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>

The web.xml is generic. As we do not use servlet we do not have to define mappings inside it. Only access to db subdirectory is disabled for visitors. The attached WAR included ANT build.xml so that you can quickly make changes (edit code and then run ant compile or ant war). Tomcat will reload the application as ANT touches web.xml. Two H2 database related shell scripts are included. init_h2_db.sh will recreate database with initial contents (3 records). list.sh will print database contents to the console.

Example screenshot from the application follows:

We will use the same example (book library) in our future articles about implementing CRUD database opeartions with different Java frameworks. Your comments are very welcome and you are welcome to take a trial for any of our Java hosting packages and develop or host Spring applications with us.

Download spring-jdbc-example.war 4MB