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;
  }
示例#2
0
文件: Main.java 项目: 8CAKE/ALE
  public boolean validLogin(String username, String password) {
    boolean validUser = false;
    try {

      dbStm =
          dbCon.prepareStatement(
              "SELECT * FROM userInfo WHERE username = "******"\"" + username + "\"" + ";");

      dbRs = dbStm.executeQuery();

      while (dbRs.next()) {
        String un = "";
        String pw = "";

        un = dbRs.getString("username");
        System.out.println(un + " " + username);
        pw = dbRs.getString("password");
        System.out.println(pw + " " + password);

        if (((username.equals(un) == true) && ((password.equals(pw) == true)))) {
          validUser = true;
          System.out.println("Valid User");
        }
      }

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

    return validUser;
  }
  // 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;
  }
  // 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 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");
    }
  }