{"id":18018,"date":"2013-10-14T19:00:10","date_gmt":"2013-10-14T16:00:10","guid":{"rendered":"http:\/\/www.javacodegeeks.com\/?p=18018"},"modified":"2013-10-14T09:06:27","modified_gmt":"2013-10-14T06:06:27","slug":"getting-started-with-spring-jdbc-in-a-web-application","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2013\/10\/getting-started-with-spring-jdbc-in-a-web-application.html","title":{"rendered":"Getting started with Spring JDBC in a web application"},"content":{"rendered":"

I have shown you how to setup a basic Spring 3 MVC web application<\/a> in my previous article. Reusing that project setup as template, I will show you how to enhance it to work with JDBC. With this you can store and retrieve data from database. We will add a new controller and a data service through Spring so you can see how Spring injection and annotation configuration works together.<\/p>\n

A direct JDBC based application is easy to setup in comparison to a full ORM such as Hibernate. You don\u2019t need to worry AOP, TranactionManager, Entity mapping and full array of other configurations. On top of the JDK\u2019s java.jdbc<\/code> API, the Spring comes with spring-jdbc<\/code> module that can boots your productively with their well known JdbcTemplate<\/code> class. Let\u2019s explore how this can be setup and run as web application.<\/p>\n

Getting started and project setup<\/h2>\n

For demo purpose, I will use the in-memory version of H2Database as JDBC store. It\u2019s simple to use and setup. And if you decided to use their FILE or TCP based database, you would simply have to re-set the datasource, and you may continue to explore more.<\/p>\n

We will start by adding new dependencies to your existing spring-web-annotation\/pom.xml<\/code> file.<\/p>\n

<dependency>\r\n   <groupId>com.h2database<\/groupId>\r\n   <artifactId>h2<\/artifactId>\r\n   <version>1.3.163<\/version>\r\n  <\/dependency>\r\n  <dependency>\r\n   <groupId>org.springframework<\/groupId>\r\n   <artifactId>spring-jdbc<\/artifactId>\r\n   <version>3.2.4.RELEASE<\/version>\r\n  <\/dependency><\/pre>\n
With this, you will have access to Spring module classes for configuration. Find the previous src\/main\/java\/springweb\/WebApp.java<\/code> file in your existing project and add what\u2019s new compare to below:<\/p>\n
package springweb;\r\n\r\nimport org.springframework.context.annotation.Bean;\r\nimport org.springframework.context.annotation.ComponentScan;\r\nimport org.springframework.context.annotation.Configuration;\r\nimport org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;\r\nimport org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;\r\nimport org.springframework.web.servlet.config.annotation.EnableWebMvc;\r\nimport org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;\r\n\r\nimport javax.sql.DataSource;\r\n\r\npublic class WebApp extends AbstractAnnotationConfigDispatcherServletInitializer {\r\n    @Override\r\n    protected Class<?>[] getRootConfigClasses() {\r\n        return new Class<?>[]{ RootConfig.class };\r\n    }\r\n\r\n    @Override\r\n    protected Class<?>[] getServletConfigClasses() {\r\n        return new Class<?>[]{ WebAppConfig.class };\r\n    }\r\n\r\n    @Override\r\n    protected String[] getServletMappings() {\r\n        return new String[]{ \"\/\" };\r\n    }\r\n\r\n @Configuration\r\n    @EnableWebMvc\r\n    @ComponentScan(\"springweb.controller\")\r\n    public static class WebAppConfig {\r\n    }\r\n\r\n @Configuration\r\n @ComponentScan(\"springweb.data\")\r\n public static class RootConfig {\r\n  @Bean\r\n  public DataSource dataSource() {\r\n   DataSource bean = new EmbeddedDatabaseBuilder()\r\n     .setType(EmbeddedDatabaseType.H2)\r\n     .addScript(\"classpath:schema.sql\")\r\n     .build();\r\n   return bean;\r\n  }\r\n }\r\n}<\/pre>\n
What\u2019s new in here is we introduced a new RootConfig<\/code> class that will be loaded inside getRootConfigClasses()<\/code> method. The RootConfig<\/code> is just another Spring annotation based configuration that creates a new Spring context for bean definitions. We\u2019ve created a bean there that will run the in-memory database. The bean returned by the builder also conveniently implemented the javax.sql.DataSource<\/code> interface, so we can actually inject this into any data service and start using it immediately.
 
<\/div> <\/div><\/p>\n
One more cool thing about the Spring embedded database builder is that it also runs any SQL script as part of the startup! For this demo, we will create a PING<\/code> table in the src\/main\/resources\/schema.sql<\/code> file. This file is visible to Spring as in root of the CLASSPATH due to Maven standard source structure.<\/p>\n
CREATE TABLE PING (\r\n  ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,\r\n  TAG VARCHAR(1024) NOT NULL,\r\n  TS DATETIME NOT NULL\r\n);<\/pre>\n
That\u2019s the datasource setup. Now notice that I did not add this datasource Spring bean definition into existing WebAppConfig<\/code> class. The reason is that we want a separate Spring context to configure all service level beans, while reserving the WebAppConfig<\/code> for all Spring MVC related beans (such as Controller, URL mapping etc). This helps organize your bean definitions in hierarchical order of Spring contexts; with RootConfig<\/code> as parent and WebAppConfig<\/code> as child layers. This also means that all service beans in RootConfig<\/code> are automatically visible to WebAppConfig<\/code>; for the purpose of injection etc.<\/p>\n
Also notice that with separated config classes, we are able to specify two distinct packages to scan for service components; we use springweb.controller<\/code> for WebAppConfig<\/code> and springweb.data<\/code> for RootConfig<\/code>. This is important and it can save you some troubles letting Spring auto detecting your all these annotation based components.<\/p>\n
Creating Data Service<\/h2>\n
Now it\u2019s time we use the JDBC, so let\u2019s write a data service class under src\/main\/java\/springweb\/data\/PingService.java<\/code> file.<\/p>\n
package springweb.data;\r\n\r\nimport org.apache.commons.logging.Log;\r\nimport org.apache.commons.logging.LogFactory;\r\nimport org.springframework.beans.factory.annotation.Autowired;\r\nimport org.springframework.jdbc.core.JdbcTemplate;\r\nimport org.springframework.stereotype.Repository;\r\n\r\nimport javax.sql.DataSource;\r\nimport java.util.Date;\r\nimport java.util.List;\r\nimport java.util.Map;\r\n\r\n@Repository\r\npublic class PingService {\r\n    public static Log LOG = LogFactory.getLog(PingService.class);\r\n\r\n    private JdbcTemplate jdbcTemplate;\r\n\r\n    @Autowired\r\n    public void setDataSource(DataSource dataSource) {\r\n        this.jdbcTemplate = new JdbcTemplate(dataSource);\r\n    }\r\n\r\n    public void insert(String tag) {\r\n        LOG.info(\"Inserting Ping tag: \" + tag);\r\n        jdbcTemplate.update(\"INSERT INTO PING(TAG, TS) VALUES(?, ?)\", tag, new Date());\r\n    }\r\n\r\n    public List<Map<String, Object>> findAllPings() {\r\n        return jdbcTemplate.queryForList(\"SELECT * FROM PING ORDER BY TS\");\r\n    }\r\n}<\/pre>\n
The service is very straight forward. I expose two methods: one for insert and one for retrieve all Ping data. Noticed that I used @Repository<\/code> to indicate to Spring that this class is a component service that perform data service. Also note how we inject the DataSource<\/code> using a setter method, and then instantiate the JdbcTemplate<\/code> as member field. From that we can take full advantage of the Spring JDBC API for query and update.<\/p>\n
A note on logging. Spring core itself uses Apache common-logging<\/code>, so I reused that API without even explicitly declare them in my pom.xml<\/code>. If you want to see more details from log output, you should add log4j<\/code> logger implementation to the project, and it should automatically work. I will leave that as your exercise.<\/p>\n
Next we will need to write a Controller that can bring data to web UI page. We will create this under src\/main\/java\/springweb\/controller\/PingController.java<\/code> file.<\/p>\n
package springweb.controller;\r\n\r\nimport org.springframework.beans.factory.annotation.Autowired;\r\nimport org.springframework.stereotype.Controller;\r\nimport org.springframework.web.bind.annotation.PathVariable;\r\nimport org.springframework.web.bind.annotation.RequestMapping;\r\nimport org.springframework.web.bind.annotation.ResponseBody;\r\nimport springweb.data.PingService;\r\n\r\nimport java.util.List;\r\nimport java.util.Map;\r\n\r\n@Controller\r\npublic class PingController {\r\n\r\n    @Autowired\r\n    private PingService pingService;\r\n\r\n    @RequestMapping(value=\"\/ping\/{tag}\", produces=\"text\/plain\")\r\n    @ResponseBody\r\n    public String pingTag(@PathVariable(\"tag\") String tag) {\r\n        pingService.insert(tag);\r\n        return \"Ping tag '\" + tag + \"' has been inserted. \";\r\n    }\r\n\r\n    @RequestMapping(value=\"\/pings\", produces=\"text\/plain\")\r\n    @ResponseBody\r\n    public String pings() {\r\n        List<Map<String, Object>> result = pingService.findAllPings();\r\n  if (result.size() == 0)\r\n   return \"No record found.\";\r\n\r\n        StringBuilder sb = new StringBuilder();\r\n        for (Map<String, Object> row : result) {\r\n            sb.append(\"Ping\" + row).append(\"\\n\");\r\n        }\r\n        return sb.toString();\r\n    }\r\n}<\/pre>\n
In this controller, you can easily see that the Ping data is fetched and updated through our data service by injection. I\u2019ve declared and map URL \/ping\/{tag}<\/code> to insert Ping data into the database. Spring has this very nice short hand syntax annotation mapping that can extract parameter from your URL. I allow user to set a simple tag word to be insert as Ping record so we can identify the source in database.<\/p>\n
The other controller handler \/pings<\/code> URL is very straight forward; it simply returns all the records from PING table.<\/p>\n
For demo purpose, I choose to not use JSP as view, but to return plain text directly from the Controller. Spring let you do this by adding @ResponseBody<\/code> to the handler method. Notice also we can specify the content type as text\/plain<\/code> as output directly using the annotation.<\/p>\n
Testing<\/h2>\n
To see your hard labor with above, you simply need to run the Maven tomcat plugin. The previous article has shown you an command to do that. Once you restarted it, you should able to open a browser and use these URLS for testing.<\/p>\n