import java.sql.*;

import java.util.*;
import java.text.*;

public class JDBCTest2 {
	
	public static void execute(Connection c,List dates,GregorianCalendar cal,boolean useCalendarInJDBC)throws SQLException{
		DateFormat df=new SimpleDateFormat();
		df.setCalendar(cal);
		df.setTimeZone(cal.getTimeZone());
		int j=0;
		PreparedStatement ps=c.prepareStatement("insert into date_test values (?)");
		for(Iterator it=dates.iterator();it.hasNext();){
			java.sql.Date d=(java.sql.Date)it.next();
			if(useCalendarInJDBC){
				ps.setDate(1,d,cal);
			}
			else{
				ps.setDate(1,d);
			}
			ps.executeUpdate();
			System.out.println("date ("+(j++)+") inserted: "+df.format(d));
		}
		ps.close();

		Statement s2=c.createStatement();
		ResultSet rs=s2.executeQuery("select * from date_test ");
		List loadedDates=new ArrayList();
		j=0;
		while(rs.next()){
			java.sql.Date d;
			if(useCalendarInJDBC){
				d=rs.getDate(1,cal);
			}
			else{
				d=rs.getDate(1);
			}
			loadedDates.add(d);
			System.out.println("date ("+(j++)+") loaded: "+df.format(d));
		}
		for(int i=0;i<dates.size();i++){
			System.out.println("**********************************");
			System.out.println("dates equal? "+(datesEqual(cal,(java.util.Date)dates.get(i),(java.util.Date)loadedDates.get(i))));
		}
		s2.close();
	}

	public static void execute()throws SQLException,ClassNotFoundException{
		Class.forName("org.postgresql.Driver");
		Connection c=DriverManager.getConnection("jdbc:postgresql://kenny:5432/locanet_jair","postgres","");
		c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		c.setAutoCommit(true);
		
		TimeZone tz= new SimpleTimeZone(-10 * 60 * 60 * 1000,"BLABLA");
		GregorianCalendar gc=new GregorianCalendar();
		gc.setTimeZone(tz);
		List dates=new ArrayList();
		dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),0,0,0,0));
		dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),23,59,59,999));
		dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),8,0,0,0));
		dates.add(setTimeInDate(gc,new java.sql.Date(System.currentTimeMillis()),19,0,0,0));
		
		Statement s=c.createStatement();
		try{
			s.execute("drop table date_test");
		}
		catch(SQLException e){
			System.err.println("SQLException while trying to drop 'date_test' table. 'date_test' table probably does not exist. Ignoring exception.");
			e.printStackTrace();
		}
		s.execute("create table date_test (d date)");
		s.close();
		execute(c,dates,gc,true);
		c.close();
	}
	
	public static final boolean datesEqual(GregorianCalendar gc,java.util.Date d1,java.util.Date d2){
		int year1,year2,month1,month2,date1,date2;
		gc.setTimeInMillis(d1.getTime());					
		year1=gc.get(GregorianCalendar.YEAR);
		month1=gc.get(GregorianCalendar.MONTH);
		date1=gc.get(GregorianCalendar.DATE);
		System.out.println("=========");
		System.out.println("hour: "+gc.get(GregorianCalendar.HOUR_OF_DAY));
		System.out.println("minute: "+gc.get(GregorianCalendar.MINUTE));
		System.out.println("second: "+gc.get(GregorianCalendar.SECOND));
		System.out.println("millisecond: "+gc.get(GregorianCalendar.MILLISECOND));

		gc.setTimeInMillis(d2.getTime());
		year2=gc.get(GregorianCalendar.YEAR);
		month2=gc.get(GregorianCalendar.MONTH);
		date2=gc.get(GregorianCalendar.DATE);
		System.out.println("=========");
		System.out.println("hour: "+gc.get(GregorianCalendar.HOUR_OF_DAY));
		System.out.println("minute: "+gc.get(GregorianCalendar.MINUTE));
		System.out.println("second: "+gc.get(GregorianCalendar.SECOND));
		System.out.println("millisecond: "+gc.get(GregorianCalendar.MILLISECOND));
		System.out.println("=========");
		System.out.println("year1: "+year1+"; month1: "+month1+"; date1: "+date1);
		System.out.println("year2: "+year2+"; month2: "+month2+"; date2: "+date2);
		return (date1==date2)&&(month1==month2)&&(year1==year2);
	}

	public static java.util.Date setTimeInDate(GregorianCalendar gc,java.util.Date d,int hourOfDay,int minute,int second,int millisecond){
		gc.setTimeInMillis(d.getTime());
		gc.set(GregorianCalendar.HOUR_OF_DAY,hourOfDay);
		gc.set(GregorianCalendar.MINUTE,minute);
		gc.set(GregorianCalendar.SECOND,second);
		gc.set(GregorianCalendar.MILLISECOND,millisecond);
		d.setTime(gc.getTimeInMillis());
		return d;
	}
	
	public static void main(String[] args)throws SQLException,ClassNotFoundException{
		JDBCTest2.execute();
	}
}