java - JDBC、用户定义对象和用户定义对象的数组列表

Question

我已经创建了一个对象预留和预留数组列表，并且正在使用 JDBC 来处理预留数据库。我可以将预订插入表中，但我必须在 main 中输入预订对象的所有属性的值。但是，数据库设置为自动生成reservationID。我的问题是如何才能做到这一点，以便当我插入数据库时​​，而不是在 main 中输入reservationID，而当我插入表时，会输入自动生成的 ID。这是我第一次使用数据库，所以我对此有点陌生。

预订:

/** The Reserve ID. */
    int ReserveID;

    /** The Room ID. */
    int RoomID;

    /** The Guest ID. */
    int GuestID;

    /** The Stay duration. */
    int StayDuration;

    /** The check. */
    boolean Check;

    public reservation()
    {
        ReserveID = 0;
        GuestID = 0;
        RoomID = 0;
        StayDuration = 0;
        Check = false;

    }

    /**
     * Instantiates a new reservation.
     *
     * @param reserveID the reserve ID
     * @param roomID the room ID
     * @param guestID the guest ID
     * @param stayDuration the stay duration
     * @param check the check
     */
    public reservation(int reserveID, int guestID, int roomID, int stayDuration,      boolean check) {
        ReserveID = reserveID;
        GuestID = guestID;
        RoomID = roomID;
        StayDuration = stayDuration;
        Check = check;
    }

预订数组列表:

/** The res list. */
ArrayList<reservation> resList;

/**
 * Instantiates a new reservation collection.
 */
public reservationCollection()
{
    resList = new ArrayList<reservation>();
}

/**
 * Adds the reservation.
 *
 * @param r the r
 * @return true, if successful
 */
public boolean addReservation(reservation r) throws SQLException {
    resList.add(r);
    reservationJDBC.insertReservation(r);
    return true;
}

JDBC类插入方法:

public static boolean insertReservation(reservation newReservation) throws SQLException {
    Connection dbConnection = null;
    Statement statement = null;

    String insRes = "INSERT INTO " + RESERVATIONTABLE 
            + " (RESERVATION_ID, GUEST_ID, ROOM_ID, DURATION, STATUS) VALUES ("
            + newReservation.getReserveID() + ", "
            + newReservation.getGuestID() + ", "
            + newReservation.getRoomID() + ", " 
            + newReservation.getStayDuration() + ", "
            + newReservation.getCheck() + ")";    


    try {
        dbConnection = getDBConnection();
        statement = dbConnection.createStatement();
        statement.executeUpdate(insRes);
        System.out.println("Record is inserted into DBUSER table!");
} catch (SQLException e) {
        System.out.println(e.getMessage());
    } finally {
        if (statement != null) {
            statement.close();
        }

        if (dbConnection != null) {
            dbConnection.close();
        }
    }
    return false;
}

主要:

System.out.println("Hotel Management System - Test 1");

reservationCollection myCollection = new reservationCollection();

boolean on = true;

while(on){
    reservation r = new reservation(45, 34, 56, 78, false);

    if(myCollection.addReservation(r)){
        System.out.println("Success! The reservation has been inserted to database");
    } else {
        System.out.println("Failed! There has been a problem with inserting reservation to the database");
    }

    on = false;
}

Answer 1

您可以对 mysql 数据库表中的 RESERVATION_ID 列使用 AUTO_INCRMENT(以生成序列)，您可以查看 here关于如何在 mysql 数据库表中设置AUTO_INCRMENT。

设置 AUTO_INCRMENT 后，您无需在 JDBC 查询中传递列 RESERVATION_ID 和 newReservation.getReserveID()，如下所示:

String insRes = "INSERT INTO " + RESERVATIONTABLE 
            + " (GUEST_ID, ROOM_ID, DURATION, STATUS) VALUES ("
            + newReservation.getGuestID() + ", "
            + newReservation.getRoomID() + ", " 
            + newReservation.getStayDuration() + ", "
            + newReservation.getCheck() + ")";    

并且我强烈建议不要将上述输入参数直接硬编码到sql字符串中(这容易受到sql注入(inject)攻击)，而是使用prepareStatement setter 方法如图 here .