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");
            System.exit(1);
        }

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

}