import java.sql.*;

public class JdbcTest {

    public static final String dbDriver = "org.postgresql.Driver";
    public static final String dbHost = "bueno.fim.uni-passau.de";

    // customization start
    public static final String dbName = "";
    public static final String dbUser = "";
    public static final String dbPassword = "";
    // customization end

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

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

        try {
            System.out.println("Opening Database Connection");
            conn = DriverManager.getConnection("jdbc:postgresql://" 
					       + dbHost + "/" 
					       + dbName, dbUser, dbPassword);

            // 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();
    }

}