TestDateTime


1   /* Copyright (c) 2001-2005, The HSQL Development Group
2    * All rights reserved.
3    *
4    * Redistribution and use in source and binary forms, with or without
5    * modification, are permitted provided that the following conditions are met:
6    *
7    * Redistributions of source code must retain the above copyright notice, this
8    * list of conditions and the following disclaimer.
9    *
10   * Redistributions in binary form must reproduce the above copyright notice,
11   * this list of conditions and the following disclaimer in the documentation
12   * and/or other materials provided with the distribution.
13   *
14   * Neither the name of the HSQL Development Group nor the names of its
15   * contributors may be used to endorse or promote products derived from this
16   * software without specific prior written permission.
17   *
18   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21   * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22   * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23   * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24   * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25   * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28   * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29   */
30  
31  
32  package org.hsqldb.test;
33  
34  import java.sql.Connection  ;
35  import java.sql.PreparedStatement  ;
36  import java.sql.ResultSet  ;
37  import java.sql.Statement  ;
38  import java.text.DateFormat  ;
39  import java.util.Calendar  ;
40  
41  import junit.framework.Assert;
42  
43  /**
44   * Tests for normalisation of Time and Date values.
45   * Base on the original test submission.
46   * @author Miro Halas
47   */
48  public class TestDateTime extends TestBase {
49  
50      public TestDateTime(String   s) {
51          super(s);
52      }
53  
54      protected void setUp() {
55  
56          super.setUp();
57  
58          try {
59              Connection   connection = super.newConnection();
60              Statement    statement  = connection.createStatement();
61  
62              statement.execute("drop table time_test if exists");
63              statement.execute("drop table date_test if exists");
64              statement.execute("create table time_test(time_test time)");
65              statement.execute("create table date_test(date_test date)");
66              connection.close();
67          } catch (Exception   e) {}
68      }
69  
70      /**
71       * Test the database support for Date objects. Date object ignores the time
72       * portion of the Java Date.
73       *
74       * This class inserts date into database, then retrieve it back using
75       * different java time
76       *
77       * @throws Throwable - an error has occured during test
78       */
79      public void testBasicDateSupport() throws Throwable   {
80  
81          final String   INSERT_DATE =
82              "insert into date_test(date_test) values (?)";
83  
84          // See OracleTests class why we need to select tablename.*
85          final String   SELECT_DATE =
86              "select date_test.* from date_test where date_test = ?";
87          final String   DELETE_DATE =
88              "delete from date_test where date_test = ?";
89          Calendar            calGenerate = Calendar.getInstance();
90          java.sql.Date       insertDate;
91          Connection          connection = super.newConnection();
92          PreparedStatement   insertStatement;
93          int               iUpdateCount = 0;
94  
95          // Set date of my birthday ;-)
96          calGenerate.set(1995, 9, 15, 1, 2, 3);
97  
98          insertDate      = new java.sql.Date  (calGenerate.getTime().getTime());
99          insertStatement = connection.prepareStatement(INSERT_DATE);
100 
101         insertStatement.setDate(1, insertDate);
102 
103         iUpdateCount = insertStatement.executeUpdate();
104 
105         insertStatement.close();
106         Assert.assertEquals(
107             "Exactly one record with date data shoud have been inserted.",
108             iUpdateCount, 1);
109 
110         // Now select it back to be sure it is there
111         PreparedStatement   selectStatement = null;
112         PreparedStatement   deleteStatement = null;
113         ResultSet           results         = null;
114         java.sql.Date       retrievedDate   = null;
115         boolean           bHasMoreThanOne;
116         int               iDeletedCount = 0;
117 
118         // Set different time, since when we are dealing with just dates it
119         // shouldn't matter
120         calGenerate.set(1995, 9, 15, 2, 3, 4);
121 
122         java.sql.Date   selectDate =
123             new java.sql.Date  (calGenerate.getTime().getTime());
124 
125         selectStatement = connection.prepareStatement(SELECT_DATE);
126 
127         selectStatement.setDate(1, selectDate);
128 
129         results = selectStatement.executeQuery();
130 
131         // Get the date from the database
132         Assert.assertTrue("The inserted date is not in the database.",
133                           results.next());
134 
135         retrievedDate   = results.getDate(1);
136         deleteStatement = connection.prepareStatement(DELETE_DATE);
137 
138         deleteStatement.setDate(1, insertDate);
139 
140         iDeletedCount = deleteStatement.executeUpdate();
141 
142         deleteStatement.close();
143         Assert.assertEquals(
144             "Exactly one record with date data shoud have been deleted.",
145             iDeletedCount, 1);
146 
147         boolean result = retrievedDate.toString().startsWith(
148             insertDate.toString().substring(0, 10));
149 
150         Assert.assertTrue(
151             "The date retrieved from database "
152             + DateFormat.getDateTimeInstance().format(retrievedDate)
153             + " is not the same as the inserted one "
154             + DateFormat.getDateTimeInstance().format(insertDate), 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 =
172             "insert into time_test(time_test) values (?)";
173 
174         // See OracleTests class why we need to select tablename.*
175         final String   SELECT_TIME =
176             "select time_test.* from time_test where time_test = ?";
177         final String   DELETE_TIME =
178             "delete from time_test where time_test = ?";
179         Calendar            calGenerate = Calendar.getInstance();
180         java.sql.Time       insertTime;
181         Connection          connection = super.newConnection();
182         PreparedStatement   insertStatement;
183         int               iUpdateCount = 0;
184 
185         // Set date of my birthday ;-)
186         calGenerate.set(1995, 9, 15, 1, 2, 3);
187 
188         insertTime      = new java.sql.Time  (calGenerate.getTime().getTime());
189         insertStatement = connection.prepareStatement(INSERT_TIME);
190 
191         insertStatement.setTime(1, insertTime);
192 
193         iUpdateCount = insertStatement.executeUpdate();
194 
195         insertStatement.close();
196         Assert.assertEquals(
197             "Exactly one record with time data shoud have been inserted.",
198             iUpdateCount, 1);
199 
200         // Now select it back to be sure it is there
201         PreparedStatement   selectStatement = null;
202         PreparedStatement   deleteStatement = null;
203         ResultSet           results         = null;
204         java.sql.Time       retrievedTime;
205         int               iDeletedCount = 0;
206         java.sql.Time       selectTime;
207 
208         selectStatement = connection.prepareStatement(SELECT_TIME);
209 
210         // Set different date, since when we are dealing with just time it
211         // shouldn't matter
212         // fredt - but make sure the date is in the same daylight saving range as today !
213         calGenerate.set(1975, 4, 16, 1, 2, 3);
214 
215         selectTime = new java.sql.Time  (calGenerate.getTime().getTime());
216 
217         selectStatement.setTime(1, selectTime);
218 
219         results = selectStatement.executeQuery();
220 
221         // Get the date from the database
222         Assert.assertTrue("The inserted time is not in the database.",
223                           results.next());
224 
225         retrievedTime = results.getTime(1);
226 
227         //
228         deleteStatement = connection.prepareStatement(DELETE_TIME);
229 
230         deleteStatement.setTime(1, insertTime);
231 
232         iDeletedCount = deleteStatement.executeUpdate();
233 
234         Assert.assertEquals(
235             "Exactly one record with time data shoud have been deleted.",
236             iDeletedCount, 1);
237 
238         // And now test the date
239         Assert.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 =
245             retrievedTime.toString().equals(insertTime.toString());
246 
247         Assert.assertTrue(
248             "The time retrieved from database "
249             + DateFormat.getDateTimeInstance().format(retrievedTime)
250             + " is not the same as the inserted one "
251             + DateFormat.getDateTimeInstance().format(insertTime), result);
252     }
253 }
254
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags