PostgreSQL notes

Installation

On Linux Mint, I used the Software Manager which gave me version 10 though the latest is 11.1.

Basic set-up

Start by verifying that I've got a basic shell capability and find out what version of PostgreSQL I've got installed. I know that the latest PostgreSQL version today is 11.1 because that's what I get when rolling a container instance of Docker Hub's postgres image.

russ@moria:~$ which psql
/usr/bin/psql
russ@moria:~$ psql --version
psql (PostgreSQL) 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

As always, Ubuntu software is way behind (at this time, 11.1 is the latest version).

Okay, I'm in. Note that it's unwise and not best practice to use or modify user postgres on your host because:

1. It's normally locked from the OS so no one can log into the OS as postgres. You must reach postgres from root.
2. It's normally not password-protected and it delegates to the host.
3. Keeping postgres disabled removes the risk of a brute-force attack through a named super-user.

This said, I have found no one on stackoverflow to step up to avow that this is so. I've got work to do, especially on localhost, so I'm going to violate this seemingly unwritten rule.

Setting a password for user postgres

Start pgAdmin III...

I find it, launch it, then put it into the background so I can have my console back.

russ@moria:~$ which pgadmin3
/usr/bin/pgadmin3
russ@moria:~$ pgadmin3
^Z
[1]+  Stopped                 pgadmin3
russ@moria:~$ bg
[1]+ pgadmin3 &
russ@moria:~$

It's a nice utility that's going to take time to figure out. I'm not going to waste my time now since I know that IntelliJ IDEA's database editor is super good (better, even, than SQuirreL).

Practical PostgreSQL

User postgres

At least on Debian Linux, installing PostgreSQL results in its superuser being user postgres:

$ cat /etc/passwd
...
postgres:x:124:133:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

You can bring up psql simply thus:

$ psql postgres
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

postgres=#

To change or add a password to user postgres, do this:

$ psql postgres
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# ALTER USER postgres PASSWORD 'new password';
ALTER ROLE

More practical PostgreSQL

As compared to MySQL (MariahDB), there is much less information available to search through.

The term, database, in PostgreSQL is not exactly the same thing in MySQL. (Or, I would add, MongoDB.)

Creating a database with schema (table), etc.

To do this in a .sql script, create the following file:

acme-database.sql:

CREATE DATABASE acme;
\c acme  --switch to our new database to do the rest...
CREATE SEQUENCE IF NOT EXISTS event_sequence;
CREATE TABLE IF NOT EXISTS event
(
  sequencenum BIGINT      NOT NULL DEFAULT NEXTVAL( 'event_sequence' ),
  name        TEXT        NOT NULL,
  objecttype  TEXT        NOT NULL,
  objectid    UUID        NOT NULL,
  version     UUID        NOT NULL,
  revision    INT         NOT NULL,
  data        JSONB       NOT NULL,
  meta        JSONB       NOT NULL,
  logdate     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY ( sequencenum ),
  UNIQUE ( objectid, version )
);
CREATE TABLE IF NOT EXISTS snapshot
(
  sequencenum BIGINT      NOT NULL,
  revision    INT         NOT NULL,
  data        JSONB       NOT NULL,
  meta        JSONB       NOT NULL,
  logdate     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY ( sequencenum ),
  FOREIGN KEY ( sequencenum ) REFERENCES event ( sequencenum )
);

When you get a connection to this RDBMS, you always get a connection to a single database. I think that, by default, it's to the eponymous database if you fail to specify one. For example, in the connection made below, we connect first, by default, to postgres, but then get out and reconnect to acme:

$ psql --host=127.0.0.1 --port=5432 --username=postgres
psql (11.1 (Debian 11.1-3.pgdg90+1))
Type "help" for help.

postgres=# \list
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 acme      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres=# \q
root@a2ad580676ad:/# psql --host=127.0.0.1 --port=5432 --username=postgres --dbname=acme
psql (11.1 (Debian 11.1-3.pgdg90+1))
Type "help" for help.

acme=# \conninfo
You are connected to database "acme" as user "postgres" on host "127.0.0.1" at port "5432".

acme=# \list
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 acme      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

acme=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | event    | table | postgres
 public | snapshot | table | postgres
(2 rows)

acme=# \d+ event
                                                          Table "public.event"
   Column    |           Type           | Collation | Nullable |            Default                  | Storage  | Stats target | Description
-------------+--------------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
 sequencenum | bigint                   |           | not null | nextval('event_sequence'::regclass) | plain    |              |
 name        | text                     |           | not null |                                     | extended |              |
 objecttype  | text                     |           | not null |                                     | extended |              |
 objectid    | uuid                     |           | not null |                                     | plain    |              |
 version     | uuid                     |           | not null |                                     | plain    |              |
 revision    | integer                  |           | not null |                                     | plain    |              |
 data        | jsonb                    |           | not null |                                     | extended |              |
 meta        | jsonb                    |           | not null |                                     | extended |              |
 logdate     | timestamp with time zone |           | not null | now()                               | plain    |              |
Indexes:
    "event_pkey" PRIMARY KEY, btree (sequencenum)
    "event_objectid_version_key" UNIQUE CONSTRAINT, btree (objectid, version)
Referenced by:
    TABLE "snapshot" CONSTRAINT "snapshot_sequencenum_fkey" FOREIGN KEY (sequencenum) REFERENCES event(sequencenum)

(This is the canonical SQL way, works for most RDBMS:)
acme=# SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'event';
 column_name |        data_type
-------------+--------------------------
 sequencenum | bigint
 name        | text
 objecttype  | text
 objectid    | uuid
 version     | uuid
 revision    | integer
 data        | jsonb
 meta        | jsonb
 logdate     | timestamp with time zone
(9 rows)

acme=# \d snapshot
                         Table "public.snapshot"
   Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 sequencenum | bigint                   |           | not null |
 revision    | integer                  |           | not null |
 data        | jsonb                    |           | not null |
 meta        | jsonb                    |           | not null |
 logdate     | timestamp with time zone |           | not null | now()
Indexes:
    "snapshot_pkey" PRIMARY KEY, btree (sequencenum)
Foreign-key constraints:
    "snapshot_sequencenum_fkey" FOREIGN KEY (sequencenum) REFERENCES event(sequencenum)

acme=# SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'snapshot';
 column_name |        data_type
-------------+--------------------------
 sequencenum | bigint
 revision    | integer
 data        | jsonb
 meta        | jsonb
 logdate     | timestamp with time zone
(5 rows)

acme=# \ds
            List of relations
 Schema |   Name         |   Type   |  Owner
--------+----------------+----------+----------
 public | event_sequence | sequence | postgres
(1 row)

acme=# SELECT * FROM information_schema.sequences;
 sequence_catalog | sequence_schema | sequence_name  | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_opt
ion
------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+----------
----
 acme             | public          | event_sequence | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
(1 row)

Auto-incrementing fields

Note that, while many (MySQL, for instance) databases let you code something like:

CREATE TABLE event
(
  sequencenum BIGINT AUTO_INCREMENT PRIMARY KEY,
  .
  .
  .
);

PostgreSQL requires the definition of such a field (for use as a column) as a separate thing,

CREATE SEQUENCE IF NOT EXISTS event_sequence;

CREATE TABLE IF NOT EXISTS event
(
  sequencenum BIGINT NOT NULL DEFAULT NEXTVAL( 'event_sequence' ),
  .
  .
  .
);

In PostgreSQL, SEQUENCE is equivalent to AUTO_INCREMENT. This is something I discovered years ago at HP when moving from MySQL to PostgreSQL (but, then we moved to MongoDB).

Summary

Executing the contents of an SQL script file

To execute a .sql file from the command line, first set PGPASSWORD or create a file, .pgpass to store the password in this format:

# hostname:port:database-name:username:password
localhost:5432:acme:postgres:******

Use chmod 0600 .pgpass to set permissions. Permissions must be at least as strict as this or they will be ignored.

By either of the methods above, issuing commands from a .sql script will be accepted by PostgreSQL. Here's how a .sql script is fired at PostgreSQL:

$ psl --host=localhost --dbname=acme --username=postgres --password=password --file=file.sql

Docker and PostgreSQL

This is with my Dockerfile thus:

FROM artifactory.acme.net/postgres

# This SQL script is run as user postgres as soon as PostgreSQL is up:
COPY acme-database.sql /docker-entrypoint-initdb.d/
COPY postgresql.conf /etc/postgresql/11.1/

I'm going to display on the left-hand side what's what presently. Then, I'll remove what I've stricken out here and try again to see if anything I set in this file was obeyed; these are marked above on the right-hand side of whatever is compared.

Here's what I see in the container. I think this is where the author(s) of the postgres container put the data, probably using their own postgresql.conf file which I found on the path /var/lib/postgresql/data/postgresql.conf.

root@46928e5b058c:/var/lib/postgresql/data# ll
total 128
drwx------ 19 postgres  4096 Feb 12 18:25 .
drwxr-xr-x  1 postgres  4096 Jan 30 21:25 ..
drwx------  6 postgres  4096 Feb 12 18:25 base
drwx------  2 postgres  4096 Feb 12 18:25 global
drwx------  2 postgres  4096 Feb 12 18:25 pg_commit_ts
drwx------  2 postgres  4096 Feb 12 18:25 pg_dynshmem
...etc.
-rw-------  1 postgres    88 Feb 12 18:25 postgresql.auto.conf
-rw-------  1 postgres 23750 Feb 12 18:25 postgresql.conf
-rw-------  1 postgres    36 Feb 12 18:25 postmaster.opts
-rw-------  1 postgres    94 Feb 12 18:25 postmaster.pid

(Everything is identical, I think.)

Here are the articles of configuration I'm seeing (only the ones that are set):

root@46928e5b058c:/var/lib/postgresql/data# cat postgresql.conf | more
#---------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------
max_connections = 100
listen_addresses = '*'

#---------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------
shared_buffers = 128MB
dynamic_shared_memory_type = posix

#---------------------------------
# WRITE-AHEAD LOG
#---------------------------------

max_wal_size = 1GB
min_wal_size = 80MB

#---------------------------------
# REPORTING AND LOGGING
#---------------------------------
log_timezone = 'UTC'

#---------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'

(Everything is identical, I think.)

And here are the environment variables defined in the container:

root@46928e5b058c:# env
LANG=en_US.utf8
HOSTNAME=46928e5b058c
PG_MAJOR=11
PWD=
HOME=/root
PG_VERSION=11.1-3.pgdg90+1
GOSU_VERSION=1.11
PGDATA=/var/lib/postgresql/data
TERM=xterm
POSTGRES_PASSWORD=postgres
POSTGRES_USER=postgres
SHLVL=1
PATH=/usr/local/sbin:/usr/local/bin:/us...
_=/usr/bin/env
OLDPWD=/

(from docker-compose.yaml)
(from docker-compose.yaml)

root@a86264faedb4:/# env
LANG=en_US.utf8
HOSTNAME=a86264faedb4
PG_MAJOR=11
PWD=/
HOME=/root
PG_VERSION=11.1-3.pgdg90+1
GOSU_VERSION=1.11
PGDATA=/var/lib/postgresql/data
TERM=xterm
POSTGRES_PASSWORD=postgres
POSTGRES_USER=postgres
SHLVL=1
PATH=/usr/local/sbin:/usr/local/bin:/us...
_=/usr/bin/env

So, it appears that nothing I've planned for postgresql.conf, my own version, is going to be used, but I don't really need it since the author(s) of the container image have it all together.

The canonical place of PGDATA in the standard distribution is /var/lib/postgresql/PG_VERSION/main. Our container author(s) have put this elsewhere, which is fine. So I'm going to do this in docker-compose.yaml:

version: '3.5'
services:
  .
  .
  .
  postgres:
    image: "artifactory.acme.net/postgres"
    restart: always
    networks:
      acme:
    volumes:                ("volume-name:path")
      - "postgres-data:/var/lib/postgresql/data"
    environment:
      POSTGRES_DB:       acme
      POSTGRES_USER:     postgres
      POSTGRES_PASSWORD: postgres
    deploy:
      mode: global
      #placement:
      #  contraints:
      #    # Only deploy on Docker nodes labeled with acme.mgmt=true, to wit:
      #    # $ docker node update --label-add acme.mgmt=true 
      #    - node.labels.acme.mgmt == true
.
.
.
volumes:
  acme-sst:
    external: true
    name: acme-sst-data
  postgres-data:            (volume-name/what volume is called in this file)
    external: true          (means the volume is created outside of Compose)
    name: acme-postgres-data (what volume name is called in host filesystem)
  logs:
    name: acme-log-data
  elastic-search-data:
    name: acme-elk-data

This appears to work. I see the volume stuff on the path /var/lib/docker/volumes/acme-postgres-data/_data:

root@moria:# ll /var/lib/docker/volumes/acme-postgres-data/_data
total 128
drwx------ 19  999  999  4096 Feb 12 14:45 ./
drwxr-xr-x  3 root root  4096 Feb 12 14:45 ../
drwx------  6  999  999  4096 Feb 12 14:45 base/
drwx------  2  999  999  4096 Feb 12 14:46 global/
drwx------  2  999  999  4096 Feb 12 14:45 pg_commit_ts/
drwx------  2  999  999  4096 Feb 12 14:45 pg_dynshmem/
-rw-------  1  999  999  4535 Feb 12 14:45 pg_hba.conf
-rw-------  1  999  999  1636 Feb 12 14:45 pg_ident.conf
drwx------  4  999  999  4096 Feb 12 14:50 pg_logical/
drwx------  4  999  999  4096 Feb 12 14:45 pg_multixact/
drwx------  2  999  999  4096 Feb 12 14:45 pg_notify/
drwx------  2  999  999  4096 Feb 12 14:45 pg_replslot/
drwx------  2  999  999  4096 Feb 12 14:45 pg_serial/
drwx------  2  999  999  4096 Feb 12 14:45 pg_snapshots/
drwx------  2  999  999  4096 Feb 12 14:45 pg_stat/
drwx------  2  999  999  4096 Feb 12 14:52 pg_stat_tmp/
drwx------  2  999  999  4096 Feb 12 14:45 pg_subtrans/
drwx------  2  999  999  4096 Feb 12 14:45 pg_tblspc/
drwx------  2  999  999  4096 Feb 12 14:45 pg_twophase/
-rw-------  1  999  999     3 Feb 12 14:45 PG_VERSION
drwx------  3  999  999  4096 Feb 12 14:45 pg_wal/
drwx------  2  999  999  4096 Feb 12 14:45 pg_xact/
-rw-------  1  999  999    88 Feb 12 14:45 postgresql.auto.conf
-rw-------  1  999  999 23750 Feb 12 14:45 postgresql.conf
-rw-------  1  999  999    36 Feb 12 14:45 postmaster.opts
-rw-------  1  999  999    94 Feb 12 14:45 postmaster.pid

I don't know yet what PostgreSQL data files look like, but digging down under base and global, I'm impressed that this is right. Here's what I'm seeing in the container, which is pretty much what I see in moria's filesystem (noted above):

root@9ba1ab4cb82b:/# ll /var/lib/postgresql/data/
total 128
drwx------ 19 postgres  4096 Feb 12 23:06 .
drwxr-xr-x  1 postgres  4096 Jan 30 21:25 ..
drwx------  6 postgres  4096 Feb 12 21:45 base
drwx------  2 postgres  4096 Feb 12 23:07 global
drwx------  2 postgres  4096 Feb 12 21:45 pg_commit_ts
drwx------  2 postgres  4096 Feb 12 21:45 pg_dynshmem
-rw-------  1 postgres  4535 Feb 12 21:45 pg_hba.conf
-rw-------  1 postgres  1636 Feb 12 21:45 pg_ident.conf
drwx------  4 postgres  4096 Feb 12 23:06 pg_logical
drwx------  4 postgres  4096 Feb 12 21:45 pg_multixact
drwx------  2 postgres  4096 Feb 12 23:06 pg_notify
drwx------  2 postgres  4096 Feb 12 21:45 pg_replslot
drwx------  2 postgres  4096 Feb 12 21:45 pg_serial
drwx------  2 postgres  4096 Feb 12 21:45 pg_snapshots
drwx------  2 postgres  4096 Feb 12 23:06 pg_stat
drwx------  2 postgres  4096 Feb 12 23:07 pg_stat_tmp
drwx------  2 postgres  4096 Feb 12 21:45 pg_subtrans
drwx------  2 postgres  4096 Feb 12 21:45 pg_tblspc
drwx------  2 postgres  4096 Feb 12 21:45 pg_twophase
-rw-------  1 postgres     3 Feb 12 21:45 PG_VERSION
drwx------  3 postgres  4096 Feb 12 21:45 pg_wal
drwx------  2 postgres  4096 Feb 12 21:45 pg_xact
-rw-------  1 postgres    88 Feb 12 21:45 postgresql.auto.conf
-rw-------  1 postgres 23750 Feb 12 21:45 postgresql.conf
-rw-------  1 postgres    36 Feb 12 23:06 postmaster.opts
-rw-------  1 postgres    94 Feb 12 23:06 postmaster.pid
root@9ba1ab4cb82b:/# ll /var/lib/postgresql/data/base/
total 56
drwx------  6 postgres  4096 Feb 12 21:45 .
drwx------ 19 postgres  4096 Feb 12 23:06 ..
drwx------  2 postgres 12288 Feb 12 21:45 1
drwx------  2 postgres 12288 Feb 12 21:45 13066
drwx------  2 postgres 12288 Feb 12 23:07 13067
drwx------  2 postgres 12288 Feb 12 23:06 16384

PostgreSQL JDBC

The PostgreSQL™ Java database connection (JDBC) driver is not thread-safe. The PostgreSQL server itself is not multithreaded. Each connection creates a new process on the server; as such the serialization of any concurrent requests to the process are the responsibility of the application.

The PostgreSQL JDBC driver supports java.util.logging, for resolving issues with the driver, in the consuming application. Pass to JVM, -Djava.util.logging.config.file=logging.properties.

logging.properties:

handlers= java.util.logging.FileHandler

# Default global logging level, { OFF, FINE, FINEST }.
.level= OFF

# default file output is in user's home directory.
java.util.logging.FileHandler.pattern = %h/pgjdbc%u.log
java.util.logging.FileHandler.limit = 5000000
java.util.logging.FileHandler.count = 20
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
java.util.logging.FileHandler.level = FINEST

java.util.logging.SimpleFormatter.format=%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS %4$s %2$s %5$s%6$s%n

# Facility specific properties { OFF, FINE, FINEST }.
org.postgresql.level=FINEST

Maven dependency

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1200-jdbc41</version>
</dependency>

Links

Issuing a query

Statement statement = connection.createStatement();
ResultSet result    = statement.executeQuery( "SELECT * FROM mytable WHERE columnfoo = 500" );
while( result.next() )
{
  System.out.print  ( "Column 1 returned " );
  System.out.println( result.getString( 1 ) );
}

result.close();
statement.close();

Issuing a query using a PreparedStatement

PreparedStatement statement = connection.prepareStatement( "SELECT * FROM mytable WHERE columnfoo = ?" );
statement.setInt( 1, 500 );
ResultSet result = statement.executeQuery();

while( result.next() )
{
    System.out.print( "Column 1 returned " );
    System.out.println( result.getString( 1 ) );
}

result.close();
statement.close();

Notes on PostgreSQL and data-typing:

PostgreSQL is strongly typed. Every value has a defined type and every function and operator is defined to work with one or more particular types, but not all.

In practice, you need to make both sides of an equal sign be the same type in a query, or you might get some exception/error saying something like:

org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
	  Position: 36

This appears to happen most frequently with types such as uuid and timestamptz and the remedy is often to ensure that the value passed into the query (SELECT...WITH value = ?) is the object and not some string that looks like the object.

Using a cursor...

connection.setAutoCommit( false );
Statement statement = connection.createStatement();

// turn cursor on...
statement.setFetchSize( 50 );
ResultSet result = st.executeQuery( "SELECT * FROM mytable" );

while( result.next() )
  System.out.print( "a row was returned." );

result.close();

// turn cursor off...
statement.setFetchSize( 0 );
result = statement.executeQuery( "SELECT * FROM mytable" );

while( rs.next() )
  System.out.print( "many rows were returned." );

result.close();
statement.close();

Delete

PreparedStatement statement = connection.prepareStatement( "DELETE FROM mytable WHERE columnfoo = ?" );
statement.setInt( 1, 500 );
int deleted = statement.executeUpdate();
System.out.println( deleted + " rows deleted" );
statement.close();

Binary data

CREATE TABLE images ( name TEXT, image BYTEA );

Inserting an image:

File              file      = new File( "myimage.gif" );
FileInputStream   stream    = new FileInputStream( file );
PreparedStatement statement = conn.prepareStatement( "INSERT INTO images VALUES (?, ?)" );

statement.setString( 1, file.getName() );
statement.setBinaryStream( 2, stream, ( int ) file.length() );
statement.executeUpdate();
statement.close();
stream.close();

Retrieving an image:

PreparedStatement statement = conn.prepareStatement( "SELECT image FROM images WHERE name = ?" );
statement.setString( 1, "myimage.gif" );
ResultSet result = ps.executeQuery();

while (result.next() )
{
    byte[] imageBytes = result.getBytes( 1 ); // (or create an InputStream)
    ...
}

result.close();
statement.close();

Java 8 (Joda) time and PostgreSQL time

How the two date/time systems correlate:

Reading Java 8 Date and Time using JDBC:

Statement statement = connection.createStatement();
ResultSet result    = st.executeQuery( "SELECT * FROM mytable WHERE columnfoo = 500" );

while( result.next() )
{
  LocalDate localDate = result.getObject( 1, LocalDate.class ) );

  System.out.print( "Column 1 returned " );
  System.out.println( localDate );
}

result.close();
statement.close();

Writing Java 8 Date and Time using JDBC:

LocalDate         localDate = LocalDate.now();
PreparedStatement statement = connection.prepareStatement( "INSERT INTO mytable (columnfoo) VALUES (?)" );

statement.setObject( 1, localDate );
statement.executeUpdate();
statement.close();

Miscellaneous statements

Statement statement = connection.createStatement();
statement.execute( "DROP TABLE mytable" );
statement.close();