|
1
24
25 package org.objectweb.cjdbc.scenario.raidb1.driver;
26
27 import java.math.BigDecimal  ;
28 import java.sql.Connection ;
29 import java.sql.PreparedStatement ;
30 import java.sql.ResultSet ;
31 import java.sql.SQLException ;
32 import java.sql.Statement ;
33 import java.util.Calendar ;
34 import java.util.Date ;
35 import java.util.Properties ;
36
37 import org.objectweb.cjdbc.driver.ControllerInfo;
38 import org.objectweb.cjdbc.scenario.templates.Raidb1Template;
39
40
46 public class SetXXXandGetXXXScenario extends Raidb1Template
47 {
48
49 String tableName;
50
51
54
55
60 protected void setUp()
61 {
62 tableName = "test" + System.currentTimeMillis();
63 super.setUp();
64 }
65
66
69 protected void tearDown()
70 {
71 try
72 {
73 Connection con = getCJDBCConnection();
74 con.createStatement().executeUpdate("drop table " + tableName);
75 con.close();
76 }
77 catch (Exception e)
78 {
79
80 }
81 super.tearDown();
82 }
83
84
89 public void testBoolean() throws Exception
90 {
91 Properties props = new Properties ();
92 props.put("user", "user");
93 props.put("password", "");
94 props.put("booleanTrue", "true");
96 props.put("booleanFalse", "false");
97
98 Connection con = getCJDBCConnection(
99 new ControllerInfo[]{new ControllerInfo("localhost", 25322)}, "myDB",
100 props);
101 Statement stmt = con.createStatement();
102 stmt.executeUpdate("create table " + tableName
103 + " (isOk BIT, booleanChar char(1))");
104 java.sql.PreparedStatement pstmt = con.prepareStatement("insert into "
105 + tableName + " (isok) values (?)");
106 pstmt.setBoolean(1, false);
107 pstmt.executeUpdate();
108 ResultSet 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 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
149 public void testBigDecimal() throws Exception
150 {
151 Connection con = getCJDBCConnection();
152 Statement stmt = con.createStatement();
153 stmt.executeUpdate("create table " + tableName
154 + " (numbervalue decimal(10,2),floatvalue float, intvalue int)");
155 java.sql.PreparedStatement pstmt = con.prepareStatement("insert into "
156 + tableName + " (numbervalue, floatvalue,intvalue) values (?,?,?)");
157 BigDecimal expectedValue = new BigDecimal ("0.00");
158 pstmt.setBigDecimal(1, expectedValue);
159 pstmt.setFloat(2, expectedValue.floatValue());
160 pstmt.setInt(3, expectedValue.intValue());
161 pstmt.executeUpdate();
162 ResultSet rs = stmt.executeQuery("select * from " + tableName);
163 while (rs.next())
164 {
165 BigDecimal 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 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 bigDecimal = rs.getBigDecimal("numbervalue");
183 assertEquals(expectedValue, bigDecimal);
184 }
185
186 expectedValue = new BigDecimal ("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 bigDecimal = rs.getBigDecimal("numbervalue");
197 assertEquals(expectedValue, bigDecimal);
198 BigDecimal floatValue = rs.getBigDecimal("floatvalue");
199 assertTrue(0.1f == floatValue.floatValue());
200 }
201 con.close();
202 }
203
204
209 public void testString() throws Exception
210 {
211 Properties props = new Properties ();
212 props.put("user", "user");
213 props.put("password", "");
214 props.put("escapeBackslash", "false");
215 props.put("escapeSingleQuote", "true");
216
217 Connection con = getCJDBCConnection(
218 new ControllerInfo[]{new ControllerInfo("localhost", 25322)}, "myDB",
219 props);
220 Statement stmt = con.createStatement();
221 stmt.executeUpdate("create table " + tableName
222 + " (stringvalue varchar(1000))");
223 java.sql.PreparedStatement pstmt = con.prepareStatement("insert into "
224 + tableName + " (stringvalue) values (?)");
225 String expectedValue = "A text with lower and UPPERCASE letters and some numbers 12345143";
226 pstmt.setString(1, expectedValue);
227 pstmt.executeUpdate();
228 ResultSet rs = stmt.executeQuery("select * from " + tableName);
229 while (rs.next())
230 {
231 String string = rs.getString(1);
232 assertEquals(expectedValue, string);
233 }
234
235 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 string = rs.getString(1);
245 assertEquals(expectedValue, string);
246 }
247
248 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 string = rs.getString(1);
258 assertEquals(expectedValue, string);
259 }
260
261 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 string = rs.getString(1);
272 assertEquals(expectedValue, string);
273 }
274
275 con.close();
276 }
277
278
283 public void testMacros() throws Exception
284 {
285 Properties props = new Properties ();
286 props.put("user", "user");
287 props.put("password", "");
288 props.put("escapeBackslash", "false");
289 props.put("escapeSingleQuote", "true");
290 Connection con = getCJDBCConnection(
292 new ControllerInfo[]{new ControllerInfo("localhost", 25322)}, "myDB",
293 props);
294 Statement stmt = con.createStatement();
295 stmt.executeUpdate("create table " + tableName
296 + " (stringvalue varchar(1000))");
297 String expectedValue = "macros in string should not be replaced , like rand(), now() and current_timestamp";
298 PreparedStatement pstmt = con.prepareStatement("update " + tableName
299 + " set stringvalue = ?");
300 pstmt.setEscapeProcessing(true);
301 pstmt.setString(1, expectedValue);
302 pstmt.executeUpdate();
303 ResultSet rs = pstmt.executeQuery("select * from " + tableName);
304 while (rs.next())
305 {
306 String string = rs.getString(1);
307 assertEquals(expectedValue, string);
308 }
309 con.close();
310 }
311
312
317 public void testTimefields() throws Exception
318 {
319 Connection con = getCJDBCConnection();
320 Statement stmt = con.createStatement();
321 stmt
322 .executeUpdate("create table "
323 + tableName
324 + " (timestampvalue timestamp, stringtsvalue varchar(20),stringtimevalue varchar(8))");
325 java.sql.PreparedStatement pstmt = con.prepareStatement("insert into "
326 + tableName
327 + " (timestampvalue,stringtsvalue,stringtimevalue) values (?,?,?)");
328 Date expectedValue = new Date ();
329 pstmt.setTimestamp(1, new java.sql.Timestamp (expectedValue.getTime()));
330 pstmt.setString(2, new java.sql.Timestamp (expectedValue.getTime())
331 .toString());
332 pstmt.setString(3, new java.sql.Time (expectedValue.getTime()).toString());
333 pstmt.executeUpdate();
334 ResultSet rs = stmt.executeQuery("select * from " + tableName);
335 while (rs.next())
336 {
337 Date timestamp = rs.getTimestamp(1);
338 assertEquals(expectedValue, timestamp);
339
340 timestamp = rs.getTimestamp(2);
342 assertEquals(expectedValue, timestamp);
343
344 Date date = rs.getDate(1);
345
346 Calendar 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 time = rs.getTime(1);
361 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 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 date = rs.getTimestamp(1);
385 assertEquals(expectedValue, date);
386 }
387
388 con.close();
389 }
390
391
396 public void testObject() throws Exception
397 {
398 Connection con = getCJDBCConnection();
399 Statement stmt = con.createStatement();
400 stmt.executeUpdate("create table " + tableName + " (numbervalue int)");
401 java.sql.PreparedStatement pstmt = con.prepareStatement("insert into "
402 + tableName + " (numbervalue) values (?)");
403 Integer intObj = new Integer (0);
404 pstmt.setObject(1, intObj);
405 pstmt.executeUpdate();
406 ResultSet rs = stmt.executeQuery("select * from " + tableName);
407 while (rs.next())
408 {
409 Integer intObjResult = new Integer (rs.getInt(1));
410 assertEquals(intObj, intObjResult);
411 }
412
413 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 obj = rs.getObject(1);
422 assertEquals(null, obj);
423 }
424 con.close();
425 }
426
427
432 public void testLong() throws Exception
433 {
434 Connection con = getCJDBCConnection();
435 Statement stmt = con.createStatement();
436 stmt.executeUpdate("create table " + tableName
437 + " (numbervalue int, stringvalue varchar(20),charvalue char)");
438 java.sql.PreparedStatement pstmt = con.prepareStatement("insert into "
439 + tableName + " (numbervalue,stringvalue,charvalue) values (?,22,3)");
440 Integer intObj = new Integer (11);
441 pstmt.setObject(1, intObj);
442 pstmt.executeUpdate();
443 ResultSet 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 success)
461 {
462 }
464
465 con.close();
466 }
467
468
473 public void testBytes() throws Exception
474 {
475 Connection con = getCJDBCConnection();
476 Statement stmt = con.createStatement();
477 stmt.executeUpdate("create table " + tableName + " (binaryvalue binary)");
478 java.sql.PreparedStatement 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 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 |
Java API By Example, From Geeks To Geeks.