Methods of using JDBC API to connect to databases and create programs in Java programming

JDBC connection database

Programming involving establishing a JDBC connection is quite simple. Here are four simple steps:

1. Import the JDBC package: Add import statement to the Java program to import the required classes in Java code.
2. Register JDBC driver: This step causes the JVM to load the required drivers implemented into memory, so it can implement JDBC requests.
3. Database URL formulation: This is to create a correctly formatted address pointing to the database to which you want to connect.
4. Create a connection object: Finally, the code calls the getConnection() method of the DriverManager object to establish the actual database connection.

Importing JDBC package:
The import statement tells the Java compiler where to find the references in the code and place the class at the beginning of your source code.

Using the standard JDBC package, it allows selection, insertion, update and delete data from SQL tables, adding the following imports to your source code:

 import java.sql.* ; // for standard JDBC programsimport java.math.* ; // for BigDecimal and BigInteger support

Register the JDBC driver:
Before using it, you must register your driver in the program. A registered driver is an implementation process in which the class file of the Oracle driver is loaded into memory so that it can be used as a JDBC interface.

You need to do this registration only once in your program. A driver can be registered in one of two ways.

Method (I) - Class.forName():
The most common method in registering a driver is to use Java's Class.forName() method to dynamically load the driver's class files into memory, and it will automatically register it. This approach is desirable because it allows the driver to register configuration for easy portability.

The following example uses Class.forName() to register an Oracle driver:

 try { Class.forName("oracle.jdbc.driver.OracleDriver");}catch(ClassNotFoundException ex) { System.out.println("Error: unable to load driver class!"); System.exit(1);}

You can use the getInstance() method to resolve incompatible JVMs, but two additional exceptions are written as follows:

 try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();}catch(ClassNotFoundException ex) { System.out.println("Error: unable to load driver class!"); System.exit(1);catch(IllegalAccessException ex) { System.out.println("Error: access problem while loading!"); System.exit(2);catch(InstantiationException ex) { System.out.println("Error: unable to instantiate driver!"); System.exit(3);}

Method (II) - DriverManager.registerDriver():
The second way to use it to register a driver is to use the staticDriverManager.registerDriver() method.

It should, if you are using an incompatible JDK JVM, for example, Microsoft provides a method using registerDriver().

The following example uses registerDriver() to register the Oracle driver:

 try { Driver myDriver = new oracle.jdbc.driver.OracleDriver(); DriverManager.registerDriver( myDriver );}catch(ClassNotFoundException ex) { System.out.println("Error: unable to load driver class!"); System.exit(1);}

Database URL formulation:
When loading the driver, you can establish a connection in the program using the DriverManager.getConnection() method. For easy reference, let's list three overloaded DriverManager.getConnection() methods:

1. getConnection(String url)
2. getConnection(String url, Properties prop)
3. getConnection(String url, String user, String password)

Here, each form requires a database URL. The URL of the database points to the database address.

Developing a database URL is most commonly used to establish connections.

The following table lists the popular JDBC driver names and database URLs.

RDBMSJDBC driver name URL format

 MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/ databaseNameORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port Number:databaseNameDB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:port Number/databaseNameSybase com.sybase.jdbc.SybDriver jdbc:sybase:Tds:hostname:port Number/databaseName

All highlighted parts in URL format are static, and only the remaining parts need to be changed according to the database settings.

Create a connection object: Use the username and password of the database URL:
The following three forms of DriverManager.getConnection() method are used to create a connection object. The most common form of getConnection() requires passing a database URL, username and password:

The value of databaseName for the URL database: Assuming that you are using Oracle's thin driver, you need to specify a host: port.

Suppose there is a host TCP/IP address 192.0.0.1 and the host name and Oracle listener are configured on port 1521, the database name is EMP, and then the complete database URL is:

 jdbc:oracle:thin:@amrood:1521:EMP

Now, the appropriate username and password and getConnection() method must be called to obtain a Connection object as shown below:

 String URL = "jdbc:oracle:thin:@amrood:1521:EMP";String USER = "username";String PASS = "password"Connection conn = DriverManager.getConnection(URL, USER, PASS);

Use only one database URL:
The second form of DriverManager.getConnection() method only requires a database URL:

 DriverManager.getConnection(String url);

However, in this case, the URL of the database, including the username and password, has the following general form:

 jdbc:oracle:driver:username/password@database

So the above connection can be created as follows:

 String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";Connection conn = DriverManager.getConnection(URL);

Use the database URL and a Properties object:
The third form of DriverManager.getConnection() method requires a database URL and a Properties object:

DriverManager.getConnection(String url, Properties info);
Properties object, saves a set of keyword-value pairs. It is used to call the getConnection() method when the driver attribute is passed to the driver.

To make the same connection made through the previous example, use the following code:

 import java.util.*;String URL = "jdbc:oracle:thin:@amrood:1521:EMP";Properties info = new Properties( );info.put( "user", "username" );info.put( "password", "password" );Connection conn = DriverManager.getConnection(URL, info);

Close JDBC connection:
At the end of the JDBC program, it explicitly requires that all connections to the database be closed to end each database session. However, if you forget, the Java garbage collector closes the connection and it clears the stale objects.

Relying on garbage collection, especially in database programming, is a very poor programming habit. It should always be in the habit of closing the close() method associated with the connection object.

To ensure that the connection is closed, it can be executed in finally blocks in the code. Finally blocks will be executed, whether or not occur or not.

To close the connection opened above, the close() method should be called as follows:

conn.close();
Explicitly close connections to DBMS to save resources.

Create a JDBC application:
There are six steps involved in building a JDBC application:

1. Importing packets. You need to include packages containing JDBC classes that require database programming. In most cases, just use import java.sql.*.
2. Register the JDBC driver. The driver needs to be initialized and a communication channel can be opened with the database.
3. Open the connection. You need to use the DriverManager.getConnection() method to create a Connection object that represents a physical connection to the database.
4. Execute the query. You need to use the type declaration object to create and submit an SQL statement to the database.
5. Extract data from the result set. It is required to use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.
6. Clean up the environment. It is necessary to explicitly close all database resources that rely on the JVM's garbage collection.

Sample code:
This example can be used as a template when you need to build a JDBC application.

This sample code is written based on the environment and database installation in the previous chapter.

Copy the following example FirstExample.java, compile and run as follows:

 //STEP 1. Import required packagesimport java.sql.*;public class FirstExample { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/EMP"; // Database credentials static final String USER = "username"; static final String PASS = "password"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); //STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql; sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); //STEP 5: Extract data from result set while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values ​​System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } //STEP 6: Clean-up environment rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); } finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// nothing we can do try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try System.out.println("Goodbye!"); }//end main}//end FirstExample

Now let’s compile the above example as follows:

 C:>javac FirstExample.java

When running FirstExample, it produces the following results:

 C:>java FirstExample

 Connecting to database...Creating statement...ID: 100, Age: 18, First: Zara, Last: AliID: 101, Age: 25, First: Mahnaz, Last: FatmaID: 102, Age: 30, First: Zaid, Last: KhanID: 103, Age: 28, First: Sumit, Last: Mittal