001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: /*
032: * TestSubQueriesInPreparedStatements.java
033: *
034: * Created on July 9, 2003, 4:03 PM
035: */
036: package org.hsqldb.test;
037:
038: import java.sql.Connection;
039: import java.sql.Driver;
040: import java.sql.DriverManager;
041: import java.sql.PreparedStatement;
042: import java.sql.ResultSet;
043: import java.sql.Statement;
044:
045: /**
046: *
047: * @author boucherb@users
048: */
049: public class TestSubQueriesInPreparedStatements {
050:
051: public static void main(String[] args) throws Exception {
052: test();
053: }
054:
055: public static void test() throws Exception {
056:
057: Connection conn;
058: Statement stmnt;
059: PreparedStatement pstmnt;
060: Driver driver;
061:
062: driver = (Driver) Class.forName("org.hsqldb.jdbcDriver")
063: .newInstance();
064:
065: DriverManager.registerDriver(driver);
066:
067: conn = DriverManager.getConnection("jdbc:hsqldb:mem:test",
068: "sa", "");
069: stmnt = conn.createStatement();
070: pstmnt = conn.prepareStatement("drop table t if exists");
071:
072: boolean result = pstmnt.execute();
073:
074: pstmnt = conn.prepareStatement("create table t(i decimal)");
075:
076: int updatecount = pstmnt.executeUpdate();
077:
078: pstmnt = conn.prepareStatement("insert into t values(?)");
079:
080: for (int i = 0; i < 100; i++) {
081: pstmnt.setInt(1, i);
082: pstmnt.executeUpdate();
083: }
084:
085: pstmnt = conn
086: .prepareStatement("select * from (select * from t where i < ?)");
087:
088: System.out.println("Expecting: 0..3");
089: pstmnt.setInt(1, 4);
090:
091: ResultSet rs = pstmnt.executeQuery();
092:
093: while (rs.next()) {
094: System.out.println(rs.getInt(1));
095: }
096:
097: System.out.println("Expecting: 0..4");
098: pstmnt.setInt(1, 5);
099:
100: rs = pstmnt.executeQuery();
101:
102: while (rs.next()) {
103: System.out.println(rs.getInt(1));
104: }
105:
106: pstmnt = conn
107: .prepareStatement("select sum(i) from (select i from t where i between ? and ?)");
108:
109: System.out.println("Expecting: 9");
110: pstmnt.setInt(1, 4);
111: pstmnt.setInt(2, 5);
112:
113: rs = pstmnt.executeQuery();
114:
115: while (rs.next()) {
116: System.out.println(rs.getInt(1));
117: }
118:
119: System.out.println("Expecting: 15");
120: pstmnt.setInt(2, 6);
121:
122: rs = pstmnt.executeQuery();
123:
124: while (rs.next()) {
125: System.out.println(rs.getInt(1));
126: }
127:
128: pstmnt = conn
129: .prepareStatement("select * from (select i as c1 from t where i < ?) a, (select i as c2 from t where i < ?) b");
130:
131: System.out.println("Expecting: (0,0)");
132: pstmnt.setInt(1, 1);
133: pstmnt.setInt(2, 1);
134:
135: rs = pstmnt.executeQuery();
136:
137: while (rs.next()) {
138: System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2)
139: + ")");
140: }
141:
142: System.out.println("Expecting: ((0,0), (0,1), (1,0), (1,1)");
143: pstmnt.setInt(1, 2);
144: pstmnt.setInt(2, 2);
145:
146: rs = pstmnt.executeQuery();
147:
148: while (rs.next()) {
149: System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2)
150: + ")");
151: }
152:
153: System.out.println("Expecting: ((0,0) .. (3,3)");
154: pstmnt.setInt(1, 4);
155: pstmnt.setInt(2, 4);
156:
157: rs = pstmnt.executeQuery();
158:
159: while (rs.next()) {
160: System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2)
161: + ")");
162: }
163: }
164: }
|