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"?>
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
<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>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<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>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jasper</artifactId>
<version>9.0.12</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>3.0-alpha-1</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<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" <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)
@WebAppConfigurationpublic 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.