· 9 years ago · Dec 05, 2016, 08:24 PM
1import java.sql.*;
2
3public class XmasDB {
4 static final String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/";
5 static final String USER = "root"; //TODO replace with your username
6 static final String PASS = "****"; //TODO replace with your password
7 static private final String DB_NAME = "xmas";
8
9
10 public final static String WANT_TABLE_NAME = "want_list";
11 public final static String NEED_TABLE_NAME = "need_list";
12 // Each solver will have a unique ID
13 public final static String PK_COLUMN = "id";
14 // A primary key is needed to allow updates to the database on modifications to ResultSet
15 public final static String NAME_COLUMN = "name";
16 public final static String PRICE_COLUMN = "price";
17 public final static String PRIORITY_COLUMN = "riority";
18
19 public final static int MOVIE_MIN_RATING = 1;
20 public final static int MOVIE_MAX_RATING = 5;
21
22
23
24 // Name our database
25
26 static Statement statement = null;
27 static Connection conn = null;
28 static ResultSet rs = null;
29 // Create out data model
30 private static XmasDataModel xmasDataModel;
31
32 public static void main(String args[]) {
33
34
35 //setup creates database (if it doesn't exist), opens connection, and adds sample data
36
37 if (!setup()) {
38 System.exit(-1);
39 }
40
41 if (!loadAllMovies()) {
42 System.exit(-1);
43 }
44
45 //If no errors, then start GUI
46 XmasGUI tableGUI = new XmasGUI (xmasDataModel);
47
48 }
49
50 //Create or recreate a ResultSet containing the whole database, and give it to movieDataModel
51 public static boolean loadAllMovies(){
52
53 try{
54
55 if (rs!=null) {
56 rs.close();
57 }
58
59 String getSomeData = "SELECT * FROM " + WANT_TABLE_NAME;
60 String getRestData = "SELECT * FROM " + NEED_TABLE_NAME;
61
62 rs = statement.executeQuery (getSomeData);
63 rs = statement.executeQuery (getRestData);
64
65
66 if (xmasDataModel == null) {
67 //If no current movieDataModel, then make one
68 xmasDataModel = new XmasDataModel (rs);
69 } else {
70 //Or, if one already exists, update its ResultSet
71 xmasDataModel.updateResultSet(rs);
72 }
73
74 return true;
75
76 } catch (Exception e) {
77 System.out.println("Error loading or reloading lists");
78 System.out.println(e);
79 e.printStackTrace();
80 return false;
81 }
82
83 }
84
85 public static boolean setup(){
86 try {
87
88 //Load driver class
89 try {
90 String Driver = "com.mysql.jdbc.Driver";
91 Class.forName(Driver);
92 } catch (ClassNotFoundException cnfe) {
93 System.out.println("No database drivers found. Quitting");
94 return false;
95 }
96
97 conn = DriverManager.getConnection(DB_CONNECTION_URL + DB_NAME, USER, PASS);
98
99 statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
100
101 //Does the table exist? If not, create it.
102 if (! xmasTableOneExists ()) {
103
104 //Create a table
105 String createNeedTableSQL = "CREATE TABLE " + NEED_TABLE_NAME + " (" + PK_COLUMN + " int NOT NULL AUTO_INCREMENT, " + NAME_COLUMN + " varchar(50), " + PRICE_COLUMN + " int, " + PRIORITY_COLUMN + " int, PRIMARY KEY(" + PK_COLUMN + "))";
106 System.out.println ( createNeedTableSQL );
107
108
109 statement.executeUpdate ( createNeedTableSQL );
110
111 System.out.println ( "Created Need table..." );
112
113 String addDataSQL = "INSERT INTO " + NEED_TABLE_NAME + "(" + NAME_COLUMN + ", " + PRICE_COLUMN + ", " + PRIORITY_COLUMN + ")" + " VALUES ('New Winter Boots', 85, 3)";
114 statement.executeUpdate ( addDataSQL );
115 addDataSQL = "INSERT INTO " + NEED_TABLE_NAME + "(" + NAME_COLUMN + ", " + PRICE_COLUMN + ", " + PRIORITY_COLUMN + ")" + " VALUES('New Car', 3000, 2)";
116 statement.executeUpdate ( addDataSQL );
117 addDataSQL = "INSERT INTO " + NEED_TABLE_NAME + "(" + NAME_COLUMN + ", " + PRICE_COLUMN + ", " + PRIORITY_COLUMN + ")" + " VALUES ('Gift Card to Grocery Store', 300, 1)";
118 statement.executeUpdate ( addDataSQL );
119
120 }else if (! xmasTableTwoExists ()) {
121 String createWantTableSQL = "CREATE TABLE " + WANT_TABLE_NAME + " (" + PK_COLUMN + " int NOT NULL AUTO_INCREMENT, " + NAME_COLUMN + " varchar(50), " + PRICE_COLUMN + " int, " + PRIORITY_COLUMN + " int, PRIMARY KEY(" + PK_COLUMN + "))";
122 System.out.println (createWantTableSQL);
123 statement.executeUpdate(createWantTableSQL);
124 System.out.println("Created Want Table...");
125
126 String addMoreSQL = "INSERT INTO " + WANT_TABLE_NAME + "(" + NAME_COLUMN + ", " + PRICE_COLUMN + ", " + PRIORITY_COLUMN + ")" + " VALUES ('Gaming PC', 500, 3)";
127 statement.executeUpdate(addMoreSQL);
128 addMoreSQL = "INSERT INTO " + WANT_TABLE_NAME + "(" + NAME_COLUMN + ", " + PRICE_COLUMN + ", " + PRIORITY_COLUMN + ")" + " VALUES('Astrology Book', 150, 2)";
129 statement.executeUpdate(addMoreSQL);
130 addMoreSQL = "INSERT INTO " + WANT_TABLE_NAME + "(" + NAME_COLUMN + ", " + PRICE_COLUMN + ", " + PRIORITY_COLUMN + ")" + " VALUES ('Hover Board', 300, 1)";
131 statement.executeUpdate(addMoreSQL);
132 }
133 return true;
134
135 } catch (SQLException se) {
136 System.out.println(se);
137 se.printStackTrace();
138 return false;
139 }
140 }
141
142 private static boolean xmasTableOneExists () throws SQLException {
143
144 String checkNeedTablePresentQuery = "SHOW TABLES LIKE '" + NEED_TABLE_NAME + "'"; //Can query the database schema
145 ResultSet tablesRS = statement.executeQuery(checkNeedTablePresentQuery);
146
147 if (tablesRS.next()) { //If ResultSet has a next row, it has at least one row... that must be our table
148 return true;
149 }
150 return false;
151
152 }
153 private static boolean xmasTableTwoExists () throws SQLException {
154
155 String checkWantTablePresentQuery = "SHOW TABLES LIKE '" + WANT_TABLE_NAME + "'";
156
157 ResultSet tableTwoRS = statement.executeQuery(checkWantTablePresentQuery);
158
159 if (tableTwoRS.next()) { //If ResultSet has a next row, it has at least one row... that must be our table
160 return true;
161 }
162 return false;
163
164 }
165
166 //Close the ResultSet, statement and connection, in that order.
167 public static void shutdown(){
168 try {
169 if (rs != null) {
170 rs.close();
171 System.out.println("Result set closed");
172 }
173 } catch (SQLException se) {
174 se.printStackTrace();
175 }
176
177 try {
178 if (statement != null) {
179 statement.close();
180 System.out.println("Statement closed");
181 }
182 } catch (SQLException se){
183 //Closing the connection could throw an exception too
184 se.printStackTrace();
185 }
186
187 try {
188 if (conn != null) {
189 conn.close();
190 System.out.println("Database connection closed");
191 }
192 }
193 catch (SQLException se) {
194 se.printStackTrace();
195 }
196 }
197}
198
199//(This is my XmasGUI.java Class)
200/
201
202
203import javax.swing.*;
204import java.awt.*;
205import java.awt.event.ActionEvent;
206import java.awt.event.ActionListener;
207import java.awt.event.WindowEvent;
208import java.awt.event.WindowListener;
209import java.util.Calendar;
210
211/**
212 * Created by Nnamdi on 11/29/2016.
213 */
214public class XmasGUI extends JFrame implements WindowListener{
215 private JTable needTable;
216 private JTextPane NEEDTABLETextPane1;
217 private JTextPane WANTTABLETextPane;
218 private JTable wantTable;
219 private JButton addButton;
220 private JButton exitButton;
221 private JTextPane welcomeToXmasListTextPane;
222 private JButton transferButton;
223 private JRadioButton radioNeed;
224 private JRadioButton radioWant;
225 private JTextField txtNameInput;
226 private JSpinner prioritySpinner;
227 private JTextField txtPriceInput;
228 private JPanel rootPanel;
229 private JButton deleteButton;
230
231 XmasGUI(final XmasDataModel xmasDataTableModel) {
232
233 setContentPane(rootPanel);
234 pack();
235 setTitle("Christmas List Database Application");
236 addWindowListener(this);
237 setVisible(true);
238 setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
239
240
241 //Set up JTables
242 needTable.setGridColor(Color.BLACK);
243 needTable.setModel(xmasDataTableModel);
244 wantTable.setGridColor (Color.YELLOW);
245 wantTable.setModel (xmasDataTableModel );
246
247 //Set up the priority spinner.
248 //SpinnerNumberModel constructor arguments: spinner's initial value, min, max, step.
249 prioritySpinner.setModel(new SpinnerNumberModel(1, XmasDB.MOVIE_MIN_RATING, XmasDB.MOVIE_MAX_RATING, 1));
250
251 exitButton.addActionListener ( new ActionListener ( ) {
252 @Override
253 public void actionPerformed ( ActionEvent e ) {
254 XmasDB.shutdown();
255 System.exit (0);
256 }
257 } );
258 deleteButton.addActionListener ( new ActionListener ( ) {
259 @Override
260 public void actionPerformed ( ActionEvent e ) {
261 int currentRow = needTable.getSelectedRow();
262 int currentrow = wantTable.getSelectedRow();
263
264 if (currentRow == -1) { // -1 means no row is selected. Display error message.
265 JOptionPane.showMessageDialog(rootPane, "Please choose a item to delete");
266 }
267 boolean deleted = xmasDataTableModel.deleteRow(currentRow);
268 if (deleted) {
269 XmasDB.loadAllMovies();
270 } else {
271 JOptionPane.showMessageDialog(rootPane, "Error deleting item");
272 }
273 }
274 } );
275 }
276
277
278 @Override
279 public void windowOpened ( WindowEvent e ) {
280
281 }
282
283 @Override
284 public void windowClosing ( WindowEvent e ) {
285 System.out.println("closing");
286 XmasDB.shutdown();
287 }
288
289 @Override
290 public void windowClosed ( WindowEvent e ) {
291
292 }
293
294 @Override
295 public void windowIconified ( WindowEvent e ) {
296
297 }
298
299 @Override
300 public void windowDeiconified ( WindowEvent e ) {
301
302 }
303
304 @Override
305 public void windowActivated ( WindowEvent e ) {
306
307 }
308
309 @Override
310 public void windowDeactivated ( WindowEvent e ) {
311
312 }
313
314 private void createUIComponents () {
315 // TODO: place custom component creation code here
316 }
317}