Database

This page provides an example of how to create a simple JDBC application. This will show you how to open a database connection, execute a SQL query, and display the results.

There are  5  steps to connect any java application with the database in java using JDBC. They are as follows:


1. Register the Driver class
2. Create the Connection object
3. Create statement Object
4. Execute Query
5. Closing the Connection

    


Register the driver class


The most common approach 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. The forName() method of Class class is used to register the driver class.


MySQL

Class.forName("com.mysql.jdbc.Driver");


Oracle
Class.forName("oracle.jdbc.driver.OracleDriver");


DB2

Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");


Sybase
Class.forName("com.sybase.jdbc.SybDriver")
 


RDBMS JDBC driver name 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


Create the Connection Object

The getConnection() method of DriverManager class is used to establish connection with the database.
If you have a host at TCP/IP address 192.0.0.1 with a host name of javaseleniumworld , and your Oracle listener is configured to listen on port 1521, and your database name is EMP, then complete database URL would then be:

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

 

Create statement Object

The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.


     Statement stmt=con.createStatement();


Execute Query

The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

   
     ResultSet rs=stmt.executeQuery("select * from emp");    
    while(rs.next())
    {  
      System.out.println(rs.getInt(1)+" "+rs.getString(2));
     }


Closing the connection

 By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection. At the end of your JDBC program, it is required explicitly close all the connections to the database to end each database session. However, if you forget, Java''s garbage collector will close the connection when it cleans up stale objects.

    
       con.close();

 



Example

This example will explain on how to use above mentioned 5 sections in a Java Code.

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