package foo;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Driver;

/*
 * Table creation and insertion of single row:
 * DROP TABLE FOO;
 * CREATE TABLE FOO (
 *   ID INT(3) NOT NULL,
 *   ADate DATE NOT NULL,
 *   PRIMARY KEY  (ID)
 *  );
 *  INSERT INTO FOO(ID, ADate) VALUES(1, 0000-00-00);
 */
public class MySQLDateTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			//	register mysql driver
			DriverManager.registerDriver( new Driver() );
			//	make sure we see some output
			DriverManager.setLogWriter(new PrintWriter(System.out));
			//	connect to the database and set the "zeroDateTimeBehaviour" property to convert 0000-00-00 to null
			Connection dbConnection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/foodb?zeroDateTimeBehavior=convertToNull", "foouser", "foopwd" );
			//	create an instance of statement and execute the select query to select the single row of the database
			Statement stmt = dbConnection.createStatement();
			ResultSet resultset = stmt.executeQuery( "SELECT ID, ADate FROM FOO WHERE ID=1" );
			//	move to the first row and check if we got it 
			if( resultset.first() ) {
				//	get the date column as date (should be null)
				Date theDate = resultset.getDate("ADate");
				//	print the value of the date. Should be "null"
				System.out.println("value of date is " + theDate);
				//	print what "wasNull" tells us about the last column. Should be "true" for null-date, but returns false!
				if( theDate == null ) {
					if( resultset.wasNull() ) {
						System.out.println("OK, value is null and wasNull returns " + resultset.wasNull());
					} else {
						System.err.println("Error: value is null, but wasNull returns " + resultset.wasNull());
					}
				} else {
					System.out.println("wasNull returns " + resultset.wasNull());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}