· 5 years ago · Jun 14, 2020, 01:10 AM
1import java.sql.*;
2
3public class Assignment2 {
4
5 // A connection to the database
6 Connection connection;
7
8 // Statement to run queries
9 Statement sql;
10
11 // Prepared Statement
12 PreparedStatement ps;
13
14 // Resultset for the query
15 ResultSet rs;
16
17 // public static void main(String[] args){
18 // Assignment2 a2 = new Assignment2();
19 // a2.connectDB("jdbc:postgresql://db:5432/sehmim", "sehmim", "214900633");
20
21 // // insertCountry TESTS
22 // System.out.println("--------METHOD insertCountry");
23 // System.out.println("TRUE -> " + a2.insertCountry(1, "Canada", 100, 200));
24 // System.out.println("FALSE -> " + a2.insertCountry(1 , "USA", 1000, 2000));
25 // System.out.println("TRUE -> " + a2.insertCountry(2 , "USA", 1000, 2000));
26 // System.out.println("TRUE -> " + a2.insertCountry(3 , "Mexico", 5000, 2000));
27 // System.out.println("FALSE -> " + a2.insertCountry(2 , "USA", 1000, 2000));
28
29 // System.out.println("--------METHOD getCountriesNExtTo");
30 // // getCountriesNExtTo OCEAn (run dataset.ddl to test)
31 // System.out.println("9 -> " +a2.getCountriesNextToOceanCount(1001));
32 // System.out.println("4 -> " +a2.getCountriesNextToOceanCount(1002));
33 // System.out.println("? -> " +a2.getCountriesNextToOceanCount(1003));
34
35 // System.out.println("--------METHOD getOceanInfo");
36 // // getOceanInfo
37 // System.out.println("1001:Atlantic:28344 -> " +a2.getOceanInfo(1001));
38 // System.out.println("1002:Pacific:36198 -> " +a2.getOceanInfo(1002));
39 // System.out.println("1003:Indian:25344 -> " +a2.getOceanInfo(1003));
40
41 // System.out.println("--------METHOD chgHDI");
42 // // chgHDI(int cid, int year, float newHDI)
43 // System.out.println("FALSE -> " + a2.chgHDI(6666, 46556, 1.123f));
44 // System.out.println("True -> " + a2.chgHDI(1, 2009, 1.01f));
45 // System.out.println("True -> " + a2.chgHDI(1, 2010, 5.41f));
46
47 // System.out.println("--------METHOD deleteNeighbour");
48 // // deleteNeighbour cid1 cid2
49 // System.out.println("True -> " + a2.deleteNeighbour(1, 2));
50 // System.out.println("True -> " + a2.deleteNeighbour(2, 3));
51 // System.out.println("FALSE -> " + a2.deleteNeighbour(3, 45));
52 // System.out.println("FALSE -> " + a2.deleteNeighbour(2, 8));
53
54 // System.out.println("--------METHOD listCountryLanguages");
55 // // listCountryLanguages(int cid)
56 // System.out.println(a2.listCountryLanguages(1));
57 // System.out.println(a2.listCountryLanguages(2));
58 // System.out.println(a2.listCountryLanguages(11));
59
60 // //
61
62 // System.out.println("--------METHOD updateDB");
63 // // updateDB
64 // System.out.println(a2.updateDB());
65
66
67 // }
68 //CONSTRUCTOR: Identifies the postgreSQL driver using Class.forName method.
69 Assignment2(){
70 try {
71 Class.forName("org.postgresql.Driver");
72 }catch (ClassNotFoundException e){
73 return;
74 }
75 }
76
77 //Using the input parameters, establish a connection to be used for this session. Returns true if connection is successful
78 public boolean connectDB(String URL, String username, String password){
79 try {
80 connection = DriverManager.getConnection(URL, username, password);
81
82 sql = connection.createStatement();
83 sql.execute("SET search_path TO A2");
84 sql.close();
85
86 } catch (SQLException e) {
87 return false;
88 }
89 return connection != null;
90 }
91
92 //Closes the connection. Returns true if closure was successful
93 public boolean disconnectDB(){
94 try {
95 connection.close();
96 }catch (SQLException e){
97 return false;
98 }
99 return true;
100 }
101
102 //Inserts a row into the country table. cid is the name of the country, name is the
103 //name of the country, height is the highest elevation point and population is the
104 //population of the newly inserted country. You have to check if the country with id
105 //cid exists. Returns true if the insertion was successful, false otherwise
106 public boolean insertCountry (int cid, String name, int height, int population) {
107 try {
108 sql = connection.createStatement();
109 sql.executeUpdate("INSERT INTO country VALUES (" +
110 + cid + ", '" + name + "', " + height + ", " + population + ")");
111
112 sql.close();
113
114 }catch (SQLException e){
115 return false;
116 }
117 return true;
118 }
119
120 //Returns the number of countries in table “oceanAccess” that are located next to
121 //the ocean with id oid. Returns -1 if an error occurs.
122 public int getCountriesNextToOceanCount(int oid) {
123 int result = -1;
124
125 try {
126 sql = connection.createStatement();
127 rs = sql.executeQuery("SELECT count(*) " +
128 "FROM oceanAccess " +
129 "WHERE oceanAccess.oid=" + oid);
130
131 while (rs.next())
132 result = rs.getInt(1);
133
134
135 rs.close();
136 sql.close();
137
138 return result;
139
140 }catch (SQLException e){
141 return -1;
142 }
143 }
144
145 //Returns a string with information of an ocean with id oid. The output should be
146 //formatted as “oid:oname:depth”. Returns an empty string “” if the ocean does not
147 //exist.
148 public String getOceanInfo(int oid){
149 String result = "";
150
151 try {
152 sql = connection.createStatement();
153 rs = sql.executeQuery("SELECT oid,oname,depth " +
154 "FROM ocean " +
155 "WHERE ocean.oid=" + oid);
156
157 while (rs.next())
158 result = rs.getInt(1)+ ":" + rs.getString(2) + ":" + rs.getInt(3);
159
160 rs.close();
161 sql.close();
162
163 }catch (SQLException e){
164 return result;
165 }
166 return result;
167 }
168
169 //Changes the HDI value of the country cid for the year year to the HDI value
170 //supplied (newHDI). Returns true if the change was successful, false otherwise.
171 public boolean chgHDI(int cid, int year, float newHDI){
172 try {
173 sql = connection.createStatement();
174 sql.executeUpdate("UPDATE hdi SET hdi_score=" + newHDI +
175 " WHERE cid=" + cid+ " AND year=" + year);
176
177 }catch (SQLException e){
178 return false;
179 }
180
181 float result = 0;
182
183 try {
184 sql = connection.createStatement();
185 rs = sql.executeQuery("SELECT hdi_score " +
186 "FROM hdi " +
187 "WHERE cid=" + cid + " AND year=" + year);
188
189 while (rs.next())
190 result = rs.getFloat("hdi_score");
191
192 rs.close();
193 sql.close();
194
195 return result == newHDI;
196
197 }catch (SQLException e){
198 return false;
199 }
200 }
201
202 //Deletes the neighboring relation between two countries. Returns true if the
203 //deletion was successful, false otherwise. You can assume that the neighboring
204 //relation to be deleted exists in the database. Remember that if c2 is a neighbor of
205 //c1, c1 is also a neighbour of c2.
206 public boolean deleteNeighbour(int c1id, int c2id){
207 int result = 0;
208 try {
209 sql = connection.createStatement();
210 rs = sql.executeQuery("SELECT neighbor " +
211 "FROM neighbour " +
212 "WHERE country=" + c1id + " AND neighbor=" + c2id);
213
214 while (rs.next())
215 result = rs.getInt("neighbor");
216
217 rs.close();
218 sql.close();
219
220 if(result == 0){
221 return false;
222 }
223
224 }catch (SQLException e){
225 return false;
226 }
227
228 try {
229 sql = connection.createStatement();
230 sql.executeUpdate("DELETE " +
231 "FROM neighbour " +
232 "WHERE (country=" + c1id + " AND neighbor= " + c2id + ") " +
233 "OR (country="+ c2id + " AND neighbor=" + c1id + ")");
234 }catch (SQLException e){
235 return false;
236 }
237
238 return true;
239 }
240
241 //Returns a string with all the languages that are spoken in the country with id cid.
242 //The list of languages should follow the contiguous format described below, and
243 //contain the following attributes in the order shown: (NOTE: before creating the
244 //string order your results by population).
245 //“l1id:l1lname:l1population\nl2id:l2lname:l2population\n... ”
246 //where:
247 //• lid is the id of the language.
248 //• lname is name of the language.
249 //• population is the number of people in a country that speak the language,
250 //note that you will need to compute this number, as it is not readily
251 //available in the database.
252 //Returns an empty string “” if the country does not exist.
253public String listCountryLanguages(int cid){
254 String result = "";
255 try {
256 sql = connection.createStatement();
257 rs = sql.executeQuery(
258 "SELECT L.lid, L.lname, (C.population*L.lpercentage)/100 AS planguage " +
259 "FROM language L JOIN country C ON C.cid = L.cid " +
260 "WHERE L.cid=" + cid +
261 " ORDER BY population DESC");
262
263 while (rs.next())
264 result += rs.getInt("lid") +
265 ":" + rs.getString(2) +
266 ":" + rs.getFloat("planguage") + "\n";
267
268 rs.close();
269 sql.close();
270
271 }catch (SQLException e){
272 return result;
273 }
274 return result;
275 }
276
277 //Decreases the height of the country with id cid. This decrease might happen due
278 //to the natural erosion (i.e. height - decrH). Returns true if the update was
279 //successful, false otherwise.
280 public boolean updateHeight(int cid, int decrH){
281 try {
282 int result = 0, result1 = 1;
283
284 sql = connection.createStatement();
285 rs = sql.executeQuery("SELECT height " +
286 "FROM country " +
287 "WHERE cid=" + cid);
288
289 while (rs.next())
290 result = rs.getInt("height");
291
292 sql.executeUpdate("UPDATE country " +
293 "SET height=(height - " + decrH + ") " +
294 "WHERE cid=" + cid);
295
296 rs = sql.executeQuery("SELECT (height + " + decrH + ") AS height " +
297 "FROM country " +
298 "WHERE cid=" + cid);
299 while (rs.next())
300 result1 = rs.getInt("height");
301
302 rs.close();
303 sql.close();
304
305 return result == result1;
306
307 }catch (SQLException e){
308 return false;
309 }
310 }
311
312 //Create a table containing all the countries which have a population over 100
313 //million. The name of the table should be mostPopulousCountries and the
314 //attributes should be:
315 //• cid INTEGER (country id)
316 //• cname VARCHAR(20) (country name)
317 //Returns true if the database was successfully updated, false otherwise. Store the
318 //results in ASC order according to the country id (cid).
319 public boolean updateDB(){
320 try {
321 sql = connection.createStatement();
322 sql.executeUpdate("CREATE TABLE mostPopulousCountries(" +
323 "cid INTEGER," +
324 "cname VARCHAR(20));");
325
326 sql.executeUpdate("INSERT INTO mostPopulousCountries (" +
327 "SELECT cid, cname " +
328 "FROM country " +
329 "WHERE population > 100000000) " +
330 "ORDER BY cid ASC;");
331
332 }catch (SQLException e) {
333 return false;
334 }
335 return true;
336 }
337
338}