· 9 years ago · Nov 09, 2016, 08:32 AM
1import java.sql.*;
2import java.text.DateFormat;
3import java.text.DecimalFormat;
4import java.text.SimpleDateFormat;
5import java.util.Calendar;
6
7/**
8 * Created by Dominik Ryńko on 2016-11-06.
9 */
10class Database extends Cities
11{
12 /**
13 * Constants used for connection
14 */
15 private static final String DRIVER = "org.sqlite.JDBC";
16 private static final String DB_URL = "jdbc:sqlite:" + DATABASE;
17
18 /**
19 * Global variable used while connection to database
20 */
21 private static Statement stmt;
22 private static Connection conn;
23
24 /**
25 * Initialization basic staff
26 */
27 Database() {
28
29 try {
30 Class.forName(DRIVER);
31 } catch (ClassNotFoundException e) {
32 System.err.println(info.get(3));
33 e.printStackTrace();
34 }
35
36 try {
37 conn = DriverManager.getConnection(DB_URL);
38 stmt = conn.createStatement();
39 } catch (SQLException e) {
40 System.err.println(info.get(4));
41 e.printStackTrace();
42 }
43 }
44
45 /**
46 * Checks if there is table in database
47 * @param table
48 * @return
49 * @throws SQLException
50 */
51 boolean tableExist(String table) throws SQLException {
52
53 DatabaseMetaData dbm = conn.getMetaData();
54 ResultSet tables = dbm.getTables(null, null, table, null);
55
56 return tables.next();
57 }
58
59 /**
60 * Created table in database
61 */
62 boolean createDB()
63 {
64 String sql1, sql2, sql3;
65
66 sql1 = "CREATE TABLE IF NOT EXISTS cities(id INTEGER PRIMARY KEY AUTOINCREMENT, city VARCHAR(100), province VARCHAR(100), area FLOAT , Population INTEGER)";
67 sql2 = "CREATE TABLE IF NOT EXISTS population(id INTEGER PRIMARY KEY AUTOINCREMENT, number INTEGER, add_date datetime )";
68 sql3 = "CREATE TABLE IF NOT EXISTS area(id INTEGER PRIMARY KEY AUTOINCREMENT, area VARCHAR(200), add_date datetime )";
69
70
71 try {
72 stmt.execute(sql1);
73 stmt.execute(sql2);
74 stmt.execute(sql3);
75
76 return true;
77 } catch (SQLException e) {
78 e.printStackTrace();
79 return false;
80 }
81 }
82
83 /**
84 * Deletes all tables if they exist
85 * @throws SQLException
86 */
87 boolean dropDB() throws SQLException {
88
89 String cities = "DROP TABLE IF EXiSTS cities ";
90 String population = "DROP TABLE IF EXiSTS population";
91 String area = "DROP TABLE IF EXiSTS area";
92
93 stmt.executeUpdate(cities);
94 stmt.executeUpdate(population);
95 stmt.executeUpdate(area);
96
97 return true;
98 }
99
100 /**
101 * Insert cities to database
102 * @param data
103 * @return
104 * @throws SQLException
105 */
106 boolean InsertAll(String[][] data) throws SQLException {
107 String sql;
108
109 for (String[] line : data) {
110 sql = "INSERT INTO " + TABLENAME + " (city, province, area, Population) VALUES ('" + line[0] + "', '" + line[1] + "', '" + line[2] + "', '" + line[3] + "');";
111 stmt.execute(sql);
112 }
113
114 return true;
115 }
116
117 /**
118 * Returns number of records in table arg
119 * @return
120 * @throws SQLException
121 */
122 int countRecords(String table) throws SQLException {
123
124 ResultSet result = stmt.executeQuery("SELECT COUNT(*) FROM " + table);
125 result.next();
126 return result.getInt(1);
127 }
128
129 /**
130 * Returns number of all residents
131 * @return
132 * @throws SQLException
133 */
134 int getNumberOfResidents() throws SQLException {
135
136 int sum = 0;
137 ResultSet result = stmt.executeQuery("SELECT SUM(Population) as number FROM " + TABLENAME);
138
139 while (result.next()) {
140 int c = result.getInt(1);
141 sum = sum + c;
142 }
143
144 return sum;
145 }
146
147 /**
148 *
149 * @param area
150 * @throws SQLException
151 */
152 void saveAverageArea(String area) throws SQLException {
153
154 DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
155 Calendar cal = Calendar.getInstance();
156 String sql = "INSERT INTO area (area, add_date) VALUES ('" + area + "', '" + dateFormat.format(cal.getTime()) + "');";
157 stmt.execute(sql);
158 }
159
160 /**
161 *
162 * @param population
163 * @throws SQLException
164 */
165 void savePopulation(int population) throws SQLException {
166
167 DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
168 Calendar cal = Calendar.getInstance();
169 String sql = "INSERT INTO population (number, add_date) VALUES ('" + population + "', '" + dateFormat.format(cal.getTime()) + "');";
170 stmt.execute(sql);
171 }
172
173 /**
174 *
175 * @return
176 * @throws SQLException
177 */
178 String getAverageArea() throws SQLException {
179 double sum = 0;
180 ResultSet result = stmt.executeQuery("SELECT AVG(area) as number FROM " + TABLENAME);
181
182 while (result.next()) {
183 double c = result.getDouble(1);
184 sum = sum + c;
185 }
186
187 return new DecimalFormat("#.##").format(sum);
188 }
189
190 /**
191 * Displays all data from area table
192 */
193 void showAreaTable()
194 {
195 try {
196 ResultSet result = stmt.executeQuery("SELECT * FROM area");
197 int area;
198 String date;
199 System.out.printf("%15s %15s \n", "Obszar miasta", "Data odczytu");
200
201 while(result.next()) {
202 area = result.getInt("area");
203 date = result.getString("add_date");
204 System.out.printf("%10d km^2 %15s \n", area, date);
205 }
206 } catch (SQLException e) {
207 e.printStackTrace();
208 }
209 }
210
211
212 /**
213 * Displays all data from population table
214 */
215 void showPopulationTable()
216 {
217 try {
218 ResultSet result = stmt.executeQuery("SELECT * FROM population");
219 int number;
220 String date;
221 System.out.printf("%15s %15s \n", "Liczba ludności", "Data odczytu");
222
223 while(result.next()) {
224 number = result.getInt("number");
225 date = result.getString("add_date");
226 System.out.printf("%15d %15s \n", number, date);
227 }
228 } catch (SQLException e) {
229 e.printStackTrace();
230 }
231 }
232}