DataTable - InCell Editing--- how to update database with edited values

Question

 I have a managed bean called registerBean.

        private List<registerBean> std;

        public void onEdit(RowEditEvent event) {
            registerBean ul = (registerBean) event.getObject();
            Connection con = null;
            PreparedStatement stat = null;
            ResultSet rs = null;

            try {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db", "root", "");
                stat = con.prepareStatement("update regtbl set fname=? where rno=?");
                stat.setString(1, ul.fname);
                stat.setInt(3, ul.rno);
                stat.executeUpdate();
                con.commit();
                stat.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    This is my Facelets page

    std.xhtml

    <!-- language: xhtml -->

        <p:dataTable  value="#{registerBean.list}"  var="rb"  rows="10" paginator="true"
            paginatorTemplate=" {FirstPageLink} {PreviousPageLink} {CurrentPageReport}  {NextPageLink}  {LastPageLink} {RowsPerPageDropdown}"
            rowsPerPageTemplate="5,15,20" rowKey="#{rb.fname}" selectionMode="single"
            selection="#{registerBean.selectedStudent}"
            scrollable="true" resizableColumns="true"  editable="true" >
            <p:ajax event="rowEdit" listener="#{registerBean.onEdit}"
                update=":form:messages" />
            <p:ajax event="rowEditCancel" listener="#{registerBean.onCancel}"
                update=":form:messages" />
            <p:column headerText="Roll No" filterBy="#{rb.rno}" sortBy="#{rb.rno}" width="124">
                <h:outputText value="#{rb.rno}" />
            </p:column>
            <p:column headerText="First Name" filterBy="#{rb.fname}" sortBy="#{rb.fname}" width="124">
                <p:cellEditor>
                    <f:facet name="output">
                        <h:outputText value="#{rb.fname}" />
                    </f:facet>
                    <f:facet name="input">
                        <h:inputText value="#{rb.fname}" />
                    </f:facet>
                </p:cellEditor>
            </p:column>
            <!-- more columns... -->
        </p:dataTable>

Am I going wrong some where here ..... this method is called to save the edited rows of datatable.... but database records are not getting updated. When I edit a particular row in datatable and click on right mark to save the changes onEdit method is being called which is inside bean. The issue is database value are remaining the same not getting updated list is of type registerBean. In registerBean I am performing other operations like Inserting (working fine ), getting values from database that are displayed in datatable ... facing problem in row editing.

Thanks in Advance

Note: Check [this link][1]. My table is similar to this but i am using database.

[1]: http://www.primefaces.org/showcase/ui/datatableEditing.jsf

Answer 1

Apart from the missing finally block, the unnecessarily repeated Class#forName() call and the completely unnecessary newInstance() call on it, tight-coupling data access code in a controller class, not using a fast connection pool, all which are unrelated to the concrete problem, the code posted so far looks okay.

Only, you're setting the rno value as 3rd parameter while you've only 2 in your prepared SQL string.

    stat = con.prepareStatement("update regtbl set fname=? where rno=?");
    stat.setString(1, ul.fname);
    stat.setInt(3, ul.rno);

I'm not sure if this is careless oversimplification or if you have really read the server logs for any indication of the e.printStackTrace(), but this is not right. Make sure that the ul.rno is set on parameter index 2. I'd also rather fix that silly e.printStackTrace() which causes the code to continue to run which you obviously don't want to happen.

} catch (Exception e) {
    throw new FacesException(e);
}

If the SQL was indeed right after all and you indeed don't get an exception, then apparently there's simply no row which matches the WHERE clause. The PreparedStatement#executeUpdate() returns an int representing the affected rows. I suggest to get hold of it and verify if there was really an update:

int affectedRows = stat.executeUpdate();

if (affectedRows == 0) {
    // No rows affected!
}

Also check what value of ul.rno you've set it with.

---

Update finally you took the little effort to read the server logs for any evidence of the exception. You should understand that exceptions are very important to learn about as they basically are the whole answer to your concrete problem. The one which you retrieved is basically telling that in the following piece of code

public String getBdate() {
    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
    bdate = sdf.format(dob);
    return bdate;
}

the variable dob is null. You should be doing a nullcheck:

public String getBdate() { 
    if (dob != null) {
        bdate = new SimpleDateFormat(DATE_FORMAT).format(dob);
    }

    return bdate;
}