Record not being updated by prepared statement

Question

Here is the view:

<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:h="http://xmlns.jcp.org/jsf/html"
      xmlns:ui="http://xmlns.jcp.org/jsf/facelets">
    <h:head>
        <title>Books </title>
    </h:head>
    <h:body>

        <h:link value="Add Books" outcome="addBook.xhtml"></h:link>
        <table border="1">
            <tr>
                <td><b>ID</b> </td>
                <td><b>Title</b> </td>
                <td><b>Year</b> </td>
                <td><b>ISBN</b> </td>
                <td><b>Total</b> </td>
                <td><b>Remaining</b></td>

            </tr>
            <ui:repeat value="#{booksController.fetch_book_data}" var="r">

                <tr>
                    <h:form>
                        <td><h:inputText value="#{r.id}"></h:inputText></td>
                        <td><h:inputText value="#{r.title}"></h:inputText> </td>
                        <td><h:inputText value="#{r.year}"></h:inputText> </td>
                        <td><h:inputText value="#{r.isbn}"></h:inputText> </td>
                        <td><h:inputText value="#{r.total}"></h:inputText> </td>
                        <td><h:inputText value="#{r.remaining}"></h:inputText></td>
                        <td><h:commandButton value="update record" action="#{book.setUpdateBook_data()}"/></td>
                    </h:form>
                </tr>

            </ui:repeat>
        </table>

    </h:body>
</html>

View submits data to setUpdateBook_data() here is its definition:

public String setUpdateBook_data() throws SQLException {
        this.setRemaining(this.getTotal());
        Statement stmt = con.createStatement();

        String query = "UPDATE books SET title=? , year123=? , isbn=? , total=? , remaining=? where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(query);

        preparedStatement.setString(1, this.getTitle());
        preparedStatement.setString(2, this.getYear());
        preparedStatement.setString(3, this.getIsbn());
        preparedStatement.setInt(4, this.getTotal());
        preparedStatement.setInt(5, this.getRemaining());
        preparedStatement.setInt(6, this.getId());
        preparedStatement.executeUpdate();

        preparedStatement.close();
        stmt.close();

        return "success";

    }

When data is submitted application shows no error but record is not being updated. What did I miss?

I have tested following query directly from Netbeans, it works there but not here.

UPDATE books SET title='qqq' , year123='3009' , isbn='123456asdfgh' , total=10 , remaining=5 where id=5

have you solved your problem ? – Farhan Ibn Wahid Aug 15 '19 at 00:53 — Farhan Ibn Wahid, Aug 15 '19 at 00:53

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

If there was no error, but no data after executing executeUpdate you are probably not committing the transaction, because of a disabled autoCommit option.

Try to execute the commit, like the following:

preparedStatement.executeUpdate();
con.commit();

preparedStatement.close();

Just noticed that you're trying to access something from out of the context:

See this explanation here, this should explain everyting you need to know: https://stackoverflow.com/a/3791736/4956256

edited May 23 '17 at 10:26

Community

1
1

answered May 30 '15 at 15:58

Tobias Gurtzick

46
5

Do you have debugged your application and checked if all values that get passed to the query are those which you expect them to be? – Tobias Gurtzick May 30 '15 at 16:10
I have only made sure that I am sending correct values using this method http://superuser.com/questions/395919/where-is-the-post-tab-in-chrome-developer-tools-network But I don't know how to debug JSF application in Netbeans. When I marked some break points control did not stop there. – user4913383 May 30 '15 at 16:19
You'r right https://scontent-bru2-1.xx.fbcdn.net/hphotos-xta1/v/t1.0-9/11295720_399573593562296_8521051609062600339_n.jpg?oh=30f046af9d8f6cbdff934c52afd87295&oe=56084AF4 – user4913383 May 30 '15 at 16:36
Is there anyway I can get reference to 'r' ? – user4913383 May 30 '15 at 16:38
1

I think you're accessing the data not from right the context, you write but you call #{book.setUpdateBook_data()}, book can't be in the same context, thus I don't think you can't access your data via this.getTitle() – Tobias Gurtzick May 30 '15 at 16:39
you need to pass the index as parameter to your save function and then access in your save function via the index the returned "r". – Tobias Gurtzick May 30 '15 at 16:47
or just pass r as parameter directly, should work in this case. action="#{book.setUpdateBook_data(r)} – Tobias Gurtzick May 30 '15 at 16:48

Record not being updated by prepared statement

1 Answers1