/*
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 RSAccounts extends JFrame {
private JButton getAccountButton, insertAccountButton, deleteAccountButton,
updateAccountButton, nextButton, previousButton, lastButton,
firstButton, gotoButton, freeQueryButton;
private JList accountNumberList;
private JTextField accountIDText, usernameText, passwordText, tsText,
activeTSText, gotoText, freeQueryText;
private JTextArea errorText;
private Connection connection;
private Statement statement;
private ResultSet rs;
public RSAccounts() {
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 {
rs = statement.executeQuery("SELECT * FROM acc_acc");
while (rs.next()) {
v.addElement(rs.getString("acc_id"));
}
} 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);
gotoText = new JTextField(3);
freeQueryText = new JTextField(40);
//Do Get Account Button
getAccountButton = new JButton("Get Account");
getAccountButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
rs.first();
while (rs.next()) {
if (rs.getString("acc_id").equals(
accountNumberList.getSelectedValue()))
break;
}
if (!rs.isAfterLast()) {
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);
}
}
});
//Do Next Button
nextButton = new JButton(">");
nextButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
if (!rs.isLast()) {
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 insertException) {
displaySQLErrors(insertException);
}
}
});
//Do Next Button
previousButton = new JButton("<");
previousButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
if (!rs.isFirst()) {
rs.previous();
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 insertException) {
displaySQLErrors(insertException);
}
}
});
//Do last Button
lastButton = new JButton(">|");
lastButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
rs.last();
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 insertException) {
displaySQLErrors(insertException);
}
}
});
//Do first Button
firstButton = new JButton("|<");
firstButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
rs.first();
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 insertException) {
displaySQLErrors(insertException);
}
}
});
//Do gotoButton
gotoButton = new JButton("Goto");
gotoButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
rs.absolute(Integer.parseInt(gotoText.getText()));
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 insertException) {
displaySQLErrors(insertException);
}
}
});
//Do freeQueryButton
freeQueryButton = new JButton("Execute Query");
freeQueryButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
if (freeQueryText.getText().toUpperCase().indexOf("SELECT") >= 0) {
rs = statement.executeQuery(freeQueryText.getText());
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"));
}
} else {
int i = statement
.executeUpdate(freeQueryText.getText());
errorText.append("Rows affected = " + i);
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));
JPanel fourth = new JPanel();
fourth.add(firstButton);
fourth.add(previousButton);
fourth.add(nextButton);
fourth.add(lastButton);
fourth.add(gotoText);
fourth.add(gotoButton);
JPanel fifth = new JPanel();
fifth.add(freeQueryText);
c.add(first);
c.add(second);
c.add(third);
c.add(fourth);
c.add(fifth);
c.add(freeQueryButton);
setSize(500, 500);
show();
}
public void connectToDB() {
try {
connection = DriverManager
.getConnection("jdbc:mysql://192.168.1.25/accounts?user=spider&password=spider");
statement = connection.createStatement();
} catch (SQLException connectException) {
System.out.println(connectException.getMessage());
System.out.println(connectException.getSQLState());
System.out.println(connectException.getErrorCode());
System.exit(1);
}
}
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) {
RSAccounts accounts = new RSAccounts();
accounts.addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent e) {
System.exit(0);
}
});
accounts.init();
accounts.buildGUI();
}
}
|