Methods in Java programming that use the JDBC API to connect to a database and create a program

JDBC connection to database

The programming involved in establishing a JDBC connection is fairly straightforward. Here are four simple steps:

Import the JDBC package: add the import statement to the Java program to import the required classes in the Java code. Register the JDBC driver: this step causes the driver required for the JVM to load to be implemented in memory, so it can implement the JDBC request. Database URL rendering: this is the creation of a properly formatted address pointing to the database to which you want to connect. Create a connection object: finally, the code calls the getConnection() method of the DriverManager object to establish the actual database connection.

Import JDBC package:
The import statement tells the Java compiler where to find the class that is referenced in the code and placed at the beginning of your source code.

Using the standard JDBC package, which allows you to select, insert, update, and delete data from SQL tables, add the following imports to your source code:

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

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

Need to do this registration only once in your program. You can register a driver in one of two ways.

Method (I) -class.forname () :
The most common way to register a driver is to use Java's class.forname () method to dynamically load the driver's Class file into memory, which automatically registers it. This approach is desirable because it allows you to make the driver registration configuration easy to carry.

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);
}

The getInstance() method can be used 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 (2) - DriverManager. RegisterDriver () :
It can be used to register a driver of the second method is to use staticDriverManager. RegisterDriver () method.

If you are using an incompatible JDK JVM, for example, Microsoft provides a method to use registerDriver ().

The following example USES registerDriver() to register an 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, can build applications using the DriverManager. GetConnection () method of the connection. For the convenience of reference, make lists three overloading DriverManager. GetConnection () method:

GetConnection (String url) GetConnection (String url, the Properties prop) GetConnection (String url, String user, String password)

In this case, each form requires a database URL. The URL of the database is a reference to the database address.

Making a database URL is mostly used to establish connection related.

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

RDBMS  Name of the JDBC driver   URL format

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

All highlighted parts in the URL format are static and need to be changed with only the rest set according to the database.

Create a connection object: use the username and password of the database URL:
The following three forms DriverManager. GetConnection () method to create a connection object. The most common form of getConnection() requires that a database URL, username, and password be passed:

Value of the URL database part databaseName: suppose you are using Oracle's thin driver and 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 the full database URL is:

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

Now you must invoke the appropriate username and password and the getConnection() method 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 DriverManager. GetConnection () method only needs a database URL:

DriverManager.getConnection(String url);

However, in this case, the database URL, 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);

Using the database URL and a Properties object:
The third form DriverManager. GetConnection () method needs a database URL and a Properties object:

DriverManager. GetConnection (String url, the Properties of info).
Properties object, which holds a set of keyword-value pairs. It is used to pass the driver property to the driver when the getConnection() method is called.

To make the same connection as in 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 the 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, when the Java garbage collector closes the connection, it clears the stale object.

Relying on garbage collection, especially in database programming, is a very bad programming habit. You should always close the habit of connecting the close() method associated with the connection object.

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

To close the connection opened above, call the close() method, as shown below:

Conn. Close ();
Explicitly closing the connection DBMS saves resources.

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

Import packets. Packages containing JDBC classes that need to be database programmed need to be included. Most of the time, use import java.sql. That's it. Register the JDBC driver. The driver needs to be initialized and a communication channel can be opened with the database. Open the Connection. Need to use the DriverManager. GetConnection () method to create a Connection object, that represents the physical Connection with the database. Execute the query. Need to create and submit an SQL statement to the database using a type declared object. Extracting data from a ResultSet. Requires the appropriate method about resultset.getxxx () to retrieve data from the ResultSet. Clean up the environment. You need to explicitly shut down all database resources relative to the JVM's garbage collection.

Sample code:
The example of this example can be used as a template where you need to build a JDBC application.

Based on the environment and database installed in the previous section do this sample code has been written.

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

//STEP 1. Import required packages
import 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 compile the above example as follows:

C:>javac FirstExample.java

When you run FirstExample, it produces the following results:

C:>java FirstExample

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