com.aerothai.database.dept.DeptService.java Source code

Introduction

Here is the source code for com.aerothai.database.dept.DeptService.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.aerothai.database.dept;

import com.aerothai.database.DBConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

/**
 *
 * @author chaia_000
 */
public class DeptService {
    public JSONObject GetDeptAll() throws Exception {

        Connection dbConn = null;
        JSONObject obj = new JSONObject();
        JSONArray objList = new JSONArray();

        int no = 1;
        //obj.put("draw", 2);
        obj.put("tag", "list");
        obj.put("msg", "error");
        obj.put("status", false);
        try {
            dbConn = DBConnection.createConnection();

            Statement stmt = dbConn.createStatement();
            String query = "SELECT * FROM dept";
            System.out.println(query);
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {

                JSONObject jsonData = new JSONObject();
                jsonData.put("id", rs.getInt("iddept"));
                jsonData.put("name", rs.getString("name"));
                jsonData.put("shortname", rs.getString("shortname"));
                jsonData.put("no", no);
                objList.add(jsonData);
                no++;
            }
            obj.put("msg", "done");
            obj.put("status", true);
            obj.put("data", objList);
        } catch (SQLException sqle) {
            throw sqle;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            if (dbConn != null) {
                dbConn.close();
            }
            throw e;
        } finally {
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return obj;
    }

    public JSONObject GetDeptAt(int id) throws Exception {

        Connection dbConn = null;
        JSONObject obj = new JSONObject();
        JSONArray objList = new JSONArray();

        int no = 1;
        //obj.put("draw", 2);
        obj.put("tag", "listat");
        obj.put("msg", "error");
        obj.put("status", false);
        try {
            dbConn = DBConnection.createConnection();

            Statement stmt = dbConn.createStatement();
            String query = "SELECT * FROM dept where iddept = " + id;
            System.out.println(query);
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {

                JSONObject jsonData = new JSONObject();
                jsonData.put("id", rs.getInt("iddept"));
                jsonData.put("name", rs.getString("name"));
                jsonData.put("shortname", rs.getString("shortname"));
                jsonData.put("no", no);
                objList.add(jsonData);
                no++;
            }
            obj.put("msg", "done");
            obj.put("status", true);
            obj.put("data", objList);
        } catch (SQLException sqle) {
            throw sqle;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            if (dbConn != null) {
                dbConn.close();
            }
            throw e;
        } finally {
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return obj;
    }

    /**
     * Method to check whether uname and pwd combination are correct
     * 
     * @param uname
     * @param pwd
     * @return
     * @throws Exception
     */
    public String createDept(String query) throws Exception {

        Connection dbConn = null;
        JSONObject obj = new JSONObject();
        obj.put("msg", "error");
        obj.put("status", false);
        try {
            dbConn = DBConnection.createConnection();

            Statement stmt = dbConn.createStatement();
            System.out.println(query);
            stmt.executeUpdate(query);

            obj.put("tag", "create");
            obj.put("msg", "done");
            obj.put("status", true);

        } catch (SQLException sqle) {
            throw sqle;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            if (dbConn != null) {
                dbConn.close();
            }
            throw e;
        } finally {
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return obj.toJSONString();
    }

    /**
    * Method to check whether uname and pwd combination are correct
    * 
    * @param uname
    * @param pwd
    * @return
    * @throws Exception
    */
    public String updateDept(String query) throws Exception {

        Connection dbConn = null;
        JSONObject obj = new JSONObject();

        try {
            dbConn = DBConnection.createConnection();

            Statement stmt = dbConn.createStatement();
            System.out.println(query);
            stmt.executeUpdate(query);

            obj.put("tag", "update");
            obj.put("msg", "done");
            obj.put("status", true);

        } catch (SQLException sqle) {
            throw sqle;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            if (dbConn != null) {
                dbConn.close();
            }
            throw e;
        } finally {
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return obj.toJSONString();
    }

    /**
     * Method to check whether uname and pwd combination are correct
     * 
     * @param uname
     * @param pwd
     * @return
     * @throws Exception
     */
    public String deleteDept(String query) throws Exception {

        Connection dbConn = null;
        JSONObject obj = new JSONObject();

        try {
            dbConn = DBConnection.createConnection();

            Statement stmt = dbConn.createStatement();
            System.out.println(query);
            stmt.executeUpdate(query);

            obj.put("tag", "delete");
            obj.put("msg", "done");
            obj.put("status", true);

        } catch (SQLException sqle) {
            throw sqle;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            if (dbConn != null) {
                dbConn.close();
            }
            throw e;
        } finally {
            if (dbConn != null) {
                dbConn.close();
            }
        }
        return obj.toJSONString();
    }

}