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

public class JdbcTest {

    private static final String DB_DRIVER = "org.postgresql.Driver";
    private static final String DB_HOST = "bueno.fim.uni-passau.de";

    // customization start
    private static final String DB_NAME = "";
    private static final String DB_USER = "";
    private static final String DB_PASSWORD = "";
    // customization end

    public static void main(String[] args) {
        Connection conn = null;

        try {
            System.out.println("Loading JDBC Driver");
            Class.forName(DB_DRIVER);
        }
        catch(ClassNotFoundException e) {
            System.err.println("JDBC Driver not found");
            return;
        }

        Properties props = new Properties();
        props.setProperty("user", DB_USER);
        props.setProperty("password", DB_PASSWORD);
        props.setProperty("ssl", "true");   // necessary!

        // since jdbc driver 42.2.5 necessary to find ca chain in java keystore,
        // whereas older versions do this automatically and this setting leads
        // to an error
        props.setProperty("sslfactory",
            "org.postgresql.ssl.DefaultJavaSSLFactory");

        // insecure: omitting server cert validation, e.g., if root ca is not
        // contained in jvm cert store (OracleJDK and OpenJDK do contain
        // it automatically)
        // props.setProperty("sslfactory",
        //    "org.postgresql.ssl.NonValidatingFactory");

        try {
            System.out.println("Opening Database Connection");
            conn = DriverManager.getConnection("jdbc:postgresql://"
                + DB_HOST + "/" + DB_NAME, props);

            // do some sample work
            createTables(conn);
            insertData(conn);
            showContents(conn);
            deleteData(conn);
            showContents(conn);
            dropTables(conn);
        }
        catch(SQLException e) {
            e.printStackTrace();
        } finally {
            // close DB connection even if we have an exception
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch(SQLException e) {
                System.err.println(
                    "Exception while closing Database Connection:");
                e.printStackTrace();
            }
        }
    }

    private static void createTables(Connection conn)
            throws SQLException {
        System.out.println("Creating Table");

        Statement stmt = conn.createStatement();

        stmt.executeUpdate(
            "CREATE TABLE test ( " +
            "  id      INTEGER not null primary key, " +
            "  value VARCHAR(30) " +
            ")"
        );

        stmt.close();
    }

    private static void insertData(Connection conn)
            throws SQLException {
        System.out.println("Inserting Data");

        Statement stmt = conn.createStatement();

        stmt.executeUpdate(
            "INSERT INTO test (id, value) " +
            "VALUES (1, 'Apfel')"
        );

        stmt.close();
    }

    private static void showContents(Connection conn)
            throws SQLException {
        System.out.println("Contents:");

        Statement stmt = conn.createStatement();
        ResultSet rst = stmt.executeQuery("SELECT * FROM test");

        while(rst.next())
            System.out.println("  " + rst.getInt("id") +
                               "  " + rst.getString("value"));

        stmt.close();
    }

    private static void deleteData(Connection conn)
            throws SQLException {
        System.out.println("Deleting Data");

        Statement stmt = conn.createStatement();

        stmt.executeUpdate("DELETE FROM test");

        stmt.close();
    }

    private static void dropTables(Connection conn)
            throws SQLException {
        System.out.println("Dropping Table");

        Statement stmt = conn.createStatement();
        stmt.executeUpdate("DROP TABLE test");
        stmt.close();
    }

}