· 6 years ago · Nov 18, 2019, 05:36 PM
1package no.ntnu.imt3281.ludo.server;
2
3import org.json.JSONArray;
4import org.json.JSONObject;
5
6import java.io.BufferedReader;
7import java.io.IOException;
8import java.io.InputStreamReader;
9import java.net.URL;
10import java.net.URLConnection;
11import java.sql.*;
12import java.util.Arrays;
13import java.util.LinkedList;
14import java.util.List;
15import java.util.concurrent.ExecutorService;
16import java.util.concurrent.Executors;
17import java.util.concurrent.LinkedBlockingQueue;
18import java.util.concurrent.TimeUnit;
19
20/**
21 * Utgangspunkt for swapi-import oppgaven
22 */
23public class Database {
24 LinkedBlockingQueue<String> toRead = new LinkedBlockingQueue<>();
25 LinkedList<String> found = new LinkedList<>();
26 final static String[] categories = {"residents", "films", "species", "vehicles", "starships", "characters", "pilots", "planets"};
27 final static String[] types = {"people", "planets", "films", "species", "vehicles", "starships"};
28 List<String> typesList = Arrays.asList(types);
29 final static int[] typeCount = new int[types.length];
30 Connection con = null;
31 final int MAXTHREADS = 5;
32
33 Database() {
34 String dbURL = "jdbc:derby:./swapiDB";
35 try { // Attempt to connect to DB
36 con = DriverManager.getConnection(dbURL);
37 } catch (SQLException e) { // if it failed
38 if (e.getMessage().equals("Database './swapiDB' not found.")) { // Is the database missing
39 try { // Attempt to create the DB
40 con = DriverManager.getConnection(dbURL + ";create=true");
41 createTable();
42 } catch (SQLException e1) { // If creation failed, exit
43 System.err.println("Kunne ikke opprette databasen");
44 System.exit(1);
45 }
46 } else { // Database exists, but could not connect. Exit.
47 System.err.println("Kunne ikke koble til databasen");
48 System.exit(1);
49 }
50 }
51
52 toRead.offer("https://swapi.co/api/films/1/"); // Add starting point
53 found.offer("https://swapi.co/api/films/1/");
54 ExecutorService executor = Executors.newCachedThreadPool();
55 System.out.println("Starting first reader thread");
56 executor.execute(new Downloader()); // Start one thread, read the first file
57 while (toRead.size()<MAXTHREADS) { // Wait for first thread to read the first file so we have more items to read
58 try {
59 Thread.currentThread().sleep(10);
60 } catch (InterruptedException e) {
61 e.printStackTrace();
62 }
63 }
64 System.out.println("\nFirst item read, starting other reader threads");
65 for (int i=0; i<MAXTHREADS; i++) {
66 executor.execute(new Downloader()); // We now have more items, start more threads
67 }
68
69 executor.shutdown();
70 try {
71 executor.awaitTermination(10, TimeUnit.MINUTES); // Wait for threads to finish
72 } catch (InterruptedException e) {
73 e.printStackTrace();
74 }
75 try {
76 con.close();
77 } catch (SQLException e) {
78 e.printStackTrace();
79 }
80 System.out.println("\n"); // Create report
81 for (int i=0; i<types.length; i++) {
82 System.out.printf("Fant %d elementer i kategorien '%s'.\n", typeCount[i], types[i]);
83 }
84 }
85
86 class Downloader implements Runnable {
87 public void run() {
88 PreparedStatement stmt = null;
89 try {
90 stmt = con.prepareStatement("INSERT INTO swapi (id, type, nr, json) VALUES (?, ?, ?, ?)");
91 } catch (SQLException e) {
92 e.printStackTrace();
93 }
94
95 do {
96 try {
97 String next = toRead.take();
98 System.out.print(".");
99 String jsonString = getURLContent(next);
100 JSONObject json = new JSONObject(jsonString);
101 JSONArray lists[] = new JSONArray[categories.length];
102 for (int i = 0; i < categories.length; i++) {
103 lists[i] = json.has(categories[i]) ? json.getJSONArray(categories[i]) : null;
104 }
105
106 addToDB(stmt, next, jsonString);
107
108 findNewItems(lists);
109 } catch (IOException e) {
110 e.printStackTrace();
111 } catch (InterruptedException e) {
112 e.printStackTrace();
113 }
114 } while (!toRead.isEmpty());
115 }
116
117 private String getURLContent(String next) throws IOException {
118 URL url = new URL(next); // Get information about item
119 URLConnection connection = url.openConnection(); // Need a bit extra
120 // Must set user-agent, the java default user agent is denied
121 connection.setRequestProperty("User-Agent", "curl/7.8 (i386-redhat-linux-gnu) libcurl 7.8 (OpenSSL 0.9.6b) (ipv6 enabled)");
122 // Must set accept to application/json, if not html is returned
123 connection.setRequestProperty("Accept", "application/json");
124 connection.connect();
125 BufferedReader br = new BufferedReader(new InputStreamReader(connection.getInputStream()));
126 return br.readLine();
127 }
128
129 /**
130 * Adds a new item to the database, expects a prepared statement,
131 * the url from where the item was retrieved and a string with the JSON
132 * content of the item as parameters.
133 *
134 * Extracts the number and type of resource from the url and stores that information
135 * with the actual JSON content in the database.
136 *
137 * @param stmt a prepared statement used to store information in the database
138 * @param url the location from witch the content was retrieved
139 * @param jsonString the actual JSON content for the item
140 */
141 private void addToDB(PreparedStatement stmt, String url, String jsonString) {
142 String type = url.substring(21);
143 String nr = type.substring(type.indexOf("/") + 1, type.length() - 1);
144 type = type.substring(0, type.indexOf("/"));
145
146 try {
147 stmt.setString(1, url);
148 stmt.setString(2, type);
149 stmt.setInt(3, Integer.parseInt(nr));
150 stmt.setString(4, jsonString);
151 stmt.execute();
152 typeCount[typesList.indexOf(type)]++;
153 } catch (SQLException e) {
154 System.err.printf("Does '%s' exist already??\n", url);
155 }
156 }
157
158 /**
159 * Accepts a JSONArray of JSONArrays where the inner arrays contains items.
160 * Checks each item in every array and adds the ones we do not already know about.
161 *
162 * Adds new items both to the linked list "found" that contains all items found and
163 * the LinkedBlockingQueue "toRead" where other threads will pick them up and read and
164 * parse the content.
165 *
166 * @param lists a JSONArray containing JSONArrays for each category (planets, movies, characters etc.)
167 */
168 private void findNewItems(JSONArray[] lists) {
169 for (JSONArray list : lists) {
170 if (list != null) {
171 list.forEach(item -> {
172 synchronized (found) {
173 if (!found.contains(item)) {
174 found.offer((String) item);
175 toRead.offer((String) item);
176 }
177 }
178 });
179 }
180 }
181 }
182 }
183
184 private void createTable() {
185 try {
186 Statement stmt = con.createStatement();
187 stmt.execute("CREATE TABLE swapi (" +
188 "id varchar(255) NOT NULL," +
189 "type varchar(16) NOT NULL," +
190 "nr int NOT NULL," +
191 "json varchar(32672) NOT NULL)");
192 stmt.execute("CREATE INDEX searchIndex on swapi (type, nr)");
193 stmt.execute("CREATE UNIQUE INDEX idx on swapi (id)");
194 } catch (SQLException e) {
195 e.printStackTrace();
196 }
197
198 }
199
200 public static void main(String[] args) {
201 for (int i = 0; i < typeCount.length; i++) { // Make sure this is cleared, should not be neededs
202 typeCount[i] = 0;
203 }
204 new Database();
205 }
206}