DinosaurHunter
DinosaurHunter

Reputation: 692

Updating a specific record in a database in a web app

I have a database with details of plots across a number of sites. My web app shows plots available to book and provides a button which, when clicked, should book a specific plot. However I cannot get my book() method to work - it's not updating the table. When the h:commandButton is clicked it should update BOOKINGNO column on the specific row of the table overwriting <null> with a random 6 digit booking number.

I can't see where I'm going wrong. I'm using NetBeans & GlassFish (if it matters).

The snippet below shows the relevant code from my most recent attempt:

table sample

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 page

<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>

Can it be done this way?

I've tried doing the same thing but using persistence. But I get syntax errors: 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;
}

and the 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>

Upvotes: 0

Views: 135

Answers (1)

Predrag Maric
Predrag Maric

Reputation: 24433

If everything else is wired up ok, to update a row in the database by using JDBC connection, try this

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

Upvotes: 1

Related Questions