Quantcast

Servlets, MySQL and JDBC Drivers

Get the WebProNews Newsletter:
[ Business]

With the “write once, compile once, run anywhere” power that the JDBC offers you, Java’s database connectivity allows you to worry about the translation of relational data into objects instead of worrying about how you are getting the data.

Java provides database programmers with the following features: Easy object to relational mapping Database independence Distributed computing A Java database application does not care where the database engine is located. No matter how many times the database engine changes, the application itself need never change. Java helps tear down the wall between the client and the server through another piece of its Enterprise platform, RMI (RMI stands for remote method invocation). RMI allows applications to call methods in objects on remote machines as if those objects were located on the same machine.

What is JDBC?
JDBC is an API (application programming interface) for database access. By allowing you to construct SQL statements and embed them inside the Java API calls, JDBC becomes an SQL-level API. In short, you are basically using SQL. JDBC accomplishes its goals through a set of Java interfaces, each implemented by individual vendors.

THE JDBC DRIVER
The set of classes that implement JDBC interfaces for a particular database engine is called a JDBC driver. If you think of a database query for any database engine, it requires you to connect to the database engine, issue a SELECT statement, and process the result.

When you write a java database application, the only driver-specific information JDBC requires from you is the database URL. Using the URL and whatever properties your JDBC driver requires (generally a user ID and password), your application will first request a java.sql.Connection implementation from the driver manager. The driver manager in turn will search through all the known java.sql.Driver implementations for the one that connects with the URL you provided. If it exhausts all the implementations, without finding a match, it throws an exception back to your application. Once a driver recognizes your URL, it creates a database connection using the properties you specify. It then provides the driver manager with a java.sql.Connection implementation representing the database connection. The DriverManager then passes the Connection object back to the application. In your code, this one line handles the entire database connection process:

Connection con = DriverManager.getConnection(url, uid, password);

Of course, you are probably wondering how the JDBC DriverManager learns about a new driver implementation. The DriverManager actually keeps a list of classes that implement the java.sql.Driver interface. Somehow, somewhere, something needs to register the driver implementation for any potential drivers it may require with the driver manager.

The JDBC classes for creating a Connection
Java.sql.Driver This class gives JDBC a launching point for database connectivity by responding to DriverManager connection requests and providing information about the implementation in question.

Java.sql.DriverManager This is a class not an interface. It maintains a list of driver implementations and presents an application with one that matches. Methods: registerDriver() and deregisterDriver() allows a driver implementation to register with the driver manager or remove itself from that list. Using getDrivers() you can get a list of the registered drivers.

THE STATEMENT OBJECT (java.sql.Statement)
You use the connection object to generate implementations of java.sql.Statemnt tied to the same database transaction. A statement is very much what its name implies –a SQL statement. It is the most basic of the three JDBC classes representing the SQL statement. Once you get the statement object from a connection, you have what amounts to a blank check that you can write against the transaction represented by the connection. Methods in the Statement class: -executeQuery() takes a SQL string as a query and returns a ResultSet object; it should be used for any SQL calls that expect to return calls from the database. -executeUpdate() method takes a SQL string as a query and updates the database. The method returns the number of rows affected. -execute() it could be used when you don’t know if the statement is an update or a query and that happens when the application is executing dynamically created SQL statements. If a row is returned from the database, the method returns true and the application can use the getResultSet() method to get the returned row.

THE RESULT SET
The methods for queries return an instance of java.sql.Resultset, which provides you with access to the data retrieved by a query. JDBC uses different methods for sending queries than for sending updates. The key difference is the fact that the method for nonqueries returns an instance of java.sql.ResultSet, while the method for nonqueries returns an integer. A ResultSet provides you with access to the data retrieved by a query. A ResultSet object then provides the application result of the query.

My environment: Windows 2000, JDK1.3.1, MySQL. The servlet container is JSDK2.1. The JDBC driver is mm.mysql-2.0.4-bin.jar.

Let’s look at two small applications that are the basis for the next article, where I will present a complete application. First we are going to look for a driver and make sure we are able to load the desired driver. For any database there are a number of drivers available, and we can use any of them depending on the database and the operating system.

import java.sql.*;

public class test { static
public void main
(String args[])
 {
   //Connection connection = null;

  try
  { Class.forName(“org.gjt.mm.mysql.Driver“);

   System.out.println(“LOADED THE DATABASE
DRIVER
“);
   System.out.println(“HAPPY NOW???“);

  }

 catch(Exception e)

  {
   System.out.println(“COULD NOT LOAD THE DRIVER!!!!“);
   System.out.println(e);
  }
 }
}

As you can tell we are attempting to load the database driver. If an attempt to connect is not successful, an exception will be generated and the screen output will help you locate the problem. Most often is the Environment variables setup in Windows. If the output is “LOADED THE DATABASE DRIVER HAPPY NOW???” it means that you have a driver available, otherwise look in windows at the Path and CLASSPATH variables.

import java.sql.*;

public class tcdb { static
public void main
(String args[])
 {
   Connection con = null;

 try
   {
    Class.forName(“org.gjt.mm.mysql.Driver“);

   }

 catch (Exception e)
   {
    System.err.println(“Unable to load driver.“);

    e.printStackTrace();
    return;
    }

 try {

 con = DriverManager.getConnection(
 ”jdbc:mysql://11.111.111.11/test?user=dragos&password=dragos“);

 //SYNTAX

 //”jdbc:mysql://ip_address (or just plain localhost/

 //database_name?user=your_user_name
 // &password=ypour_password”);

 System.out.println(“Connection successful!“);

 System.out.println(“I AM IN THE TEST DATABASE!“);
}

 catch(SQLException e)
 { e.printStackTrace(); }

 finally { if (con
!= null )
  {
   try { con.close();
   System.out.println(“I HAVE CLOSED THE
CONNECTION TO THE
   DATABASE!
“);
   }
 catch (SQLException e )
 { e.printStackTrace(); }
   } //if
  } //finally
 } //main
} //tcdb

Above we are attempting a connection to the database, and most likely if you succeeded with the first application, the second should be a breeze. In the next part of the article I will present a complete application, easy to follow and I hope fun.

Dragos Mincinoiu is a staff writer for iEntry.

Servlets, MySQL and JDBC Drivers
Top Rated White Papers and Resources
  • Keshav Kumar

    I was tryed the above code but I was not able to connect remote machine’s sql server.

    can you give me you full sussation to connect  remote  sql server.

    Thanks

    Keshav

  • Join for Access to Our Exclusive Web Tools
  • Sidebar Top
  • Sidebar Middle
  • Sign Up For The Free Newsletter
  • Sidebar Bottom