001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.PreparedStatement;
035: import java.sql.ResultSet;
036: import java.sql.Statement;
037: import java.text.DateFormat;
038: import java.util.Calendar;
039:
040: import junit.framework.Assert;
041:
042: /**
043: * Tests for normalisation of Time and Date values.
044: * Base on the original test submission.
045: * @author Miro Halas
046: */
047: public class TestDateTime extends TestBase {
048:
049: public TestDateTime(String s) {
050: super (s);
051: }
052:
053: protected void setUp() {
054:
055: super .setUp();
056:
057: try {
058: Connection connection = super .newConnection();
059: Statement statement = connection.createStatement();
060:
061: statement.execute("drop table time_test if exists");
062: statement.execute("drop table date_test if exists");
063: statement.execute("create table time_test(time_test time)");
064: statement.execute("create table date_test(date_test date)");
065: connection.close();
066: } catch (Exception e) {
067: }
068: }
069:
070: /**
071: * Test the database support for Date objects. Date object ignores the time
072: * portion of the Java Date.
073: *
074: * This class inserts date into database, then retrieve it back using
075: * different java time
076: *
077: * @throws Throwable - an error has occured during test
078: */
079: public void testBasicDateSupport() throws Throwable {
080:
081: final String INSERT_DATE = "insert into date_test(date_test) values (?)";
082:
083: // See OracleTests class why we need to select tablename.*
084: final String SELECT_DATE = "select date_test.* from date_test where date_test = ?";
085: final String DELETE_DATE = "delete from date_test where date_test = ?";
086: Calendar calGenerate = Calendar.getInstance();
087: java.sql.Date insertDate;
088: Connection connection = super .newConnection();
089: PreparedStatement insertStatement;
090: int iUpdateCount = 0;
091:
092: // Set date of my birthday ;-)
093: calGenerate.set(1995, 9, 15, 1, 2, 3);
094:
095: insertDate = new java.sql.Date(calGenerate.getTime().getTime());
096: insertStatement = connection.prepareStatement(INSERT_DATE);
097:
098: insertStatement.setDate(1, insertDate);
099:
100: iUpdateCount = insertStatement.executeUpdate();
101:
102: insertStatement.close();
103: Assert
104: .assertEquals(
105: "Exactly one record with date data shoud have been inserted.",
106: iUpdateCount, 1);
107:
108: // Now select it back to be sure it is there
109: PreparedStatement selectStatement = null;
110: PreparedStatement deleteStatement = null;
111: ResultSet results = null;
112: java.sql.Date retrievedDate = null;
113: boolean bHasMoreThanOne;
114: int iDeletedCount = 0;
115:
116: // Set different time, since when we are dealing with just dates it
117: // shouldn't matter
118: calGenerate.set(1995, 9, 15, 2, 3, 4);
119:
120: java.sql.Date selectDate = new java.sql.Date(calGenerate
121: .getTime().getTime());
122:
123: selectStatement = connection.prepareStatement(SELECT_DATE);
124:
125: selectStatement.setDate(1, selectDate);
126:
127: results = selectStatement.executeQuery();
128:
129: // Get the date from the database
130: Assert.assertTrue("The inserted date is not in the database.",
131: results.next());
132:
133: retrievedDate = results.getDate(1);
134: deleteStatement = connection.prepareStatement(DELETE_DATE);
135:
136: deleteStatement.setDate(1, insertDate);
137:
138: iDeletedCount = deleteStatement.executeUpdate();
139:
140: deleteStatement.close();
141: Assert
142: .assertEquals(
143: "Exactly one record with date data shoud have been deleted.",
144: iDeletedCount, 1);
145:
146: boolean result = retrievedDate.toString().startsWith(
147: insertDate.toString().substring(0, 10));
148:
149: Assert.assertTrue("The date retrieved from database "
150: + DateFormat.getDateTimeInstance()
151: .format(retrievedDate)
152: + " is not the same as the inserted one "
153: + DateFormat.getDateTimeInstance().format(insertDate),
154: result);
155: }
156:
157: /**
158: * Test the database support for Time objects. Time object ignores the date
159: * portion of the Java Date.
160: *
161: * This class inserts time into database, then retrieve it back using
162: * different java date and deletes it using cursor.
163: *
164: * Uses the already setup connection and transaction.
165: * No need to close the connection since base class is doing it for us.
166: *
167: * @throws Throwable - an error has occured during test
168: */
169: public void testBasicTimeSupport() throws Throwable {
170:
171: final String INSERT_TIME = "insert into time_test(time_test) values (?)";
172:
173: // See OracleTests class why we need to select tablename.*
174: final String SELECT_TIME = "select time_test.* from time_test where time_test = ?";
175: final String DELETE_TIME = "delete from time_test where time_test = ?";
176: Calendar calGenerate = Calendar.getInstance();
177: java.sql.Time insertTime;
178: Connection connection = super .newConnection();
179: PreparedStatement insertStatement;
180: int iUpdateCount = 0;
181:
182: // Set date of my birthday ;-)
183: calGenerate.set(1995, 9, 15, 1, 2, 3);
184:
185: insertTime = new java.sql.Time(calGenerate.getTime().getTime());
186: insertStatement = connection.prepareStatement(INSERT_TIME);
187:
188: insertStatement.setTime(1, insertTime);
189:
190: iUpdateCount = insertStatement.executeUpdate();
191:
192: insertStatement.close();
193: Assert
194: .assertEquals(
195: "Exactly one record with time data shoud have been inserted.",
196: iUpdateCount, 1);
197:
198: // Now select it back to be sure it is there
199: PreparedStatement selectStatement = null;
200: PreparedStatement deleteStatement = null;
201: ResultSet results = null;
202: java.sql.Time retrievedTime;
203: int iDeletedCount = 0;
204: java.sql.Time selectTime;
205:
206: selectStatement = connection.prepareStatement(SELECT_TIME);
207:
208: // Set different date, since when we are dealing with just time it
209: // shouldn't matter
210: // fredt - but make sure the date is in the same daylight saving range as today !
211: calGenerate.set(1975, 4, 16, 1, 2, 3);
212:
213: selectTime = new java.sql.Time(calGenerate.getTime().getTime());
214:
215: selectStatement.setTime(1, selectTime);
216:
217: results = selectStatement.executeQuery();
218:
219: // Get the date from the database
220: Assert.assertTrue("The inserted time is not in the database.",
221: results.next());
222:
223: retrievedTime = results.getTime(1);
224:
225: //
226: deleteStatement = connection.prepareStatement(DELETE_TIME);
227:
228: deleteStatement.setTime(1, insertTime);
229:
230: iDeletedCount = deleteStatement.executeUpdate();
231:
232: Assert
233: .assertEquals(
234: "Exactly one record with time data shoud have been deleted.",
235: iDeletedCount, 1);
236:
237: // And now test the date
238: Assert
239: .assertNotNull(
240: "The inserted time shouldn't be retrieved as null from the database",
241: retrievedTime);
242:
243: // Ignore milliseconds when comparing dates
244: boolean result = retrievedTime.toString().equals(
245: insertTime.toString());
246:
247: Assert.assertTrue("The time retrieved from database "
248: + DateFormat.getDateTimeInstance()
249: .format(retrievedTime)
250: + " is not the same as the inserted one "
251: + DateFormat.getDateTimeInstance().format(insertTime),
252: result);
253: }
254: }
|