Oracle-JDBC DBUtil : Helps you connect to Oracle DB from java

Here is the DBUtil to connecto to Oracle DB

NOTE: Need to download ojdbc6.jar from oracle official site and add it in the libraries.

We can get this from maven, but some times it might not work as expected.

<!-- https://mvnrepository.com/artifact/oracle/ojdbc6 -->
<dependency>
    <groupId>oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>

Code starts here

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

/**
 * DBUtil class is to make connections to DB and execute query
 */
public class DbUtil {
Connection sqlConnection = null;
ResultSet resultSet = null;
Statement statement = null;
String driver = "oracle.jdbc.driver.OracleDriver";
String serverName, dbName, portNo, userName, PassWord;

/**
* Constructor, which fetches db property values form config files
*/
public DbUtil() {
/* fetches working directory path */
String user_dir = System.getProperty("user.dir");
/*
* PropertiesReaderUtility reads selenium.properties and loads in prop
* variable
*/
PropertiesReaderUtility prop = new PropertiesReaderUtility(user_dir + "/selenium.properties");
serverName = System.getProperty("test.db.servername", prop.getProperty("test.db.servername"));
dbName = System.getProperty("test.db.dbname", prop.getProperty("test.db.dbname"));
portNo = System.getProperty("test.db.port", prop.getProperty("test.db.port"));
userName = System.getProperty("test.db.username", prop.getProperty("test.db.username"));
PassWord = System.getProperty("test.db.password", prop.getProperty("test.db.password"));
}

/**
* makes DB connection
*
* @return
*/
private Connection getConncetion() {
try {
Class.forName(driver);
String host = "jdbc:oracle:thin:@" + serverName + ":" + portNo + "/" + dbName;
sqlConnection = DriverManager.getConnection(host, userName, PassWord);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return sqlConnection;
}

/**
* connect to db and execute the query and fills in resultset
*
* @param query
* @return
*/
public ResultSet runQuery(String query) {
try {
sqlConnection = getConncetion();
statement = sqlConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
resultSet = statement.executeQuery(query);
} catch (Exception e) {
e.printStackTrace();
}
return resultSet;
}

/**
* Method to fetch no. of rows or size of the given result set
*
* @param rs
* @return
*/
public int getResultSetSize(ResultSet rs) {
int i = 0;
try {
while (rs.next()) {
i++;
}
while (rs.previous()) {
}
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}

/**
* closes the all the opened objects
*/
public void close() {
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (sqlConnection != null)
sqlConnection.close();
} catch (Exception ex) {
}
}
}