· 5 years ago · Jun 13, 2020, 07:46 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 //CONSTRUCTOR: Identifies the postgreSQL driver using Class.forName method.
18 Assignment2(){
19 try {
20 Class.forName("org.postgresql.Driver");
21 }catch (ClassNotFoundException e){
22 return;
23 }
24 }
25
26 //Using the input parameters, establish a connection to be used for this session. Returns true if connection is successful
27 public boolean connectDB(String URL, String username, String password){
28 try {
29 connection = DriverManager.getConnection(URL, username, password);
30
31 sql = connection.createStatement();
32 sql.execute("SET search_path TO A2");
33 sql.close();
34
35 } catch (SQLException e) {
36 return false;
37 }
38 return connection != null;
39 }
40
41 //Closes the connection. Returns true if closure was successful
42 public boolean disconnectDB(){
43 try {
44 connection.close();
45 }catch (SQLException e){
46 return false;
47 }
48 return true;
49 }
50
51 //Inserts a row into the country table. cid is the name of the country, name is the
52 //name of the country, height is the highest elevation point and population is the
53 //population of the newly inserted country. You have to check if the country with id
54 //cid exists. Returns true if the insertion was successful, false otherwise
55 public boolean insertCountry (int cid, String name, int height, int population) {
56 try {
57 sql = connection.createStatement();
58 sql.executeUpdate("INSERT INTO country VALUES (" +
59 + cid + ", '" + name + "', " + height + ", " + population + ")");
60
61 sql.close();
62
63 }catch (SQLException e){
64 return false;
65 }
66 return true;
67 }
68
69 //Returns the number of countries in table “oceanAccess” that are located next to
70 //the ocean with id oid. Returns -1 if an error occurs.
71 public int getCountriesNextToOceanCount(int oid) {
72 int result = -1;
73
74 try {
75 sql = connection.createStatement();
76 rs = sql.executeQuery("SELECT count(*) " +
77 "FROM oceanAccess " +
78 "WHERE oceanAccess.oid=" + oid);
79
80 while (rs.next())
81 result = rs.getInt(1);
82
83
84 rs.close();
85 sql.close();
86
87 return result;
88
89 }catch (SQLException e){
90 return -1;
91 }
92 }
93
94 //Returns a string with information of an ocean with id oid. The output should be
95 //formatted as “oid:oname:depth”. Returns an empty string “” if the ocean does not
96 //exist.
97 public String getOceanInfo(int oid){
98 String result = "";
99
100 try {
101 sql = connection.createStatement();
102 rs = sql.executeQuery("SELECT oid,oname,depth " +
103 "FROM ocean " +
104 "WHERE ocean.oid=" + oid);
105
106 while (rs.next())
107 result = rs.getInt(1)+ ":" + rs.getString(2) + ":" + rs.getInt(3);
108
109 rs.close();
110 sql.close();
111
112 }catch (SQLException e){
113 return result;
114 }
115 return result;
116 }
117
118 //Changes the HDI value of the country cid for the year year to the HDI value
119 //supplied (newHDI). Returns true if the change was successful, false otherwise.
120 public boolean chgHDI(int cid, int year, float newHDI){
121 try {
122 sql = connection.createStatement();
123 sql.executeUpdate("UPDATE hdi SET hdi_score=" + newHDI +
124 " WHERE cid=" + cid+ " AND year=" + year);
125
126 }catch (SQLException e){
127 return false;
128 }
129
130 float result = 0;
131
132 try {
133 sql = connection.createStatement();
134 rs = sql.executeQuery("SELECT hdi_score " +
135 "FROM hdi " +
136 "WHERE cid=" + cid + " AND year=" + year);
137
138 while (rs.next())
139 result = rs.getFloat("hdi_score");
140
141 rs.close();
142 sql.close();
143
144 return result == newHDI;
145
146 }catch (SQLException e){
147 return false;
148 }
149 }
150
151 //Deletes the neighboring relation between two countries. Returns true if the
152 //deletion was successful, false otherwise. You can assume that the neighboring
153 //relation to be deleted exists in the database. Remember that if c2 is a neighbor of
154 //c1, c1 is also a neighbour of c2.
155 public boolean deleteNeighbour(int c1id, int c2id){
156 try {
157 sql = connection.createStatement();
158 sql.executeUpdate("DELETE " +
159 "FROM neighbour " +
160 "WHERE (country=" + c1id + " AND neighbor= " + c2id + ") " +
161 "OR (country="+ c2id + " AND neighbor=" + c1id + ")");
162
163 }catch (SQLException e){
164 return false;
165 }
166
167 return true;
168 }
169
170 //Returns a string with all the languages that are spoken in the country with id cid.
171 //The list of languages should follow the contiguous format described below, and
172 //contain the following attributes in the order shown: (NOTE: before creating the
173 //string order your results by population).
174 //“l1id:l1lname:l1population\nl2id:l2lname:l2population\n... ”
175 //where:
176 //• lid is the id of the language.
177 //• lname is name of the language.
178 //• population is the number of people in a country that speak the language,
179 //note that you will need to compute this number, as it is not readily
180 //available in the database.
181 //Returns an empty string “” if the country does not exist.
182 public String listCountryLanguages(int cid){
183 String result = "";
184 try {
185 sql = connection.createStatement();
186 rs = sql.executeQuery("SELECT C.cid, C.cname, C.population" +
187 "FROM country C JOIN language L ON C.cid = L.cid" +
188 "WHERE L.cid=" + cid +
189 "GROUP BY C.cid, C.cname, C.population" +
190 "ORDER BY population DESC");
191
192 while (rs.next())
193 result += rs.getInt("lid") +
194 ":" + rs.getString(2) +
195 ":" + rs.getFloat("poplution") + "\n";
196
197 rs.close();
198 sql.close();
199
200 }catch (SQLException e){
201 return result;
202 }
203 return result;
204 }
205
206 //Decreases the height of the country with id cid. This decrease might happen due
207 //to the natural erosion (i.e. height - decrH). Returns true if the update was
208 //successful, false otherwise.
209 public boolean updateHeight(int cid, int decrH){
210 try {
211 int result = 0, result1 = 1;
212
213 sql = connection.createStatement();
214 rs = sql.executeQuery("SELECT height " +
215 "FROM country " +
216 "WHERE cid=" + cid);
217
218 while (rs.next())
219 result = rs.getInt("height");
220
221 sql.executeUpdate("UPDATE country " +
222 "SET height=(height - " + decrH + ") " +
223 "WHERE cid=" + cid);
224
225 rs = sql.executeQuery("SELECT (height + " + decrH + ") AS height " +
226 "FROM country " +
227 "WHERE cid=" + cid);
228 while (rs.next())
229 result1 = rs.getInt("height");
230
231 rs.close();
232 sql.close();
233
234 return result == result1;
235
236 }catch (SQLException e){
237 return false;
238 }
239 }
240
241 //Create a table containing all the countries which have a population over 100
242 //million. The name of the table should be mostPopulousCountries and the
243 //attributes should be:
244 //• cid INTEGER (country id)
245 //• cname VARCHAR(20) (country name)
246 //Returns true if the database was successfully updated, false otherwise. Store the
247 //results in ASC order according to the country id (cid).
248 public boolean updateDB(){
249 try {
250 sql = connection.createStatement();
251 sql.executeUpdate("CREATE TABLE mostPopulousCountries(" +
252 "cid INTEGER," +
253 "cname VARCHAR(20));");
254
255 sql.executeUpdate("INSERT INTO mostPopulousCountries (" +
256 "SELECT cid, cname " +
257 "FROM country " +
258 "WHERE population > 100000000) " +
259 "ORDER BY cid ASC;");
260
261 }catch (SQLException e) {
262 return false;
263 }
264 return true;
265 }
266
267}