Menampilkan Struktur Tabel MySQL dengan JTree

Tertarik menampilkan struktur table database MySQL dalam bentuk tree, penulis melakukan percobaan memanggil database menggunakan JTree.

Penggunaan JTree memang karena aplikasi yang sedang penulis bangun adalah berbasis web app, Android dan dengan back-end berupa dekstop menggunakan Java.

Seperti yang telah disebutkan dalam posting-posting sebelumnya, penggunaan Java tidak lain karena penulis ingin tidak terlalu ribet masalah lisensi dikemudian hari.

Diharapkan dengan pembuatan dengan platform yang mayoritas 'free', distribusi aplikasi nantinya dapat dilakukan lebih mudah dan bebas.

Desain

Skenario utama pembuatan back-end aplikasi penulis, adalah bagaimanapun juga database yang digunakan harus satu, dan dapat diakses baik dari sisi web, Android dan juga software.

Oleh karena itu penulis menggunakan database MySQL (My-Structured Query Language), dengan menggunakan komunikasi data berupa JSON (JavaScript Object Notation), yang di akses dan di encode menggunakan PHP (Personal Home Page).

Dengan menggunakan library simple JSON dari Google Archive dan compiler Java Netbeans, desain interface tampilan software Java-nya adalah sebagaimana gambar di atas.

Skema Kerja

Gambar dibawah ini adalah alur komunikasi data mulai dari sisi software, sampai ke database MySQL, dan kembali ke sisi software lagi.

Halaman fortreeinspect.php adalah jembatan komunikasi antara aplikasi Java ke MySQL dan sebaliknya menggunakan fungsi HttpsURLConnection, di mana dari MySQL ke Java dalam bentuk JSON.

Halaman ini perlu disematkan (upload) terlebih dahulu ke server, dan alamatnya menjadi isi dari kolom URL Page pada aplikasi.

Kemudian parameter lain yang perlu dikirimkan, termasuk nama database, username, serta password dengan metode POST, yang mana parameter-parameter ini perlu diisikan pada kolom yang telah disediakan.

Di bawah ini adalah kode file fortreeinspect.php dan TreeWPDatabase.java.

fortreeinspect.php

<?php 
session_start(); 
 
ini_set('display_errors', 1); 
ini_set('display_startup_errors', 1); 
error_reporting(E_ALL); 
 
$hasil["error"]=""; 
$dbconnection = new mysqli("localhost",$_POST["dbusername"],$_POST["dbpassword"],$_POST["dbdatabase"]); 
if ($dbconnection->connect_error){ 
 $hasil["error"]="Cannot connect!"; 
 echo json_encode($hasil); 
 die(); 
} 
 
function getTable($dbs,$name,$dbb) 
{ 
 $hslo=array(); 
 $sqlo = "SELECT COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA = '".$dbb."' AND TABLE_NAME = '".$name."'"; 
 $resulto = $dbs->query($sqlo); 
 if($resulto->num_rows>0){ 
  while($rowo=$resulto -> fetch_row()){ 
   array_push($hslo,$rowo[0]); 
  } 
 } 
 return $hslo; 
} 
 
$sql = "SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='".$_POST["dbdatabase"]."'"; 
$result = $dbconnection->query($sql); 
if($result->num_rows>0){ 
 $hasil["database"]=array(); 
 while($row=$result -> fetch_row()){ 
  $hsl["name"]=$row[0]; 
  $hsl["table"]=getTable($dbconnection,$row[0],$_POST["dbdatabase"]); 
  array_push($hasil["database"],$hsl); 
 } 
} 
 
echo json_encode($hasil); 
$dbconnection->close(); 
?> 

TreeWPDatabase.java

package treewpdatabase; 
 
import java.awt.Dimension; 
import java.awt.FlowLayout; 
import java.awt.GridBagConstraints; 
import java.awt.GridBagLayout; 
import java.awt.GridLayout; 
import java.awt.Toolkit; 
import java.awt.event.ActionEvent; 
import java.awt.event.ActionListener; 
import java.util.HashMap; 
import java.util.Map; 
import javax.swing.JButton; 
import javax.swing.JFrame; 
import javax.swing.JLabel; 
import javax.swing.JOptionPane; 
import javax.swing.JPanel; 
import javax.swing.JScrollPane; 
import javax.swing.JTextField; 
import javax.swing.JTree; 
import javax.swing.WindowConstants; 
import javax.swing.tree.DefaultMutableTreeNode; 
import javax.swing.tree.DefaultTreeModel; 
import org.json.simple.JSONArray; 
import org.json.simple.JSONObject; 
 
/** 
 * 
 * @author Edugameapp 
 */ 
public class TreeWPDatabase extends JFrame 
{ 
    private JTextField dbhost=new JTextField(15),dbusername=new JTextField(15),dbpassword=new JTextField(15),dbdatabase=new JTextField(15); 
    private JTree mytree=new JTree(); 
    private JLabel status=new JLabel("Ready"); 
    private LoopProgressBar progressbar; 

    public void setup() 
    { 
        setTitle("Tree WPDatabase"); 

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

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

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

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

        JLabel jl=new JLabel("URL Page"); 
        jl.setHorizontalAlignment(JTextField.RIGHT); 
        panel.add(jl,con2); 
        con2.gridy++; 
        jl=new JLabel("Username"); 
        jl.setHorizontalAlignment(JTextField.RIGHT); 
        panel.add(jl,con2); 
        con2.gridy++; 
        jl=new JLabel("Password"); 
        jl.setHorizontalAlignment(JTextField.RIGHT); 
        panel.add(jl,con2); 
        con2.gridy++; 
        jl=new JLabel("Database"); 
        jl.setHorizontalAlignment(JTextField.RIGHT); 
        panel.add(jl,con2); 

        con2.fill = GridBagConstraints.BOTH; 
        con2.gridx++; 
        con2.gridy=0; 
        panel.add(dbhost,con2); 
        con2.gridy++; 
        panel.add(dbusername,con2); 
        con2.gridy++; 
        panel.add(dbpassword,con2); 
        con2.gridy++; 
        panel.add(dbdatabase,con2); 
        con2.gridy++; 
        JButton button=new JButton("Submit"); 
        button.addActionListener(new ActionListener() { 
            public void actionPerformed(ActionEvent ev){ 
                makeTree(); 
            } 
        }); 
        panel.add(button,con2); 

        add(panel,con); 

        panel=new JPanel(); 
        panel.setLayout(new GridLayout()); 
        con.weightx=1; 
        con.gridx++; 
        JScrollPane cpane=new JScrollPane(mytree,JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED); 
        cpane.setPreferredSize(new Dimension(200,512)); 
        panel.add(cpane); 
        add(panel,con); 

        con.weighty=0; 
        con.gridx=0; 
        con.gridy++; 
        con.gridwidth=3; 

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

        setSize(512,512); 
        pack(); 
 
        Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize(); 
        setLocation((int)(0.5*(screenSize.getWidth()-getWidth())),(int)(0.5*(screenSize.getHeight()-getHeight()))); 
        setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE); 
        getContentPane().requestFocusInWindow(); 

        setVisible(true); 
    } 

    private void makeTree() 
    { 
        progressbar.showup(); 
        laporan("Start.."); 
        Map<String,String> data = new HashMap<>(); 
        data.put("dbusername",dbusername.getText()); 
        data.put("dbpassword",dbpassword.getText()); 
        data.put("dbdatabase",dbdatabase.getText()); 
        JSONCaller json=new JSONCaller(dbhost.getText(),data){ 
            public void onFinished() 
            { 
                JSONObject result=getResult(); 
                if(result!=null){ 
                    if(!((String)result.get("error")).isEmpty()){ 
                        showAlert((String)result.get("error")); 
                        laporan("Failed"); 
                    }else{ 
                        JSONArray subroot=(JSONArray)result.get("database"); 
                        DefaultTreeModel model = (DefaultTreeModel)mytree.getModel(); 
                        DefaultMutableTreeNode root=(DefaultMutableTreeNode)model.getRoot(); 
                        root.removeAllChildren(); 
                        root.setUserObject(dbdatabase.getText()); 

                        for(int i=0;i<subroot.size();i++){ 
                            JSONObject subbase=(JSONObject)subroot.get(i); 
                            String name=(String)subbase.get("name"); 
                            DefaultMutableTreeNode rootname = new DefaultMutableTreeNode(name); 
                            JSONArray tables=(JSONArray)subbase.get("table"); 
                            for(int j=0;j<tables.size();j++){ 
                                DefaultMutableTreeNode roottable = new DefaultMutableTreeNode((String)tables.get(j)); 
                                rootname.add(roottable); 
                            } 
                            root.add(rootname); 
                        } 
                        model.nodeStructureChanged(root); 
                        model.reload(); 
                        mytree.treeDidChange(); 
                        laporan("Done!"); 
                    } 
                }else{ 
                    showAlert(this.getErrorMessage()); 
                    laporan("Failed"); 
                } 
                progressbar.hidden(); 
            } 
        }; 
        //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); 
    } 

    public static void main(String[] args) { 
        TreeWPDatabase mainwin=new TreeWPDatabase(); 
        mainwin.setup(); 
    } 
} 

Berikut ini adala class tambahan JSONCaller.java, LoopProgressBar.java dan ProgressCircleUI.java.

JSONCaller.java

package treewpdatabase; 
 
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(); 
    } 
} 

LoopProgressBar.java

package treewpdatabase; 
 
import java.awt.event.ActionEvent; 
import java.awt.event.ActionListener; 
import javax.swing.BorderFactory; 
import javax.swing.JProgressBar; 
import javax.swing.Timer; 
 
/** 
 * 
 * @author Edugame 
 */ 
public class LoopProgressBar extends JProgressBar 
{ 
    private Timer timer; 
    private int modeProgressbar=1; 
    private boolean STOP=false; 

    public LoopProgressBar() 
    { 
        setUI(new ProgressCircleUI()); 
        ((ProgressCircleUI)getUI()).setStartAngle(90); 
        setBorder(BorderFactory.createEmptyBorder(0,0,0,0)); 
    } 

    public void setup() 
    { 
        timer = new Timer(10, new ActionListener() { 
            @Override 
            public void actionPerformed(ActionEvent e) { 
                int iv = getValue(); 
                if(modeProgressbar>0){ 
                    iv++; 
                }else{ 
                    iv--; 
                } 
                iv = Math.max(0,Math.min(100,iv)); 
                setValue(iv); 
                if (getValue() == 100) { 
                    modeProgressbar*=-1; 
                    ((ProgressCircleUI)getUI()).setReverse(-modeProgressbar); 
                }else if (getValue() == 0) { 
                    modeProgressbar*=-1; 
                    ((ProgressCircleUI)getUI()).setReverse(-modeProgressbar); 
                } 
            } 
        }); 
        timer.start(); 
    } 

    public void showup() 
    { 
        if(!timer.isRunning())timer.restart(); 
        setVisible(true); 
    } 

    public void hidden() 
    { 
        if(!timer.isRunning())timer.stop(); 
        setVisible(false); 
    } 
} 

ProgressCircleUI.java

package treewpdatabase; 
 
import java.awt.BasicStroke; 
import java.awt.Dimension; 
import java.awt.Graphics; 
import java.awt.Graphics2D; 
import java.awt.Insets; 
import java.awt.RenderingHints; 
import javax.swing.JComponent; 
import javax.swing.plaf.basic.BasicProgressBarUI; 
 
/** 
 * 
 * @author Edugameapp 
 */ 
public class ProgressCircleUI extends BasicProgressBarUI 
{ 
    private int startAngle=0; 
    private int reverse=-1; 
    private int lineWidth=2; 

    public Dimension getPreferredSize(JComponent c) { 
        Dimension d = super.getPreferredSize(c); 
        int v = Math.max(d.width, d.height); 
        d.setSize(v, v); 
        return d; 
    } 
 
    public void paint(Graphics g, JComponent c) { 
        Insets b = progressBar.getInsets(); // area for border 
        int barRectWidth = progressBar.getWidth() - b.right - b.left; 
        int barRectHeight = progressBar.getHeight() - b.top - b.bottom; 
        if (barRectWidth <= 0 || barRectHeight <= 0) { 
            return; 
        } 
 
        // draw the cells 
        Graphics2D g2 = (Graphics2D) g.create(); 
        g2.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON); 
        g2.setPaint(progressBar.getForeground()); 
        double degree = 360 * progressBar.getPercentComplete(); 
        double sz = Math.min(barRectWidth, barRectHeight); 
        g2.setStroke(new BasicStroke(2*lineWidth)); 
        g2.drawArc(lineWidth,lineWidth,(int)(sz-2*lineWidth),(int)(sz-2*lineWidth),startAngle,(int)(reverse*degree)); 
        g2.dispose(); 
 
        // Deal with possible text painting 
        if (progressBar.isStringPainted()) { 
            paintString(g, b.left, b.top, barRectWidth, barRectHeight, 0, b); 
        } 
    } 
 
    public int getStartAngle() { 
        return startAngle; 
    } 
 
    public void setStartAngle(int startAngle) { 
        this.startAngle = startAngle; 
    } 
 
    public int getReverse() { 
        return reverse; 
    } 
 
    public void setReverse(int reverse) { 
        this.reverse = reverse; 
    } 
 
    public void setLineWidth(int lineWidth) { 
        this.lineWidth = lineWidth; 
    } 
} 

Sedangkan library simple JSON dapat di download di link Google Archive berikut ini:

https://code.google.com/archive/p/json-simple/downloads

Pertanyaan dapat dilayangkan ke email uboiz@yahoo.com.