java - 在 Web 应用程序中更新数据库中的特定记录

Question

我有一个数据库，其中包含多个站点的地 block 详细信息。我的网络应用程序显示可预订的地 block ，并提供一个按钮，单击该按钮后，应预订特定的地 block 。但是我无法获取我的book()工作方法 - 它不更新表。当 h:commandButton单击它应该更新 BOOKINGNO表的特定行上的列覆盖 <null>带有随机的 6 位数预订号码。

我看不出我错在哪里。我正在使用 NetBeans 和 GlassFish(如果重要的话)。

下面的代码片段显示了我最近尝试的相关代码:

表格示例

PLOT
PLOTNO ACCOMTYPE STARTDATE ENDDATE BOOKINGNO
1 Tent 2014-02-03 2014-02-06 <null>

bean

public String book(int plotNo) throws SQLException
{
    // check whether dataSource was injected by the server
    if (dataSource == null)
        throw new SQLException("Unable to obtain DataSource");
    // obtain a connection from the connection pool
    Connection connection = DriverManager.getConnection
        ("jdbc:derby://localhost:1527/2Day4U", "APP", "APP");
    // check whether connection was successful
    if (connection == null)
        throw new SQLException("Unable to connect to DataSource");
    try
    {
        int newBookingNo = generateBookingNo();
        setBookingNo(newBookingNo);        
        String updateTableSQL = "UPDATE PLOT SET BOOKINGNO = ? WHERE PLOTNO = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(updateTableSQL);
        preparedStatement.setInt(1, getBookingNo());
        preparedStatement.setInt(2, plotNo);
        preparedStatement.executeUpdate();    
        return "ConfirmBooking"; // go to ConfirmBooking.xhtml page
    } // end try
    finally
    {
        connection.close(); // return this connection to pool
    } // end finally
} // end method book

public int generateBookingNo(){
    Random r = new Random();
    int rand = r.nextInt((999999 - 1) + 1);
    setBookingNo(rand);
    return bookingNo;
}

public int getBookingNo(){
    return bookingNo;
}
public void setBookingNo(int bookingNo){
    this.bookingNo = bookingNo;
}

JSF 页面

<h:dataTable value="#{dealsBean.plotDetailsLiverpool}" var="item"
                 rowClasses="oddRows, evenRows" headerClass="header"
                 styleClass="table" cellpadding="5" cellspacing="0">
        <h:column>
            <f:facet name="header">Plot</f:facet>
                #{item.PLOTNO}
        </h:column>
        <h:column>
            <f:facet name="header">Accom Type</f:facet>
                #{item.ACCOMTYPE}
        </h:column>
        <h:column>
            <f:facet name="header">Sleeps</f:facet>
                #{item.SLEEPINGCAPACITY}
        </h:column>
        <h:column>
            <f:facet name="header">Toilet?</f:facet>
                #{item.TOILETFACILITY}
        </h:column>
        <h:column>
            <f:facet name="header">Price (£)</f:facet>
                #{item.PRICE}
        </h:column>
        <h:column>
            <f:facet name="header">Start Date</f:facet>
                #{item.STARTDATE}
        </h:column>
        <h:column>
            <f:facet name="header">End Date</f:facet>
                #{item.ENDDATE}
        </h:column>
        <h:column>
            <f:facet name="header">Book?</f:facet>
            <h:commandButton onclick="if (!confirm('Do you want to book this holiday?')) return false"
                             value="Book"
                             action="#{dealsBean.book(dealsBean.plotNo)}"
                             rendered="#{dealsBean.bookingNo >= 0 }">
            </h:commandButton>
        </h:column>
    </h:dataTable>

这样可以吗？

我尝试过做同样的事情，但要坚持不懈。但我收到语法错误: java.lang.RuntimeException: java.lang.IllegalArgumentException: Object: 750,573 is not a known entity type.

@PersistenceContext(unitName = "2Day4UPU")
private EntityManager em;
@Resource
private javax.transaction.UserTransaction utx;

private int bookingNo;

public int getBookingNo(){
    return bookingNo;
}
public void setBookingNo(int bookingNo){
    this.bookingNo = bookingNo;

public String updateEntity(Object object){
    int newBookingNo = generateBookingNo();
    setBookingNo(newBookingNo);

    try {
        utx.begin();
        em.merge(bookingNo);
        utx.commit();
        return "ConfirmBooking";
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
public int generateBookingNo(){
    Random r = new Random();
    int rand = r.nextInt((999999 - 1) + 1);
    setBookingNo(rand);
    return bookingNo;
}

和 JSF:

<h:commandButton onclick="if (!confirm('Do you want to book this holiday?')) return false"
                             value="Book"
                             action="#{dealsBean.updateEntity(item.bookingNo)}"
                             rendered="#{dealsBean.bookingNo != null}">
            </h:commandButton>

Answer 1

如果其他一切都连接正常，要使用 JDBC 连接更新数据库中的行，请尝试此操作

public String book(int plotNo) throws SQLException
{
    ...

    try
    {
        int newBookingNo = generateBookingNo();
        setBookingNo(newBookingNo);        
        String updateTableSQL = "UPDATE PLOT SET BOOKINGNO = ? WHERE PLOTNO = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(updateTableSQL);
        preparedStatement.setInt(1, getBookingNo());
        preparedStatement.setInt(2, plotNo);
        preparedStatement.executeUpdate();    
        return "ConfirmBooking"; // go to ConfirmBooking.xhtml page
    } // end try
    finally
    {
        connection.close(); // return this connection to pool
    } // end finally
} // end method book