public boolean checkPnr(String param) {
// Local variables
String query;
ResultSet rs;
PreparedStatement stmt;
boolean result = false;
try {
// Set the SQL statement into the query variable
query = "SELECT personnummer FROM Stamkund WHERE personnummer = ?";
stmt = con.prepareStatement(query);
stmt.setString(1, param);
// Execute the SQL statement that is prepared in the variable stmt
// and store the result in the variable rs.
rs = stmt.executeQuery();
// Sets result to true if personnummer exists
result = rs.next();
// Close the variable stmt and release all resources bound to it
stmt.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("Data check error");
}
return result;
}
// insertStamkund takes six strings as input and calls randomNumber for an integer
public int insertStamkund(
String pnrParam,
String fnameParam,
String snameParam,
String addrParam,
String mailParam,
String cellParam) {
// Call randomNumber() and stores the value to cardParam.
int cardParam = randomNumber(1000, 9999);
// Set the SQL statement into the query variable
String query =
"INSERT INTO Stamkund (kortnummer,personnummer,förnamn,"
+ "efternamn,adress,epost,mobilnummer) VALUES (?,?,?,?,?,?,?)";
try {
// Create a statement associated to the connection and the query.
// The new statement is placed in the variable stmt.
PreparedStatement stmt = con.prepareStatement(query);
// Provide the values for the ?'s in the SQL statement, from 1 to 7.
stmt.setInt(1, cardParam);
stmt.setString(2, pnrParam);
stmt.setString(3, fnameParam);
stmt.setString(4, snameParam);
stmt.setString(5, addrParam);
stmt.setString(6, mailParam);
stmt.setString(7, cellParam);
// Execute the SQL statement that is prepared in the variable stmt
stmt.executeUpdate();
// Close the variable stmt and release all resources bound to it
stmt.close();
// Commit the changes made to the database.
con.commit();
} catch (Exception e) {
e.printStackTrace();
System.out.println("Error on Data Insert");
}
// Returns card number for user display
return cardParam;
}
// Method for generating and returning a randomized card number in the range of 1000-9999.
// The range can be edited in the insertStamkund method.
public int randomNumber(int min, int max) {
// Local variables
String query;
ResultSet rs;
PreparedStatement stmt;
int internalNumber;
// A random number is generated in the selected range min-max (1000-9999)
Random rand = new Random();
internalNumber = rand.nextInt((max - min) + 1) + min;
try {
// Set the SQL statement into the query variable
// We check if our generated number exists in the database.
query = "SELECT kortnummer FROM Stamkund WHERE kortnummer = ?";
stmt = con.prepareStatement(query);
stmt.setInt(1, internalNumber);
// Execute the SQL statement that is prepared in the variable stmt
// and store the result in the variable rs.
// If we receive a ResultSet, boolean result is set to 1, and the generated number exists.
rs = stmt.executeQuery();
boolean result = rs.next();
// If result is true, we call ourselves for a new number
if (result) {
return randomNumber(min, max);
}
// Close the variable stmt and release all resources bound to it
stmt.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("Data Insert error");
}
// If result is false, returns the unique card number
return internalNumber;
}
// 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 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");
}
}