|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.lang.reflect.*;
25
26 import java.sql.CallableStatement  ;
27 import java.sql.Connection ;
28 import java.sql.PreparedStatement ;
29 import java.sql.ResultSet ;
30 import java.sql.ResultSetMetaData ;
31 import java.sql.SQLException ;
32 import java.sql.Statement ;
33 import java.sql.Types ;
34
35 import org.apache.derby.tools.ij;
36 import org.apache.derby.tools.JDBCDisplayUtil;
37
38
42 public class holdCursorJavaReflection {
43
44
53 private static Class [] PREP_STMT_PARAM = { String .class, Integer.TYPE, Integer.TYPE, Integer.TYPE };
55 private static Object [] PREP_STMT_ARG = { "select * from t1 where c12 = ?", new Integer (ResultSet.TYPE_FORWARD_ONLY),
56 new Integer (ResultSet.CONCUR_READ_ONLY), new Integer (ResultSet.HOLD_CURSORS_OVER_COMMIT)};
57
58 private static Object [] PREP_STMT_ERROR_ARG = { "select * from t1NotThere where c12 = ?",
59 new
60 Integer (ResultSet.TYPE_FORWARD_ONLY), new Integer (ResultSet.CONCUR_READ_ONLY), new Integer (ResultSet.HOLD_CURSORS_OVER_COMMIT)};
61
62 private static Class [] STMT_PARAM = { Integer.TYPE, Integer.TYPE, Integer.TYPE };
64 private static Object [] STMT_ARG = { new Integer (ResultSet.TYPE_FORWARD_ONLY),
65 new Integer (ResultSet.CONCUR_READ_ONLY), new Integer (ResultSet.HOLD_CURSORS_OVER_COMMIT)};
66
67 public static void main (String args[])
68 {
69 try {
70
71 ij.getPropertyArg(args);
74 Connection conn = ij.startJBMS();
75
76 dropTable(conn);
77 createAndPopulateTable(conn);
78
79 conn.setAutoCommit(false);
81 testHoldability(conn);
82 testPreparedStatement(conn);
83 testCallableStatement(conn);
84 conn.rollback();
85 conn.setAutoCommit(true);
86 dropTable(conn);
87 conn.close();
88 } catch (Exception e) {
89 System.out.println("FAIL -- unexpected exception "+e);
90 JDBCDisplayUtil.ShowException(System.out, e);
91 e.printStackTrace();
92 }
93 }
94
95 private static void createAndPopulateTable(Connection conn) throws SQLException {
97 Statement stmt = conn.createStatement();
98
99 System.out.println("Creating table...");
100 stmt.executeUpdate( "CREATE TABLE T1 (c11 int, c12 int)" );
101 stmt.executeUpdate("INSERT INTO T1 VALUES(1,1)");
102 stmt.executeUpdate("INSERT INTO T1 VALUES(2,1)");
103 System.out.println("done creating table and inserting data.");
104
105 stmt.close();
106 }
107
108 private static void dropTable(Connection conn)
110 {
112 try {
113 Statement stmt = conn.createStatement();
114 stmt.executeUpdate( "DROP TABLE T1" );
115 stmt.close();
116 } catch (SQLException se) {} }
118
119 private static void testCallableStatement(Connection conn) throws Exception
121 {
122 CallableStatement cs;
123 ResultSet rs;
124
125 System.out.println("Start hold cursor for callable statements test");
126
127 Method sh = conn.getClass().getMethod("prepareCall", PREP_STMT_PARAM);
129 cs = (CallableStatement ) (sh.invoke(conn, PREP_STMT_ARG));
130 cs.setInt(1,1);
131 rs = cs.executeQuery();
132
133 System.out.println("do next() before commit");
134 rs.next();
135 System.out.println("look at first column's value: " + rs.getInt(1));
136 conn.commit();
137 System.out.println("After commit, look at first column's value: " + rs.getInt(1));
138 System.out.println("do next() after commit. Should be at the end of resultset");
139 rs.next();
140 System.out.println("one more next() here will give no more rows");
141 rs.next();
142 System.out.println("Holdable cursor after commit for callable statements test over");
143 rs.close();
144 }
145
146 private static void testHoldability(Connection conn) throws Exception
148 {
149 Statement s;
150 PreparedStatement ps;
151 ResultSet rs;
152
153 System.out.println("Start holdable cursor after commit test");
154 Method sh = conn.getClass().getMethod("createStatement", STMT_PARAM);
156 s = (Statement ) (sh.invoke(conn, STMT_ARG));
157
158 rs = s.executeQuery("select * from t1");
160 System.out.println("do next() before commit");
161 rs.next();
162 System.out.println("look at first column's value: " + rs.getInt(1));
163 conn.commit();
164 System.out.println("After commit, look at first column's value: " + rs.getInt(1));
165 System.out.println("do next() after commit. Should be at the end of resultset");
166 rs.next();
167 System.out.println("one more next() here will give no more rows");
168 rs.next();
169 System.out.println("Holdable cursor after commit test over");
170 rs.close();
171 }
172
173 private static void testPreparedStatement(Connection conn) throws Exception
175 {
176 PreparedStatement ps;
177 ResultSet rs;
178
179 System.out.println("Start hold cursor for prepared statements test");
180
181 Method sh = conn.getClass().getMethod("prepareStatement", PREP_STMT_PARAM);
183 ps = (PreparedStatement ) (sh.invoke(conn, PREP_STMT_ARG));
184
185 ps.setInt(1,1);
186 rs = ps.executeQuery();
187
188 System.out.println("do next() before commit");
189 rs.next();
190 System.out.println("look at first column's value: " + rs.getInt(1));
191 conn.commit();
192 System.out.println("After commit, look at first column's value: " + rs.getInt(1));
193 System.out.println("do next() after commit. Should be at the end of resultset");
194 rs.next();
195 System.out.println("one more next() here will give no more rows");
196 rs.next();
197 System.out.println("Holdable cursor after commit for prepared statements test over");
198 rs.close();
199
200 sh = conn.getClass().getMethod("prepareStatement", PREP_STMT_PARAM);
203 try {
204 ps = (PreparedStatement ) (sh.invoke(conn, PREP_STMT_ERROR_ARG));
205
206 ps.setInt(1,1);
207 rs = ps.executeQuery();
208 }
209 catch (SQLException se)
210 {
211 System.out.println("Expected Exception:" + se.getMessage());
212 }
213 catch (InvocationTargetException itex) {
214 Throwable e = itex.getTargetException();
215 if (e instanceof SQLException )
217 {
218 SQLException se = (SQLException )e;
219 System.out.println("Expected Exception:" + se.getMessage());
220 }
221 else
222 throw itex;
223 }
224 conn.commit();
226 }
227
228 }
229
230
231 |
Popular Tags |
Java API By Example, From Geeks To Geeks.