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

}
}
}