MySQL Developing And Programming Blog

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

Using JDBC with Java Applications and Applets 84 Byte getByte(int columnIndex); Byte getByte(String columnName); byte[] getBytes(int columnIndex); byte[] getBytes(String columnName); In most cases, these methods will not throw an exception because nearly all values in a MySQL column can be returned as bytes. Double If the value in a MySQL column is a double or a value that can be converted to a double, then you can use the following two methods to pull that value: double getDouble(int columnIndex); double getDouble(String columnName); If the value in the MySQL column cannot be converted to a double, a SQLException exception will be thrown with an error value of S1009. Float Real or floating-point values can be returned from the MySQL database using these methods: float getFloat(int columnIndex); float getFloat(String columnName); If the value in the MySQL column cannot be converted to a float, a SQLException exception will be thrown with an error value of S1009. If the strictFloatingPoint property supplied to the Connection object has a value of true, then Connector/J attempts to compensate the returned value for rounding errors that might have occurred in the server. Int The MySQL server can handle integer values, and you can use the following two methods to pull their associated value from the database: int getInt(int columnIndex); int getInt(String columnName); If the strictFloatingPoint property has been set to true in the Connection object, the Connector/J driver attempts to handle rounding errors in the integer values stored on the database. Values that cannot be converted to an integer will throw the SQLException exception. Long Longs can be pulled from the database using these methods: long getLong(int columnIndex); long getLong(String columnName);

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

Using the ResultSet Object 83 methods, the outcome is a Boolean value. Consider a table defined as mysql> describe bool; +——-+————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————+——+—–+———+——-+ | id | int(11) | YES | | NULL | | | a | tinyint(1) | YES | | NULL | | | b | int(11) | YES | | NULL | | | c | varchar(4) | YES | | NULL | | | d | varchar(5) | YES | | NULL | | +——-+————+——+—–+———+——-+ 5 rows in set (0.00 sec) Now see what happens if we put the following data into the table: mysql> select * from bool; +——+——+——+——+——+ | id | a | b | c | d | +——+——+——+——+——+ | 1 | 1 | 0 | true | f | +——+——+——+——+——+ 1 row in set (0.00 sec) The data can be pulled with the following Java code: ResultSet rs = statement.executeQuery( “SELECT * FROM bool”); while (rs.next()) { System.out.println(rs.getString(”a”) + ” ” + rs.getBoolean(”a”)); System.out.println(rs.getString(”b”) + ” ” + rs.getBoolean(”b”)); System.out.println(rs.getString(”c”) + ” ” + r rs.getBoolean(”c”)); System.out.println(rs.getString(”d”) + ” ” + rs.getBoolean(”d”)); } Can you guess the output? Here it is: 1 true 0 false true true f false As you can see, the values within the columns are properly translated into Boolean values. Byte If the information in your database needs to be obtained as a raw byte or series of bytes, then the following four methods will be helpful to you:
Hint: If you are looking for high quality webhost to host and run your jsp application check Vision web hosting jsp services

Using JDBC with Java Applications and Applets 82 output statement, we use the name of the column as defined in the query. There is hidden meaning in that last sentence. In the query we used SELECT * FROM acc_acc we asked for all of the columns from data in the acc_acc table without any row restrictions. The * pulls all of the columns as well as the column names defined in the table. What this means to the ResultSet is that the values can be pulled using the names as declared in the table. Consider the following code: ResultSet rs = statement.executeQuery( “SELECT acc_id, username FROM acc_acc”); rs.next(); System.out.println(rs.getString(”username”)); System.out.println(rs.getString(”password”)); The first output line pulls the username value from the ResultSet. We can again use the name of the column as defined in the table since we ve asked the database to return both the acc_id and username from the table. The second output line will produce a SQLException exception because no password column is defined in the ResultSet. Finally, consider this code: ResultSet rs = statement.executeQuery( “SELECT acc_id, username “User” FROM acc_acc”); rs.next(); System.out.println(rs.getString(”User”)); System.out.println(rs.getString(”username”)); The first output line attempts to pull a column called User from the ResultSet. It will be successful because our SELECT pulled the username column from the table but renamed it as User (which is the column name used in the ResultSet). The second output line in this code example produces a SQLException exception. Primitive Getters Connector/J includes getter methods for all of the primitive types defined within a MySQL table. In this section, we present examples for using each of the methods. Boolean If you are interested in retrieving a column s value as a Java Boolean value, two methods are available: Boolean getBoolean(int columnIndex) Boolean getBoolean(String columnName); As we ve discussed, the task of the getter method is to pull the value from a table column and attempt to convert it to the intended Java type. For the getBoolean()
Note: If you are looking for top 10 and very good webhost to host and run your jsp application check Actions jsp hosting services

Using the ResultSet Object 81 This code tells the ResultSet to return (as a String) the value located in the first column of the row the internal cursor is currently pointing to. Clearly, the cursor must be pointing to a valid row; otherwise, the getter method will throw a SQLException exception. Looking at the ResultSet API, you will notice that there are quite a large number of methods for obtaining values from the set. Each method is designed to pull a specific type, such as integer or string. As an example, consider the getString() methods: String getString(int columnIndex); String getString(String columnName); Both of these methods pull a value from MySQL as a String. Even if the value in MySQL is an integer, the integer will be coaxed into the String type. However, what we really want to consider are the parameters to the method. Notice how one of them is passing an integer and the other is a String. Let s look at an example of how the getters will work based on a real database. One of our sample databases is called accounts, and it contains a table named acc_acc. This table is defined as: acc_id - int username - varchar acc_id int username varchar password varchar ts timestamp act_ts - timestamp Using the getString() methods, we can pull the value contained in the username column in two different ways. First, we pull the values using some example SQL: ResultSet rs = statement.executeQuery(”SELECT * FROM acc_acc”); Now we know that the variable rs is a ResultSet and that its internal pointer is set at a position before the first row. To start pulling the data from the set, we need to move the internal pointer to the next row: rs.next(); With the internal pointer at the first row in the object, we can output the values in the username column by using the getString() method. Two different methods are available, as shown here: System.out.println(rs.getString(1)); System.out.println(rs.getString(”username”)); In the first output statement, the column number is used to let the ResultSet object know which column the value should be pulled from. In the second
Note: If you are looking for cheap and inexpensive provider to host and run your tomcat application check professional tomcat hosting services