Software Java untuk Inspect Database MySQL

Adakalanya untuk melihat isi database ketika melakukan pemrograman dan pecobaan transfer data, dibutuhkan suatu software inspect (memeriksa) database yang dapat diakses secara mudah.

Saat tulisan ini ditulis, penulis sedang mengerjakan project pembuatan aplikasi berbasis web dan Android, dengan aplikasi back-end-nya menggunakan aplikasi Java.

Alasan pemilihan menggunakan Java adalah aplikasi yang dibuat nantinya akan lebih bersifat free, tidak terlalu terbelit masalah lisensi dan konon katanya multi-platform.

Salah satu buktinya adalah app Android yang masih dapat dibuat menggunakan bahasa pemrograman Java.

Untuk membuat software inspect database MySQL, dibutuhkan pembuatan script, yaitu script PHP (Personal Home Page) di sisi server, dan script Java di sisi client atau komputer yang digunakan untuk akses.

Kode PHP digunakan untuk mengakses database MySQL, dan kode Java digunakan untuk mengirim dan menerima hasil eksekusi.

Berikut ini adalah kode PHP dan Java untuk mengakses database:

check_database.php

<?php 
define('DB_SERVER', 'localhost'); 
define('DB_USERNAME', 'dbusername'); 
define('DB_PASSWORD', 'dbpassword'); 
define('DB_DATABASE', 'dbname'); 
define('KEY_APP', 'ILsSpKocwtmqCR5'); 
 
session_start(); 
 
ini_set('display_errors', 1); 
ini_set('display_startup_errors', 1); 
error_reporting(E_ALL); 
 
$dbconnection = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE); 
if ($dbconnection->connect_error){ 
   die("Unable to connect!"); 
} 
 
if(isset($_POST["key_app"]) && $_POST["key_app"]==KEY_APP){ 
 $sql = "SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='".DB_DATABASE."'"; 
 $result = $dbconnection->query($sql); 
 if($result->num_rows>0){ 
  $hasil["name"]=array(); 
  while($row=$result -> fetch_row()){ 
   array_push($hasil["name"],$row[0]); 
  } 
 }else{ 
  $hasil["error"]="Cannot get name of database tables "; 
 } 

 echo json_encode($hasil); 
}else{ 
 if($_POST["key_app"]!=KEY_APP)$hasil["error"]="Unrecognized device!"; 
 else $hasil["error"]="No result"; 
 echo json_encode($hasil); 
} 
$dbconnection->close(); 

check_table.php

<?php 
define('DB_SERVER', 'localhost'); 
define('DB_USERNAME', 'dbusername'); 
define('DB_PASSWORD', 'dbpassword'); 
define('DB_DATABASE', 'dbname'); 
define('KEY_APP', 'ILsSpKocwtmqCR5'); 
 
session_start(); 
 
ini_set('display_errors', 1); 
ini_set('display_startup_errors', 1); 
error_reporting(E_ALL); 
 
$dbconnection = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE); 
if ($dbconnection->connect_error){ 
   die("Unable to connect!"); 
} 
 
if(isset($_POST["key_app"]) && $_POST["key_app"]==KEY_APP){ 
 $sql = "SELECT COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA = '".DB_DATABASE."' AND TABLE_NAME = '".$_POST["table_name"]."'"; 
 $result = $dbconnection->query($sql); 
 if($result->num_rows>0){ 
  $hasil["name"]=array(); 
  while($row=$result -> fetch_row()){ 
   array_push($hasil["name"],$row[0]); 
  } 
 }else{ 
  $hasil["error"]="Cannot get column name of table ".$_POST["table_name"]; 
 } 
 
 if(!empty($hasil["error"])){ 
  die("Unable to get Column name!"); 
 } 

 $sql = "SELECT * FROM ".$_POST["table_name"].""; 
 $result = $dbconnection->query($sql); 
 if($result->num_rows>0){ 
  $hasil["content"]=array(); 
  while($row=$result -> fetch_row()){ 
   array_push($hasil["content"],$row); 
  } 
 }else{ 
  $hasil["error"]="Cannot get content of table ".$_POST["table_name"]." or Null result"; 
 } 

 echo json_encode($hasil); 
}else{ 
 if($_POST["key_app"]!=KEY_APP)$hasil["error"]="Unrecognized device!"; 
 else $hasil["error"]="No result"; 
 echo json_encode($hasil); 
} 
$dbconnection->close(); 

DatabaseView.java

package percobaan; 
 
import AlbertTable.ClipboardKeyAdapter; 
import AlbertTable.TModel; 
import AlbertTable.TableMouseAdapter; 
import java.awt.Dimension; 
import java.awt.FlowLayout; 
import java.awt.Font; 
import java.awt.GridBagConstraints; 
import java.awt.GridBagLayout; 
import java.awt.Image; 
import java.awt.Toolkit; 
import java.awt.event.ActionEvent; 
import java.awt.event.ActionListener; 
import java.awt.event.MouseEvent; 
import java.util.HashMap; 
import java.util.Map; 
import javax.swing.JFrame; 
import javax.swing.JLabel; 
import javax.swing.JMenu; 
import javax.swing.JMenuBar; 
import javax.swing.JMenuItem; 
import javax.swing.JOptionPane; 
import javax.swing.JPanel; 
import javax.swing.JScrollPane; 
import javax.swing.JTable; 
import javax.swing.WindowConstants; 
import javax.swing.table.TableColumn; 
import org.json.simple.JSONArray; 
import org.json.simple.JSONObject; 
 
/** 
 * 
 * @author Edugameapp 
 */ 
public class DatabaseView extends JFrame 
{ 
    private JLabel status=new JLabel("Ready"); 
    private JTable tabledatabase; 
    private JTable tablecontent; 
    private LoopProgressBar progressbar = new LoopProgressBar(); 

 private String URLHOSTING="https//example.com"; 

    private boolean ONCALL=false; 

    public void setup() 
    { 
        setTitle("View Database"); 

        JMenuBar menubar=new JMenuBar(); 
        JMenu menu=new JMenu("File"); 
        JMenuItem jm=new JMenuItem("Call Database"); 
        jm.addActionListener(new ActionListener() { 
            public void actionPerformed(ActionEvent ev){ 
                if(!ONCALL)callDatabase(); 
            } 
        }); 
        menu.add(jm); 
        menubar.add(menu); 
        setJMenuBar(menubar); 

        setLayout(new GridBagLayout()); 
        GridBagConstraints con = new GridBagConstraints(); 

        con.fill = GridBagConstraints.BOTH; 
        con.weightx=1; 
        con.weighty=1; 
        con.gridx=0; 
        con.gridy=0; 

        JPanel panel = new JPanel(); 
        panel.setLayout(new GridBagLayout()); 
        GridBagConstraints con22 = new GridBagConstraints(); 

        con22.fill = GridBagConstraints.BOTH; 
        con22.weightx=0; 
        con22.weighty=1; 
        con22.gridx=0; 
        con22.gridy=0; 

        String[] columnNames = {"Database"}; 
        String[][] Variables = { 
            {""} 
        }; 
        TModel tmodel = new TModel(columnNames, Variables){ 
            public boolean isCellEditable(int row, int col) 
            { 
                return false; 
            } 
        }; 
        tabledatabase = new JTable(tmodel){ 
            public void doLayout() { 
                for (int i = 0; i <getColumnModel().getColumnCount(); i++) { 
                    TableColumn column = getColumnModel().getColumn(i); 
                    column.setPreferredWidth(140); 
                } 
                super.doLayout(); 
            } 
        }; 
        //tabledatabase.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); 
        JScrollPane cpane=new JScrollPane(tabledatabase,JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED); 
        ClipboardKeyAdapter cbcb=new ClipboardKeyAdapter(tabledatabase){ 
            public void sendLaporan(String a) 
            { 
                laporan(a); 
            } 
        }; 
        tabledatabase.addKeyListener(cbcb); 
        tabledatabase.addMouseListener(new TableMouseAdapter(tabledatabase,cbcb){ 
            public void mouseClicked(MouseEvent e) { 
                if (e.getClickCount() == 2) { 
                    int row = tabledatabase.getSelectedRow(); 
                    openTable(row); 
                } 
             } 
        }); 
        cpane.setPreferredSize(new Dimension(200,400)); 
        panel.add(cpane,con22); 

        con22.weightx=1; 
        con22.gridx=1; 

        columnNames = new String[]{"Content"}; 
        Variables = new String[][]{ 
            {""} 
        }; 
        tmodel = new TModel(columnNames, Variables); 
        tablecontent = new JTable(tmodel){ 
            public void doLayout() { 
                for (int i = 0; i <getColumnModel().getColumnCount(); i++) { 
                    TableColumn column = getColumnModel().getColumn(i); 
                    column.setPreferredWidth(140); 
                } 
                super.doLayout(); 
            } 
        }; 
        tablecontent.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); 
        cpane=new JScrollPane(tablecontent,JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED); 
        cbcb=new ClipboardKeyAdapter(tablecontent){ 
            public void sendLaporan(String a) 
            { 
                laporan(a); 
            } 
        }; 
        tablecontent.addKeyListener(cbcb); 
        tablecontent.addMouseListener(new TableMouseAdapter(tablecontent,cbcb)); 
        cpane.setPreferredSize(new Dimension(600,400)); 
        panel.add(cpane,con22); 
        add(panel,con); 

        Font f = status.getFont(); 
        status.setFont(f.deriveFont(f.getStyle() & ~Font.BOLD)); 

        con.weighty=0; 
        con.gridy++; 

        panel = new JPanel(); 
        panel.setLayout(new FlowLayout(FlowLayout.LEFT)); 
        progressbar = new LoopProgressBar(); 
        progressbar.setup(); 
        progressbar.setPreferredSize(new Dimension(16,16)); 
        panel.add(progressbar); 
        panel.add(status); 
        add(panel,con); 

        pack(); 

        Image im = Toolkit.getDefaultToolkit().getImage(System.getProperty("user.dir")+"\\logo.png"); 
        if(im!=null)setIconImage(im); 

        Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize(); 
        setLocation((int)(0.5*(screenSize.getWidth()-getWidth())),(int)(0.5*(screenSize.getHeight()-getHeight()))); 
        setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE); 
        getContentPane().requestFocusInWindow(); 
        setVisible(true); 

        callDatabase(); 
    } 

    private void fillTableName(Object[] arr) 
    { 
        Object[][] data=new Object[arr.length][1]; 
        for(int i=0;i<data.length;i++){ 
            data[i][0]=arr[i]; 
        } 
        ((TModel)tabledatabase.getModel()).setData(data); 
        ((TModel)tabledatabase.getModel()).updateTable(); 
        laporan("Open Database done!"); 
    } 

    private void fillTableContent(Object[] arr,Object[][] cont) 
    { 
        String[] title=new String[arr.length]; 
        for(int i=0;i<title.length;i++){ 
            title[i]=(String)arr[i]; 
        } 
        ((TModel)tablecontent.getModel()).setData(title,cont); 
        ((TModel)tablecontent.getModel()).updateTable(); 
        laporan("Fill content done!"); 
    } 

    private void openTable(int row) 
    { 
        ONCALL=true; 
        progressbar.showup(); 
        laporan("Open table "+(String)tabledatabase.getValueAt(row,0)); 
        Map<String,String> data = new HashMap<>(); 
        data.put("key_app","ILsSpKocwtmqCR5"); 
        data.put("table_name",(String)tabledatabase.getValueAt(row,0)); 
        JSONCaller json=new JSONCaller(URLHOSTING+"check_content.php",data){ 
            public void onFinished() 
            { 
                JSONObject result=getResult(); 
                if(result!=null){ 
                    if(!((String)result.get("error")).isEmpty()){ 
                        laporan((String)result.get("error")); 
                        fillTableContent(new String[]{"Unkown"},new Object[][]{{""}}); 
                    }else{ 
                        JSONArray name=(JSONArray)result.get("name"); 
                        JSONArray content=(JSONArray)result.get("content"); 
                        Object[][] isi=new Object[content.size()][name.size()]; 
                        for(int i=0;i<isi.length;i++){ 
                            JSONArray subcontent=(JSONArray)content.get(i); 
                            for(int j=0;j<isi[i].length;j++){ 
                                isi[i][j]=subcontent.get(j); 
                            } 
                        } 
                        fillTableContent(name.toArray(),isi); 
                    } 
                }else{ 
                    String a=toString(); 
                    if(!a.isEmpty()){ 
                        HTMLViewer v=new HTMLViewer(); 
                        v.setup("DatabaseView",a); 
                    }else showAlert(this.getErrorMessage()); 
                    laporan("Failed"); 
                } 
                progressbar.hidden(); 
                ONCALL=false; 
            } 
        }; 
        //json.setStringMode(true); 
        new Thread(json).start(); 
    } 

    private void callDatabase() 
    { 
        ONCALL=true; 
        progressbar.showup(); 
        laporan("Call database.."); 
        Map<String,String> data = new HashMap<>(); 
        data.put("key_app","ILsSpKocwtmqCR5"); 
        JSONCaller json=new JSONCaller(URLHOSTING+"check_database.php",data){ 
            public void onFinished() 
            { 
                JSONObject result=getResult(); 
                if(result!=null){ 
                    if(!((String)result.get("error")).isEmpty()){ 
                        laporan((String)result.get("error")); 
                    }else{ 
                        JSONArray name=(JSONArray)result.get("name"); 
                        fillTableName(name.toArray()); 
                    } 
                }else{ 
                    String a=toString(); 
                    if(!a.isEmpty()){ 
                        HTMLViewer v=new HTMLViewer(); 
                        v.setup("DatabaseView",a); 
                    }else showAlert(this.getErrorMessage()); 
                    laporan("Failed"); 
                } 
                progressbar.hidden(); 
                ONCALL=false; 
            } 
        }; 
        //json.setStringMode(true); 
        new Thread(json).start(); 
    } 

    private void laporan(String status) 
    { 
        this.status.setText(status); 
    } 

    private void showAlert(String e) 
    { 
        JOptionPane.showMessageDialog(this,"Error: "+e); 
    } 
} 

Dimana penulis menggunakan HttpsURLConnection dan JSON file sebagai komunikasi datanya, dengan class JSONCaller.

JSONCaller.java

package percobaan; 
 
import java.io.BufferedInputStream; 
import java.io.BufferedReader; 
import java.io.DataOutputStream; 
import java.io.IOException; 
import java.io.InputStream; 
import java.io.InputStreamReader; 
import java.io.UnsupportedEncodingException; 
import java.net.URL; 
import java.net.URLEncoder; 
import java.util.Map; 
import javax.net.ssl.HttpsURLConnection; 
import org.json.simple.JSONObject; 
import org.json.simple.parser.JSONParser; 
import org.json.simple.parser.ParseException; 
 
public class JSONCaller implements Runnable 
{ 
    private String ErrorMessage=""; 
    private Map<String,String> data; 
    private String urltarget=""; 
    private boolean STRINGMODE=false; 
    public JSONObject result=null; 
    public String resultString=null; 

    public JSONCaller() 
    { 

    } 

    public JSONCaller(String url,Map<String, String> data) 
    { 
        this.urltarget = url; 
        this.data = data; 
    } 
 
    public Map<String, String> getData() { 
        return data; 
    } 
 
    public void setData(Map<String, String> data) { 
        this.data = data; 
    } 
 
    public String getUrl() { 
        return urltarget; 
    } 
 
    public void setUrltarget(String url) { 
        this.urltarget = url; 
    } 
 
    public JSONObject getResult() { 
        return result; 
    } 
 
    public boolean isStringMode() { 
        return STRINGMODE; 
    } 
 
    public void setStringMode(boolean STRINGMODE) { 
        this.STRINGMODE = STRINGMODE; 
    } 

    public String toString() 
    { 
        return resultString; 
    } 
 
    public String getErrorMessage() { 
        return ErrorMessage; 
    } 

    public void onFinished() 
    { 

    } 

    public void run() 
    { 
        StringBuilder sbParams = new StringBuilder(); 
        int i = 0; 
        for (String key : data.keySet()) { 
            try { 
                if (i != 0){ 
                    sbParams.append("&"); 
                } 
                sbParams.append(key).append("=") 
                        .append(URLEncoder.encode(data.get(key), "UTF-8")); 
 
            } catch (UnsupportedEncodingException e) { 
                ErrorMessage=e.toString(); 
            } 
            i++; 
        } 

        HttpsURLConnection connection=null; 
        try{ 
            URL url = new URL(urltarget); 
            connection = (HttpsURLConnection)url.openConnection(); 
            connection.setReadTimeout(15000); 
            connection.setConnectTimeout(15000); 
            connection.setDoInput(true); 
            connection.setDoOutput(true); 

            String paramsString = sbParams.toString(); 
 
            DataOutputStream wr = new DataOutputStream(connection.getOutputStream()); 
            wr.writeBytes(paramsString); 
            wr.flush(); 
            wr.close(); 

            connection.connect(); 

            int responseCode=connection.getResponseCode(); 
            if (responseCode == HttpsURLConnection.HTTP_OK) { 
                InputStream in = new BufferedInputStream(connection.getInputStream()); 
                if(!STRINGMODE){ 
                    JSONParser jsonParser = new JSONParser(); 
                    result = (JSONObject) jsonParser.parse(new InputStreamReader(in)); 
                }else{ 
                    BufferedReader reader = new BufferedReader(new InputStreamReader(in)); 
                    StringBuilder restring = new StringBuilder(); 
                    String line; 
                    while ((line = reader.readLine()) != null) { 
                        restring.append(line); 
                    } 
                    resultString=restring.toString(); 
                } 
            }else{ 
                ErrorMessage="Error code "+responseCode; 
            } 
        } catch (IOException | ParseException e) { 
            ErrorMessage=e.toString(); 
        } finally { 
          if (connection != null) { 
              connection.disconnect(); 
          } 
        } 
        onFinished(); 
    } 
} 

Sedangkan library AlbertTable dapat di download pada link dalam posting yang berjudul "Membuat Table di Java dengan AlbertTable", class LoopProgressBar pada posting yang berjudul "Menampilkan Undetermined Circle Progressbar di Java" dan library Simple JSON dapat didownload di https://code.google.com/archive/p/json-simple/downloads.