|
1
21
22 package org.apache.derbyTesting.functionTests.tests.demo;
23
24 import org.apache.derby.tools.ij;
25 import toursdb.insertMaps;
26 import java.sql.DriverManager  ;
27 import java.sql.PreparedStatement ;
28 import java.sql.Statement ;
29 import java.sql.SQLException ;
30 import java.sql.Connection ;
31 import java.sql.ResultSet ;
32
33 public class checkToursDB {
34
35 public static void main(String args[]) {
36
37 String [] dbfiles = {"ToursDB_schema.sql","loadTables.sql"};
38 try {
39 System.setProperty("ij.database","jdbc:derby:toursDB;create=true");
40 for (int i = 0 ; i < dbfiles.length ; i++)
41 {
42 String [] ijArgs = {dbfiles[i]};
43 ij.main(ijArgs);
44 }
45 } catch (Exception e) {
46 e.printStackTrace();
47 }
48
49 PreparedStatement ps = null;
50 ResultSet rs = null;
51 Connection connCS = null;
52
53 try {
55 insertMaps.main(args);
56 } catch (SQLException se) {
57 se.printStackTrace();
58 } catch (Exception e) {
59 e.printStackTrace();
60 }
61
62 try {
64 ij.getPropertyArg(args);
65 connCS = ij.startJBMS();
66 } catch (SQLException se) {
67 se.printStackTrace();
68 } catch (Exception e) {
69 e.printStackTrace();
70 }
71
72 doSelect(connCS);
74
75 try {
77 ps = connCS.prepareStatement
78 ("select ECONOMY_SEATS from AIRLINES where AIRLINE = 'AA'");
79 rs = ps.executeQuery();
80 if (rs.next())
81 System.out.print("ECONOMY_SEATS is first: " + rs.getInt(1));
82 Statement stmt = connCS.createStatement();
83 stmt.execute("update AIRLINES set ECONOMY_SEATS=108 where AIRLINE = 'AA'");
84 rs = ps.executeQuery();
85 if (rs.next())
86 System.out.println(", ECONOMY_SEATS is then: " + rs.getString(1));
87
88 ps = connCS.prepareStatement ("select COUNTRY from COUNTRIES where COUNTRY_ISO_CODE = 'US'" );
89 rs = ps.executeQuery();
90 if (rs.next())
91 System.out.print("COUNTRY is first: " + rs.getString(1));
92 stmt = connCS.createStatement();
93 stmt.execute("update COUNTRIES set COUNTRY='United States of America' where COUNTRY_ISO_CODE = 'US'");
94 rs = ps.executeQuery();
95 if (rs.next())
96 System.out.println(", COUNTRY is then: " + rs.getString(1));
97
98 ps = connCS.prepareStatement ("select COUNTRY from CITIES where CITY_ID = 52" );
99 rs = ps.executeQuery();
100 if (rs.next())
101 System.out.print("COUNTRY is first: " + rs.getString(1));
102 stmt = connCS.createStatement();
103 stmt.execute("update CITIES set COUNTRY='United States of America' where COUNTRY='United States'");
104 rs = ps.executeQuery();
105 if (rs.next())
106 System.out.println(", COUNTRY is then: " + rs.getString(1));
107
108 ps = connCS.prepareStatement ("select ECONOMY_SEATS_TAKEN from FLIGHTAVAILABILITY where FLIGHT_ID = 'AA1134' and FLIGHT_DATE='2004-03-31'" );
109 rs = ps.executeQuery();
110 if (rs.next())
111 System.out.print("ECONOMY_SEATS_TAKEN is first: " + rs.getInt(1));
112 stmt = connCS.createStatement();
113 stmt.execute("update FLIGHTAVAILABILITY set ECONOMY_SEATS_TAKEN=20 where FLIGHT_ID = 'AA1134' and FLIGHT_DATE='2004-03-31'");
114 rs = ps.executeQuery();
115 if (rs.next())
116 System.out.println(", ECONOMY_SEATS_TAKEN is then: " + rs.getString(1));
117
118 ps = connCS.prepareStatement ("select AIRCRAFT from FLIGHTS where FLIGHT_ID = 'AA1183'" );
119 rs = ps.executeQuery();
120 if (rs.next())
121 System.out.print("AIRCRAFT is first: " + rs.getString(1));
122 stmt = connCS.createStatement();
123 stmt.execute("update FLIGHTS set AIRCRAFT='B777' where FLIGHT_ID = 'AA1134'");
124 rs = ps.executeQuery();
125 if (rs.next())
126 System.out.println(", AIRCRAFT is then: " + rs.getString(1));
127
128 ps = connCS.prepareStatement ("select REGION from MAPS where MAP_NAME = 'BART'" );
129 rs = ps.executeQuery();
130 if (rs.next())
131 System.out.print("REGION is first: " + rs.getString(1));
132 stmt = connCS.createStatement();
133 stmt.execute("update MAPS set REGION='San Francisco Bay Area' where MAP_NAME = 'BART'");
134 rs = ps.executeQuery();
135 if (rs.next())
136 System.out.println(", REGION is then: " + rs.getString(1));
137
138 stmt = connCS.createStatement();
140 ps = connCS.prepareStatement ("select STATUS from FLIGHTS_HISTORY where FLIGHT_ID = 'AA1134'" );
141 rs = ps.executeQuery();
142 if (rs.next())
143 System.out.print("STATUS is first: " + rs.getString(1));
144 stmt = connCS.createStatement();
145 stmt.execute("update FLIGHTS_HISTORY set STATUS='over' where FLIGHT_ID='AA1134'");
146 rs = ps.executeQuery();
147 if (rs.next())
148 System.out.println(", STATUS is then: " + rs.getString(1));
149
150 } catch (SQLException se) {
151 se.printStackTrace();
152 } catch (Exception e) {
153 e.printStackTrace();
154 }
155
156 try {
158 ps = null;
159
160 String tableName[] = {"AIRLINES","CITIES","COUNTRIES","FLIGHTAVAILABILITY","FLIGHTS","MAPS"};
161 for (int i = 0 ; i < 6; i++) {
162 Statement stmt = connCS.createStatement();
163 stmt.execute("delete from " + tableName[i]);
164 System.out.println("deleted all from table " + tableName[i]);
165 }
166 Statement stmt = connCS.createStatement();
169 rs = stmt.executeQuery("select STATUS from FLIGHTS_HISTORY where FLIGHT_ID IS NULL and STATUS <> 'over'");
170 if (rs.next())
172 System.out.println("STATUS is here: " + rs.getString(1));
173 stmt.execute("delete from FLIGHTS_HISTORY");
175 System.out.println("deleted all from table FLIGHTS_HISTORY");
176 } catch (SQLException se) {
177 se.printStackTrace();
178 } catch (Exception e) {
179 e.printStackTrace();
180 }
181
182 doSelect(connCS);
184
185 }
186
187 private static void doSelect(Connection connCS)
188 {
189 try {
191 PreparedStatement ps = null;
192
193 String tableName[] = {"AIRLINES","COUNTRIES","CITIES","FLIGHTAVAILABILITY","FLIGHTS","MAPS","FLIGHTS_HISTORY"};
194 for (int i = 0 ; i < 7; i++) {
195 ps = connCS.prepareStatement ("select count(*) from " + tableName[i]);
196 System.out.print("count for select * from table " + tableName[i]);
197 ResultSet rs = ps.executeQuery();
198 if (rs.next())
199 System.out.println(": " + rs.getInt(1));
200 else System.out.println(": 0");
201 }
202 } catch (SQLException se) {
203 se.printStackTrace();
204 } catch (Exception e) {
205 e.printStackTrace();
206 }
207 }
208
209 }
210 |
Popular Tags |
Java API By Example, From Geeks To Geeks.