· 7 years ago · Dec 06, 2018, 05:34 PM
1import java.sql.Connection;
2import java.sql.DriverManager;
3import java.sql.PreparedStatement;
4import java.sql.SQLException;
5import java.sql.Statement;
6import java.util.Calendar;
7
8import javax.swing.JOptionPane;
9
10public class myDataBase {
11
12 private Connection connection = null;
13 private Statement statement;
14
15 public void createDatabase(String databaseName) {
16 try {
17 Class.forName("com.mysql.jdbc.Driver");
18 this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/","root","");
19 Statement s = connection.createStatement();
20 s.executeUpdate("CREATE DATABASE IF NOT EXISTS " + databaseName);
21 this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+databaseName,"root","");
22
23 }
24 catch (SQLException e) {
25 JOptionPane.showMessageDialog(null, "Error!");
26 }
27 catch(Exception e) {
28 JOptionPane.showMessageDialog(null, "Error!");
29 }
30 }
31
32 public void getConnection(String databaseName) {
33 try {
34 Class.forName("com.mysql.jdbc.Driver");
35
36 this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+databaseName,"root","");
37
38 }
39 catch (SQLException e) {
40 JOptionPane.showMessageDialog(null, "Error!");
41 }
42 catch(Exception e) {
43 JOptionPane.showMessageDialog(null, "Error!");
44 }
45 }
46
47 public void createTable(String tableName) {
48 String myTableName = "CREATE TABLE " + tableName + " ( "
49 + "id INT(64) NOT NULL AUTO_INCREMENT,"
50 + "name VARCHAR(20),"
51 + "date DATE,"
52 + "agentCount INT(64), "
53 + "PRIMARY KEY (`id`))";
54 try {
55
56 statement = connection.createStatement();
57 statement.executeUpdate(myTableName);
58
59 } catch (SQLException e ) {
60 JOptionPane.showMessageDialog(null, "Error!");
61 }
62 }
63
64 public void insertRow(String tableName, String name, Calendar calendar, int num) {
65
66 try {
67
68 java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());
69 String query = " INSERT INTO " + tableName + " (name, date, agentCount)"
70 + " values (?,?,? )";
71
72 PreparedStatement ps = connection.prepareStatement(query);
73 ps.setString (1, name);
74 ps.setDate (2, startDate);
75 ps.setInt (3, num);
76
77 ps.execute();
78
79 } catch (SQLException e) {
80 JOptionPane.showMessageDialog(null, "Error!");
81 }
82 }
83
84 public void updateRow(String tableName, String id, String name, Calendar calendar, int num) {
85 try {
86
87 java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());
88 String query = "UPDATE " + tableName + " SET name = ?, date = ?, agentCount = ? WHERE id = " + id;
89 PreparedStatement ps = connection.prepareStatement(query);
90
91 ps.setString (1, name);
92 ps.setDate (2, startDate);
93 ps.setInt (3, num);
94
95 ps.execute();
96
97 } catch (SQLException e) {
98 JOptionPane.showMessageDialog(null, "Error!");
99 }
100 }
101
102 public void deleteRow(String tableName, String id) {
103
104 try {
105
106 String query = " DELETE FROM " + tableName + " WHERE id = " + id;
107
108 PreparedStatement ps = connection.prepareStatement(query);
109 ps.execute();
110
111 } catch (SQLException e) {
112 JOptionPane.showMessageDialog(null, "Error!");
113 }
114
115 }
116
117 public void dropTable(String tableName) {
118
119 try {
120
121 String query = " DROP TABLE IF EXISTS " + tableName;
122
123 PreparedStatement ps = connection.prepareStatement(query);
124 ps.execute();
125
126 } catch (SQLException e) {
127 JOptionPane.showMessageDialog(null, "Error!");
128 }
129 }
130
131 public void dropDatabase(String dbName) {
132
133 try {
134
135 String query = " DROP DATABASE IF EXISTS " + dbName;
136
137 PreparedStatement ps = connection.prepareStatement(query);
138 ps.execute();
139
140 } catch (SQLException e) {
141 JOptionPane.showMessageDialog(null, "Error!");
142 }
143 }
144
145 public void closeConnection() {
146 try {
147 connection.close();
148 } catch (SQLException e) {
149 JOptionPane.showMessageDialog(null, "Error!");
150 }
151 }
152
153}
154
155import java.awt.EventQueue;
156
157import javax.swing.JFrame;
158import javax.swing.JButton;
159import javax.swing.JTextField;
160import javax.swing.JLabel;
161import javax.swing.JOptionPane;
162
163import java.awt.Font;
164import javax.swing.JComboBox;
165import java.awt.event.ActionListener;
166import java.util.Calendar;
167import java.awt.event.ActionEvent;
168
169public class myInterface {
170
171 private JFrame frame;
172 private JTextField database;
173 private JTextField table;
174 private JTextField id;
175 private JTextField name;
176 private JTextField number;
177
178 /**
179 * Launch the application.
180 */
181 public static void main(String[] args) {
182 EventQueue.invokeLater(new Runnable() {
183 public void run() {
184 try {
185 myInterface window = new myInterface();
186 window.frame.setVisible(true);
187 } catch (Exception e) {
188 e.printStackTrace();
189 }
190 }
191 });
192 }
193
194 /**
195 * Create the application.
196 */
197 public myInterface() {
198 initialize();
199 }
200
201 /**
202 * Initialize the contents of the frame.
203 */
204 private void initialize() {
205 frame = new JFrame();
206 frame.setBounds(100, 100, 314, 396);
207 frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
208 frame.getContentPane().setLayout(null);
209
210 database = new JTextField();
211 database.setEditable(false);
212 database.setBounds(156, 29, 101, 25);
213 frame.getContentPane().add(database);
214 database.setColumns(10);
215
216 table = new JTextField();
217 table.setEditable(false);
218 table.setColumns(10);
219 table.setBounds(156, 65, 101, 25);
220 frame.getContentPane().add(table);
221
222 id = new JTextField();
223 id.setEditable(false);
224 id.setColumns(10);
225 id.setBounds(156, 101, 101, 25);
226 frame.getContentPane().add(id);
227
228 name = new JTextField();
229 name.setEditable(false);
230 name.setColumns(10);
231 name.setBounds(156, 137, 101, 25);
232 frame.getContentPane().add(name);
233
234 number = new JTextField();
235 number.setEditable(false);
236 number.setColumns(10);
237 number.setBounds(156, 173, 101, 25);
238 frame.getContentPane().add(number);
239
240 JLabel lblDatabase = new JLabel("Database:");
241 lblDatabase.setFont(new Font("Arial", Font.BOLD, 12));
242 lblDatabase.setBounds(39, 29, 86, 25);
243 frame.getContentPane().add(lblDatabase);
244
245 JLabel lblTable = new JLabel("Table name:");
246 lblTable.setFont(new Font("Arial", Font.BOLD, 12));
247 lblTable.setBounds(39, 65, 86, 25);
248 frame.getContentPane().add(lblTable);
249
250 JLabel lblId = new JLabel("ID column:");
251 lblId.setFont(new Font("Arial", Font.BOLD, 12));
252 lblId.setBounds(39, 101, 86, 25);
253 frame.getContentPane().add(lblId);
254
255 JLabel lblNameColumn = new JLabel("Name column:");
256 lblNameColumn.setFont(new Font("Arial", Font.BOLD, 12));
257 lblNameColumn.setBounds(39, 137, 86, 25);
258 frame.getContentPane().add(lblNameColumn);
259
260 JLabel lblNumberColumn = new JLabel("Number column:");
261 lblNumberColumn.setFont(new Font("Arial", Font.BOLD, 12));
262 lblNumberColumn.setBounds(39, 173, 112, 25);
263 frame.getContentPane().add(lblNumberColumn);
264
265 String[] sqlStrings = {"", "Create database", "Create table", "Insert row", "Update row", "Delete row", "Drop table", "Drop database" };
266 JComboBox<String> comboBox = new JComboBox<String>(sqlStrings);
267 comboBox.addActionListener(new ActionListener() {
268 public void actionPerformed(ActionEvent e) {
269
270 if(comboBox.getSelectedItem().equals("Create database")) {
271 database.setEditable(true);
272 table.setEditable(false);
273 id.setEditable(false);
274 name.setEditable(false);
275 number.setEditable(false);
276 }
277
278 if(comboBox.getSelectedItem().equals("Create table")) {
279 database.setEditable(false);
280 table.setEditable(true);
281 id.setEditable(false);
282 name.setEditable(false);
283 number.setEditable(false);
284 }
285
286 if(comboBox.getSelectedItem().equals("Insert row")) {
287 database.setEditable(false);
288 table.setEditable(true);
289 id.setEditable(false);
290 name.setEditable(true);
291 number.setEditable(true);
292 }
293
294 if(comboBox.getSelectedItem().equals("Update row")) {
295 database.setEditable(false);
296 table.setEditable(true);
297 id.setEditable(true);
298 name.setEditable(true);
299 number.setEditable(true);
300 }
301
302 if(comboBox.getSelectedItem().equals("Delete row")) {
303 database.setEditable(false);
304 table.setEditable(true);
305 id.setEditable(true);
306 name.setEditable(false);
307 number.setEditable(false);
308 }
309
310 if(comboBox.getSelectedItem().equals("Drop table")) {
311 database.setEditable(false);
312 table.setEditable(true);
313 id.setEditable(false);
314 name.setEditable(false);
315 number.setEditable(false);
316 }
317
318 if(comboBox.getSelectedItem().equals("Drop database")) {
319 database.setEditable(true);
320 table.setEditable(false);
321 id.setEditable(false);
322 name.setEditable(false);
323 number.setEditable(false);
324 }
325
326 if(comboBox.getSelectedItem().equals("")){
327 database.setEditable(false);
328 table.setEditable(false);
329 id.setEditable(false);
330 name.setEditable(false);
331 number.setEditable(false);
332 }
333 }
334 });
335 comboBox.setBounds(39, 226, 218, 45);
336 frame.getContentPane().add(comboBox);
337
338 JButton btnNewButton = new JButton("Confirm action");
339 btnNewButton.addActionListener(new ActionListener() {
340 public void actionPerformed(ActionEvent arg0) {
341 if(comboBox.getSelectedItem().equals("Create database") && !(database.getText().isEmpty())) {
342 myDataBase myDB = new myDataBase();
343 myDB.createDatabase(database.getText());
344 myDB.closeConnection();
345 JOptionPane.showMessageDialog(frame, "Database created!");
346 }
347 else if(comboBox.getSelectedItem().equals("Create table") && !(table.getText().isEmpty())) {
348 myDataBase myDB = new myDataBase();
349 myDB.getConnection(database.getText());
350 myDB.createTable(table.getText());
351 myDB.closeConnection();
352 JOptionPane.showMessageDialog(frame, "Table created!");
353 }
354 else if(comboBox.getSelectedItem().equals("Insert row") && !(table.getText().isEmpty()) && !(name.getText().isEmpty()) && !(number.getText().isEmpty())) {
355 myDataBase myDB = new myDataBase();
356 myDB.getConnection(database.getText());
357 Calendar calendar = Calendar.getInstance();
358 myDB.insertRow(table.getText(), name.getText(), calendar, Integer.parseInt(number.getText()));
359 myDB.closeConnection();
360 JOptionPane.showMessageDialog(frame, "New row inserted!");
361 }
362 else if(comboBox.getSelectedItem().equals("Update row") && !(table.getText().isEmpty()) && !(id.getText().isEmpty()) && !(name.getText().isEmpty()) && !(number.getText().isEmpty())) {
363 myDataBase myDB = new myDataBase();
364 myDB.getConnection(database.getText());
365 Calendar calendar = Calendar.getInstance();
366 myDB.updateRow(table.getText(),id.getText(), name.getText(), calendar, Integer.parseInt(number.getText()));
367 myDB.closeConnection();
368 JOptionPane.showMessageDialog(frame, "Row updated!");
369 }
370 else if(comboBox.getSelectedItem().equals("Delete row") && !(table.getText().isEmpty()) && !(id.getText().isEmpty())) {
371 myDataBase myDB = new myDataBase();
372 myDB.getConnection(database.getText());
373 myDB.deleteRow(table.getText(),id.getText());
374 myDB.closeConnection();
375 JOptionPane.showMessageDialog(frame, "Row deleted!");
376 }
377 else if(comboBox.getSelectedItem().equals("Drop table") && !(table.getText().isEmpty())) {
378 myDataBase myDB = new myDataBase();
379 myDB.getConnection(database.getText());
380 myDB.dropTable(table.getText());
381 myDB.closeConnection();
382 JOptionPane.showMessageDialog(frame, "Table dropped!");
383 }
384 else if(comboBox.getSelectedItem().equals("Drop database") && !(database.getText().isEmpty())) {
385 myDataBase myDB = new myDataBase();
386 myDB.getConnection(database.getText());
387 myDB.dropDatabase(database.getText());
388 myDB.closeConnection();
389 JOptionPane.showMessageDialog(frame, "Database dropped!");
390 }
391 else JOptionPane.showMessageDialog(frame, "Error!");
392
393 }
394 });
395 btnNewButton.setBounds(39, 280, 218, 45);
396 frame.getContentPane().add(btnNewButton);
397 }
398}