KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > objectweb > cjdbc > scenario > raidb1 > driver > SetXXXandGetXXXScenario


1 /**
2  * C-JDBC: Clustered JDBC.
3  * Copyright (C) 2002-2005 French National Institute For Research In Computer
4  * Science And Control (INRIA).
5  * Contact: c-jdbc@objectweb.org
6  *
7  * This library is free software; you can redistribute it and/or modify it
8  * under the terms of the GNU Lesser General Public License as published by the
9  * Free Software Foundation; either version 2.1 of the License, or any later
10  * version.
11  *
12  * This library is distributed in the hope that it will be useful, but WITHOUT
13  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
14  * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
15  * for more details.
16  *
17  * You should have received a copy of the GNU Lesser General Public License
18  * along with this library; if not, write to the Free Software Foundation,
19  * Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
20  *
21  * Initial developer(s): Marc Wick.
22  * Contributor(s): Emmanuel Cecchet
23  */

24
25 package org.objectweb.cjdbc.scenario.raidb1.driver;
26
27 import java.math.BigDecimal JavaDoc;
28 import java.sql.Connection JavaDoc;
29 import java.sql.PreparedStatement JavaDoc;
30 import java.sql.ResultSet JavaDoc;
31 import java.sql.SQLException JavaDoc;
32 import java.sql.Statement JavaDoc;
33 import java.util.Calendar JavaDoc;
34 import java.util.Date JavaDoc;
35 import java.util.Properties JavaDoc;
36
37 import org.objectweb.cjdbc.driver.ControllerInfo;
38 import org.objectweb.cjdbc.scenario.templates.Raidb1Template;
39
40 /**
41  * This class is testing the setter for preparedstatement and the getters for
42  * ResultSet.
43  *
44  * @author <a HREF="mailto:marc.wick@monte-bre.ch">Marc Wick </a>
45  */

46 public class SetXXXandGetXXXScenario extends Raidb1Template
47 {
48
49   String JavaDoc tableName;
50
51   /**
52    * The connection to be used to the database for the test
53    */

54
55   /**
56    * Load C-JDBC driver and retrieve connection from database
57    *
58    * @see junit.framework.TestCase#setUp()
59    */

60   protected void setUp()
61   {
62     tableName = "test" + System.currentTimeMillis();
63     super.setUp();
64   }
65
66   /**
67    * @see junit.framework.TestCase#tearDown()
68    */

69   protected void tearDown()
70   {
71     try
72     {
73       Connection JavaDoc con = getCJDBCConnection();
74       con.createStatement().executeUpdate("drop table " + tableName);
75       con.close();
76     }
77     catch (Exception JavaDoc e)
78     {
79
80     }
81     super.tearDown();
82   }
83
84   /**
85    * Test method for boolean
86    *
87    * @throws Exception possibly ...
88    */

89   public void testBoolean() throws Exception JavaDoc
90   {
91     Properties JavaDoc props = new Properties JavaDoc();
92     props.put("user", "user");
93     props.put("password", "");
94     // This is database specific value so for testing we use the hypersonic ones
95
props.put("booleanTrue", "true");
96     props.put("booleanFalse", "false");
97
98     Connection JavaDoc con = getCJDBCConnection(
99         new ControllerInfo[]{new ControllerInfo("localhost", 25322)}, "myDB",
100         props);
101     Statement JavaDoc stmt = con.createStatement();
102     stmt.executeUpdate("create table " + tableName
103         + " (isOk BIT, booleanChar char(1))");
104     java.sql.PreparedStatement JavaDoc pstmt = con.prepareStatement("insert into "
105         + tableName + " (isok) values (?)");
106     pstmt.setBoolean(1, false);
107     pstmt.executeUpdate();
108     ResultSet JavaDoc rs = stmt.executeQuery("select * from " + tableName);
109     while (rs.next())
110     {
111       boolean isok = rs.getBoolean(1);
112       assertEquals("problems with setBoolean/getBoolean for value false",
113           false, isok);
114     }
115     stmt.executeUpdate("delete from " + tableName);
116     pstmt.setBoolean(1, true);
117     pstmt.executeUpdate();
118     rs = stmt.executeQuery("select * from " + tableName);
119     while (rs.next())
120     {
121       boolean isok = rs.getBoolean(1);
122       assertEquals("problems with setBoolean/getBoolean for value true", true,
123           isok);
124       boolean isokCharNull = rs.getBoolean(2);
125       assertEquals("null columns should return false", false, isokCharNull);
126     }
127
128     // now we test conversion from char/varchar columns
129
pstmt = con
130         .prepareStatement("update " + tableName + " set booleanChar =? ");
131     pstmt.setString(1, "t");
132     pstmt.executeUpdate();
133     rs = stmt.executeQuery("select booleanChar from " + tableName);
134     while (rs.next())
135     {
136       boolean isok = rs.getBoolean(1);
137       assertEquals("problems with setBoolean/getBoolean for value true", true,
138           isok);
139     }
140
141     con.close();
142   }
143
144   /**
145    * Test method for BigDecimal
146    *
147    * @throws Exception possibly ...
148    */

149   public void testBigDecimal() throws Exception JavaDoc
150   {
151     Connection JavaDoc con = getCJDBCConnection();
152     Statement JavaDoc stmt = con.createStatement();
153     stmt.executeUpdate("create table " + tableName
154         + " (numbervalue decimal(10,2),floatvalue float, intvalue int)");
155     java.sql.PreparedStatement JavaDoc pstmt = con.prepareStatement("insert into "
156         + tableName + " (numbervalue, floatvalue,intvalue) values (?,?,?)");
157     BigDecimal JavaDoc expectedValue = new BigDecimal JavaDoc("0.00");
158     pstmt.setBigDecimal(1, expectedValue);
159     pstmt.setFloat(2, expectedValue.floatValue());
160     pstmt.setInt(3, expectedValue.intValue());
161     pstmt.executeUpdate();
162     ResultSet JavaDoc rs = stmt.executeQuery("select * from " + tableName);
163     while (rs.next())
164     {
165       BigDecimal JavaDoc bigDecimal = rs.getBigDecimal("numbervalue");
166       assertEquals(expectedValue, bigDecimal);
167       bigDecimal = rs.getBigDecimal("floatvalue");
168       assertTrue(expectedValue.floatValue() == bigDecimal.floatValue());
169       bigDecimal = rs.getBigDecimal("intvalue");
170       assertEquals(expectedValue.intValue(), bigDecimal.intValue());
171     }
172
173     // test null value
174
expectedValue = null;
175     pstmt = con
176         .prepareStatement("update " + tableName + " set numbervalue = ?");
177     pstmt.setBigDecimal(1, expectedValue);
178     pstmt.executeUpdate();
179     rs = stmt.executeQuery("select * from " + tableName);
180     while (rs.next())
181     {
182       BigDecimal JavaDoc bigDecimal = rs.getBigDecimal("numbervalue");
183       assertEquals(expectedValue, bigDecimal);
184     }
185
186     // test some values
187
expectedValue = new BigDecimal JavaDoc("98765.41");
188     pstmt = con.prepareStatement("update " + tableName
189         + " set numbervalue = ?,floatvalue=?");
190     pstmt.setBigDecimal(1, expectedValue);
191     pstmt.setFloat(2, 0.1f);
192     pstmt.executeUpdate();
193     rs = stmt.executeQuery("select * from " + tableName);
194     while (rs.next())
195     {
196       BigDecimal JavaDoc bigDecimal = rs.getBigDecimal("numbervalue");
197       assertEquals(expectedValue, bigDecimal);
198       BigDecimal JavaDoc floatValue = rs.getBigDecimal("floatvalue");
199       assertTrue(0.1f == floatValue.floatValue());
200     }
201     con.close();
202   }
203
204   /**
205    * Test method for String
206    *
207    * @throws Exception possibly ...
208    */

209   public void testString() throws Exception JavaDoc
210   {
211     Properties JavaDoc props = new Properties JavaDoc();
212     props.put("user", "user");
213     props.put("password", "");
214     props.put("escapeBackslash", "false");
215     props.put("escapeSingleQuote", "true");
216
217     Connection JavaDoc con = getCJDBCConnection(
218         new ControllerInfo[]{new ControllerInfo("localhost", 25322)}, "myDB",
219         props);
220     Statement JavaDoc stmt = con.createStatement();
221     stmt.executeUpdate("create table " + tableName
222         + " (stringvalue varchar(1000))");
223     java.sql.PreparedStatement JavaDoc pstmt = con.prepareStatement("insert into "
224         + tableName + " (stringvalue) values (?)");
225     String JavaDoc expectedValue = "A text with lower and UPPERCASE letters and some numbers 12345143";
226     pstmt.setString(1, expectedValue);
227     pstmt.executeUpdate();
228     ResultSet JavaDoc rs = stmt.executeQuery("select * from " + tableName);
229     while (rs.next())
230     {
231       String JavaDoc string = rs.getString(1);
232       assertEquals(expectedValue, string);
233     }
234
235     // test null value
236
expectedValue = null;
237     pstmt = con
238         .prepareStatement("update " + tableName + " set stringvalue = ?");
239     pstmt.setString(1, expectedValue);
240     pstmt.executeUpdate();
241     rs = stmt.executeQuery("select * from " + tableName);
242     while (rs.next())
243     {
244       String JavaDoc string = rs.getString(1);
245       assertEquals(expectedValue, string);
246     }
247
248     // test 'null' value
249
expectedValue = "null";
250     pstmt = con
251         .prepareStatement("update " + tableName + " set stringvalue = ?");
252     pstmt.setString(1, expectedValue);
253     pstmt.executeUpdate();
254     rs = stmt.executeQuery("select * from " + tableName);
255     while (rs.next())
256     {
257       String JavaDoc string = rs.getString(1);
258       assertEquals(expectedValue, string);
259     }
260
261     // test quotes
262
expectedValue = "\"this is a quote\" and 'single quotes' and other strange things \\ \\0 / {d } ";
263     pstmt = con
264         .prepareStatement("update " + tableName + " set stringvalue = ?");
265     pstmt.setEscapeProcessing(true);
266     pstmt.setString(1, expectedValue);
267     pstmt.executeUpdate();
268     rs = stmt.executeQuery("select * from " + tableName);
269     while (rs.next())
270     {
271       String JavaDoc string = rs.getString(1);
272       assertEquals(expectedValue, string);
273     }
274
275     con.close();
276   }
277
278   /**
279    * Test macros are not replaced within string
280    *
281    * @throws Exception if fails
282    */

283   public void testMacros() throws Exception JavaDoc
284   {
285     Properties JavaDoc props = new Properties JavaDoc();
286     props.put("user", "user");
287     props.put("password", "");
288     props.put("escapeBackslash", "false");
289     props.put("escapeSingleQuote", "true");
290     // test macros
291
Connection JavaDoc con = getCJDBCConnection(
292         new ControllerInfo[]{new ControllerInfo("localhost", 25322)}, "myDB",
293         props);
294     Statement JavaDoc stmt = con.createStatement();
295     stmt.executeUpdate("create table " + tableName
296         + " (stringvalue varchar(1000))");
297     String JavaDoc expectedValue = "macros in string should not be replaced , like rand(), now() and current_timestamp";
298     PreparedStatement JavaDoc pstmt = con.prepareStatement("update " + tableName
299         + " set stringvalue = ?");
300     pstmt.setEscapeProcessing(true);
301     pstmt.setString(1, expectedValue);
302     pstmt.executeUpdate();
303     ResultSet JavaDoc rs = pstmt.executeQuery("select * from " + tableName);
304     while (rs.next())
305     {
306       String JavaDoc string = rs.getString(1);
307       assertEquals(expectedValue, string);
308     }
309     con.close();
310   }
311
312   /**
313    * Test method for Time fields (timestamp, date, time)
314    *
315    * @throws Exception possibly ...
316    */

317   public void testTimefields() throws Exception JavaDoc
318   {
319     Connection JavaDoc con = getCJDBCConnection();
320     Statement JavaDoc stmt = con.createStatement();
321     stmt
322         .executeUpdate("create table "
323             + tableName
324             + " (timestampvalue timestamp, stringtsvalue varchar(20),stringtimevalue varchar(8))");
325     java.sql.PreparedStatement JavaDoc pstmt = con.prepareStatement("insert into "
326         + tableName
327         + " (timestampvalue,stringtsvalue,stringtimevalue) values (?,?,?)");
328     Date JavaDoc expectedValue = new Date JavaDoc();
329     pstmt.setTimestamp(1, new java.sql.Timestamp JavaDoc(expectedValue.getTime()));
330     pstmt.setString(2, new java.sql.Timestamp JavaDoc(expectedValue.getTime())
331         .toString());
332     pstmt.setString(3, new java.sql.Time JavaDoc(expectedValue.getTime()).toString());
333     pstmt.executeUpdate();
334     ResultSet JavaDoc rs = stmt.executeQuery("select * from " + tableName);
335     while (rs.next())
336     {
337       Date JavaDoc timestamp = rs.getTimestamp(1);
338       assertEquals(expectedValue, timestamp);
339
340       // test string column
341
timestamp = rs.getTimestamp(2);
342       assertEquals(expectedValue, timestamp);
343
344       Date JavaDoc date = rs.getDate(1);
345
346       // clear all time fields to get date
347
Calendar JavaDoc cal = Calendar.getInstance();
348       cal.setTime(expectedValue);
349       cal.clear(Calendar.HOUR_OF_DAY);
350       cal.clear(Calendar.HOUR);
351       cal.clear(Calendar.MINUTE);
352       cal.clear(Calendar.SECOND);
353       cal.clear(Calendar.MILLISECOND);
354       cal.clear(Calendar.AM_PM);
355       assertEquals(cal.getTime(), date);
356
357       date = rs.getDate(2);
358       assertEquals(cal.getTime(), date);
359
360       Date JavaDoc time = rs.getTime(1);
361       // clear all date fields to get time
362
cal = Calendar.getInstance();
363       cal.setTime(expectedValue);
364       cal.clear(Calendar.YEAR);
365       cal.clear(Calendar.MONTH);
366       cal.clear(Calendar.DATE);
367       cal.clear(Calendar.MILLISECOND);
368       assertEquals(cal.getTimeInMillis(), time.getTime());
369
370       date = rs.getTime(3);
371       assertEquals(cal.getTimeInMillis(), time.getTime());
372
373     }
374
375     // test null value
376
expectedValue = null;
377     pstmt = con.prepareStatement("update " + tableName
378         + " set timestampvalue = ?");
379     pstmt.setTimestamp(1, null);
380     pstmt.executeUpdate();
381     rs = stmt.executeQuery("select * from " + tableName);
382     while (rs.next())
383     {
384       Date JavaDoc date = rs.getTimestamp(1);
385       assertEquals(expectedValue, date);
386     }
387
388     con.close();
389   }
390
391   /**
392    * Test method for Object
393    *
394    * @throws Exception possibly ...
395    */

396   public void testObject() throws Exception JavaDoc
397   {
398     Connection JavaDoc con = getCJDBCConnection();
399     Statement JavaDoc stmt = con.createStatement();
400     stmt.executeUpdate("create table " + tableName + " (numbervalue int)");
401     java.sql.PreparedStatement JavaDoc pstmt = con.prepareStatement("insert into "
402         + tableName + " (numbervalue) values (?)");
403     Integer JavaDoc intObj = new Integer JavaDoc(0);
404     pstmt.setObject(1, intObj);
405     pstmt.executeUpdate();
406     ResultSet JavaDoc rs = stmt.executeQuery("select * from " + tableName);
407     while (rs.next())
408     {
409       Integer JavaDoc intObjResult = new Integer JavaDoc(rs.getInt(1));
410       assertEquals(intObj, intObjResult);
411     }
412
413     // test null value
414
pstmt = con
415         .prepareStatement("update " + tableName + " set numbervalue = ?");
416     pstmt.setObject(1, null);
417     pstmt.executeUpdate();
418     rs = stmt.executeQuery("select * from " + tableName);
419     while (rs.next())
420     {
421       Object JavaDoc obj = rs.getObject(1);
422       assertEquals(null, obj);
423     }
424     con.close();
425   }
426
427   /**
428    * Test method for long
429    *
430    * @throws Exception possibly ...
431    */

432   public void testLong() throws Exception JavaDoc
433   {
434     Connection JavaDoc con = getCJDBCConnection();
435     Statement JavaDoc stmt = con.createStatement();
436     stmt.executeUpdate("create table " + tableName
437         + " (numbervalue int, stringvalue varchar(20),charvalue char)");
438     java.sql.PreparedStatement JavaDoc pstmt = con.prepareStatement("insert into "
439         + tableName + " (numbervalue,stringvalue,charvalue) values (?,22,3)");
440     Integer JavaDoc intObj = new Integer JavaDoc(11);
441     pstmt.setObject(1, intObj);
442     pstmt.executeUpdate();
443     ResultSet JavaDoc rs = stmt.executeQuery("select * from " + tableName);
444     rs.next();
445     assertEquals("convert int to long", 11, rs.getLong(1));
446     assertEquals("convert varchar to long", 22, rs.getLong(2));
447     assertEquals("convert char to long", 3, rs.getLong(3));
448     rs.close();
449
450     stmt
451         .executeUpdate("update " + tableName + " set stringvalue = 'no number'");
452
453     rs = stmt.executeQuery("select * from " + tableName);
454     rs.next();
455     try
456     {
457       rs.getLong(2);
458       fail("invalid number should throw an SQLException");
459     }
460     catch (SQLException JavaDoc success)
461     {
462       // fine we were excpecting an SQLException
463
}
464
465     con.close();
466   }
467
468   /**
469    * Test method for Bytes
470    *
471    * @throws Exception possibly ...
472    */

473   public void testBytes() throws Exception JavaDoc
474   {
475     Connection JavaDoc con = getCJDBCConnection();
476     Statement JavaDoc stmt = con.createStatement();
477     stmt.executeUpdate("create table " + tableName + " (binaryvalue binary)");
478     java.sql.PreparedStatement JavaDoc pstmt = con.prepareStatement("insert into "
479         + tableName + " (binaryvalue) values (?)");
480
481     byte[] bbuf = new byte[256];
482     for (int i = -128; i < 128; i++)
483     {
484       bbuf[128 + i] = (byte) i;
485     }
486
487     pstmt.setBytes(1, bbuf);
488     pstmt.executeUpdate();
489     ResultSet JavaDoc rs = stmt.executeQuery("select * from " + tableName);
490     while (rs.next())
491     {
492       bbuf = rs.getBytes(1);
493       for (int i = -128; i < 128; i++)
494       {
495         assertEquals(bbuf[128 + i], i);
496       }
497     }
498
499     con.close();
500   }
501 }
502
Popular Tags