Demo of the new Java 8 Time API vs Oracle JDBC driver for the Database Laboratory course

LocalDateTestJDBC.java

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;

public class LocalDateTestJDBC {
	protected static final String driverName = "oracle.jdbc.driver.OracleDriver";
	protected static final String databaseThinUrl = "jdbc:oracle:thin:@rapid.eik.bme.hu:1521:szglab";
	protected static final String databaseOCIUrl = "jdbc:oracle:oci:@rapid.eik.bme.hu:1521:szglab";
	protected static String databaseUrl=databaseThinUrl;


	// Connection object
	protected static Connection connection = null;

	/**
	 * Application entry point.
	 *
	 * Args:
	 * 1. db username
	 * 2. db password
	 */
	public static void main(String[] args) throws SQLException {
		String db_username=args[0];
		String db_password=args[1];

		if (connect(db_username, db_password)) {
			System.out.println("Connected.");

			System.out.println("\nExecuting DDLs\n===============");
			// given a Connection in connection, that is open and working
			// we first execute some DDLs to prepare for the demo
			String[] DDLs = {
			  "drop table localdatetest purge"
			, "drop sequence localdatetest_seq"
			, "create sequence localdatetest_seq increment by 1 start with 100 nocycle"
			, "create table localdatetest (id number default localdatetest_seq.nextval, column_date date)"
			};
			for (String ddl: DDLs) {
				System.out.println("Executing: "+ddl);
				try (Statement s2 = connection.createStatement()) {
					s2.executeUpdate(ddl);
				} catch (SQLException e) {
					System.err.println(e.toString());
				}
			}

			// we ask for the ID to be returned after the insert
			// and also for the value of the column_date field
			PreparedStatement PSinsertDate=connection.prepareStatement("insert into localdatetest (column_date) values (?)"
                                                                                  , new String[] {"ID", "COLUMN_DATE"});

			System.out.println("\nTry to utilize java.time.LocalDate directly through JDBC\n===============");
			try {
				System.out.println("inserting LocalDate 2017-01-22 to column_date using PreparedStatement.setObject...");
				PSinsertDate.setObject(1, LocalDate.parse("2017-01-22"));
				PSinsertDate.executeUpdate();

				try (ResultSet rs = PSinsertDate.getGeneratedKeys()) {
					while(rs.next()) {
						System.out.println("Row inserted with id="+rs.getInt(1));
					}
				}
			} catch (SQLException e) {
				System.err.println(e.toString());
				System.err.println("Unfortunately, 12.1 Oracle JDBC driver does not support setting and getting java.time.LocalDate instances. If you are using 12.1 JDBC driver, this might be the case.");
			}

			System.out.println("\nTry to utilize java.time.LocalDate and LocalDateTime after conversion to and from java.sql.Date and Timestamp\n===============");
			try {
				System.out.println("inserting LocalDate 2017-01-22 and LocalDateTime 2017-01-22 15:52:14 to column_date using PreparedStatement.setDate and setTimeStamp respectively. Remember that the Oracle date datatype contains date and time information to seconds precision without timezone information...");
				// LocalDate holds a date without time component and without timezone information
				LocalDate d = LocalDate.parse("2017-01-22");
				// we convert LocatDate to java.sql.Date using java.sql.Date.valueOf(LocalDate)
				// and set using PerapedStatement.setDate(java.sql.Date)
				PSinsertDate.setDate(1, Date.valueOf(d));
				// store this record for future execution
				PSinsertDate.executeUpdate();

				try (ResultSet rs = PSinsertDate.getGeneratedKeys()) {
					while(rs.next()) {
						// unfortunately, getDate is not supported on the generated keys resultset, thus we use getString
						System.out.println("Row inserted with id="+rs.getInt(1)
						                 + " column_date(getString)="+rs.getString(2)
						                  );
					}
				}

				// LocalDateTime holds a date with time component but without timezone information
				LocalDateTime dt = LocalDateTime.parse("2017-01-22T15:52:14");
				// we convert LocatDateTime to java.sql.TimeStamp using java.sql.TimeStamp.valueOf(LocalDateTime)
				// and set using PerapedStatement.setTimeStamp(java.sql.TimeStamp)
				PSinsertDate.setTimestamp(1, Timestamp.valueOf(dt));
				// store this record for future execution
				PSinsertDate.executeUpdate();

				try (ResultSet rs = PSinsertDate.getGeneratedKeys()) {
					while(rs.next()) {
						// unfortunately, getDate is not supported on the generated keys resultset, thus we use getString
						System.out.println("Row inserted with id="+rs.getInt(1)
						                 + " column_date(getString)="+rs.getString(2)
						                  );
					}
				}

				System.out.println("\nQuery rows from out demo table\n===============");
				try (
					Statement s = connection.createStatement();
					ResultSet rs = s.executeQuery("select id, column_date from localdatetest")
				) {
					while(rs.next()) {
						// fetch rows, convert values to java.time.LocalDate and java.time.LocalDateTime and then display
						System.out.println("Retrieved row:"
						                  +" id="+rs.getInt(1)
						                  +" column_date(getString)="+rs.getString(2)
						                  +" column_date(getDate)="+rs.getDate(2).toLocalDate()
						                  +" column_date(getTimestamp)="+rs.getTimestamp(2).toLocalDateTime()
								  );
					}
				} catch (SQLException e) {
					System.err.println(e.toString());
				}
			} catch (SQLException e) {
				System.err.println(e.toString());
				e.printStackTrace();
			}

			connection.close();
			System.out.println("\nConnection closed.");
		} else {
			System.err.println("Database connection failed.");
		}
	}

	/**
	 * Tries to connect to the database
	 *
	 * @param userName
	 *            User who has access to the database
	 * @param password
	 *            User's password
	 * @return True on success, false on fail
	 */
	public static boolean connect(String userName, String password) {

		try {

			// If connection status is disconnected
			if (connection == null || !connection.isValid(30)) {

				if (connection == null) {
					// Load the specified database driver
					Class.forName(driverName);
				} else {
					connection.close();
				}

				// Create new connection
				connection = DriverManager.getConnection(databaseUrl, userName, password);
			}

			return true;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}
}

Compile and run using

javac -cp ojdbc7.jar LocalDateTestJDBC.java
java -cp ojdbc7.jar:. LocalDateTestJDBC username password

Using Oracle 12cR1 12.1 JDBC driver against 12cR1 server, it outputs the following:

Connected.

Executing DDLs
===============
Executing: drop table localdatetest purge
Executing: drop sequence localdatetest_seq
Executing: create sequence localdatetest_seq increment by 1 start with 100 nocycle
Executing: create table localdatetest (id number default localdatetest_seq.nextval, column_date date)

Try to utilize java.time.LocalDate directly through JDBC
===============
inserting LocalDate 2017-01-22 to column_date using PreparedStatement.setObject...
java.sql.SQLException: Invalid column type
Unfortunately, 12.1 Oracle JDBC driver does not support setting and getting java.time.LocalDate instances. If you are using 12.1 JDBC driver, this might be the case.

Try to utilize java.time.LocalDate and LocalDateTime after conversion to and from java.sql.Date and Timestamp
===============
inserting LocalDate 2017-01-22 and LocalDateTime 2017-01-22 15:52:14 to column_date using PreparedStatement.setDate and setTimeStamp respectively. Remember that the Oracle date datatype contains date and time information to seconds precision without timezone information...
Row inserted with id=100 column_date(getString)=2017-01-22 00:00:00
Row inserted with id=101 column_date(getString)=2017-01-22 15:52:14

Query rows from out demo table
===============
Retrieved row: id=100 column_date(getString)=2017-01-22 00:00:00.0 column_date(getDate)=2017-01-22 column_date(getTimestamp)=2017-01-22T00:00
Retrieved row: id=101 column_date(getString)=2017-01-22 15:52:14.0 column_date(getDate)=2017-01-22 column_date(getTimestamp)=2017-01-22T15:52:14

Connection closed.