Spring MVC with MySQL – Sample Project For Calculating Electricity Bill

Let us see a sample electricity bill calculation project by using Spring MVC + MySQL connectivity + JDBCTemplate. Additionally, let us test the same by using MockMvc + JUnit.

MySQL Queries:

DROP DATABASE IF EXISTS test;

CREATE DATABASE test;

USE test;

DROP TABLE test.personsdetails;

CREATE TABLE personsdetails (
  id int(6) unsigned NOT NULL,
  Name varchar(50) NOT NULL,
  serviceNumber varchar(10) NOT NULL,
  consumedUnits int(11) NOT NULL,
  gender varchar(10) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

INSERT INTO test. personsdetails
(id,Name, serviceNumber, consumedUnits,gender) VALUES
(1,'personA','123-456',250,'Female');

INSERT INTO test. personsdetails
(id,Name, serviceNumber, consumedUnits,gender) VALUES
(2,'personB','246-468',350,'male');

INSERT INTO test. personsdetails
(id,Name, serviceNumber, consumedUnits,gender) VALUES
(3,'personC','123-678',150,'Female');

INSERT INTO test. personsdetails
(id,Name, serviceNumber, consumedUnits,gender) VALUES
(4,'personD','246-789',220,'male');

INSERT INTO test. personsdetails
(id,Name, serviceNumber, consumedUnits,gender) VALUES
(5,'personE','146-189',500,'female');

SELECT * FROM test.personsdetails;

Output:

For this MySQL data, let us get the electricity charges by using Spring MVC + JdbcTemplate

Implementation

Project Structure:

Maven-driven project

pom.xml

XML

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" x
         mlns: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.persons</groupId>
   <artifactId>SpringMVCElectricityBillCalculation</artifactId>
   <packaging>war</packaging>
   <properties>
      <maven.compiler.source>1.8</maven.compiler.source>
      <maven.compiler.target>1.8</maven.compiler.target>
   </properties>
   <version>0.0.1-SNAPSHOT</version>
   <name>SpringMVCElectricityBillCalculation Maven Webapp</name>
   <url>http://maven.apache.org</url>
   <dependencies>
      <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.12</version>
         <scope>test</scope>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
      <dependency>
         <groupId>org.mockito</groupId>
         <artifactId>mockito-all</artifactId>
         <version>1.9.5</version>
         <scope>test</scope>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-webmvc</artifactId>
         <version>5.1.1.RELEASE</version>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context</artifactId>
         <version>5.1.1.RELEASE</version>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-test</artifactId>
         <version>5.1.1.RELEASE</version>
         <scope>test</scope>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-jasper -->
      <dependency>
         <groupId>org.apache.tomcat</groupId>
         <artifactId>tomcat-jasper</artifactId>
         <version>9.0.12</version>
      </dependency>
      <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
      <dependency>
         <groupId>javax.servlet</groupId>
         <artifactId>servlet-api</artifactId>
         <version>3.0-alpha-1</version>
      </dependency>
      <!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
      <dependency>
         <groupId>javax.servlet</groupId>
         <artifactId>jstl</artifactId>
         <version>1.2</version>
      </dependency>
      <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.11</version>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jdbc</artifactId>
         <version>5.1.1.RELEASE</version>
      </dependency>
   </dependencies>
   <build>
      <finalName>SpringMVCElectricityBillCalculation</finalName>
      <sourceDirectory>src/main/java</sourceDirectory>
      <plugins>
         <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-surefire-plugin</artifactId>
            <version>3.0.0-M3</version>
            <configuration>
               <testFailureIgnore>true</testFailureIgnore>
               <shutdown>kill</shutdown>
               <!-- Use it if required-->
            </configuration>
         </plugin>
         <!-- This should be added to overcome Could not initialize class org.apache.maven.plugin.war.util.WebappStructureSerializer -->
         <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-war-plugin</artifactId>
            <version>3.3.2</version>
         </plugin>
      </plugins>
   </build>
</project>

Bean class

Person.java

Java

public class Person {

    private int id;
    private String name;
    private String serviceNumber;
    private int consumedUnits;
    private String gender;

    public int getConsumedUnits() {
        return consumedUnits;
    }

    public void setConsumedUnits(int consumedUnits) {
        this.consumedUnits = consumedUnits;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getServiceNumber() {
        return serviceNumber;
    }

    public void setServiceNumber(String serviceNumber) {
        this.serviceNumber = serviceNumber;
    }

}

Controller class

PersonController.java

Java

import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.SessionAttributes;
import org.springframework.web.servlet.ModelAndView;

import com.persons.beans.Person;
import com.persons.dao.PersonDao;

@Controller
@SessionAttributes("person")
public class PersonController {
    // @Autowired
    PersonDao dao;

    @Autowired
    public PersonController(PersonDao dao) {
        this.dao = dao;
    }

    @ModelAttribute("person")
    public Person getPerson() {
        return new Person();
    }

    // for searchform
    @RequestMapping("/personsearchform")
    public String searchform(Model m) {
        m.addAttribute("command", new Person());
        return "personsearchform";
    }

    // It provides check persons and calculate the amount based on consumed units
    @RequestMapping(value = "/calculateAmount", method = RequestMethod.POST)
    public ModelAndView calculateAmountForConsumedUnits(@ModelAttribute("person") Person person) {

        ModelAndView mav = null;
        Person person1 = null;
        try {
            if (person.getName() != null && person.getName() != "") {
                person1 = dao.getPersonsByName(person.getName());
            }
            if (person.getServiceNumber() != null && person.getServiceNumber() != "") {
                person1 = dao.getPersonsByServiceNumber(person.getServiceNumber());
            }
            mav = new ModelAndView("welcome");
            if (null != person1) {
                System.out.println(person1.getId() + "..." + person1.getName() + ".." + person1.getServiceNumber()
                + "..consumed units.." + person1.getConsumedUnits());
                boolean isAvailable = false;
                int consumedUnits = person1.getConsumedUnits();
                int electricityChargesNeedToPay = 0;
                if (consumedUnits <= 100) {
                    electricityChargesNeedToPay = consumedUnits * 10;
                }
                if (consumedUnits <= 200) {
                    electricityChargesNeedToPay = (100 * 10) + (consumedUnits - 100) * 15;
                }
                if (consumedUnits <= 300) {
                    electricityChargesNeedToPay = (100 * 10) + (100 * 15) + (consumedUnits - 200) * 20;
                }
                if (consumedUnits > 300) {
                    electricityChargesNeedToPay = (100 * 10) + (100 * 15) + (100 * 20) + (consumedUnits - 300) * 25;
                }


                System.out.println("electricityChargesNeedToPay.."+electricityChargesNeedToPay);
                mav.addObject("firstname", person1.getName());
                mav.addObject("servicenumber", person1.getServiceNumber());
                mav.addObject("consumedunits", person1.getConsumedUnits());
                mav.addObject("electricitycharges", electricityChargesNeedToPay);
            }
        else {
            mav.addObject("firstname", person1.getName());
            mav.addObject("electricitycharges", "Not present in the database");
            //mav.addObject("location", person.getLocation());
        }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return mav;
    }

}

DAO class

PersonDao.java

Java

package com.persons.dao;

import java.sql.SQLException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import com.persons.beans.Person;

public class PersonDao {

    JdbcTemplate template;

    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }

    public Person getPersonsByName(String personName) throws SQLException {
        String sql = "select * from personsdetails where name=?";
        return template.queryForObject(sql, new Object[] {personName},
                new BeanPropertyRowMapper<Person>(Person.class));
    }

    public Person getPersonsByServiceNumber(String serviceNumber) throws SQLException {
        String sql = "select * from personsdetails where serviceNumber=?";
        return template.queryForObject(sql, new Object[] {serviceNumber},
                new BeanPropertyRowMapper<Person>(Person.class));
    }

    public Person getPersonsById(int id) throws SQLException {
        String sql = "select * from personsdetails where id =?";
        return template.queryForObject(sql, new Object[] { id },
                new BeanPropertyRowMapper<Person>(Person.class));
    }

    public Person getPersonsByConsumedUnits(int consumedUnits) throws SQLException {
        String sql = "select * from personsdetails where consumedUnits=?";
        return template.queryForObject(sql, new Object[] { consumedUnits },
                new BeanPropertyRowMapper<Person>(Person.class));
    }

}

Let us see the JSP files

indexPageForFindingElectricityCharges.jsp

HTML

<center><B><a href="personsearchform">Electricity Charge calculation</a></B></center>

personsearchform.jsp

HTML

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                "http://www.w3.org/TR/html4/loose.dtd">
<html>
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
      <title>Electricity charge calculation</title>
   </head>
   <body>
      <h1>Electricity charge calculation</h1>
      <form:form  method="post" action="/SpringMVCElectricityBillCalculation/calculateAmount"  >
         <table >
            <tr>
               <td>Person Name : </td>
               <td>
                  <form:input path="name"/>
               </td>
            </tr>
            <tr>
               <td>  (Or) </td>
            </tr>
            <tr>
               <td>Service Number : </td>
               <td>
                  <form:input path="serviceNumber"/>
               </td>
            </tr>
            <tr>
               <td> </td>
               <td><input type="submit" value="Calculate Electricity charge" /></td>
            </tr>
         </table>
      </form:form>
   </body>
</html>

Output:

We can search either by Person Name or Service Number

SearchBy PersonName

SearchBy Service Number

We can test the same by using MockMvc + JUnit

PersonControllerTest.java

Java

import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;

import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.InjectMocks;
import org.mockito.MockitoAnnotations;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.setup.MockMvcBuilders;
import org.springframework.web.context.WebApplicationContext;

import com.persons.beans.Person;
import com.persons.controllers.PersonController;
import com.persons.dao.PersonDao;

@ContextConfiguration(locations = { "file:src/main/webapp/WEB-INF/spring-servlet.xml" })
@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
public class PersonControllerTest {

    @InjectMocks
    private PersonController personController;

    private MockMvc mockMvc;

    @Autowired
    private PersonDao dao;

    @Autowired
    WebApplicationContext webApplicationContext;

    @Before
    public void setup() {
        MockitoAnnotations.initMocks(this);
        this.mockMvc = MockMvcBuilders.standaloneSetup(personController).build();
    }

    @Test
    // 404 error thrown when coming from invalid resources
    public void testCreateSearchPersonsPageFormInvalidUser() throws Exception {
        this.mockMvc.perform(get("/"))
        .andExpect(status().isNotFound());
    }

      @Test
      // positive checks for Name
      public void testCheckConsumedUnitsByName() throws Exception {
          Person person = new Person();
          person.setName("personA");
          person = dao.getPersonsByName(person.getName());
          Assert.assertEquals(1, person.getId());
          Assert.assertEquals("123-456", person.getServiceNumber());
          Assert.assertEquals(250, person.getConsumedUnits());
      }

      @Test
      // Negative checks for Name
      public void testCheckConsumedUnitsByNameForNegative() throws Exception {
          Person person = new Person();
          person.setName("personA");
          person = dao.getPersonsByName(person.getName());
          Assert.assertNotEquals(2, person.getId());
          Assert.assertNotEquals("asd-123", person.getServiceNumber());
          Assert.assertNotEquals(1500, person.getConsumedUnits());
          Assert.assertNotEquals("Male", person.getGender());
      }

        @Test
      // Negative checks + Positive
      public void testCheckConsumedUnits() throws Exception {
          Person person = new Person();
          person.setConsumedUnits(250);
          person = dao.getPersonsByConsumedUnits(person.getConsumedUnits());
          Assert.assertNotEquals(10, person.getId());
          Assert.assertNotEquals("ST-123", person.getServiceNumber());
          Assert.assertEquals("Female", person.getGender());
      }

      @Test
      // Positive  checks for consumedunits
      public void testCheckConsumedUnitsPositive() throws Exception {
          Person person = new Person();
          person.setConsumedUnits(500);
          person = dao.getPersonsByConsumedUnits(person.getConsumedUnits());
          Assert.assertEquals(5, person.getId());
          Assert.assertEquals("146-189", person.getServiceNumber());
          Assert.assertEquals("personE", person.getName());
          Assert.assertEquals("female", person.getGender());
      }

      @Test
      // Negative + Positive checks
      public void testCheckConsumedUnitsById() throws Exception {
          Person person = new Person();
          person.setId(2);
          person = dao.getPersonsById(person.getId());
          Assert.assertEquals(350, person.getConsumedUnits());
          Assert.assertNotEquals("personZ", person.getName());
          Assert.assertNotEquals("Gen-123", person.getServiceNumber());
      }

      @Test
      // Positive checks for ID
      public void testCheckConsumedUnitsByIdPositiveCheck() throws Exception {
          Person person = new Person();
          person.setId(5);
          person = dao.getPersonsById(person.getId());
          Assert.assertEquals(500, person.getConsumedUnits());
          Assert.assertEquals("personE", person.getName());
          Assert.assertEquals("146-189", person.getServiceNumber());
          Assert.assertEquals("female", person.getGender());
      }

}

On execution of this, we will get the below output if MySQL data (actual data) matches with the expected data as given in the code. If there is something wrong, we need to check with the logic as well as the data

In this way, we can execute the projects and also write test case execution.