/**
 * This is a sample application to show the incorrect implementation of
 * Date/Timestamp storing in the mysql JDBC driver.
 * 
 * This sample code is provided to demonstrate an error in the
 * MySQL Connector/J implementation.
 * 
 * This code is in the public domain.
 * Copyright Joachim Buechse, joachim@buechse.ch
 */

import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;

public class Main {

    private static String _driver= "com.mysql.jdbc.Driver";
    private static String _host= "localhost";
    private static String _db= "test";
    private static String _user= "test";
    private static String _password= "test";
    
    private static SimpleDateFormat UTC= new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ");
    static {
        UTC.setTimeZone(TimeZone.getTimeZone("GMT"));
    }
    private static SimpleDateFormat CET= new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ");
    static {
        CET.setTimeZone(TimeZone.getTimeZone("CET"));
    }

    public static void main (String[] args) throws Exception {
        parseParams(args);

        Properties props= new Properties();
        //props.setProperty();
        
        // -- build the mysql url
        StringBuffer url= new StringBuffer("jdbc:mysql://"); 
        url.append(_host);
        url.append('/');
        url.append(_db);
        url.append('?');
        
        // -- append the user and password
        if (_user != null) {
            url.append("user=").append(_user);
            if (_password != null) {
                url.append("&password=").append(_password);
            }
            url.append('&');
        }
        url.append("sessionVariables=time_zone=GMT&");
        url.append("useLegacyDatetimeCode=false&");
//        url.append("useGmtMillisForDatetimes=true&");
//        url.append("useTimezone=true&");
//        url.append("useJDBCCompliantTimezoneShift=true&");
//        url.append("pedantic=true&");
        url.append("zeroDateTimeBehavior=convertToNull&");

        url.setLength(url.length() - 1);

        Date s1= UTC.parse("2009-03-29 01:59:59+0100");
        Date e1= UTC.parse("2009-03-29 03:00:01+0200");

        Date s2= UTC.parse("2009-10-25 02:59:59+0200");
        Date e2= UTC.parse("2009-10-25 02:00:02+0100");

        // -- get a (non-pooled) connection --> sessionVariables setting works
        System.out.println("\n\n*** Using non-pooled connection\n\n");
        Driver driver= (Driver) Class.forName(_driver).newInstance();
        Connection conn= driver.connect(url.toString(), props);
        queryTZ(conn);
        storeDates(conn, s1, e1, "spring DST switch, regular JDBC connection");
        storeDates(conn, s2, e2, "autumn DST switch, regular JDBC connection");
        queryDates(conn);
        
        // -- get a pooled connection --> sessionVariables setting broken
        System.out.println("\n\n*** Using pooled connection\n\n");
        ConnPool cp= new ConnPool(url.toString(), false);
        Connection pconn= cp.getConnection();
        queryTZ(pconn);
        storeDates(pconn, s1, e1, "spring DST switch, pooled JDBC connection");
        storeDates(pconn, s2, e2, "autumn DST switch, pooled JDBC connection");
        queryDates(pconn);
    }
    
    private static void storeDates (Connection c, Date start, Date end, String comment) 
        throws SQLException
    {
        final PreparedStatement ps= c.prepareStatement(
            "INSERT INTO tztest (start, end, comment) VALUES (?,?,?)"
        );
        ps.setTimestamp(1, new Timestamp(start.getTime()));
        ps.setTimestamp(2, new Timestamp(end.getTime()));
        ps.setString(3, comment);
        ps.executeUpdate();
        ps.close();
        
        System.out.println("### millis: " + start.getTime() + " ... " + end.getTime());
        System.out.println("### UTC: " + UTC.format(start) + " ... " +  UTC.format(end));
    }

    private static void queryDates (Connection c) throws SQLException {
        Statement s= c.createStatement();
        ResultSet rs= s.executeQuery(
            "SELECT " +
            " DATE_FORMAT(start, '%d-%m-%Y %H:%i:%s'), " +
            " DATE_FORMAT(end,   '%d-%m-%Y %H:%i:%s'), " +
            " UNIX_TIMESTAMP(end)-UNIX_TIMESTAMP(start), " +
            " comment " +
            "FROM tztest"
        );
        dump(rs);
        rs.close();
    }
    
    private static void queryTZ (Connection c) throws SQLException {
        Statement s= c.createStatement();
        ResultSet rs= s.executeQuery("SELECT @@global.time_zone, @@session.time_zone");
        dump(rs);
        rs.close();
        ResultSet rs2= s.executeQuery("SHOW VARIABLES LIKE '%zone%'");
        dump(rs2);
        rs2.close();
        s.close();
    }

    // dump a result set with headers
    private static void dump (ResultSet rs) throws SQLException {
        ResultSetMetaData rsm= rs.getMetaData();
        int cols= rsm.getColumnCount();
        System.out.println("---------------------");
        for (int i= 1; i <= cols; i++) {
            System.out.print(rsm.getColumnLabel(i) + " | ");
        }
        System.out.println();
        while (rs.next()) {
            for (int i= 1; i <= cols; i++) {
                System.out.print(rs.getString(i) + " | ");
            }
            System.out.println();
        }
    }
    
    private static void parseParams (String[] args) {
        for (int i= 0; i < args.length;) {
            String name= args[i++].replaceAll("^--", "-");
            if ("-driver".equals(name)) {
                _driver= args[i++];
            } else if ("-host".equals(name) || "-hostname".equals(name)) {
                _host= args[i++];
            } else if ("-db".equals(name) || "-database".equals(name)) {
                _db= args[i++];
            } else if ("-user".equals(name) || "-username".equals(name)) {
                _user= args[i++];
            } else if ("-pass".equals(name) || "-password".equals(name)) {
                _password= args[i++];
            } else {
                throw new IllegalArgumentException(
                    "Unknown command line parameter " + name
                );
            }
        }
    }

}