Skip to main content

Sample Crud Using Spring MVC + JDBC Template + PostgreSQL + Primefaces Part 02



In this lesson we are going to talk about how to create entities, managers, services and the beans

first we need to create our entity classes, in the previous session we talk about we have 3 tables COUNTRY, CITY and EMPLOYEE. so we need to create entity classes for those.

Country.java

package com.test.entity;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/*Country Entity*/
public class Country implements Serializable {
 private static final long serialVersionUID = 1L;

 private Integer countryId;
 private String name;
 private String code;
 private Date addedDate;
 private Short statCode;
 private List<City> cities = new ArrayList<City>(0);
 private List<Employee> employees = new ArrayList<Employee>(0);

 // Getters & Setters
 public Integer getCountryId() {
  return countryId;
 }

 public void setCountryId(Integer countryId) {
  this.countryId = countryId;
 }

 public String getName() {
  return name;
 }

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

 public String getCode() {
  return code;
 }

 public void setCode(String code) {
  this.code = code;
 }

 public Date getAddedDate() {
  return addedDate;
 }

 public void setAddedDate(Date addedDate) {
  this.addedDate = addedDate;
 }

 public Short getStatCode() {
  return statCode;
 }

 public void setStatCode(Short statCode) {
  this.statCode = statCode;
 }

 public List<City> getCities() {
  return cities;
 }

 public void setCities(List<City> cities) {
  this.cities = cities;
 }

 public List<Employee> getEmployees() {
  return employees;
 }

 public void setEmployees(List<Employee> employees) {
  this.employees = employees;
 }

}


City.java

package com.test.entity;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/*City Entity*/
public class City implements Serializable {
 private static final long serialVersionUID = 1L;

 private Integer cityId;
 private String name;
 private Date addedDate;
 private Short statCode;
 private Country country;
 private List<Employee> employees = new ArrayList<Employee>(0);

 // Getters & Setters
 public Integer getCityId() {
  return cityId;
 }

 public void setCityId(Integer cityId) {
  this.cityId = cityId;
 }

 public String getName() {
  return name;
 }

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

 public Date getAddedDate() {
  return addedDate;
 }

 public void setAddedDate(Date addedDate) {
  this.addedDate = addedDate;
 }

 public Short getStatCode() {
  return statCode;
 }

 public void setStatCode(Short statCode) {
  this.statCode = statCode;
 }

 public Country getCountry() {
  return country;
 }

 public void setCountry(Country country) {
  this.country = country;
 }

 public List<Employee> getEmployees() {
  return employees;
 }

 public void setEmployees(List<Employee> employees) {
  this.employees = employees;
 }

}


Employee.java

package com.test.entity;

import java.io.Serializable;
import java.util.Date;

/*Employee Entity*/
public class Employee implements Serializable {
 private static final long serialVersionUID = 1L;

 private Integer empId;
 private String empName;
 private Integer age;
 private String email;
 private String mobileNo;
 private String nic;
 private Date dob;
 private String gender;
 private String maritalState;
 private Short statCode;
 private Date addedDate;
 private Country country;
 private City city;

 // Getters & Setters
 public Integer getEmpId() {
  return empId;
 }

 public void setEmpId(Integer empId) {
  this.empId = empId;
 }

 public String getEmpName() {
  return empName;
 }

 public void setEmpName(String empName) {
  this.empName = empName;
 }

 public Integer getAge() {
  return age;
 }

 public void setAge(Integer age) {
  this.age = age;
 }

 public String getEmail() {
  return email;
 }

 public void setEmail(String email) {
  this.email = email;
 }

 public String getMobileNo() {
  return mobileNo;
 }

 public void setMobileNo(String mobileNo) {
  this.mobileNo = mobileNo;
 }

 public String getNic() {
  return nic;
 }

 public void setNic(String nic) {
  this.nic = nic;
 }

 public Date getDob() {
  return dob;
 }

 public void setDob(Date dob) {
  this.dob = dob;
 }

 public String getGender() {
  return gender;
 }

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

 public String getMaritalState() {
  return maritalState;
 }

 public void setMaritalState(String maritalState) {
  this.maritalState = maritalState;
 }

 public Short getStatCode() {
  return statCode;
 }

 public void setStatCode(Short statCode) {
  this.statCode = statCode;
 }

 public Date getAddedDate() {
  return addedDate;
 }

 public void setAddedDate(Date addedDate) {
  this.addedDate = addedDate;
 }

 public Country getCountry() {
  return country;
 }

 public void setCountry(Country country) {
  this.country = country;
 }

 public City getCity() {
  return city;
 }

 public void setCity(City city) {
  this.city = city;
 }

}


Implementing Mangers using DAO pattern:

EventManager.java

package com.test.event.manager;

import java.util.List;
import java.util.Map;

public interface EventManager {

 public abstract <K, V, E> Map<K, V> create(E paramE) throws Exception;

 public abstract <K, V, E> Map<K, V> update(E paramE) throws Exception;

 public abstract <K, V, E> Map<K, V> delete(List<E> paramList) throws Exception;

 public abstract <E, K1, K2, K3, K4> List<E> getList(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception;

 public abstract <V, K1, K2, K3, K4> V getObject(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception;
}

CountryManager.java

package com.test.persistance;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.test.entity.Country;
import com.test.event.manager.EventManager;

@Repository("countryManager")
public class CountryManager implements EventManager {

 static final Logger logger = Logger.getLogger(CountryManager.class);
 private DataSource dataSource;

 public CountryManager() {

 }

 public DataSource getDataSource() {
  return dataSource;
 }

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> create(E paramE) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxn = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxn);

   Integer nextValue = nextRowValue();
   logger.debug(">>----- nextValue-->" + nextValue);

   Country country = (Country) paramE;

   String SQL = "INSERT INTO  country(country_id, name, code, added_date, state_code) VALUES (:country_id, :name, :code, :added_date, :state_code)";

   Map<String, Object> namedParameters = new HashMap<String, Object>();
   namedParameters.put("country_id", nextValue);
   namedParameters.put("name", country.getName());
   namedParameters.put("code", country.getCode());
   namedParameters.put("added_date", country.getAddedDate());
   namedParameters.put("state_code", Short.valueOf((short) 1));

   NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
   namedParameterJdbcTemplate.update(SQL, namedParameters);
   transactionManager.commit(status);

   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Create Operation Success");

  } catch (DuplicateKeyException e1) {
   logger.error("Error : DuplicateKeyException --> " + e1);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Create Operation Fail, Country Already Exist");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  } catch (Exception e) {
   logger.error("Error: create(E) -- > " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Create Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }

  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> update(E paramE) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxn = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxn);

   Country country = (Country) paramE;

   String SQL = "UPDATE country SET name=:name, code=:code WHERE country_id=:country_id";

   Map<String, Object> namedParameters = new HashMap<String, Object>();
   namedParameters.put("name", country.getName());
   namedParameters.put("code", country.getCode());
   namedParameters.put("country_id", country.getCountryId());

   NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
   namedParameterJdbcTemplate.update(SQL, namedParameters);
   transactionManager.commit(status);

   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Modify Operation Success");

  } catch (DuplicateKeyException e1) {
   logger.error("Error : DuplicateKeyException --> " + e1);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Modify Operation Fail, Country Already Exist");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  } catch (Exception e) {
   logger.error("Error: modify(E) -- > " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Modify Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }

  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> delete(List<E> paramList) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxnDf = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   logger.info("<--Execute Delete Operation -->");
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxnDf);
   List<Country> empList = (List<Country>) paramList;

   for (Country country : empList) {
    String SQL = " UPDATE country SET state_code=:state_code WHERE country_id=:country_id";

    Map<String, Object> namedParameters = new HashMap<String, Object>();

    namedParameters.put("country_id", country.getCountryId());
    namedParameters.put("state_code", Short.valueOf((short) 10));

    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
    namedParameterJdbcTemplate.update(SQL, namedParameters);
   }
   transactionManager.commit(status);
   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Delete Operation Success");
  } catch (Exception e) {
   logger.error("Error : Delete --> " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Delete Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }
  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <E, K1, K2, K3, K4> List<E> getList(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {
  List<Country> dataList = null;
  try {
   logger.info("<--Excute getList(K1, K2, K3)-->");

   String param = (String) paramK1;
   String action = (String) paramK2;
   Integer countryId = (Integer) paramK3;

   logger.info(">>----- param ----> " + param);
   logger.info(">>----- action ---> " + action);
   logger.info(">>----- countryId --> " + countryId);

   String SQL = "SELECT * FROM  country_get_data(?,?,?)";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

   dataList = jdbcTemplate.query(SQL, new Object[] { param, action, countryId }, new RowMapper<Country>() {
    public Country mapRow(ResultSet rs, int rownumber) throws SQLException {
     Country country = new Country();
     country.setCountryId(Integer.valueOf(rs.getInt("country_id")));
     country.setName(rs.getString("name"));
     country.setCode(rs.getString("code"));
     country.setAddedDate(rs.getTimestamp("added_date"));
     country.setStatCode(Short.valueOf(rs.getShort("state_code")));

     return country;
    }
   });
  } catch (Exception e) {
   logger.error("Error : getList(K1, K2, K3)--> " + e);
  }
  return (List<E>) dataList;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <V, K1, K2, K3, K4> V getObject(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {
  Country country = null;
  try {
   logger.info("<--Excute getObject(K1, K2, K3)-->");
   String param = (String) paramK1;
   String action = (String) paramK2;
   Integer countryId = (Integer) paramK3;

   logger.info(">>----- param ----> " + param);
   logger.info(">>----- action ---> " + action);
   logger.info(">>----- countryId --> " + countryId);

   String SQL = "SELECT * FROM  country_get_data(?,?,?)";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

   country = jdbcTemplate.queryForObject(SQL, new Object[] { param, action, countryId }, new RowMapper<Country>() {
    public Country mapRow(ResultSet rs, int rownumber) throws SQLException {
     Country country = new Country();
     country.setCountryId(Integer.valueOf(rs.getInt("country_id")));
     country.setName(rs.getString("name"));
     country.setCode(rs.getString("code"));
     country.setAddedDate(rs.getTimestamp("added_date"));
     country.setStatCode(Short.valueOf(rs.getShort("state_code")));

     return country;
    }
   });
  } catch (Exception e) {
   logger.error("Error : getObject(K1, K2, K3)--> " + e);
  }
  return (V) country;
 }

 // Get Next Row Value
 private int nextRowValue() throws Exception {
  logger.info("Execute nextRowValue");
  JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
  String SQL = "SELECT * FROM  country_next_row_val()";
  Integer maxNo = (Integer) jdbcTemplate.queryForObject(SQL, Integer.class);

  return maxNo.intValue();
 }

}


CityManager.java

package com.test.persistance;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.test.entity.City;
import com.test.entity.Country;
import com.test.event.manager.EventManager;

@Repository("cityManager")
public class CityManager implements EventManager {

 static final Logger logger = Logger.getLogger(CityManager.class);
 private DataSource dataSource;

 public CityManager() {

 }

 public DataSource getDataSource() {
  return dataSource;
 }

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> create(E paramE) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxn = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxn);

   Integer nextValue = nextRowValue();
   logger.debug(">>----- nextValue-->" + nextValue);

   City city = (City) paramE;

   String SQL = "INSERT INTO  city(city_id, name, country_id, added_date, state_code) VALUES (:city_id, :name, :country_id, :added_date, :state_code)";

   Map<String, Object> namedParameters = new HashMap<String, Object>();
   namedParameters.put("city_id", nextValue);
   namedParameters.put("name", city.getName());
   namedParameters.put("country_id", city.getCountry().getCountryId());
   namedParameters.put("added_date", city.getAddedDate());
   namedParameters.put("state_code", Short.valueOf((short) 1));

   NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
   namedParameterJdbcTemplate.update(SQL, namedParameters);
   transactionManager.commit(status);

   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Create Operation Success");

  } catch (DuplicateKeyException e1) {
   logger.error("Error : DuplicateKeyException --> " + e1);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Create Operation Fail, City Already Exist");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  } catch (Exception e) {
   logger.error("Error: create(E) -- > " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Create Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }

  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> update(E paramE) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxn = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxn);

   City city = (City) paramE;

   String SQL = "UPDATE city SET name=:name, country_id=:country_id WHERE city_id=:city_id";

   Map<String, Object> namedParameters = new HashMap<String, Object>();
   namedParameters.put("name", city.getName());
   namedParameters.put("country_id", city.getCountry().getCountryId());
   namedParameters.put("city_id", city.getCityId());

   NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
   namedParameterJdbcTemplate.update(SQL, namedParameters);
   transactionManager.commit(status);

   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Modify Operation Success");

  } catch (DuplicateKeyException e1) {
   logger.error("Error : DuplicateKeyException --> " + e1);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Modify Operation Fail, City Already Exist");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  } catch (Exception e) {
   logger.error("Error: modify(E) -- > " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Modify Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }

  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> delete(List<E> paramList) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxnDf = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   logger.info("<--Execute Delete Operation -->");
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxnDf);
   List<City> empList = (List<City>) paramList;

   for (City city : empList) {
    String SQL = " UPDATE city SET state_code=:state_code WHERE city_id=:city_id";

    Map<String, Object> namedParameters = new HashMap<String, Object>();

    namedParameters.put("city_id", city.getCityId());
    namedParameters.put("state_code", Short.valueOf((short) 10));

    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
    namedParameterJdbcTemplate.update(SQL, namedParameters);
   }
   transactionManager.commit(status);
   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Delete Operation Success");
  } catch (Exception e) {
   logger.error("Error : Delete --> " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Delete Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }
  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <E, K1, K2, K3, K4> List<E> getList(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {
  List<City> dataList = null;
  try {
   logger.info("<--Excute getList(K1, K2, K3)-->");

   String param = (String) paramK1;
   String action = (String) paramK2;
   Integer cityId = (Integer) paramK3;
   Integer countryId = (Integer) paramK4;

   logger.info(">>----- param ----> " + param);
   logger.info(">>----- action ---> " + action);
   logger.info(">>----- cityId --> " + cityId);
   logger.info(">>----- countryId --> " + countryId);

   String SQL = "SELECT * FROM  city_get_data(?,?,?,?)";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

   dataList = jdbcTemplate.query(SQL, new Object[] { param, action, cityId, countryId }, new RowMapper<City>() {
    public City mapRow(ResultSet rs, int rownumber) throws SQLException {
     City city = new City();
     city.setCityId(Integer.valueOf(rs.getInt("city_id")));
     city.setName(rs.getString("name"));
     city.setAddedDate(rs.getTimestamp("added_date"));
     city.setStatCode(Short.valueOf(rs.getShort("state_code")));

     // Country Drop Down
     Country country = new Country();
     country.setCountryId(Integer.valueOf(rs.getInt("country_id")));
     country.setName(rs.getString("country"));
     city.setCountry(country);

     return city;
    }
   });
  } catch (Exception e) {
   logger.error("Error : getList(K1, K2, K3)--> " + e);
  }
  return (List<E>) dataList;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <V, K1, K2, K3, K4> V getObject(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {
  City city = null;
  try {
   logger.info("<--Excute getObject(K1, K2, K3)-->");
   String param = (String) paramK1;
   String action = (String) paramK2;
   Integer cityId = (Integer) paramK3;
   Integer countryId = (Integer) paramK4;

   logger.info(">>----- param ----> " + param);
   logger.info(">>----- action ---> " + action);
   logger.info(">>----- cityId --> " + cityId);
   logger.info(">>----- countryId --> " + countryId);

   String SQL = "SELECT * FROM  city_get_data(?,?,?,?)";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

   city = jdbcTemplate.queryForObject(SQL, new Object[] { param, action, cityId, countryId }, new RowMapper<City>() {
    public City mapRow(ResultSet rs, int rownumber) throws SQLException {
     City city = new City();
     city.setCityId(Integer.valueOf(rs.getInt("city_id")));
     city.setName(rs.getString("name"));
     city.setAddedDate(rs.getTimestamp("added_date"));
     city.setStatCode(Short.valueOf(rs.getShort("state_code")));

     // Country Drop Down
     Country country = new Country();
     country.setCountryId(Integer.valueOf(rs.getInt("country_id")));
     country.setName(rs.getString("country"));
     city.setCountry(country);

     return city;
    }
   });
  } catch (Exception e) {
   logger.error("Error : getObject(K1, K2, K3)--> " + e);
  }
  return (V) city;
 }

 // Get Next Row Value
 private int nextRowValue() throws Exception {
  logger.info("Execute nextRowValue");
  JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
  String SQL = "SELECT * FROM  city_next_row_val()";
  Integer maxNo = (Integer) jdbcTemplate.queryForObject(SQL, Integer.class);

  return maxNo.intValue();
 }

}


EmployeeManager.java

package com.test.persistance;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.test.entity.City;
import com.test.entity.Country;
import com.test.entity.Employee;
import com.test.event.manager.EventManager;

@Repository("employeeManager")
public class EmployeeManager implements EventManager {

 static final Logger logger = Logger.getLogger(EmployeeManager.class);
 private DataSource dataSource;

 public EmployeeManager() {

 }

 public DataSource getDataSource() {
  return dataSource;
 }

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> create(E paramE) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxn = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxn);

   Integer nextValue = nextRowValue();
   logger.debug(">>----- nextValue-->" + nextValue);

   Employee employee = (Employee) paramE;

   String SQL = "INSERT INTO  employee(emp_id, emp_name, age, email, mobile_no, nic, dob, gender, marital_state, country_id, city_id, added_date, state_code) VALUES (:emp_id, :emp_name, :age, :email, :mobile_no, :nic, :dob, :gender, :marital_state, :country_id, :city_id, :added_date, :state_code)";

   Map<String, Object> namedParameters = new HashMap<String, Object>();
   namedParameters.put("emp_id", nextValue);
   namedParameters.put("emp_name", employee.getEmpName());
   namedParameters.put("age", employee.getAge());
   namedParameters.put("email", employee.getEmail());
   namedParameters.put("mobile_no", employee.getMobileNo());
   namedParameters.put("nic", employee.getNic());
   namedParameters.put("dob", employee.getDob());
   namedParameters.put("gender", employee.getGender());
   namedParameters.put("marital_state", employee.getMaritalState());
   namedParameters.put("country_id", employee.getCountry().getCountryId());
   namedParameters.put("city_id", employee.getCity().getCityId());
   namedParameters.put("added_date", employee.getAddedDate());
   namedParameters.put("state_code", Short.valueOf((short) 1));

   NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
   namedParameterJdbcTemplate.update(SQL, namedParameters);
   transactionManager.commit(status);

   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Create Operation Success");

  } catch (DuplicateKeyException e1) {
   logger.error("Error : DuplicateKeyException --> " + e1);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Create Operation Fail, Employee Already Exist");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  } catch (Exception e) {
   logger.error("Error: create(E) -- > " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Create Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }

  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> update(E paramE) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxn = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxn);

   Employee employee = (Employee) paramE;

   String SQL = "UPDATE employee SET emp_name=:emp_name, age=:age, email=:email, mobile_no=:mobile_no, nic=:nic, dob=:dob, gender=:gender, marital_state=:marital_state, country_id=:country_id, city_id=:city_id WHERE emp_id=:emp_id";

   Map<String, Object> namedParameters = new HashMap<String, Object>();
   namedParameters.put("emp_name", employee.getEmpName());
   namedParameters.put("age", employee.getAge());
   namedParameters.put("email", employee.getEmail());
   namedParameters.put("mobile_no", employee.getMobileNo());
   namedParameters.put("nic", employee.getNic());
   namedParameters.put("dob", employee.getDob());
   namedParameters.put("gender", employee.getGender());
   namedParameters.put("marital_state", employee.getMaritalState());
   namedParameters.put("country_id", employee.getCountry().getCountryId());
   namedParameters.put("city_id", employee.getCity().getCityId());
   namedParameters.put("emp_id", employee.getEmpId());

   NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
   namedParameterJdbcTemplate.update(SQL, namedParameters);
   transactionManager.commit(status);

   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Modify Operation Success");

  } catch (DuplicateKeyException e1) {
   logger.error("Error : DuplicateKeyException --> " + e1);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Modify Operation Fail, Employee Already Exist");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  } catch (Exception e) {
   logger.error("Error: modify(E) -- > " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Modify Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }

  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <K, V, E> Map<K, V> delete(List<E> paramList) throws Exception {
  DataSourceTransactionManager transactionManager = null;
  TransactionStatus status = null;
  TransactionDefinition trxnDf = new DefaultTransactionDefinition();
  Map<String, String> returnMap = new HashMap<String, String>();
  try {
   logger.info("<--Execute Delete Operation -->");
   transactionManager = new DataSourceTransactionManager(this.dataSource);
   status = transactionManager.getTransaction(trxnDf);
   List<Employee> empList = (List<Employee>) paramList;

   for (Employee employee : empList) {
    String SQL = " UPDATE employee SET state_code=:state_code WHERE emp_id=:emp_id";

    Map<String, Object> namedParameters = new HashMap<String, Object>();

    namedParameters.put("emp_id", employee.getEmpId());
    namedParameters.put("state_code", Short.valueOf((short) 10));

    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
    namedParameterJdbcTemplate.update(SQL, namedParameters);
   }
   transactionManager.commit(status);
   returnMap.put("STATE", "SUCCESS");
   returnMap.put("MESSAGE", "Delete Operation Success");
  } catch (Exception e) {
   logger.error("Error : Delete --> " + e);
   returnMap.put("STATE", "FAIL");
   returnMap.put("MESSAGE", "Delete Operation Fail");
   if (transactionManager != null) {
    transactionManager.rollback(status);
   }
  }
  return (Map<K, V>) returnMap;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <E, K1, K2, K3, K4> List<E> getList(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {
  List<Employee> dataList = null;
  try {
   logger.info("<--Excute getList(K1, K2, K3)-->");

   String param = (String) paramK1;
   String action = (String) paramK2;
   Integer empId = (Integer) paramK3;

   logger.info(">>----- param ----> " + param);
   logger.info(">>----- action ---> " + action);
   logger.info(">>----- empId --> " + empId);

   String SQL = "SELECT * FROM  employee_get_data(?,?,?)";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

   dataList = jdbcTemplate.query(SQL, new Object[] { param, action, empId }, new RowMapper<Employee>() {
    public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
     Employee employee = new Employee();
     employee.setEmpId(Integer.valueOf(rs.getInt("emp_id")));
     employee.setEmpName(rs.getString("emp_name"));
     employee.setAge(rs.getInt("age"));
     employee.setEmail(rs.getString("email"));
     employee.setMobileNo(rs.getString("mobile_no"));
     employee.setNic(rs.getString("nic"));
     employee.setDob(rs.getTimestamp("dob"));
     employee.setGender(rs.getString("gender"));
     employee.setMaritalState(rs.getString("marital_state"));
     employee.setAddedDate(rs.getTimestamp("added_date"));
     employee.setStatCode(Short.valueOf(rs.getShort("state_code")));

     // Country Drop Down
     Country country = new Country();
     country.setCountryId(Integer.valueOf(rs.getInt("country_id")));
     country.setName(rs.getString("country"));
     employee.setCountry(country);

     // City Drop Down
     City city = new City();
     city.setCityId(Integer.valueOf(rs.getInt("city_id")));
     city.setName(rs.getString("city"));
     employee.setCity(city);

     return employee;
    }
   });
  } catch (Exception e) {
   logger.error("Error : getList(K1, K2, K3)--> " + e);
  }
  return (List<E>) dataList;
 }

 @SuppressWarnings("unchecked")
 @Override
 public <V, K1, K2, K3, K4> V getObject(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {
  Employee employee = null;
  try {
   logger.info("<--Excute getObject(K1, K2, K3)-->");

   String param = (String) paramK1;
   String action = (String) paramK2;
   Integer empId = (Integer) paramK3;

   logger.info(">>----- param ----> " + param);
   logger.info(">>----- action ---> " + action);
   logger.info(">>----- empId --> " + empId);

   String SQL = "SELECT * FROM  employee_get_data(?,?,?)";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

   employee = jdbcTemplate.queryForObject(SQL, new Object[] { param, action, empId }, new RowMapper<Employee>() {
    public Employee mapRow(ResultSet rs, int rownumber) throws SQLException {
     Employee employee = new Employee();
     employee.setEmpId(Integer.valueOf(rs.getInt("emp_id")));
     employee.setEmpName(rs.getString("emp_name"));
     employee.setAge(rs.getInt("age"));
     employee.setEmail(rs.getString("email"));
     employee.setMobileNo(rs.getString("mobile_no"));
     employee.setNic(rs.getString("nic"));
     employee.setDob(rs.getTimestamp("dob"));
     employee.setGender(rs.getString("gender"));
     employee.setMaritalState(rs.getString("marital_state"));
     employee.setAddedDate(rs.getTimestamp("added_date"));
     employee.setStatCode(Short.valueOf(rs.getShort("state_code")));

     // Country Drop Down
     Country country = new Country();
     country.setCountryId(Integer.valueOf(rs.getInt("country_id")));
     country.setName(rs.getString("country"));
     employee.setCountry(country);

     // City Drop Down
     City city = new City();
     city.setCityId(Integer.valueOf(rs.getInt("city_id")));
     city.setName(rs.getString("city"));
     employee.setCity(city);

     return employee;
    }
   });
  } catch (Exception e) {
   logger.error("Error : getObject(K1, K2, K3)--> " + e);
  }
  return (V) employee;
 }

 // Get Next Row Value
 private int nextRowValue() throws Exception {
  logger.info("Execute nextRowValue");
  JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
  String SQL = "SELECT * FROM  employee_next_row_val()";
  Integer maxNo = (Integer) jdbcTemplate.queryForObject(SQL, Integer.class);

  return maxNo.intValue();
 }

}



Creating Services for Country, City, Employee:

CountryService.java 

package com.test.service;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.test.event.manager.EventManager;

@Service("countryService")
public class CountryService implements EventManager {

 @Autowired
 @Qualifier("countryManager")
 private EventManager countryManager;

 public CountryService() {

 }

 @Override
 public <K, V, E> Map<K, V> create(E paramE) throws Exception {

  return countryManager.create(paramE);
 }

 @Override
 public <K, V, E> Map<K, V> update(E paramE) throws Exception {

  return countryManager.update(paramE);
 }

 @Override
 public <K, V, E> Map<K, V> delete(List<E> paramList) throws Exception {

  return countryManager.delete(paramList);
 }

 @Override
 public <E, K1, K2, K3, K4> List<E> getList(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {

  return countryManager.getList(paramK1, paramK2, paramK3, paramK4);
 }

 @Override
 public <V, K1, K2, K3, K4> V getObject(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {

  return countryManager.getObject(paramK1, paramK2, paramK3, paramK4);
 }

 // Getters & Setters
 public EventManager getCountryManager() {
  return countryManager;
 }

 public void setCountryManager(EventManager countryManager) {
  this.countryManager = countryManager;
 }

}


CityService.java 

package com.test.service;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.test.event.manager.EventManager;

@Service("cityService")
public class CityService implements EventManager {

 @Autowired
 @Qualifier("cityManager")
 private EventManager cityManager;

 public CityService() {

 }

 @Override
 public <K, V, E> Map<K, V> create(E paramE) throws Exception {

  return cityManager.create(paramE);
 }

 @Override
 public <K, V, E> Map<K, V> update(E paramE) throws Exception {

  return cityManager.update(paramE);
 }

 @Override
 public <K, V, E> Map<K, V> delete(List<E> paramList) throws Exception {

  return cityManager.delete(paramList);
 }

 @Override
 public <E, K1, K2, K3, K4> List<E> getList(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {

  return cityManager.getList(paramK1, paramK2, paramK3, paramK4);
 }

 @Override
 public <V, K1, K2, K3, K4> V getObject(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {

  return cityManager.getObject(paramK1, paramK2, paramK3, paramK4);
 }

 // Getters & Setters
 public EventManager getCityManager() {
  return cityManager;
 }

 public void setCityManager(EventManager cityManager) {
  this.cityManager = cityManager;
 }

}


EmployeeService.java

package com.test.service;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.test.event.manager.EventManager;

@Service("employeeService")
public class EmployeeService implements EventManager {

 @Autowired
 @Qualifier("employeeManager")
 private EventManager employeeManager;

 public EmployeeService() {

 }

 @Override
 public <K, V, E> Map<K, V> create(E paramE) throws Exception {

  return employeeManager.create(paramE);
 }

 @Override
 public <K, V, E> Map<K, V> update(E paramE) throws Exception {

  return employeeManager.update(paramE);
 }

 @Override
 public <K, V, E> Map<K, V> delete(List<E> paramList) throws Exception {

  return employeeManager.delete(paramList);
 }

 @Override
 public <E, K1, K2, K3, K4> List<E> getList(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {

  return employeeManager.getList(paramK1, paramK2, paramK3, paramK4);
 }

 @Override
 public <V, K1, K2, K3, K4> V getObject(K1 paramK1, K2 paramK2, K3 paramK3, K4 paramK4) throws Exception {

  return employeeManager.getObject(paramK1, paramK2, paramK3, paramK4);
 }

 // Getters & Setters
 public EventManager getEmployeeManager() {
  return employeeManager;
 }

 public void setEmployeeManager(EventManager employeeManager) {
  this.employeeManager = employeeManager;
 }

}


Creating Beans For Country, City, Employee:

EmployeeBean.java

package com.test.bean;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;

import com.test.entity.City;
import com.test.entity.Country;
import com.test.entity.Employee;
import com.test.event.manager.EventManager;

@ManagedBean(name = "employeeBean")
@ViewScoped
public class EmployeeBean implements Serializable {
 private static final long serialVersionUID = 1L;
 static final Logger logger = Logger.getLogger(EmployeeBean.class);

 // Connect to Services
 @ManagedProperty("#{employeeService}")
 private EventManager employeeService;

 @ManagedProperty("#{countryService}")
 private EventManager countryService;

 @ManagedProperty("#{cityService}")
 private EventManager cityService;

 // Employee Variables
 private Employee employee = new Employee();
 private List<Employee> empList;
 private List<Employee> selectedEmpList;
 private List<Employee> filteredList;

 // Country Variables
 private List<Country> countryList = new ArrayList<Country>(0);
 private Country country = new Country();
 private Integer countryId;

 // City Variables
 private List<City> cityList = new ArrayList<City>(0);
 private City city = new City();
 private Integer cityId;

 private String reqParam = null;

 @PostConstruct
 public void init() {
  logger.info("<--Execute init-->");
  try {
   HttpServletRequest request = (HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest();
   this.reqParam = request.getParameter("param");
   logger.info("--reqParam-->" + this.reqParam);
   if ((this.reqParam != null) && (!this.reqParam.isEmpty())) {
    if (this.reqParam.equalsIgnoreCase("create")) {
     setCountryList(getCountryService().getList(null, "DROPDOWN_LIST", null, null));

    } else if (this.reqParam.equalsIgnoreCase("modify")) {
     setCountryList(getCountryService().getList(null, "DROPDOWN_LIST", null, null));
     setEmpList(getEmployeeService().getList(this.reqParam, "TABLE", null, null));

    } else if (this.reqParam.equalsIgnoreCase("delete")) {
     setEmpList(getEmployeeService().getList(this.reqParam, "TABLE", null, null));

    } else if (this.reqParam.equalsIgnoreCase("search")) {
     setEmpList(getEmployeeService().getList(this.reqParam, "TABLE", null, null));

    } else {
     logger.warn("<<--Invalid Action Type-->>");
     FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", "Invalid Action Type");
     FacesContext.getCurrentInstance().addMessage(null, msg);
    }
   } else {
    logger.warn("<<--Unauthorized Access-->>");
    FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", "Unauthorized Access");
    FacesContext.getCurrentInstance().addMessage(null, msg);
   }
  } catch (Exception e) {
   logger.fatal("--Initial Data loading Error-->" + e);
   FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", "Initial Data loading Error");
   FacesContext.getCurrentInstance().addMessage(null, msg);
  }
 }

 // Country Drop down Change
 public void onCountryChange() {
  try {
   if (getCountryId() != null) {
    for (Country country : this.countryList) {
     if (getCountryId().intValue() == country.getCountryId().intValue()) {
      setCountry(country);
      setCityList(getCityService().getList(null, "DROPDOWN_LIST_BY_REF_KEY", null, country.getCountryId()));
      this.employee.setCountry(getCountry());
      break;
     }
    }
   } else {
    this.country = new Country();
   }
  } catch (Exception e) {
   logger.error("Error : onCountryChange-->" + e);
  }
 }

 // City Drop down Change
 public void onCityChange() {
  try {
   if (getCityId() != null) {
    for (City city : this.cityList) {
     if (getCityId().intValue() == city.getCityId().intValue()) {
      setCity(city);
      this.employee.setCity(getCity());
      break;
     }
    }
   } else {
    this.city = new City();
   }
  } catch (Exception e) {
   logger.error("Error : onCityChange-->" + e);
  }
 }

 public void save() {
  try {
   logger.info("<--Execute Save() --> ");
   String validationMsg = validator();
   String[] msgArr = validationMsg.split("@");
   if (!msgArr[0].contains("SEVERITY_ERROR")) {

    this.employee.setAddedDate(new Date(System.currentTimeMillis()));

    Map<String, String> resultMap = getEmployeeService().create(this.employee);
    logger.info("--Create State-->" + (String) resultMap.get("STATE"));
    logger.info("--Message-->" + (String) resultMap.get("MESSAGE"));
    if (resultMap.get("STATE").equals("SUCCESS")) {
     FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_INFO, (String) resultMap.get("MESSAGE"), "");
     FacesContext.getCurrentInstance().addMessage(null, msg);

     reset();
    } else {
     FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", (String) resultMap.get("MESSAGE"));
     FacesContext.getCurrentInstance().addMessage(null, msg);
    }
   } else {
    FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Warning!", msgArr[1]);
    FacesContext.getCurrentInstance().addMessage(null, msg);
   }
  } catch (Exception e) {
   logger.error("Error: Save() --> " + e);
   FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", "Operation Fail");
   FacesContext.getCurrentInstance().addMessage(null, msg);
  }
 }

 public void modify() {
  try {
   logger.info("<--Execute Modify() --> ");
   if (this.employee != null) {
    String validationMsg = validator();
    String[] msgArr = validationMsg.split("@");
    if (!msgArr[0].contains("SEVERITY_ERROR")) {

     Map<String, String> resultMap = getEmployeeService().update(this.employee);
     logger.info("--Modify State-->" + (String) resultMap.get("STATE"));
     logger.info("--Message-->" + (String) resultMap.get("MESSAGE"));
     if (resultMap.get("STATE").equals("SUCCESS")) {
      FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_INFO, (String) resultMap.get("MESSAGE"), "");
      FacesContext.getCurrentInstance().addMessage(null, msg);

      reset();
     } else {
      FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", (String) resultMap.get("MESSAGE"));
      FacesContext.getCurrentInstance().addMessage(null, msg);
     }
    } else {
     FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Warning!", msgArr[1]);
     FacesContext.getCurrentInstance().addMessage(null, msg);
    }
   } else {
    FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Warning!", "Please Select at least One Record To Modify");
    FacesContext.getCurrentInstance().addMessage(null, msg);
   }
  } catch (Exception e) {
   logger.error("Error: Modify() --> " + e);
   FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", "Operation Fail");
   FacesContext.getCurrentInstance().addMessage(null, msg);
  }
 }

 public void delete() {
  try {
   logger.info("<--Execute Delete() -->");
   if ((this.selectedEmpList != null) && (this.selectedEmpList.size() > 0)) {
    Map<String, String> resultMap = getEmployeeService().delete(getSelectedEmpList());
    logger.info("--Delete State-->" + (String) resultMap.get("STATE"));
    logger.info("--Message-->" + (String) resultMap.get("MESSAGE"));
    if (resultMap.get("STATE").equals("SUCCESS")) {
     FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_INFO, (String) resultMap.get("MESSAGE"), "");
     FacesContext.getCurrentInstance().addMessage(null, msg);

     this.empList.removeAll(this.selectedEmpList);
    } else {
     FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", (String) resultMap.get("MESSAGE"));
     FacesContext.getCurrentInstance().addMessage(null, msg);
    }
   } else {
    FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", "Please Select at least One Record To Delete");
    FacesContext.getCurrentInstance().addMessage(null, msg);
   }
  } catch (Exception e) {
   logger.error("Error: Delete() --> " + e);
   FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error!", "Operation Fail");
   FacesContext.getCurrentInstance().addMessage(null, msg);
  }
 }

 private String validator() throws Exception {
  String rtnMsg = "SEVERITY_INFO@OK";
  if ((this.employee.getEmpName() == null) || (this.employee.getEmpName().isEmpty())) {
   rtnMsg = "SEVERITY_ERROR@Employee Name is Required";
   return rtnMsg;
  }

  if ((this.employee.getMobileNo() == null) || (this.employee.getMobileNo().isEmpty())) {
   rtnMsg = "SEVERITY_ERROR@Mobile # is Required";
   return rtnMsg;
  }
  if ((this.employee.getNic() == null) || (this.employee.getNic().isEmpty())) {
   rtnMsg = "SEVERITY_ERROR@NIC # is Required";
   return rtnMsg;
  }

  return rtnMsg;
 }

 private void reset() {
  this.employee = new Employee();
  this.country = new Country();
  this.city = new City();
  setCountryId(null);
  setCityId(null);
 }

 // Getters & Setters
 public Employee getEmployee() {
  return employee;
 }

 public void setEmployee(Employee employee) {
  try {
   if (employee != null) {

    employee = (Employee) getEmployeeService().getObject(null, "DROPDOWN_LIST_BY_ID", employee.getEmpId(), null);

    // Country Drop down
    if (employee.getCountry() != null) {
     setCountry(employee.getCountry());
     setCountryId(employee.getCountry().getCountryId());
     setCityList(getCityService().getList(null, "DROPDOWN_LIST_BY_REF_KEY", null, country.getCountryId()));
    } else {
     setCountry(new Country());
     setCountryId(null);
    }
    // City Drop down
    if (employee.getCity() != null) {
     setCity(employee.getCity());
     setCityId(employee.getCity().getCityId());
    } else {
     setCity(new City());
     setCityId(null);
    }

    if (this.reqParam.equalsIgnoreCase("search")) {
     // Gender Radio
     if (employee.getGender().equalsIgnoreCase("M")) {
      employee.setGender("Male");
     } else {
      employee.setGender("Female");
     }

     // Marital State Radio
     if (employee.getMaritalState().equalsIgnoreCase("Y")) {
      employee.setMaritalState("Married");
     } else {
      employee.setMaritalState("Single");
     }
    }

    this.employee = employee;
   }
  } catch (Exception e) {
   logger.info("Error: setEmployee() --> " + e);
  }
 }

 public EventManager getEmployeeService() {
  return employeeService;
 }

 public void setEmployeeService(EventManager employeeService) {
  this.employeeService = employeeService;
 }

 public EventManager getCountryService() {
  return countryService;
 }

 public void setCountryService(EventManager countryService) {
  this.countryService = countryService;
 }

 public EventManager getCityService() {
  return cityService;
 }

 public void setCityService(EventManager cityService) {
  this.cityService = cityService;
 }

 public List<Employee> getEmpList() {
  return empList;
 }

 public void setEmpList(List<Employee> empList) {
  this.empList = empList;
 }

 public List<Employee> getSelectedEmpList() {
  return selectedEmpList;
 }

 public void setSelectedEmpList(List<Employee> selectedEmpList) {
  this.selectedEmpList = selectedEmpList;
 }

 public List<Employee> getFilteredList() {
  return filteredList;
 }

 public void setFilteredList(List<Employee> filteredList) {
  this.filteredList = filteredList;
 }

 public List<Country> getCountryList() {
  return countryList;
 }

 public void setCountryList(List<Country> countryList) {
  this.countryList = countryList;
 }

 public Country getCountry() {
  return country;
 }

 public void setCountry(Country country) {
  this.country = country;
 }

 public Integer getCountryId() {
  return countryId;
 }

 public void setCountryId(Integer countryId) {
  this.countryId = countryId;
 }

 public List<City> getCityList() {
  return cityList;
 }

 public void setCityList(List<City> cityList) {
  this.cityList = cityList;
 }

 public City getCity() {
  return city;
 }

 public void setCity(City city) {
  this.city = city;
 }

 public Integer getCityId() {
  return cityId;
 }

 public void setCityId(Integer cityId) {
  this.cityId = cityId;
 }

 public String getReqParam() {
  return reqParam;
 }

 public void setReqParam(String reqParam) {
  this.reqParam = reqParam;
 }

}


SQL Functions:

--- Country Next Row Value --
CREATE OR REPLACE FUNCTION "country_next_row_val" ()
RETURNS integer AS $$
declare
 maxNo integer; 
 
BEGIN
   SELECT coalesce(MAX(country_id),0) INTO maxNo FROM country;
   
IF(maxNo = 0)THEN
   RETURN maxNo + 1;
   
ELSE
 RETURN maxNo+1;  
 
END IF;
 END;
$$ LANGUAGE plpgsql;

 


--Country Search, Modify, Delete, Drop Down List --
CREATE OR REPLACE FUNCTION "country_get_data" (param character varying, action character varying, countryid integer)
RETURNS TABLE( 
"country_id" integer,
"name" character varying(10),
"code" character varying(40), 
"added_date" timestamp without time zone,
"state_code" smallint 
) 
AS $$
BEGIN
CASE action

WHEN 'DROPDOWN_LIST' THEN
/*Drop Down List By Name*/
RETURN QUERY
   SELECT c.country_id, c.name, c.code, null::timestamp without time zone, null::smallint FROM country c WHERE c.state_code = 1 ORDER BY c.name ASC;

WHEN 'DROPDOWN_LIST_BY_ID' THEN
/*Drop Down List By ID*/
RETURN QUERY
  SELECT c.country_id, c.name, c.code,  null::timestamp without time zone, null::smallint FROM country c WHERE c.state_code = 1 AND c.country_id = countryId ORDER BY c.name ASC;

 ELSE
/*Search, Modify, Delete List*/

CASE param
 /*Modify*/
WHEN 'modify' THEN
RETURN QUERY
   SELECT c.country_id, c.name, c.code, c.added_date, null::smallint FROM country c WHERE c.state_code = 1 ORDER BY c.country_id ASC;

/*Delete*/
WHEN 'delete' THEN
RETURN QUERY
   SELECT c.country_id, c.name, c.code, c.added_date, null::smallint FROM country c WHERE c.state_code = 1 ORDER BY c.country_id ASC;

/*Search*/
ELSE

 RETURN QUERY
   SELECT c.country_id, c.name, c.code, c.added_date, null::smallint FROM country c WHERE c.state_code = 1 ORDER BY c.country_id ASC;
 
END CASE;

END CASE;
 
END;
$$ 
LANGUAGE plpgsql;



--- City Next Row Value --
CREATE OR REPLACE FUNCTION "city_next_row_val" ()
RETURNS integer AS $$
declare
 maxNo integer; 
 
BEGIN
   SELECT coalesce(MAX(city_id),0) INTO maxNo FROM city;
   
IF(maxNo = 0)THEN
   RETURN maxNo + 1;
   
ELSE
 RETURN maxNo+1;  
 
END IF;
 END;
$$ LANGUAGE plpgsql;

 


--City Search, Modify, Delete, Drop Down List --
CREATE OR REPLACE FUNCTION "city_get_data" (param character varying, action character varying, cityId integer, countryId integer)
RETURNS TABLE(
"city_id" integer,
"name" character varying(50),
"country_id" integer, 
"country" character varying, 
"added_date" timestamp without time zone,  
"state_code" smallint
) 
AS $$
BEGIN
CASE action

WHEN 'DROPDOWN_LIST' THEN
/*Drop Down List By Name*/
RETURN QUERY
   SELECT c.city_id, c.name, null::integer, null::character varying, null::timestamp without time zone, null::smallint 
   FROM city c LEFT OUTER JOIN country co ON (c.country_id=co.country_id) WHERE c.state_code = 1 ORDER BY c.name ASC;

WHEN 'DROPDOWN_LIST_BY_ID' THEN
/*Drop Down List By ID*/
RETURN QUERY
  SELECT c.city_id, c.name, c.country_id, null::character varying, null::timestamp without time zone, null::smallint 
  FROM city c LEFT OUTER JOIN country co ON (c.country_id=co.country_id) WHERE c.state_code = 1 AND c.city_id = cityId ORDER BY c.name ASC;

WHEN 'DROPDOWN_LIST_BY_REF_KEY' THEN
/*Drop Down List By Country ID*/
RETURN QUERY
  SELECT c.city_id, c.name, c.country_id, null::character varying, null::timestamp without time zone, null::smallint 
  FROM city c LEFT OUTER JOIN country co ON (c.country_id=co.country_id) WHERE c.state_code = 1 AND c.country_id = countryId ORDER BY c.name ASC;
  
  
ELSE
/*Search, Modify, Delete List*/

CASE param
 
/*Modify*/
WHEN 'modify' THEN
RETURN QUERY
   SELECT c.city_id, c.name, c.country_id, co.name AS country, c.added_date, null::smallint 
   FROM city c LEFT OUTER JOIN country co ON (c.country_id=co.country_id) WHERE c.state_code = 1 ORDER BY c.city_id ASC;

/*Delete*/
WHEN 'delete' THEN
RETURN QUERY
   SELECT c.city_id, c.name, c.country_id, co.name AS country, c.added_date, null::smallint 
   FROM city c LEFT OUTER JOIN country co ON (c.country_id=co.country_id) WHERE c.state_code = 1 ORDER BY c.city_id ASC;

/*Search*/
ELSE
 
 RETURN QUERY
   SELECT c.city_id, c.name, c.country_id, co.name AS country, c.added_date, null::smallint 
   FROM city c LEFT OUTER JOIN country co ON (c.country_id=co.country_id) WHERE c.state_code = 1 ORDER BY c.city_id ASC;
 
   
END CASE;

END CASE;
  
END;
$$ 
LANGUAGE plpgsql;




--- Employee Next Row Value --
CREATE OR REPLACE FUNCTION "employee_next_row_val" ()
RETURNS integer AS $$
declare
 maxNo integer; 
 
BEGIN
   SELECT coalesce(MAX(emp_id),0) INTO maxNo FROM employee;
   
IF(maxNo = 0)THEN
   RETURN maxNo + 1;
   
ELSE
 RETURN maxNo+1;  
 
END IF;
 END;
$$ LANGUAGE plpgsql;

 


--Employee Search, Modify, Delete, Drop Down List --
CREATE OR REPLACE FUNCTION "employee_get_data" (param character varying, action character varying, empid integer)
RETURNS TABLE(
"emp_id" integer, 
"emp_name" character varying, 
"age" integer,
"email" character varying, 
"mobile_no" character varying, 
"nic" character varying, 
"dob" timestamp without time zone, 
"gender" character varying, 
"marital_state" character varying, 
"country_id" integer, 
"country" character varying, 
"city_id" integer, 
"city" character varying, 
"added_date" timestamp without time zone,
"state_code" smallint    
 
) 
AS $$
BEGIN
CASE action

WHEN 'DROPDOWN_LIST' THEN
/*Drop Down List By Name*/
RETURN QUERY
  SELECT e.emp_id, e.emp_name, null::integer, null::character varying, null::character varying, null::character varying, null::timestamp without time zone, null::character varying, null::character varying,  null::integer,  null::character varying, null::integer, null::character varying, null::timestamp without time zone, null::smallint 
  FROM employee e LEFT OUTER JOIN country co ON (e.country_id=co.country_id) LEFT OUTER JOIN city c ON (c.city_id=c.city_id) WHERE e.state_code = 1 ORDER BY e.emp_name ASC;

WHEN 'DROPDOWN_LIST_BY_ID' THEN
/*Drop Down List By ID*/
RETURN QUERY
  SELECT e.emp_id, e.emp_name, e.age, e.email, e.mobile_no, e.nic, e.dob, e.gender, e.marital_state, e.country_id, co.name AS country, e.city_id, c.name AS city, null::timestamp without time zone, null::smallint 
  FROM employee e LEFT OUTER JOIN country co ON (e.country_id=co.country_id) LEFT OUTER JOIN city c ON (c.city_id=c.city_id) WHERE e.state_code = 1 AND e.emp_id = empId ORDER BY e.emp_name ASC;
 
ELSE
/*Search, Modify, Delete List*/

CASE param
 
/*Modify*/
WHEN 'modify' THEN
RETURN QUERY
  SELECT e.emp_id, e.emp_name, null::integer, e.email, e.mobile_no, e.nic, null::timestamp without time zone, e.gender, null::character varying, null::integer, null::character varying, null::integer, null::character varying, null::timestamp without time zone, null::smallint 
  FROM employee e LEFT OUTER JOIN country co ON (e.country_id=co.country_id) LEFT OUTER JOIN city c ON (c.city_id=c.city_id) WHERE e.state_code = 1  ORDER BY e.emp_id ASC;

/*Delete*/
WHEN 'delete' THEN
RETURN QUERY
  SELECT e.emp_id, e.emp_name, null::integer, e.email, e.mobile_no, e.nic, null::timestamp without time zone, e.gender, null::character varying, null::integer, null::character varying, null::integer, null::character varying, null::timestamp without time zone, null::smallint 
  FROM employee e LEFT OUTER JOIN country co ON (e.country_id=co.country_id) LEFT OUTER JOIN city c ON (c.city_id=c.city_id) WHERE e.state_code = 1   ORDER BY e.emp_id ASC;

/*Search*/
ELSE
 
 RETURN QUERY
  SELECT e.emp_id, e.emp_name, null::integer, e.email, e.mobile_no, e.nic, null::timestamp without time zone, e.gender, null::character varying, null::integer, null::character varying, null::integer, null::character varying, null::timestamp without time zone, null::smallint 
  FROM employee e LEFT OUTER JOIN country co ON (e.country_id=co.country_id) LEFT OUTER JOIN city c ON (c.city_id=c.city_id) WHERE e.state_code = 1  ORDER BY e.emp_id ASC;
 
   
END CASE;

END CASE;
  
END;
$$ 
LANGUAGE plpgsql;



applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans:beans xmlns="http://www.springframework.org/schema/mvc"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/beans"
 xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
 xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

 <context:annotation-config />
 <context:component-scan base-package="com.test" />

 <beans:bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
  destroy-method="close" scope="singleton">

  <beans:property name="preferredTestQuery" value="select 1" />
  <beans:property name="checkoutTimeout" value="30000" />
  <beans:property name="idleConnectionTestPeriod" value="30" />
  <beans:property name="initialPoolSize" value="10" />
  <beans:property name="maxIdleTime" value="30" />
  <beans:property name="maxPoolSize" value="100" />
  <beans:property name="minPoolSize" value="10" />
  <beans:property name="maxStatements" value="200" />

  <beans:property name="driverClass" value="org.postgresql.Driver" />
  <beans:property name="jdbcUrl"
   value="jdbc:postgresql://localhost:5432/testdb" />
  <beans:property name="user" value="postgres" />
  <beans:property name="password" value="admin" />
 </beans:bean>


 <beans:bean id="employeeManager" class="com.test.persistance.EmployeeManager">
  <beans:property name="dataSource" ref="dataSource" />
 </beans:bean>

 <beans:bean id="countryManager" class="com.test.persistance.CountryManager">
  <beans:property name="dataSource" ref="dataSource" />
 </beans:bean>

 <beans:bean id="cityManager" class="com.test.persistance.CityManager">
  <beans:property name="dataSource" ref="dataSource" />
 </beans:bean>
</beans:beans>


You can Download the source File Here
<----- Click Here To Download The Source ----->






















Comments

Post a Comment