Using Java check to determine if there is data in mySQL table before updating table

I am new to Java and am trying to see if the date (actually day of week) column in MySQL contains the date before updating the table. If the date already exist the table should not be updated and if the date doesn’t exist the table should be updated. I’ve tried different code. My prior code either updated or didn’t update the table depending on whether I has “!rs.next()” or “rs.next()” instead of depending on whether the date was in the table.

    @PostMapping("/add")

public Object processMenuAddForm(@ModelAttribute @Valid Menu menu, Errors errors, HttpServletRequest request,
                                 Model model/*, @PathVariable int id, @PathVariable String Date*/)
                                 throws ParseException, SQLException {

    User user = getUserFromSession(request.getSession());

    if (errors.hasErrors()) {
        model.addAttribute("title", "Add");
        return "/menu/add";
    }

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/family_organizer",
            "family_organizer", "LiftOff2021");
    try (PreparedStatement checkDateExists = con.prepareStatement(
            "SELECT COUNT(date) AS count FROM menu WHERE user_id = ? AND date = ?")) {
        checkDateExists.setInt(1, user.getId());
        checkDateExists.setString(2, (menu.getDate()));

    try (ResultSet rs = checkDateExists.executeQuery()) {

          if (!rs.next()) {

            Menu newMenu = new Menu(menu.getDate(), menu.getMainCourse(), menu.getVegetable(), menu.getMainSide(),
                    menu.getAdditionalSide(), menu.getDessert(), user);
            menuRepository.save(newMenu);

        } else {
            return "redirect:/menu/view";
    }

    } catch (SQLException err) {
        System.out.println(err.getMessage());
    }

        return "redirect:/menu/view";

   }
  }

My current attempt doesn’t update my table at all.

    @PostMapping("/add")

public Object processMenuAddForm(@ModelAttribute @Valid Menu menu, Errors errors, HttpServletRequest request,
                                 Model model/*, @PathVariable int id, @PathVariable String Date*/)
                                 throws ParseException, SQLException {

    User user = getUserFromSession(request.getSession());

    if (errors.hasErrors()) {
        model.addAttribute("title", "Add");
        return "/menu/add";
    }

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/family_organizer",
            "family_organizer", "LiftOff2021");
    try (PreparedStatement checkDateExists = con.prepareStatement(
            "SELECT COUNT(date) AS count FROM menu WHERE user_id = ? AND date = ?")) {
        checkDateExists.setInt(1, user.getId());
        checkDateExists.setString(2, (menu.getDate()));

    try (ResultSet rs = checkDateExists.executeQuery()) {

          rs.first();
          if (rs.getInt("count") == 0) {

            Menu newMenu = new Menu(menu.getDate(), menu.getMainCourse(), menu.getVegetable(), menu.getMainSide(),
                    menu.getAdditionalSide(), menu.getDessert(), user);
            menuRepository.save(newMenu);

        } else {
            return "redirect:/menu/view";
    }

    } catch (SQLException err) {
        System.out.println(err.getMessage());
    }

        return "redirect:/menu/view";

   }
 }

Any help would be appreciated.

Answer

I got it to work. Instead of “rs.first();”, I changed it to “rs.next();”. Now my code doesn’t post anything unless the date currently isn’t being used.

Advertisement

Answer