com.save.area.serviceprovider.AreaServiceImpl.java Source code

Introduction

Here is the source code for com.save.area.serviceprovider.AreaServiceImpl.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.save.area.serviceprovider;

import com.save.connection.DBConnection;
import com.save.global.ErrorLoggedNotification;
import com.save.model.Area;
import com.save.model.City;
import com.save.model.Province;
import com.save.service.AreaService;
import com.save.utilities.CommonUtilities;
import com.vaadin.ui.Notification;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author jetdario
 */
public class AreaServiceImpl implements AreaService {

    @Override
    public List<Area> getAllArea() {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        List<Area> listOfAreas = new ArrayList<>();

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM area ORDER BY AreaCode ASC");
            while (rs.next()) {
                Area a = new Area();
                a.setAreaId(CommonUtilities.convertStringToInt(rs.getString("AreaID")));
                a.setAreaCode(rs.getString("AreaCode"));
                listOfAreas.add(a);
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return listOfAreas;
    }

    @Override
    public boolean createNewArea(String areaCode) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            pstmt = conn.prepareStatement("INSERT INTO Area(AreaCode) VALUES(?)");
            pstmt.setString(1, areaCode);
            pstmt.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean updateLocationToArea(String areaCode, int provinceId, int cityId) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            pstmt = conn.prepareStatement(
                    "UPDATE city " + "SET AreaCode = ? " + "WHERE ProvinceID = ? " + "AND CityID = ? ");
            pstmt.setString(1, areaCode);
            pstmt.setInt(2, provinceId);
            pstmt.setInt(3, cityId);
            pstmt.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean isAreaExist(String areaCode) {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT COUNT(*) FROM area " + "WHERE AreaCode = '" + areaCode + "' ");
            while (rs.next()) {
                if (!rs.getString("COUNT(*)").equals("0")) {
                    result = true;
                }
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean updateArea(String newAreaCode, String previousAreaCode) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("UPDATE area SET AreaCode = ? " + "WHERE AreaCode = ? ");
            pstmt.setString(1, newAreaCode);
            pstmt.setString(2, previousAreaCode);
            pstmt.executeUpdate();

            pstmt = conn.prepareStatement("UPDATE city SET AreaCode = ? " + "WHERE AreaCode = ? ");
            pstmt.setString(1, newAreaCode);
            pstmt.setString(2, previousAreaCode);
            pstmt.executeUpdate();

            conn.commit();
            Notification.show("Commit Update", Notification.Type.TRAY_NOTIFICATION);
            result = true;
        } catch (SQLException ex) {
            try {
                conn.rollback();
                Notification.show("Commit Rollback", Notification.Type.ERROR_MESSAGE);
            } catch (SQLException ex1) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex1.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean deleteArea(int areaId, String areaCode) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            conn.setAutoCommit(false);

            pstmt = conn.prepareStatement("DELETE FROM area WHERE AreaID = " + areaId + " ");
            pstmt.executeUpdate();

            pstmt = conn.prepareStatement(
                    "UPDATE city " + "SET AreaCode = null " + "WHERE AreaCode = '" + areaCode + "' ");
            pstmt.executeUpdate();

            conn.commit();
            result = true;
            Notification.show("Delete Area " + areaCode, Notification.Type.TRAY_NOTIFICATION);
        } catch (SQLException ex) {
            try {
                conn.rollback();
                Notification.show("Delete Failed!", Notification.Type.ERROR_MESSAGE);
            } catch (SQLException ex1) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex1.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean removeFromArea(int provinceId, int cityId) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            pstmt = conn.prepareStatement(
                    "UPDATE city " + "SET AreaCode = null " + "WHERE ProvinceID = ? " + "AND cityID = ? ");
            pstmt.setInt(1, provinceId);
            pstmt.setInt(2, cityId);
            pstmt.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean addProvince(String name) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            pstmt = conn.prepareStatement("INSERT INTO province (ProvinceName) VALUES(?) ");
            pstmt.setString(1, name);
            pstmt.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean addCity(String name, int provinceId) {
        Connection conn = DBConnection.connect();
        PreparedStatement pstmt = null;
        boolean result = false;

        try {
            pstmt = conn.prepareStatement("INSERT INTO city (CityName, ProvinceID) VALUES(?, ?) ");
            pstmt.setString(1, name);
            pstmt.setInt(2, provinceId);
            pstmt.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    pstmt.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean isProvinceExist(String name) {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT COUNT(*) FROM province " + "WHERE ProvinceName = '" + name + "' ");
            while (rs.next()) {
                if (!rs.getString("COUNT(*)").equals("0")) {
                    result = true;
                }
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public boolean isCityExist(String name, int provinceId) {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT COUNT(*) FROM city " + "WHERE CityName = '" + name
                    + "' AND ProvinceID = " + provinceId + " ");
            while (rs.next()) {
                if (!rs.getString("COUNT(*)").equals("0")) {
                    result = true;
                }
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return result;
    }

    @Override
    public int getAreaIdByCode(String areaCode) {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        int id = 0;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT AreaID FROM area " + "WHERE AreaCode = '" + areaCode + "' ");
            while (rs.next()) {
                id = CommonUtilities.convertStringToInt(rs.getString("id"));
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return id;
    }

    @Override
    public String getAreaById(int areaId) {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        String areaCode = null;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT AreaCode FROM area " + "WHERE AreaID = " + areaId + " ");
            while (rs.next()) {
                areaCode = rs.getString("AreaCode");
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return areaCode;
    }

    @Override
    public List<Province> getAllProvinces() {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        List<Province> listProvinces = new ArrayList<>();

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM province");
            while (rs.next()) {
                Province p = new Province();
                p.setProvinceId(CommonUtilities.convertStringToInt(rs.getString("ProvinceID")));
                p.setProvinceName(rs.getString("ProvinceName"));
                listProvinces.add(p);
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return listProvinces;
    }

    @Override
    public List<City> getAllCityProvinceArea() {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        List<City> listOfAreas = new ArrayList<City>();

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM area_list_view ORDER BY AreaCode ASC");
            while (rs.next()) {
                City c = new City();
                c.setAreaCode(rs.getString("AreaCode"));
                c.setProvinceName(rs.getString("ProvinceName"));
                c.setCityName(rs.getString("CityName"));
                listOfAreas.add(c);
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return listOfAreas;
    }

    @Override
    public List<City> getCitiesByProvinceId(int provinceId) {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        List<City> cityList = new ArrayList<>();

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(
                    "SELECT CityID, CityName " + "FROM city " + "WHERE ProvinceID = " + provinceId + " ");
            while (rs.next()) {
                City c = new City();
                c.setCityId(CommonUtilities.convertStringToInt(rs.getString("CityID")));
                c.setCityName(rs.getString("CityName"));
                cityList.add(c);
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return cityList;
    }

    @Override
    public int getProvinceId(String provinceName) {
        Connection conn = DBConnection.connect();
        Statement stmt = null;
        ResultSet rs = null;
        int id = 0;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(
                    "SELECT ProvinceID " + "FROM province " + "WHERE ProvinceName = '" + provinceName + "' ");
            while (rs.next()) {
                id = CommonUtilities.convertStringToInt(rs.getString("id"));
            }
        } catch (SQLException ex) {
            ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
            Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (conn != null || !conn.isClosed()) {
                    stmt.close();
                    rs.close();
                    conn.close();
                }
            } catch (SQLException ex) {
                ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
                Logger.getLogger(AreaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return id;
    }

}