Java Code to connect to oracle database and call query with PreparedStatement Class

Below is the simple example to connect to oracle database and to call queries using PreparedStatement Class.




Connect to database:



import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;



public class DBConnection {





DBConnection() {

}



protected Connection dbInitiateConnection() {

Connection con = null;

String dbHost = “dbhost";

String dbPort = dbport;

String dbSid = dbsid;

String dbUsername = dbusername;

String dbpassword = dbpassword;





try {

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

} catch (ClassNotFoundException ex) {

ex.printStackTrace();

} catch (NullPointerException npe) {

npe.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}



try {

con = DriverManager.getConnection("jdbc:oracle:thin:@" + dbHost + ":" + dbPort + ":" + dbSid, dbUsername, dbpassword);

con.setAutoCommit(false);

} catch (SQLException se) {

se.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}



return con;

}

}







Calling the queries to database:

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;



public class DBDetails {



protected void getDateDetailsFromDatabase(Connection con) {

String queryDate = "select sysdate from dual";



try {

PreparedStatement psDate = con.prepareStatement(queryDate);

ResultSet rsDate = psDate.executeQuery();



while (rsDate.next()) {

java.sql.Date date = rsDate.getDate(1);

System.out.println(date.toString());

}

} catch (SQLException ex) {

ex.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}



protected void getTableDetailsFromDatabase(Connection con) {

String queryAllTables = "SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'E%' AND ROWNUM <= 10";

try {

PreparedStatement psAllTables = con.prepareStatement(queryAllTables);

ResultSet rsAllTables = psAllTables.executeQuery();



while (rsAllTables.next()) {

String table_name = rsAllTables.getString(1);

System.out.println(table_name);

}

} catch (SQLException ex) {

ex.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}




protected String getCreatedDateDetailsFromDatabase(Connection con, String customerID) {



String queryDate = "a_query";

//put any query you like, here I am assuming that you are trying to extract the date from a table.

String createdDate = new String("");



System.out.println(queryDate);

try {

PreparedStatement psDate = con.prepareStatement(queryDate);

ResultSet rsDate = psDate.executeQuery();



while (rsDate.next()) {

java.sql.Date date = rsDate.getDate(1);

createdDate = date.toString();

System.out.println(createdDate);

}

} catch (SQLException ex) {

ex.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}



return createdDate;

}



}













Main class to call the other classes:





import java.sql.Connection;



public class Main {





public static void main(String[] args) {

DBConnection dbConnection = new DBConnection();

DBDetails dbDetails = new DBDetails();



Connection con = dbConnection.dbInitiateConnection();



if (con != null) {

dbDetails.getDateDetailsFromDatabase(con);

}
}
}

Source code to connect to a Oracle Database – Java

This is sample source code to connect to Oracle Database.
You need to have Oracle Driver [Can be downloaded from http://www.findjar.com/jar/mule/dependencies/maven1/oracle-jdbc/jars/ojdbc14.jar.html], the ip address of the machine where database exists and service identifier of the database. We use 1521 as a default ip address to connect to the database.



/*
* DBConnection.java
*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBConnection {

Connection con;
DBConnection() {
}

private void dbInitiateConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
} catch(NullPointerException npe) {
npe.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}

try {
con = DriverManager.getConnection("jdbc:oracle:thin:@ipaddress:1521:serviceidentifierofdatabase","username","password");
con.setAutoCommit(false);
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}


private void getDataFromTable() {
try {
PreparedStatement psd = con.prepareStatement("select empno from emp");
ResultSet rst = psd.executeQuery();
System.out.println("Employee Nos are ");
while(rst.next()){
int empNo = rst.getInt(1);
System.out.println(empNo);
}
rst.close();
psd.close();
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}




private void dbCloseConnection() {
try {
con.close();
} catch(SQLException ex) {
ex.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}
}

public static void main(String args[]) {
DBConnection dbConnect = new DBConnection();
dbConnect.dbInitiateConnection();
dbConnect.getDataFromTable();
dbConnect.dbCloseConnection();
}

}