This tutorial shows an example on how MVC(Model, View, Controller) works in Spring 3.x. In this tutorial you will also find how JDBC works with Spring MVC. We will also see how annotation like @Autowired works in Spring. You will also see how datasource is configured in Spring. This example shows how to read the database configurations from properties file. You may also read Spring MVC and JDBC CRUD Example

Spring MVC and JDBC CRUD with zero XML


Once you finish the example and run the example you will see the below outputs in the browser

When welcome screen appears

spring mvc and jdbc example

When you click on the ‘Add New Item’ link

spring mvc and jdbc example

When you click ‘Add Item’ button without any input

spring mvc and jdbc example

spring mvc and jdbc example

Once the item gets successfully added

spring mvc and jdbc example

Well, now you have got an idea what you will get after the completion of this tutorial.

So before begin coding for this tutorial please make sure the below things you have.

Prerequisites

JDK 1.6.0_43
Tomcat Server v7.0
Eclipse Helios/Juno/Kepler
JAR dependency

spring mvc and jdbc example

spring mvc and jdbc example
Now we will start our coding for the example. First create a dynamic web project in Eclipse and put the jar files under WEB-INF/lib directory.

Create jdbc.properties file and put it under WEB-INF directory

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/cdcol
jdbc.username=root
jdbc.password=

 

Create a MySQL table

DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_name` varchar(45) COLLATE latin1_general_ci NOT NULL,
  `item_price` double unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
insert  into `items`(`item_id`,`item_name`,`item_price`) values (1,'CD',100),(2,'DVD',150),(3,'ABC',24),(4,'XYZ',25.32),(5,'CD Player',30.02);

 

The deployment descriptor file – 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" xsi:schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
    <display-name>spring-mvc</display-name>
    
    <!-- load the applicationContext.xml file when application starts up -->
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/applicationContext.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    
    <!-- spring mvc dispatcher servlet -->
    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>*.html</url-pattern>
    </servlet-mapping>
    <welcome-file-list>
        <welcome-file>welcome.html</welcome-file>
    </welcome-file-list>
</web-app>

 

Below is the WEB-INF/applicationContext.xml file

<?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:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xsi:schemalocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">
    
    <!-- get benefit of annotations -->
    <context:annotation-config></context:annotation-config>
    <!-- properties file holds database connection parameters -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer" p:location="/WEB-INF/jdbc.properties"></bean>
    <!-- configure datasource -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" p:driverclassname="${jdbc.driverClassName}" p:url="${jdbc.url}" p:username="${jdbc.username}" p:password="${jdbc.password}">
    </bean>
    <!-- use spring's jdbc template -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- declare spring DAO -->
    <bean id="itemDao" class="in.sblog.spring.dao.ItemDaoImpl"></bean>
    <!-- declare spring Service -->
    <bean id="itemService" class="in.sblog.spring.service.ItemServiceImpl">
        <property name="itemDao" ref="itemDao"></property>
    </bean>
</beans>

 

Spring’s dispatcher file – WEB-INF/dispatcher-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:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xsi:schemalocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    
    <context:component-scan base-package="in.sblog.spring.controller"></context:component-scan><
    bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix">
            <value>/pages/</value>
        </property>
        <property name="suffix">
            <value>.jsp</value>
        </property>
    </bean>
    <bean id="item" class="in.sblog.spring.model.Item"></bean>
</beans>

 

index.jsp file under Webcontent is used to show all the items from the database table

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
        pageEncoding="ISO-8859-1"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <title>Spring MVC 3.x Example</title>
    <p>${msg}</p>
    <p>
        <a href="/jcms/<%=request.getContextPath()%>/welcome/addPage.html">Add New
            Item</a>
    </p>
    <c:choose>
        <c:when test="${itemList.size() > 0}">
    <h3>List of Items</h3>    
    <table cellpadding="5" cellspacing="5">
    <thead>
        <tr>
            <th>ID</th><th>Name</th><th>Price</th>
        </tr>
    </thead>
        <tbody>
           <c:foreach var="item" items="${itemList}">
            <tr>
                <td>${item.itemId}</td>
                <td>${item.itemName}</td>
                <td>${item.itemPrice}</td>
            </tr>
           </c:foreach>
        </tbody>
            </table>
        </c:when>
        <c:otherwise>
        No Item found in the DB!
        </c:otherwise>
    </c:choose>

add.jsp file under Webcontent is used to add an item

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
        pageEncoding="ISO-8859-1"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <title>Add new item</title>
    <p><a href="/jcms/&lt;%=request.getContextPath()%&gt;/welcome.html">Back to
            Item List</a>
    </p>
    <c:if test="${not empty error}">
        ${error}
    </c:if>
    <c:if test="${not empty success}">
        ${success}
    </c:if>
    <h3>Add new item</h3>
    <form method="POST" name="login" action="&lt;%=request.getContextPath()%&gt;/welcome/add.html">
        Item Name: <input name="name" value="${name}" type="text" /> <br /><br /> Item Price: <input name="price" value="${price}" type="text" /><br /> <input value="Add Item" type="submit" />
    </form>

 

Model class

public class Item {
    private Long itemId;
    private String itemName;
    private Double itemPrice;
    public Long getItemId() {
        return itemId;
    }
    public void setItemId(Long itemId) {
        this.itemId = itemId;
    }
    public String getItemName() {
        return itemName;
    }
    public void setItemName(String itemName) {
        this.itemName = itemName;
    }
    public Double getItemPrice() {
        return itemPrice;
    }
    public void setItemPrice(Double itemPrice) {
        this.itemPrice = itemPrice;
    }
}

 

Spring Controller – WelcomeController.java. Look this file annotated with @Controller to make it a controller.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
@Controller
@RequestMapping("/welcome")
public class WelcomeController {
    @Autowired
    ItemService itemService;
    @RequestMapping(method = RequestMethod.GET)
    public String springMVC(ModelMap modelMap) {
        List items = itemService.getItems();
        modelMap.addAttribute("itemList", items);
        modelMap.addAttribute("msg", "Welcome to Spring MVC");
        return "index";
    }
    @RequestMapping(value = "/addPage", method = RequestMethod.GET)
    public String addPage() {
        return "add";
    }
    @RequestMapping(value = "/add", method = RequestMethod.POST)
    public String addItem(@RequestParam("name") String name,
            @RequestParam("price") String price, ModelMap modelMap) {
        modelMap.addAttribute("name", name);
        modelMap.addAttribute("price", price);
        if (name == null || name.trim().isEmpty()) {
            modelMap.addAttribute("error", "Item Name is required!");
        } else if (price == null || price.trim().isEmpty()) {
            modelMap.addAttribute("error", "Item Price is required!");
        } else if (Utils.isStringNumeric(price)) {
            modelMap.addAttribute("error",
                    "Item Price cannot contain character(s)!");
        } else {
            Item item = new Item();
            item.setItemName(name);
            item.setItemPrice(Double.valueOf(price));
            itemService.addItem(item);
            modelMap.addAttribute("success", "Item successfully added!");
        }
        return "add";
    }
}

 

Spring DAO

import in.sblog.spring.model.Item;
import java.util.List;
public interface ItemDao {
    List getItems();
    void addItem(Item item);
}
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
public class ItemDaoImpl implements ItemDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Override
    public List getItems() {
        String sql = "SELECT * FROM items";
        List items = new ArrayList();
        items = jdbcTemplate.query(sql, new ItemRowMapper());
        return items;
    }
    @Override
    public void addItem(Item item) {
        String sql = "INSERT INTO items(item_name,item_price) VALUES (?,?);";
        jdbcTemplate.update(sql,
                new Object[] { item.getItemName(), item.getItemPrice() });
    }
}

 

Spring Service

import java.util.List;
public interface ItemService {
    List getItems();
    void addItem(Item item);
}

 

RowMapper – represent each model value as a row

import java.util.List;
public class ItemServiceImpl implements ItemService {
    ItemDao itemDao;
    public ItemDao getItemDao() {
        return itemDao;
    }
    public void setItemDao(ItemDao itemDao) {
        this.itemDao = itemDao;
    }
    @Override
    public List getItems() {
        return itemDao.getItems();
    }
    @Override
    public void addItem(Item item) {
        itemDao.addItem(item);
    }
}

 

ResultSet extractor – populate model with ResultSet

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
public class ItemExtracter implements ResultSetExtractor {
    @Override
    public Item extractData(ResultSet resultSet) throws SQLException,
            DataAccessException {
        Item item = new Item();
        item.setItemId(resultSet.getLong(1));
        item.setItemName(resultSet.getString(2));
        item.setItemPrice(resultSet.getDouble(3));
        return item;
    }
}

 

RowMapper – represent each model value as a row

import in.sblog.spring.model.Item;
import org.springframework.jdbc.core.RowMapper;
public class ItemRowMapper implements RowMapper {
    @Override
    public Item mapRow(ResultSet resultSet, int rowNum) throws SQLException {
        ItemExtracter itemExtracter = new ItemExtracter();
        return itemExtracter.extractData(resultSet);
    }
}

 

Utils.java class which contains all the utility methods fo the application

import java.util.regex.Pattern;
public class Utils {
    
    public static boolean isStringNumeric(String str) {
        final String Digits = "(\\p{Digit}+)";
        final String HexDigits = "(\\p{XDigit}+)";
        final String Exp = "[eE][+-]?" + Digits;
        final String fpRegex = ("[\\0-\\x20]*[+-]?(NaN|Infinity|(((" + Digits
                + "(\\.)?(" + Digits + "?)(" + Exp + ")?)|(\\.(" + Digits
                + ")(" + Exp + ")?)|(((0[xX]" + HexDigits + "(\\.)?)|(0[xX]"
                + HexDigits + "?(\\.)" + HexDigits + ")" + ")[pP][+-]?"
                + Digits + "))[fFdD]?))[\\0-\\x20]*");
        if (Pattern.matches(fpRegex, str)) {
            return false;
        }
        return true;
    }
    
}

 

Thanks for your reading. Please do not forget to leave a comment.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

0 thoughts on “Spring MVC and Spring JDBC Example

Leave a Reply

Your email address will not be published. Required fields are marked *