· 4 years ago · Jun 28, 2021, 12:54 PM
1package de.ifdgmbh.mad.easydbms.controller;
2
3import java.io.File;
4import java.sql.Connection;
5import java.sql.DatabaseMetaData;
6import java.sql.DriverManager;
7import java.sql.PreparedStatement;
8import java.sql.ResultSet;
9import java.sql.ResultSetMetaData;
10import java.sql.SQLException;
11import java.util.ArrayList;
12import java.util.LinkedList;
13import java.util.Optional;
14import java.util.logging.Logger;
15import javafx.beans.property.ReadOnlyObjectWrapper;
16import javafx.collections.FXCollections;
17import javafx.collections.ObservableList;
18import javafx.fxml.FXML;
19import javafx.geometry.Pos;
20import javafx.scene.Scene;
21import javafx.scene.control.Alert;
22import javafx.scene.control.Button;
23import javafx.scene.control.ButtonType;
24import javafx.scene.control.CheckBox;
25import javafx.scene.control.ChoiceBox;
26import javafx.scene.control.Label;
27import javafx.scene.control.ListView;
28import javafx.scene.control.Tab;
29import javafx.scene.control.TableColumn;
30import javafx.scene.control.TableView;
31import javafx.scene.control.TextArea;
32import javafx.scene.control.TextField;
33import javafx.scene.control.TextInputDialog;
34import javafx.scene.control.cell.PropertyValueFactory;
35import javafx.scene.layout.AnchorPane;
36import javafx.scene.layout.VBox;
37import javafx.scene.text.Font;
38import javafx.stage.FileChooser;
39import javafx.stage.Modality;
40import javafx.stage.Stage;
41import jfxtras.styles.jmetro.JMetro;
42import jfxtras.styles.jmetro.Style;
43
44public class MyController {
45 @FXML
46 private AnchorPane mypane;
47 @FXML
48 private AnchorPane myDBPane;
49 @FXML
50 private AnchorPane myTablePane;
51 @FXML
52 private Button chooseDbButton, newDbButton, discButton;
53 @FXML
54 private Label activeDbLabel;
55 @FXML
56 private Button newTableButton, commandButton;
57 @FXML
58 private Button strAddCoButton, strRefrButton, strDelCoButton, strEditCoButton, strDelTButton;
59 @FXML
60 private Button dataRefrButton, dataInsButton, dataDelButton, dataEditButton;
61 @FXML
62 private Button closeButton, minButton;
63 @FXML
64 private Tab dataTab;
65 @FXML
66 private ListView<String> tableListView = new ListView<>();
67 @FXML
68 private TableView<TableColumnVal> strTableView;
69 @FXML
70 private TableView<ObservableList<String>> dataTableView;
71
72 // stores all the values stored in the columns of dataTableView
73 private ObservableList<ObservableList<String>> dataList = FXCollections.observableArrayList();
74 // List that stores all the TableColumns needed for dataTableView
75 private LinkedList<TableColumn<ObservableList<String>, String>> myDataColumns = new LinkedList<>();
76
77 // columns
78 @FXML
79 private TableColumn<TableColumnVal, String> strVal;
80 @FXML
81 private TableColumn<TableColumnVal, String> strNull;
82 @FXML
83 private TableColumn<TableColumnVal, String> strUnique;
84 @FXML
85 private TableColumn<TableColumnVal, String> strFKey;
86 @FXML
87 private TableColumn<TableColumnVal, String> strPKey;
88 @FXML
89 private TableColumn<TableColumnVal, String> strDataType;
90 @FXML
91 private TableColumn<TableColumnVal, String> strName;
92 @FXML
93 private TableColumn<TableColumnVal, String> strNo;
94
95 // string to compare when looking for numbers/letters only
96 final static String regex = "\\d+";
97
98 // Logger
99 private static Logger myLog = Logger.getLogger("EasyDbmsLog");
100
101 // JDBC VARs
102 private String dbURL = null;
103 private Connection myConn = null;
104
105 boolean closing = false;
106
107 // "pre-start"
108 public void initialize() {
109 // initialize cell values
110 strNo.setCellValueFactory(new PropertyValueFactory<>("Num"));
111 strName.setCellValueFactory(new PropertyValueFactory<>("Name"));
112 strDataType.setCellValueFactory(new PropertyValueFactory<>("DataType"));
113 strPKey.setCellValueFactory(new PropertyValueFactory<>("pKey"));
114 strFKey.setCellValueFactory(new PropertyValueFactory<>("fKey"));
115 strUnique.setCellValueFactory(new PropertyValueFactory<>("unique"));
116 strNull.setCellValueFactory(new PropertyValueFactory<>("Null"));
117 strVal.setCellValueFactory(new PropertyValueFactory<>("defVal"));
118
119 closeButton.setStyle("-fx-background-color: #FF605C");
120 ;
121 minButton.setStyle("-fx-background-color: #00CA4E");
122 mypane.setStyle("-fx-background-color:linear-gradient(to left top,#00E0FF 0%,#A0F3FF 60%, white 40%);\r\n"
123 + " -fx-border-color:black;");
124 myDBPane.setStyle("-fx-background-color: #ffffff00");
125 myTablePane.setStyle("-fx-background-color: #ffffff00");
126 tableListView.setStyle("-fx-background-color: #ffffff00");
127
128 tableListView.getSelectionModel().selectedItemProperty().addListener(e -> {
129 if (closing)
130 return;
131 strRefrButton.setDisable(false);
132 strAddCoButton.setDisable(false);
133 strDelTButton.setDisable(false);
134 dataRefrButton.setDisable(false);
135 dataInsButton.setDisable(false);
136 strRefrButtonClicked();
137 });
138
139 strTableView.getSelectionModel().selectedItemProperty().addListener(e -> {
140 if (closing)
141 return;
142 strEditCoButton.setDisable(false);
143 strDelCoButton.setDisable(false);
144 });
145
146 dataTableView.getSelectionModel().selectedItemProperty().addListener(e -> {
147 if (closing)
148 return;
149 dataDelButton.setDisable(false);
150 dataEditButton.setDisable(false);
151 });
152 }
153
154 /**
155 * Choose DataBase will make the user to choose a sql-database file & tries to
156 * connect to this file
157 */
158 public void chooseDbButtonClick() {
159 if (myConn != null) {
160 showJMetroDialog(Alert.AlertType.WARNING, "There is still an active connection to a database!");
161 return;
162 }
163
164 File myDB = null;
165 FileChooser fileChooser = new FileChooser();
166 fileChooser.setTitle("Please choose your file!");
167 Stage mystage = null;
168 fileChooser.getExtensionFilters().addAll(
169 new FileChooser.ExtensionFilter("All SQLite databases", "*.db", "*.sdb", "*.sqlite"),
170 new FileChooser.ExtensionFilter("SQLite3", "*.db3", "*.s3db", "*.sqlite3", "*.sl3"),
171 new FileChooser.ExtensionFilter("SQLite2", "*.db2", "*.s2db", "*.sqlite2", "*.sl2"));
172 myDB = fileChooser.showOpenDialog(mystage);
173 dbURL = "jdbc:sqlite:" + myDB.getAbsolutePath();
174 // connect to database
175 ResultSet rs = null;
176 try {
177 myConn = DriverManager.getConnection(dbURL);
178 activeDbLabel.setText(activeDbLabel.getText() + "\n" + myDB.getName());
179 // load all tables
180 DatabaseMetaData md = myConn.getMetaData();
181 rs = md.getTables(null, null, "%", null);
182 while (rs.next()) {
183 tableListView.getItems().add(rs.getString(3));
184 }
185 } catch (SQLException e) {
186 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
187 } finally {
188 try {
189 if (rs != null)
190 rs.close();
191 } catch (SQLException e) {
192 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
193 }
194 }
195 }
196
197 /**
198 * Create New DB will create a new sqlite database file and try to connect to it
199 */
200 public void newDbButtonClick() {
201 if (myConn != null) {
202 showJMetroDialog(Alert.AlertType.WARNING, "There is still an active connection to a database!");
203 return;
204 }
205 File myDB = null;
206 FileChooser fileChooser = new FileChooser();
207 fileChooser.setTitle("Save file!");
208 Stage mystage = null;
209 fileChooser.getExtensionFilters().addAll(new FileChooser.ExtensionFilter("SQLite Database", "*.db"));
210 myDB = fileChooser.showSaveDialog(mystage);
211 // create database
212 try (Connection conn = DriverManager.getConnection("jdbc:sqlite:" + myDB.getAbsolutePath())) {
213 if (conn != null) {
214 @SuppressWarnings("unused")
215 DatabaseMetaData meta = conn.getMetaData();
216 conn.close();
217 }
218 } catch (SQLException e) {
219 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
220 } finally {
221 // connect to database
222 dbURL = "jdbc:sqlite:" + myDB.getAbsolutePath();
223 try {
224 myConn = DriverManager.getConnection(dbURL);
225 activeDbLabel.setText(activeDbLabel.getText() + "\n" + myDB.getName());
226 } catch (SQLException e) {
227 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
228 }
229 }
230
231 }
232
233 /**
234 * Disconnect will disconnect from the current database file
235 * <p>
236 * resets @strTableView, @dataTableView, @dataList, @tableListView
237 */
238 public void discButtonClick() {
239 // disconnect database if possible
240 if (myConn != null) {
241 try {
242 myConn.close();
243 myConn = null;
244 myLog.info("Successfully disconnected!");
245 activeDbLabel.setText("Active Database:");
246 // clear table list
247 tableListView.getItems().removeAll(tableListView.getItems());
248 } catch (SQLException e) {
249 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
250 } finally {
251 strTableView.getItems().removeAll(strTableView.getItems());
252 dataTableView.getItems().removeAll(dataTableView.getItems());
253 dataList.clear();
254 myDataColumns.clear();
255 strRefrButton.setDisable(true);
256 strAddCoButton.setDisable(true);
257 strDelCoButton.setDisable(true);
258 strEditCoButton.setDisable(true);
259 strDelTButton.setDisable(true);
260 dataRefrButton.setDisable(true);
261 dataInsButton.setDisable(true);
262 dataDelButton.setDisable(true);
263 dataEditButton.setDisable(true);
264 }
265 } else {
266 myLog.warning("No active connection!");
267 }
268 }
269
270 /**
271 * New Table adds a table to the active database needs input: name for table,
272 * the first column name + data type (primary key is optional)
273 */
274 public void newTableButtonClick() {
275 if (!checkConnection()) {
276 return;
277 }
278 // open dialogbox
279 // ask for table name, column name, data type
280 Stage popUp = new Stage();
281 // make no change in main while this is open
282 popUp.initModality(Modality.APPLICATION_MODAL);
283 popUp.setTitle("Please fill out!");
284 popUp.setMinHeight(250);
285 popUp.setMinWidth(400);
286 // add all controls
287 TextField tableName = new TextField();
288 tableName.setMaxWidth(popUp.getMinWidth() - 100);
289 tableName.setPromptText("Table Name");
290 TextField columnName = new TextField();
291 columnName.setMaxWidth(popUp.getMinWidth() - 100);
292 columnName.setPromptText("Column Name");
293 Label myLabel = new Label("Data Type:");
294 myLabel.setFont(new Font("System", 14));
295 ChoiceBox<String> dataTypeChoice = createDataTypeChoiceBox();
296 dataTypeChoice.setMaxWidth(tableName.getMaxWidth() / 2);
297 CheckBox pkCheck = new CheckBox("Primary Key");
298 pkCheck.setFont(new Font("System", 13));
299 Button enterButton = new Button("CONFIRM");
300 enterButton.setMaxSize(tableName.getMaxWidth() / 2, 75);
301 // set action
302 enterButton.setOnAction(e -> {
303 String dataType;
304 if (dataTypeChoice.getSelectionModel().getSelectedItem() == null) {
305 showJMetroDialog(Alert.AlertType.WARNING, "Please select a data type!");
306 return;
307 } else {
308 dataType = dataTypeChoice.getSelectionModel().getSelectedItem();
309 }
310 if (tableName.getText().isBlank() || columnName.getText().isBlank() || dataType.isBlank()) {
311 if (tableName.getText().isBlank()) {
312 tableName.setText("Enter value");
313 tableName.setStyle("-fx-text-fill: red");
314 }
315 if (columnName.getText().isBlank()) {
316 columnName.setText("Enter value");
317 columnName.setStyle("-fx-text-fill: red");
318 }
319 } else {
320 String sql = "CREATE TABLE IF NOT EXISTS " + tableName.getText().trim() + " ("
321 + columnName.getText().trim() + " " + dataType.trim();
322 if (pkCheck.isSelected()) {
323 sql += " PRIMARY KEY";
324 }
325 sql += ")";
326 PreparedStatement myStatement = null;
327 ResultSet rs = null;
328 try {
329 myStatement = myConn.prepareStatement(sql);
330 DatabaseMetaData md = myConn.getMetaData();
331 rs = md.getTables(null, null, "%", null);
332 while (rs.next()) {
333 if (rs.getString(3).contentEquals(tableName.getText().trim())) {
334 showJMetroDialog(Alert.AlertType.WARNING, "Table " + rs.getString(3) + " already exists!");
335 return;
336 }
337 }
338 myLog.info("Executing statement: '" + sql + "'");
339 myStatement.execute();
340 // add table to list
341 tableListView.getItems().add(tableName.getText().trim());
342 // close dialog
343 popUp.close();
344 } catch (SQLException g) {
345 showJMetroDialog(Alert.AlertType.WARNING, g.getMessage());
346 } finally {
347 try {
348 myStatement.close();
349 rs.close();
350 } catch (SQLException e1) {
351 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
352 }
353 }
354 }
355 });
356 // Vbox will store everything
357 VBox vBox = new VBox();
358 vBox.setStyle("-fx-background-color: white");
359 vBox.setSpacing(5);
360 vBox.getChildren().addAll(tableName, columnName, myLabel, dataTypeChoice, pkCheck, enterButton);
361 vBox.setAlignment(Pos.CENTER);
362 // open
363 Scene scene = new Scene(vBox);
364 JMetro jMetro = new JMetro(Style.LIGHT);
365 jMetro.setScene(scene);
366 popUp.setScene(scene);
367 popUp.showAndWait();
368
369 }
370
371 /**
372 * REFRESH (Structure) refreshes the structure table of the selected database
373 * table
374 */
375 public void strRefrButtonClicked() {
376 // list data from selected table
377 // database connected?
378 if (!checkConnection()) {
379 return;
380 }
381
382 // name of active table
383 // none?
384 if (!checkActiveTable()) {
385 return;
386 }
387
388 String myTable = tableListView.getSelectionModel().getSelectedItem();
389
390 // delete old content
391 strTableView.getItems().clear();
392
393 // iterate threw table and get every column
394 PreparedStatement stmnt = null;
395 ResultSet rs = null;
396 try {
397 stmnt = myConn.prepareStatement("SELECT * FROM " + myTable);
398 rs = stmnt.executeQuery();
399 ResultSetMetaData rsmd = rs.getMetaData();
400 //
401 // TODO:
402 // implement unique & not null
403 //
404 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
405
406 TableColumnVal mytableCoVal = new TableColumnVal(i, rsmd.getColumnName(i), rsmd.getColumnTypeName(i),
407 checkPK(rsmd.getColumnName(i), myTable, myConn),
408 checkFK(rsmd.getColumnName(i), myTable, myConn),
409 checkUnique(rsmd.getColumnName(i), myTable, myConn),
410 checkNULL(rsmd.getColumnName(i), myTable, myConn), "");
411 strTableView.getItems().add(mytableCoVal);
412 }
413 } catch (SQLException e) {
414 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
415 } finally {
416 try {
417 if (stmnt != null)
418 stmnt.close();
419 if (rs != null)
420 rs.close();
421
422 } catch (SQLException e) {
423 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
424 }
425 }
426 }
427
428 /**
429 * ADD COLUMN adds a column to the data table and along with that a new value to
430 * the structure table
431 */
432 public void strAddCoButtonClicked() {
433 if (!checkConnection()) {
434 return;
435 }
436 // name of active table
437 // none?
438 if (!checkActiveTable()) {
439 return;
440 }
441 String myTable = tableListView.getSelectionModel().getSelectedItem();
442 // add a column to selected table
443 // open dialogbox
444 // column name, data type, pk, fk, unique, not null, defval
445 Stage popUp = new Stage();
446 // make no change in main while this is open
447 popUp.initModality(Modality.APPLICATION_MODAL);
448 popUp.setTitle("Please fill out!");
449 popUp.setMinHeight(300);
450 popUp.setMinWidth(400);
451 // add all controls
452 TextField columnName = new TextField();
453 columnName.setPromptText("Enter column name");
454 columnName.setMaxWidth(popUp.getMinWidth() - 100);
455 Label myLabel = new Label("Data Type:");
456 myLabel.setFont(new Font("System", 14));
457 ChoiceBox<String> dataTypeChoice = createDataTypeChoiceBox();
458 dataTypeChoice.setMaxWidth(columnName.getMaxWidth() / 2);
459 TextField defval = new TextField();
460 defval.setMaxWidth(popUp.getMinWidth() - 100);
461 defval.setPromptText("Enter Default value");
462 CheckBox pkCheck = new CheckBox("Primary Key");
463 pkCheck.setFont(new Font("System", 13));
464 CheckBox fkCheck = new CheckBox("Foreign Key");
465 fkCheck.setFont(new Font("System", 13));
466 CheckBox uniqueCheck = new CheckBox("Unique ");
467 uniqueCheck.setFont(new Font("System", 13));
468 CheckBox nullCheck = new CheckBox("Not NULL ");
469 nullCheck.setFont(new Font("System", 13));
470 Button enterButton = new Button("Confirm");
471 // set action
472 enterButton.setOnAction(e -> {
473 String dataType;
474 if (dataTypeChoice.getSelectionModel().getSelectedItem() == null) {
475 showJMetroDialog(Alert.AlertType.WARNING, "Please select a data type!");
476 return;
477 } else {
478 dataType = dataTypeChoice.getSelectionModel().getSelectedItem();
479 }
480 if (columnName.getText().isBlank() || dataType.isBlank()) {
481 if (columnName.getText().isBlank()) {
482 columnName.setText("Enter value");
483 columnName.setStyle("-fx-text-fill: red");
484 } else if (pkCheck.isSelected() && fkCheck.isSelected()) {
485 showJMetroDialog(Alert.AlertType.WARNING, "Uncheck private or foreign key!");
486 }
487 } else {
488 String begin = "BEGIN TRANSACTION;";
489 String copy = "CREATE TABLE " + myTable + "bup (";
490 ObservableList<TableColumnVal> tempList = strTableView.getItems();
491 String tempStr = "";
492 String tempStr2 = "";
493
494 for (TableColumnVal o : tempList) {
495 // copy columns
496 tempStr += o.getName() + " " + o.getDataType();
497 // check if primary key
498 if (checkPK(o.getName(), myTable, myConn)) {
499 tempStr += " PRIMARY KEY, ";
500 } else {
501 tempStr += ", ";
502 }
503 tempStr2 += o.getName() + ", ";
504 }
505 tempStr += columnName.getText().trim() + " " + dataType + " ";
506 if (pkCheck.isSelected()) {
507 tempStr += " PRIMARY KEY";
508 }
509 if (fkCheck.isSelected()) {
510 boolean tablebool = false;
511 boolean columnbool = false;
512 TextInputDialog tID = new TextInputDialog();
513 tID.setHeaderText("Enter name of table!");
514 tID.showAndWait();
515 String table = tID.getEditor().getText().trim();
516 String column = null;
517 // does table exist?
518 for (String mystr : tableListView.getItems()) {
519 if (mystr.contentEquals(table)) {
520 tablebool = true;
521 tID.setHeaderText("Enter name of column!");
522 tID.showAndWait();
523 column = tID.getEditor().getText().trim();
524 // does column for table exist?
525 PreparedStatement stmnt = null;
526 ResultSet rs = null;
527 try {
528 stmnt = myConn.prepareStatement("SELECT * FROM " + table);
529 rs = stmnt.executeQuery();
530 ResultSetMetaData rsmd = rs.getMetaData();
531 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
532 if (rsmd.getColumnName(i).contentEquals(column)) {
533 columnbool = true;
534 }
535 }
536 } catch (SQLException e2) {
537 showJMetroDialog(Alert.AlertType.WARNING, e2.getMessage());
538 } finally {
539 try {
540 if (stmnt != null)
541 stmnt.close();
542 if (rs != null)
543 rs.close();
544
545 } catch (SQLException e3) {
546 showJMetroDialog(Alert.AlertType.WARNING, e3.getMessage());
547 }
548 }
549 if (!columnbool) {
550 showJMetroDialog(Alert.AlertType.WARNING, "Wrong input on column!");
551 return;
552 }
553 }
554 }
555 if (!tablebool) {
556 showJMetroDialog(Alert.AlertType.WARNING, "Wrong input on table!");
557 return;
558 }
559 tempStr += ", FOREIGN KEY(" + columnName.getText().trim() + ") REFERENCES " + table + "(" + column
560 + ")";
561 }
562 if (nullCheck.isSelected()) {
563 tempStr += " NOT NULL";
564 }
565 if (uniqueCheck.isSelected()) {
566 tempStr += " UNIQUE";
567 }
568 tempStr2 = tempStr2.substring(0, tempStr2.length() - 2);
569
570 // check if other columns have foreign keys
571 for (TableColumnVal val : tempList) {
572 ResultSet rs = null;
573 try {
574 rs = myConn.getMetaData().getImportedKeys(null, null, myTable);
575 while (rs.next()) {
576 // 3,4,8
577 if (rs.getString(8).contentEquals(val.getName())) {
578 tempStr += ", FOREIGN KEY(" + val.getName() + ") REFERENCES " + rs.getString(3) + "("
579 + rs.getString(4) + ")";
580 }
581 }
582 } catch (SQLException e1) {
583 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
584 } finally {
585 try {
586 rs.close();
587 } catch (SQLException e1) {
588 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
589 }
590 }
591 }
592 //
593 copy += tempStr + ");";
594 String insert = "INSERT INTO " + myTable + "bup SELECT " + tempStr2 + ", NULL" + " FROM " + myTable
595 + ";";
596 String drop = "DROP TABLE " + myTable + ";";
597 String alter = "ALTER TABLE " + myTable + "bup RENAME TO " + myTable + ";";
598 String commit = "COMMIT;";
599 PreparedStatement stmntBegin = null;
600 PreparedStatement stmntCopy = null;
601 PreparedStatement stmntInsert = null;
602 PreparedStatement stmntDrop = null;
603 PreparedStatement stmntAlter = null;
604 PreparedStatement stmntCommit = null;
605 try {
606 stmntBegin = myConn.prepareStatement(begin);
607 myLog.info("Executing statement: '" + begin + "'");
608 stmntBegin.execute();
609 stmntCopy = myConn.prepareStatement(copy);
610 myLog.info("Executing statement: '" + copy + "'");
611 stmntCopy.execute();
612 stmntInsert = myConn.prepareStatement(insert);
613 myLog.info("Executing statement: '" + insert + "'");
614 stmntInsert.executeUpdate();
615 stmntDrop = myConn.prepareStatement(drop);
616 myLog.info("Executing statement: '" + drop + "'");
617 stmntDrop.executeUpdate();
618 stmntAlter = myConn.prepareStatement(alter);
619 myLog.info("Executing statement: '" + alter + "'");
620 stmntAlter.execute();
621 stmntCommit = myConn.prepareStatement(commit);
622 myLog.info("Executing statement: '" + commit + "'");
623 stmntCommit.execute();
624 // close dialog
625 popUp.close();
626 } catch (SQLException g) {
627 showJMetroDialog(Alert.AlertType.WARNING, g.getMessage());
628 } finally {
629 try {
630 stmntBegin.close();
631 stmntCopy.close();
632 stmntInsert.close();
633 stmntDrop.close();
634 stmntAlter.close();
635 stmntCommit.close();
636 strRefrButtonClicked();
637 } catch (SQLException e1) {
638 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
639 }
640 }
641 }
642
643 });
644 // Vbox will store everything
645 VBox vBox = new VBox();
646 vBox.setStyle("-fx-background-color: white");
647 vBox.setSpacing(5);
648 vBox.getChildren().addAll(columnName, myLabel, dataTypeChoice, defval, pkCheck, fkCheck, uniqueCheck, nullCheck,
649 enterButton);
650 vBox.setAlignment(Pos.CENTER);
651 // open
652 Scene scene = new Scene(vBox);
653 JMetro jMetro = new JMetro(Style.LIGHT);
654 jMetro.setScene(scene);
655 popUp.setScene(scene);
656 popUp.showAndWait();
657 }
658
659 /**
660 * EDIT COLUMN edits the selected row in structure table and along that modifies
661 * that certain column in data table
662 */
663 public void strEditCoButtonClicked() {
664 // steps: create table copy --> insert every column and edit the one selected
665 // --> drop
666 // original table --> rename copy to original
667 if (!checkConnection()) {
668 return;
669 }
670 // name of active table
671 // none?
672 if (!checkActiveTable()) {
673 return;
674 }
675 String myTable = tableListView.getSelectionModel().getSelectedItem();
676
677 // name of active column
678 // none?
679 if (strTableView.getSelectionModel().getSelectedItem() == null) {
680 showJMetroDialog(Alert.AlertType.WARNING, "No column selected!");
681 return;
682 }
683 String activeColumn = strTableView.getSelectionModel().getSelectedItem().getName();
684
685 // open dialogbox
686 // column name, data type, pk, fk, unique, not null, defval
687 Stage popUp = new Stage();
688 // make no change in main while this is open
689 popUp.initModality(Modality.APPLICATION_MODAL);
690 popUp.setTitle("Please fill out!");
691 popUp.setMinHeight(300);
692 popUp.setMinWidth(400);
693 // add all controls
694 TextField columnName = new TextField(activeColumn);
695 columnName.setPromptText("Enter column name");
696 columnName.setMaxWidth(popUp.getMinWidth() - 100);
697 Label myLabel = new Label("Data Type:");
698 myLabel.setFont(new Font("System", 14));
699 ChoiceBox<String> dataTypeChoice = createDataTypeChoiceBox();
700 dataTypeChoice.setValue(strTableView.getSelectionModel().getSelectedItem().getDataType());
701 dataTypeChoice.setMaxWidth(columnName.getMaxWidth() / 2);
702 dataTypeChoice.setDisable(true);
703 TextField defval = new TextField();
704 defval.setMaxWidth(popUp.getMinWidth() - 100);
705 defval.setPromptText("Enter Default value");
706 CheckBox pkCheck = new CheckBox("Primary Key");
707 pkCheck.setFont(new Font("System", 13));
708 CheckBox fkCheck = new CheckBox("Foreign Key");
709 fkCheck.setFont(new Font("System", 13));
710 CheckBox uniqueCheck = new CheckBox("Unique ");
711 uniqueCheck.setFont(new Font("System", 13));
712 CheckBox nullCheck = new CheckBox("Not NULL ");
713 nullCheck.setFont(new Font("System", 13));
714 Button enterButton = new Button("Confirm");
715 // set action
716 enterButton.setOnAction(e -> {
717 String dataType;
718 if (dataTypeChoice.getSelectionModel().getSelectedItem() == null) {
719 showJMetroDialog(Alert.AlertType.WARNING, "Please select a data type!");
720 return;
721 } else {
722 dataType = dataTypeChoice.getSelectionModel().getSelectedItem();
723 }
724 if (columnName.getText().isBlank() || dataType.isBlank()) {
725 if (columnName.getText().isBlank()) {
726 columnName.setText("Enter value");
727 columnName.setStyle("-fx-text-fill: red");
728 }
729 } else {
730 // TODO:
731 // implement unique & not null
732
733 // edit the selected column
734 // create copy
735 String begin = "BEGIN TRANSACTION;";
736 String copy = "CREATE TABLE " + myTable + "bup (";
737 ObservableList<TableColumnVal> tempList = strTableView.getItems();
738 String tempStr = "";
739 String tempStr2 = "";
740
741 for (TableColumnVal o : tempList) {
742 if (o.getName().contentEquals(activeColumn)) {
743 // change if column is selected
744 tempStr += columnName.getText().trim() + " " + dataType.trim() + ", ";
745 tempStr2 += o.getName() + ", ";
746 } else {
747 // copy not selected columns
748 tempStr += o.getName() + " " + o.getDataType();
749 // check if primary key
750 if (checkPK(o.getName(), myTable, myConn)) {
751 tempStr += " PRIMARY KEY, ";
752 } else {
753 tempStr += ", ";
754 }
755 tempStr2 += o.getName() + ", ";
756 }
757 }
758 // cut last ", "
759 tempStr = tempStr.substring(0, tempStr.length() - 2);
760 // check if other columns have foreign keys
761 for (TableColumnVal val : tempList) {
762 ResultSet rs = null;
763 try {
764 rs = myConn.getMetaData().getImportedKeys(null, null, myTable);
765 while (rs.next()) {
766 // 3,4,8
767 if (rs.getString(8).contentEquals(val.getName())) {
768 tempStr += ", FOREIGN KEY(" + val.getName() + ") REFERENCES " + rs.getString(3) + "("
769 + rs.getString(4) + ")";
770 }
771 }
772 } catch (SQLException e1) {
773 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
774 } finally {
775 try {
776 rs.close();
777 } catch (SQLException e1) {
778 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
779 }
780 }
781 }
782 //
783 tempStr2 = tempStr2.substring(0, tempStr2.length() - 2);
784 copy += tempStr + ");";
785 String insert = "INSERT INTO " + myTable + "bup SELECT " + tempStr2 + " FROM " + myTable + ";";
786 String drop = "DROP TABLE " + myTable + ";";
787 String alter = "ALTER TABLE " + myTable + "bup RENAME TO " + myTable + ";";
788 String commit = "COMMIT;";
789 PreparedStatement stmntBegin = null;
790 PreparedStatement stmntCopy = null;
791 PreparedStatement stmntInsert = null;
792 PreparedStatement stmntDrop = null;
793 PreparedStatement stmntAlter = null;
794 PreparedStatement stmntCommit = null;
795 try {
796 stmntBegin = myConn.prepareStatement(begin);
797 myLog.info("Executing statement: '" + begin + "'");
798 stmntBegin.execute();
799 stmntCopy = myConn.prepareStatement(copy);
800 myLog.info("Executing statement: '" + copy + "'");
801 stmntCopy.execute();
802 stmntInsert = myConn.prepareStatement(insert);
803 myLog.info("Executing statement: '" + insert + "'");
804 stmntInsert.executeUpdate();
805 stmntDrop = myConn.prepareStatement(drop);
806 myLog.info("Executing statement: '" + drop + "'");
807 stmntDrop.executeUpdate();
808 stmntAlter = myConn.prepareStatement(alter);
809 myLog.info("Executing statement: '" + alter + "'");
810 stmntAlter.execute();
811 stmntCommit = myConn.prepareStatement(commit);
812 myLog.info("Executing statement: '" + commit + "'");
813 stmntCommit.execute();
814 popUp.close();
815 } catch (SQLException q) {
816 showJMetroDialog(Alert.AlertType.WARNING, q.getMessage());
817 } finally {
818 // close all connections
819 try {
820 stmntBegin.close();
821 stmntCopy.close();
822 stmntInsert.close();
823 stmntDrop.close();
824 stmntAlter.close();
825 stmntCommit.close();
826 } catch (SQLException e1) {
827 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
828 }
829 // update list of columns
830 strRefrButtonClicked();
831 }
832 }
833 });
834 // Vbox will store everything
835 VBox vBox = new VBox();
836 vBox.setStyle("-fx-background-color: white");
837 vBox.setSpacing(5);
838 vBox.getChildren().addAll(columnName, myLabel, dataTypeChoice, defval, pkCheck, fkCheck, uniqueCheck, nullCheck,
839 enterButton);
840 vBox.setAlignment(Pos.CENTER);
841 // open
842 Scene scene = new Scene(vBox);
843 JMetro jMetro = new JMetro(Style.LIGHT);
844 jMetro.setScene(scene);
845 popUp.setScene(scene);
846 popUp.showAndWait();
847
848 }
849
850 /**
851 * DELETE COLUMN deletes the selected row in structure table and the fitting
852 * column in data table
853 * <p>
854 * will only work if there are at least 2 columns (rows in structure table) left
855 */
856 public void strDelCoButtonClicked() {
857 // steps: create table copy --> insert every column except the one --> drop
858 // original table --> rename copy to original
859 if (!checkConnection()) {
860 return;
861 }
862 // name of active table
863 // none?
864 if (!checkActiveTable()) {
865 return;
866 }
867 String myTable = tableListView.getSelectionModel().getSelectedItem();
868
869 // name of active column
870 // none?
871 if (strTableView.getSelectionModel().getSelectedItem() == null) {
872 showJMetroDialog(Alert.AlertType.WARNING, "No column selected!");
873 return;
874 }
875 String activeColumn = strTableView.getSelectionModel().getSelectedItem().getName();
876
877 // only column?
878 if (strTableView.getItems().size() < 2) {
879 showJMetroDialog(Alert.AlertType.WARNING, "Table will be empty, please delete the table instead!");
880 return;
881 }
882
883 if (showJMetroChoiceDialog(activeColumn) == false) {
884 return;
885 }
886
887 // delete selected column
888 // create copy
889 String begin = "BEGIN TRANSACTION;";
890 String copy = "CREATE TABLE " + myTable + "bup (";
891 ObservableList<TableColumnVal> tempList = strTableView.getItems();
892 String tempStr = "";
893 String tempStr2 = "";
894 for (TableColumnVal o : tempList) {
895 if (o.getName().contentEquals(activeColumn)) {
896 // do not copy selected column (active column)
897 } else {
898 // copy not selected columns
899 tempStr += o.getName() + " " + o.getDataType();
900 // check if primary key
901 if (checkPK(o.getName(), myTable, myConn)) {
902 tempStr += " PRIMARY KEY, ";
903 } else {
904 tempStr += ", ";
905 }
906 tempStr2 += o.getName() + ", ";
907 }
908 }
909 // cut last ", "
910 tempStr = tempStr.substring(0, tempStr.length() - 2);
911 // check if other columns have foreign keys
912 for (TableColumnVal val : tempList) {
913 ResultSet rs = null;
914 try {
915 rs = myConn.getMetaData().getImportedKeys(null, null, myTable);
916 while (rs.next()) {
917 // 3,4,8
918 if (rs.getString(8).contentEquals(val.getName())) {
919 tempStr += ", FOREIGN KEY(" + val.getName() + ") REFERENCES " + rs.getString(3) + "("
920 + rs.getString(4) + ")";
921 }
922 }
923 } catch (SQLException e1) {
924 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
925 } finally {
926 try {
927 rs.close();
928 } catch (SQLException e1) {
929 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
930 }
931 }
932 }
933 tempStr2 = tempStr2.substring(0, tempStr2.length() - 2);
934 copy += tempStr + ");";
935 String insert = "INSERT INTO " + myTable + "bup SELECT " + tempStr2 + " FROM " + myTable + ";";
936 String drop = "DROP TABLE " + myTable + ";";
937 String alter = "ALTER TABLE " + myTable + "bup RENAME TO " + myTable + ";";
938 String commit = "COMMIT;";
939 PreparedStatement stmntBegin = null;
940 PreparedStatement stmntCopy = null;
941 PreparedStatement stmntInsert = null;
942 PreparedStatement stmntDrop = null;
943 PreparedStatement stmntAlter = null;
944 PreparedStatement stmntCommit = null;
945 try {
946 stmntBegin = myConn.prepareStatement(begin);
947 myLog.info("Executing statement: '" + begin + "'");
948 stmntBegin.execute();
949 stmntCopy = myConn.prepareStatement(copy);
950 myLog.info("Executing statement: '" + copy + "'");
951 stmntCopy.execute();
952 stmntInsert = myConn.prepareStatement(insert);
953 myLog.info("Executing statement: '" + insert + "'");
954 stmntInsert.executeUpdate();
955 stmntDrop = myConn.prepareStatement(drop);
956 myLog.info("Executing statement: '" + drop + "'");
957 stmntDrop.executeUpdate();
958 stmntAlter = myConn.prepareStatement(alter);
959 myLog.info("Executing statement: '" + alter + "'");
960 stmntAlter.execute();
961 stmntCommit = myConn.prepareStatement(commit);
962 myLog.info("Executing statement: '" + commit + "'");
963 stmntCommit.execute();
964 // update list of columns
965 strRefrButtonClicked();
966 } catch (SQLException e) {
967 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
968 } finally {
969 try {
970 stmntBegin.close();
971 stmntCopy.close();
972 stmntInsert.close();
973 stmntDrop.close();
974 stmntAlter.close();
975 stmntCommit.close();
976 } catch (SQLException e) {
977 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
978 }
979 }
980 }
981
982 /**
983 * DELETE TABLE deletes the selected table & updates list of tables
984 */
985 public void strDelTButtonClick() {
986 if (!checkConnection()) {
987 return;
988 }
989 // name of active table
990 // none?
991 if (!checkActiveTable()) {
992 return;
993 }
994 String myTable = tableListView.getSelectionModel().getSelectedItem();
995
996 if (showJMetroChoiceDialog(myTable) == false) {
997 return;
998 }
999
1000 // delete selected table
1001 String sql = "DROP TABLE " + myTable;
1002 PreparedStatement myStatement = null;
1003 ResultSet rs = null;
1004 try {
1005 myStatement = myConn.prepareStatement(sql);
1006 myStatement.execute();
1007 // update list of tables
1008 tableListView.getItems().clear();
1009 // load all tables
1010 DatabaseMetaData md = myConn.getMetaData();
1011 rs = md.getTables(null, null, "%", null);
1012 while (rs.next()) {
1013 tableListView.getItems().add(rs.getString(3));
1014 }
1015 } catch (SQLException e) {
1016 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1017 } finally {
1018 try {
1019 strTableView.getItems().removeAll(strTableView.getItems());
1020 dataTableView.getItems().removeAll(dataTableView.getItems());
1021 if (myStatement != null)
1022 myStatement.close();
1023 if (rs != null)
1024 rs.close();
1025 } catch (SQLException e) {
1026 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1027 }
1028 }
1029 }
1030
1031 /**
1032 * DataTabClick adds all the needed columns to the table, based on all values
1033 * entered in structure table
1034 */
1035 public void dataTabClick() {
1036 if (closing)
1037 return;
1038 // database connected?
1039 if (!checkConnection()) {
1040 return;
1041 }
1042 // name of active table
1043 // none?
1044 if (!checkActiveTable()) {
1045 return;
1046 }
1047 String myTable = tableListView.getSelectionModel().getSelectedItem();
1048
1049 dataDelButton.setDisable(true);
1050 dataEditButton.setDisable(true);
1051
1052 // delete old conent
1053 dataTableView.getColumns().clear();
1054 dataTableView.getItems().clear();
1055
1056 // count z columns + generate z table columns
1057 PreparedStatement stmnt = null;
1058 ResultSet rs = null;
1059 try {
1060 stmnt = myConn.prepareStatement("SELECT * FROM " + myTable);
1061 rs = stmnt.executeQuery();
1062 ResultSetMetaData rsmd = rs.getMetaData();
1063
1064 myDataColumns.clear();
1065 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
1066 final int idx = i - 1;
1067 TableColumn<ObservableList<String>, String> myColumn = new TableColumn<>(rsmd.getColumnName(i));
1068 myColumn.setCellValueFactory(param -> new ReadOnlyObjectWrapper<>(param.getValue().get(idx)));
1069
1070 dataTableView.getColumns().add(myColumn);
1071 myDataColumns.add(myColumn);
1072 }
1073
1074 } catch (SQLException e) {
1075 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1076 } finally {
1077 try {
1078 dataRefrButtonClick();
1079 if (stmnt != null)
1080 stmnt.close();
1081 if (rs != null)
1082 rs.close();
1083 } catch (SQLException e) {
1084 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1085 }
1086 }
1087
1088 }
1089
1090 /**
1091 * REFRESH (Data) refreshes the data table of the selected database table
1092 */
1093 public void dataRefrButtonClick() {
1094 // database connected?
1095 if (!checkConnection()) {
1096 return;
1097 }
1098
1099 // name of active table
1100 // none?
1101 if (!checkActiveTable()) {
1102 return;
1103 }
1104 String myTable = tableListView.getSelectionModel().getSelectedItem();
1105
1106 // dataTabClick();
1107
1108 // reset
1109 dataTableView.getItems().clear();
1110
1111 PreparedStatement stmnt = null;
1112 ResultSet rs = null;
1113 try {
1114 stmnt = myConn.prepareStatement("SELECT * FROM " + myTable + ";");
1115 rs = stmnt.executeQuery();
1116 while (rs.next()) {
1117 ObservableList<String> row = FXCollections.observableArrayList();
1118 for (int i = 1; i <= myDataColumns.size(); i++) {
1119 row.add(rs.getString(i));
1120 }
1121 dataList.add(row);
1122 }
1123 dataTableView.setItems(dataList);
1124 } catch (SQLException e) {
1125 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1126 } finally {
1127 try {
1128 if (stmnt != null)
1129 stmnt.close();
1130 if (rs != null)
1131 rs.close();
1132 } catch (SQLException e) {
1133 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1134 }
1135 }
1136 }
1137
1138 /**
1139 * INSERT ROW adds a row to the data table of the selected database table
1140 */
1141 public void dataInsButtonClick() {
1142 // database connected?
1143 if (!checkConnection()) {
1144 return;
1145 }
1146
1147 // name of active table
1148 // none?
1149 if (!checkActiveTable()) {
1150 return;
1151 }
1152 String myTable = tableListView.getSelectionModel().getSelectedItem();
1153
1154 // Refresh structure to selected table
1155 strRefrButtonClicked();
1156
1157 // open dialogbox
1158 Stage popUp = new Stage();
1159 // make no change in main while this is open
1160 popUp.initModality(Modality.APPLICATION_MODAL);
1161 popUp.setTitle("Please fill out!");
1162 popUp.setMinHeight(300);
1163 popUp.setMinWidth(600);
1164
1165 // add all controls
1166 ListView<TextField> textFields = new ListView<TextField>();
1167 textFields.setMaxHeight(popUp.getMinHeight() - 100);
1168 textFields.setMaxWidth(popUp.getMinWidth() - 100);
1169
1170 for (TableColumnVal myVal : strTableView.getItems()) {
1171 TextField feld = new TextField();
1172 feld.setMaxWidth(textFields.getMaxWidth());
1173 textFields.getItems().add(feld);
1174 feld.setPromptText(myVal.getName());
1175 }
1176
1177 Button enterButton = new Button("Confirm");
1178 enterButton.setMaxSize((popUp.getMinWidth() - 100) / 2, 75);
1179 final int[] counter = { 0 };
1180 ArrayList<String> values = new ArrayList<>();
1181
1182 // set action
1183 enterButton.setOnAction(e -> {
1184 for (TextField myField : textFields.getItems()) {
1185 // ist text blank?
1186 if (myField.getText().isBlank()) {
1187 myField.setText("Enter value");
1188 myField.setStyle("-fx-text-fill: red");
1189 // ist input dem datentyp entsprechend?
1190 } else {
1191 // ja --> continue + close popup
1192 if (strTableView.getItems().get(counter[0]).getName().contentEquals(myField.getPromptText())) {
1193 switch (strTableView.getItems().get(counter[0]).getDataType().toUpperCase()) {
1194 case "INTEGER": {
1195 if ((!(myField.getText().matches(regex))) || myField.getText().length() > 10
1196 || Double.valueOf(myField.getText()) > 2147483647
1197 || Double.valueOf(myField.getText()) < -2147483647) {
1198 myField.setText("INTEGER");
1199 myField.setStyle("-fx-text-fill: red");
1200 // reset
1201 values.clear();
1202 counter[0] = 0;
1203 return;
1204 } else {
1205 // save value
1206 values.add(myField.getText());
1207 }
1208 break;
1209 }
1210 case "TEXT": {
1211 values.add(myField.getText());
1212 break;
1213 }
1214 case "DOUBLE": {
1215 if (!(myField.getText().matches(regex))) {
1216 myField.setText("INTEGER");
1217 myField.setStyle("-fx-text-fill: red");
1218 // reset
1219 values.clear();
1220 counter[0] = 0;
1221 return;
1222 } else {
1223 try {
1224 Double.parseDouble(myField.getText());
1225 } catch (NumberFormatException e1) {
1226 // reset
1227 values.clear();
1228 counter[0] = 0;
1229 return;
1230 }
1231 values.add(myField.getText());
1232 }
1233 break;
1234 }
1235// case "VARCHAR": {
1236// break;
1237// }
1238 case "BOOLEAN": {
1239 if (myField.getText().contentEquals("FALSE") || myField.getText().contentEquals("false")
1240 || myField.getText().contentEquals("False")
1241 || myField.getText().contentEquals("TRUE")
1242 || myField.getText().contentEquals("true")
1243 || myField.getText().contentEquals("True")) {
1244 values.add(myField.getText().toUpperCase());
1245 } else {
1246 values.clear();
1247 counter[0] = 0;
1248 return;
1249 }
1250 break;
1251 }
1252// case "CHAR": {
1253// if (myField.getText().length()>1) {
1254// values.clear();
1255// counter[0] = 0;
1256// return;
1257// }
1258// break;
1259// }
1260 default: {
1261 showJMetroDialog(Alert.AlertType.WARNING, "Not implemented datatype: "
1262 + strTableView.getItems().get(counter[0]).getDataType() + "!");
1263 values.clear();
1264 counter[0] = 0;
1265 return;
1266 }
1267 }
1268 }
1269 }
1270 counter[0] += 1;
1271 }
1272 popUp.close();
1273 // build the statement
1274 String sql = "INSERT INTO " + myTable + "(";
1275 for (TableColumnVal myVal : strTableView.getItems()) {
1276 sql += myVal.getName() + ",";
1277 }
1278 sql = sql.substring(0, sql.length() - 1);
1279 sql += ") VALUES(";
1280 for (String mystr : values) {
1281 sql += "'" + mystr + "'" + ",";
1282 }
1283 sql = sql.substring(0, sql.length() - 1);
1284 sql += ");";
1285 PreparedStatement stmnt = null;
1286 try {
1287 stmnt = myConn.prepareStatement(sql);
1288 stmnt.execute();
1289 } catch (SQLException q) {
1290 showJMetroDialog(Alert.AlertType.WARNING, q.getMessage());
1291 } finally {
1292 // close all connections
1293 try {
1294 stmnt.close();
1295 } catch (SQLException e1) {
1296 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
1297 }
1298 // update data table
1299 dataRefrButtonClick();
1300 }
1301 });
1302 // Vbox will store everything
1303 VBox vBox = new VBox();
1304 vBox.setStyle("-fx-background-color: white");
1305 vBox.setSpacing(5);
1306 vBox.getChildren().addAll(textFields);
1307 vBox.getChildren().add(enterButton);
1308 vBox.setAlignment(Pos.CENTER);
1309 // open
1310 Scene scene = new Scene(vBox);
1311 JMetro jMetro = new JMetro(Style.LIGHT);
1312 jMetro.setScene(scene);
1313 popUp.setScene(scene);
1314 popUp.showAndWait();
1315 }
1316
1317 /**
1318 * EDIT ROW edits the selected row of the database table
1319 */
1320 public void dataEditButtonClick() {
1321 // database connected?
1322 if (!checkConnection()) {
1323 return;
1324 }
1325
1326 // name of active table
1327 // none?
1328 if (!checkActiveTable()) {
1329 return;
1330 }
1331 String myTable = tableListView.getSelectionModel().getSelectedItem();
1332
1333 if (strTableView == null) {
1334 strRefrButtonClicked();
1335 }
1336
1337 if (dataTableView.getSelectionModel().getSelectedItem() == null) {
1338 showJMetroDialog(Alert.AlertType.WARNING, "No row selected!");
1339 return;
1340 }
1341
1342 // open dialogbox
1343 // column name, data type, pk, fk, unique, not null, defval
1344 Stage popUp = new Stage();
1345 // make no change in main while this is open
1346 popUp.initModality(Modality.APPLICATION_MODAL);
1347 popUp.setTitle("Please fill out!");
1348 popUp.setMinHeight(300);
1349 popUp.setMinWidth(600);
1350
1351 // add all controls
1352 ListView<TextField> textFields = new ListView<TextField>();
1353 textFields.setMaxHeight(popUp.getMinHeight() - 100);
1354 textFields.setMaxWidth(popUp.getMinWidth() - 100);
1355 int cc = 0;
1356 for (TableColumnVal myVal : strTableView.getItems()) {
1357 TextField feld = new TextField(dataTableView.getSelectionModel().getSelectedItem().get(cc));
1358 cc += 1;
1359 feld.setPromptText(myVal.getName());
1360 feld.setMaxWidth(textFields.getMaxWidth());
1361 textFields.getItems().add(feld);
1362 }
1363 Button enterButton = new Button("Confirm");
1364 enterButton.setMaxSize((popUp.getMinWidth() - 100) / 2, 75);
1365 final int[] counter = { 0 };
1366 ArrayList<String> values = new ArrayList<>();
1367
1368 // set action
1369 enterButton.setOnAction(e -> {
1370 for (TextField myField : textFields.getItems()) {
1371 // ist text blank?
1372 if (myField.getText().isBlank()) {
1373 myField.setText("Enter value");
1374 myField.setStyle("-fx-text-fill: red");
1375 // ist input dem datentyp entsprechend?
1376 } else {
1377 // ja --> continue + close popup
1378 if (strTableView.getItems().get(counter[0]).getName().contentEquals(myField.getPromptText())) {
1379 switch (strTableView.getItems().get(counter[0]).getDataType()) {
1380 case "INTEGER": {
1381 if ((!(myField.getText().matches(regex))) || myField.getText().length() > 10
1382 || Double.valueOf(myField.getText()) > 2147483647
1383 || Double.valueOf(myField.getText()) < -2147483647) {
1384 myField.setText("INTEGER");
1385 myField.setStyle("-fx-text-fill: red");
1386 // reset
1387 values.clear();
1388 counter[0] = 0;
1389 return;
1390 } else {
1391 // save value
1392 values.add(myField.getText());
1393 }
1394 break;
1395 }
1396 case "TEXT": {
1397 values.add(myField.getText());
1398 break;
1399 }
1400 case "DOUBLE": {
1401 if (!(myField.getText().matches(regex))) {
1402 myField.setText("INTEGER");
1403 myField.setStyle("-fx-text-fill: red");
1404 // reset
1405 values.clear();
1406 counter[0] = 0;
1407 return;
1408 } else {
1409 try {
1410 Double.parseDouble(myField.getText());
1411 } catch (NumberFormatException e1) {
1412 // reset
1413 values.clear();
1414 counter[0] = 0;
1415 return;
1416 }
1417 values.add(myField.getText());
1418 }
1419 break;
1420 }
1421// case "VARCHAR": {
1422// break;
1423// }
1424 case "BOOLEAN": {
1425 if (myField.getText().toLowerCase().contentEquals("false")
1426 || myField.getText().toLowerCase().contentEquals("true")) {
1427 values.add(myField.getText().toUpperCase());
1428 } else {
1429 values.clear();
1430 counter[0] = 0;
1431 return;
1432 }
1433 break;
1434 }
1435// case "CHAR": {
1436// if (myField.getText().length()>1) {
1437// values.clear();
1438// counter[0] = 0;
1439// return;
1440// }
1441// break;
1442// }
1443 default: {
1444 myLog.severe("Not implemented datatype: "
1445 + strTableView.getItems().get(counter[0]).getDataType() + "!");
1446 values.clear();
1447 counter[0] = 0;
1448 return;
1449 }
1450 }
1451 }
1452 }
1453 counter[0] += 1;
1454 }
1455 popUp.close();
1456
1457 // delete row
1458 String firstColumn = dataTableView.getColumns().get(0).getText();
1459 ObservableList<String> temprList = dataTableView.getSelectionModel().getSelectedItem();
1460 String sql1 = "DELETE FROM " + myTable + " WHERE " + firstColumn + " = " + temprList.get(0) + ";";
1461 PreparedStatement stmnt1 = null;
1462 try {
1463 stmnt1 = myConn.prepareStatement(sql1);
1464 stmnt1.execute();
1465 } catch (SQLException e1) {
1466 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
1467 } finally {
1468 try {
1469 if (stmnt1 != null)
1470 stmnt1.close();
1471 } catch (SQLException e1) {
1472 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
1473 }
1474 }
1475
1476 // build the statement
1477 String sql = "INSERT INTO " + myTable + "(";
1478 for (TableColumnVal myVal : strTableView.getItems()) {
1479 sql += myVal.getName() + ",";
1480 }
1481 sql = sql.substring(0, sql.length() - 1);
1482 sql += ") VALUES(";
1483 for (String mystr : values) {
1484 sql += "'" + mystr + "'" + ",";
1485 }
1486 sql = sql.substring(0, sql.length() - 1);
1487 sql += ");";
1488 PreparedStatement stmnt = null;
1489 try {
1490 stmnt = myConn.prepareStatement(sql);
1491 stmnt.execute();
1492 } catch (SQLException q) {
1493 showJMetroDialog(Alert.AlertType.WARNING, q.getMessage());
1494 } finally {
1495 // close all connections
1496 try {
1497 stmnt.close();
1498 } catch (SQLException e1) {
1499 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
1500 }
1501 // update data table
1502 dataRefrButtonClick();
1503 }
1504 });
1505 // Vbox will store everything
1506 VBox vBox = new VBox();
1507 vBox.setStyle("-fx-background-color: white");
1508 vBox.setSpacing(5);
1509 vBox.getChildren().addAll(textFields);
1510 vBox.getChildren().add(enterButton);
1511 vBox.setAlignment(Pos.CENTER);
1512 // open
1513 Scene scene = new Scene(vBox);
1514 JMetro jMetro = new JMetro(Style.LIGHT);
1515 jMetro.setScene(scene);
1516 popUp.setScene(scene);
1517 popUp.showAndWait();
1518 }
1519
1520 /**
1521 * DELETE ROW deletes the selected row of the database table
1522 */
1523 public void dataDelButtonClick() {
1524 // database connected?
1525 if (!checkConnection()) {
1526 return;
1527 }
1528
1529 // name of active table
1530 // none?
1531 if (!checkActiveTable()) {
1532 return;
1533 }
1534 String myTable = tableListView.getSelectionModel().getSelectedItem();
1535
1536 // selected row?
1537 if (dataTableView.getSelectionModel().getSelectedItem() == null) {
1538 showJMetroDialog(Alert.AlertType.WARNING, "No row selected!");
1539 return;
1540 }
1541
1542 String firstColumn = dataTableView.getColumns().get(0).getText();
1543 ObservableList<String> tempList = dataTableView.getSelectionModel().getSelectedItem();
1544
1545 if (showJMetroChoiceDialog(tempList.get(0)) == false) {
1546 return;
1547 }
1548
1549 String sql = "DELETE FROM " + myTable + " WHERE " + firstColumn + " = " + tempList.get(0) + ";";
1550 PreparedStatement stmnt = null;
1551 try {
1552 stmnt = myConn.prepareStatement(sql);
1553 stmnt.execute();
1554 } catch (SQLException e) {
1555 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1556 } finally {
1557 try {
1558 if (stmnt != null)
1559 stmnt.close();
1560 } catch (SQLException e) {
1561 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1562 }
1563 dataRefrButtonClick();
1564 }
1565 }
1566
1567 /**
1568 * COMMAND opens a new dialog, where users can write their own sqlite statements
1569 * and queries
1570 */
1571 public void commandButtonClick() {
1572 // database connected?
1573 if (!checkConnection()) {
1574 return;
1575 }
1576 // open dialogbox
1577 // text & button
1578 Stage popUp = new Stage();
1579 // make no change in main while this is open
1580 popUp.initModality(Modality.NONE);
1581 popUp.setTitle("Enter Query!");
1582 popUp.setMinHeight(200);
1583 popUp.setMinWidth(800);
1584
1585 Button enterButton = new Button("Enter");
1586 enterButton.setMinWidth(150);
1587 Button clearButton = new Button("Clear");
1588 clearButton.setMinWidth(100);
1589 TextArea txtArea = new TextArea("Enter your code...\ne.g.:SELECT * FROM 'your_table';");
1590
1591 clearButton.setOnAction(e -> {
1592 txtArea.setText("Enter your code...\ne.g.:SELECT * FROM 'your_table';");
1593 });
1594
1595 enterButton.setOnAction(e -> {
1596 String sql = txtArea.getText().trim();
1597 PreparedStatement stmnt = null;
1598 ResultSet rs = null;
1599 try {
1600 // Daten-Anfrage
1601 if (sql.substring(0, "SELECT".length()).contentEquals("SELECT")) {
1602 stmnt = myConn.prepareStatement(sql);
1603 rs = stmnt.executeQuery();
1604 ResultSetMetaData rsmd = rs.getMetaData();
1605
1606 Stage tablePopUp = new Stage();
1607 tablePopUp.setTitle("YOUR RESULT");
1608 tablePopUp.setMinHeight(200);
1609 tablePopUp.setMinWidth(600);
1610 // Table
1611 TableView<ObservableList<String>> resultTable = new TableView<>();
1612 // Values for table
1613 ObservableList<ObservableList<String>> resultValues = FXCollections.observableArrayList();
1614
1615 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
1616 final int idx = i - 1;
1617 TableColumn<ObservableList<String>, String> myColumn = new TableColumn<>(rsmd.getColumnName(i));
1618 myColumn.setCellValueFactory(param -> new ReadOnlyObjectWrapper<>(param.getValue().get(idx)));
1619 resultTable.getColumns().add(myColumn);
1620 }
1621 while (rs.next()) {
1622 ObservableList<String> row = FXCollections.observableArrayList();
1623 for (int i = 1; i <= resultTable.getColumns().size(); i++) {
1624 row.add(rs.getString(i));
1625 }
1626 resultValues.add(row);
1627 }
1628 resultTable.setItems(resultValues);
1629
1630 // Vbox will store everything
1631 VBox vBox = new VBox();
1632 vBox.setStyle("-fx-background-color: white");
1633 vBox.setSpacing(5);
1634 vBox.getChildren().add(resultTable);
1635 vBox.setAlignment(Pos.CENTER);
1636 // open
1637 Scene scene = new Scene(vBox);
1638 JMetro jMetro = new JMetro(Style.LIGHT);
1639 jMetro.setScene(scene);
1640 tablePopUp.setScene(scene);
1641 tablePopUp.showAndWait();
1642 } else {
1643 // Befehl
1644 stmnt = myConn.prepareStatement(sql);
1645 stmnt.execute();
1646 }
1647
1648 } catch (SQLException e1) {
1649 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
1650 } finally {
1651 try {
1652 if (rs != null)
1653 rs.close();
1654 if (stmnt != null)
1655 stmnt.close();
1656 } catch (SQLException e1) {
1657 showJMetroDialog(Alert.AlertType.WARNING, e1.getMessage());
1658 }
1659 }
1660 });
1661
1662 // Vbox will store everything
1663 VBox vBox = new VBox();
1664 vBox.setStyle("-fx-background-color: white");
1665 vBox.setSpacing(5);
1666 vBox.getChildren().add(txtArea);
1667 vBox.getChildren().add(enterButton);
1668 vBox.getChildren().add(clearButton);
1669 vBox.setAlignment(Pos.CENTER);
1670 // open
1671 Scene scene = new Scene(vBox);
1672 JMetro jMetro = new JMetro(Style.LIGHT);
1673 jMetro.setScene(scene);
1674 popUp.setScene(scene);
1675 popUp.showAndWait();
1676 }
1677
1678 public void minButtonClicked() {
1679 Stage tempStage = (Stage) closeButton.getScene().getWindow();
1680 tempStage.setIconified(true);
1681 }
1682
1683 public void closeButtonClicked() {
1684 Stage tempStage = (Stage) closeButton.getScene().getWindow();
1685 closing = true;
1686 if (myConn != null) {
1687 try {
1688 myConn.close();
1689 } catch (SQLException e) {
1690 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1691 } finally {
1692 tableListView.getItems().removeAll(tableListView.getItems());
1693 strTableView.getItems().removeAll(strTableView.getItems());
1694 dataTableView.getItems().removeAll(dataTableView.getItems());
1695 }
1696 }
1697 tempStage.close();
1698 System.exit(0);
1699 }
1700
1701 public boolean showJMetroChoiceDialog(String input) {
1702 JMetro jMetro = new JMetro();
1703 Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
1704 alert.setTitle("Please confirm!");
1705 alert.setHeaderText("Sure you want to delete " + input + "?");
1706 alert.setContentText("Confirm your action...");
1707 jMetro.setScene(alert.getDialogPane().getScene());
1708 Optional<ButtonType> result = alert.showAndWait();
1709 if (result.get() == ButtonType.OK) {
1710 return true;
1711 } else if (result.get() == ButtonType.CANCEL) {
1712 return false;
1713 } else {
1714 return false;
1715 }
1716 }
1717
1718 public void showJMetroDialog(Alert.AlertType type, String msg) {
1719 JMetro jMetro = new JMetro();
1720
1721 Alert alert = new Alert(Alert.AlertType.WARNING);
1722 alert.setHeaderText("Warning");
1723 alert.setContentText(msg);
1724 alert.setTitle("");
1725
1726 jMetro.setScene(alert.getDialogPane().getScene());
1727
1728 alert.showAndWait();
1729 }
1730
1731 /**
1732 * Returns a boolean, depending on whether the column in @myTable has a foreign
1733 * key or not
1734 *
1735 * @param columnName = name of the active column in the given table
1736 * @param myTable = name of the selected table
1737 * @param conn = given connection to the active database file
1738 * @return true or false
1739 */
1740 public boolean checkFK(String columnName, String myTable, Connection conn) {
1741 ResultSet rs = null;
1742 try {
1743 rs = conn.getMetaData().getImportedKeys(null, null, myTable);
1744 while (rs.next()) {
1745 // 3,4,8
1746 if (rs.getString(8).contentEquals(columnName)) {
1747 return true;
1748 }
1749 }
1750 } catch (SQLException e) {
1751 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1752 }
1753 return false;
1754 }
1755
1756 /**
1757 * Returns a boolean, depending on whether the column in @myTable has a private
1758 * key or not
1759 *
1760 * @param columnName = name of the active column in the given table
1761 * @param myTable = name of the selected table
1762 * @param conn = given connection to the active database file
1763 * @return true or false
1764 */
1765 public boolean checkPK(String columnName, String myTable, Connection conn) {
1766 ResultSet rs = null;
1767 try {
1768 rs = conn.getMetaData().getPrimaryKeys(null, conn.getSchema(), myTable);
1769 while (rs.next()) {
1770 if (rs.getString("COLUMN_NAME").contentEquals(columnName)) {
1771 return true;
1772 }
1773 }
1774 } catch (SQLException e) {
1775 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1776 } finally {
1777 try {
1778 if (rs != null)
1779 rs.close();
1780 } catch (SQLException e) {
1781 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1782 }
1783 }
1784 return false;
1785 }
1786
1787 /**
1788 * Returns a boolean, depending on whether the column in @myTable has to be
1789 * unique or not
1790 *
1791 * @param columnName = name of the active column in the given table
1792 * @param myTable = name of the selected table
1793 * @param conn = given connection to the active database file
1794 * @return false, as true is not implemented
1795 */
1796 public boolean checkUnique(String columnName, String myTable, Connection conn) {
1797// ResultSet rs = null;
1798// PreparedStatement stmnt = null;
1799// try {
1800// String sql = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC WHERE TABLE_NAME='"+ myTable + "' and TC.CONSTRAINT_TYPE = 'UNIQUE';";
1801// stmnt = conn.prepareStatement(sql);
1802// rs = stmnt.executeQuery();
1803// while (rs.next()) {
1804 //
1805// }
1806// } catch (SQLException e) {
1807// e.printStackTrace();
1808// } finally {
1809// try {
1810// if (rs != null)
1811// rs.close();
1812// if (stmnt != null)
1813// stmnt.close();
1814// } catch (SQLException e) {
1815// e.printStackTrace();
1816// }
1817// }
1818 return false;
1819 }
1820
1821 /**
1822 * Returns a boolean, depending on whether the column in @myTable has to contain
1823 * a value or not
1824 *
1825 * @param columnName = name of the active column in the given table
1826 * @param myTable = name of the selected table
1827 * @param conn = given connection to the active database file
1828 * @return false, as true is not implemented
1829 */
1830 public boolean checkNULL(String columnName, String myTable, Connection conn) {
1831 ResultSet myRS = null;
1832 PreparedStatement stmnt = null;
1833 try {
1834 String sql = "SELECT * FROM " + myTable;
1835 stmnt = conn.prepareStatement(sql);
1836 myRS = stmnt.executeQuery();
1837 ResultSetMetaData metadata = myRS.getMetaData();
1838
1839 for (int cc = 1; cc <= metadata.getColumnCount(); cc++) {
1840 int nullable = metadata.isNullable(cc);
1841 if (nullable == ResultSetMetaData.columnNullable) {
1842 // System.out.println("nulls "+metadata.getColumnName(cc));
1843// if (myRS.getString(cc).contentEquals(columnName)) {
1844// return false;
1845// }
1846 } else if (nullable == ResultSetMetaData.columnNoNulls) {
1847 // System.out.println("Nonulls: "+metadata.getColumnName(cc));
1848 }
1849 }
1850 } catch (SQLException e) {
1851 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1852 } finally {
1853 try {
1854 stmnt.close();
1855 myRS.close();
1856 } catch (SQLException e) {
1857 showJMetroDialog(Alert.AlertType.WARNING, e.getMessage());
1858 }
1859 }
1860
1861 return false;
1862 }
1863
1864 /**
1865 * Function is called whenever the user has to pick what kind of data type his
1866 * column has to support
1867 *
1868 * @return the ChoiceBox containing String Elements, that represent all
1869 * DataTypes that are implemented
1870 */
1871 public ChoiceBox<String> createDataTypeChoiceBox() {
1872 ChoiceBox<String> tempBox = new ChoiceBox<String>();
1873 tempBox.getItems().add("INTEGER");
1874 tempBox.getItems().add("DOUBLE");
1875 tempBox.getItems().add("TEXT");
1876 tempBox.getItems().add("BOOLEAN");
1877 return tempBox;
1878 }
1879
1880 /**
1881 * Function is called whenever there is a need to check if user selected a table
1882 *
1883 * @return true or false depending on selection
1884 */
1885 public boolean checkActiveTable() {
1886 if (tableListView.getSelectionModel().getSelectedItem() == null) {
1887 showJMetroDialog(Alert.AlertType.WARNING, "No table selected!");
1888 return false;
1889 } else {
1890 return true;
1891 }
1892 }
1893
1894 /**
1895 * Function is called whenever there is a need to check if user is connected to
1896 * a database
1897 *
1898 * @return true or false depending on the database connection status
1899 */
1900 public boolean checkConnection() {
1901 if (myConn == null) {
1902 showJMetroDialog(Alert.AlertType.WARNING, "No active connection!");
1903 return false;
1904 } else {
1905 return true;
1906 }
1907 }
1908
1909}
1910