· 8 years ago · Mar 20, 2018, 02:32 PM
1import java.io.*;
2import java.sql.*;
3import java.util.Arrays;
4import java.util.List;
5
6import static java.lang.Integer.parseInt;
7
8public class DatabaseHandler {
9 static Connection conn = null;
10 static Statement stmt;
11
12 static void connect() throws SQLException {
13 String url="URL";
14
15 //Probeer een database connectie te maken
16 try {
17 conn = DriverManager.getConnection( url, "root", "fietsbel" );
18 System.out.println("database connection success.");
19 } catch (SQLException e) {
20 System.out.println("database connection failed: "+e);
21 }
22 stmt = conn.createStatement();
23 }
24 static void createTables() throws SQLException {
25 //Reset database
26 String sql = "DROP TABLE IF EXISTS " +
27 "ratings," +
28 "locations," +
29 "soundtracks," +
30 "genres," +
31 "movies" +
32 ";";
33 stmt.executeUpdate(sql);
34 System.out.println("database rebuild started.\nDO NOT STOP THE PARSER AFTER THIS MESSAGE!\ndeleted all tables.");
35
36 sql = "CREATE TABLE IF NOT EXISTS movies (" +
37 "id INT AUTO_INCREMENT KEY,"+
38 "INDEX (id),"+
39 "movietitle VARCHAR(999)," +
40 "INDEX (movietitle),"+
41 "startyear VARCHAR(999)," +
42 "endyear VARCHAR(999)," +
43 "isSerie INT(1)" +
44 ");";
45 stmt.executeUpdate(sql);
46 System.out.println("created new movies table.");
47
48 sql = "CREATE TABLE IF NOT EXISTS locations (" +
49 "id INT AUTO_INCREMENT KEY,"+
50 "INDEX (id),"+
51 "movie_id INT REFERENCES movies(id)," +
52 "fulllocation VARCHAR(999)," +
53 "country VARCHAR(999)" +
54 ");";
55 stmt.executeUpdate(sql);
56 System.out.println("created new locations table.");
57
58 sql = "CREATE TABLE IF NOT EXISTS genres (" +
59 "id INT AUTO_INCREMENT KEY,"+
60 "INDEX (id),"+
61 "movie_id INT REFERENCES movies(id)," +
62 "genre VARCHAR(999)" +
63 ");";
64 stmt.executeUpdate(sql);
65 System.out.println("created new genres table.");
66
67 sql = "CREATE TABLE IF NOT EXISTS soundtracks (" +
68 "id INT AUTO_INCREMENT KEY,"+
69 "INDEX (id),"+
70 "movie_id INT REFERENCES movies(id)," +
71 "soundtrack VARCHAR(999)" +
72 ");";
73 stmt.executeUpdate(sql);
74 System.out.println("created new soundtracks table.");
75
76 sql = "CREATE TABLE IF NOT EXISTS ratings (" +
77 "id INT AUTO_INCREMENT KEY,"+
78 "INDEX (id),"+
79 "movie_id INT REFERENCES movies(id)," +
80 "votes INT(99)," +
81 "rating DOUBLE" +
82 ");";
83 stmt.executeUpdate(sql);
84 System.out.println("created new ratings table.");
85
86 List<String> addToDatabase = Arrays.asList("movies","locations","ratings","soundtracks","genres");
87 writeToDB(addToDatabase);
88 }
89
90 static void writeToDB(List<String> csvFiles) throws SQLException {
91
92 //Ga door elke csvFile
93 for (int i=0; i<csvFiles.size(); i++){
94 System.out.println("building "+ csvFiles.get(i)+" query...");
95
96 String csvFile = "Parser/src/main/resources/output/"+ csvFiles.get(i) + ".csv";
97 BufferedReader br = null;
98 String line = "";
99 String add = "";
100 StringBuilder query = new StringBuilder("INSERT INTO " + csvFiles.get(i) + " VALUES ");
101
102 //Nodig voor movies
103 String title = "";
104 String year1 = "";
105 String year2 = "";
106
107 //Nodig voor ratings
108 int count = 1;
109 int votes = 0;
110 double rating = 0.0;
111 int movie_id = 0;
112
113 //Skip alle "top" ratings/genres
114 Boolean skipped = false;
115
116 try {
117 br = new BufferedReader(new FileReader(csvFile));
118
119 while ((line = br.readLine()) != null) {
120 //Komma seperate
121 String[] current = line.split(",&");
122
123 //movies file
124 if(csvFiles.get(i).equals("movies") && !clean(current[0]).equals("")){
125 if(current[0].equals(title)){
126 try{
127 //Probeer als het meerdere met dezelfde naam zijn, de kleinste startjaar als jaar 1 te zetten.
128 if(parseInt(clean(current[1]))<parseInt(year1)){
129 year1 = clean(current[1]);
130 }
131 if(parseInt(clean(current[2]))>parseInt(year2)){
132 year2 = clean(current[2]);
133 }
134 }
135 catch(Exception e){
136 //System.out.println(clean(current[1]) + " is not an integer.");
137 }
138 }
139 else{
140 //Als de titel start met " is het een serie.
141 if(title.startsWith("\"")){
142 //Zet hem in de database als serie
143 query.append("\""+clean(year1)+"\",\""+clean(year2)+"\","+1+"),");
144 }
145 //Anders check je of het de eerste film is, zo niet, zet hem er in als film.
146 else if(!title.equals("")){
147 query.append("\""+clean(year1)+"\",\""+clean(year2)+"\","+0+"),");
148 }
149 query.append("(NULL,\""+clean(current[0])+"\",");
150 title = current[0];
151 year1 = clean(current[1]);
152 year2 = clean(current[2]);
153 }
154 }
155
156 //Ratings file
157 else if(csvFiles.get(i).equals("ratings")){
158 if (!skipped){
159 if(line.equals("25656,&1.4,&Kod Adi K.O.Z.")){
160 skipped = true;
161 }
162 }
163 else if(skipped){
164 //Als de titel hetzelfde is als de vorige
165 if(current[2].equals(title)){
166 count++;
167 votes = votes+parseInt(current[0]);
168 rating = rating+Double.parseDouble(current[1]);
169 }
170 //Anders plaats je de vorige in de database en maak je een nieuwe set aan
171 else{
172 if (!title.equals("")){
173 Double averageRating = round((rating/count),1);
174 add = "(NULL,"+movie_id+","+votes+","+averageRating+"),";
175 query.append(add);
176 if(movie_id % 2000 == 0){
177 Double percentage = round((((movie_id*1.0)/1324000)*100),2);
178 System.out.println(percentage +"% done with ratings.csv");
179 }
180 }
181
182 //Reset gegevens
183 count=1;
184 String sql = "SELECT id FROM movies WHERE movietitle = \""+clean(current[2])+"\"";
185 ResultSet rs = stmt.executeQuery(sql);
186 if(rs.next()) {
187 movie_id = rs.getInt("id");
188 }
189 title = current[2];
190 votes = parseInt(current[0]);
191 rating = Double.parseDouble(current[1]);
192 }
193 }
194 }
195
196 //genres file
197 else if(csvFiles.get(i).equals("genres")){
198 if (!skipped){
199 if(line.equals("Internet Movie Database Ltd,&IMDb,&. While every effort has been")){
200 skipped = true;
201 }
202 }
203 else if(skipped){
204 //Reset gegevens
205 String sql = "SELECT id FROM movies WHERE movietitle = \""+clean(current[0])+"\"";
206 ResultSet rs = stmt.executeQuery(sql);
207 if(rs.next()) {
208 movie_id = rs.getInt("id");
209 }
210
211 add = "(NULL,"+movie_id+",\""+clean(current[2])+"\"),";
212 if (query.indexOf(add)==-1){
213 query.append(add);
214 if(movie_id % 2000 == 0){
215 Double percentage = round((((movie_id*1.0)/1324000)*100),2);
216 System.out.println(percentage +"% done with genres.csv");
217 }
218 }
219 }
220 }
221 //locations file
222 if(csvFiles.get(i).equals("locations")){
223 //Reset gegevens
224 String sql = "SELECT id FROM movies WHERE movietitle = \""+clean(current[0])+"\"";
225 ResultSet rs = stmt.executeQuery(sql);
226 if(rs.next()) {
227 movie_id = rs.getInt("id");
228 }
229
230 String cleanedLocation = clean(current[2].replaceAll("\\(.*?\\)",""));
231 String country = "";
232 String[] bits = cleanedLocation.split(",");
233 if (bits.length>1){
234 country=bits[bits.length-1];
235 }
236 else if(bits.length==0){
237 System.out.println("Empty country found somehow");
238 }
239 else {
240 country = bits[0];
241 }
242 add = "(NULL,"+movie_id+",\""+cleanedLocation+"\",\""+clean(country)+"\"),";
243 if (query.indexOf(add)==-1){
244 query.append(add);
245 if(movie_id % 2001 == 0){
246 Double percentage = round((((movie_id*1.0)/1324000)*100),2);
247 System.out.println(percentage +"% done with locations.csv");
248 }
249 }
250
251
252// String sql = "SELECT id FROM movies WHERE movietitle = \""+clean(current[0])+"\"";
253// ResultSet rs = stmt.executeQuery(sql);
254// if(rs.next()) {
255// movie_id = rs.getInt("id");
256// }
257//
258// String cleanedLocation = clean(current[2].replaceAll("\\(.*?\\)",""));
259// String country = "";
260// String[] bits = cleanedLocation.split(",");
261// if (bits.length>1){
262// country=bits[bits.length-1];
263// }
264// else if(bits.length==0){
265// System.out.println("Empty country found somehow");
266// }
267// else {
268// country = bits[0];
269// }
270// add = "(NULL,"+movie_id+",\""+cleanedLocation+"\",\""+clean(country)+"\"),";
271// //Als hij nog niet in de lijst staat
272// if(query.indexOf(add)==-1){
273// //Voeg hem toe
274// query.append(add);
275// System.out.println(add);
276// if(movie_id % 2000 == 0){
277// System.out.println("Now at movie ID: "+movie_id);
278// Double percentage = round((((movie_id*1.0)/1324000)*100),2);
279// System.out.println(percentage +"% done with locations.csv");
280// }
281// }
282 }
283 //soundtracks file
284 else if(csvFiles.get(i).equals("soundtracks")){
285 //Reset gegevens
286 String sql = "SELECT id FROM movies WHERE movietitle = \""+clean(current[0])+"\"";
287 ResultSet rs = stmt.executeQuery(sql);
288 if(rs.next()) {
289 movie_id = rs.getInt("id");
290 }
291
292 add = "(NULL,\""+movie_id+"\",\""+clean(current[2])+"\"),";
293 if (query.indexOf(add)==-1){
294 query.append(add);
295 if(movie_id % 2000 == 0){
296 Double percentage = round((((movie_id*1.0)/1324000)*100),2);
297 System.out.println(percentage +"% done with soundtracks.csv");
298 }
299 }
300
301// for(int c=2; c<10000; c++){
302// try{
303// if (c==2){
304// songs.append(clean(current[c]));
305// }
306//
307// else if(current[c]!=null){
308// if (songs.indexOf(clean(current[c]))==-1){
309// songs.append("," + clean(current[c]));
310// }
311// }
312// }
313// catch (ArrayIndexOutOfBoundsException e){
314// break;
315// }
316// if(c==9999){System.out.println("Limit reached");}
317// }
318// query.append(songs.toString()+"\"),");
319 }
320 }
321
322 } catch (IOException e) {
323 e.printStackTrace();
324 } finally {
325 //Voeg de laatste data toe voor movies
326 if(csvFiles.get(i).equals("movies")){
327 query.append("\""+clean(year1)+"\",\""+clean(year2)+"\","+0+"),");
328 }
329 if (br != null) {
330 try {
331 br.close();
332 } catch (IOException e) {
333 e.printStackTrace();
334 }
335 }
336 }
337
338 query.deleteCharAt(query.length()-1);
339 query.append(";");
340 System.out.println(csvFiles.get(i)+" query was build.\nexporting to database...");
341
342 stmt.executeUpdate(query.toString());
343 System.out.println(csvFiles.get(i)+" exported to database.");
344 }
345
346 conn.close();
347 stmt.close();
348 System.out.println("database build finished and all connections closed.");
349 }
350
351 static String clean(String input){
352 return input.replaceAll("\\\\","").replaceAll("\\{", "").replaceAll("javascript:void;", "").replaceAll("}", "").replaceAll("\\)", "").replaceAll("\\(", "").replaceAll("\"", "\\\\\"").replaceAll("\'","").replaceAll(",","\\,").trim();
353 }
354 private static double round (double value, int precision) {
355 int scale = (int) Math.pow(10, precision);
356 return (double) Math.round(value * scale) / scale;
357 }
358}