[ Pobierz całość w formacie PDF ]
.The code begins by instantiating the button andlabeling it, and then moves to the action associated with it.In our code exam-ple, we build the event processing code right into the button itself instead ofhaving the application implement the ActionListener interface.When the user clicks on the Get Account button, its ActionListener() will fire.We want the code to pick up the account number currently selected on the JListcontrol and use the value to pull all of the account information from the MySQLdatabase and place that information in the five JTextField controls.To accomplish this, a try/catch block is coded with the database control within it.A Statement object is instantiated from the Connection object, and the execute-Query() method is called.The parameter to the executeQuery() method is theSQL string that we want executed against the MySQL database.The full string isResultSet rs = statement.executeQuery("SELECT * FROM acc_acc WHERE acc_id = "+ accountNumberList.getSelectedValue());As you can see from the string, we have a SELECT statement that will pull allcolumns from the database where the acc_id is equal to the current selectedvalue on the JList control.If the query isn t successful, the catch block is called,but there is no error-handling at the moment.If the SQL was successful andthere is a result in the ResultSet object, each of the JTextField controls are pop-ulated by pulling the database data as String objects using the getString() gettermethods.Creating Text Fields with Account InformationOnce the account list and Get Account button have been created, they areadded to a panel, which is added to the application frame.After that step, ourcode creates five JTextField controls to hold the five column values from a rowin the acc_acc table.These controls are added to a second panel, which is alsoadded to the application frame. Executing a Query with No Results 91Once all of the controls have been created and attached to the application, theframe is sized and displayed to the user.At this point, the user can select anaccount number on the JList control and click on the Get Account button to dis-play the information on the GUI.Figure 5.4 shows an example of what the out-put will look like when this is performed.Figure 5.4 Displaying a full record.Executing a Query with No ResultsUp to now, we have been concentrating on pulling information from the data-base using a SELECT command.Connector/J, SQL, and MySQL also allowinformation to be inserted and updated as needed.The operations of insert,delete, and update are considered no-result queries because they don t return aResultSet object after being executed.For this reason, we don t use the exe-cuteQuery() method but instead use a method called executeUpdate().The sig-nature for the method isint executeUpdate(String SQL);The method accepts a single String parameter, which represents the query to beexecuted.The query shouldn t cause the database server to return a ResultSet,so no SELECTs are allowed.As you can see, the method will return an integervalue after the query is performed.This integer represents the total number ofrows affected by the query.The question arises, though, about the actual query statements that do notreturn a ResultSet.There are quite a few; let s look at the following ones: % %insert Puts a new row into the database table. % %delete Removes a row from the database table. % %update Updates an existing row in the table. % %drop table Removes a complete table from the database. % %create table Builds a new table. % %alter table Changes aspects of the table. 92 Using JDBC with Java Applications and AppletsLet s start with the insert query statement.As we already know, the insert com-mand will allow a new row to be put into a database table.We want to expandour GUI program to allow the user to place an account number, username, andpassword in the appropriate text boxes and click a button to add the informa-tion to the table.Listing 5.3 shows the new code.In addition to the insert but-ton, we have expanded the code to put SQL errors into a JTextArea.import java.awt.*;import java.awt.event.*;import javax.swing.*;import java.sql.*;import java.util.*;public class Accounts extends JFrame {private JButton getAccountButton,insertAccountButton;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"));}Listing 5.3 Our application for inserting a new row.(continues) Executing a Query with No Results 93rs.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 ButtongetAccountButton = 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 ButtoninsertAccountButton = new JButton("Insert Account");insertAccountButton.addActionListener (new ActionListener() {public void actionPerformed(ActionEvent e) {try {Statement statement = connection.createStatement();Listing 5.3 Our application for inserting a new row.(continues) 94 Using JDBC with Java Applications and Appletsint 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);}}});JPanel first = new JPanel(new GridLayout(3,1));first.add(accountNumberListScrollPane);first.add(getAccountButton);first
[ Pobierz całość w formacie PDF ]