How to connect Database using java Program

import java.sql.*;

class OracleCon {
  public static void main(String args[]) {
    try {
      // step1 load the driver class 
      Class.forName("oracle.jdbc.driver.OracleDriver");

      // step2 create the connection object
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "oracle");

      // step3 create the statement object
      Statement stmt = con.createStatement();

      // step4 execute query
      ResultSet rs = stmt.executeQuery("select * from emp");

      while (rs.next()) {
        System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3));
      }

      // step5 close the connection object
      con.close();
    } catch (Exception e) {
      System.out.println(e);
    }
  }
}

Step-by-Step Breakdown:

1. Import Necessary Classes:

import java.sql.*;

The java.sql package is imported, which contains classes required for JDBC operations such as Connection, Statement, and ResultSet.

2. Class Declaration:

class OracleCon{

The program is enclosed in a class called OracleCon.

3. Main Method:

public static void main(String args[]){

The main method is the entry point of the program. This is where the program begins execution.

4. Try-Catch Block:

• Exception handling by using try-catch block during the execution of the code for database.
• Any exception thrown during this process will be caught and printed out in the program by the help of the catch block.

5. Loading the Oracle JDBC Driver:

//step1 load the driver class

Class.forName(“oracle.jdbc.driver.OracleDriver”);.

This is required for connecting to the Oracle database.

6. Connecting to a Database:

 //step2 create the connection object

Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”, “system”, “oracle”);

• The DriverManager.getConnection() method opens a connection to the database.
• Connection URL: jdbc:oracle:thin:@localhost:1521:xe This is a connection to an Oracle database running on the local machine, localhost using port 1521 and SID xe (Oracle Express Edition).
• The following are the credentials to connect to the database.

   Username: system

    Password: oracle

7. Create Statement Object

//step3 create the statement object

Statement stmt = con.createStatement();

• The createStatement() method on the connection object (con) is used to create a Statement object.
• The Statement object is used to send SQL queries to the database.

8. Execute a Query:

//step4 execute query

ResultSet rs = stmt.executeQuery(“select * from emp”);

• The executeQuery() method of the Statement object executes an SQL query.
• The query  “select * from emp ” fetches all the rows from the emp table.
• The query result is stored in a ResultSet object (rs), which is used to traverse the result set.

9. Iterating Through the Result Set:

while(rs.next())

System.out.println(rs.getInt(1) + ” ” + rs.getString(2) + ” ” + rs.getString(3));

• Checks for the presence of a next row in the result set rs.next();
• Fetches, for each row of the result set:

      the first column of the row as an int: rs.getInt(1)

the second column of the row as a String: rs.getString(2)

     the third column of the row as a String: rs.getString(3)

• These values are printed to the console.

10. Connection Close:

//step5 close the connection object

con.close();

con.close() is the method that calls in order to close the database connection and to release any resource being used by the connection

11. Exceptions:

}catch(Exception e){

System.out.println(e);

 }

Any exception occurring anywhere in the program is caught by the catch block, and the exception message is printed.

Assumptions

• The oracle database is installed and running on localhost. The local machine.
• There is a table called emp existing in the database and has some data.
• The connection has been made by the correct username, system, and password, oracle.

Example Output:

Consider that table emp has the following data:

| emp_id | emp_name   | emp_role   |

|——–|————|————|

| 1      | John       | Manager    |

| 2      | Alice      | Developer  |

| 3      | Bob        | Analyst    |

1. John Manager

2. Alice Developer

3. Bob Analyst

This is the output of running the SQL query SELECT * FROM emp on the database, returning all rows from table emp.

Summary:

1. JDBC Driver: Driver registration using Class.forName().

2. Connection : Driver manager connection for Oracle database, DriverManager.getConnection().

3. SQL Query Execution: Using Statement, perform SQL query and then retrieve result from the ResultSet.

4. Iteration over the result set: Prints values.

5. Cleanup: Closing the connection after executing a query.

As part of the examples, a very simple connection to an Oracle database using JDBC then running a query and getting results back is displayed.