· 8 years ago · Nov 26, 2017, 08:43 PM
1
2 public static void createClubTable(Statement s) throws SQLException, ParseException{
3
4 String line = "";
5 String[] tokens;
6
7 s.execute("drop table if exists player");
8 s.execute("drop table if exists club");
9 String create = "create table IF NOT EXISTS Club(ClubName VARCHAR(20) NOT NULL, Address VARCHAR(40), DateFormed DATE, PRIMARY KEY(ClubName)) ";
10 s.executeUpdate(create);
11
12 System.out.println("Created Club Table");
13
14 String insertClub = "insert into Club values (?, ?, ?)";
15 PreparedStatement ps = conn.prepareStatement(insertClub);
16
17
18 try{
19 System.out.println("Inserting Data from text file");
20 String file = "src\\databaseassignment\\Club.txt";
21
22 inputFile = new File(file);
23 fileReader = new FileReader(inputFile);
24 inb = new BufferedReader(fileReader);
25
26 System.out.println("Ready to read line");
27
28 line = inb.readLine();
29 while( (line != null) ){
30
31 tokens = line.split(",");
32 System.out.println(tokens[0] + " "+ tokens[1]+ " "+ tokens[2]+" ");
33 ps.setString(1, tokens[0]);
34 ps.setString(2, tokens[1]);
35
36 String date_str = (tokens[2]);
37 DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
38 try {
39 javaDate = (java.util.Date) formatter.parse(date_str);
40 } catch (ParseException e) {
41 System.out.println("Error: " + e.toString());
42 }
43
44 long javaTime = javaDate.getTime();
45 java.sql.Date sqlDate = new java.sql.Date(javaTime);
46
47
48 ps.setDate(3, sqlDate);
49 ps.execute();
50 ps.clearParameters();
51
52 line=inb.readLine();
53
54
55 }
56
57 inb.close();
58 fileReader.close();
59
60 }
61 catch(IOException e){
62
63 System.out.println("Error: " + e.toString());
64 }
65
66 }
67
68 public static void createPlayerTable(Statement s) throws SQLException{
69
70 String line = "";
71 String tokens[];
72
73 //s.execute("drop table if exists player");
74 String create = "CREATE TABLE IF NOT EXISTS Player (PlayerName VARCHAR (20) NOT NULL, DateOfBirth DATE, FIDERating INT not null, check(FIDERating <=3000 AND FIDERating >= 800), FIDETitle VARCHAR(30), ClubName VARCHAR(30), PRIMARY KEY(PlayerName), FOREIGN KEY(ClubName) REFERENCES Club(ClubName));";
75
76
77 s.execute(create);
78
79 System.out.println("Player table created");
80
81 String insertPlayer = "insert into Player values (?, ?, ?, ?, ?)";
82 PreparedStatement ps = conn.prepareStatement(insertPlayer);
83
84
85 try{
86 System.out.println("Inserting Data from text file");
87 String file = "src\\databaseassignment\\Player.txt";
88
89 inputFile = new File(file);
90 fileReader = new FileReader(inputFile);
91 inb = new BufferedReader(fileReader);
92
93 System.out.println("Ready to read line");
94
95 line = inb.readLine();
96 while( (line != null) ){
97
98 tokens = line.split(",");
99 System.out.println(tokens[0] + " " + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]);
100
101 ps.setString(1, tokens[0]);
102
103 String date_str = (tokens[1]);
104
105 DateFormat formatter = new SimpleDateFormat("yy-MM-dd");
106 try {
107 javaDate = (java.util.Date) formatter.parse(date_str);
108 } catch (ParseException e) {
109 System.out.println("Error: " + e.toString());
110 }
111
112 long javaTime = javaDate.getTime();
113 java.sql.Date sqlDate = new java.sql.Date(javaTime);
114
115 ps.setDate(2, sqlDate);
116
117 ps.setInt(3, Integer.parseInt(tokens[2]));
118 ps.setString(4, tokens[3]);
119 ps.setString(5, tokens[4]);
120
121 ps.execute();
122 ps.clearParameters();
123
124 line=inb.readLine();
125
126
127 }
128
129 inb.close();
130 fileReader.close();
131
132 }
133 catch(IOException e){
134
135 System.out.println("Error: " + e.toString());
136 }
137 }
138
139 public static void createGameTable(Statement s) throws SQLException{
140
141 String line = "";
142 String tokens[];
143
144 //s.execute("drop table if exists game");
145 String create = "CREATE TABLE IF NOT EXISTS Game (GameID VARCHAR(10), DatePlayed DATE, BoardNum INT, Score VARCHAR(3), MatchID VARCHAR(10), WhitePlayer VARCHAR(20), BlackPlayer VARCHAR(20), PRIMARY KEY(GameID), FOREIGN KEY(MatchID) REFERENCES TblMatch(MatchID));";
146
147 s.execute(create);
148
149 System.out.println("Game table created");
150
151 String insertGame = "insert into Game values(?,?,?,?,?,?,?)";
152 PreparedStatement ps = conn.prepareStatement(insertGame);
153
154 try{
155 System.out.println("Inserting Data from text file");
156 String file = "src\\databaseassignment\\Game.txt";
157
158 inputFile = new File(file);
159 fileReader = new FileReader(inputFile);
160 inb = new BufferedReader(fileReader);
161
162 System.out.println("Ready to read line");
163
164 line = inb.readLine();
165 while( (line != null) ){
166
167 tokens = line.split(",");
168 System.out.println(tokens[0] + " " + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]+ " " + tokens[5] + " " + tokens[6]);
169
170 ps.setString(1, tokens[0]);
171
172 String date_str = (tokens[1]);
173 DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
174 try {
175 javaDate = (java.util.Date) formatter.parse(date_str);
176 } catch (ParseException e) {
177 System.out.println("Error: " + e.toString());
178 }
179 long javaTime = javaDate.getTime();
180 java.sql.Date sqlDate = new java.sql.Date(javaTime);
181 ps.setDate(2, sqlDate);
182
183 ps.setInt(3, Integer.parseInt(tokens[2]));
184 ps.setString(4, tokens[3]);
185 ps.setString(5, tokens[4]);
186 ps.setString(6, tokens[5]);
187 ps.setString(7, tokens[6]);
188 ps.execute();
189 ps.clearParameters();
190
191 line=inb.readLine();
192
193
194 }
195
196 inb.close();
197 fileReader.close();
198
199 }
200 catch(IOException e){
201
202 System.out.println("Error: " + e.toString());
203 }
204
205
206 }
207
208 public static void createMatchTable(Statement s) throws SQLException{
209
210 String line = "";
211 String tokens[];
212
213 s.execute("drop table if exists game");
214 s.execute("drop table if exists TBLMatch");
215 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))";
216
217 s.execute(create);
218
219 System.out.println("Match table created");
220
221 String insertMatch = "insert into TBLMatch values(?,?,?,?,?,?)";
222 PreparedStatement ps = conn.prepareStatement(insertMatch);
223
224 try{
225 System.out.println("Inserting Data from text file");
226 String file = "src\\databaseassignment\\Match.txt";
227
228 inputFile = new File(file);
229 fileReader = new FileReader(inputFile);
230 inb = new BufferedReader(fileReader);
231
232 System.out.println("Ready to read line");
233
234 line = inb.readLine();
235 while( (line != null) ){
236
237 tokens = line.split(",");
238 System.out.println(tokens[0] + " " + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]+ " " + tokens[5]);
239
240 ps.setString(1, tokens[0]);
241
242 String date_str = (tokens[1]);
243 DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
244 try {
245 javaDate = (java.util.Date) formatter.parse(date_str);
246 } catch (ParseException e) {
247 System.out.println("Error: " + e.toString());
248 }
249 long javaTime = javaDate.getTime();
250 java.sql.Date sqlDate = new java.sql.Date(javaTime);
251 ps.setDate(2, sqlDate);
252
253 ps.setString(3, tokens[2]);
254 ps.setString(4, tokens[3]);
255 ps.setString(5, tokens[4]);
256 ps.setString(6, tokens[5]);
257
258 ps.execute();
259 ps.clearParameters();
260
261 line=inb.readLine();
262
263
264 }
265
266 inb.close();
267 fileReader.close();
268
269 }
270 catch(IOException e){
271
272 System.out.println("Error: " + e.toString());
273 }
274
275 }