253 lines
8.9 KiB
Java
253 lines
8.9 KiB
Java
package io.chronos.admin;
|
|
|
|
import javafx.beans.property.ReadOnlyStringWrapper;
|
|
import javafx.beans.value.ObservableValue;
|
|
import javafx.event.ActionEvent;
|
|
import javafx.event.Event;
|
|
import javafx.scene.control.*;
|
|
import javafx.util.Callback;
|
|
|
|
import java.sql.*;
|
|
import java.util.ArrayList;
|
|
import java.util.List;
|
|
|
|
public class Controller {
|
|
public TextField connect_host;
|
|
public TextField connect_port;
|
|
public TextField connect_user;
|
|
public TextField connect_pass;
|
|
public Button connect_go;
|
|
public Label connect_status;
|
|
public TableView<List<String>> schools_table;
|
|
public TextField schools_search;
|
|
public TextField add_school_name;
|
|
public TextField add_school_domain;
|
|
public Button add_school_go;
|
|
public TextField delete_school_id;
|
|
public Button delete_school_go;
|
|
public TextField users_school;
|
|
public TableView<List<String>> users_table;
|
|
public Button users_go;
|
|
public TextField modify_user_school;
|
|
public TextField modify_user_id;
|
|
public Button modify_user_load;
|
|
public TextField modify_user_name;
|
|
public TextField modify_user_email;
|
|
public TextField modify_user_role;
|
|
public Button modify_user_go;
|
|
public TextField connect_database;
|
|
public TextField add_user_school;
|
|
public TextField add_user_name;
|
|
public TextField add_user_role;
|
|
public TextField add_user_email;
|
|
public Button add_user_go;
|
|
|
|
private Connection c;
|
|
|
|
public void initialize() {
|
|
try {
|
|
Class.forName("com.mysql.jdbc.Driver");
|
|
} catch (ClassNotFoundException e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public void connect(ActionEvent actionEvent) {
|
|
if (c != null) {
|
|
try {
|
|
c.close();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
try {
|
|
String url = "jdbc:mysql://" + connect_host.getText() + ":" + connect_port.getText() + "/" + connect_database.getText();
|
|
c = DriverManager.getConnection(url, connect_user.getText(), connect_pass.getText());
|
|
connect_status.setText("Connected to " + url);
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
connect_status.setText("Unable to connect");
|
|
}
|
|
}
|
|
|
|
private static void loadResultsIntoTable(ResultSet rs, ResultSetMetaData md, TableView tv) throws SQLException {
|
|
tv.getColumns().clear();
|
|
TableColumn[] cols = new TableColumn[md.getColumnCount()];
|
|
for (int i = 0; i < md.getColumnCount(); i++) {
|
|
cols[i] = new TableColumn(md.getColumnName(i + 1));
|
|
int finalI = i;
|
|
cols[i].setCellValueFactory(new Callback<TableColumn.CellDataFeatures<ArrayList<String>, String>, ObservableValue>() {
|
|
@Override
|
|
public ObservableValue call(TableColumn.CellDataFeatures<ArrayList<String>, String> data) {
|
|
return new ReadOnlyStringWrapper(data.getValue().get(finalI));
|
|
}
|
|
});
|
|
}
|
|
tv.getColumns().addAll(cols);
|
|
|
|
tv.getItems().clear();
|
|
ArrayList<ArrayList<String>> rows = new ArrayList<>();
|
|
while (rs.next()) {
|
|
ArrayList<String> row = new ArrayList<>();
|
|
for (int i = 0; i < md.getColumnCount(); i++) {
|
|
row.add(rs.getString(i + 1));
|
|
}
|
|
rows.add(row);
|
|
}
|
|
tv.getItems().addAll(rows);
|
|
}
|
|
|
|
public void queryUsers(ActionEvent actionEvent) {
|
|
try {
|
|
PreparedStatement ps = null;
|
|
if (users_school.getText().length() > 0) {
|
|
ps = c.prepareStatement("SELECT * FROM user WHERE user.school = ?");
|
|
ps.setInt(1, Integer.parseInt(users_school.getText()));
|
|
} else {
|
|
ps = c.prepareStatement("SELECT * FROM user");
|
|
}
|
|
ps.execute();
|
|
loadResultsIntoTable(ps.getResultSet(), ps.getMetaData(), users_table);
|
|
} catch (NumberFormatException e) {
|
|
alertBadInput();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public void modifyUserLoad(ActionEvent actionEvent) {
|
|
try {
|
|
PreparedStatement ps = c.prepareStatement("SELECT * FROM user WHERE user.school = ? AND user.id = ?");
|
|
ps.setInt(1, Integer.parseInt(modify_user_school.getText()));
|
|
ps.setInt(2, Integer.parseInt(modify_user_id.getText()));
|
|
ps.execute();
|
|
|
|
ResultSet rs = ps.getResultSet();
|
|
if (rs.next()) {
|
|
modify_user_name.setText(rs.getString("name"));
|
|
modify_user_email.setText(rs.getString("email"));
|
|
modify_user_role.setText(rs.getString("role"));
|
|
}
|
|
} catch (NumberFormatException e) {
|
|
alertBadInput();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public void modifyUser(ActionEvent actionEvent) {
|
|
try {
|
|
PreparedStatement ps = c.prepareStatement("UPDATE user SET user.name = ?, user.email = ?, user.role = ? WHERE user.school = ? AND user.id = ?");
|
|
ps.setString(1, modify_user_name.getText());
|
|
ps.setString(2, modify_user_email.getText());
|
|
ps.setString(3, modify_user_role.getText());
|
|
ps.setInt(4, Integer.parseInt(modify_user_school.getText()));
|
|
ps.setInt(5, Integer.parseInt(modify_user_id.getText()));
|
|
ps.execute();
|
|
|
|
alertUpdateDone();
|
|
} catch (NumberFormatException e) {
|
|
alertBadInput();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
alertBadInput();
|
|
}
|
|
}
|
|
|
|
public void addUser(ActionEvent actionEvent) {
|
|
try {
|
|
PreparedStatement ps = c.prepareStatement("INSERT INTO user (name, email, role, school) VALUES (?, ?, ?, ?)");
|
|
ps.setString(1, add_user_name.getText());
|
|
ps.setString(2, add_user_email.getText());
|
|
ps.setString(3, add_user_role.getText());
|
|
ps.setInt(4, Integer.parseInt(add_user_school.getText()));
|
|
ps.execute();
|
|
|
|
alertInsertDone();
|
|
} catch (NumberFormatException e) {
|
|
alertBadInput();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
alertBadInput();
|
|
}
|
|
}
|
|
|
|
public void querySchools(ActionEvent actionEvent) {
|
|
try {
|
|
PreparedStatement ps = c.prepareStatement("SELECT * FROM school WHERE school.name LIKE ?");
|
|
ps.setString(1,
|
|
"%" +
|
|
schools_search.getText()
|
|
.replace("!", "!!")
|
|
.replace("%", "!%")
|
|
.replace("_", "!_")
|
|
.replace("[", "![")
|
|
+ "%"
|
|
);
|
|
ps.execute();
|
|
loadResultsIntoTable(ps.getResultSet(), ps.getMetaData(), schools_table);
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public void addSchool(ActionEvent actionEvent) {
|
|
try {
|
|
PreparedStatement ps = c.prepareStatement("INSERT INTO school (name, domain) VALUES (?, ?)");
|
|
ps.setString(1, add_school_name.getText());
|
|
ps.setString(2, add_school_domain.getText());
|
|
ps.execute();
|
|
|
|
alertInsertDone();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
alertBadInput();
|
|
}
|
|
}
|
|
|
|
public void deleteSchool(Event event) {
|
|
try {
|
|
PreparedStatement ps = c.prepareStatement("DELETE FROM school WHERE school.id = ?");
|
|
ps.setInt(1, Integer.parseInt(delete_school_id.getText()));
|
|
ps.execute();
|
|
|
|
alertDeleteDone();
|
|
} catch (NumberFormatException e) {
|
|
alertBadInput();
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
alertBadInput();
|
|
}
|
|
}
|
|
|
|
public static void alertBadInput() {
|
|
Alert alert = new Alert(Alert.AlertType.ERROR);
|
|
alert.setTitle("Woof!");
|
|
alert.setHeaderText("Woof!");
|
|
alert.setContentText("Bad input");
|
|
alert.showAndWait();
|
|
}
|
|
|
|
public static void alertInsertDone() {
|
|
Alert alert = new Alert(Alert.AlertType.INFORMATION);
|
|
alert.setTitle("Woof!");
|
|
alert.setHeaderText("Woof!");
|
|
alert.setContentText("Insert done!");
|
|
alert.showAndWait();
|
|
}
|
|
public static void alertUpdateDone() {
|
|
Alert alert = new Alert(Alert.AlertType.INFORMATION);
|
|
alert.setTitle("Woof!");
|
|
alert.setHeaderText("Woof!");
|
|
alert.setContentText("Update done!");
|
|
alert.showAndWait();
|
|
}
|
|
public static void alertDeleteDone() {
|
|
Alert alert = new Alert(Alert.AlertType.INFORMATION);
|
|
alert.setTitle("Woof!");
|
|
alert.setHeaderText("Woof!");
|
|
alert.setContentText("Delete done!");
|
|
alert.showAndWait();
|
|
}
|
|
}
|