Query Database Using Swing UI

January 12, 2012 1 Comment

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

}

Filed under Databases, Java, PostgreSQL Tagged with , , , , ,

How to bind data to JavaFX Form

January 10, 2012 Leave a comment

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);
   }
}
 

Filed under Databases, Java, JavaFX, mysql, MySQL Tagged with , , , ,

How to bind data from database with Java Swing UI

January 10, 2012 1 Comment

I have the following data in a PostgreSQL database called ‘mydb’ on Linux.

~$ psql mydb
psql (8.4.9)
Type "help" for help.

mydb=# select * from employees;
 id |   lastname   | firstname | department
----+--------------+-----------+------------
  1 | Werner       | Max       |          1
  2 | Lehmann      | Daniel    |          2
  3 | Roetzel      | David     |          1
  4 | Scherfgen    | David     |          2
  5 | Kupfer       | Andreas   |          2
  6 | Scheidweiler | Najda     |          2
  7 | Jueppner     | Daniela   |          4
  8 | Hasse        | Peter     |          4
  9 | Siebigteroth | Jennifer  |          3
(9 rows)

mydb=# select * from departments;
 id |    name    
----+------------
  1 | Management
  2 | R&D
  3 | Marketing
  4 | Accounting
(4 rows)

mydb=# select firstname, lastname, name
mydb-# from employees, departments
mydb-# where employees.department = departments.id;
 firstname |   lastname   |    name    
-----------+--------------+------------
 David     | Roetzel      | Management
 Max       | Werner       | Management
 Najda     | Scheidweiler | R&D
 Andreas   | Kupfer       | R&D
 David     | Scherfgen    | R&D
 Daniel    | Lehmann      | R&D
 Jennifer  | Siebigteroth | Marketing
 Peter     | Hasse        | Accounting
 Daniela   | Jueppner     | Accounting
(9 rows)

mydb=#

The following is the Java program which consists of two parts: the bottom part  (initComponents()) is the UI generated by using the Netbean IDE, and the upper part is the java program (DoConnect() ) to connect to a database mydb in PostgreSQL, and run a query, and then bind the data (first record) to the text fields on the UI.  (Basically, it should work with all the JDBC compliance DB like mysql, derby, Oracle, Sybase, UDB or Microsoft SQL. Just replace the connection string, and put the relevant JDBC driver jar file in the classpath. Also, the compile and execute command are for Linux.)

SimpleForm (Data Binding Using Java Swing)
SimpleForm (Data Binding Using Java Swing)
SimpleForm.java 
/*
 Written by: https://henry416.wordpress.com/
 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
            stmt = con.createStatement();
            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
   rs.next();
            txtFirst.setText(rs.getString("firstname"));
            txtLast.setText(rs.getString("lastname"));
            txtDept.setText(rs.getString("name"));
        }
        catch (SQLException err) {
            JOptionPane.showMessageDialog(SimpleForm.this, err.getMessage());
        }
    }
    /**
    * @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);
            }
        });
    }
 /*
  The following is GUI generated from Netbean
 */
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @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();
        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        lblFirst.setText("First Name:");
        lblLast.setText("Last Name");
        lblDept.setText("Department:");
        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(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)
                .addContainerGap(120, Short.MAX_VALUE))
        );
        pack();
    }// </editor-fold>

    // Variables declaration - do not modify
    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
}

Filed under Databases, Java, Linux, PostgreSQL Tagged with , , , ,

Microsoft SQL Server

December 6, 2011 Leave a comment

  1. Database design and development in T-SQL, please refer to my T-SQL script List which will be growing as I tested on SQL 2008, SQL 2008 R2, SQL 2012…
  2. Database adminstration tasks

Filed under Microsoft SQL Tagged with , ,

Design a site like this with WordPress.com
Get started