SimpleProgramToAccessOracleDatabase.java Source code

Introduction

Here is the source code for SimpleProgramToAccessOracleDatabase.java

Source

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

public class SimpleProgramToAccessOracleDatabase {
    public static Connection getConnection() throws Exception {
        // load the Oracle JDBC Driver
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // define database connection parameters
        return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:database", "userName", "password");
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = null; // connection object
        Statement stmt = null; // statement object
        ResultSet rs = null; // result set object
        try {
            conn = getConnection(); // without Connection, can not do much
            // create a statement: This object will be used for executing
            // a static SQL statement and returning the results it produces.
            stmt = conn.createStatement();
            // start a transaction
            conn.setAutoCommit(false);

            // create a table called cats_tricks
            stmt.executeUpdate("CREATE TABLE cats_tricks " + "(name VARCHAR2(30), trick VARCHAR2(30))");
            // insert two new records to the cats_tricks table
            stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'r')");
            stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'j')");

            // commit the transaction
            conn.commit();

            // set auto commit to true (from now on every single
            // statement will be treated as a single transaction
            conn.setAutoCommit(true);

            // get all of the the records from the cats_tricks table
            rs = stmt.executeQuery("SELECT name, trick FROM cats_tricks");

            // iterate the result set and get one row at a time
            while (rs.next()) {
                String name = rs.getString(1); // 1st column in query
                String trick = rs.getString(2); // 2nd column in query
                System.out.println("name=" + name);
                System.out.println("trick=" + trick);
                System.out.println("==========");
            }
        } catch (ClassNotFoundException ce) {
            // if the driver class not found, then we will be here
            System.out.println(ce.getMessage());
        } catch (SQLException e) {
            // something went wrong, we are handling the exception here
            if (conn != null) {
                conn.rollback();
                conn.setAutoCommit(true);
            }

            System.out.println("--- SQLException caught ---");
            // iterate and get all of the errors as much as possible.
            while (e != null) {
                System.out.println("Message   : " + e.getMessage());
                System.out.println("SQLState  : " + e.getSQLState());
                System.out.println("ErrorCode : " + e.getErrorCode());
                System.out.println("---");
                e = e.getNextException();
            }
        } finally { // close db resources
            try {
                rs.close();
                stmt.close();
                conn.close();
            } catch (Exception e) {
            }

        }
    }
}