JDBC Sample Java Code to Connect With Oracle

In previous blog I covered different interface of java.sql package that are important for writing jdbc code to get connected to the database. In this blog we will study a sample code on how we can get connected to the Oracle data base and execute a sample query to get the Result set and print it on the console.

For this purpose we are assuming that oracle is installed on our local system and we have a schema called "vinodoracle" and a password "vinodoracle". We also assume we do have a table with name EMP with column names "EMPLOYEEID","NAME" and "SALARY" of types NUMBER,VARCHAR2 and NUMBER respectively.

The table does look as below :

The comments in the below program are self explanatory.

The Line #22 loads the OracleDriver class and initializes the driver. The line #24 supplies the url,uid and pwd and creates a coonection to oracle data base. We assume Oracle is installed on local machine so localhost in our url. Line #27 obtains a statement type object from the connection and Line #29 executes SELECT query and stores the result in ResultSet type object of Oracle. Which can then be printed as we are doing.

We are catching ClassNotFoundException for Class.forName("") call. If we don't have oracle supplied Driver jar in our class path we do catch this exception. So make sure we have ojdbc14jar in our class path .

The url information can be obtained from a file called trans.ora file within oracle installation folder.

Please focus on how we are retrieving the values from the record set. RecordSet interface provides methods like getInt, getString etc.. to retrieve the values from the current record set. We can either pass Column Name or Column Number as parameter. In the below code at Line #35 we use Column Name to get the values of EMPLOYEEID and NAME . But we are getting SALARY by using column number as parameter to getNumber . Also note that column numbers are not zero based . They start from one.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

package com.vinod.oracle; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class FirstOracleConnection { public static void main(String[] args) { String url="jdbc:oracle:thin:@localhost:1521:XE"; String uid="ravioracle"; String pwd="ravioracle"; try { //Loading the OracleDriver Class Class.forName("oracle.jdbc.driver.OracleDriver"); //Creating the Connection type object by providing the url , uid and password parameters to DriverManager Connection con=DriverManager.getConnection(url, uid, pwd); System.out.println("Connection Successful"); //Get a Statement type object from Connection Statement st=con.createStatement(); //Execute a query ResultSet rs=st.executeQuery("SELECT * FROM EMP"); System.out.println("EMPLOYEEID EMPLOYEENAME SALRAY" ); // Resord set by default is pointed to a Top-1 location. So we need to move next and then access the first record. while(rs.next()) { System.out.println(""+rs.getInt("EMPLOYEEID")+"\t "+rs.getString("NAME")+"\t "+rs.getInt(3)); } rs.close(); con.close(); } catch(ClassNotFoundException cnf) { } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }