MySQL Developing And Programming Blog

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

Using JDBC with Java Applications and Applets 80 With the relative() method, the system moves the cursor using the current row as a pivot point. A positive parameter moves the internal cursor X number of rows from the current position. A negative parameter moves the internal cursor X number of rows back from the current position. If a value of 0 is passed to the method, the cursor will not move. As you might have guessed, using the method absolute(1) will move the cursor to the first row and the method absolute(-1) will move the cursor to the last row. Two methods for doing the same thing are first() and last(). These methods will move the cursor to the first and last rows in the ResultSet, respectively. It s even possible to move the cursor before the first row as well as after the last row. The beforeFirst() method moves the internal cursor to row 0, which is just before the first row. The method afterLast() moves the cursor to a position just after the last row. In most cases, though, you probably want to move through the ResultSet one row at a time. Just as we did in our example code in Listing 5.1, the next() method moves the cursor one row ahead at a time. Since the internal cursor starts before the first row, the next() method should be called before any processor starts on the ResultSet. Note that a default ResultSet is a forward- only data type; therefore, only the next() method should be valid. However, Connector/J has implemented the previous() method to work on any ResultSet object. In fact, there is even a prev() method defined in Connector/J for moving the cursor backward. In the cases of first(), last(), next(), and previous(), the methods all return a Boolean value indicating whether the command was successful. For first() and last(), the methods return false only when the ResultSet object is empty and therefore no first or last row exists. The methods next(), previous(), and Connector/J s prev() return false when there are no longer any valid rows left in the ResultSet. For example, next() returns true until the internal cursor points to the position after the last row. As you might have noticed, there is no method for determining the size of the ResultSet. We must rely on the Boolean values returned by the methods that move the internal cursor. There is a way to get the total size of a result from the database using a query, but it s a little more complex than the current topics we are discussing. We tackle that one in the next chapter. Getter Methods Once the cursor has been set on a particular row, the contents of each column can be obtained. In our example code, we pull the first column the column starting at 1 using the code System.out.println(rs.getString(1));
Note: If you are looking for best hosting provider to host and run your tomcat application check Astra tomcat hosting services

Using the ResultSet Object 79 Determining the Cursor Position As we mentioned earlier, when a ResultSet is first instantiated, the internal cursor is positioned just before the first row in the set. You have four methods for monitoring where the cursor is in the set. To determine if it is sitting before the first row, use the method isBeforeFirst(); for example: ResultSet rs = statement.executeQuery(”SELECT * FROM acc_acc”); boolean whereIsIt = rs.isBeforeFirst() The isBeforeFirst() method returns a value of true if the internal cursor is sitting before the first row. In our code example, the value returned will be true. The complement to this method is isAfterLast(). When the cursor has been moved beyond all of the rows in the set, the isAfterLast() method returns a value of true. We can also tell whether the internal cursor has been moved to either the first or the last row of the object. The isFirst() method will return true if the cursor is sitting at the first row, and isLast() returns true if the cursor is sitting on the last row. Finally, you can use the getRow() method to return the current row number from the ResultSet. If you execute the getRow() method just after getting the ResultSet from the executeQuery() method, the value returned will be 0. Thus, the first actual data row in a ResultSet has a value of 1. This is something to remember when using the methods in the next section to move around the object. Moving the Cursor Once you know where the cursor is currently pointing within the set, you can move it anywhere you like. First, let s look at two methods that allow you to move to a specific location within the ResultSet. The first method is based on counting from an absolute position from either the beginning or the end of the rows: boolean absolute(int rows) The absolute() method moves the internal cursor to a specific row in the ResultSet. Thus, the method called rs.absolute(2) moves to the second row in the object. If a value is entered that is outside the bounds of the row count in the ResultSet, a SQLException exception will be thrown. To the method, a positive value indicates that it should count from the beginning of the rows; a negative value indicates that it should count from the end of the rows. The second method counts based on the current cursor position: boolean relative(int rows)
Note: If you are looking for cheapest and affordable webspace to host and run your servlet application check Astra j2ee hosting services

Using JDBC with Java Applications and Applets 78 outcome also produces a ResultSet, but instead the set is empty, which indicates that the query didn t produce any rows from the database. Displaying Results The example code takes the ResultSet produced by the execution of our query string and displays the first column of each row. As you see in the next section, the ResultSet object includes a host of methods for manipulating the rows and columns it currently stores. Using the ResultSet Object The ResultSet object is the primary storage mechanism for the rows returned from a query on the MySQL database. It is imperative that you have a full understanding of how the object works and how you get our data out of it. Conceptually, the ResultSet object looks like an adjustable two-dimensional array, as you can see in Figure 5.2. Internal pointer acc_id username password 1034033 jimmy hispassw 1034035 jdoe does Figure 5.2 The ResultSet object. As shown in Figure 5.2, the ResultSet object consists of rows containing data based on the information returned from the database query. The columns of the object are the fields from the database as specified in the query. If the query uses a * in the SELECT, then all of the columns from the database will be represented in the ResultSet. If only a few of the columns are listed in the SELECT, then only those columns will appear in the set. The ResultSet uses an internal cursor to keep track of what row data should be returned when the application requests data. The default behavior for a Result- Set is to maintain read-only data and allow the internal cursor to move forward through the rows. If the data needs to be used a second time, the cursor will need to be moved to the beginning. When a ResultSet object is first instantiated and filled, the internal cursor is set to a position just before the first row. A large number of getter methods are available for retrieving data from the ResultSet object. These methods pull data from a specific row/column cell and attempt to convert the data to a Java data type as defined by the getter method. See Chapter 7, MySQL Type Mapping, for a full discussion on mapping between MySQL, Connector/J, and Java.

Hint: This post is supported by Gama php5 hosting services

Hello World 77 ResultSetHoldability This parameter is not implemented in Connector/J s implementation of createStatement(). When you re using the createStatement() methods, you include the parameters when you re creating a ResultSet or use the defaults as appropriate. In most cases, you use createStatement() without any parameters. Executing SQL Now that we have a Statement object, it s time to execute the SQL statements designed to return results for use in our application. The Statement object includes several types of query methods, as shown in Appendix B. In this section, we cover the method executeQuery(), which is designed to execute SQL that will return a result. This means the method expects to execute a SELECT query. In our example code, the following line sets off the process of retrieving results from the database: ResultSet rs = statement.executeQuery(”SELECT * FROM acc_acc”); There are a few things you should note about this code. The first is that the SQL query statement is provided to the executeQuery() method as a String. The object passes the query to the database, which in turn executes it. Connector/J doesn t, and shouldn t, make any type of determination on the validity of the SQL being passed by the application. If the database is unable to execute the SQL, a SQLException exception will be thrown. If the command is successful, the executeQuery() method returns a ResultSet object containing the rows from the database. Ultimately, three outcomes can occur when the executeQuery() method executes. The first is an exception. An exception can occur for many reasons, among them are the following: The connection is no longer valid to the database server. The SQL has a syntax error in it. The currently logged-in user doesn t have permission to the database table used in the SQL. You need to wrap your executeQuery() in a try/catch block, but it will be a design issue as to which errors you attempt to recover from and which allow the application to fail. There are some database operation errors that you recover from by changing the nature of the operation you might be able to connect to a secondary database, or limit the results. Other errors may be catastrophic, like being unable to update the database. The second outcome is a ResultSet with results in it. This is the most favorable outcome. The third
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 76 Statement object executes a query, it returns a ResultSet object. The default configuration for the Statement object is to return a single ResultSet. If the application needs to work with two different results at the same time, multiple Statement objects will need to be instantiated. As you can see from the API documentation in Appendix B Databases and Tables , the Statement object has quite a few methods associated with it. Throughout this chapter, we cover most of those methods and how they relate to the MySQL database. The Statement object to be used in our example code is created from the Connection object using the method createStatement(), as shown here: Statement statement = connection.createStatement(); When calling the createStatement() object, you must enclose it within a try/catch block and capture any SQLException exceptions. The Connection object contains three different variations of the createStatement() method: Statement createStatement() Instantiates a Statement object to be used for sending queries to the database server. Statement createStatement(int resultSetType, int resultSet Concurrency) Instantiates a Statement object to be used for sending queries to the database server using the provided type and concurrency. Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldabilitiy) Instantiates a Statement object to be used for sending queries to the database server using the provided type, concurrency, and holdability. Three parameters are set for ResultSets when a Statement object is created. These are listed below, and we cover them in more detail when we discuss ResultSet objects: ResultSetType The default is TYPE_SCROLL_INSENSITIVE; the possible values are TYPE_FORWARD_ONLY The ResultSet cursor moves forward. TYPE_SCROLL_INSENSITIVE The cursor may scroll in any direction and is not sensitive to changes. TYPE_SCROLL_SENSITIVE The cursor may scroll in any direction and is sensitive to changes. ResultSetConcurrency This parameter determines whether the ResultSet may be updated in place and the updates automatically applied to the database. The default is CONCUR_READ_ONLY; it is the only option supported by Connector/J.
Note: If you are looking for inexpensive but high quality provider to host and run your serlvet application check Astra servlet hosting services

Hello World 75 to values appropriate for the connection. After all of the properties are set, the object is used in a call to create a connection to the database. Handling Errors When dealing with connections to external sources, you must know how to handle errors that might occur. Both the JDBC driver and MySQL provide numerous types of errors. As you will see throughout our example program, try/catch blocks are provided to capture SQLException exceptions that are thrown by the Connector/J driver. When a SQLException exception is thrown, a call is made to the displaySQLErrors() method defined as a private method within our object. That method is shown here: private void displaySQLErrors(SQLException e) { System.out.println(”SQLException: ” + e.getMessage()); System.out.println(”SQLState: ” + e.getSQLState()); System.out.println(”VendorError: ” + e.getErrorCode()); } Like Connector/J, JDBC drivers implement three different specification- defined pieces of error information. These are the exception itself, the SQL- State, and a vendor error code. Our method outputs the values of these three components if an error occurs when we re trying to accomplish some JDBC task. For example, if we define a host address for our MySQL database system that doesn t exist, the following is displayed on the console: Unable to connect to host 08S01 0 In a production system, we probably want to log the error to an error file and attempt to recover from the error. This might include attempting to connect to another database. Executing Queries Through Statement Objects At this point in our code, we have pulled the Connector/J JDBC driver into our application and created a connection to the database. The example code in Listing 5.1 makes a call to an object method called executeSQL(), where the work to pull results from the database occurs. Within this method, the code builds a SQL statement object, executes the SQL, and displays the results. Building a Statement Object The first step in getting data from the MySQL database is to build a Statement object. The Statement object is designed to be an intermediary between the database connection and the results found from executing some SQL. When a
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services