package database; import javafx.collections.FXCollections; import javafx.collections.ObservableList; import javafx.scene.control.TableColumn; import javafx.scene.control.TableView; import javafx.scene.control.cell.PropertyValueFactory; import java.math.BigDecimal; import java.sql.*; import java.time.LocalDate; import java.util.Collection; import java.util.LinkedList; /** Created by Rafał Byczek on 02.06.16. */ final public class Database { /** Only instance of database */ public static final Database instance=new Database(); /** database to database */ /** Constructor */ private Database() { try { Class.forName("org.postgresql.Driver"); connection=DriverManager.getConnection("jdbc:postgresql://"+SERVER_ADRES+':'+PORT+'/'+DB_NAME,USER_NAME,PASSWORD); { throw new DatabaseException(e); } } private static <T,S> void insertColumn(final TableView<T> table,final Class<S> typeTag,final String name,final int minWidth) { final TableColumn<T, S> firstNameCol = new TableColumn<>(name); firstNameCol.setMinWidth(minWidth); firstNameCol.setCellValueFactory(new PropertyValueFactory<>(name)); table.getColumns().addAll(firstNameCol); } private ObservableList<Driver> getDriversList() { final Collection<Driver> data=new LinkedList<>(); { while(resultSet.next()) { data.add(new Driver(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7))); } { throw new DatabaseException(e); } return FXCollections.observableArrayList(data); } public void getDriversTable(final TableView<Driver> driversTable) { driversTable.setItems(getDriversList()); } private ObservableList<Vehicle> getVehiclesList() { final Collection<Vehicle> data=new LinkedList<>(); { while(resultSet.next()) { data.add(new Vehicle(resultSet.getInt(1),resultSet.getString(2),resultSet.getDate(3).toLocalDate(),resultSet.getString(4),resultSet.getString(5),resultSet.getString(6))); } { throw new DatabaseException(e); } return FXCollections.observableArrayList(data); } public void getVehiclesTable(final TableView<Vehicle> vehiclesTable) { insertColumn(vehiclesTable,LocalDate.class,"data_rejestracji",20); vehiclesTable.setItems(getVehiclesList()); } private ObservableList<Exam> getExamsList() { final Collection<Exam> data=new LinkedList<>(); ResultSet resultSet=statement.executeQuery("SELECT id_egzaminu,data_przeprowadzenia,typ,nazwa,O.adres,E.imię,E.nazwisko,K.imię,K.nazwisko,wynik FROM egzaminy NATURAL JOIN egzaminatorzy E NATURAL JOIN ośrodki O NATURAL JOIN wyniki_egzaminów JOIN kierowcy K USING(id_kierowcy);")) { while(resultSet.next()) { data.add(new Exam(resultSet.getInt(1),resultSet.getDate(2).toLocalDate(),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7),resultSet.getString(8),resultSet.getString(9),resultSet.getString(10))); } { throw new DatabaseException(e); } return FXCollections.observableArrayList(data); } public void getExamsTable(final TableView<Exam> examTable) { insertColumn(examTable,LocalDate.class,"data_przeprowadzenia",30); examTable.setItems(getExamsList()); } private ObservableList<Offence> getOffenceList() { final Collection<Offence> data=new LinkedList<>(); ResultSet resultSet=statement.executeQuery("SELECT K.imię,K.nazwisko,W.opis,W.wysokość_grzywny,W.punkty_karne FROM kierowcy K NATURAL JOIN mandaty M NATURAL JOIN wykroczenia W")) { while(resultSet.next()) { data.add(new Offence(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getBigDecimal(4),resultSet.getInt(5))); } { throw new DatabaseException(e); } return FXCollections.observableArrayList(data); } public void getOffenceTable(final TableView<Offence> examTable) { examTable.setItems(getOffenceList()); } { { preparedStatement.setInt(1,driver); { while(resultSet.next()) pojazdy+=resultSet.getString(1)+','; } { throw new DatabaseException(e); } if(pojazdy.isEmpty()) return ""; return pojazdy.substring(0,pojazdy.length()-1); } { String result; { preparedStatement.setInt(1,driver); { if(resultSet.next()) return resultSet.getString(1); } { throw new DatabaseException(e); } } /*public static void main(String[] args) { launch(args); } public void start() { final Label label = new Label("Address Book"); label.setFont(new Font("Arial", 20)); table.setEditable(true); TableColumn firstNameCol = new TableColumn("First Name"); firstNameCol.setMinWidth(100); firstNameCol.setCellValueFactory( new PropertyValueFactory<Person, String>("firstName")); TableColumn lastNameCol = new TableColumn("Last Name"); lastNameCol.setMinWidth(100); lastNameCol.setCellValueFactory( new PropertyValueFactory<Person, String>("lastName")); TableColumn emailCol = new TableColumn("Email"); emailCol.setMinWidth(200); emailCol.setCellValueFactory( new PropertyValueFactory<Person, String>("email")); table.setItems(data); table.getColumns().addAll(firstNameCol, lastNameCol, emailCol); final VBox vbox = new VBox(); vbox.setSpacing(5); vbox.setPadding(new Insets(10, 0, 0, 10)); vbox.getChildren().addAll(label, table); ((Group) scene.getRoot()).getChildren().addAll(vbox); stage.setScene(scene); stage.show(); } public static class Person { private final SimpleStringProperty firstName; private final SimpleStringProperty lastName; private final SimpleStringProperty email; private Person(String fName, String lName, String email) { this.firstName = new SimpleStringProperty(fName); this.lastName = new SimpleStringProperty(lName); this.email = new SimpleStringProperty(email); } public String getFirstName() { return firstName.get(); } public void setFirstName(String fName) { firstName.set(fName); } public String getLastName() { return lastName.get(); } public void setLastName(String fName) { lastName.set(fName); } public String getEmail() { return email.get(); } public void setEmail(String fName) { email.set(fName); } } */ /** Standard exception thrown when something wrong with database */ { /** UID for serialization */ private static final long serialVersionUID=4187053082188070490L; /** Constructor */ DatabaseException() { super(); } /** Constructor @param message Message why exception occurred */ { super(message); } /** Constructor @param cause Cause of exception */ { super(cause); } /** Constructor @param message Message why exception occurred @param cause Cause of exception */ { super(message,cause); } } }