timestamptest.java

来自「PostgreSQL7.4.6 for Linux」· Java 代码 · 共 539 行 · 第 1/2 页

package org.postgresql.test.jdbc2;

import org.postgresql.test.TestUtil;
import junit.framework.TestCase;
import java.sql.*;

/*
 * $Id: TimestampTest.java,v 1.12 2003/09/22 04:55:00 barry Exp $
 *
 * Test get/setTimestamp for both timestamp with time zone and
 * timestamp without time zone datatypes
 *
 */
public class TimestampTest extends TestCase
{

	private Connection con;

	public TimestampTest(String name)
	{
		super(name);
	}

	protected void setUp() throws Exception
	{
		con = TestUtil.openDB();
		TestUtil.createTable(con, TSWTZ_TABLE, "ts timestamp with time zone");
		TestUtil.createTable(con, TSWOTZ_TABLE, "ts timestamp without time zone");
	}

	protected void tearDown() throws Exception
	{
		TestUtil.dropTable(con, TSWTZ_TABLE);
		TestUtil.dropTable(con, TSWOTZ_TABLE);
		TestUtil.closeDB(con);
	}

	/*
	 * Tests the timestamp methods in ResultSet on timestamp with time zone
			* we insert a known string value (don't use setTimestamp) then see that 
			* we get back the same value from getTimestamp
	 */
	public void testGetTimestampWTZ()
	{
		try
		{
			Statement stmt = con.createStatement();

			//Insert the three timestamp values in raw pg format
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS1WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS2WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS3WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS4WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS1WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS2WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS3WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS4WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS1WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS2WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS3WTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + TS4WTZ_PGFORMAT + "'")));												
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate1.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate2.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate3.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate4.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime1.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime2.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime3.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime4.getTime()) + "'")));
			

			// Fall through helper
			timestampTestWTZ();

			assertEquals(20, stmt.executeUpdate("DELETE FROM " + TSWTZ_TABLE));

			stmt.close();
		}
		catch (Exception ex)
		{
			fail(ex.getMessage());
		}
	}

	/*
	 * Tests the timestamp methods in PreparedStatement on timestamp with time zone
			* we insert a value using setTimestamp then see that
			* we get back the same value from getTimestamp (which we know works as it was tested
			* independently of setTimestamp
	 */
	public void testSetTimestampWTZ()
	{
		try
		{
			Statement stmt = con.createStatement();
			PreparedStatement pstmt = con.prepareStatement(TestUtil.insertSQL(TSWTZ_TABLE, "?"));

			pstmt.setTimestamp(1, TS1WTZ);
			assertEquals(1, pstmt.executeUpdate());

			pstmt.setTimestamp(1, TS2WTZ);
			assertEquals(1, pstmt.executeUpdate());

			pstmt.setTimestamp(1, TS3WTZ);
			assertEquals(1, pstmt.executeUpdate());

			pstmt.setTimestamp(1, TS4WTZ);
			assertEquals(1, pstmt.executeUpdate());
			
			// With java.sql.Timestamp
			pstmt.setObject(1,TS1WTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS2WTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS3WTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());			
			pstmt.setObject(1,TS4WTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			
			// With Strings
			pstmt.setObject(1,TS1WTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS2WTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS3WTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());			
			pstmt.setObject(1,TS4WTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());

			// With java.sql.Date
			pstmt.setObject(1,tmpDate1, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,tmpDate2, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,tmpDate3, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());			
			pstmt.setObject(1,tmpDate4, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
		
			// With java.sql.Time	
			pstmt.setObject(1,tmpTime1, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,tmpTime2, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,tmpTime3, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());			
			pstmt.setObject(1,tmpTime4, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());															
			
			// Fall through helper
			timestampTestWTZ();

			assertEquals(20, stmt.executeUpdate("DELETE FROM " + TSWTZ_TABLE));

			pstmt.close();
			stmt.close();
		}
		catch (Exception ex)
		{
			fail(ex.getMessage());
		}
	}

	/*
	 * Tests the timestamp methods in ResultSet on timestamp without time zone
			* we insert a known string value (don't use setTimestamp) then see that 
			* we get back the same value from getTimestamp
	 */
	public void testGetTimestampWOTZ()
	{
		try
		{
			Statement stmt = con.createStatement();

			//Insert the three timestamp values in raw pg format
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS1WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS2WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS3WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS4WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS1WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS2WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS3WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS4WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS1WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS2WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS3WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + TS4WOTZ_PGFORMAT + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate1WOTZ.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate2WOTZ.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate3WOTZ.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpDate4WOTZ.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime1WOTZ.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime2WOTZ.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime3WOTZ.getTime()) + "'")));
			assertEquals(1, stmt.executeUpdate(TestUtil.insertSQL(TSWOTZ_TABLE, "'" + new java.sql.Timestamp(tmpTime4WOTZ.getTime()) + "'")));												

			// Fall through helper
			timestampTestWOTZ();

			assertEquals(20, stmt.executeUpdate("DELETE FROM " + TSWOTZ_TABLE));

			stmt.close();
		}
		catch (Exception ex)
		{
			fail(ex.getMessage());
		}
	}


	/*
	 * Tests the timestamp methods in PreparedStatement on timestamp without time zone
			* we insert a value using setTimestamp then see that
			* we get back the same value from getTimestamp (which we know works as it was tested
			* independently of setTimestamp
	 */
	public void testSetTimestampWOTZ()
	{
		try
		{
			Statement stmt = con.createStatement();
			PreparedStatement pstmt = con.prepareStatement(TestUtil.insertSQL(TSWOTZ_TABLE, "?"));

			pstmt.setTimestamp(1, TS1WOTZ);
			assertEquals(1, pstmt.executeUpdate());

			pstmt.setTimestamp(1, TS2WOTZ);
			assertEquals(1, pstmt.executeUpdate());

			pstmt.setTimestamp(1, TS3WOTZ);
			assertEquals(1, pstmt.executeUpdate());

			pstmt.setTimestamp(1, TS4WOTZ);
			assertEquals(1, pstmt.executeUpdate());


			// With java.sql.Timestamp
			pstmt.setObject(1,TS1WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS2WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS3WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());			
			pstmt.setObject(1,TS4WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			
			// With Strings
			pstmt.setObject(1,TS1WOTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS2WOTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,TS3WOTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());			
			pstmt.setObject(1,TS4WOTZ_PGFORMAT, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());

			// With java.sql.Date
			pstmt.setObject(1,tmpDate1WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,tmpDate2WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,tmpDate3WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());			
			pstmt.setObject(1,tmpDate4WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
		
			// With java.sql.Time	
			pstmt.setObject(1,tmpTime1WOTZ, java.sql.Types.TIMESTAMP);
			assertEquals(1, pstmt.executeUpdate());
			pstmt.setObject(1,tmpTime2WOTZ, java.sql.Types.TIMESTAMP);