Datatable not saving any edits

Question

Hi guys i have a datatable from the primefaces library, which i am trying to include the feature of editing each cell and when the user presses a "save" button at the row it will update the edited values to a database, however currently when a user edits a cell what ever they change the cell to the second they click off the cell the value goes the same as what it was before, it does not save the new values, and also another issues is when the user presses save at the end of the row the values passwed to the database is always Null, how can i solve these two issues ?

here is the xhtml

<p:dataTable id="dataTable" var="u" value="#{userBean.getUserList()}"  
                                 paginator="true" rows="10"  

                                 paginatorTemplate="{CurrentPageReport}  {FirstPageLink} {PreviousPageLink} {PageLinks} {NextPageLink} {LastPageLink} {RowsPerPageDropdown}"  
                                 rowsPerPageTemplate="5,10,15,25"
                                 editable="true" editMode="cell"
                                 >  
                        <p:column>
                            <!--
                            <p:ajax event="rowEdit" listener="{u.onEdit}" update=":form:messages" />  
                            <p:ajax event="rowEditCancel" listener="{u.onCancel}" update=":form:messages" />  
                            -->

                            <f:facet name="header">
                                User ID
                            </f:facet>
                            #{u.userID}
                        </p:column>

                        <p:column headerText="Name" >
                            <p:cellEditor>
                                <f:facet name="output">
                                    <h:outputText value="#{u.name}" />
                                </f:facet>
                                <f:facet name="input">
                                    <p:inputText id="NameInput" value="#{u.name}"
                                                 style="width:96%" />
                                </f:facet>
                            </p:cellEditor>
                        </p:column>

                        <p:column headerText="Email">
                            <p:cellEditor>
                                <f:facet name="output">
                                    <h:outputText value="#{u.email}" />
                                </f:facet>
                                <f:facet name="input">
                                    <p:inputText id="EmailInput" value="#{u.email}"
                                                 />
                                </f:facet>
                            </p:cellEditor>
                        </p:column>

                        <p:column headerText="Address">
                            <p:cellEditor>
                                <f:facet name="output">
                                    <h:outputText value="#{u.address}" />
                                </f:facet>
                                <f:facet name="input">
                                    <p:inputText id="AddressInput" value="#{u.address}"
                                                 />
                                </f:facet>
                            </p:cellEditor>
                        </p:column>

                        <p:column>
                            <f:facet name="header">
                                Created Date
                            </f:facet>
                            #{u.created_date}
                        </p:column>

                        <p:column>
                            <f:facet name="header">
                                Delete
                            </f:facet>
                            <h:commandButton value="Delete" action="#{user.delete(u.userID)}" />
                        </p:column>

                        <p:column>
                            <f:facet name="header">
                                Save Edit
                            </f:facet>
                            <h:commandButton value="Save" action="#{user.editData(u.userID)}" />
                        </p:column>
                    </p:dataTable>

and here is the backing bean although currently this is only updating the database with the values from the databale

public void editData(long userID) {
        System.out.println(name);
        PreparedStatement ps = null;
        Connection con = null;
        if (userID != 0) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
                System.out.println(name);
                String sql = "UPDATE user1 set name = '" + name + "', email = '" + email + "', address = '" + address + "' WHERE userId=" + userID;
                ps = con.prepareStatement(sql);
                int i = ps.executeUpdate();
                if (i > 0) {
                    System.out.println("Row updated successfully");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    con.close();
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

the datable originally gets its values from the database

Thanks

Here is how i populate the datatable with values from the database,/*

 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package richard.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

import richard.test.User;

@ManagedBean(name = "userBean")
@SessionScoped
public class UserBean {

    List<User> list;
    PreparedStatement ps = null;
    Connection con = null;
    ResultSet rs = null;

    public List<User> getList() {
        return list;
    }

    public List<User> getUserList() {
        list = new ArrayList<User>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
            String sql = "select * from user1";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User usr = new User();
                usr.setUserID(rs.getLong("userId"));
                usr.setName(rs.getString("name"));
                usr.setEmail(rs.getString("email"));
                usr.setAddress(rs.getString("address"));
                usr.setCreated_date(rs.getDate("created_date"));
                list.add(usr);
                Map<Long, Boolean> checked = new HashMap<Long, Boolean>();
                List<User> checkedItems = new ArrayList<User>();
                for (User item : list) {
                    if (checked.get(item.getUserID()) != null) {
                        checkedItems.add(item);
                        usr.delete(usr.getUserID());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}

HERE is the full user bean code

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package richard.test;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.component.UIColumn;
import javax.faces.event.ActionEvent;
import org.primefaces.component.datatable.DataTable;
import org.primefaces.event.CellEditEvent;
import org.primefaces.event.RowEditEvent;

@ManagedBean
@RequestScoped
public class User {

    List<User> list;
    PreparedStatement ps = null;
    Connection con = null;
    ResultSet rs = null;
    private long userID = 1;
    private String name;
    private String address;
    private Date created_date;
    private String email;
    boolean editable;

    public boolean isEditable() {
        return editable;
    }

    public void setEditable(boolean editable) {
        this.editable = editable;
    }

    public String editAction(User order) {
        order.setEditable(true);
        return null;
    }

    public long getUserID() {
        return userID;
    }

    public void setUserID(long userID) {
        this.userID = userID;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Date getCreated_date() {
        return created_date;
    }

    public void setCreated_date(Date created_date) {
        this.created_date = created_date;
    }

    public String add() {

        System.out.println("In add");
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        int i = 0;

        if (userID != 0) {
            PreparedStatement ps = null;
            Connection con = null;
            try {
                System.out.println("about to add to db");
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
                String sql = "INSERT INTO user1( name, email, address, created_date) VALUES(?,?,?,?)";
                ps = con.prepareStatement(sql);
                ps.setString(1, name);
                ps.setString(2, email);
                ps.setString(3, address);
                if (created_date != null) {
                    String date = fmt.format(created_date);
                    Object obj = date;
                    if (obj == null) {
                        ps.setDate(4, null);
                    } else {
                        java.sql.Date dt = java.sql.Date.valueOf(new String(date));
                        ps.setDate(4, dt);
                    }
                }

                i = ps.executeUpdate();
                System.out.println("Data Added Successfully");

            } catch (Exception e) {
                System.out.println(e);
            } finally {
                try {
                    con.close();
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (i > 0) {
                return "output";
            } else {
                return "invalid";
            }
        } else {
            return "invalid";
        }
    }

    public void delete(long userID) {
        PreparedStatement ps = null;
        Connection con = null;
        if (userID != 0) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
                String sql = "DELETE FROM user1 WHERE userId=" + userID;
                ps = con.prepareStatement(sql);
                int i = ps.executeUpdate();
                if (i > 0) {
                    System.out.println("Row deleted successfully");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    con.close();
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }



    public void editData(long userID) {
        PreparedStatement ps = null;
        Connection con = null;
        if (userID != 0) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
                System.out.println(name);
                String sql = "UPDATE user1 set name = '" + name + "', email = '" + email + "', address = '" + address + "' WHERE userId=" + userID;
                ps = con.prepareStatement(sql);
                int i = ps.executeUpdate();
                if (i > 0) {
                    System.out.println("Row updated successfully");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    con.close();
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

Answer 1

The main problem is that you have two User objects:

• User u - currently selected user.
• User user - just a ManagedBean, most probably with no state (no id, no name, no address).

When performing the edit operation you try to use u.id and user.name, user.address, etc. What you need to do, is to take all the values from the u object.

There are many different approaches (I've never used the third one, but it should work and it's the closest to what you've already have):

A1. You can keep currently selected object in your UserBean and set it with setPropertyActionListener:

<f:setPropertyActionListener target="#{userBean.selectedUser}" value="#{u}" />

Then you can call the edit method implemented in the same bean (it has full access to selectedUser object and it can implement the edit itself of just delegate the action to object with edit method implementation).

A2. You can place your edit method in the UserBean and pass entire User object as a parameter:

<h:commandButton value="Save" action="#{userBean.editData(u)}" />

A3. Or you can just call:

<h:commandButton value="Save" action="#{u.editData()}" />

instead of:

<h:commandButton value="Save" action="#{user.editData(u.userID)}" />