CODEWITHSUNDEEP
javasqljsp
falkon114
falkon114

Reputation: 11

Prevent SQL statement from executing on JSP page load

How can I have a SQL statement that updates data when a button is clicked, not execute when the page loads?

I have a very simple table that has a player name, a player score, and player id attributes and only one row. When I am on this page, I want the button to execute the update query to increase player score, but it's happening every time the page loads.

Is there a boolean or something I can set to prevent it from adding 1 to the player score on every load?

Please don't ask why I'm using a JSP in this context. It's bad to do, I know, but these are my requirements.

Here is my code:

<%@ page contentType="text/html" %>
<%@ page import="java.text.DecimalFormat" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.lang*" %>
<!DOCTYPE html>
<html>
  <head>
    <title>Player scores</title>
    <script>
      function reloadPage() {
        location.reload();
      }
    </script>
  </head>    
  <body>
  <%!
    String name = "";
    Integer score = 0;
    Integer pID = 0;
  %>
  <form name="form1" method="POST" onsubmit="return false">
    <p align="center">
      <input type="BUTTON" value="+" onclick="reloadPage();"/>
    </p>

    <%
      try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "admin");
        Statement stmnt = conn.createStatement();
        stmnt.executeUpdate("update players set playerScore=playerScore+1 where playerID=1");
      } catch (Exception e) {
      }
    %>
  </form>

  <%
  try {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "admin");
    Statement stmnt = conn.createStatement();
    ResultSet rs = stmnt.executeQuery("select playerID, playerName, playerScore from players where playerID=1");
    if (rs.next()) {
       name = rs.getString("playerName");
       score = rs.getInt("playerScore");
       pID = rs.getInt("playerID");
    } else {
      name = "-";
      score = 0;
      pID = 404;
    }
  } catch(Exception e) {
  }
  %>
  <table align="center">
    <tr>
      <th>Player name:</th>
      <td id="pID"><%=name%></td>
    </tr>
    <tr>
      <th>Player score:</th>
      <td id="pN"><%=score%></td>
    </tr>
    <tr>
      <th>Player id:</th>
      <td id="pi"><%=pID%></td>
    </tr>
    <p class="center">
      <a href="Page2.jsp">Go back</a>
    </p>
  </table>
</body>
</html>

Upvotes: 0

Views: 1352

Answers (1)

Bogdan
Bogdan

Reputation: 24590

The update code always gets executed because you have your code written that way.

This code:

<form name="form1" method="POST" onsubmit="return false">
    <p align="center">
      <input type="BUTTON" value="+" onclick="reloadPage();"/>
    </p>

that calls:

<script>
  function reloadPage() {
    location.reload();
  }
</script>

is just a refresh with no extra parameters. You always perform a GET.

You need to tell the JSP to do something different when you click the + button. You might try something like this (please have a look at this tutorial first):

<form name="form1" method="POST" action="yourJSPWhateverIsCalled.jsp">
  <p align="center">
    <input type="submit" value="+" name="increaseScore" />
  </p>

Then guard your update code with an if:

<%
  if (request.getParameter("increaseScore") != null) {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "admin");
      Statement stmnt = conn.createStatement();
      stmnt.executeUpdate("update players set playerScore=playerScore+1 where playerID=1");
    } catch (Exception e) {
    }
  }
%>

Class.forName is needed only once and you could reuse the connection to create the statements but that's another story. Also, a JSP isn't the place to postprocess a form submit (use a plain vanilla servlet).

One other problem with your code is that the JSP is thread UNsafe because of this definition:

<%!
    String name = "";
    Integer score = 0;
    Integer pID = 0;
%>

If you use <%! the code is placed at servlet class level when your JSP gets translated to a servlet and not inside the _jspService method where it belongs. You are basically adding state to your JSP which is thread unsafe because the servlet container can reuse the same servlet instance to handle multiple requests.

Finally, I would strongly suggest you to read a JSP tutorial before continuing. An official tutorial is here.

Upvotes: 1

Related Questions