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

Question

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.

Why do you use setString on a date column? Create a proper date object corresponding to the type I the dB instead. That will sa e you a lot of trouble. — Just another Java programmer, Sep 25 '21 at 15:49
Have you tried putting in some debug, like after the line where you say `rs.first();` put a line that says `System.out.println(rs.getInt("count");` (I personally prefer to use a proper logging framework - Slf4j et. al.) — Bill Naylor, Sep 25 '21 at 15:53
Also you might want to look at this question: https://stackoverflow.com/questions/867194/java-resultset-how-to-check-if-there-are-any-results?rq=1 — Bill Naylor, Sep 25 '21 at 16:03

score 0 · Accepted Answer · answered Sep 25 '21 at 17:07

0

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.

answered Sep 25 '21 at 17:07

Coder2021

1
2

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

1 Answers1