Section 15.3. Making Connections

15.3. Making Connections

The most complicated part of JDBC is establishing the connection. There are several ways to make a connection, depending on how much information about the connection driver you want hard-coded into your application. We are going to keep it simple and describe one straightforward way to connect.

The DriverManager class is where our application goes to get its connection to the database, as shown in our example. Many different JDBC drivers can register with the DriverManager, and it can make the connection to the kind of driver that you want based on the URL that you provide in the call to getConnection(). So where did our example register anything with the DriverManager? Well, it happened indirectly, via the Class.forName(...).newInstance(); call. That loaded the class and created an instance of it. The JDBC specification says that when a Driver class initializes it must register with the DriverManager. So it happened "under the covers," in loading the driver class.

Another difference between the two examples deals with how the username and password are supplied to the database. Both are supplied in the URL, though in different syntax. That syntax is at the discretion of those who implemented the JDBC driver for that particular flavor of database. If we were to construct the URL at runtime, so that the user could supply a username and password dynamically, we'd want to remove the difference in how the URL is constructed. To do that we could use a call to getConnection() with a signature that includes the username and password as separate String parameters:

Example 15.1. Simple sample program using JDBC for MySQL

 import java.sql.*; public class MyCon {   public static void   main(String [] args)   {     try {       // A simple connection example looks like this:       Class.forName("com.mysql.jdbc.Driver").newInstance();       String url = "jdbc:mysql://host.domain.com/test"+                    "?user=blah&password=blah";       Connection conn = DriverManager.getConnection(url);       // query       String mySQL = "SELECT id, pw FROM Users WHERE name = ?";       PreparedStatement stmt = conn.prepareStatement(mySQL);       stmt.setString(1, args[0]);       // execute the query       ResultSet rs = stmt.executeQuery();       // read the results       while(rs.next()) {           int id = rs.getInt("id");           String pw = rs.getString("pw");           System.out.println("Exception: "+e);         e.printStackTrace();     }   } // main } // class MyCon 

Example 15.2. Simple sample program using JDBC for Oracle

 // import oracle.jdbc.driver.*; import java.sql.*; public class MyCon {  public static void  main(String [] args)  {    try {      // A simple connection example looks like this:      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();      String url = "jdbc:oracle:thin:mydbusername/mydbpasswd"+                   "@host.domain.com:1521:dbname";      Connection conn = DriverManager.getConnection(url);      // query      String mySQL = "SELECT id, pw FROM Users WHERE name = ?";      PreparedStatement stmt = conn.prepareStatement(mySQL);      stmt.setString(1, args[0]);      // execute the query      ResultSet rs = stmt.executeQuery();      // read the results      while(rs.next()) {        int id = rs.getInt("id");        String pw = rs.getString("pw");        System.out.println("Exception: "+e);        e.printStackTrace();    }  } // main } // class MyCon Connection conn = DriverManager.getConnection(url, username, password); 

Getting this to compile and run requires you to have the appropriate JDBC JAR files available. For Oracle, see your Oracle DBA, or see pages 228229 of Java Oracle Database Development by David Gallardo. For MySQL, it's an easy download you can install from the Internet.

15.3.1. Downloading JDBC for MySQL

The JDBC implementation for MySQL is available for free from

http://www.mysql.com/downloads/api-jdbc.html.

The current version at the time of writing was mysql-connector-java-3.0.9-stable.tar.gz which you can unpack as follows:

 $ gunzip mysql-connector-java-3.0.9-stable.tar.gz $ tar xvf mysql-connector-java-3.0.9-stable.tar 

That leaves you with a directory named mysql-connector-java-3.0.9-stable which contains a JAR file named mysql-connector-java-3.0.9-stable-bin.jar along with some directories (which are the contents of the JAR, unpacked) and a few miscellaneous files.

From the readme file:

Once you have unarchived the distribution archive, you can install the driver in one of two ways:

• Either copy the com and org subdirectories and all of their contents to anywhere you like, and put the directory holding the com and org subdirectories in your classpath, or

• Put mysql-connector-java-3.0.9-stable-bin.jar in your class-path, either by adding the full path to it to your CLASSPATH environment variable, or putting it in $JAVA_HOME/jre/lib/ext.

Unlike JUnit, it is OK to put this JAR in the ext directory.