The following page describes how to create a simple web application that performs CRUD operations against the database using Ibatis SQL Mapper and the Spring Framework and an in-memory Java database called HyperSQL. Since we are using HSQLDB there is no need to set-up an external database. This page contains all the code you need to have a working database driven application.
Note: This page was originally written on Nov 6 2009.
Background
CRUD = Create Read Update Delete.
The application described here presents the user with a simple html form that allows them to create read update and delete items in the database. The Spring JDBC template and Ibatis is used to execute SQL statements.
Requirements
- Maven 2
- Java 6
- Servlet 2.5
- Ibatis 2
- Spring 2.5
- Basic understanding of Spring and Ibatis
Procedure
Outline
- Create a new Project
- Database Setup and Configuration
- Spring Application
- Integration with Ibatis
- Tying it all Together with Spring MVC
- Web Application Configuration
- Test the application
Create a new project
mvn archetype:generate -DarchetypeArtifactId=maven-archetype-webapp
groupId: com.test
artifactId: crud
Answer the rest of the questions with defaults “Just hit the enter key”,
cd to the project base folder.
cd crud
Since this is a web project maven2 does not create the java source folder.
Create missing folders now.
mkdir -p src/main/java/com/test
mkdir -p src/main/resources/com/test
mkdir -p src/main/webapp/WEB-INF/jsp
Modify the project Configuration
The pom.xml file should look something like this.
vi pom.xml
<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>com.test</groupId>
<artifactId>crud</artifactId>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>
<name>crud Maven Webapp</name>
<url>http://maven.apache.org</url>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20090211</version>
</dependency>
<dependency>
<groupId>org.apache.ibatis</groupId>
<artifactId>ibatis-sqlmap</artifactId>
<version>2.3.4.726</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring</artifactId>
<version>2.5.6</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>2.5.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.14</version>
</dependency>
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>1.8.0.10</version>
</dependency>
<dependency>
<groupId>org.apache.ddlutils</groupId>
<artifactId>ddlutils</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jsp-api</artifactId>
<version>2.0</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<finalName>crud</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.0.2</version>
<configuration>
<source>1.6</source>
<target>1.6</target>
</configuration>
</plugin>
<plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>7.0.0.v20091005</version>
<configuration>
<scanIntervalSeconds>2</scanIntervalSeconds>
<webAppConfig>
<contextPath>/crud</contextPath>
</webAppConfig>
<stopKey>s</stopKey>
</configuration>
</plugin>
</plugins>
</build>
</project>
Database Setup and Configuration
The following configuration file is used by Apache DDLUtils to generate apply the schema changes each time the application restarts. Changes are made incrementally and every attempt is made to preserve the data. For more information about DDLUtils please see my other tutorial.
vi src/main/resources/ddl.xml
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="testdb">
<table name="item">
<column name="item_id" type="INTEGER" required="true"
primaryKey="true" autoIncrement="true" />
<column name="category_id" type="INTEGER" />
<column name="oem_id" type="VARCHAR" size="50"/>
<column name="item_x" type="VARCHAR" size="100" />
<column name="special_f" type="CHAR" size="1" />
<column name="description" type="VARCHAR" size="1000" />
<column name="color" type="VARCHAR" size="20" />
<column name="image_url" type="VARCHAR" size="100" />
<column name="price" type="DECIMAL" size="15" />
<column name="length" type="DECIMAL" size="10" />
<column name="width" type="DECIMAL" size="10" />
<column name="height" type="DECIMAL" size="10" />
<column name="weight" type="DECIMAL" size="10" />
<column name="stock_qty" type="DECIMAL" size="10" />
<index name="item_item_x">
<index-column name="item_x" />
</index>
</table>
</database>
Since we are working with HSQLDB, it is critical that we gracefully shut-down the database when the application stops. Otherwise the data that was cached in memory will be lost.
We will use a “ServletContextListener” to listen for the application shut-down event. This will allow us to execute code to graceful shut-down the HSQLDB database.
vi src/main/java/com/test/DBLifecycleContextListener.java
package com.test;
import java.io.InputStreamReader;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
/**
* This listener initializes or gracefully shuts down the database based on
* events from the web application.
*/
public class DBLifecycleContextListener implements ServletContextListener {
private DataSource dataSource = null;
private WebApplicationContext springContext = null;
public void contextDestroyed(ServletContextEvent event) {
SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
template.update("SHUTDOWN;");
System.out.println("Database Successfully Shutdown.");
}
public void contextInitialized(ServletContextEvent event) {
springContext = WebApplicationContextUtils
.getWebApplicationContext(event.getServletContext());
dataSource = (DataSource) springContext.getBean("dataSource");
Platform platform = PlatformFactory
.createNewPlatformInstance(dataSource);
Database database = new DatabaseIO().read(new InputStreamReader(
getClass().getResourceAsStream("/ddl.xml")));
platform.alterTables(database, false);
}
}
Spring Application
The Spring application consists of a
- Item – POJO that represents the individual item
- ItemModel – Service Interface
- ItemModelImpl – Implementation of the Service
- ItemDataManager – DataManager Interface
- ItemDataManagerImpl – DataManager Implementation
The majority of the configuration is Annotation Based. For more information on annotation based spring configuration please see my other tutorial “using annotations in the spring framework“. Also checkout the following: “hello world spring mvc with annotations“.
Item Bean
vi src/main/java/com/test/Item.java
package com.test;
import java.math.BigDecimal;
/**
* Represents an item in inventory or in a shopping cart.
*/
public class Item {
private Integer itemId;
private String oemId;
private String name;
private String description;
private String imageURL;
private String color;
private BigDecimal price;
private BigDecimal length;
private BigDecimal width;
private BigDecimal height;
/**
* Weight in grams
*/
private BigDecimal weight;
/**
* Quantity in stock.
*/
private Integer stockQty;
public Item() {
super();
}
public Item(Integer id, String name) {
this.name = name;
this.itemId = id;
}
public Integer getItemId() {
return itemId;
}
public void setItemId(Integer itemId) {
this.itemId = itemId;
}
public String getOemId() {
return oemId;
}
public void setOemId(String oemId) {
this.oemId = oemId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public String getColor() {
return color;
}
public void setColor(String color) {
this.color = color;
}
public String getImageURL() {
return imageURL;
}
public void setImageURL(String imageURL) {
this.imageURL = imageURL;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public BigDecimal getLength() {
return length;
}
public void setLength(BigDecimal length) {
this.length = length;
}
public BigDecimal getWidth() {
return width;
}
public void setWidth(BigDecimal width) {
this.width = width;
}
public BigDecimal getHeight() {
return height;
}
public void setHeight(BigDecimal height) {
this.height = height;
}
public BigDecimal getWeight() {
return weight;
}
public void setWeight(BigDecimal weight) {
this.weight = weight;
}
public Integer getStockQty() {
return stockQty;
}
public void setStockQty(Integer stockQty) {
this.stockQty = stockQty;
}
}
Spring Model classes
The following interface and implementation classes are currently being used a pass-thru to the data manager. Its not adding any value to the application but once we start using Spring Transactions we will be glad we did it this way.
vi src/main/java/com/test/ItemModel.java
package com.test;
import java.util.List;
public interface ItemModel {
/**
* This is a pass-thru method that just calls the DataManager.
*/
public Item getItemInfo(Integer itemId);
public Item createItem(Item item);
public void updateItem(Item item);
public void deleteItem(Integer itemId);
/**
* This is a pass-thru method that just calls the DataManager.
*/
public List<Item> getAllItems(int skipResults, int maxItems);
/**
* This is a pass-thru method that just calls the DataManager.
*/
public int getItemCount();
public void deleteItemImage(Integer itemId);
}
The following is the implementation class.
vi src/main/java/com/test/ItemModelImpl.java
package com.test;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.stereotype.Service;
@Service
public class ItemModelImpl implements ItemModel {
private ItemDataManager itemDataManager;
@Override
public Item createItem(Item item) {
return itemDataManager.createItem(item);
}
@Override
public void deleteItem(Integer itemId) {
itemDataManager.deleteItem(itemId);
}
@Override
public void deleteItemImage(Integer itemId) {
itemDataManager.deleteItemImage(itemId);
}
@Override
public List<Item> getAllItems(int skipResults, int maxItems) {
return itemDataManager.getAllItems(skipResults, maxItems);
}
@Override
public int getItemCount() {
return itemDataManager.getItemCount();
}
@Override
public Item getItemInfo(Integer itemId) {
return itemDataManager.getItemInfo(itemId);
}
@Override
public void updateItem(Item item) {
itemDataManager.updateItem(item);
}
@Required
@Autowired
public void setItemDataManager(ItemDataManager itemDataManager) {
this.itemDataManager = itemDataManager;
}
public ItemDataManager getItemDataManager() {
return itemDataManager;
}
}
Spring Data Managers
Data Managers isolate the Model from the implementation of the data store.
vi src/main/java/com/test/ItemDataManager.java
package com.test;
import java.util.List;
public interface ItemDataManager {
public Item getItemInfo(Integer itemId);
public Item createItem(Item item);
public void updateItem(Item item);
public void deleteItem(Integer itemId);
List<Item> getAllItems(int skipResults, int maxItems);
int getItemCount();
public void deleteItemImage(Integer itemId);
}
The following is the data manager implementation class.
vi src/main/java/com/test/ItemDataManagerImpl.java
package com.test;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.orm.ibatis.SqlMapClientTemplate;
import org.springframework.stereotype.Repository;
@Repository("itemDataManager")
public class ItemDataManagerImpl implements ItemDataManager {
private final Log logger = LogFactory.getLog(ItemDataManagerImpl.class);
private SqlMapClientTemplate sqlMapClientTemplate;
public SqlMapClientTemplate getSqlMapClientTemplate() {
return sqlMapClientTemplate;
}
@Autowired
@Required
public void setSqlMapClientTemplate(SqlMapClientTemplate sqlMapClientTemplate) {
this.sqlMapClientTemplate = sqlMapClientTemplate;
}
@Override
public Item getItemInfo(Integer itemId) {
return (Item)getSqlMapClientTemplate().queryForObject("getItemInfo", itemId);
}
@Override
public Item createItem(Item item) {
Integer id = (Integer) getSqlMapClientTemplate().insert("itemInsert", item);
item.setItemId(id);
return item;
}
@Override
public void deleteItem(Integer itemId) {
if(itemId != null) {
getSqlMapClientTemplate().delete("deleteItem", itemId);
}
}
@Override
public void deleteItemImage(Integer itemId) {
if(itemId != null) {
getSqlMapClientTemplate().delete("deleteItemImage", itemId);
}
}
@Override
public void updateItem(Item item) {
getSqlMapClientTemplate().update("updateItem", item);
}
@Override
public int getItemCount() {
return 0;
}
@Override
public List<Item> getAllItems(int skipResults, int maxItems) {
List<Item> list = null;
list = getSqlMapClientTemplate().queryForList("allItems", null, skipResults, maxItems);
return list;
}
}
Spring Configuration
Most of the spring configuration was done using Java Annotations, therefore this file is not too large.
vi src/main/resources/applicationContext.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-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<context:annotation-config/>
<context:component-scan base-package="com.test"/>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
<property name="url" value="jdbc:hsqldb:file:src/main/webapp/WEB-INF/db/testdb"/>
<property name="username" value="sa"/>
<property name="password" value=""/>
</bean>
<!-- Used for Ibatis -->
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation" value="/WEB-INF/mapper-config.xml"/>
<property name="dataSource" ref="dataSource"/>
<property name="useTransactionAwareDataSource" value="true"/>
</bean>
<bean id="sqlMapClientTemplate" class="org.springframework.orm.ibatis.SqlMapClientTemplate">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>
</beans>
Configure simple logging to the console.
vi src/main/resources/log4j.properties
# Set root logger level to DEBUG and its only appender to A1.
log4j.rootLogger=INFO, A1
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%d %-5p %c - %m%n
Integration with IBATIS
The following needs to be defined in the spring configuration. The SQLMapClientTemplate is the primary class that will be used by our data-mangers to query the database thru SQL statements and stored procedure calls. It needs a reference to sqlMapClient that we just created.
The following is the ibatis main configuration file. Since we are using spring framework there is not much configuration that you need to put in here. All that is required is that ibatis knows where the xml files for all the Data Managers are located.
For more information on ibatis please see my other tutorial “ibatis 2.3 spring 2.5 hello world“
vi src/main/webapp/WEB-INF/mapper-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<sqlMap resource="com/test/ItemDataManager.xml"/>
</sqlMapConfig>
vi src/main/resources/com/test/ItemDataManager.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Item">
<typeAlias alias="Item" type="com.test.Item" />
<select id="allItems" resultClass="Item">
select item_id as itemId,
oem_id as oemId,
item_x as name,
description as description,
color as color,
image_url as imageURL,
price as price,
length as length,
width as width,
height as height,
weight as weight,
stock_qty as stockQty
from item;
</select>
<select id="getItemInfo" parameterClass="int" resultClass="Item">
select item_id as itemId,
oem_id as oemId,
item_x as name,
description as description,
color as color,
image_url as imageURL,
price as price,
length as length,
width as width,
height as height,
weight as weight,
stock_qty as stockQty
from item where item_id = #id#;
</select>
<insert id="itemInsert" parameterClass="Item">
insert into item (oem_id,
item_x,
description,
color,
image_url,
price,
length,
width,
height,
weight,
stock_qty)
values ( #oemId#, #name#, #description#, #color#,
#imageURL#, #price#, #length#, #width#,
#height#, #weight#, #stockQty# );
<selectKey resultClass="int" >
<!-- HSQLDB -->
CALL IDENTITY();
<!-- MySQL -->
<!-- SELECT LAST_INSERT_ID(); -->
</selectKey>
</insert>
<update id="updateItem" parameterClass="Item">
update item set oem_id = #oemId#, item_x = #name#, description=#description#,
color = #color#, image_url = #imageURL#, price = #price#, length = #length#,
width = #width#, height = #height#, weight = #weight#, stock_qty = #stockQty#
where item_id = #itemId#;
</update>
<delete id="deleteItem" parameterClass="java.lang.Integer">
delete from item where item_id = #itemId#;
</delete>
</sqlMap>
Tying it all Together with Spring MVC
The following is the controller class that handles the http form submissions.
vi src/main/java/com/test/ItemController.java
package com.test;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
@Controller
@RequestMapping("/item")
public class ItemController {
private ItemModel itemModel;
@RequestMapping(method = RequestMethod.GET)
public String itemInfo(HttpServletRequest request) {
List <Item> items = itemModel.getAllItems(0, 100);
request.setAttribute("items", items);
return "index";
}
@RequestMapping(method = RequestMethod.POST)
public String createItem(HttpServletRequest request,
HttpServletResponse response) {
if ("C".equals(request.getParameter("operation"))) {
System.out.println("create");
Item item = new Item();
item.setName("item created: " + new Date());
itemModel.createItem(item);
String message = "newly created item: " + item.getItemId();
System.out.println(message);
request.setAttribute("message", message);
} else if ("R".equals(request.getParameter("operation"))) {
// left for the reader to implement
} else if ("U".equals(request.getParameter("operation"))) {
// left for the reader to implement
} else if ("D".equals(request.getParameter("operation"))) {
Integer id = new Integer(request.getParameter("id"));
itemModel.deleteItem(id);
String message = "item deleted: " + id;
System.out.println(message);
request.setAttribute("message", message);
}
request.setAttribute("items", itemModel.getAllItems(0, 100));
return "index";
}
public ItemModel getItemModel() {
return itemModel;
}
@Required
@Autowired
public void setItemModel(ItemModel itemModel) {
this.itemModel = itemModel;
}
}
Create the Test HTML Form
The following form issues a POST request to the spring MVC controller. The form is missing the “action” attribute because by default the form posts back to the originating URL. In this case the originating URL is “/apps/item”
vi src/main/webapp/WEB-INF/jsp/index.jsp
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>DB Parameters</title>
<meta http-equiv="Content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="Content-Language" content="en-us" />
</head>
<body>
<h3><c:out value="${message}"/></h3>
<h3>Items:</h3>
<table border="1">
<thead>
<td>Id</td>
<td>Name</td>
</thead>
<tbody>
<c:forEach items="${items}" var="item" >
<tr>
<td><c:out value="${item.itemId}"/></td>
<td><c:out value="${item.name}"/></td>
</tr>
</c:forEach>
</tbody>
</table>
<form method="post">
<label>Operation: </label>
<select name="operation">
<option value="C">Create</option>
<option value="R">Read</option>
<option value="U">Update</option>
<option value="D">Delete</option>
</select>
<br/>
<label>ID: </label><input type="text" name="id"/><br/>
<input type="submit" value="submit"/><br/>
</form>
This page saves data to an HyperSQL database located in the src/main/webapp/WEB-INF/db folder.
</body>
</html>
Web Application Configuration
The ordering of the listeners are important. You want to make sure that the spring application context is initialized before the DBLifecycleListener.
vi src/main/webapp/WEB-INF/web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
id="WebApp_ID" version="2.5">
<display-name>Archetype Created Web Application</display-name>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<listener>
<listener-class>com.test.DBLifecycleContextListener</listener-class>
</listener>
<servlet>
<servlet-name>spring</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>spring</servlet-name>
<url-pattern>/app/*</url-pattern>
</servlet-mapping>
</web-app>
vi src/main/webapp/WEB-INF/spring-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-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<context:annotation-config />
<context:component-scan base-package="com.test" />
<bean
class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping" />
<bean
class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" />
<bean id="viewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/jsp/" />
<property name="suffix" value=".jsp" />
</bean>
</beans>
Start Jetty Servlet Engine
mvn clean compile jetty:run
Test the application
Navigate to: http://localhost:8080/crud/app/item and enter some data
Test the following as well.
- then shutting down jetty – CTRL-C
- verify the data survived in the src/main/webapp/WEB-INF/db directory.
Creating a WAR file
If you want to run the application in another environment you can create a war file and drop it into a server of your choice. This method will also package up any runtime dependencies and put them in the /WEB-INF/lib folder of the WAR file.
mvn clean compile package
The war file should appear in the following location…
target/crud-1.0-SNAPSHOT.war
Next Steps
Appendix
If you don’t like using IBatis you can replace the Implementation of the ItemDataManager with the following. It is a drop in replacement. Just remember to update your spring application configuration. (annotation, configuration etc..)
vi src/main/java/com/test/SimpleItemDataManagerImpl.java
package com.test;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository("simpleItemDataManager")
public class SimpleItemDataManagerImpl implements ItemDataManager {
private final Log logger = LogFactory.getLog(SimpleItemDataManagerImpl.class);
private SimpleJdbcTemplate jdbcTemplate;
private SimpleJdbcInsert insertItem;
@Autowired
@Required
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new SimpleJdbcTemplate(dataSource);
insertItem = new SimpleJdbcInsert(dataSource).withTableName("item")
.usingGeneratedKeyColumns("item_id");
}
@Override
public Item getItemInfo(Integer itemId) {
logger.info("using JDBCTemplate");
return jdbcTemplate.queryForObject( "select item_id as itemId, " +
" oem_id as oemId, item_x as name, description as description, " +
" color as color, image_url as imageURL, price as price, " +
" length as length, width as width, height as height, " +
" weight as weight, stock_qty as stockQty " +
"from item where item_id = ?", new ParameterizedRowMapper<Item>() {
@Override
public Item mapRow(ResultSet rs, int rowNum)
throws SQLException {
Item item = new Item();
item.setItemId(rs.getInt(1));
item.setOemId(rs.getString(2));
item.setName(rs.getString(3));
item.setDescription(rs.getString(4));
item.setColor(rs.getString(5));
item.setImageURL(rs.getString(6));
item.setPrice(rs.getBigDecimal(7));
item.setLength(rs.getBigDecimal(8));
item.setWidth(rs.getBigDecimal(9));
item.setHeight(rs.getBigDecimal(10));
item.setWeight(rs.getBigDecimal(11));
item.setStockQty(rs.getInt(12));
return item;
}
}, itemId);
}
@Override
public Item createItem(Item item) {
logger.info("using JDBCTemplate");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("oem_id", item.getOemId());
parameters.put("item_x", item.getName());
parameters.put("description", item.getDescription());
parameters.put("color", item.getColor());
parameters.put("image_url", item.getImageURL());
parameters.put("price", item.getPrice());
parameters.put("length", item.getLength());
parameters.put("width", item.getWidth());
parameters.put("height", item.getHeight());
parameters.put("weight", item.getWeight());
parameters.put("stock_qty", item.getStockQty());
Number newId = insertItem.executeAndReturnKey(parameters);
item.setItemId(newId.intValue());
return item;
}
@Override
public void deleteItem(Integer itemId) {
logger.info("using JDBCTemplate");
if(itemId != null) {
jdbcTemplate.update("delete from item where item_id = ?;", itemId);
}
}
@Override
public void deleteItemImage(Integer itemId) {
if(itemId != null) {
// not implemented.
}
}
@Override
public void updateItem(Item i) {
logger.info("using JDBCTemplate");
jdbcTemplate.update(
"update item set oem_id = ?, item_x = ?, description=?, "
+ "color = ?, image_url = ?, price = ?, length = ?, "
+ "width = ?, height = ?, weight = ?, stock_qty = ? "
+ "where item_id = ? ", i.getOemId(), i.getName(),
i.getDescription(), i.getColor(), i.getImageURL(),
i.getPrice(), i.getLength(), i.getWidth(), i.getHeight(),
i.getWeight(), i.getStockQty(), i.getItemId());
}
@Override
public int getItemCount() {
logger.info("using JDBCTemplate");
return jdbcTemplate.queryForInt("select count(*) from item");
}
@Override
public List<Item> getAllItems(int skipResults, int maxItems) {
logger.info("using JDBCTemplate");
return this.jdbcTemplate.<Item>query (
"select item_id as itemId, " +
"oem_id as oemId, item_x as name, description as description, " +
"color as color, image_url as imageURL, price as price, " +
"length as length, width as width, height as height, " +
"weight as weight, stock_qty as stockQty " +
"from item;",
new ParameterizedRowMapper<Item>() {
public Item mapRow(ResultSet rs, int rowNum) throws SQLException {
Item item = new Item();
item.setItemId(rs.getInt(1));
item.setOemId(rs.getString(2));
item.setName(rs.getString(3));
item.setDescription(rs.getString(4));
item.setColor(rs.getString(5));
item.setImageURL(rs.getString(6));
item.setPrice(rs.getBigDecimal(7));
item.setLength(rs.getBigDecimal(8));
item.setWidth(rs.getBigDecimal(9));
item.setHeight(rs.getBigDecimal(10));
item.setWeight(rs.getBigDecimal(11));
item.setStockQty(rs.getInt(12));
return item;
}
});
}
}
