Java Program: Simple JDBC Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;


public class JdbcExample
{

    public static void main(String[] args)
        throws SQLException
    {
        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("1. Connect to the database");

        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user", "MY_USERNAME");
        connectionProps.put("password", "MyPassword1234");
        conn = DriverManager.getConnection("jdbc:oracle:thin:@mydatabase.mycompany.com:1521:MYDBNAME", connectionProps);


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("2. Auto Commit off!!!");

        conn.setAutoCommit(false);


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("3. Create a test table with test data");

        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE ARTIST (ID NUMBER, NAME VARCHAR2(255))");

        stmt.execute("INSERT INTO ARTIST (ID, NAME) VALUES (1, 'New Ones')");
        stmt.execute("INSERT INTO ARTIST (ID, NAME) VALUES (2, 'Adele')");
        stmt.execute("INSERT INTO ARTIST (ID, NAME) VALUES (3, 'Coldplay')");
        stmt.execute("INSERT INTO ARTIST (ID, NAME) VALUES (4, 'Silent')");

        conn.commit();


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("4. Get a Column of the first Row - access column by Position:");

        PreparedStatement pStmt = conn.prepareStatement("SELECT a.ID, a.NAME FROM ARTIST a WHERE a.ID = 2");
        ResultSet rset = pStmt.executeQuery();
        rset.next();

        System.out.println("    Artist ID  : " + rset.getString(1));
        System.out.println("    Artist Name: " + rset.getString(2));


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("5. Get a Column of the first Row - access column by Name: ");

        pStmt = conn.prepareStatement("SELECT a.ID, a.NAME FROM ARTIST a WHERE a.ID = 2");
        rset = pStmt.executeQuery();
        rset.next();

        System.out.println("    Artist ID  : " + rset.getString("ID"));
        System.out.println("    Artist Name: " + rset.getString("NAME"));


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("6. Process all Rows:");

        pStmt = conn.prepareStatement("SELECT a.ID, a.NAME FROM ARTIST a");
        rset = pStmt.executeQuery();

        while(rset.next()) {
            System.out.println("    " + rset.getString("NAME"));
        }


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("7. Get all Rows:");

        pStmt = conn.prepareStatement("SELECT a.ID, a.NAME FROM ARTIST a");
        rset = pStmt.executeQuery();
        java.util.List<String> resultRows = new ArrayList<String>();

        while(rset.next()) {
            resultRows.add(rset.getString("NAME"));
        }

        for(String name : resultRows) {
            System.out.println("    " + name);
        }


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("8. Bind Variables:");

        // Build a (reusable) prepared statement:
        String command = "SELECT count(*) as found_artists, max(a.name) as max_name \n"
                       + "  FROM ARTIST a \n"
                       + " WHERE a.ID = ? \n"
                       + "   AND a.NAME = ?";
        pStmt = conn.prepareStatement(command);

        // Look for Adele:
        int id = 2;
        String name = "Adele";
        pStmt.setInt(1, id);
        pStmt.setString(2, name);
        rset = pStmt.executeQuery();
        rset.next();
        System.out.println("    Found Artists (ID = " + id + ", NAME = " + name + "): " + rset.getString("FOUND_ARTISTS"));

        // Look for Coldplay - reuse the prepared statement!
        id = 3;
        name = "Coldplay";
        //pStmt.setInt(1, id);
        //pStmt.setString(2, name);
        rset = pStmt.executeQuery();
        rset.next();
        System.out.println("    Found Artists (ID = " + id + ", NAME = " + name + "): " + rset.getString("FOUND_ARTISTS"));

        // Look for XYZ (not there) - reuse the prepared statement!
        id = 9999;
        name = "XYZ";
        pStmt.setInt(1, id);
        pStmt.setString(2, name);
        rset = pStmt.executeQuery();
        rset.next();
        System.out.println("    Found Artists (ID = " + id + ", NAME = " + name + "): " + rset.getString("FOUND_ARTISTS"));


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("9. Drop test table");

        stmt.execute("DROP TABLE ARTIST");


        //////////////////////////////////////////////////////////////////////////////////////////
        System.out.println("10. Close the connection");

        conn.close();
    }

}

Output:

1. Connect to the database

2. Auto Commit off!!!

3. Create a test table with test data

4. Get a Column of the first Row - access column by Position:
    Artist ID  : 2
    Artist Name: Adele

5. Get a Column of the first Row - access column by Name: 
    Artist ID  : 2
    Artist Name: Adele

6. Process all Rows:
    New Ones
    Adele
    Coldplay
    Silent

7. Get all Rows:
    New Ones
    Adele
    Coldplay
    Silent

8. Bind Variables:
    Found Artists (ID = 2, NAME = Adele): 1
    Found Artists (ID = 3, NAME = Coldplay): 1
    Found Artists (ID = 9999, NAME = XYZ): 0

9. Drop test table

10. Close the connection