Java Database Connectivity with Oracle

Connecting to Oracle Datatabase using a Java Application

About JDBC

JDBC Driver is a software component that enables java application to interact with the database. There are 4 types of JDBC drivers:

1.JDBC- ODBC bridge driver

2.Native -API driver (partially java driver)

3.Network Protocol driver (fully java driver)

4.Thin driver (fully java driver) – recommended to use

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

Register the Driver class

Create connection

Create Statement/Prepare Statement

Execute queries

Close connection

1.Diver class: The driver class for the oracle database is "oracle.jdbc.driver.OracleDriver."

   Syntax : Class.forName("Diver class")

2.Connection URL: The connection URL for the oracle10G database is “jdbc:oracle:thin:@localhost:1521:xe”  where jdbc is the API, oracle is the database, thin is the driver, localhost is the server name on which oracle is  running, we may also use IP address, 1521 is the port number and XE is the Oracle service name. You may get all these information from the tnsnames.ora file.

  

   jdbc - api

   oracle - databaes

   thin   - driver type

   1521 - exculusive port number of oracle listener

   Syntax : Connection con=DriverManager.getConnection("Connection URL");

3.Username: The default username for the oracle database is system.

4.Password: It is the password given by the user at the time of installing the oracle database.

5. Get downloaded file "ojdbc14.jar" and copy the file in the folder  jre/lib/ext

    Ex: C:\Program Files (x86)\Java\jre1.8.0_60\lib\ext

Program to Connect to Oracle and retrieve the data from a table.

import java.sql.*; 

class OracleCon

{ 

  public static void main(String args[])

 { 

    try

   { 

     //step1 load the driver class 

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

     //step2 create  the connection object 

    Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr"); 

         //step3 create the statement object 

     //Statement stmt=con.createStatement();

     PreparedStatement ps=con.prepareStatement("select * from emp"); 

     ResultSet rs=ps.executeQuery();

      //step4 execute query 

      //ResultSet rs=stmt.executeQuery("select * from employees"); 

      while(rs.next()) 

          System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3)); 

 

      //step5 close the connection object 

        con.close(); 

   }

   catch(Exception e)

   {

      System.out.println(e);

   } 

} 

}

Output:

E:\>javac OracleCon.java

E:\>java OracleCon

111  NAMMU  ANALYST

222  MOHAN  CLERK

333  GURU  MANAGER

444  KRISHNA  ENGINEER

Java ResultSetMetaData Interface

If you have to get metadata of a table like total number of column, column name, column type etc. , ResultSetMetaData interface is used

Commonly used methods of ResultSetMetaData interface

Commonly used methods of ResultSetMetaData interface

Method                                                                          Description

public int getColumnCount()throws SQLException               it returns the total number of

                                                                                          columns in the ResultSet object.

public String getColumnName(int index)     it returns the column name of the specified

throws SQLException                                  column index.

public String getColumnTypeName(int index)   it returns the column type name

throws SQLException                                        for the specified index.

public String getTableName(int index)           it returns the table name for the

throws SQLException                                    specified column index.

Write a program to Display Resultset Metadata

import java.sql.*; 

class rsmd{ 

public static void main(String args[]){ 

try{ 

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

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr"); 

 

PreparedStatement ps=con.prepareStatement("select * from emp"); 

ResultSet rs=ps.executeQuery(); 

ResultSetMetaData rsmd=rs.getMetaData(); 

 

System.out.println("Total columns: "+rsmd.getColumnCount()); 

System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1)); 

System.out.println("Column Type Name of 1st column: "+rsmd.getColumnTypeName(1)); 

 

con.close(); 

}catch(Exception e){ System.out.println(e);} 

} 

} 

Output:

E:\>javac rsmd.java

E:\>java rsmd

Total columns: 5

Column Name of 1st column: EMPNO

Column Type Name of 1st column: NUMBER

Towers Of Hanoi Program

//Program to demonstrate Towers Of Hanoi 
import java.util.Scanner;
class tb
{ 
// Java recursive function to solve tower of BRAHMA/HANOI puzzle 
 static int c=0;   
 static void th(int n, char ST, char DT, char TT) 
    { 
        if (n == 1) 
        { 
            System.out.println("Move disk 1 from PEG " +  ST + " to PEG " + DT); 
            c++;
            return; 
        } 
        th(n-1, ST, TT, DT); 
        System.out.println("Move disk " + n + " from PEG " +  ST + " to PEG " + DT); 
        c++;
        th(n-1, TT, DT, ST);
         
    }
    public static void main(String args[]) 
    { 
        Scanner s=new Scanner(System.in);
        System.out.println("Enter Number Of Disks To Move:");
        int n = s.nextInt(); // Number of disks 
        th(n, 'A', 'C', 'B');  // A, B and C are names of PEG/TOWERS
        System.out.println("\nTotal Number Of Moves Occurred: "+c); 
    }
}

Output:
E:\>java tb
Enter Number Of Disks To Move:
1
Move disk 1 from PEG A to PEG C

Total Number Of Moves Occurred: 1

E:\>java tb
Enter Number Of Disks To Move:
2
Move disk 1 from PEG A to PEG B
Move disk 2 from PEG A to PEG C
Move disk 1 from PEG B to PEG C

Total Number Of Moves Occurred: 3

E:\>java tb
Enter Number Of Disks To Move:
3
Move disk 1 from PEG A to PEG C
Move disk 2 from PEG A to PEG B
Move disk 1 from PEG C to PEG B
Move disk 3 from PEG A to PEG C
Move disk 1 from PEG B to PEG A
Move disk 2 from PEG B to PEG C
Move disk 1 from PEG A to PEG C

Total Number Of Moves Occurred: 7

Towers Of Brahma or Towers of Hanoi

There is a story about an ancient temple in India has a large room with three towers surrounded by 64 golden disks. These disks are continuously moved by priests in the temple. According to a prophecy, when the last move of the puzzle is completed the world will end. These priests acting on the prophecy, follow the immutable rule by Lord Brahma of moving these disk one at a time. Hence this puzzle is often called Tower of Brahma(and also called Tower of Hanoi) puzzle.



What is the game of Tower of Hanoi?
Tower of Hanoi consists of three pegs or towers with n disks placed one over the other.
The objective of the puzzle is to move the stack to another peg following these simple rules.
Only one disk can be moved at a time.
No disk can be placed on top of the smaller disk

But what about the prophecy for the tower of Hanoi where the priests are using 64 disks?
Suppose that these priests are highly powerful and can move these massive disks at a speed of 1 per second per hour every day. At this speed, they would need 2^64 -1 move to complete the task.
That is, 18,446,744,073,709,551,615 moves to complete, which would take about 580 billion years

 Merge Sort ::  Data structure | Merge Sort using JAVA code

File Name : Mrgsrt.java

import java.util.*;

public class Mrgsrt

{

    public static void main(String[] args)

   {

      int a[],n,i;

      Scanner s=new Scanner(System.in);

      System.out.println("Enter size of the aay: ");

      n=s.nextInt(); 

      a=new int[n];

      System.out.println("Enter "+n+" Elements : ");

      for (i=0;i<n;i++)

      a[i]=s.nextInt();

      System.out.println("Elements in Array Before Merge Sort is: ");

      for (i=0;i<n; i++)

      System.out.print(a[i] + " ");

      System.out.println("\n");

      a=Merge_sort(a, n);

      System.out.println("Elements in Array After Merge Sort is: ");

      for (i=0;i<n; i++)

      System.out.print(a[i] + " ");

      System.out.println("\n");

   }

      static int[] Merge_sort(int a[], int size)

     {

        if (size>1)

       {

        int mid=size /2,first[],second[];

        first = Arrays.copyOfRange(a, 0, mid);

        first = Merge_sort(first, mid); // recursive call for first half aay

        second = Arrays.copyOfRange(a, mid, size);

        second = Merge_sort(second, size - mid); // recursive call for second half aay

        a=Merge_arrays(first, second, mid, size - mid);

     }

      return a;

  }

  static int[] Merge_arrays(int first[], int second[], int n, int m) // respectively

 {

    int a[] = new int[n + m];

    int i = 0, f = 0, s = 0;

    while(f<n && s<m)

    {

       a[i++]=(first[f]<second[s])?first[f++]:second[s++];

     }

    while(f <n)

      a[i++] = first[f++];

    while(s<m)

      a[i++]=second[s++];

    return a;

  }

}

Compilation

E:\>javac Mrgsrt.java

Execution

E:\>java Mrgsrt

Output

Enter size of the aay:

6

Enter 6 Elements :

23

5

66

1

8

99

Elements in Array Before Merge Sort is:

23 5 66 1 8 99

Elements in Array After Merge Sort is:

1 5 8 23 66 99