MySQL Developing And Programming Blog

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

Using JDBC with Java Applications and Applets 70 connectToDB() method in our Hello object, you see that the connection from Java to the database is performed in a single line of code: connection = DriverManager.getConnection( “jdbc:mysql://localhost/accounts?user=&password=”); As you can see, the DriverManager is the catalyst used to create the connection to the database. This is consistent with its job of managing all JDBC drivers. When the getConnection() method is called, the DriverManager needs to decide what JDBC driver to use to connect to the database. Figure 5.1 shows how the DriverManager determines the proper JDBC driver to use with a given connection request. DriverManager Connector/J Oracle SQLServer MySQL Oracle Application SQLServer Figure 5.1 Determining the proper driver. Let s begin our discussion of obtaining a connection to the database by examining the API for the DriverManager. DriverManager API DriverManager is a static class that exposes methods for handling connections to a database as well as administrative methods for JDBC drivers. The following methods are those we might be interested in using: Connection getConnection(String URL) The DriverManager uses a reg istered driver in an attempt to build a connection to a specified database. Connection getConnection(String URL, Properties props) The DriverManager uses a registered driver in an attempt to build a connection to the specified database using the properties provided in the Properties object. Connection getConnection(String URL, String username, String password) The DriverManager uses a registered driver in an attempt to build a connection to the specified database using the provided username and password.
Note: If you are looking for cheapest and affordable webspace to host and run your servlet application check Astra j2ee hosting services

Hello World 69 Hello hello = new Hello(); hello.connectToDB(); hello.executeSQL(); } } Listing 5.1 Hello World. (continued) Since this is our first code for connecting Java to MySQL through Connector/J, we want to spend a fair amount of time going through it. First, note that this is a traditional Java application that instantiates an object and calls a few methods. When the Hello object is instantiated, the constructor is called to handle any initialization that needs to take place. Loading the Connector/J Driver In the constructor, we have placed code that attempts to locate and instantiate our Connector/J JDBC driver. The process begins with the Class.forName method. This method is designed to dynamically load a Java class at runtime. The Java Virtual Machine (JVM) uses the current system classpath (as well as any additional paths defined when the JVM was executed) to find the class passed to the method as a parameter. In our case, the system attempts to find the Driver class found in the com.mysql.jdbc package. In Chapter 4, we placed the Connector/J JAR file in the classpath of the JVM so it could be found. Once it finds the file, the code executes the newInstance() method to instantiate a new object from the Driver class. During the instantiation, the Driver will register itself with a static class called DriverManager, which is responsible for managing all JDBC drivers installed on the current system. If the JVM is unable to locate the driver, it outputs a message to the console and exits the application. Note that the DriverManager is designed to handle multiple JDBC driver objects just as long as they register with the class. This means that you can write a Java application that connects with more than one type of database system through JDBC. Note that simply loading the JDBC driver for a database doesn t result in any type of connection with the database. Using DriverManager to Connect to a Database Once our application object has been created and initialized, the code attempts to build a connection to the database. This is an important step, and therefore we ll spend some time discussing the connection code. If you look in the
Note: If you are looking for inexpensive but high quality provider to host and run your serlvet application check Astra servlet hosting services

Using JDBC with Java Applications and Applets 68 private void displaySQLErrors(SQLException e) { System.out.println(”SQLException: ” + e.getMessage()); System.out.println(”SQLState: ” + e.getSQLState()); System.out.println(”VendorError: ” + e.getErrorCode()); } public Hello() { try { Class.forName(”com.mysql.jdbc.Driver”).newInstance(); } catch (SQLException e) { System.err.println(”Unable to find and load driver”); System.exit(1); } } public void connectToDB() { try { connection = DriverManager.getConnection( “jdbc:mysql://localhost/accounts?user=&password=”); } catch(SQLException e) { displaySQLErrors(e); } } public void executeSQL() { try { Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery( “SELECT * FROM acc_acc”); while (rs.next()) { System.out.println(rs.getString(1)); } rs.close(); statement.close(); connection.close(); } catch(SQLException e) { displaySQLErrors(e); } } public static void main(String[] args) { Listing 5.1 Hello World. (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

CHAPTER 5 Using JDBC with Java Applications and Applets Now that we have a development environment put together, it s time to start writing Java code that will allow access to a MySQL database using the Connector/J JDBC driver. In the remaining chapters of this blog, it is our goal to exercise as much of the functionality found in the driver as possible. This chapter covers the basics of instantiating the driver, connecting to the database from Java, executing queries, and handling results. From a Java perspective, we look at doing all of these tasks from both applications and applets utilizing various GUI components to deal with the information transfer from the user to the database and from the database to the user. Hello World For the sake of tradition, the first application we build is Hello World. The code in Listing 5.1 creates a Java application and pulls information from a MySQL database. package mysql; import java.sql.*; public class Hello { Connection connection; Listing 5.1 Hello World. (continues) 67

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

Installing MySQL, Java, and Connector/J 66 Testing the Connector/J Installation Once you ve installed both Java and the Connector/J driver, create a test file called test.java and enter the following code into the file: public class test { public static void main(String[] args) { try { Class.forName(”com.mysql.jdbc.Driver”).newInstance(); System.out.println(”Good to go”); } catch (Exception E) { System.out.println(”JDBC Driver error”); } } } Save and exit the test file and compile it with this command: javac test.java Now execute the code with this command: java test If the Java Virtual Machine was able to find your Connector/J JAR file, you will see the text Good to go on the console; otherwise, you will see JDBC Driver Error . If you get an error, check that the JAR file is in the correct directory and/or check the CLASSPATH variable to be sure the full path to the JAR file has been included. Figure 4.3 shows all of these steps. Figure 4.3 Testing the Connector/J driver. What s Next Once you have installed all of the applications shown in this chapter, you are ready to start writing all sorts of Java applications that can access a MySQL database. In the next chapter, we begin looking at how to write applications and applets to access MySQL. We explore some of the basic functionality provided in the JDBC specification and implemented in Connector/J.
Note: If you are looking for top 10 and very good webhost to host and run your jsp application check Actions jsp hosting services

Installing Connector/J 65 Compile the code with the command javac hello.java If you get an error saying the javac command cannot be found, then you will need to check the path to the /bin directory; this means that the system is unable to find the Java compiler in the /bin directory. If things work out correctly, execute the Java with java Hello You should see the text Hello World It Works on your screen. If you don t see this text, check Sun s instructions to correct the installation. Installing Connector/J If you refer to Figure 4.1, you see that both the Production and Development areas have downloads available for Connector/J. Clicking on either of the links brings you to the respective page for that particular version of the code. In both cases, two files are available for download: a zip and a tar.gz. Most of the code in the remainder of this blog executes under the Production version of the code, but better performance and many small JDBC support changes are available in the Development 3.0 version. Our test machines used the 3.0 version of Connector/J. If you download the zip version of the code, we assume you are installing on a Windows box and that the tar/gz version for Linux or another Unix flavor. In either case, you need to uncompress the file to expose both the source code for the driver as well as a JAR file called (in 3.0) mysqlconnector-java-3.0.1-beta-bin.jar. This file contains all of the necessary class files for the driver. There are a few ways to install the driver. The first is to copy the /com and /org files into another directory listed in your classpath. Another option is to add the full path to the JAR file to your CLASSPATH variable. Finally, you can just copy the JAR file to the $JAVA_HOME/jre/lib/ext directory. On a Windows platform (if you installed SDK1.4.1), the directory is found at /program files/java/j2re1.4.1/lib/ext. Just copy the JAR file to that directory, and the library will be available for applications that execute within the Java Virtual Machine. On a Linux platform using SDK 1.4.1, the directory where you want to place the JAR file is /usr/java/j2sdk1.4.0/jre/lib/ext.
Note: If you are looking for reliable and quality webspace company to host and run your servlet application check professional servlet hosting services