Spring MVC with MySQL Sample Project For Calculating Electricity Bill (original) (raw)

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 `

4.0.0 com.persons SpringMVCElectricityBillCalculation war <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> 0.0.1-SNAPSHOT SpringMVCElectricityBillCalculation Maven Webapp http://maven.apache.org junit junit 4.12 test org.mockito mockito-all 1.9.5 test org.springframework spring-webmvc 5.1.1.RELEASE org.springframework spring-context 5.1.1.RELEASE org.springframework spring-test 5.1.1.RELEASE test org.apache.tomcat tomcat-jasper 9.0.12 javax.servlet servlet-api 3.0-alpha-1 javax.servlet jstl 1.2 mysql mysql-connector-java 8.0.11 org.springframework spring-jdbc 5.1.1.RELEASE SpringMVCElectricityBillCalculation src/main/java org.apache.maven.plugins maven-surefire-plugin 3.0.0-M3 true kill org.apache.maven.plugins maven-war-plugin 3.3.2

`

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 `

`

personsearchform.jsp

HTML `

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@ taglib uri="http://www.oracle.com/technetwork/java/index.html" prefix="c"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %>

Electricity charge calculation

`

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.