MySQL Developing And Programming Blog

Using JDBC with Java Applications and Applets 94 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); } } } ); JPanel first = new JPanel(new GridLayout(3,1)); first.add(accountNumberListScrollPane); first.add(getAccountButton); first.add(insertAccountButton); 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(); } Listing 5.3 Our application for inserting a new row. (continues)
Note: If you are looking for top 10 and very good webhost to host and run your jsp application check Actions jsp hosting services

Executing a Query with No Results 93 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(); Listing 5.3 Our application for inserting a new row. (continues)

Note: If you are looking for good and affordable webspace to host and run your servlet application check Sandzak servlet hosting services

Using JDBC with Java Applications and Applets 92 Let s start with the insert query statement. As we already know, the insert command will allow a new row to be put into a database table. We want to expand our GUI program to allow the user to place an account number, username, and password in the appropriate text boxes and click a button to add the information to the table. Listing 5.3 shows the new code. In addition to the insert button, 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)
Note: If you are looking for best hosting provider to host and run your tomcat application check Astra tomcat hosting services

Executing a Query with No Results 91 Once all of the controls have been created and attached to the application, the frame is sized and displayed to the user. At this point, the user can select an account number on the JList control and click on the Get Account button to display the information on the GUI. Figure 5.4 shows an example of what the output will look like when this is performed. Figure 5.4 Displaying a full record. Executing a Query with No Results Up to now, we have been concentrating on pulling information from the database using a SELECT command. Connector/J, SQL, and MySQL also allow information to be inserted and updated as needed. The operations of insert, delete, and update are considered no-result queries because they don t return a ResultSet object after being executed. For this reason, we don t use the executeQuery() method but instead use a method called executeUpdate(). The signature for the method is int executeUpdate(String SQL); The method accepts a single String parameter, which represents the query to be executed. 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 integer value after the query is performed. This integer represents the total number of rows affected by the query. The question arises, though, about the actual query statements that do not return 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.
Note: If you are looking for cheap and inexpensive provider to host and run your tomcat application check professional tomcat hosting services

Using JDBC with Java Applications and Applets 90 the acc_id numbers from the ResultSet as a String object. The String is added to the vector. The loop pulls each of the account numbers and places them in the vector. Notice that the catch doesn t do anything with a potential error. This will be fixed in our next iteration of the code. Once the vector has been populated, the JList component is created using the vector. After the JList is created, the code puts a scroll pane around it so that the user will be able to have scrollbars available to see all of the account numbers in the list. The Get Account Button After the JList component, the buildGUI() method creates the GUI s only button, called Get Account. The user will click this button after clicking on an appropriate account number. The code begins by instantiating the button and labeling it, and then moves to the action associated with it. In our code example, we build the event processing code right into the button itself instead of having 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 JList control and use the value to pull all of the account information from the MySQL database 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 the SQL string that we want executed against the MySQL database. The full string is ResultSet 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 all columns from the database where the acc_id is equal to the current selected value 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 and there is a result in the ResultSet object, each of the JTextField controls are populated by pulling the database data as String objects using the getString() getter methods. Creating Text Fields with Account Information Once the account list and Get Account button have been created, they are added to a panel, which is added to the application frame. After that step, our code creates five JTextField controls to hold the five column values from a row in the acc_acc table. These controls are added to a second panel, which is also added to the application frame.

Hint: If you are looking for very good and affordable webspace to host and run your java hosting application check Sandzak.com java web hosting provider

Making It Real 89 tor/J driver will be located and pulled into the application. Once the object has been created, a windowClosing event is attached to exit the application when the user clicks the window s close button. Two methods are called on the object. The first is init(), which builds a connection to the database, and the second is buildGUI(), which handles the construction of the GUI presentation. Figure 5.3 Our GUI when first executed. The init() Method The init() method is quite simple: It creates a Connection object and attempts to communicate with the MySQL database server. If a connection is successful, an object variable is instantiated to hold the Connection. A try/catch block is used to grab any errors in the connection attempt and to exit the application appropriately. The buildGUI() Method The vast majority of the work for the application occurs in the buildGUI() method. In Figure 5.3 you see that we have several GUI components to build and place on the GUI frame. The most important is the list in the upper-left corner, which holds all of the account numbers from our acc_acc table on the MySQL database. A user will click one of these account numbers and click on the Get Account button to pull all of the information for the one account and display it in the text boxes on the screen. Our goal in this discussion isn t to provide details on the use of Java GUI components but to describe how those components interact with Connector/J to pull information from the database. Building a JList with Account Numbers Our GUI will contain a JList component, a JButton, and five JTextFields. First, we create the JList with all of the account numbers currently in the acc_acc database table. A JList requires a Model; to populate it we ve chosen to use a vector. In the buildGUI() method, the code begins by instantiating a new Vector object. A try/catch block is entered, and SQL code executes a SELECT of just the acc_id column from the acc_acc table. Next, a loop is used to pull each of
Note: If you are looking for reliable and quality webspace company to host and run your servlet application check professional servlet hosting services

Using JDBC with Java Applications and Applets 88 show(); } public void connectToDB() { try { connection = DriverManager.getConnection( “jdbc:mysql://localhost/accounts”); } catch(SQLException e) { System.out.println(”Unable to connect to database”); System.exit(1); } } private void displaySQLErrors(SQLException e) { System.out.println(”SQLException: ” + e.getMessage()); System.out.println(”SQLState: ” + e.getSQLState()); System.out.println(”VendorError: ” + e.getErrorCode()); } 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(); } } Listing 5.2 Our GUI application. (continued) The code in Listing 5.2 is designed to illustrate using MySQL and a Java GUI application. Figure 5.3 shows what the GUI looks like when it is first executed. We ve broken down the code into a series of methods, which we discuss next. Our Main Function Just as in any Java application, our main function instantiates an object of our class type. Notice that our class extends JFrame because we need to provide a GUI with the application. When the object s constructor is called, the Connec
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

Making It Real 87 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 ee) {} } } ); JPanel first = new JPanel(); first.add(accountNumberListScrollPane); first.add(getAccountButton); accountIDText = new JTextField(15); usernameText = new JTextField(15); passwordText = new JTextField(15); tsText = new JTextField(15); activeTSText = new JTextField(15); JPanel second = new JPanel(); second.setLayout(new GridLayout(5,1)); second.add(accountIDText); second.add(usernameText); second.add(passwordText); second.add(tsText); second.add(activeTSText); c.add(first); c.add(second); setSize(200,200); Listing 5.2 Our GUI application. (continues)
Quick Hint: If you are looking for cheap and reliable provider to host and run your servlet application check Vision servlet hosting plans

Using JDBC with Java Applications and Applets 86 the GUI to insert, delete, and update the database information through the GUI. First, we have our initial code, shown in Listing 5.2. import java.awt.*; import java.awt.event.*; import javax.swing.*; import java.sql.*; import java.util.*; public class Accounts extends JFrame { private JButton getAccountButton; private JList accountNumberList; private Connection connection; private JTextField accountIDText, usernameText, passwordText, tsText, activeTSText; 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 buildGUI() { Container c = getContentPane(); c.setLayout(new FlowLayout()); //Do Account List 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) { } accountNumberList = new JList(v); Listing 5.2 Our GUI application. (continues)

Hint: If you are looking for high quality and reliable webspace provider to host and run your jsp hosting application check Sandzak jsp web hosting provider

Making It Real 85 The Connector/J code attempts to build a long by reading the value from the database as a double and applying a downcast to a long. If the value cannot be converted to a long, the exception SQLException will be thrown. Short Since the MySQL database can store shorts, we need to be able to get them out as well. The methods for doing this are short getShort(int columnIndex); short getShort(String columnName); The short values will be obtained using a downcast from a double. The SQLException exception will be thrown if the value returned cannot be converted to a short. Closing the Objects In our example code, we have created many different objects, including Result- Set, Statement, and Connection objects. When we have finished with each of the pieces, they should be closed so that the JVM as well as the Connector/J driver knows that the memory the objects are occupying can be given back to the system. It is important that we close the objects in the reverse order in which they were opened. This means the ResultSet objects should have their close() method called before we call the Connection object s close(). There will be times when closing the objects in the wrong order can produce a SQLException exception. With this in mind, a closed connection from Connector/J to the MySQL database server can cause a SQLException to be thrown if any of the methods (such as createStatement()) can be called against it. The Connection object includes a method called isClosed(), which returns a value of true if the current Connection object has lost its link to the database server. In these cases, the Connection object needs to be reconnected with the database server before any additional work can occur on the object. Making It Real Well, you may not have found our first example very exciting, so let s expand things a little and make them more useful and powerful, as well as add some graphics. Next we create a GUI that will allow us to see all of the account numbers in our database table, select one, and then display the information associated with the account number on the same GUI. Later in the chapter, we expand

Hint: If you are looking for very good and affordable webspace to host and run your tomcat hosting application check Sandzak.com tomcat web hosting provider