Spring MVC with MySQL - Sample Project For Calculating Electricity Bill

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;
    }

}

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;
    }

}

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));
    }

}

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());
      }     
        
}