// Method for checking store stock and printing it to TableView
public void storeStock(String store) {
data = FXCollections.observableArrayList();
// Local variables
ResultSet rs;
PreparedStatement stmt;
// Store query string to variable
String query =
"SELECT Förpackning.[streckkod], LagerfördVara.[antalIButik], LagerfördVara.[maxantal] FROM Förpackning, LagerfördVara, Butik WHERE Förpackning.[streckkod]=LagerfördVara.[streckkod] AND LagerfördVara.[butik_id]=Butik.[butik_id] AND Butik.[namn]=?";
try {
// The new statement is placed in the variable stmt.
// Provide the value for the first ? in the SQL statement.
// Execute query and save ResultSet to variable rs.
stmt = con.prepareStatement(query);
stmt.setString(1, store);
rs = stmt.executeQuery();
// Clearing tables of old data
tableview.getItems().clear();
tableview.getColumns().clear();
// Table columns added dynamically using ResultSet metadata.
for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
// We are using non property style for making dynamic table
final int j = i;
TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i + 1));
col.setCellValueFactory(
new Callback<
TableColumn.CellDataFeatures<ObservableList, String>, ObservableValue<String>>() {
public ObservableValue<String> call(
TableColumn.CellDataFeatures<ObservableList, String> param) {
return new SimpleStringProperty(param.getValue().get(j).toString());
}
});
tableview.getColumns().addAll(col);
System.out.println("Column [" + i + "] ");
}
// Data added to ObservableList
while (rs.next()) {
// Iterate Row
ObservableList<String> row = FXCollections.observableArrayList();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
// Iterate Column
row.add(rs.getString(i));
}
// Console printout
System.out.println("Row [1] added " + row);
data.add(row);
}
// Data added to TableView
tableview.setItems(data);
// Close the variable stmt and release all resources bound to it
// Any ResultSet associated to the Statement will be automatically closed too.
stmt.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("Error on building Data");
}
}
// Method for displaying all available product types
public void createStatement() {
data = FXCollections.observableArrayList();
// Local variables
ResultSet rs;
Statement stmt;
String query = "SELECT namn FROM Produktgrupp ORDER BY namn ASC";
try {
stmt = con.createStatement();
rs = stmt.executeQuery(query);
// Clearing tables of old data
tableview.getItems().clear();
tableview.getColumns().clear();
// Table columns added dynamically using ResultSet metadata.
for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
// We are using non property style for making dynamic table
final int j = i;
TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i + 1));
col.setCellValueFactory(
new Callback<
TableColumn.CellDataFeatures<ObservableList, String>, ObservableValue<String>>() {
public ObservableValue<String> call(
TableColumn.CellDataFeatures<ObservableList, String> param) {
return new SimpleStringProperty(param.getValue().get(j).toString());
}
});
tableview.getColumns().addAll(col);
System.out.println("Column [" + i + "] ");
}
// Data added to ObservableList
while (rs.next()) {
// Iterate Row
ObservableList<String> row = FXCollections.observableArrayList();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
// Iterate Column
row.add(rs.getString(i));
}
data.add(row);
}
// Data added to TableView
tableview.setItems(data);
// Close the variable stmt and release all resources bound to it
// Any ResultSet associated to the Statement will be automatically closed too.
stmt.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("Error on Building Data");
}
}
// Method for presenting all available brands of a selected product.
public void preparedStatement(String ptype) {
data = FXCollections.observableArrayList();
// Local variables, query statement saved to variable query.
ResultSet rs;
PreparedStatement stmt;
String query =
"SELECT DISTINCT Märke.namn FROM "
+ "Märke, Märkesprodukt, Produkt, Produktbeskrivning, Produktgrupp "
+ "WHERE Märke.[märke_id]=Märkesprodukt.[märke_id] "
+ "AND Märkesprodukt.[produkt_id]=Produkt.[produkt_id] "
+ "AND Produkt.[pbeskrivning_id]=Produktbeskrivning.[pbeskrivning_id] "
+ "AND Produktbeskrivning.[pgrupp_id]=Produktgrupp.[pgrupp_id] "
+ "AND Produktgrupp.[namn]= ?";
try {
// Create a statement associated to the connection con.
// The new statement is placed in the variable stmt.
// Provide the value for the first ? in the SQL statement.
// Execute query and save ResultSet to variable rs.
stmt = con.prepareStatement(query);
stmt.setString(1, ptype);
rs = stmt.executeQuery();
// Clearing tables of old data
tableview.getItems().clear();
tableview.getColumns().clear();
// Table columns added dynamically using ResultSet metadata.
for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
// We are using non property style for making dynamic table
final int j = i;
TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i + 1));
col.setCellValueFactory(
new Callback<
TableColumn.CellDataFeatures<ObservableList, String>, ObservableValue<String>>() {
public ObservableValue<String> call(
TableColumn.CellDataFeatures<ObservableList, String> param) {
return new SimpleStringProperty(param.getValue().get(j).toString());
}
});
tableview.getColumns().addAll(col);
System.out.println("Column [" + i + "] ");
}
// Data added to ObservableList
while (rs.next()) {
// Iterate Row
ObservableList<String> row = FXCollections.observableArrayList();
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
// Iterate Column
row.add(rs.getString(i));
}
// Console printout
System.out.println("Row [1] added " + row);
data.add(row);
}
// Data added to TableView
tableview.setItems(data);
// Close the variable stmt and release all resources bound to it
// Any ResultSet associated to the Statement will be automatically closed too.
stmt.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("Error on Building Data");
}
}