001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.demo.checkToursDB
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.demo;
023:
024: import org.apache.derby.tools.ij;
025: import toursdb.insertMaps;
026: import java.sql.DriverManager;
027: import java.sql.PreparedStatement;
028: import java.sql.Statement;
029: import java.sql.SQLException;
030: import java.sql.Connection;
031: import java.sql.ResultSet;
032:
033: public class checkToursDB {
034:
035: public static void main(String args[]) {
036:
037: String[] dbfiles = { "ToursDB_schema.sql", "loadTables.sql" };
038: try {
039: System.setProperty("ij.database",
040: "jdbc:derby:toursDB;create=true");
041: for (int i = 0; i < dbfiles.length; i++) {
042: String[] ijArgs = { dbfiles[i] };
043: ij.main(ijArgs);
044: }
045: } catch (Exception e) {
046: e.printStackTrace();
047: }
048:
049: PreparedStatement ps = null;
050: ResultSet rs = null;
051: Connection connCS = null;
052:
053: // now populate the map table
054: try {
055: insertMaps.main(args);
056: } catch (SQLException se) {
057: se.printStackTrace();
058: } catch (Exception e) {
059: e.printStackTrace();
060: }
061:
062: // first get connection...
063: try {
064: ij.getPropertyArg(args);
065: connCS = ij.startJBMS();
066: } catch (SQLException se) {
067: se.printStackTrace();
068: } catch (Exception e) {
069: e.printStackTrace();
070: }
071:
072: // now ensure we can select from all the tables
073: doSelect(connCS);
074:
075: // we've inserted, selected, now update a row in each table
076: try {
077: ps = connCS
078: .prepareStatement("select ECONOMY_SEATS from AIRLINES where AIRLINE = 'AA'");
079: rs = ps.executeQuery();
080: if (rs.next())
081: System.out.print("ECONOMY_SEATS is first: "
082: + rs.getInt(1));
083: Statement stmt = connCS.createStatement();
084: stmt
085: .execute("update AIRLINES set ECONOMY_SEATS=108 where AIRLINE = 'AA'");
086: rs = ps.executeQuery();
087: if (rs.next())
088: System.out.println(", ECONOMY_SEATS is then: "
089: + rs.getString(1));
090:
091: ps = connCS
092: .prepareStatement("select COUNTRY from COUNTRIES where COUNTRY_ISO_CODE = 'US'");
093: rs = ps.executeQuery();
094: if (rs.next())
095: System.out
096: .print("COUNTRY is first: " + rs.getString(1));
097: stmt = connCS.createStatement();
098: stmt
099: .execute("update COUNTRIES set COUNTRY='United States of America' where COUNTRY_ISO_CODE = 'US'");
100: rs = ps.executeQuery();
101: if (rs.next())
102: System.out.println(", COUNTRY is then: "
103: + rs.getString(1));
104:
105: ps = connCS
106: .prepareStatement("select COUNTRY from CITIES where CITY_ID = 52");
107: rs = ps.executeQuery();
108: if (rs.next())
109: System.out
110: .print("COUNTRY is first: " + rs.getString(1));
111: stmt = connCS.createStatement();
112: stmt
113: .execute("update CITIES set COUNTRY='United States of America' where COUNTRY='United States'");
114: rs = ps.executeQuery();
115: if (rs.next())
116: System.out.println(", COUNTRY is then: "
117: + rs.getString(1));
118:
119: ps = connCS
120: .prepareStatement("select ECONOMY_SEATS_TAKEN from FLIGHTAVAILABILITY where FLIGHT_ID = 'AA1134' and FLIGHT_DATE='2004-03-31'");
121: rs = ps.executeQuery();
122: if (rs.next())
123: System.out.print("ECONOMY_SEATS_TAKEN is first: "
124: + rs.getInt(1));
125: stmt = connCS.createStatement();
126: stmt
127: .execute("update FLIGHTAVAILABILITY set ECONOMY_SEATS_TAKEN=20 where FLIGHT_ID = 'AA1134' and FLIGHT_DATE='2004-03-31'");
128: rs = ps.executeQuery();
129: if (rs.next())
130: System.out.println(", ECONOMY_SEATS_TAKEN is then: "
131: + rs.getString(1));
132:
133: ps = connCS
134: .prepareStatement("select AIRCRAFT from FLIGHTS where FLIGHT_ID = 'AA1183'");
135: rs = ps.executeQuery();
136: if (rs.next())
137: System.out.print("AIRCRAFT is first: "
138: + rs.getString(1));
139: stmt = connCS.createStatement();
140: stmt
141: .execute("update FLIGHTS set AIRCRAFT='B777' where FLIGHT_ID = 'AA1134'");
142: rs = ps.executeQuery();
143: if (rs.next())
144: System.out.println(", AIRCRAFT is then: "
145: + rs.getString(1));
146:
147: ps = connCS
148: .prepareStatement("select REGION from MAPS where MAP_NAME = 'BART'");
149: rs = ps.executeQuery();
150: if (rs.next())
151: System.out.print("REGION is first: " + rs.getString(1));
152: stmt = connCS.createStatement();
153: stmt
154: .execute("update MAPS set REGION='San Francisco Bay Area' where MAP_NAME = 'BART'");
155: rs = ps.executeQuery();
156: if (rs.next())
157: System.out.println(", REGION is then: "
158: + rs.getString(1));
159:
160: // Flight_history is now has 1 row, because of TRIG1
161: stmt = connCS.createStatement();
162: ps = connCS
163: .prepareStatement("select STATUS from FLIGHTS_HISTORY where FLIGHT_ID = 'AA1134'");
164: rs = ps.executeQuery();
165: if (rs.next())
166: System.out.print("STATUS is first: " + rs.getString(1));
167: stmt = connCS.createStatement();
168: stmt
169: .execute("update FLIGHTS_HISTORY set STATUS='over' where FLIGHT_ID='AA1134'");
170: rs = ps.executeQuery();
171: if (rs.next())
172: System.out.println(", STATUS is then: "
173: + rs.getString(1));
174:
175: } catch (SQLException se) {
176: se.printStackTrace();
177: } catch (Exception e) {
178: e.printStackTrace();
179: }
180:
181: // now delete....
182: try {
183: ps = null;
184:
185: String tableName[] = { "AIRLINES", "CITIES", "COUNTRIES",
186: "FLIGHTAVAILABILITY", "FLIGHTS", "MAPS" };
187: for (int i = 0; i < 6; i++) {
188: Statement stmt = connCS.createStatement();
189: stmt.execute("delete from " + tableName[i]);
190: System.out.println("deleted all from table "
191: + tableName[i]);
192: }
193: // now quickly checking FLIGHTS_HISTORY -
194: // should now have a 2nd row because of trigger2
195: Statement stmt = connCS.createStatement();
196: rs = stmt
197: .executeQuery("select STATUS from FLIGHTS_HISTORY where FLIGHT_ID IS NULL and STATUS <> 'over'");
198: // don't care if there are more than 1 rows...
199: if (rs.next())
200: System.out
201: .println("STATUS is here: " + rs.getString(1));
202: // now delete this one too
203: stmt.execute("delete from FLIGHTS_HISTORY");
204: System.out
205: .println("deleted all from table FLIGHTS_HISTORY");
206: } catch (SQLException se) {
207: se.printStackTrace();
208: } catch (Exception e) {
209: e.printStackTrace();
210: }
211:
212: //select again
213: doSelect(connCS);
214:
215: }
216:
217: private static void doSelect(Connection connCS) {
218: // now ensure we can select from all the tables
219: try {
220: PreparedStatement ps = null;
221:
222: String tableName[] = { "AIRLINES", "COUNTRIES", "CITIES",
223: "FLIGHTAVAILABILITY", "FLIGHTS", "MAPS",
224: "FLIGHTS_HISTORY" };
225: for (int i = 0; i < 7; i++) {
226: ps = connCS.prepareStatement("select count(*) from "
227: + tableName[i]);
228: System.out.print("count for select * from table "
229: + tableName[i]);
230: ResultSet rs = ps.executeQuery();
231: if (rs.next())
232: System.out.println(": " + rs.getInt(1));
233: else
234: System.out.println(": 0");
235: }
236: } catch (SQLException se) {
237: se.printStackTrace();
238: } catch (Exception e) {
239: e.printStackTrace();
240: }
241: }
242:
243: }
|