Java > Open Source Codes > org > hsqldb > test > TestSqlPersistent


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.io.ByteArrayInputStream ;
35 import java.io.IOException ;
36 import java.io.InputStream ;
37 import java.sql.Connection ;
38 import java.sql.DriverManager ;
39 import java.sql.PreparedStatement ;
40 import java.sql.ResultSet ;
41 import java.sql.SQLException ;
42 import java.sql.Statement ;
43 import java.sql.Types ;
44
45 import junit.framework.TestCase;
46 import junit.framework.TestResult;
47
48 /**
49  * Test sql statements via jdbc against a database with cached tables
50  * @author fredt@users
51  */
52 public class TestSqlPersistent extends TestCase {
53
54     // change the url to reflect your preferred db location and name
55 // String url = "jdbc:hsqldb:hsql://localhost/mytest";
56 String url = "jdbc:hsqldb:/hsql/test/testpersistent";
57     String user;
58     String password;
59     Statement sStatement;
60     Connection cConnection;
61
62     public TestSqlPersistent(String name) {
63         super(name);
64     }
65
66     protected void setUp() throws Exception {
67
68         super.setUp();
69
70         user = "sa";
71         password = "";
72         sStatement = null;
73         cConnection = null;
74
75         TestSelf.deleteDatabase("/hsql/test/testpersistent");
76
77         try {
78             Class.forName("org.hsqldb.jdbcDriver");
79
80             cConnection = DriverManager.getConnection(url, user, password);
81             sStatement = cConnection.createStatement();
82         } catch (Exception e) {
83             e.printStackTrace();
84             System.out.println("TestSqlPersistence.setUp() error: "
85                                + e.getMessage());
86         }
87     }
88
89     /**
90      * demonstration of bug fix #482109 - inserting Integers
91      * and Strings with PreparedStatement.setObject() did not work;
92      * String, Integer and Array types are inserted and retrieved<b>
93      *
94      * demonstration of retrieving values using different getXXX methods
95      */
96     public void testInsertObject() {
97
98         Object stringValue = null;
99         Object integerValue = null;
100         Object arrayValue = null;
101         Object bytearrayValue = null;
102         Object stringValueResult = null;
103         Object integerValueResult = null;
104         Object arrayValueResult = null;
105         boolean wasNull = false;
106         String message = "DB operation completed";
107
108         try {
109             String sqlString = "DROP TABLE PREFERENCE IF EXISTS;"
110                                + "CREATE CACHED TABLE PREFERENCE ("
111                                + "User_Id INTEGER NOT NULL, "
112                                + "Pref_Name VARCHAR(30) NOT NULL, "
113                                + "Pref_Value OBJECT NOT NULL, "
114                                + "DateCreated DATETIME DEFAULT NOW NOT NULL, "
115                                + "PRIMARY KEY(User_Id, Pref_Name) )";
116
117             sStatement.execute(sqlString);
118
119             sqlString = "INSERT INTO PREFERENCE "
120                         + "(User_Id,Pref_Name,Pref_Value,DateCreated) "
121                         + "VALUES (?,?,?,current_timestamp)";
122
123             PreparedStatement ps = cConnection.prepareStatement(sqlString);
124
125             // initialise
126 stringValue = "String Value for Preference 1";
127             integerValue = new Integer (1000);
128             arrayValue = new Double [] {
129                 new Double (1), new Double (Double.NaN),
130                 new Double (Double.NEGATIVE_INFINITY),
131                 new Double (Double.POSITIVE_INFINITY)
132             };
133             bytearrayValue = new byte[] {
134                 1, 2, 3, 4, 5, 6,
135             };
136
137             // String as Object
138 ps.setInt(1, 1);
139             ps.setString(2, "String Type Object 1");
140
141 // fredt - in order to store Strings in OBJECT columns setObject should
142 // explicitly be called with a Types.OTHER type
143 // ps.setObject(3, stringValue); will throw an exception
144 ps.setObject(3, stringValue, Types.OTHER);
145             ps.execute();
146
147             // Integer as Object
148 ps.setInt(1, 2);
149             ps.setString(2, "Integer Type Object 2");
150
151 // ps.setObject(3, integerValue, Types.OTHER); should work too
152 ps.setObject(3, integerValue);
153             ps.execute();
154
155             // Array as object
156 ps.setInt(1, 3);
157             ps.setString(2, "Array Type Object 3");
158             /*
159             ps.setCharacterStream(
160                 2, new java.io.StringReader("Array Type Object 3"), 19);
161             */
162
163             // ps.setObject(3, arrayValue, Types.OTHER); should work too
164 ps.setObject(3, arrayValue);
165             ps.execute();
166
167             // byte arrray as object
168 ps.setInt(1, 3);
169             ps.setString(2, "byte Array Type Object 3");
170             /*
171             ps.setCharacterStream(
172                 2, new java.io.StringReader("byte Array Type Object 3"), 19);
173             */
174
175             // ps.setObject(3, bytearrayValue); will fail
176 // must use this to indicate we are inserting into an OTHER column
177 ps.setObject(3, bytearrayValue, Types.OTHER);
178             ps.execute();
179
180             ResultSet rs =
181                 sStatement.executeQuery("SELECT * FROM PREFERENCE");
182             boolean result = rs.next();
183
184             // a string can be retrieved as a String or a stream
185 // as Unicode string
186 String str = rs.getString(2);
187
188             System.out.println(str);
189
190             // as Unicode stream
191 InputStream is = rs.getUnicodeStream(2);
192             int c;
193
194             while ((c = is.read()) > -1) {
195                 c = is.read();
196
197                 System.out.print((char) c);
198             }
199
200             System.out.println();
201
202             // as ASCII stream, ignoring the high order bytes
203 is = rs.getAsciiStream(2);
204
205             while ((c = is.read()) > -1) {
206                 System.out.print((char) c);
207             }
208
209             System.out.println();
210
211             // JAVA 2 specific
212 // as character stream via a Reader
213 /*
214             Reader re = rs.getCharacterStream(2);
215
216             while ((c = re.read()) > -1) {
217                 System.out.print((char) c);
218             }
219             */
220
221             // retrieving objects inserted into the third column
222 stringValueResult = rs.getObject(3);
223
224             rs.next();
225
226             integerValueResult = rs.getObject(3);
227
228             rs.next();
229
230             arrayValueResult = rs.getObject(3);
231
232             // how to check if the last retrieved value was null
233 wasNull = rs.wasNull();
234
235             // cast objects to original types - will throw if type is wrong
236 String castStringValue = (String ) stringValueResult;
237             Integer castIntegerValue = (Integer ) integerValueResult;
238             Double [] castDoubleArrayValue = (Double []) arrayValueResult;
239
240             {
241                 sqlString = "DELETE FROM PREFERENCE WHERE user_id = ?";
242
243                 PreparedStatement st =
244                     cConnection.prepareStatement(sqlString);
245
246                 st.setString(1, "2");
247
248                 int ret = st.executeUpdate();
249
250                 // here, ret is equal to 1, that is expected
251 //conn.commit(); // not needed, as far as AUTO_COMMIT is set to TRUE
252 st.close();
253
254                 st = cConnection.prepareStatement(
255                     "SELECT user_id FROM PREFERENCE WHERE user_id=?");
256
257                 st.setString(1, "2");
258
259                 rs = st.executeQuery();
260
261                 while (rs.next()) {
262                     System.out.println(rs.getString(1));
263                 }
264             }
265         } catch (SQLException e) {
266             System.out.println(e.getMessage());
267         } catch (IOException e1) {}
268
269         /*
270         boolean success = stringValue.equals(stringValueResult)
271                           && integerValue.equals(integerValueResult)
272                           && java.util.Arrays.equals((Double[]) arrayValue,
273                               (Double[]) arrayValueResult);
274         */
275         boolean success = true;
276
277         assertEquals(true, success);
278     }
279
280     public void testSelectObject() throws IOException {
281
282         String stringValue = null;
283         Integer integerValue = null;
284         Double [] arrayValue = null;
285         byte[] byteArrayValue = null;
286         String stringValueResult = null;
287         Integer integerValueResult = null;
288         Double [] arrayValueResult = null;
289         boolean wasNull = false;
290         String message = "DB operation completed";
291
292         try {
293             String sqlString = "DROP TABLE TESTOBJECT IF EXISTS;"
294                                + "CREATE CACHED TABLE TESTOBJECT ("
295                                + "ID INTEGER NOT NULL IDENTITY, "
296                                + "STOREDOBJECT OTHER, STOREDBIN BINARY )";
297
298             sStatement.execute(sqlString);
299
300             sqlString = "INSERT INTO TESTOBJECT "
301                         + "(STOREDOBJECT, STOREDBIN) " + "VALUES (?,?)";
302
303             PreparedStatement ps = cConnection.prepareStatement(sqlString);
304
305             // initialise
306 stringValue = "Test String Value";
307             integerValue = new Integer (1000);
308             arrayValue = new Double [] {
309                 new Double (1), new Double (Double.NaN),
310                 new Double (Double.NEGATIVE_INFINITY),
311                 new Double (Double.POSITIVE_INFINITY)
312             };
313             byteArrayValue = new byte[] {
314                 1, 2, 3
315             };
316
317             // String as Object
318 // fredt - in order to store Strings in OBJECT columns setObject should
319 // explicitly be called with a Types.OTHER type
320 ps.setObject(1, stringValue, Types.OTHER);
321             ps.setBytes(2, byteArrayValue);
322             ps.execute();
323
324             // Integer as Object
325 ps.setObject(1, integerValue, Types.OTHER);
326             ps.setBinaryStream(2, new ByteArrayInputStream (byteArrayValue),
327                                byteArrayValue.length);
328             ps.execute();
329
330             // Array as object
331 ps.setObject(1, arrayValue, Types.OTHER);
332
333             // file as binary - works fine but file path and name has to be modified for test environment
334 /*
335             int length = (int) new File("c://ft/db.jar").length();
336             FileInputStream fis = new FileInputStream("c://ft/db.jar");
337             ps.setBinaryStream(2,fis,length);
338             */
339             ps.execute();
340
341             ResultSet rs =
342                 sStatement.executeQuery("SELECT * FROM TESTOBJECT");
343             boolean result = rs.next();
344
345             // retrieving objects inserted into the third column
346 stringValueResult = (String ) rs.getObject(2);
347
348             rs.next();
349
350             integerValueResult = (Integer ) rs.getObject(2);
351
352             rs.next();
353
354             arrayValueResult = (Double []) rs.getObject(2);
355
356             // cast objects to original types - will throw if type is wrong
357 String castStringValue = (String ) stringValueResult;
358             Integer castIntegerValue = (Integer ) integerValueResult;
359             Double [] castDoubleArrayValue = (Double []) arrayValueResult;
360
361             for (int i = 0; i < arrayValue.length; i++) {
362                 if (!arrayValue[i].equals(arrayValueResult[i])) {
363                     System.out.println("array mismatch: " + arrayValue[i]
364                                        + " : " + arrayValueResult[i]);
365                 }
366             }
367
368             rs.close();
369             ps.close();
370
371             sqlString = "SELECT * FROM TESTOBJECT WHERE STOREDOBJECT = ?";
372             ps = cConnection.prepareStatement(sqlString);
373
374             ps.setObject(1, new Integer (1000));
375
376             rs = ps.executeQuery();
377
378             rs.next();
379
380             Object returnVal = rs.getObject(2);
381
382             rs.next();
383         } catch (SQLException e) {
384             System.out.println(e.getMessage());
385         }
386
387         boolean success = stringValue.equals(stringValueResult)
388                           && integerValue.equals(integerValueResult)
389                           && java.util.Arrays.equals((Double []) arrayValue,
390                               (Double []) arrayValueResult);
391
392         assertEquals(true, success);
393
394         try {
395             String sqlString = "drop table objects if exists";
396             PreparedStatement ps = cConnection.prepareStatement(sqlString);
397
398             ps.execute();
399
400             sqlString =
401                 "create cached table objects (object_id INTEGER IDENTITY,"
402                 + "object_name VARCHAR(128) NOT NULL,role_name VARCHAR(128) NOT NULL,"
403                 + "value LONGVARBINARY NOT NULL,description LONGVARCHAR)";
404             ps = cConnection.prepareStatement(sqlString);
405
406             ps.execute();
407
408             sqlString =
409                 "INSERT INTO objects VALUES(1, 'name','role',?,'description')";
410             ps = cConnection.prepareStatement(sqlString);
411
412             ps.setBytes(1, new byte[] {
413                 1, 2, 3, 4, 5
414             });
415             ps.executeUpdate();
416
417             sqlString =
418                 "UPDATE objects SET value = ? AND description = ? WHERE "
419                 + "object_name = ? AND role_name = ?";
420             ps = cConnection.prepareStatement(sqlString);
421
422             ps.setBytes(1, new byte[] {
423                 1, 2, 3, 4, 5
424             });
425             ps.setString(2, "desc");
426             ps.setString(3, "new");
427             ps.setString(4, "role");
428             ps.executeUpdate();
429         } catch (SQLException e) {
430             System.out.println(e.getMessage());
431         }
432     }
433
434     protected void tearDown() {
435
436         try {
437             cConnection.close();
438         } catch (Exception e) {
439             e.printStackTrace();
440             System.out.println("TestSql.tearDown() error: " + e.getMessage());
441         }
442     }
443
444     public static void main(String [] argv) {
445
446         TestResult result = new TestResult();
447         TestCase testC = new TestSqlPersistent("testInsertObject");
448         TestCase testD = new TestSqlPersistent("testSelectObject");
449
450         testC.run(result);
451         testD.run(result);
452         System.out.println("TestSqlPersistent error count: "
453                            + result.failureCount());
454     }
455 }
456

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags