Connect to an Oracle database with JDBC in Java

JDBC mean Java Database Connectivity. In java, using JDBC drivers, we can connect to database.
Steps to connect to JDBC.
1) Load the driver, using Class.forName(DriverName);
2) Get the connection object, Connection con = Driver.getConnection(loaded driver name);
3) Create a SQL statement, Statement s = con.createStatement();
4) Create Resultset object using the statement created above, ResultSet rs = s.executeQuery("sql statement");

Iterate the result set to get all the values from the database.

Finally don't miss this
5) s.close();
6) con.close() ;

Example:

import java.sql.*;

public class TestDBOracle {

  public static void main(String[] args)
      throws ClassNotFoundException, SQLException
  {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //
    // or
    // DriverManager.registerDriver
    //        (new oracle.jdbc.driver.OracleDriver());

        String url = "jdbc:oracle:thin:@//server.local:1521/prod";
    //               jdbc:oracle:thin:@//host:port/service
    // or
    // String url = "jdbc:oracle:thin:@server.local:1521:prodsid";
    //               jdbc:oracle:thin:@host:port:SID
    //
    //  SID  - System ID of the Oracle server database instance.
 //         By default, Oracle Database 10g Express Edition
 //         creates one database instance called XE.
 //         ex : String url = "jdbc:oracle:thin:@myhost:1521:xe";



    Connection conn =
         DriverManager.getConnection(url,"scott","tiger");

    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    ResultSet rset =
         stmt.executeQuery("select BANNER from SYS.V_$VERSION");
    while (rset.next()) {
         System.out.println (rset.getString(1));
    }
    stmt.close();
    System.out.println ("Ok.");
  }
}

How to insert image in oracle database using java? |Insert Image In To Oracle Data Base Using Servlet, Jdbc

In this tutorial we will see how to insert data into oracle data base using java program/application.

Prerequisites:

1. All configuration must me done to connect java
   Image via Wikipedia
   application to oracle before starting this task. As described in my previous tutorial 'How to connect java application with oracle database'.

Steps: 
1.First start your oracle database and create a table of your choice. eg - create table student (id number(3), name varchar2(30),class number(2),marks number(3));
2. Now open IDE and write down the java code for the application which will connect to the oracle database and enter the data to the specific table.
3. After compilation and execution. You will get data inserted in the database.

Source Code for 'Inserting data into oracle database using java program/application':

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class InsertRecord {

public static void main(String[] args) {

String driver="sun.jdbc.odbc.JdbcOdbcDriver";
String cs="jdbc:odbc:connect_oracle";
String user = "system";
String pwd = "tom";
String sqlstmt="INSERT INTO STUDENT VALUES(1,'Steve',5,70)";
Connection con = null;
Statement st = null;
try
{
Class.forName(driver);
}
catch(Exception e)
{
System.out.println(e);
}
System.out.println("Driver loaded");
try
{
con=DriverManager.getConnection(cs,user,pwd);
System.out.println("Connected to the Oracle Database");
st = con.createStatement();//creates a Statement object for sending SQL statements to the database.
int updatecount=st.executeUpdate(sqlstmt);//return either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing
System.out.println(updatecount+" row inserted");
}
catch(Exception e)
{
System.out.println(e);
}
try
{
st.close();
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}//main()
}//class()

Steps for connecting to the database using JDBC

Using DriverManager: 
1. Load the Driver class using class.forName(driverclass) and class.forName() loads the driver class and passes the control to DriverManager class 
2.DriverManager.getConnection() creates the Connection to the databse 
 
Using DataSource. 
DataSource is used instead of DriverManager in Distributed Environment with the help of JNDI. 
1. Use JNDI to lookup the DataSource from Naming service server. 
2. DataSource.getConnection method will return Connection object to the database

When would you use a JDBC-ODBC Bridge?

Answer:
When ever someone wants to access microsoft oriented databases such as MS Access MS SQL server you have to use the JDBC-ODBC bridge. In general it is recommended not to use this type of bridge

What is Connection Pooling?

With servlets, opening a database connection is a major bottleneck because we are creating and tearing down a new connection for every page request and the time taken to create connection will be more.Creating a connection pool is an ideal approach for a complicated servlet. With a connection pool, we can duplicate only the resources we need to duplicate rather than the entire servlet. A connection pool can also intelligently manage the size of the pool and make sure each connection remains valid. Anumber of connection pool packages are currently available. Some like DbConnectionBroker are freely available from Java Exchange Works by creating an object that dispenses connections and connection Ids on request.The ConnectionPool class maintains a Hastable, using Connection objects as keys and Boolean values as stored values. The Boolean value indicates whether a connection is in use or not. A program calls getConnection( ) method of the ConnectionPool for getting Connection object it can use; it callsreturnConnection( ) to give the connection back to the pool