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 from database with Java Swing UI

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
}

PostGIS

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS “spatially enables” the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI’s SDE or Oracle’s Spatial extension. PostGIS follows the OpenGIS “Simple Features Specification for SQL”.

1. Installation on Ubuntu

download and install the following package using Synaptic Package Manager:

1.1. postgis

geographic objects support for PostgreSQL — common files. This package contains the PostGIS userland binaries, common files and documentation.

1.2. postgressql-8.4-postgis

geographic objects support for PostgreSQL 8.4. This package supports PostgreSQL 8.4.

1.3. libpostgis-java

geographic objects support for PostgreSQL — JDBC support. This package contains JDBC support for PostGIS.

2. What Installed

The following packages were installed:

libgeos-3.2.0 (3.2.0-1)
libgeos-c1 (3.2.0-1)
libpostgis-java (1.5.1-5)
libproj0 (4.7.0-1)
postgis (1.5.1-5)
postgresql-8.4-postgis (1.5.1-5)
proj-data (4.7.0-1)

Where?

sudo find / -name 'postgis.sql' -print
/usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
~$ cd test
~/test$ ls /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
/usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
~/test$ ls /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
/usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
~/test$ ls /usr/share/postgresql/8.4/contrib/postgis_comments.sql
/usr/share/postgresql/8.4/contrib/postgis_comments.sql
sudo find / -name 'postgis.jar' -print
/usr/share/java/postgis.jar

3. Setup your database

3.1. create a simple PostgreSQL database

createdb mydb

3.2. enable the PL/pgSQL language in the database

createlang plpgsql mydb

3.3. load the PostGIS object and function definitions into the database

psql -d mydb -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql

3.4. populate a complete set of EPSG coordinate system definition identifiers into spatial_ref_sys table

psql -d mydb -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

3.5. add comments to the PostGIS functions

psql -d mydb -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

3.6. check

~/test$ psql mydb
psql (8.4.10)
Type "help" for help.
mydb=#SELECT PostGIS_Full_Version();
postgis_full_version
-------------------------------------------------------------------------------------------------------
POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS
(1 row)

4. Create a geographic table

4.1. create a table

CREATE TABLE global_points (

id SERIAL PRIMARY KEY,

name VARCHAR(64),

location GEOGRAPHY(POINT,4326)

);

mydb=# CREATE TABLE global_points (
mydb(# id SERIAL PRIMARY KEY,
mydb(# name VARCHAR(64),
mydb(# location GEOGRAPHY(POINT,4326)
mydb(# );
NOTICE: CREATE TABLE will create implicit sequence "global_points_id_seq" for serial column "global_points.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "global_points_pkey" for table "global_points"
CREATE TABLE

4.2. check  GEOGRAPHY_COLUMNS table

SELECT * FROM geography_columns;
f_table_catalog | f_table_schema | f_table_name | f_geography_column | coord_dimension | srid | type
-----------------+----------------+---------------+--------------------+-----------------+------+-------
mydb | public | global_points | location | 2 | 4326 | Point
(1 row)

4.3.  Add some data into the test table

mydb=# INSERT INTO global_points (name, location) VALUES ('Town', ST_GeographyFromText('SRID=4326;POINT(-110 30)') );
INSERT 0 1
mydb=# INSERT INTO global_points (name, location) VALUES ('Forest', ST_GeographyFromText('SRID=4326;POINT(-109 29)') );
INSERT 0 1
mydb=# INSERT INTO global_points (name, location) VALUES ('London', ST_GeographyFromText('SRID=4326;POINT(0 49)') );
INSERT 0 1

4.4. Create an index

mydb=# CREATE INDEX global_points_gix ON global_points USING GIST ( location );

4.5. Query Geo-Data

-- Show a distance query and note, London is outside the 1000km tolerance
SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);
-- Distance calculation using GEOGRAPHY (122.2km)
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography);
mydb=# SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);
name
--------
Town
Forest
(2 rows)
mydb=# SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography);
st_distance
-----------------
122235.23814596
(1 row)
mydb=# SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)':: geometry);
st_distance
------------------
13.3422712214536
(1 row)

5. Explore More…

PostGIS 1.5.3. Manual is always the starting point.

GIS_for_Web_Developers is a must-read book..