Accounts.java Source code

Introduction

Here is the source code for Accounts.java

Source

/*
    
MySQL and Java Developer's Guide
    
Mark Matthews, Jim Cole, Joseph D. Gradecki
Publisher Wiley,
Published February 2003, 
ISBN 0471269239
    
*/

import java.awt.Container;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;

public class Accounts extends JFrame {

    private JButton getAccountButton, insertAccountButton, deleteAccountButton, updateAccountButton;

    private JList accountNumberList;

    private Connection connection;

    private JTextField accountIDText, usernameText, passwordText, tsText, activeTSText;

    private JTextArea errorText;

    public Accounts() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception e) {
            System.err.println("Unable to find and load driver");
            System.exit(1);
        }
    }

    private void loadAccounts() {
        Vector v = new Vector();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT acc_id FROM acc_acc");

            while (rs.next()) {
                v.addElement(rs.getString("acc_id"));
            }
            rs.close();
        } catch (SQLException e) {
            displaySQLErrors(e);
        }
        accountNumberList.setListData(v);
    }

    private void buildGUI() {
        Container c = getContentPane();
        c.setLayout(new FlowLayout());
        accountNumberList = new JList();
        loadAccounts();
        accountNumberList.setVisibleRowCount(2);
        JScrollPane accountNumberListScrollPane = new JScrollPane(accountNumberList);

        //Do Get Account Button
        getAccountButton = new JButton("Get Account");
        getAccountButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try {
                    Statement statement = connection.createStatement();
                    ResultSet rs = statement.executeQuery(
                            "SELECT * FROM acc_acc WHERE acc_id = " + accountNumberList.getSelectedValue());
                    if (rs.next()) {
                        accountIDText.setText(rs.getString("acc_id"));
                        usernameText.setText(rs.getString("username"));
                        passwordText.setText(rs.getString("password"));
                        tsText.setText(rs.getString("ts"));
                        activeTSText.setText(rs.getString("act_ts"));
                    }
                } catch (SQLException selectException) {
                    displaySQLErrors(selectException);
                }
            }
        });

        //Do Insert Account Button
        insertAccountButton = new JButton("Insert Account");
        insertAccountButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try {
                    Statement statement = connection.createStatement();
                    int i = statement.executeUpdate("INSERT INTO acc_acc VALUES(" + accountIDText.getText() + ", "
                            + "'" + usernameText.getText() + "', " + "'" + passwordText.getText() + "', " + "0"
                            + ", " + "now())");
                    errorText.append("Inserted " + i + " rows successfully");
                    accountNumberList.removeAll();
                    loadAccounts();
                } catch (SQLException insertException) {
                    displaySQLErrors(insertException);
                }
            }
        });

        //Do Delete Account Button
        deleteAccountButton = new JButton("Delete Account");
        deleteAccountButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try {
                    Statement statement = connection.createStatement();
                    int i = statement.executeUpdate(
                            "DELETE FROM acc_acc WHERE acc_id = " + accountNumberList.getSelectedValue());
                    errorText.append("Deleted " + i + " rows successfully");
                    accountNumberList.removeAll();
                    loadAccounts();
                } catch (SQLException insertException) {
                    displaySQLErrors(insertException);
                }
            }
        });

        //Do Update Account Button
        updateAccountButton = new JButton("Update Account");
        updateAccountButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try {
                    Statement statement = connection.createStatement();
                    int i = statement.executeUpdate("UPDATE acc_acc " + "SET username='" + usernameText.getText()
                            + "', " + "password='" + passwordText.getText() + "', " + "act_ts = now() "
                            + "WHERE acc_id = " + accountNumberList.getSelectedValue());
                    errorText.append("Updated " + i + " rows successfully");
                    accountNumberList.removeAll();
                    loadAccounts();
                } catch (SQLException insertException) {
                    displaySQLErrors(insertException);
                }
            }
        });

        JPanel first = new JPanel(new GridLayout(5, 1));
        first.add(accountNumberListScrollPane);
        first.add(getAccountButton);
        first.add(insertAccountButton);
        first.add(deleteAccountButton);
        first.add(updateAccountButton);

        accountIDText = new JTextField(15);
        usernameText = new JTextField(15);
        passwordText = new JTextField(15);
        tsText = new JTextField(15);
        activeTSText = new JTextField(15);
        errorText = new JTextArea(5, 15);
        errorText.setEditable(false);

        JPanel second = new JPanel();
        second.setLayout(new GridLayout(6, 1));
        second.add(accountIDText);
        second.add(usernameText);
        second.add(passwordText);
        second.add(tsText);
        second.add(activeTSText);

        JPanel third = new JPanel();
        third.add(new JScrollPane(errorText));

        c.add(first);
        c.add(second);
        c.add(third);
        setSize(500, 500);
        show();
    }

    public void connectToDB() {
        try {
            connection = DriverManager
                    .getConnection("jdbc:mysql://192.168.1.25/accounts?user=spider&password=spider");
        } catch (SQLException connectException) {
            System.out.println(connectException.getMessage());
            System.out.println(connectException.getSQLState());
            System.out.println(connectException.getErrorCode());

        }
    }

    private void displaySQLErrors(SQLException e) {
        errorText.append("SQLException: " + e.getMessage() + "\n");
        errorText.append("SQLState:     " + e.getSQLState() + "\n");
        errorText.append("VendorError:  " + e.getErrorCode() + "\n");
    }

    private void init() {
        connectToDB();
    }

    public static void main(String[] args) {
        Accounts accounts = new Accounts();

        accounts.addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e) {
                System.exit(0);
            }
        });

        accounts.init();
        accounts.buildGUI();
    }
}