Friday, December 24, 2010

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.
StumpleUpon DiggIt! Del.icio.us Blinklist Yahoo Furl Technorati Simpy Spurl Reddit Google I'm reading: Spring 3 MVC - JDBC Integration Tutorial ~ Twitter FaceBook

Subscribe by reader Subscribe by email Share

45 comments:

  1. Whenever i run jdbc call i get following information in my console, am i dong any wrong here??? but query is executing without any problem.
    fyi: i'm using oracle as my database.


    14 Jun 2011 11:50:47,636 INFO [STDOUT] 14 Jun 2011 11:50:47,636 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 1
    14 Jun 2011 11:50:47,636 INFO [STDOUT] 14 Jun 2011 11:50:47,636 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 2
    14 Jun 2011 11:50:47,636 INFO [STDOUT] 14 Jun 2011 11:50:47,636 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 3
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 4
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 5
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 6
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 7
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 8
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 9
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] incremented pending_acquires: 10
    14 Jun 2011 11:50:47,652 INFO [STDOUT] 14 Jun 2011 11:50:47,652 DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] com.mchange.v2.resourcepool.BasicResourcePool@6f8860 config: [start -> 3; min -> 10; max -> 100; inc -> 5; num_acq_attempts -> 30; acq_attempt_delay -> 1000; check_idle_resources_delay -> 60000; mox_resource_age -> 0; max_idle_time -> 0; excess_max_idle_time -> 0; destroy_unreturned_resc_time -> 0; expiration_enforcement_delay -> 0; break_on_acquisition_failure -> false; debug_store_checkout_exceptions -> false]

    ReplyDelete
  2. Is there any way to call oracle Stored Procedures??

    ReplyDelete
  3. this is poor web site for spring tutorial

    ReplyDelete
  4. For the jdbc-context.xml

    Getting the prefix "p" for attribute "p:driverClass" associated with an element type "bean" is not bound

    any ideas?

    ReplyDelete
  5. I am getting exceptions about application contex while running this program

    ReplyDelete
  6. can u help me out where exactly the problem the following exception occired.
    org.springframework.beans.factory.BeanDefinitionStoreException: IOException parsing XML document from ServletContext resource [/WEB-INF/applicationContext.xml]; nested exception is java.io.FileNotFoundException: Could not open ServletContext resource [/WEB-INF/applicationContext.xml]
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:349)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:310)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:143)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:178)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:149)
    at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:124)
    at org.springframework.web.context.support.XmlWebApplicationContext.loadBeanDefinitions(XmlWebApplicationContext.java:92)
    at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:123)
    at org.springframework.context.support.AbstractApplicationContext.obtainFreshBeanFactory(AbstractApplicationContext.java:423)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:353)
    at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4681)
    at org.apache.catalina.core.StandardContext$1.call(StandardContext.java:5184)
    at org.apache.catalina.core.StandardContext$1.call(StandardContext.java:5179)
    at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
    Caused by: java.io.FileNotFoundException: Could not open ServletContext resource [/WEB-INF/applicationContext.xml]
    at org.springframework.web.context.support.ServletContextResource.getInputStream(ServletContextResource.java:116)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:336)
    ... 20 more

    ReplyDelete
  7. @chandra, the exception states "njava.io.FileNotFoundException: Could not open ServletContext resource [/WEB-INF/applicationContext.xml]". Are you sure you have a web.xml in your project and it is in the correct location? It should be inside the WEB-INF folder

    ReplyDelete
  8. I have kept web.xml inside WEB-INF only but it is showing same error but i think that this exception is about contextLoaderListener class so......It's not firing even i have given the url like myapplicationname/krams/main as it is i have given.can u help me out how to perform listener tag as well in it.

    ReplyDelete
  9. @chandra, can you send me a copy of your project if possible? Or a simplified version of it. Send it to (krams915 at google dot com). I purposely spelled out my email to lessen spam from bots. Based on the exception, the web.xml isn't found. I can't verify that it's the ContextLoaderListener really.

    ReplyDelete
  10. Thanks krams,I have sent my project to ur mail.Pls rectify and forward as early as possible.

    ReplyDelete
  11. Looks good. One question though - say after I have deployed the application, I want to change the database, does Spring provide a way to do this?

    If I modify the properties, will Spring start creating new connections based on those properties?

    Thanks

    ReplyDelete
  12. @CodeMonkey, you mean switch db on-the-fly? If that's what you meant, yes it can. See the following link: http://blog.springsource.com/2007/01/23/dynamic-datasource-routing/

    ReplyDelete
  13. Excellent tutorial krams,

    Spring is an excellent framework, but really lacks in documentation.

    Thanks for being the light at the end of the tunnel.

    Regards

    ReplyDelete
  14. I am trying to use this example with sqlserver.I get the following error. If anyone would want to take a closer look at the config files or my project, I would happy to send it.

    [ERROR] [Thread-10 08:06:54] (JDBCExceptionReporter.java:logExceptions:101) Connections could not be acquired from the underlying database!
    [ERROR] [Thread-10 08:07:14] (JDBCExceptionReporter.java:logExceptions:101) Connections could not be acquired from the underlying database!
    [ERROR] [Thread-10 08:07:14] (SchemaExport.java:execute:274) schema export unsuccessful
    java.sql.SQLException: Connections could not be acquired from the underlying database!
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
    at ...

    ReplyDelete
  15. @Adel, it's probable that you've declared your database name or database driver name incorrect. Also, though this is obvious, it's possible that your sql server isn't running. "Connections could not be acquired from the underlying database!" is the error

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. I successfully ran a simple java application with the same specs. Below are the specs that I changed for my example. If you can please take a quick look.

    # database properties
    app.jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
    app.jdbc.url=jdbc:sqlserver://abc-test-efg
    app.jdbc.username=root
    app.jdbc.password=root
    #hibernate properties
    hibernate.config=/WEB-INF/hibernate.cfg.xml


    # hibernate.cfg.xml


    hibernate-configuration
    session-factory
    property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect
    property name="show_sql">false
    property name="hbm2ddl.auto">create

    ReplyDelete
  18. @Adel, your database properties seems correct. However, it's hard to determine further the cause of your app's error.

    ReplyDelete
    Replies
    1. Hi Krams,

      Even I am getting the above error. All connection attributes are proper but still not able to login...Also maven is still not installing and showing the same error:

      Delete
  19. how do i run this application from eclipse IDE?

    ReplyDelete
    Replies
    1. You have to install the Maven Eclipse plugin. I'm using STS (SpringSource Tool Suite) as my IDE which has m2e (Maven Eclipse plugin). But you should be able to install it as a separate plugin in your Eclipse IDE.

      Delete
    2. I am using STS but it shows me many errors.
      First of all, all JSP files come with errors.
      When I deploy the project as it is, it show me errors. may jar files are not there.

      Delete
  20. How do you instantiate the personService in the controller?

    @Resource(name="personService")
    private PersonService personService;

    I have made an app based in your example and the service is null inside the controller...

    thx

    ReplyDelete
  21. Thanks Kram. Excellent tutorial. I am currently using this to switch between datasources http://blog.springsource.com/2007/01/23/dynamic-datasource-routing/

    However I don't want to update the xml/java file every time I add a new client (database). Is there anyway that we can set the data source without having to change the code?

    Any pointers is greatly appreciated.

    ReplyDelete
  22. 9:37:40,866 ERROR [STDERR] SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
    19:37:40,866 ERROR [STDERR] SLF4J: Defaulting to no-operation (NOP) logger implementation
    19:37:40,866 ERROR [STDERR] SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
    19:37:40,991 INFO [DriverManagerDataSource] Loaded JDBC driver: oracle.jdbc.driver.OracleDriver
    19:37:41,038 INFO [DispatcherServlet] FrameworkServlet 'spring': initialization completed in 844 ms
    19:37:41,053 WARN [PageNotFound] No mapping found for HTTP request with URI [/NEWMVC/persons.obj] in DispatcherServlet with name 'spring'

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. kram Excelent tutorial but i want to do this in eclipse ide..how..

    ReplyDelete
    Replies
    1. I think the closest tutorial I got regarding your request can be seen here http://krams915.blogspot.com/2012/01/spring-mvc-31-jqgrid-and-spring-data_1887.html

      Depending on your background, I hope this won't be too complicated or foreign.

      Delete
    2. Mark thank for your response and i want to Implement a DAO concept in Spring MVC using JdbcTemplate in eclipse .....give me the steps ..

      Delete
  25. great article on Spring, Thanks

    ReplyDelete
  26. Hi all! I've tried to compile and run and got the folowing error:

    SEVERE: Servlet /spring-jdbc threw load() exception
    java.lang.ClassCastException: org.springframework.web.servlet.DispatcherServlet cannot be cast to javax.servlet.Servlet

    What does it mean?

    ReplyDelete
  27. can you develop a web application using spring mvc and jdbc for mysql.
    the ui should have a jsp page which shd contain a text area and a submit button. in text area sql queries should be typed and it should fetch the info from the db(mysql).

    ReplyDelete
  28. Great Article It helped me to understand how to manage a WEB app with spring I made a few changes in order to work with mysql. Thanks

    ReplyDelete
  29. Hi

    Great article.

    I get this error when trying to deploy it on jboss however.

    10:29:40,410 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/spring-jdbc]] (MSC service thread 1-1) Error configuring application listener of class org.springframework.web.context.ContextLoaderListener: java.lang.ClassNotFoundException: org.springframework.web.context.ContextLoaderListener from [Module "deployment.spring-jdbc.war:main" from Service Module Loader]


    Googled it and it says something about making modules. I see you, or other readers ahve not mentioned this / or the error. so could it be soemthing that i am doing that wrong? Please comment, if possible.

    ReplyDelete
  30. superrb example.....
    works gr8....
    i tried it with mysql changing the required details.....
    it was successfull....
    thnx for this example.....

    ReplyDelete
  31. SEVERE: Context initialization failed
    org.springframework.beans.factory.parsing.BeanDefinitionParsingException: Configuration problem: Failed to import bean definitions from relative location [jdbc-context.xml]
    Offending resource: ServletContext resource [/WEB-INF/applicationContext.xml]; nested exception is org.springframework.beans.factory.BeanDefinitionStoreException: Unexpected exception parsing XML document from ServletContext resource [/WEB-INF/jdbc-context.xml]; nested exception is java.lang.NoClassDefFoundError: org/aopalliance/intercept/MethodInterceptor
    at org.springframework.beans.factory.parsing.FailFastProblemReporter.error(FailFastProblemReporter.java:68)
    at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:85)
    at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:76)
    at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.importBeanDefinitionResource(DefaultBeanDefinitionDocumentReader.java:271)
    i have stuck last two day pls help any body

    ReplyDelete
  32. pls any body provide fast solution

    ReplyDelete
  33. This comment has been removed by the author.

    ReplyDelete
  34. @krams sir pls provide fast soluation i have waiting for ur solution

    ReplyDelete
  35. @Krams, Seems you forgot to put the personspage in the code.

    ReplyDelete
  36. Hi Krams,

    I am getting below exception.Could you pls help me how to resolve this.

    INFO: Initializing Spring root WebApplicationContext
    [ERROR] [localhost-startStop-1 09:55:16] (JDBCExceptionReporter.java:logExceptions:101) Connections could not be acquired from the underlying database!
    Feb 02, 2016 9:55:16 PM org.apache.catalina.core.ApplicationContext log

    ReplyDelete