Java code to connect to Oracle database and execute query

Java code to connect to Oracle database and execute query

Here is an example of how you can use Java to connect to an Oracle database and execute a query:

import oracle.jdbc.*;
import oracle.jdbc.pool.*;
// Load the Oracle JDBC driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// Create a connection URL
String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
// Set the user name and password for the database
String username = "username";
String password = "password";
// Create a new OracleConnection object
OracleConnection conn = null;
try {
// Establish a connection to the database
conn = DriverManager.getConnection(url, username, password);
// Create an OracleStatement object to execute the query
OracleStatement stmt = conn.createStatement();
// Execute the query
ResultSet resultSet = stmt.executeQuery("SELECT * FROM MY_TABLE");
// Process the results of the query
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
}
} catch (SQLException e) {
// Handle any errors that occur during the connection process
e.printStackTrace();
} finally {
// Close the connection when we're done with it
conn.close();
}

In this example, we first load the Oracle JDBC driver using the Class.forName() method. We then create a connection URL by specifying the hostname, port number, and database name.

Next, we set the user name and password for the database. These credentials are used to establish a connection to the database.

We then use the DriverManager.getConnection() method to establish a connection to the database using the connection URL, username, and password.

Once we have established a connection, we create an OracleStatement object to execute a query on the database. We can execute any SQL statement using this object, including SELECT statements, INSERT statements, UPDATE statements, and DELETE statements.

In this example, we execute a SELECT statement that retrieves all rows from a table called MY_TABLE. The results of the query are returned as a ResultSet object, which we can then process to extract the desired data.

Finally, we close the connection when we're done with it using the close() method. This is important because Oracle has a limit on the number of simultaneous connections that can be established from a particular Java program.

Here are some notes about the above code:

Here is a more complete example that includes error handling:

import oracle.jdbc.*;
import oracle.jdbc.pool.*;
public class OracleExample {
public static void main(String[] args) {
try {
// Load the Oracle JDBC driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// Create a connection URL
String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
// Set the user name and password for the database
String username = "username";
String password = "password";
// Create a new OracleConnection object
OracleConnection conn = null;
try {
// Establish a connection to the database
conn = DriverManager.getConnection(url, username, password);
// Create an OracleStatement object to execute the query
OracleStatement stmt = conn.createStatement();
// Execute the query
ResultSet resultSet = stmt.executeQuery("SELECT * FROM MY_TABLE");
// Process the results of the query
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
}
} catch (SQLException e) {
// Handle any errors that occur during the connection process
e.printStackTrace();
} finally {
// Close the connection when we're done with it
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// Ignore any errors closing the connection
}
}
}
} catch (ClassNotFoundException e) {
// Handle the case where the Oracle JDBC driver is not found
System.out.println("Oracle JDBC driver not found");
}
}
}

This example includes error handling for both the DriverManager.getConnection() method and the OracleStatement.executeQuery() method.

How to connect Oracle SQL Developer using Java?

Connecting Oracle SQL Developer using Java requires the usage of JDBC (Java Database Connectivity) technology. Here's a step-by-step guide on how to do it:

Prerequisites:

Step 1: Import the necessary libraries

In your Java project, import the required libraries:

import java.sql.*;

Step 2: Load the Oracle JDBC driver

Load the Oracle JDBC driver using the Class.forName() method:

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

Step 3: Establish a connection to the database

Create a Connection object using the DriverManager.getConnection() method, providing the URL, username, and password as parameters:

String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
String username = "your_username";
String password = "your_password";
Connection conn = DriverManager.getConnection(url, username, password);

Step 4: Create a Statement object

Create a Statement object using the conn.createStatement() method:

Statement stmt = conn.createStatement();

Step 5: Execute SQL queries or statements

Use the stmt.execute() method to execute your SQL query or statement. For example, if you want to execute an SQL SELECT statement:

String sqlQuery = "SELECT * FROM your_table";
ResultSet resultSet = stmt.executeQuery(sqlQuery);

Step 6: Process the result set (optional)

If you need to process the result set, iterate through the resultSet object using a while loop:

while (resultSet.next()) {
int id = resultSet.getInt("ID");
String name = resultSet.getString("NAME");
// Process the data as needed...
}

Step 7: Close the database connection

Close the Connection object when you're done using it:

conn.close();

Here's a complete example code snippet:

import java.sql.*;
public class OracleJdbcExample {
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
String username = "your_username";
String password = "your_password";
try {
// Load the Oracle JDBC driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// Establish a connection to the database
Connection conn = DriverManager.getConnection(url, username, password);
// Create a Statement object
Statement stmt = conn.createStatement();
// Execute an SQL SELECT statement
String sqlQuery = "SELECT * FROM your_table";
ResultSet resultSet = stmt.executeQuery(sqlQuery);
// Process the result set (optional)
while (resultSet.next()) {
int id = resultSet.getInt("ID");
String name = resultSet.getString("NAME");
System.out.println("ID: " + id + ", Name: " + name);
}
// Close the database connection
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

This example demonstrates a basic Java program that connects to an Oracle database using JDBC. You can customize this code to fit your specific needs and adapt it for more advanced tasks, such as inserting or updating data in the database.