· 8 years ago · Nov 12, 2017, 05:12 PM
1/*
2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
5 */
6package databaseassignment;
7
8import java.io.BufferedReader;
9import java.io.File;
10import java.io.FileReader;
11import java.io.IOException;
12import java.sql.Connection;
13import java.sql.DriverManager;
14import java.sql.PreparedStatement;
15import java.sql.ResultSet;
16import java.sql.SQLException;
17import java.sql.Statement;
18import java.text.DateFormat;
19import java.text.ParseException;
20import java.text.SimpleDateFormat;
21import java.util.Date;
22import java.util.Scanner;
23
24/**
25 *
26 * @author Ben
27 */
28public class DatabaseAssignment {
29
30 /**
31 * @param args the command line arguments
32 */
33 /**
34 * @param args the command line arguments
35 */
36 private static String username, password = "";
37 private static String host = "localhost";
38 private static String dbname = "";
39 private static Connection conn;
40 private static Statement s;
41 private static FileReader fileReader;
42 private static BufferedReader inb;
43 private static File inputFile;
44
45 private static Date javaDate;
46
47 private static Scanner sc = new Scanner(System.in);
48
49
50
51 public static void main(String[] args) throws SQLException, IOException, Exception {
52 // TODO code application logic here
53
54 // the following statement loads the MySQL jdbc driver
55 // make sure it is in the CLASSPATH
56
57
58 try {
59 Class.forName ("com.mysql.jdbc.Driver");
60 } catch (ClassNotFoundException e) {
61 System.out.println ("Could not load the driver");
62 }
63
64 connectToDatabaseQuick();
65 //connectToDatabase(username,password,host,dbname);
66 s = conn.createStatement();
67
68 createClubTable(s);
69 createPlayerTable(s);
70 createGameTable(s);
71 createMatchTable(s);
72
73 conn.commit();
74
75
76 conn.close();
77 }
78
79 public static void createClubTable(Statement s) throws SQLException, ParseException{
80
81 String line = "";
82 String[] tokens;
83
84 s.execute("drop table if exists club");
85 String create = "create table IF NOT EXISTS Club(ClubName VARCHAR(20) PRIMARY KEY, Address VARCHAR(40), DateFormed DATE)";
86 s.executeUpdate(create);
87
88 System.out.println("Created Club Table");
89
90 String insertClub = "insert into Club values (?, ?, ?)";
91 PreparedStatement ps = conn.prepareStatement(insertClub);
92
93
94 try{
95 System.out.println("Inserting Data from text file");
96 String file = "src\\databaseassignment\\Club.txt";
97
98 inputFile = new File(file);
99 fileReader = new FileReader(inputFile);
100 inb = new BufferedReader(fileReader);
101
102 System.out.println("Ready to read line");
103
104 line = inb.readLine();
105 while( (line != null) ){
106
107 tokens = line.split(",");
108 System.out.println(tokens[0] + " "+ tokens[1]+ " "+ tokens[2]+" ");
109 ps.setString(1, tokens[0]);
110 ps.setString(2, tokens[1]);
111
112 String date_str = (tokens[2]);
113 DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
114 try {
115 javaDate = (java.util.Date) formatter.parse(date_str);
116 } catch (ParseException e) {
117 System.out.println("Error: " + e.toString());
118 }
119
120 long javaTime = javaDate.getTime();
121 java.sql.Date sqlDate = new java.sql.Date(javaTime);
122
123
124 ps.setDate(3, sqlDate);
125 ps.execute();
126 ps.clearParameters();
127
128 line=inb.readLine();
129
130
131 }
132
133 inb.close();
134 fileReader.close();
135
136 }
137 catch(IOException e){
138
139 System.out.println("Error: " + e.toString());
140 }
141
142 }
143
144 public static void createPlayerTable(Statement s) throws SQLException{
145
146 String line = "";
147 String tokens[];
148
149 s.execute("drop table if exists player");
150 String create = "CREATE TABLE IF NOT EXISTS Player (PlayerName VARCHAR (20) PRIMARY KEY, DateOfBirth DATE, FIDERating INT not null, check(FIDERating <=3000 AND FIDERating >= 800), FIDETitle VARCHAR(30), ClubName VARCHAR(30))";
151
152
153 s.execute(create);
154
155 System.out.println("Player table created");
156
157 String insertPlayer = "insert into Player values (?, ?, ?, ?, ?)";
158 PreparedStatement ps = conn.prepareStatement(insertPlayer);
159
160
161 try{
162 System.out.println("Inserting Data from text file");
163 String file = "src\\databaseassignment\\Player.txt";
164
165 inputFile = new File(file);
166 fileReader = new FileReader(inputFile);
167 inb = new BufferedReader(fileReader);
168
169 System.out.println("Ready to read line");
170
171 line = inb.readLine();
172 while( (line != null) ){
173
174 tokens = line.split(",");
175 System.out.println(tokens[0] + " " + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]);
176
177 ps.setString(1, tokens[0]);
178
179 String date_str = (tokens[1]);
180
181 DateFormat formatter = new SimpleDateFormat("yy-MM-dd");
182 try {
183 javaDate = (java.util.Date) formatter.parse(date_str);
184 } catch (ParseException e) {
185 System.out.println("Error: " + e.toString());
186 }
187
188 long javaTime = javaDate.getTime();
189 java.sql.Date sqlDate = new java.sql.Date(javaTime);
190
191 ps.setDate(2, sqlDate);
192
193 ps.setInt(3, Integer.parseInt(tokens[2]));
194 ps.setString(4, tokens[3]);
195 ps.setString(5, tokens[4]);
196
197 ps.execute();
198 ps.clearParameters();
199
200 line=inb.readLine();
201
202
203 }
204
205 inb.close();
206 fileReader.close();
207
208 }
209 catch(IOException e){
210
211 System.out.println("Error: " + e.toString());
212 }
213 }
214
215 public static void createGameTable(Statement s) throws SQLException{
216
217 String line = "";
218 String tokens[];
219
220 s.execute("drop table if exists game");
221 String create = "CREATE TABLE IF NOT EXISTS Game (GameID VARCHAR(10) PRIMARY KEY, DatePlayed DATE, BoardNum INT, Score VARCHAR(3), MatchID VARCHAR(10), WhitePlayer VARCHAR(20), BlackPlayer VARCHAR(20))";
222
223 s.execute(create);
224
225 System.out.println("Game table created");
226
227 String insertGame = "insert into Game values(?,?,?,?,?,?,?)";
228 PreparedStatement ps = conn.prepareStatement(insertGame);
229
230 try{
231 System.out.println("Inserting Data from text file");
232 String file = "src\\databaseassignment\\Game.txt";
233
234 inputFile = new File(file);
235 fileReader = new FileReader(inputFile);
236 inb = new BufferedReader(fileReader);
237
238 System.out.println("Ready to read line");
239
240 line = inb.readLine();
241 while( (line != null) ){
242
243 tokens = line.split(",");
244 System.out.println(tokens[0] + " " + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]+ " " + tokens[5] + " " + tokens[6]);
245
246 ps.setString(1, tokens[0]);
247
248 String date_str = (tokens[1]);
249 DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
250 try {
251 javaDate = (java.util.Date) formatter.parse(date_str);
252 } catch (ParseException e) {
253 System.out.println("Error: " + e.toString());
254 }
255 long javaTime = javaDate.getTime();
256 java.sql.Date sqlDate = new java.sql.Date(javaTime);
257 ps.setDate(2, sqlDate);
258
259 ps.setInt(3, Integer.parseInt(tokens[2]));
260 ps.setString(4, tokens[3]);
261 ps.setString(5, tokens[4]);
262 ps.setString(6, tokens[5]);
263 ps.setString(7, tokens[6]);
264 ps.execute();
265 ps.clearParameters();
266
267 line=inb.readLine();
268
269
270 }
271
272 inb.close();
273 fileReader.close();
274
275 }
276 catch(IOException e){
277
278 System.out.println("Error: " + e.toString());
279 }
280
281
282 }
283
284 public static void createMatchTable(Statement s) throws SQLException{
285
286 String line = "";
287 String tokens[];
288
289 s.execute("drop table if exists TBLMatch");
290 String create = "CREATE TABLE IF NOT EXISTS TBLMatch(MatchID VARCHAR(10) PRIMARY KEY, MatchDate DATE, Venue VARCHAR(20), Score VARCHAR(3), WinningClub VARCHAR(20), LosingClub VARCHAR(20))";
291
292 s.execute(create);
293
294 System.out.println("Match table created");
295
296 String insertMatch = "insert into TBLMatch values(?,?,?,?,?,?)";
297 PreparedStatement ps = conn.prepareStatement(insertMatch);
298
299 try{
300 System.out.println("Inserting Data from text file");
301 String file = "src\\databaseassignment\\Match.txt";
302
303 inputFile = new File(file);
304 fileReader = new FileReader(inputFile);
305 inb = new BufferedReader(fileReader);
306
307 System.out.println("Ready to read line");
308
309 line = inb.readLine();
310 while( (line != null) ){
311
312 tokens = line.split(",");
313 System.out.println(tokens[0] + " " + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]+ " " + tokens[5]);
314
315 ps.setString(1, tokens[0]);
316
317 String date_str = (tokens[1]);
318 DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
319 try {
320 javaDate = (java.util.Date) formatter.parse(date_str);
321 } catch (ParseException e) {
322 System.out.println("Error: " + e.toString());
323 }
324 long javaTime = javaDate.getTime();
325 java.sql.Date sqlDate = new java.sql.Date(javaTime);
326 ps.setDate(2, sqlDate);
327
328 ps.setString(3, tokens[2]);
329 ps.setString(4, tokens[3]);
330 ps.setString(5, tokens[4]);
331 ps.setString(6, tokens[5]);
332
333 ps.execute();
334 ps.clearParameters();
335
336 line=inb.readLine();
337
338
339 }
340
341 inb.close();
342 fileReader.close();
343
344 }
345 catch(IOException e){
346
347 System.out.println("Error: " + e.toString());
348 }
349
350 }
351
352 //doesn't work/not needed
353 public static void createDatabase(Statement s) {
354
355 try {
356
357 String sql = ("CREATE DATABASE CHESS");
358 System.out.println("Database created successfully");
359
360 s.executeUpdate(sql);
361 } catch (SQLException e) {
362 System.out.println("Error:" + e.toString());
363 }
364
365 }
366
367 public static void connectToDatabase(String username, String password, String host, String dbname) throws SQLException{
368
369 // userid, password and hostname are obtained from the console
370 try{
371 System.out.println("Type userid, password, hostname or ipaddress, database name: ");
372 username = sc.next();
373 password = sc.next();
374 host = sc.next();
375 dbname = sc.next();
376 System.out.println(username+" "+password+" "+host+" "+dbname);
377 }
378 catch(Exception e){
379 System.out.println("Error: " + e.toString());
380 }
381
382 conn = DriverManager.getConnection
383 ("jdbc:mysql://"+host+":3306/chess", username, password);
384 conn.setAutoCommit(false);
385
386
387
388
389
390 }
391
392 public static void connectToDatabaseQuick() throws SQLException{
393
394 host = "localhost";
395 username = "root";
396 password = "admin";
397
398
399 conn = DriverManager.getConnection
400 ("jdbc:mysql://"+host+":3306/chess", username, password);
401 conn.setAutoCommit(false);
402
403 }
404
405
406
407}