JDBC Driver and Database Connection

 To Store form data to database using JDBC Driver

Many applications require storing form data into database. In this post  we will study JDBC driver to implement database .

The Common Gateway Interface (CGI) has and continues to be the most commonly used method for creating dynamic and responsive web pages. The main problem with CGI is that each new client request results in a new instance of the CGI executable being formed by the HTTP daemon. Servlets is the Java answer to the CGI problem. Servlets are Java classes, loaded and kept resident by the HTTP daemon. When the servlet is loaded, database connections can be established and held between client requests.

What is JDBC?

Java Database Connectivity or JDBC for short is set of Java API's that enables the developers to create platform and database independent applications in java.

What are JDBC Drivers?

JDBC Drivers are set of classes that enable the Java application to communicate with databases. Java.sql that ships with JDK contains various classes for using relational databases. But these classes do not provide any implementation, only the behaviors are defined. The actual implementations are done in third-party drivers. Third party vendors implement the java.sql.Driver interface in their database driver.

JDBC Drivers Types

Sun has defined four JDBC driver types. These are: 

Type 1: JDBC-ODBC Bridge Driver

The first type of JDBC driver is JDBC-ODBC Bridge which provides JDBC access to any ODBC compliant databases through ODBC drivers. Sun's JDBC-ODBC bridge is example of type 1 driver. 

Type 2: Native -API Partly - Java Driver

Type 2 drivers are developed using native code libraries, which were originally designed for accessing the database through C/C++. Here a thin code of Java wrap around the native code and converts JDBC commands to DBMS-specific native calls. 

Type 3: JDBC-Net Pure Java Driver

Type 3 drivers are three-tier solutions. This type of driver communicates to a middleware component which in turn connects to database and provide database connectivity. 

Type 4: Native-Protocol Pure Java Driver

Type 4 drivers are entirely written in Java that communicates directly with vendor's database through socket connection. Here no translation or middleware layer, are required which improves performance tremendously. 

There are seven standard steps in querying databases:

1. Load the JDBC driver.

2. Define the connection URL.

3. Establish the connection.

4. Create a statement object.

5. Execute a query or update.

6. Process the results.

7. Close the connection.

Here are some details of the process. 

Load the Driver

The driver is the piece of software that knows how to talk to the actual database server.

Use Class.forName (), this method takes a string representing a fully qualified class name (i.e., one that includes package names) and loads the corresponding class. This call could throw a ClassNotFound- Exception, so should be inside a try/catch block.

e.g.

try

{
          Class.forName("connect.microsoft.MicrosoftDriver");
          Class.forName("oracle.jdbc.driver.OracleDriver");                   
          Class.forName("com.sybase.jdbc.SybDriver");

}

catch (ClassNotFoundException cnfe)

{

System.err.println("Error loading driver: " + cnfe)

} 

Define the Connection URL

Once you have loaded the JDBC driver, you need to specify the location of the database server. URLs referring to databases use the JDBC: protocol and have the server host, port, and database name (or reference) embedded within the URL. The exact format will be defined in the documentation that comes with the particular driver. 

Establish the Connection

To make the actual network connection, pass the URL, the database username, and the password to the getConnection () method of the Driver- Manager class,

String username = "data";

String password = "xyz";

Connection connection = DriverManager.getConnection(oracleURL, username, password);

Create a Statement

A Statement object is used to send queries and commands to the database and is created from the Connection as follows:

Statement statement = connection.createStatement(); 

Execute a Query

Once you have a Statement object, you can use it to send SQL queries by using the executeQuery method, which returns an object of type Result-Set.

e.g.

String query = "SELECT col1, col2, col3 FROM Table_1";

ResultSet resultSet = statement.executeQuery(query);

To modify the database, use executeUpdate instead of executeQuery, and supply a string that uses UPDATE, INSERT, or DELETE. 

Process the Results

The simplest way to handle the results is to process them one row at a time, using the ResultSet’s next method to move through the table a row at a time. Within a row, ResultSet provides various getXxx methods that take a column index or column name as an argument and return the result as a variety of different Java types. For instance, use getInt if the value should be an integer, getString for a String, and so on for most other data types. If you just want to display the results, you can use getString regardless of the actual column type.

while(resultSet.next())

{
           System.out.println(results.getString(1) + " " +
           results.getString(2) + " " +
           results.getString(3));

}

Close the Connection

To close the connection explicitly, you would do:

connection.close();