· 7 years ago · Dec 04, 2018, 11:38 AM
1/*
2 * This class holds all methods that interact with the database.
3 */
4package prog38081.Assignment_1;
5
6import java.sql.Connection;
7import java.sql.ResultSet;
8import java.sql.SQLException;
9import java.sql.Statement;
10import java.util.ArrayList;
11
12/**
13 *
14 * @author alertaj
15 */
16public class DBMethods {
17
18 /*
19 * Get all platforms from the database using the prepareStatement. Order by
20 * id.
21 */
22 public ArrayList<Platform> getAllPlatforms() {
23 Statement prstmt = null;
24 ResultSet rs = null;
25 Connection conn = null;
26 try {
27 Class.forName("com.mysql.jdbc.Driver").newInstance();
28 DBConnectionFactory.getConnection();
29 String getAllPlatforms = "SELECT * FROM Platform ORDER BY id";
30 prstmt = conn.prepareStatement(getAllPlatforms);
31 rs = prstmt.executeQuery(getAllPlatforms);
32
33 String name = rs.getString("video_game_title");
34 System.out.println("table name : " + name);
35
36 while (rs.next()) {
37 String platform = rs.getString("Platform");
38 System.out.println("Platform: " + platform);
39 System.out.println("");
40 }
41
42 rs.close();
43 conn.close();
44
45 } catch (Exception ex) {
46 System.out.println(ex.getMessage());
47 }
48 return null;
49 }
50 /*
51 * Adds a new game to the database using the prepareStatement. Returns true
52 * if insert is successful, false otherwise.
53 */
54
55 public boolean addGame(VideoGame videoGame) throws SQLException {
56
57 // Needs Implementation
58 Statement prstmt = null;
59 boolean result = false;
60
61 Connection conn = DBConnectionFactory.getConnection();
62 try {
63 String driver = "com.mysql.jdbc.Driver";
64 Class.forName(driver).newInstance();
65 String addGame = "INSERT INTO "
66 + "gamesrus(id, title, developer, publisher, year, region)"
67 + "VALUES(?, ?, ?, ?, ?, ? )";
68 prstmt = conn.prepareStatement(addGame);
69 //here i dont know how to get the fields into the method to set the values...
70 prstmt.setInt(1, id);
71 prstmt.setString(2, title);
72 prstmt.setString(3, developer);
73 prstmt.setString(4, publisher);
74 prstmt.setYear(5, year);
75 prstmt.setString(6, region);
76
77 prstmt.executeUpdate(addGame);
78 result = true;
79
80 } catch (Exception ex) {
81 System.out.println(ex.getMessage());
82
83 } finally {
84 //Close everything
85 DBConnectionFactory.close(conn);
86 }
87
88 return result;
89 }
90 /*
91 * Get all games from the database using the prepareStatement. Order by
92 * title.
93 */
94
95 public ArrayList<VideoGame> getAllGames() {
96 Statement prstmt = null;
97 ResultSet rs = null;
98 Connection conn = null;
99 try {
100 Class.forName("com.mysql.jdbc.Driver").newInstance();
101 DBConnectionFactory.getConnection();
102 String getAllGames = "SELECT * FROM video_game_title ORDER BY title";
103 prstmt = conn.prepareStatement(getAllGames);
104 rs = prstmt.executeQuery(getAllGames);
105
106 String name = rs.getString("video_game_title");
107 System.out.println("table name : " + name);
108
109 while (rs.next()) {
110 String game = rs.getString("title");
111 System.out.println("Game: " + game);
112 System.out.println("");
113 }
114
115 rs.close();
116 conn.close();
117
118 } catch (Exception ex) {
119 System.out.println(ex.getMessage());
120 }
121 return null;
122 }
123
124 /*
125 * Create the video_game_title table using the preparedStatement. The method
126 * should return true if the table was added successfully, false otherwise.
127 *
128 * Check your table before you delete it for what typoes they should be...
129 */
130 public boolean createVideoGameTable() {
131 // Needs Implementation
132 Statement prstmt = null;
133 boolean result = false;
134
135 try {
136
137 Class.forName("com.mysql.jdbc.Driver").newInstance();
138 DBConnectionFactory.getConnection();
139
140 try {
141
142 //Step 4a: Executing SQL statements;
143 String tblCreate = "CREATE TABLE video_game_title(id INT NOT NULL, "
144 + "title VARCHAR(45) NOT NULL, "
145 + "developer VARCHAR(45), NOT NULL "
146 + "publisher VARCHAR(45), NOT NULL "
147 + "year YEAR, NOT NULL"
148 + "region VARCHAR(45), "
149 + "Platform_id INT, "
150 + "PRIMARY KEY (id))"
151 + "foreign key(Platform_id) references Platform)";
152 prstmt.executeUpdate(tblCreate);
153 result = true;
154
155 } catch (Exception ex) {
156 System.out.println("Table already exists.");
157 }
158
159 } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException ex) {
160 System.out.println(ex.getMessage());
161 }
162 return result;
163 }
164
165 /*
166 * Delete the video_game_title table using the preparedStatement. The method
167 * should return true if the table was deleted successfully, false
168 * otherwise.
169 *
170 * can be very dangerous to use!!
171 */
172 public boolean deleteVideoGameTable() {
173 // Needs Implementation
174 Statement prstmt = null;
175 boolean result = false;
176 Connection conn = null;
177
178 try {
179
180 Class.forName("com.mysql.jdbc.Driver").newInstance();
181 DBConnectionFactory.getConnection();
182
183 String deleteTable = "DROP TABLE IF EXISTS ?";
184 prstmt = conn.prepareStatement(deleteTable);
185//here i dont know what to put in the set string part for table
186 prstmt.setString(1, table);
187
188 prstmt.executeUpdate(deleteTable);
189 result = true;
190
191 } catch (Exception ex) {
192 System.out.println(ex.getMessage());
193 }
194 return result;
195 }
196}