Anonymous
Anonymous asked in Computers & InternetProgramming & Design · 2 months ago

How to check if values exist in a database using Java?

I am trying to make a room booking system. I want it so a faculty can only reserve one room per day. I want to check if the room is still available by using the faculty name, seats, and date, if it is, their spot is served otherwise they are waitlisted. I'm not sure what the proper way is to check multiple entries. The only tables that have anything are the rooms (only 3: 101(60 seats) 102(40 seats) 201 (20 seats)) and dates with 2 to choose from. I've tried several methods and I can't seem to make things work completely. If there are any obvious mistakes or suggestions please point them out. Also if you could dumb down the language, I don't have a ton of experience.

My code:

https://imgur.com/a/caB7Sj1

Update:

I'm using the built in database netbeans has

4 Answers

Relevance
  • Anonymous
    2 months ago
    Favourite answer

    You only need two functions they are isAvailable and addBooking.

    try this...

    import java.sql.*;

    import java.text.*;

    import java.util.*;

    public class Program {

      public static void main(String[] args) {

        DbService service = new DbServiceImpl();

        String room = args[0];

        java.util.Date date = getAsDate(args[1]);

        if (service.isAvailable(room, date)) {

          service.addBooking(room, date);

        }

      }

      private static java.util.Date getAsDate(String value) {

        SimpleDateFormat sdf = new SimpleDateFormat("d/MM/yyyy");

        java.util.Date date = null;

        try {

          date = sdf.parse(value);

        } catch (ParseException ignore) {}

        return date;

      }

    }

    interface DbService {

      boolean isAvailable(String room, java.util.Date date);

      void addBooking(String room, java.util.Date date);

    }

    class DbServiceImpl implements DbService {

      public boolean isAvailable(String room, java.util.Date date) {

        final String sql = "SELECT 1 FROM bookings WHERE room=? AND date=?";

        boolean result = true;

        Connection con = null;

        PreparedStatement ps = null;

        ResultSet rst = null;

        try {

          con = getConnection();

          ps = con.prepareStatement(sql);

          ps.setString(1, room);

          ps.setString(2, new SimpleDateFormat("yyyy-MM-dd").format(date));

          rst = ps.executeQuery();

          result = !rst.next();

          System.out.printf("is_available=%s%n", result);

        } catch (SQLException e) {

          e.printStackTrace();

        } finally {

          cleanup(con, ps, rst);

        }

        return result;

      }

      public void addBooking(String room, java.util.Date date) {

        final String sql = "INSERT INTO bookings (room, date) VALUES (?, ?)";

        Connection con = null;

        PreparedStatement ps = null;

        try {

          con = getConnection();

          ps = con.prepareStatement(sql);

          ps.setString(1, room);

          ps.setString(2, new SimpleDateFormat("yyyy-MM-dd").format(date));

          ps.executeUpdate();

          System.out.println("Booking added");

        } catch (SQLException e) {

          System.err.println("Booking failed");

          e.printStackTrace();

        } finally {

          cleanup(con, ps, null);

        }

      }

      private final Connection getConnection() {

        Connection con = null;

        try {

          Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

          con =

            DriverManager.getConnection(

              "jdbc:derby://localhost:1527/bookingsdb;create=false"

            );

        } catch (Exception e) {

          e.printStackTrace();

        }

        return con;

      }

      private final void cleanup(

        Connection con,

        PreparedStatement ps,

        ResultSet rst

      ) {

        if (rst != null) {

          try {

            rst.close();

            rst = null;

          } catch (SQLException e) {

            e.printStackTrace();

          }

        }

        if (ps != null) {

          try {

            ps.close();

            ps = null;

          } catch (SQLException e) {

            e.printStackTrace();

          }

        }

        if (con != null) {

          try {

            con.close();

            con = null;

          } catch (SQLException e) {

            e.printStackTrace();

          }

        }

      }

    }

  • Chris
    Lv 7
    2 months ago

    You need a reservation table with columns for faculty name, date and room number.

    This way you can easily check if a room is booked on a certain day, or if a faculty already has booked a room on a certain day, etc.

    You seem to be already doing that but I'm not going to look at blurry images of tiny code when you can easily put your actual code online, for instance on pastebin.com

  • Anonymous
    2 months ago

    use mysql       

  • Not
    Lv 4
    2 months ago

    Basically to check multiple entries you need a loop. The obvious mistake is that you program but haven't learned the language. A less obvious mistake is that you lose the point of having a database if you do the operations outside SQL querying. In other words, there is no point of having a SQL database at all if you are going to do the querying in java.

Still have questions? Get answers by asking now.