MySQL Abfrage mit kumulierten Werten

[didi577]

Hi,

ich habe eine Abfrage die mir die Summe aus 52 Spalten liefert:

public int loadServices(int employe, int year, int week) {

       PreparedStatement pst = null;
       ResultSet rs = null;
       int sumEmp = 0;
       
       String query = "SELECT services_id, employe, year, (kw1 + kw2 + kw3 + kw4 + kw5 + kw6 + kw7 + kw8 + kw9 + kw10 + kw11 + kw12 + kw13 + kw14 + kw15 + kw16 + kw17 + kw18 + kw19 + kw20 + kw21 + kw22 + kw23 + kw24 + kw25 + kw26 + kw27 + kw28 + kw29 + kw30 + kw31 + kw32 + kw33 + kw34 + kw35 + kw36 + kw37 + kw38 + kw39 + kw40 + kw41 + kw42 + kw43 + kw44 + kw45 + kw46 + kw47 + kw48 + kw49 + kw50 + kw51 + kw52) AS sumEmp FROM services WHERE employe =? AND year =?";
       // int i = 1;
       try {
           pst = con.prepareStatement(query);
           pst.setInt(1, employe);
           pst.setInt(2, year);
           rs = pst.executeQuery();
           while (rs.next()) {
              

               sumEmp = rs.getInt("sumEmp");

           }

       } catch (SQLException e) {
           e.printStackTrace();

       } finally {
           if (rs != null && pst != null) {
               try {
                   rs.close();
                   pst.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }

       return sumEmp;
   }

ich möchte diese Anfrage so modifizieren dass ich jederzeit die Spalten kw1 - kw8 oder kw1 - kw34 also kumulierte Werte von kw1 bis kwX. den Wert X gebe ich als Parameter "int week" mit. ich habe hier noch wenig Erfahrung. wer kann mir helfen?

 

[mrBrown]

ich würde die DB anders aufbauen, und die Wochen (kw1 meint doch Kalenderwoche 1?) in einer eigenen Tabelle speichern. Summieren musst du dann über mehrere Zeilen, das dürfte deutliche einfacher sein.

 

[didi577]

ja kw sind Kalenderwochen

ich habe bislang lediglich die 52 kw, eine MitarbeierID und ein Jahr als Spalten in der Tabelle. Jede Zeile steht also für einen Mitarbeiter in einem Jahr. Was schlägst du genau vor?

 

[Thallius]

Spaltennamen mit Zahlen sind IMMER ein Zeichen, dass Dein DB Format falsch ist.

Wie Brown schon beschreibt du musst deine Kalenderwochen in eine eigene Tabelle auslagern und dann mit JOINS arbeiten.

 

[didi577]

ok also je kw eine Tabelle?

 

[mrBrown]

Nein!
Eine Tabelle "Woche", in der alle Wochen liegen. Die enthält dann jeweils die Wochennummer, eine Referenz auf den Mitarbeiter, die Jahreszahl und die relevanten Daten für die Woche.

Alles, was du zählen kannst, sollten Zeilen sein, nicht Spalten und erst recht nicht Tabellen.

 

[didi577]

ok...
Spalte Woche | Spalte Mitarbeiter | Spalte Jahr | Spalte Daten
1 5 2017 2
1 4 2017 3
2 5 2017 0
2 4 2017 0
3 4 2017 4
3 5 2017 1
4
5
6
...

habe ich so richtig verstanden?

 

[mrBrown]

Jap, genau so. Mitarbeiter sollte dann ein Fremdschlüssel sein

 

[didi577]

ok super Danke dir

 

[didi577]

jetzt habe ich doch noch mal eine Frage...

Spalte Woche | Spalte Mitarbeiter | Spalte Jahr | Spalte Daten
1 5 2017 2
1 4 2017 3
2 5 2017 0
2 4 2017 0
3 4 2017 4
3 5 2017 1
4
5
6
...

wie bekomme ich aus der oben beschriebenen Spalte "Daten" die Werte per Abfrage in meine Tabelle ? ich habe 52 Spalten in der tableview und jede wird aus der DB Spalte "Daten" gefüllt. in der tableview sollen die Daten der Mitarbeiter je Woche geladen werden
das ist der Code zur Abfrage:

public void loadServices(List<Service> serviceList,
           List<Boolean> booleanList, int employe, int year) {

       PreparedStatement pst = null;
       ResultSet rs = null;

       String query = "SELECT services_id, week, employe, year, days FROM services WHERE employe =? AND year =? AND week =?";

       try {
           pst = con.prepareStatement(query);
           pst.setInt(1, employe);
           pst.setInt(2, year);
           rs = pst.executeQuery();
           while (rs.next()) {
               
               

               Service serviceObject = new Service();
               serviceObject.setServices_id(rs.getInt("services_id"));
               serviceObject.setWeek(rs.getInt("week"));
               serviceObject.setEmploye(rs.getInt("employe"));
               serviceObject.setYear(rs.getInt("year"));
               serviceObject.setDays(rs.getInt("days"));
                               
               serviceList.add(serviceObject);

           }

       } catch (SQLException e) {
           e.printStackTrace();

       } finally {
           if (rs != null && pst != null) {
               try {
                   rs.close();
                   pst.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }
   }

der Aufruf:

mysql.loadServices(serviceList, booleanList, lvEmploye
               .getSelectionModel().getSelectedItem().getEmployes_id(),
               cbYearService.getValue());

die Klasse Service:

public class Service {

   private int services_id;
   private int week;
   private int employe;
   private int year;
   private int days;

   public Service() {

   }

   public int getServices_id() {
       return services_id;
   }

   public void setServices_id(int services_id) {
       this.services_id = services_id;
   }

   public int getEmploye() {
       return employe;
   }

   public void setEmploye(int employe) {
       this.employe = employe;
   }

   public int getYear() {
       return year;
   }

   public void setYear(int year) {
       this.year = year;
   }

   public int getWeek() {
       return week;
   }

   public void setWeek(int week) {
       this.week = week;
   }

   public int getDays() {
       return days;
   }

   public void setDays(int days) {
       this.days = days;
   }

}

so habe ich es bisher nur hinbekommen dass in der tableview in jeder Spalte der Wert der 1.Spalte steht

wer kann mir hier weiter helfen?

 

[mrBrown]

Hast du ein Problem mit dem Laden der Daten oder ein Problem mit dem Anzeigen?

Generell wäre es sinnvoll, ein Objekt zu haben, was eine Spalte in der Tabelle darstellt.
Wenn ich das grad richtig sehe, wäre das ein Objekt, was die Mitarbeiterdaten, das Jahr und eine Liste von Wochen (die jeweils die entsprechenden Daten für eine Woche enthalten) enthält?

 

[didi577]

er lädt momentan in jede Spalte der tableview die DB Spalte days rein, also 52 Einträge in jeder Spalte der tableview

es sollen je Spalte nur die Daten der Woche angezeigt werden. Also ein int je Spalte. Die Mitarbeiter ID übergebe ich weil ich die Mitarbeiter in einer ListView selektiere und zu diesem Mitarbeiter die tableview angezeigt wird. Das Jahr wird auch als Parameter übergeben da ich die tableview jahresweise lade.

die Wochen sind in der DB als int gespeichert 1 - 52, das Jahr als int 2016 - 2019, die Mitarbeiter ID als int und die Daten als int 1 - 5

die Table Columns der tableview sind mit kw1Column - kw52Column benannt

 

[mrBrown]

er lädt momentan in jede Spalte der tableview die DB Spalte days rein, also 52 Einträge in jeder Spalte der tableview

Was sind denn jetzt plötzlich "days"?

es sollen je Spalte nur die Daten der Woche angezeigt werden. Also ein int je Spalte. Die Mitarbeiter ID übergebe ich weil ich die Mitarbeiter in einer ListView selektiere und zu diesem Mitarbeiter die tableview angezeigt wird. Das Jahr wird auch als Parameter übergeben da ich die tableview jahresweise lade.

die Wochen sind in der DB als int gespeichert 1 - 52, das Jahr als int 2016 - 2019, die Mitarbeiter ID als int und die Daten als int 1 - 5

die Table Columns der tableview sind mit kw1Column - kw52Column benannt

Ich werd aus dem Teil nicht schlau - wie sieht denn jetzt dein Datenbank-Schema aus und wie brauchst du das in der Anwendung?

Hast du in deiner Anwendung schon ein passendes Datenmodell oder gibt es nur das auf der vorherigen Seite?

 

[didi577]

Hi,

wie sieht denn jetzt dein Datenbank-Schema aus

Was sind denn jetzt plötzlich "days"?

days sind die Daten

Hast du in deiner Anwendung schon ein passendes Datenmodell oder gibt es nur das auf der vorherigen Seite?

ja nur das der vorigen Seite

in der controller class habe ich dazu diesen code:

@FXML
   private TableColumn<Service, Integer> kw1Column, kw2Column, kw3Column,
           kw4Column, kw5Column, kw6Column, kw7Column, kw8Column, kw9Column,
           kw10Column, kw11Column, kw12Column, kw13Column, kw14Column,
           kw15Column, kw16Column, kw17Column, kw18Column, kw19Column,
           kw20Column, kw21Column, kw22Column, kw23Column, kw24Column,
           kw25Column, kw26Column, kw27Column, kw28Column, kw29Column,
           kw30Column, kw31Column, kw32Column, kw33Column, kw34Column,
           kw35Column, kw36Column, kw37Column, kw38Column, kw39Column,
           kw40Column, kw41Column, kw42Column, kw43Column, kw44Column,
           kw45Column, kw46Column, kw47Column, kw48Column, kw49Column,
           kw50Column, kw51Column, kw52Column;

private ObservableList<Service> services;

List<Service> serviceList = new ArrayList<>();

dann 52 x

kw1Column
               .setCellValueFactory(new PropertyValueFactory<Service, Integer>(
                       "days"));
       kw2Column
               .setCellValueFactory(new PropertyValueFactory<Service, Integer>(
                       "days"));

       kw3Column
               .setCellValueFactory(new PropertyValueFactory<Service, Integer>(
                       "days"));

...

hier lade ich die Tabelle:

mysql.loadServices(serviceList, booleanList, lvEmploye
               .getSelectionModel().getSelectedItem().getEmployes_id(),
               cbYearService.getValue());
       services = FXCollections.observableArrayList(serviceList);
       tableViewService.setItems(getservices());
       tableViewService1.setItems(getservices());
       tableViewService2.setItems(getservices());
       tableViewService3.setItems(getservices());

ich hoffe mein Anliegen is jetzt etwas durchsichtiger geworden: der Wert der Spalte "days" soll beim entsprechenden Mitarbeiter in diesen Tabellen angezeigt werden:


Danke schon mal für deine Unterstützung bis hierhin

 

[Thallius]

Wenn für jeden Mitarbeiter 52 Einträge in der DB sind und es dort kein Löcher gibt, dann kannst du einfach mit einem LEFT JOIN arbeiten und bei der Anzeige die Werte entsprechend der Liste aus indes*52 holen. Also

SELECT name, days FROM daystable LEFT JOIN employe ON (employe.id = days.employe) ORDER BY employe.name, week ASC

Dann hast du eine Liste mit immer 52 Einträgen für jeden Mitarbeiter und gibst in den entsprechenden Methoden des Tablemodels eben

int rowCount()
{
    return list.size() / 52;
}
int valueForRow(row, column)
{
    row = row * 52;
    if(column == 0)
         return list.get(row).name;
    else
    {
          column--;
         return list.get(row+column).days;
    }    
}

Gruß

Claus

 

[Meniskusschaden]

die Wochen sind in der DB als int gespeichert 1 - 52, das Jahr als int 2016 - 2019, die Mitarbeiter ID als int und die Daten als int 1 - 5

die Table Columns der tableview sind mit kw1Column - kw52Column benannt

Nur mal so nebenbei als Hinweis, weil es sich hier so auf die 52 fokussiert: Ein Jahr hat gelegentlich auch 53 Wochen. Außerdem beginnt es nicht unbedingt mit KW1, kann aber durchaus damit enden.

 

[didi577]

Danke ich werde versuchen es so umzusetzen.

Das mit den 53 Wochen ist mir bewusst aber die Werte der ersten und letzten Woche sind für diesen zweck unwesentlich