JDBC

Kelas DataHandler

import java.sql.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;

public class DataHandler {
// string ‘localhost’ anda ganti dengan IP server yang digunakan
    String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:orcl3";
    String userid = "kejut";
    String password = "105314078";

    Connection conn;
    Statement stmt;
    ResultSet rset;
    String query;
    String sqlString;

    public DataHandler() {
    }

    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";
        System.out.println("\nExecuting query: " + query);
        rset = stmt.executeQuery(query);
        return rset;
    }


    public static void main(String[] args) throws SQLException {
        DataHandler datahandler = new DataHandler();
        ResultSet rset = datahandler.getAllEmployees();
        while (rset.next()) {
            System.out.println(rset.getInt(1) + " " + rset.getString(2) + " " + rset.getString(3) + " " + rset.getString(4));
        }
        datahandler.conn.close();
    }

}



Kelas Regions

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Regions {

    private Integer regionId;
    private String regionName;

    public Regions() {
    }

    public Regions(Integer regionId, String regionName) {
        this.regionId = regionId;
        this.regionName = regionName;
    }

    public static boolean isRegionExist(Integer regionID) throws SQLException {
        boolean result = false;

        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 regions where region_id = " + regionID;

        ResultSet rset = stmt.executeQuery(query);

        rset.next();
        if (rset.isLast()) {
            result = true;
        } else {
            result = false;
        }

        conn.close();
        return result;
    }

    public static boolean addRegions(int id, String kota) throws SQLException {
        int x = 0;
       
        DataHandler dataHandler = new DataHandler();
        dataHandler.getDBConnection();
        Connection conn = dataHandler.conn;
        Statement stat = conn.createStatement();
        String sql = "INSERT INTO Regions values ("+id+",'"+kota+"')";
        x = stat.executeUpdate(sql);
        conn.close();

        if(x>0){
            return true;
        } else {
            return false;
        }         
           
    }

    public static ArrayList searchRegions(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 regions where region_name like '%" + keyword + "%'";
//        System.out.println(query);
        ResultSet rset = stmt.executeQuery(query);
        result = new ArrayList();
        while (rset.next()) {
          Regions temp = new Regions(rset.getInt(1),rset.getString(2));
            result.add(temp);
        }
        conn.close();
        return result;
    }


    /**
     * @return the regionId
     */
    public Integer getRegionId() {
        return regionId;
    }

    /**
     * @param regionId the regionId to set
     */
    public void setRegionId(Integer regionId) {
        this.regionId = regionId;
    }

    /**
     * @return the regionName
     */
    public String getRegionName() {
        return regionName;
    }

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

    public static void main(String[] args) throws SQLException {
        System.out.println("isRegionExist " + Regions.isRegionExist(1));
//        System.out.println(Regions.addRegions(7,"jogja"));
        ArrayList test = Regions.searchRegions("s");
        for (int i = 0; i < test.size(); i++) {
            Regions temp = (Regions) test.get(i);
            System.out.println("id " + temp.getRegionId());
            System.out.println("nama " + temp.getRegionName());

        }
       
    }
}



Kelas RegionsTableModel


import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.table.AbstractTableModel;

public class RegionsTableModel extends AbstractTableModel {

    String columNames[] = {"Nomor", "Regions"};
    ArrayList data;

    public RegionsTableModel(String keyword) throws SQLException {
        data = Regions.searchRegions(keyword);
    }

    public int getRowCount() {
        return data.size();
    }

    public int getColumnCount() {
        return columNames.length;
    }

    @Override
    public String getColumnName(int col) {
        return columNames[col];
    }

    public Object getValueAt(int rowIndex, int columnIndex) {
        Regions temp = (Regions) data.get(rowIndex);
        if (columnIndex == 0) {
            return temp.getRegionId();
        } else {
            return temp.getRegionName();
        }
    }

    @Override
    public Class getColumnClass(int c) {
        return getValueAt(0, c).getClass();
    }

    public static void main(String[] args) throws SQLException {
        RegionsTableModel test = new RegionsTableModel("Americas");
        System.out.println("nama :" + test.getValueAt(0, 1));
    }
}





Kelas SearchPanel


import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.SQLException;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author admin
 */
public class SearchPanel extends JPanel {

    public SearchPanel(String keyWord) throws SQLException {
        super(new GridLayout(1, 0));

        JTable table = new JTable(new RegionsTableModel(keyWord));
        table.setPreferredScrollableViewportSize(new Dimension(500, 70));

        //Create the scroll pane and add the table to it.
        JScrollPane scrollPane = new JScrollPane(table);

        //Add the scroll pane to this panel.
        add(scrollPane);
    }

    public static void main(String args[]) throws SQLException {
        JDialog dialog = new JDialog(new JFrame(), true);
        dialog.addWindowListener(new WindowAdapter() {

            @Override
            public void windowClosing(WindowEvent e) {
                System.exit(0);
            }
        });

        SearchPanel searchPanel = new SearchPanel("Americas");
        dialog.add(searchPanel);

        dialog.setSize(450, 250);
        dialog.setVisible(true);
    }
}