MySQL Developing And Programming Blog

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

Using JDBC with Java Applications and Applets 74 Table 5.1 Connection Properties (continued) NAME DESCRIPTION DEFAULT maxRows Limits the total number of rows to be returned by a query. 0 (maximum) useUnicode If true, the server will use Unicode when returning strings; otherwise, the server attempts to use the character set that is being used on the server. true characterEncoding If useUnicode=true, specifies the encoding to be used. None relaxAutoCommit If relaxAutoCommit=true, then the server allows transaction calls even if the server doesn’t support transactions. false capitalizeTypeNames If set to true, type names will be capitalized in DatabaseMetaData results. false profileSql If set to true, queries and timings will be dumped to STDERR. false socketTimeout If > 0 in milliseconds, the driver will drop the connection when the timeout expires and return the SQLState error of 08S01. 0 StrictFloatingPoint If set to true, the driver will compensate for floating float rounding errors in the server. false As you can see, there is quite a bit of information that can be conveyed to the Driver and used for queries to the database. Using Properties with the Connection One of the getConnection() methods exposed by the DriverManager allows the use of a Properties object to pass information to the DriverManager. All of the connection parameters shown in Table 5.1 can be placed in a Java Properties object. For example: Properties prop = new Properties(); prop.setProperty(”user”, “newuser”); prop.setProperty(”password”, “newpass”); myConnection = getConnection( “jdbc:mysql://localhost/accounts”, prop); In this code, a Properties object is instantiated and assigned to the prop variable. Using the setProperty() method, the user and password properties are set
Note: If you are looking for cheap and inexpensive provider to host and run your tomcat application check professional tomcat hosting services

Hello World 73 The is a little more complex because it consists of up to three different components. The general format of the is //[:
][/] Notice the use of the double slashes just as with an HTTP URL. The component is the domain name or IP address of the server hosting the MySQL database application. The can be followed by a colon and a port number where the database application accepts connections. The default port in MySQL is 3306; the Connector/J driver will also default to port 3306 if one is not found in the . Finally, the database the driver should begin using when a connection is first made can be added to the . Here are a few examples: jdbc:mysql://localhost jdbc:mysql://localhost/accounts jdbc:mysql://192.156.44.3/db_dev jdbc:mysql://database.company.com/prod jdbc:mysql://database.company.com:4533/prod In each of the sample URLs, the JDBC driver will be able to determine which host currently is running a MySQL database application, what port to communicate through to the database system, and the initial database. In addition to specifying the initial database that the application should use for the current connection, the Connector/J driver allows properties to be appended to the driver string. For example, we can specify the username and password to be used with the connection: jdbc:mysql://192.156.44.3/db_dev?user=newuser&password=newpassword The properties are appended to the driver string using the ? and & delimiters. The first property must use the ? delimiter, and all others must use &. Connector/J includes quite a few properties that can be specified on the connection string, as shown in Table 5.1. Table 5.1 Connection Properties NAME DESCRIPTION DEFAULT user The username for the connection. None password The password for the user. None autoReconnect Set to true if the connection should automatically be reconnected. false maxReconnects If autoReconnect=true, represents the 3 total reconnect attempts. initialTimeout If autoReconnect=true, represents 2 the time to wait (in seconds) between reconnect attempts.
Hint: If you are looking for good and high quality web space to host and run your java application check Vision java web hosting services

Using JDBC with Java Applications and Applets 72 The setLoginTimeout() method accepts a single integer value representing the maximum timeout in seconds for a connection attempt. If you need to obtain the current timeout setting, use the getLoginTimeout() method. If you use this method without setting the timeout, a value of 0 will be returned, indicating that the system default timeout of 30 seconds should be used. Connection Management Methods The meat of the DriverManager object is found in the connection methods. A method called getConnection() is overloaded three times to provide numerous ways of supplying arguments to the DriverManager. The signatures for the methods are as follows: Connection getConnection(String URL); Connection getConnection(String URL, Properties info); Connection getConnection(String URL, String user, String password); In all three methods, the primary connection information is found in the first parameter of type URL (which we discuss in the next section). The first overloaded method assumes that all of the information for the connection will be passed in the URL. The second method gets connection options from the Properties parameter. The third method obtains connection information from the URL, but pulls the username and password for the database connection from the method parameters. Using URL Options in Connector/J In all of the getConnection() methods, the URL parameter is responsible for providing the DriverManager with information about the type and location of the database with which a connection should be established. From a standards perspective, a URL (Uniform Resource Locator) provides a common way of locating resources found on the Internet. More than likely, you use HTTP URLs every day. A lot of information is transferred in URLs, and that information can be used for Web pages as well as database locations. The general format of a URL is
:: In a URL for a Web page, the protocol is HTTP and there is no subprotocol or subname. In the JDBC world, the protocol is defined as jdbc. The is typically the name of the driver this particular connection URL needs to use, and the is a string representing connection information, such as the source of the database. The Connector/J driver requires that the be defined as mysql. So our URL looks like this: jdbc:mysql:

Hint: This post is supported by Gama web hosting php services

Hello World 71 Driver getDriver(String URL) The method returns a registered driver that will potentially be used to connect to a database with the provided URL. Enumeration getDrivers() The method returns all of the currently registered drivers. int getLoginTimeout() The method returns the maximum time in seconds that the current DriverManager will wait for a connection to a database. void setLoginTimeout(int secs) The method sets the maximum time in seconds that the current DriverManager will wait for a connection to the database. These methods can be characterized into three groups: driver management, timeout management, and connection management. Driver Management Methods Once a driver (or set of drivers) has been registered with a DriverManager, you usually don t have to do anything further with the driver. However, a few methods are available for obtaining and removing drivers from the DriverManager if you need to. A current list of registered drivers can be obtained using code like this: Enumeration e = DriverManager.getDrivers(); while (e.hasMoreElements()) { Driver d = (Driver)e.nextElement(); System.out.println(”Driver Major Version = ” + d.getMajorVersion()); } Once a reference to a driver has been obtained, the deRegisterDriver() method can be used to remove the driver. In almost all cases, you won t need to use any of this information unless you want to remove from the application all JDBC access to a particular database. Timeout Management Methods When connecting to a database whether local or remote to the Java application the application doesn t know if the database system itself is currently online. There can be situations where a database is down for maintenance or the machine has crashed. A Java application has the option of setting a timeout value for the maximum time that the DriverManager will wait as it attempts to create a connection. The default timeout is 30 seconds before the driver throws a java.net.ConnectException exception. For situations where the database is on a remote machine, the timeout might need to be extended. The following code shows an example of setting a timeout of 90 seconds: DriverManager.setLoginTimeout(90);
Note: If you are looking for good and quality webspace to host and run your java application check professional java hosting services