JSP: How to increase a value of a database in JSP? Error when i click the button

Question

------ 2 DAYS AGO --------

"I made a database only for the "likes" of a website. The database has a table (Likes) with several fields (Like1, Like2, etc ...)

In a JSP file I made the connection and show the value of a field (Like1).

But what I want to do is: by clicking on a certain text, the value increases by one, updating that value in the database from Like1 to Like1 + 1."

--- EDIT ---

When i click the button, show me this:

ERROR

When I refresh, the data does not change. So I think if I did not show the error, it would work.

What I want is, when you press the button, the DB data increment by 1.

Structure:

Structure

index.jsp:

<body>
    <div class="col-lg-12">
        <form method="post" action="likeCount">
            <button type="submit" name="click"><b style="color:#ff5858;font-size: 24px;">❤</b></button>
            <% try {
                Connection conn = DBConnect.connect();
                PreparedStatement pstmt = conn.prepareStatement("SELECT Like1 from Likes");
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {%>
                <input type="text" hidden name="counts" value="<%=rs.getInt("Like1")%>" />

        </form>
        <span style="font-weight: bold"><%=session.getAttribute("count")==null)?rs.getInt("Like1"):(session.getAttribute("count"))%></span><% }
            } catch (SQLException ex) {
                System.out.println("Error in select: " + ex.getMessage());
                   }%>
    </div>
</body>

DBConnect.java:

package myPack;

import com.mysql.jdbc.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnect {

    public static Connection connect() throws SQLException {
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/Likes";
        String username = "root";
        String password = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Error : " + e.getMessage());
        }
        return conn;
    }
}

likeCount.java:

package myPack;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class likeCount {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    /*check whether the button is clicked*/
    if (request.getParameter("click") != null) {
        try {
            /*getting the number of likes*/
            String x = request.getParameter("counts");
            int c = Integer.parseInt(x);
            int count = c;
            count = count + 1; //increment the value

            Connection conn = DBConnect.connect();
            PreparedStatement ps = conn.prepareStatement("UPDATE Likes SET Like1 = ?");
            ps.setInt(1, count);
            int num = ps.executeUpdate();
            if (num > 0) {
                HttpSession session = request.getSession();
                session.setAttribute("count", count);
                response.setIntHeader("Refresh", 3);
                response.sendRedirect("index.jsp");
            }

        } catch (SQLException e) {
            System.out.println("Error in update :" + e.getMessage());
            }
        }
    }
}

Read this: http://engineering.harrys.com/2017/06/28/atomic-operations-in-sql.html and https://stackoverflow.com/questions/4358732/is-incrementing-a-field-in-mysql-atomic. The idea is to write it in a way that makes sure your increment is atomic. — Nic3500, Jul 14 '18 at 02:35

score 0 · Accepted Answer · edited Aug 28 '18 at 04:26

In this case you will need a servlet and a database connection classes.

First, this is the database connection class. DBConnect.java

public class DBConnect {
    public static Connection connect() {
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/likes";
        String username = "root";
        String password = "";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Error : "+e.getMessage());
        }
        return conn;
    }
}

Next, this will be the index.jsp form

<body>
        <div class="col-lg-2">
        <form method="post" action="likeCount">
            <button type="submit" name="click">TEXTO DONDE QUISIERA QUE INCREMENTE AL HACER CLICK.</button>
            <% try {
                    Connection conn = DBConnect.connect();
                    PreparedStatement pstmt = conn.prepareStatement("SELECT Like1 from Likes");
                    ResultSet rs = pstmt.executeQuery();
                    while (rs.next()) {%>
                    <input type="text" hidden name="counts" value="<%=rs.getInt("Like1")%>" />

        </form>
        <span style="font-weight: bold"><%=(session.getAttribute("count")==null)?rs.getInt("Like1"):(session.getAttribute("count"))%></span>
        <% }
                } catch (SQLException ex) {
                    System.out.println("Error in select: " + ex.getMessage());
                       }%>
    </div>
    </body>

And finally, the servlet. likeCount.java

@Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        /*check whether the button is clicked*/
        if (request.getParameter("click") != null) {
            try {
                /*getting the number of likes*/
                String x = request.getParameter("counts");
                int c = Integer.parseInt(x);
                int count = c;
                count = count + 1; //increment the value

                Connection conn = DBConnect.connect();
                PreparedStatement ps = conn.prepareStatement("UPDATE Likes SET Like1 = ?");
                ps.setInt(1, count);
                int num = ps.executeUpdate();
                if (num > 0) {
                    HttpSession session = request.getSession();
                    session.setAttribute("count", count);
                    response.setIntHeader("Refresh", 3);
                    response.sendRedirect("index.jsp");
                }

            } catch (SQLException e) {
                System.out.println("Error in update :" + e.getMessage());
            }
        }
    }

Hope it would work.

There is something more. When I click, increases, but when I update the page, also increases, how could that be solved? sorry and thanks. — Guillermo López, Jul 14 '18 at 19:00
@GuillermoLópez the doubt is fixed. I edited the code. Try it — , Jul 15 '18 at 04:01
Unfortunately, it shows me this error: HTTP Status 404 - Not Found type: Status report message: Not Found description: The requested resource is not available. I checked if all the names and addresses are correct, and if all the files are in the folders and much more, but the error continues. I know it's an easy mistake to solve, but I can not find it. If you have an idea of what causes that error, please tell me and I will try to solve it. Thank you! — Guillermo López, Jul 15 '18 at 23:01
Mmm okay. When you get the error? After clicking the button or when running the program? — , Jul 15 '18 at 23:03
If you can, edit the question updating with a image of file structure of the project. It would be easier to find a solution — , Jul 15 '18 at 23:05
EDIT! When i click the button, the error appears... In the edition I show the structure, and the codes that are the same! Thank you! — Guillermo López, Jul 16 '18 at 01:25
Found it. You have just copy paste my code. use your database password in database connection — , Jul 16 '18 at 01:40

JSP: How to increase a value of a database in JSP? Error when i click the button

1 Answers1