/* Copyright (c) 2001-2010, The HSQL Development Group
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this
* list of conditions and the following disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
*
* Neither the name of the HSQL Development Group nor the names of its
* contributors may be used to endorse or promote products derived from this
* software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
* OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
package org.hsqldb.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.Calendar;
import junit.framework.Assert;
/**
* Tests for normalisation of Time and Date values.
* Base on the original test submission.
* @author Miro Halas
*/
public class TestDateTime extends TestBase {
public TestDateTime(String s) {
super(s);
}
protected void setUp() {
super.setUp();
try {
Connection connection = super.newConnection();
Statement statement = connection.createStatement();
statement.execute("drop table time_test if exists");
statement.execute("drop table date_test if exists");
statement.execute("create table time_test(time_test time)");
statement.execute("create table date_test(date_test date)");
connection.close();
} catch (Exception e) {}
}
/**
* Test the database support for Date objects. Date object ignores the time
* portion of the Java Date.
*
* This class inserts date into database, then retrieve it back using
* different java time
*
* @throws Throwable - an error has occured during test
*/
public void testBasicDateSupport() throws Throwable {
final String INSERT_DATE =
"insert into date_test(date_test) values (?)";
// See OracleTests class why we need to select tablename.*
final String SELECT_DATE =
"select date_test.* from date_test where date_test = ?";
final String DELETE_DATE =
"delete from date_test where date_test = ?";
Calendar calGenerate = Calendar.getInstance();
java.sql.Date insertDate;
Connection connection = super.newConnection();
PreparedStatement insertStatement;
int iUpdateCount = 0;
// Set date of my birthday ;-)
calGenerate.set(1995, 9, 15, 1, 2, 3);
insertDate = new java.sql.Date(calGenerate.getTimeInMillis());
insertStatement = connection.prepareStatement(INSERT_DATE);
insertStatement.setDate(1, insertDate);
iUpdateCount = insertStatement.executeUpdate();
insertStatement.close();
Assert.assertEquals(
"Exactly one record with date data shoud have been inserted.",
iUpdateCount, 1);
// Now select it back to be sure it is there
PreparedStatement selectStatement = null;
PreparedStatement deleteStatement = null;
ResultSet results = null;
java.sql.Date retrievedDate = null;
boolean bHasMoreThanOne;
int iDeletedCount = 0;
// Set different time, since when we are dealing with just dates it
// shouldn't matter
calGenerate.set(1995, 9, 15, 2, 3, 4);
java.sql.Date selectDate =
new java.sql.Date(calGenerate.getTimeInMillis());
selectStatement = connection.prepareStatement(SELECT_DATE);
selectStatement.setDate(1, selectDate);
results = selectStatement.executeQuery();
// Get the date from the database
Assert.assertTrue("The inserted date is not in the database.",
results.next());
retrievedDate = results.getDate(1);
deleteStatement = connection.prepareStatement(DELETE_DATE);
deleteStatement.setDate(1, insertDate);
iDeletedCount = deleteStatement.executeUpdate();
deleteStatement.close();
Assert.assertEquals(
"Exactly one record with date data shoud have been deleted.",
iDeletedCount, 1);
boolean result = retrievedDate.toString().startsWith(
insertDate.toString().substring(0, 10));
Assert.assertTrue(
"The date retrieved from database "
+ DateFormat.getDateTimeInstance().format(retrievedDate)
+ " is not the same as the inserted one "
+ DateFormat.getDateTimeInstance().format(insertDate), result);
}
public void testBasicDefaultTimeSupport() throws Throwable {
final String INSERT_TIME =
"insert into time_test(time_test) values (?)";
// See OracleTests class why we need to select tablename.*
final String SELECT_TIME =
"select time_test.* from time_test where time_test = ?";
final String DELETE_TIME =
"delete from time_test where time_test = ?";
java.sql.Time insertTime;
Connection connection = super.newConnection();
PreparedStatement insertStatement;
int iUpdateCount = 0;
insertTime = new java.sql.Time(3600000);
insertStatement = connection.prepareStatement(INSERT_TIME);
insertStatement.setTime(1, insertTime);
iUpdateCount = insertStatement.executeUpdate();
insertStatement.close();
Assert.assertEquals(
"Exactly one record with time data shoud have been inserted.",
iUpdateCount, 1);
// Now select it back to be sure it is there
PreparedStatement selectStatement = null;
PreparedStatement deleteStatement = null;
ResultSet results = null;
java.sql.Time retrievedTime;
int iDeletedCount = 0;
java.sql.Time selectTime;
selectStatement = connection.prepareStatement(SELECT_TIME);
selectTime = new java.sql.Time(3600000);
selectStatement.setTime(1, selectTime);
results = selectStatement.executeQuery();
// Get the date from the database
Assert.assertTrue("The inserted time is not in the database.",
results.next());
retrievedTime = results.getTime(1);
//
deleteStatement = connection.prepareStatement(DELETE_TIME);
deleteStatement.setTime(1, insertTime);
iDeletedCount = deleteStatement.executeUpdate();
Assert.assertEquals(
"Exactly one record with time data shoud have been deleted.",
iDeletedCount, 1);
// And now test the date
Assert.assertNotNull(
"The inserted time shouldn't be retrieved as null from the database",
retrievedTime);
// Ignore milliseconds when comparing dates
String selectString = selectTime.toString();
String retrievedString = retrievedTime.toString();
boolean result =
retrievedString.equals(selectString);
Assert.assertTrue(
"The time retrieved from database "
+ DateFormat.getDateTimeInstance().format(retrievedTime)
+ " is not the same as the inserted one "
+ DateFormat.getDateTimeInstance().format(insertTime), result);
}
/**
* Test the database support for Time objects. Time object ignores the date
* portion of the Java Date.
*
* This class inserts time into database, then retrieve it back using
* different java date and deletes it using cursor.
*
* Uses the already setup connection and transaction.
* No need to close the connection since base class is doing it for us.
*
* @throws Throwable - an error has occured during test
*/
public void testBasicTimeSupport() throws Throwable {
final String INSERT_TIME =
"insert into time_test(time_test) values (?)";
// See OracleTests class why we need to select tablename.*
final String SELECT_TIME =
"select time_test.* from time_test where time_test = ?";
final String DELETE_TIME =
"delete from time_test where time_test = ?";
Calendar calGenerate = Calendar.getInstance();
java.sql.Time insertTime;
Connection connection = super.newConnection();
PreparedStatement insertStatement;
int iUpdateCount = 0;
// Set date of my birthday ;-)
calGenerate.set(1995, 9, 15, 1, 2, 3);
insertTime = new java.sql.Time(calGenerate.getTime().getTime());
insertStatement = connection.prepareStatement(INSERT_TIME);
insertStatement.setTime(1, insertTime, calGenerate);
iUpdateCount = insertStatement.executeUpdate();
insertStatement.close();
Assert.assertEquals(
"Exactly one record with time data shoud have been inserted.",
iUpdateCount, 1);
// Now select it back to be sure it is there
PreparedStatement selectStatement = null;
PreparedStatement deleteStatement = null;
ResultSet results = null;
java.sql.Time retrievedTime;
int iDeletedCount = 0;
java.sql.Time selectTime;
selectStatement = connection.prepareStatement(SELECT_TIME);
// Set different date, since when we are dealing with just time it
// shouldn't matter
// fredt - but make sure the date is in the same daylight saving range as today !
calGenerate.set(1975, 9, 16, 1, 2, 3);
selectTime = new java.sql.Time(calGenerate.getTime().getTime());
selectStatement.setTime(1, selectTime, calGenerate);
results = selectStatement.executeQuery();
// Get the date from the database
Assert.assertTrue("The inserted time is not in the database.",
results.next());
retrievedTime = results.getTime(1, calGenerate);
//
deleteStatement = connection.prepareStatement(DELETE_TIME);
deleteStatement.setTime(1, insertTime, calGenerate);
iDeletedCount = deleteStatement.executeUpdate();
Assert.assertEquals(
"Exactly one record with time data shoud have been deleted.",
iDeletedCount, 1);
// And now test the date
Assert.assertNotNull(
"The inserted time shouldn't be retrieved as null from the database",
retrievedTime);
// Ignore milliseconds when comparing dates
String selectString = selectTime.toString();
String retrievedString = retrievedTime.toString();
boolean result =
retrievedString.equals(selectString);
Assert.assertTrue(
"The time retrieved from database "
+ DateFormat.getDateTimeInstance().format(retrievedTime)
+ " is not the same as the inserted one "
+ DateFormat.getDateTimeInstance().format(insertTime), result);
}
}