| Before I demonstrate how to use JDBC in more detail, it's best to confirm that you can successfully connect to the database. This implies that Java can access the JDBC driver and that you're using the right connection information for MySQL. Frequently this can be the most taxing step in the whole process. It starts with stating that you want to use the SQL classes: import java.sql.*;
Then, you specify which java.sql.Driver class to use. In this case, it will be the MySQL Connector/J: Class.forName("com.mysql.jdbc.Driver");
On some systems that code can be problematic. A more foolproof version is: Class.forName("com.mysql.jdbc.Driver").newInstance();
Next, you can establish a connection to the database using the DriverManager: Connection con = DriverManager.getConnection(url, "username", "password");
The url value is a string that indicates the database, hostname, and more, in the format jdbc:protocol:dsn, where protocol (in this case) would be mysql, and dsn would include the hostname, the port, and the database name, like so (the square brackets indicate optional parameters): jdbc:mysql://[hostname][:port]/databasename
As an example, to connect to the test database, without specifying a host, your url would be jdbc:mysql:///test
To specify the host and the port, use jdbc:mysql://localhost:3306/test
At the end of the script, after all of the MySQL interaction has been completed, you should close the database connection and free up its resources using the connection's close() method: con.close();
To connect to MySQL: | | 1. | Create a new Java class in your text editor or Java development tool (Script 9.1).
As long as you have Java installed, you can write the scripts in nearly any text editor. If you have a particular program for coding, compiling, and running Java, so much the better.
Script 9.1. This simple Java class establishes a connection to a MySQL database. 1 import java.sql.*; 2 3 // Script 9.1 'Connect.java' 4 5 public class Connect { 6 7 public static void main(String argv[]) throws Exception { 8 9 // Initialize variables. 10 Connection con = null; 11 12 try { 13 14 // Set the connection URL. 15 String url = "jdbc:mysql:///test"; 16 17 Class.forName("com.mysql.jdbc.Driver").newInstance(); 18 19 // Connect. 20 con = DriverManager.getConnection(url); 21 22 // Report on the results. 23 if (con != null) { 24 System.out.println("A database connection has been established!"); 25 } 26 27 } catch (Exception e) { 28 29 System.out.println("Problem: " + e.toString()); 30 31 } 32 33 // Clean up. 34 finally { 35 36 if (con != null) { 37 38 try { 39 con.close(); 40 } catch (Exception e) { 41 System.out.println(e.toString()); 42 } 43 con = null; 44 } 45 } 46 47 } // End of main(). 48 49 } // End of class Connect.
|
| 2. | Use the sql classes and define the class.
import java.sql.*; public class Connect { public static void main(String argv[]) throws Exception {
The first application I will write will be called Connect and have just one main block of code to be executed. I'll use this basic format throughout the chapter.
| 3. | Initialize the connection variable.
Connection con = null;
The con variable will be of type java.sql.Connection, as defined by the java.sql.* classes. Here I'm setting its initial value to null.
| | | 4. | Establish a connection to the test database.
try { String url = "jdbc:mysql:///test"; Class.forName("com.mysql.jdbc.Driver").newInstance(); con = DriverManager.getConnection(url);
The second line of this group defines the JDBC url variable (the address for connecting to the database). The third line dictates which driver should be loaded, and the final line attempts to make a connection, assigning the connection to the previously established con variable.
In this example I am not using a specific hostname, username, or password. This is fine as long as the permissions on your MySQL database allow for an unnamed user without a password from any host to connect to the test database. If the permissions would not allow for this, the Java application will fail here (Figure 9.2) and you should change the settings accordingly. As with any time you interact with MySQL, using the proper username, password, and host is critical to the success of your Java applications.
Figure 9.2. Any MySQL connection issues are reported just as they would be when trying to access a database using the mysql client. 
| 5. | Print a message if the connection was made.
if (con != null) { System.out.println("A database connection has been established!"); }
To a degree, this code is not really necessary, because a failure to connect to the database will throw an error. On the other hand, should the connection be made, I'd like to indicate its success somehow.
| | | 6. | Complete the try clause.
} catch (Exception e) { System.out.println("Problem: " + e.toString()); }
Any exceptions caused by the TRy code will be caught here. For simplicity's sake, the exception message is just printed. The most common problem would be a failure to find the driver (Figure 9.3).
Figure 9.3. If Java cannot find the MySQL Connector/J driver, you'll see an error message like this. See the installation instructions and the sidebar for troubleshooting information. 
| 7. | Perform any cleanup.
finally { if (con != null) { try { con.close(); } catch (Exception e) { System.out.println(e.toString()); } } con = null; }
From the perspective of MySQL, the only relevant line here is try { con.close(); }, which closes an open database connection. If, for some reason, it cannot close the connection, the error message will be reported.
| | | 8. | Complete the class.
} // End of main(). } // End of class Connect.
I've added comments to help avoid confusion with all of the closing curly braces at the end of the script.
| 9. | Save the file as Connect.java.
Per Java's syntax, you will need to make sure you name the file using the same spelling and capitalization as the name of the class.
| 10. | Compile Connect.java.
There are two primary options for doing this:
- Type javac /path/to/Connect.java at a command prompt (or just move into the same directory as Connect.java and use javac Connect.java).
or
- Use your Java IDE to compile the class.
If you don't know how to compile a Java application, refer to any of the available tutorials online.
Troubleshooting Java can be more than a little difficult, particularly as you first try to get everything running. If, when compiling your first Java application, the computer complains that it doesn't recognize javac, you should first check that it is installed. You can do a simple search for it or make sure you installed a Java Development Kit (JDK). If you can find it on your computer, then it's probably not in your system PATH: the realm of places your computer looks for applications. There are tutorials online for altering your PATH; see one pertaining to your operating system. If javac and java work but you see any errors involving java.lang.NoClassDefFoundError or the com.mysql.jdbc.Driver, this is most likely because Java cannot find the MySQL Connector/J. This is a CLASSPATH issue, discussed in the first section of this chapter. |
| | | 11. | Run Connect (Figure 9.4).
Figure 9.4. Successfully running the application creates this message. 
Again, two choices:
|
 Tips
Sometimes if you specify localhost as the host, Java will replace this value with the IP address of the host (e.g., 192.169.1.1 or 127.0.0.1), which may interfere with the permissions as established in the MySQL database. If you have a problem connecting and are definitely using the right username and password combination, set the host value explicitly in your Java code. You can add extra parameters to your url string by using this format: jdbc:mysql:///test?name=value&name2=value2 For example, an alternative way to establish a connection is to put the username and password in the url: jdbc:mysql:///test?user=Marc&password=Javaman
|
