JDBC

Overview

JDBC 4 Compliance

Hello World Example

// You need to have h2.jar in the same folder
// Compile and run: javac App.java; java -cp ".:h2.jar" App
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");

Statement s = conn.createStatement();
s.execute("CREATE TABLE person(id int, name varchar, lastname varchar);");
s.execute("INSERT INTO person VALUES(1, 'Koray', 'Tugay');");
s.execute("INSERT INTO person VALUES(2, 'John', 'Doe');");
s.execute("INSERT INTO person VALUES(3, 'Jane', 'Doe');");
s.execute("INSERT INTO person VALUES(4, 'Pinar', 'Tugay');");

ResultSet rs = s.executeQuery("SELECT * FROM person");

ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();

String[] columnNames = new String[colCount];
for (int i = 1; i <= colCount; i++) 
    columnNames[i-1] = rsmd.getColumnName(i);

String header = Stream.of(columnNames).collect(Collectors.joining("\t"));
System.out.println(header);

while (rs.next()) {
    for (String columnName : columnNames)
        System.out.print(rs.getObject(columnName) + "\t");
    System.out.println("");            
}

Output

ID	NAME	LASTNAME
1	Koray	Tugay	
2	John	Doe	
3	Jane	Doe	
4	Pinar	Tugay	

Notes on Hello World

ResultSet

ResultSetType

ResultSetConcurrency

Finding Number of Rows Using TYPE_SCROLL_INSENSITIVE

Connection con = DriverManager.getConnection("jdbc:derby:mydb;create=true");

Statement st = con.createStatement();
try {
    st.execute("DROP TABLE person");
} catch (Exception ignored) {}
st.execute("CREATE TABLE person(Id INT, Name VARCHAR(255))");
st.execute("INSERT INTO person VALUES(1, 'Koray')");
st.execute("INSERT INTO person VALUES(2, 'Deniz')");
st.execute("INSERT INTO person VALUES(3, 'Toprak')");
st.execute("INSERT INTO person VALUES(4, 'Pinar')");
st.close();

// ResultSet.TYPE_SCROLL_INSENSITIVE lets you move cursor around contrary to FORWARD_ONLY
// ResultSet.CONCUR_READ_ONLY is the default where we can only read data and not update it
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery("SELECT * FROM person");
rs.last();                  // Move to last row
int count = rs.getRow();    // Get row number, in this example 4
rs.beforeFirst();           // Position cursor back

st.close();
con.close();

Transaction Isolation

Transaction Isolation Levels Example with Derby

import java.sql.*;
import java.util.*;

class App {
    static Map<Integer, String> isolationLevels = new HashMap<>();
    static {
        isolationLevels.put(Connection.TRANSACTION_READ_COMMITTED, "Connection.TRANSACTION_READ_COMMITTED");
        isolationLevels.put(Connection.TRANSACTION_READ_UNCOMMITTED, "Connection.TRANSACTION_READ_UNCOMMITTED");
    }

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:derby:coffeshop;create=true";
        Connection con = DriverManager.getConnection(url);
        
        // Default for Derby is Connection.TRANSACTION_READ_COMMITTED
        System.out.println("Default: " + isolationLevels.get(con.getTransactionIsolation()));

        Statement st = con.createStatement();
        try {
            st.execute("DROP TABLE coffee");
        } catch (Exception ignored) {}

        st.execute("CREATE TABLE coffee(Type VARCHAR(255), Price INT)");
        st.executeUpdate("INSERT INTO coffee VALUES ('Blonde', 10)");

        st.close();
        con.close();

        // Example
        Connection supplier = DriverManager.getConnection(url);
        Connection consumer = DriverManager.getConnection(url);

        supplier.setAutoCommit(false);
        supplier.createStatement().executeUpdate("UPDATE coffee SET Price = 20");

        if (args.length > 0 && args[0].equals("true")) {
            // Allow consumer to read uncommitted data
            consumer.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        }
        // Application blocks here if consumers transaction isolation is TRANSACTION_READ_COMMITTED
        ResultSet rs = consumer.createStatement().executeQuery("SELECT Price FROM coffee");
        rs.next();
        
        // Prints 10 if consumers transaction isolation is TRANSACTION_READ_UNCOMMITTED
        // This is potentially dirty data since supplier may choose to rollback
        System.out.println(rs.getInt("Price")); 

        supplier.rollback();
        supplier.close();
        consumer.close();
    }
}

Notes on the Example