ProyekPBO_Kelas DataHandler

package uts_pbo_2;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import oracle.jdbc.pool.OracleDataSource;

public class DataHandler {

    String jdbcUrl = "jdbc:oracle:thin:172.23.9.185:1521:orcl";
    String userid = "mhs105314031";
    String password = "mhs105314031";
    Connection conn;
    Statement stmt;
    ResultSet rset;
    String query;
    String sqlString;
    private String no;
    private String hal;
    private String pnrm;
    private String pkrm;
    private String TglSurat;
    private String TglTerima;

    public DataHandler() {
    }

    public DataHandler(String no, String hal, String pnrm, String pkrm, String TglSurat, String TglTerima) {
        this.no = no;
        this.hal = hal;
        this.pnrm = pnrm;
        this.pkrm = pkrm;
        this.TglSurat = TglSurat;
        this.TglTerima = TglTerima;
    }

    public void getDBConnection() throws SQLException {
        OracleDataSource ds;
        ds = new OracleDataSource();
        ds.setURL(jdbcUrl);
        conn = ds.getConnection(userid, password);
    }

    public ResultSet getAllEmployees() throws SQLException {
        getDBConnection();
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//        query = "SELECT * FROM Employees ORDER BY employee_id";
        query = "SELECT first_name FROM Employees ORDER BY employee_id";
        System.out.println("\nExecuting query: " + query);
        rset = stmt.executeQuery(query);
        return rset;
    }

     public static ArrayList searchMail(String keyword) throws SQLException {
        ArrayList result = null;

        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "SELECT * FROM Mail";

        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          DataHandler temp = new DataHandler(rset.getString(1),rset.getString(2),rset.getString(3)
                  ,rset.getString(4),rset.getString(5),rset.getString(6));
            result.add(temp);
        }
        conn.close();
        return result;
    }

    public static ArrayList searchNo(String keyword) throws SQLException {
        ArrayList result = null;

        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "SELECT * FROM Mail where NoSurat like '%" + keyword + "%'";

        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          DataHandler temp = new DataHandler(rset.getString(1),rset.getString(2),rset.getString(3)
                  ,rset.getString(4),rset.getString(5),rset.getString(6));
            result.add(temp);
        }
        conn.close();
        return result;
    }

     public static ArrayList searchHal(String keyword) throws SQLException {
        ArrayList result = null;

        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "SELECT * FROM Mail where LOWER (Perihal) like '%" + keyword + "%'";

        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          DataHandler temp = new DataHandler(rset.getString(1),rset.getString(2),rset.getString(3)
                  ,rset.getString(4),rset.getString(5),rset.getString(6));
            result.add(temp);
        }
        conn.close();
        return result;
    }

      public static ArrayList searchPenerima(String keyword) throws SQLException {
        ArrayList result = null;

        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "SELECT * FROM Mail where LOWER (Penerima) like '%" + keyword + "%'";

        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          DataHandler temp = new DataHandler(rset.getString(1),rset.getString(2),rset.getString(3)
                  ,rset.getString(4),rset.getString(5),rset.getString(6));
            result.add(temp);
        }
        conn.close();
        return result;
    }

       public static ArrayList searchPengirim(String keyword) throws SQLException {
        ArrayList result = null;

        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "SELECT * FROM Mail where LOWER (Pengirim) like '%" + keyword + "%'";

        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          DataHandler temp = new DataHandler(rset.getString(1),rset.getString(2),rset.getString(3)
                  ,rset.getString(4),rset.getString(5),rset.getString(6));
            result.add(temp);
        }
        conn.close();
        return result;
    }

        public static ArrayList searchTglSurat(String keyword) throws SQLException {
        ArrayList result = null;

        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "SELECT * FROM Mail where TglSurat like '%" + keyword + "%'";

        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          DataHandler temp = new DataHandler(rset.getString(1),rset.getString(2),rset.getString(3)
                  ,rset.getString(4),rset.getString(5),rset.getString(6));
            result.add(temp);
        }
        conn.close();
        return result;
    }

         public static ArrayList searchTglTerima(String keyword) throws SQLException {
        ArrayList result = null;

        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "SELECT * FROM Mail where TglTerima like '%" + keyword + "%'";

        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          DataHandler temp = new DataHandler(rset.getString(1),rset.getString(2),rset.getString(3)
                  ,rset.getString(4),rset.getString(5),rset.getString(6));
            result.add(temp);
        }
        conn.close();
        return result;
    }

    public static void add(String no, String hal, String pnrm, String pkrm, String TglSurat, String TglTerima) throws SQLException {
        DataHandler dataHandler = new DataHandler();
        ArrayList result = null;
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        String query = "INSERT INTO Mail VALUES ('" + no + "', '" + hal + "', '"
                + pnrm + "', '" + pkrm + "', '" + TglSurat + "', '"
                + TglTerima + "')";
        result = new ArrayList();
        try {
            result.add(stmt.executeUpdate(query));
        } catch (SQLException ex) {
            System.out.println("error : " + ex.getMessage());
        }
        conn.close();
    }


    /**
     * @return the no
     */
    public String getNo() {
        return no;
    }

    /**
     * @param no the no to set
     */
    public void setNo(String no) {
        this.no = no;
    }

    /**
     * @return the hal
     */
    public String getHal() {
        return hal;
    }

    /**
     * @param hal the hal to set
     */
    public void setHal(String hal) {
        this.hal = hal;
    }

    /**
     * @return the pnrm
     */
    public String getPnrm() {
        return pnrm;
    }

    /**
     * @param pnrm the pnrm to set
     */
    public void setPnrm(String pnrm) {
        this.pnrm = pnrm;
    }

    /**
     * @return the pkrm
     */
    public String getPkrm() {
        return pkrm;
    }

    /**
     * @param pkrm the pkrm to set
     */
    public void setPkrm(String pkrm) {
        this.pkrm = pkrm;
    }

    /**
     * @return the TglSurat
     */
    public String getTglSurat() {
        return TglSurat;
    }

    /**
     * @param TglSurat the TglSurat to set
     */
    public void setTglSurat(String TglSurat) {
        this.TglSurat = TglSurat;
    }

    /**
     * @return the TglTerima
     */
    public String getTglTerima() {
        return TglTerima;
    }

    /**
     * @param TglTerima the TglTerima to set
     */
    public void setTglTerima(String TglTerima) {
        this.TglTerima = TglTerima;
    }

}