Java and Database (NetBeans)

An awesome tutorial from here: http://www.homeandlearn.co.uk/java/java.html (See section 12 and 13 at the bottom of tutorial page).

/*
 * Source code was from Home and Learn Tutorial
 *  Section 12 - 13: Java and Database
 *  http://www.homeandlearn.co.uk/java/java.html
 *
 * Notes:
 *  1. Must include derbyclient.jar
 *  2. Database server must already be connected and running
 *  3. The code has been slightly refactored
 *  4. This is a form application
 */

package Employees;

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 Workers extends javax.swing.JFrame {
    Connection con;
    Statement stmt;
    ResultSet rs;
    int curRow;

    public Workers() {
        initComponents();
        DoConnect();
        curRow = 0;
    }

    public void DoConnect() {
        try {
            String host = "jdbc:derby://localhost:1527/Employees";
            String username = "ad";
            String password = "ad";
            con = DriverManager.getConnection( host, username, password );
            createStatementAndResultSet();
            setFields();
        } catch (SQLException err) {
            System.out.println( err.getMessage() );
        }
    }
    private void createStatementAndResultSet() throws SQLException {
        stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
        String SQL = "SELECT * FROM Workers";
        rs = stmt.executeQuery( SQL );
        rs.next();
    }
    private void setFields() throws SQLException {
        int id_col = rs.getInt("ID");
        String id = Integer.toString(id_col);
        String first_name = rs.getString("First_Name");
        String last_name = rs.getString("Last_Name");
        String job = rs.getString("Job_Title");

        textID.setText(id);
        textFirstName.setText(first_name);
        textLastName.setText(last_name);
        textJobTitle.setText(job);
    }

    private void btnFirstActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            rs.first();
            setFields();
        } catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }
    private void btnNextActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            if ( rs.next( ) ) {
                setFields();
            }
            else {
                rs.previous( ); //move the cursor back to the last item
                JOptionPane.showMessageDialog(Workers.this, "End of File");
            }
        }
        catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }
    private void btnPreviousActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            if ( rs.previous( ) ) {
                setFields();
            }
            else {
                rs.next(); //move the cursor back to the first item
                JOptionPane.showMessageDialog(Workers.this, "Start of File");
            }
        }
        catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }
    private void btnLastActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            rs.last();
            setFields();
        } catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }

    private void btnUpdateRecordActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            updateFieldsBeforeCommit();
            rs.updateRow();
            JOptionPane.showMessageDialog(Workers.this, "Updated");

        } catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }
    private void updateFieldsBeforeCommit() throws SQLException {
        String ID = textID.getText();
        int newID = Integer.parseInt(ID);

        String first = textFirstName.getText();
        String last = textLastName.getText();
        String job = textJobTitle.getText();

        rs.updateInt("ID", newID);
        rs.updateString("First_Name", first);
        rs.updateString("Last_Name", last);
        rs.updateString("Job_Title", job);
    }

    private void btnNewRecordActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            curRow = rs.getRow();
            textFirstName.setText("");
            textLastName.setText("");
            textJobTitle.setText("");
            textID.setText("");
            enableNewRecordActionButtons(true);
        } catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }
    private void btnCancelNewRecordActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            rs.absolute(curRow);  //to move the cursor back to the row that was previously pointed to
            setFields();
            enableNewRecordActionButtons(false);
        } catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }
    private void btnSaveRecordActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            rs.moveToInsertRow();   //move the record for a new record
            updateFieldsBeforeCommit();
            rs.insertRow();

            stmt.close();//Statement and ResultSet must be closed to ensure that the data is added correctly
            rs.close();

            createStatementAndResultSet();
            setFields();

            enableNewRecordActionButtons(false);
        } catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }

    }
    private void enableNewRecordActionButtons(boolean enable) {
        boolean disable = !enable;

        btnFirst.setEnabled(disable);
        btnPrevious.setEnabled(disable);
        btnNext.setEnabled(disable);
        btnLast.setEnabled(disable);

        btnUpdateRecord.setEnabled(disable);
        btnDeleteRecord.setEnabled( disable );
        btnNewRecord.setEnabled( disable );

        btnSaveRecord.setEnabled( enable );
        btnCancelNewRecord.setEnabled( enable );
    }

    private void btnDeleteRecordActionPerformed(java.awt.event.ActionEvent evt) {
        try {
            rs.deleteRow();

            stmt.close();
            rs.close();

            createStatementAndResultSet(); //update and reload the result set and statement
            setFields();
        } catch (SQLException err) {
            JOptionPane.showMessageDialog(Workers.this, err.getMessage());
        }
    }

    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new Workers().setVisible(true);
            }
        });
    }
}

db

Advertisements