JaVa
   

Creating a Connection to a Database

Now that we have had a brief introduction to the JDBC, we can look at how to establish a connection from a console app to a MySQL database. Let's first create a database called firsttest in MySQL using the MySQL console client (for more information on this, please look at the previous chapter). Here is the SQL statement that we require to create an empty database:

mysql> CREATE DATABASE firsttest;


Now we need to extract the JDBC driver to the directory that our source code is going to be stored in (in my case, this will be c:\java\jdbcex1). Let's now extract the JDBC driver to our source directory. First, copy the jar file to the source directory, and then load up the command prompt window. Enter the following command and press Return.

C:\java\jdbcex1>jar xvf mm.mysql-2.0.11-you-must-unjar-me.jar


Next we need to "cut" all of the files and directories out of the mm.mysql-2.0.11 directory that it created and simply paste them into the source directory (i.e., c:\java\jdbcex1). When this is done, we can then delete the mm.mysql-2.0.11 directory, as it will be empty, and also the original file that we extracted (i.e., mm.mysql-2.0.11-you-must-unjar-me.jar). Screenshot-1 is a screen shot of how our directory should now be structured:

Java Click To expand
Screenshot-1: Our source directory structure

Now let's create a simple Java console app that will connect to and then disconnect from the database. First we'll look at and compile the complete source for the console app and then we will discuss in detail why each part of the code is there.

Code Listing 16-1: Connecting to a database
import java.sql.*;
public class DatabaseExample1
{
 public static void main(String[] args)
 {
 try
 {
 Class.forName("org.gjt.mm.mysql.Driver");
 }
 catch(ClassNotFoundException e)
 {
 System.out.println(e);
 }
 try
 {
 Connection conn; System.out.println("Attempting to connect...\n"); conn = DriverManager.getConnection(
 "jdbc:mysql://localhost/firsttest?user=root&password=");
 System.out.println("Connected\n");
 System.out.println("Attempting to disconnect...\n");
 conn.close();
 System.out.println("Disconnected\n"); }
 catch(SQLException e)
 {
 System.out.println(e);
 }
 }
}


Java End example

When we execute the code with the MySQL server also running, the following console output can be seen from the app:

Java Click To expand
Screenshot-2: JDBC Example 1

In our main method, the first code that we have added is to ensure that the MySQL driver is available and initialized. This is accomplished by calling the static forName method of the Class class, which can be seen in the following block of code.

 try
 {
 Class.forName("org.gjt.mm.mysql.Driver");
 }
 catch(ClassNotFoundException e)
 {
 System.out.println(e);
 }


Note that if the driver class cannot be found, a ClassNotFoundException exception is thrown. After we have checked that the driver class is initialized, we can then create a Connection object, which handles our connection to the database. Here is the line of code that we used in our example to declare our Connection object:

 Connection conn;


Once we have declared our Connection object, we then attempt to establish a connection to the database with the following chunk of code:

 conn = DriverManager.getConnection(
 "jdbc:mysql://localhost/firsttest?user=root&password=");


We use the static method getConnection of the DriverManager class (which is part of the JDBC) to obtain the connection to the database. Note that we can replace firsttest with the name of any database to which we wish to connect, and we can also specify a username and password if they are required to gain access to the database. After our connection is complete, we then terminate the connection to the database with the following statement:

 conn.close();


Also note that we encapsulate all of our database code within a try/catch statement, as the JDBC can throw an SQL exception if anything goes wrong.

NoteĀ 

All connections should be closed after use, as there is a limited amount of simultaneous connections, and if you forget to close connections, the database runs out of them quite quickly. However, note that a connection is closed when a Connection object is garbage collected (when no more references to it exist), and certain fatal errors will also cause the connection to close.

JaVa
   
Comments