Query Database Using Swing UI

SimpleForm.java

Query DB on A Swing Form

DoConnect(): connect to db, run query, get first record into UI

BtnAction(): logic to manipulate the records (NEXT, PRVIOUS, FIRST, LAST)

Rest of codes (SWING UI) are generated by using NeatBean IDE 6.8 on Linux.

/*
 to compile: javac -cp /usr/share/java/postgresql.jar SimpleForm.java
 to execute: java -cp /usr/share/java/postgresql.jar:. SimpleForm
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;

public class SimpleForm extends javax.swing.JFrame {
    Connection con;
    Statement stmt;
    ResultSet rs;

    /** Creates new form SimpleForm */
    public SimpleForm() {
        initComponents();
        DoConnect();
    }

    public void DoConnect( ) {
        try {
            //connect to the database
            con = DriverManager.getConnection("jdbc:postgresql:mydb",
                    "testuser","testpassword");
            // construct a sql statement, execute it and load resultset to rs                                    "logan","ready2go");
            //stmt = con.createStatement();
            stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
            String sql = "select firstname, lastname, name from employees, departments where employees.department = departments.id";
            rs = stmt.executeQuery(sql);
            // retrieve the fields from rs to txtFields
            if (rs.next()) {
               txtFirst.setText(rs.getString("firstname"));
               txtLast.setText(rs.getString("lastname"));
               txtDept.setText(rs.getString("name"));
            }
            else { JOptionPane.showMessageDialog(SimpleForm.this, "No records!");}
        }
        catch (SQLException err) {
            JOptionPane.showMessageDialog(SimpleForm.this, err.getMessage());
        }
    }
    // Next, Previous, First, Last Action Event Handlers
    public void DoBtnAction(int BtnAction ) {
    try {
         if (rs != null)
         {
          boolean Action = false;
          switch (BtnAction) {
            case 1:  Action = rs.next();
                     break;
            case 2:  Action = rs.previous();
                     break;
            case 3:  Action = rs.first();
                     break;
            case 4:  Action = rs.last();
                     break;
            default:
                     break;
            }
          if ( Action ) {
            txtFirst.setText(rs.getString("firstname"));
            txtLast.setText(rs.getString("lastname"));
            txtDept.setText(rs.getString("name"));
                        }
          else {
          switch (BtnAction) {
            case 1:  rs.previous( );
                     JOptionPane.showMessageDialog(SimpleForm.this, "End of File");
                     break;
            case 2:  rs.next( );
                     JOptionPane.showMessageDialog(SimpleForm.this, "Start of File");
                     break;
            default:
                     break;
                             }
              } // end of else for Action
             } // if Action
         else { JOptionPane.showMessageDialog(SimpleForm.this, "No records!");}
        } // try
    catch (SQLException err) {
            JOptionPane.showMessageDialog(SimpleForm.this, err.getMessage());
        }    
    }

    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">
    private void initComponents() {

        txtFirst = new javax.swing.JTextField();
        txtLast = new javax.swing.JTextField();
        txtDept = new javax.swing.JTextField();
        lblFirst = new javax.swing.JLabel();
        lblLast = new javax.swing.JLabel();
        lblDept = new javax.swing.JLabel();
        btnNext = new javax.swing.JButton();
        btnPrevious = new javax.swing.JButton();
        btnFirst = new javax.swing.JButton();
        btnLast = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        lblFirst.setText("First Name:");

        lblLast.setText("Last Name");

        lblDept.setText("Department:");

        btnNext.setText("Next");
        btnNext.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnNextActionPerformed(evt);
            }
        });

        btnPrevious.setText("Previous");
        btnPrevious.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnPreviousActionPerformed(evt);
            }
        });

        btnFirst.setText("First");
        btnFirst.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnFirstActionPerformed(evt);
            }
        });

        btnLast.setText("Last");
        btnLast.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnLastActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(49, 49, 49)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(btnPrevious)
                        .addGap(18, 18, 18)
                        .addComponent(btnNext)
                        .addGap(46, 46, 46)
                        .addComponent(btnFirst)
                        .addGap(18, 18, 18)
                        .addComponent(btnLast)
                        .addContainerGap())
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addGroup(layout.createSequentialGroup()
                            .addComponent(lblDept)
                            .addContainerGap())
                        .addGroup(layout.createSequentialGroup()
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                                .addComponent(txtDept, javax.swing.GroupLayout.DEFAULT_SIZE, 323, Short.MAX_VALUE)
                                .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                        .addComponent(txtFirst, javax.swing.GroupLayout.PREFERRED_SIZE, 126, javax.swing.GroupLayout.PREFERRED_SIZE)
                                        .addComponent(lblFirst))
                                    .addGap(42, 42, 42)
                                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                        .addComponent(lblLast)
                                        .addComponent(txtLast, javax.swing.GroupLayout.PREFERRED_SIZE, 129, javax.swing.GroupLayout.PREFERRED_SIZE))))
                            .addContainerGap(81, Short.MAX_VALUE)))))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(27, 27, 27)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblFirst)
                    .addComponent(lblLast))
                .addGap(27, 27, 27)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(txtLast, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(txtFirst, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(22, 22, 22)
                .addComponent(lblDept)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(txtDept, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(32, 32, 32)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(btnPrevious)
                    .addComponent(btnLast)
                    .addComponent(btnFirst)
                    .addComponent(btnNext))
                .addContainerGap(58, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>

    private void btnNextActionPerformed(java.awt.event.ActionEvent evt) {
        DoBtnAction(1);
    }

    private void btnPreviousActionPerformed(java.awt.event.ActionEvent evt) {
        DoBtnAction(2);
    }

    private void btnFirstActionPerformed(java.awt.event.ActionEvent evt) {
        DoBtnAction(3);
    }

    private void btnLastActionPerformed(java.awt.event.ActionEvent evt) {
        DoBtnAction(4);
    }

    /**
    * @param args the command line arguments
    */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new SimpleForm().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify
    private javax.swing.JButton btnFirst;
    private javax.swing.JButton btnLast;
    private javax.swing.JButton btnNext;
    private javax.swing.JButton btnPrevious;
    private javax.swing.JLabel lblDept;
    private javax.swing.JLabel lblFirst;
    private javax.swing.JLabel lblLast;
    private javax.swing.JTextField txtDept;
    private javax.swing.JTextField txtFirst;
    private javax.swing.JTextField txtLast;
    // End of variables declaration

}

How to bind data to JavaFX Form

SimpleFormFx.java

/*
 * File: SimpleFormFx.java
 *    Create a SimpleForm from a query
 * Author: https://henry416.wordpress.com/
 *
 * Compile: javac -cp "C:\Program Files\Oracle\JavaFX Runtime 2.0\lib\jfxrt.jar" SimpleFormFx.java
 * Execute: java -cp "C:\Program Files\Oracle\JavaFX Runtime 2.0\lib\jfxrt.jar";"Z:\test\javafx\mysql-connector-java-5.1.18\mysql-connector-java-5.1.18-bin.jar";. SimpleFormFx
 * ------------------------------
 * Test Data:
 * mysql -u testuser -p
 * mysql> select * from test.t1;
   +------+------------+
   | name | telephone  |
   +------+------------+
   | John | 4168889999 |
   | Tom  | 4161239999 |
   +------+------------+
   2 rows in set (0.00 sec)
 *--------------------------------
 */
import javafx.application.Application;
import javafx.scene.Group;
import javafx.scene.Scene;
import javafx.scene.paint.Color;
import javafx.stage.Stage;
import javafx.scene.layout.VBox;
import javafx.scene.control.Button;
import javafx.geometry.Insets;
import javafx.scene.layout.BorderPane;
import javafx.scene.text.Text;
import javafx.scene.text.Font;
import javafx.scene.text.FontWeight;
import javafx.scene.control.Label;
import javafx.scene.control.TextField;
import javafx.scene.layout.HBox;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class SimpleFormFx extends Application {

   @Override
   public void start(Stage stage) {
    Group root = new Group();
    Scene scene = new Scene(root,300,200,Color.WHITE);
        Connection con;
        Statement stmt;
        ResultSet rs;
        VBox vbox = new VBox();
        vbox.setPadding(new Insets(10, 10, 10, 10));
        vbox.setSpacing(10);
        // create a title, add it into vbox
        Text title = new Text("Simple Database Form in JavaFX");
        title.setFont(Font.font("Amble CN", FontWeight.BOLD, 12));
        vbox.getChildren().add(title);

        try {
            //connect to the database
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/","testuser","testpassword");
            // construct a sql statement, execute it and load resultset to rs
            stmt = con.createStatement();
            String sql = "select name,telephone from test.t1";
            rs = stmt.executeQuery(sql);

            if (rs != null) {
            // more controls in an array, add them into vbox
            ResultSetMetaData rsMetaData = rs.getMetaData();
            int numberOfColumns = rsMetaData.getColumnCount();
            rs.next();
            // get the column names into labels, fields into textfields, pack them into vbox
            for (int i = 1; i < numberOfColumns + 1; i++) {
              Label lbl= new Label(rsMetaData.getColumnName(i)+":");
              TextField txtField = new TextField(rs.getString(i));
              HBox hbox =new HBox(5);
              hbox.getChildren().addAll(lbl,txtField);
              vbox.getChildren().add(hbox);
            }
            }
        }
        catch (SQLException err) {
            System.err.println (err.getMessage());
        }

       // put VBox onto a border Pane
       BorderPane border = new BorderPane();
       border.setLeft(vbox);
       // add the border pane as a child
       root.getChildren().add(border);

       stage.setTitle("Simple Database Form in JavaFX");
       stage.setScene(scene);
       stage.show();
   }

   public static void main(String[] args) {
       launch(args);
   }
}