Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbc4 > TestPreparedStatementMethods


1 /*
2  
3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc4.TestPreparedStatementMethods
4  
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to you under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11  
12       http://www.apache.org/licenses/LICENSE-2.0
13  
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19  
20  */
21
22 package org.apache.derbyTesting.functionTests.tests.jdbc4;
23
24 import java.io.File ;
25 import java.io.FileInputStream ;
26 import java.io.FileNotFoundException ;
27 import java.io.Reader ;
28 import java.io.InputStream ;
29 import java.io.IOException ;
30 import java.security.*;
31 import java.sql.Blob ;
32 import java.sql.Connection ;
33 import java.sql.Clob ;
34 import java.sql.DriverManager ;
35 import java.sql.PreparedStatement ;
36 import java.sql.SQLException ;
37 import java.sql.NClob ;
38 import java.sql.ResultSet ;
39 import java.sql.SQLXML ;
40 import java.sql.Statement ;
41 import org.apache.derby.shared.common.reference.SQLState;
42 import org.apache.derby.tools.ij;
43 import org.apache.derby.iapi.error.StandardException;
44 import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
45 /**
46  * This class is used to test the implementations of the JDBC 4.0 methods
47  * in the PreparedStatement interface
48  */
49
50 public class TestPreparedStatementMethods {
51     
52     static Connection conn = null;
53     PreparedStatement ps = null;
54     
55     String filepath;
56     String sep;
57     boolean exists;
58     /*
59      * This function is used to build the path to the directory where the files
60      * that are used to create the test blob and clob are present
61      */
62     void buildFilePath(String filename) {
63         filepath = "extin";
64         sep = System.getProperty("file.separator");
65         exists = (new File ("extin", filename)).exists();
66         if(!exists){
67             String userDir = System.getProperty("user.dir");
68             filepath = userDir + sep + ".." + sep + filepath;
69         }
70     }
71     void t_setNString() {
72         try {
73             ps.setNString(0,null);
74             System.out.println("UnImplemented Exception not thrown in code");
75         } catch(SQLException e) {
76             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
77                 System.out.println("Unexpected SQLException"+e);
78             }
79             
80         } catch(Exception e) {
81             System.out.println("Unexpected exception thrown in method"+e);
82             e.printStackTrace();
83         }
84     }
85     void t_setNCharacterStream() {
86         try {
87             ps.setNCharacterStream(0,null,0);
88             System.out.println("UnImplemented Exception not thrown in code");
89         } catch(SQLException e) {
90             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
91                 System.out.println("Unexpected SQLException"+e);
92             }
93             
94         } catch(Exception e) {
95             System.out.println("Unexpected exception thrown in method"+e);
96             e.printStackTrace();
97         }
98     }
99     void t_setNClob1() {
100         try {
101             ps.setNClob(0,(NClob )null);
102             System.out.println("UnImplemented Exception not thrown in code");
103         } catch(SQLException e) {
104             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
105                 System.out.println("Unexpected SQLException"+e);
106             }
107             
108         } catch(Exception e) {
109             System.out.println("Unexpected exception thrown in method"+e);
110             e.printStackTrace();
111         }
112     }
113     /*
114      * Compares the two clobs supplied to se if they are similar
115      * returns true if they are similar and false otherwise
116      */
117     boolean compareClob(Clob clob1,Clob clob2) {
118         int c1,c2;
119         InputStream is1=null,is2=null;
120         try {
121             is1 = clob1.getAsciiStream();
122             is2 = clob2.getAsciiStream();
123             if(clob1.length()!=clob2.length())
124                 return false;
125         } catch(SQLException sqle){
126             sqle.printStackTrace();
127         }
128         try {
129             for(long i=0;i<clob1.length();i++) {
130                 c1=is1.read();
131                 c2=is2.read();
132                 if(c1!=c2)
133                     return false;
134             }
135         } catch(IOException e) {
136             e.printStackTrace();
137         } catch(SQLException e) {
138             e.printStackTrace();
139         }
140         return true;
141     }
142     /*
143      * Compares the two blobs supplied to se if they are similar
144      * returns true if they are similar and false otherwise
145      */
146     boolean compareBlob(Blob blob1,Blob blob2) {
147         int c1,c2;
148         InputStream is1=null,is2=null;
149         try {
150             is1 = blob1.getBinaryStream();
151             is2 = blob2.getBinaryStream();
152             if(blob1.length()!=blob2.length())
153                 return false;
154         } catch(SQLException sqle){
155             sqle.printStackTrace();
156         }
157         try {
158             for(long i=0;i<blob1.length();i++) {
159                 c1=is1.read();
160                 c2=is2.read();
161                 if(c1!=c2)
162                     return false;
163             }
164         } catch(IOException e) {
165             e.printStackTrace();
166         } catch(SQLException e) {
167             e.printStackTrace();
168         }
169         return true;
170     }
171     /*
172      * Build the clob value to be inserted into the table and insert it using
173      * the setClob method in the PreparedStatement interface
174      */
175     Clob buildAndInsertClobValue(int n,String filename,Connection conn){
176         int c;
177         byte [] fromFile = new byte[1024];
178         Clob clob=null;
179         try {
180             clob = conn.createClob();
181             java.io.OutputStream os = clob.setAsciiStream(1);
182             buildFilePath(filename);
183             File f = new File (filepath + sep + filename);
184             InputStream is = getInputStream(f);
185             c = is.read(fromFile);
186             while(c>0) {
187                 os.write(fromFile,0,c);
188                 c = is.read(fromFile);
189             }
190             PreparedStatement ps = conn.prepareStatement("insert into clobtable3 values(?,?)");
191             ps.setInt(1, n);
192             ps.setClob(2,clob);
193             ps.executeUpdate();
194         } catch(IOException ioe) {
195             ioe.printStackTrace();
196         } catch(PrivilegedActionException pae) {
197             pae.printStackTrace();
198         } catch(SQLException sqle) {
199             sqle.printStackTrace();
200         }
201         return clob;
202     }
203     /*
204      * Build the clob value to be inserted into the table and insert it using
205      * the setBlob method in the PreparedStatement interface
206      */
207     Blob buildAndInsertBlobValue(int n,String filename,Connection conn){
208         int c;
209         byte [] fromFile = new byte[1024];
210         Blob blob=null;
211         try {
212             blob = conn.createBlob();
213             java.io.OutputStream os = blob.setBinaryStream(1);
214             buildFilePath(filename);
215             File f = new File (filepath + sep + filename);
216             InputStream is = getInputStream(f);
217             c = is.read(fromFile);
218             while(c>0) {
219                 os.write(fromFile,0,c);
220                 c = is.read(fromFile);
221             }
222             PreparedStatement ps = conn.prepareStatement("insert into blobtable3 values(?,?)");
223             ps.setInt(1, n);
224             ps.setBlob(2,blob);
225             ps.executeUpdate();
226         } catch(IOException ioe) {
227             ioe.printStackTrace();
228         } catch(PrivilegedActionException pae) {
229             pae.printStackTrace();
230         } catch(SQLException sqle) {
231             sqle.printStackTrace();
232         }
233         return blob;
234     }
235
236     /**
237      * May need to convert this into a privileged block for reading a file.
238      */
239     protected static FileInputStream getInputStream(final File f)
240         throws PrivilegedActionException, FileNotFoundException
241     {
242         return (FileInputStream )AccessController.doPrivileged
243             ( new PrivilegedExceptionAction<FileInputStream >( )
244         {
245             public FileInputStream run() throws FileNotFoundException
246             {
247                 return new FileInputStream (f);
248             }
249             });
250     }
251
252
253     /*
254      * 1) Insert the clob in to the clob table by calling the
255      * buildAndInsertClobValue function
256      * 2) Check whether the clob value has been correctly inserted in to the
257      * table by using the compareClob function
258      */
259     void t_setClob() {
260         try {
261             int c;
262             byte [] fromFile;
263             fromFile = new byte[1024];
264             Statement s = conn.createStatement();
265             s.execute("create table clobtable3(n int)");
266             s.execute("alter table clobtable3 add column clobCol CLOB(1M)");
267             s.close();
268             Clob clob = buildAndInsertClobValue(0000,"short.txt",conn);
269             Clob clob1 = buildAndInsertClobValue(1000, "aclob.txt",conn);
270             Clob clob2 = buildAndInsertClobValue(2000,"littleclob.txt",conn);
271             PreparedStatement ps3 = conn.prepareStatement("select * from " +
272                     "clobtable3 where n=1000");
273             ResultSet rs3 = ps3.executeQuery();
274             rs3.next();
275             Clob clob3 = rs3.getClob(2);
276             if(!compareClob(clob1,clob3)) {
277                 System.out.println("Difference between the inserted and the " +
278                         "queried Clob values");
279             }
280             PreparedStatement ps4 = conn.prepareStatement("select * from " +
281                     "clobtable3");
282             ResultSet rs4 = ps4.executeQuery();
283             rs4.next();
284             Clob clob4 = rs4.getClob(2);
285             if(!compareClob(clob,clob4)) {
286                 System.out.println("Difference between the inserted and the " +
287                         "queried Clob values");
288             }
289             rs4.next();
290             clob4 = rs4.getClob(2);
291             if(!compareClob(clob1,clob4)) {
292                 System.out.println("Difference between the inserted and the " +
293                         "queried Clob values");
294             }
295             rs4.next();
296             clob4 = rs4.getClob(2);
297             if(!compareClob(clob2,clob4)) {
298                 System.out.println("Difference between the inserted and the " +
299                         "queried Clob values");
300             }
301             
302         } catch(SQLException e) {
303             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
304                 e.printStackTrace();
305             }
306         } catch(Exception e) {
307             e.printStackTrace();
308         }
309     }
310     /*
311      * 1) Insert the blob in to the blob table by calling the
312      * buildAndInsertBlobValue function
313      * 2) Check whether the blob value has been correctly inserted in to the
314      * table by using the compareBlob function
315      */
316     void t_setBlob() {
317         try {
318             int c;
319             byte [] fromFile;
320             fromFile = new byte[1024];
321             Statement s = conn.createStatement();
322             s.execute("create table blobtable3(n int)");
323             s.execute("alter table blobtable3 add column blobCol BLOB(1M)");
324             s.close();
325             Blob blob = buildAndInsertBlobValue(0000, "short.txt", conn);
326             Blob blob1 = buildAndInsertBlobValue(1000, "aclob.txt", conn);
327             Blob blob2 = buildAndInsertBlobValue(2000, "littleclob.txt", conn);
328             PreparedStatement ps3 = conn.prepareStatement("select * from " +
329                     "blobtable3 where n=1000");
330             ResultSet rs3 = ps3.executeQuery();
331             rs3.next();
332             Blob blob3 = rs3.getBlob(2);
333             if(!compareBlob(blob1,blob3)) {
334                 System.out.println("Difference between the inserted and the " +
335                         "queried Blob values");
336             }
337             PreparedStatement ps4 = conn.prepareStatement("select * from blobtable3");
338             ResultSet rs4 = ps4.executeQuery();
339             rs4.next();
340             Blob blob4 = rs4.getBlob(2);
341             if(!compareBlob(blob,blob4)) {
342                 System.out.println("Difference between the inserted and the " +
343                         "queried Blob values");
344             }
345             rs4.next();
346             blob4 = rs4.getBlob(2);
347             if(!compareBlob(blob1,blob4)) {
348                 System.out.println("Difference between the inserted and the " +
349                         "queried Blob values");
350             }
351             rs4.next();
352             blob4 = rs4.getBlob(2);
353             if(!compareBlob(blob2,blob4)) {
354                 System.out.println("Difference between the inserted and the " +
355                         "queried Blob values");
356             }
357             
358         } catch(SQLException e) {
359             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
360                 e.printStackTrace();
361             }
362         } catch(Exception e) {
363             e.printStackTrace();
364         }
365     }
366     /*
367      * The setClob method on the embedded side. Here functionality has still not
368      * been added. It still throws a notImplemented exception only
369      */
370     void t_Clob_setMethods_Embedded(Connection conn){
371         try {
372             Statement s = conn.createStatement();
373             ResultSet rs = s.executeQuery("select * from clobtable3");
374             rs.next();
375             Clob clob = rs.getClob(2);
376             PreparedStatement ps = conn.prepareStatement("insert into clobtable3" +
377                     " values(?,?)");
378             ps.setInt(1, 3000);
379             ps.setClob(2, clob);
380             ps.executeUpdate();
381             ps.close();
382         } catch(SQLException e){
383             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
384                 System.out.println("Unexpected SQLException"+e);
385                 e.printStackTrace();
386             }
387         }
388     }
389     /*
390      * The setBlob method on the embedded side. Here functionality has still not
391      * been added. It still throws a notImplemented exception only
392      */
393     void t_Blob_setMethods_Embedded(Connection conn){
394         try {
395             Statement s = conn.createStatement();
396             ResultSet rs = s.executeQuery("select * from blobtable3");
397             rs.next();
398             Blob blob = rs.getBlob(2);
399             PreparedStatement ps = conn.prepareStatement("insert into blobtable3" +
400                     " values(?,?)");
401             ps.setInt(1, 3000);
402             ps.setBlob(2, blob);
403             ps.executeUpdate();
404             ps.close();
405         } catch(SQLException e){
406             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
407                 System.out.println("Unexpected SQLException"+e);
408                 e.printStackTrace();
409             }
410         }
411     }
412     
413     void t_setNClob2() {
414         try {
415             ps.setNClob(0,null,0);
416             System.out.println("UnImplemented Exception not thrown in code");
417         } catch(SQLException e) {
418             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
419                 System.out.println("Unexpected SQLException"+e);
420             }
421             
422         } catch(Exception e) {
423             System.out.println("Unexpected exception thrown in method"+e);
424             e.printStackTrace();
425         }
426     }
427     void t_setSQLXML() {
428         try {
429             ps.setSQLXML(0,null);
430             System.out.println("UnImplemented Exception not thrown in code");
431         } catch(SQLException e) {
432             if(SQLState.NOT_IMPLEMENTED.equals (e.getSQLState())) {
433                 System.out.println("Unexpected SQLException"+e);
434             }
435             
436         } catch(Exception e) {
437             System.out.println("Unexpected exception thrown in method"+e);
438             e.printStackTrace();
439         }
440     }
441     void t_setPoolable() {
442         try {
443             // Set the poolable statement hint to false
444 ps.setPoolable(false);
445             if (ps.isPoolable())
446                 System.out.println("Expected a non-poolable statement");
447             // Set the poolable statement hint to true
448 ps.setPoolable(true);
449             if (!ps.isPoolable())
450                 System.out.println("Expected a poolable statement");
451         } catch(SQLException sqle) {
452             // Check which SQLException state we've got and if it is
453 // expected, do not print a stackTrace
454 // Embedded uses XJ012, client uses XCL31.
455 if (sqle.getSQLState().equals("XJ012") ||
456                 sqle.getSQLState().equals("XCL31")) {
457                 // All is good and is expected
458 } else {
459                 System.out.println("Unexpected SQLException " + sqle);
460                 sqle.printStackTrace();
461             }
462         } catch(Exception e) {
463             System.out.println("Unexpected exception thrown in method " + e);
464             e.printStackTrace();
465         }
466     }
467     void t_isPoolable() {
468         try {
469             // By default a prepared statement is poolable
470 if (!ps.isPoolable())
471                 System.out.println("Expected a poolable statement");
472         } catch(SQLException sqle) {
473             // Check which SQLException state we've got and if it is
474 // expected, do not print a stackTrace
475 // Embedded uses XJ012, client uses XCL31.
476 if (sqle.getSQLState().equals("XJ012") ||
477                 sqle.getSQLState().equals("XCL31")) {
478                 // All is good and is expected
479 } else {
480                 System.out.println("Unexpected SQLException " + sqle);
481                 sqle.printStackTrace();
482             }
483         } catch(Exception e) {
484             System.out.println("Unexpected exception thrown in method " + e);
485             e.printStackTrace();
486         }
487     }
488     
489     /**
490      * Tests the wrapper methods isWrapperFor and unwrap. There are two cases
491      * to be tested
492      * Case 1: isWrapperFor returns true and we call unwrap
493      * Case 2: isWrapperFor returns false and we call unwrap
494      */
495     void t_wrapper() {
496         Class <PreparedStatement > wrap_class = PreparedStatement .class;
497         
498         //The if method succeeds enabling us to call the unwrap method without
499 //throwing an exception
500 try {
501             if(ps.isWrapperFor(wrap_class)) {
502                 PreparedStatement stmt1 =
503                         (PreparedStatement )ps.unwrap(wrap_class);
504             }
505             else {
506                 System.out.println("isWrapperFor wrongly returns false");
507             }
508         }
509         catch(SQLException sqle) {
510             sqle.printStackTrace();
511         }
512         
513         //Begin test for case2
514 //test for the case when isWrapper returns false
515 //using some class that will return false when
516 //passed to isWrapperFor
517 Class <ResultSet > wrap_class1 = ResultSet .class;
518         
519         try {
520             //returning false is the correct behaviour in this case
521 //Generate a message if it returns true
522 if(ps.isWrapperFor(wrap_class1)) {
523                 System.out.println("isWrapperFor wrongly returns true");
524             }
525             else {
526                 ResultSet rs1 = (ResultSet )
527                                            ps.unwrap(wrap_class1);
528                 System.out.println("unwrap does not throw the expected " +
529                                    "exception");
530             }
531         }
532         catch (SQLException sqle) {
533             //calling unwrap in this case throws an SQLException
534 //ensure that this SQLException has the correct SQLState
535 if(!SQLStateConstants.UNABLE_TO_UNWRAP.equals(sqle.getSQLState())) {
536                 sqle.printStackTrace();
537             }
538         }
539     }
540     /*
541      * Start the tests for the JDBC4.0 methods on the client side
542      */
543     void startClientTestMethods( Connection conn_main ) {
544         PreparedStatement ps_main = null;
545         
546         try {
547             ps_main = conn_main.prepareStatement("select count(*) from " +
548                     "sys.systables");
549             conn = conn_main;
550             ps = ps_main;
551             t_setNString();
552             t_setNCharacterStream();
553             t_setNClob1();
554             t_setClob();
555             t_setBlob();
556             t_setNClob2();
557             t_setSQLXML();
558             t_isPoolable();
559             t_setPoolable();
560             t_wrapper();
561             // Close the prepared statement and verify the poolable hint
562 // cannot be set or retrieved
563 ps.close();
564             t_isPoolable();
565             t_setPoolable();
566         } catch(SQLException sqle) {
567             sqle.printStackTrace();
568         } finally {
569             try {
570                 conn_main.close();
571             } catch(SQLException sqle){
572                 sqle.printStackTrace();
573             }
574         }
575     }
576     /*
577      * Start the tests for testing the JDBC4.0 methods on the embedded side
578      */
579     void startEmbeddedTestMethods( Connection conn_main ) {
580         PreparedStatement ps_main = null;
581         
582         try {
583             Statement s = conn_main.createStatement();
584             s.execute("create table clobtable3 (n int,clobcol CLOB)");
585             File file = new File ("extin/short.txt");
586             int fileLength = (int) file.length();
587             InputStream fin = getInputStream(file);
588             ps = conn_main.prepareStatement("INSERT INTO " +
589                     "clobtable3 " +
590                     "VALUES (?, ?)");
591             ps.setInt(1, 1000);
592             ps.setAsciiStream(2, fin, fileLength);
593             ps.execute();
594             ps.close();
595             
596             Statement s1 = conn_main.createStatement();
597             s1.execute("create table blobtable3 (n int,blobcol BLOB)");
598             File file1 = new File ("extin/short.txt");
599             int fileLength1 = (int) file1.length();
600             InputStream fin1 = getInputStream(file1);
601             PreparedStatement ps1 = conn_main.prepareStatement("INSERT INTO " +
602                     "blobtable3 " +
603                     "VALUES (?, ?)");
604             ps1.setInt(1, 1000);
605             ps1.setBinaryStream(2, fin1, fileLength1);
606             ps1.execute();
607             
608             conn_main.commit();
609             t_Clob_setMethods_Embedded(conn_main);
610             t_Blob_setMethods_Embedded(conn_main);
611             ps_main = conn_main.prepareStatement("select count(*) from " +
612                     "sys.systables");
613             conn = conn_main;
614             ps = ps_main;
615             t_setNString();
616             t_setNCharacterStream();
617             t_setNClob1();
618             t_setNClob2();
619             t_setSQLXML();
620             t_isPoolable();
621             t_setPoolable();
622             t_wrapper();
623             // Close the prepared statement and verify the poolable hint
624 // cannot be set or retrieved
625 ps.close();
626             t_isPoolable();
627             t_setPoolable();
628         }
629         catch(SQLException sqle) {
630             sqle.printStackTrace();
631         } catch(PrivilegedActionException pae) {
632             pae.printStackTrace();
633         } catch(FileNotFoundException fne) {
634             fne.printStackTrace();
635         } finally {
636             try {
637                 conn_main.close();
638             } catch(SQLException sqle){
639                 sqle.printStackTrace();
640             }
641         }
642     }
643     
644     /**
645      * <p>
646      * Return true if we're running under the embedded client.
647      * </p>
648      */
649     private static boolean usingEmbeddedClient()
650     {
651         return "embedded".equals( System.getProperty( "framework" ) );
652     }
653
654     public static void main(String args[]) {
655         try {
656             // use the ij utility to read the property file and
657 // make the initial connection.
658 ij.getPropertyArg(args);
659         
660             Connection conn_main = ij.startJBMS();
661
662             TestPreparedStatementMethods tpsm = new TestPreparedStatementMethods();
663
664             if ( usingEmbeddedClient() )
665             {
666                 tpsm.startEmbeddedTestMethods( conn_main );
667             }
668             else // DerbyNetClient
669 {
670                 tpsm.startClientTestMethods( conn_main );
671             }
672                     
673         } catch(Exception e) { printStackTrace( e ); }
674     }
675
676     private static void printStackTrace( Throwable e )
677     {
678         System.out.println(""+e);
679         e.printStackTrace();
680     }
681 }
682

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags