refreshing mysql table in jsp with a button

Question

I am trying to retrieve the data from a mysql database on a jsp page, When the page loads, it loads the latest data (which is what i want), but then when i click on the refresh button i made it refreshes the table with older data and then the button doesnt work after that (wont refresh the table).

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*"%>


<!DOCTYPE html>
<html>
<head>
<title>Main Page</title>
<h1>
    <strong>test page</strong>
</h1>
<hr />
<canvas id="mycanvas" width="400" height="186"></canvas>
<script src="JavaScript/smoothie.js"></script>
<script src="JavaScript/chart.js"></script>
<script src="JavaScript/basic.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>

<script>
    $(document).ready(function(){
        $("#refresh").click(function(){
            $("#heart_rate").load("basic2.jsp")                             
            }); 
        }); 
    </script>
</head>
<body>
    <br>
    <br>
    <button id="refresh">Refresh table</button>
    <br>
    <br>
    <form method="post" name="form">
        <table id="heart_rate" border="1">
            <tr>
                <th>Sensor id</th>
                <th>Time stamp</th>
                <th>Heart rate</th>
                <th>Event time</th>
            </tr>
            <% 
 Connection con = null; 
 String url = "jdbc:mysql://localhost:3306/";
 String db = "Avantidrome";
 String driver = "com.mysql.jdbc.Driver";
 String un ="root";
 String pw="root";
 Statement st; 
 try{ 
     Class.forName(driver).newInstance(); 
     con = DriverManager.getConnection(url + db, un, pw); 
     String sql = "select * from avantidrome.heartratedata order by timestamp DESC limit 10";
     st = con.createStatement(); 
     ResultSet rs = st.executeQuery(sql); 
     %>
            <%
     while(rs.next())
     {
         %>
            <tr>
                <td><%=rs.getString(1)%></td>
                <td><%=rs.getString(2)%></td>
                <td><%=rs.getString(4)%></td>
                <td><%=rs.getString(5)%></td>
            </tr>
            <% 
             }

     %>
            <%
     } catch(Exception e){
         e.printStackTrace();
         }
 %>
        </table>
    </form>


</body>

</html>

once the button is clicked it calls basic.jsp which is just:

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*"%>
<!DOCTYPE html>
<html>
<head>
</head>
<body>
    <br>
    <br>
    <form method="post" name="form">
        <table id="heart_rate" border="1">
            <tr>
                <th>Sensor id</th>
                <th>Time stamp</th>
                <th>Heart rate</th>
                <th>Event time</th>
            </tr>
            <% 
 Connection con = null; 
 String url = "jdbc:mysql://localhost:3306/";
 String db = "Avantidrome";
 String driver = "com.mysql.jdbc.Driver";
 String un ="root";
 String pw="root";
 Statement st; 
 try{ 
     Class.forName(driver).newInstance(); 
     con = DriverManager.getConnection(url + db, un, pw); 
     String sql = "select * from avantidrome.heartratedata order by timestamp DESC limit 10";
     st = con.createStatement(); 
     ResultSet rs = st.executeQuery(sql); 
     %>
            <%
     while(rs.next())
     {
         %>
            <tr>
                <td><%=rs.getString(1)%></td>
                <td><%=rs.getString(2)%></td>
                <td><%=rs.getString(4)%></td>
                <td><%=rs.getString(5)%></td>
            </tr>
            <% 
             }

     %>
            <%
     } catch(Exception e){
         e.printStackTrace();
         }
 %>
        </table>
    </form>


</body>

</html>

all it needs to do is for the button to select the newest data from the database and return it to the table whenever it is clicked.

Quick question, why are you not using servlets? – Luis Alves Feb 08 '15 at 23:19 — Luis Alves, Feb 08 '15 at 23:19

score 0 · Answer 1 · edited May 23 '17 at 12:05

There are two ways to do what you want:

When the user press the refresh button, you simple refresh the page (this should be so fast that he wont notice that the page changed)
Using ajax. So, if choose this option you need to understand that to do this you only need one jsp (the one that loads the page in the begin and has the refresh button), a scrip of javascript that handles the refresh button and uses ajax to get the new data, and a servlet that receives the ajax request and returns an ajax response in json/xml containing the updates.

In my opinion, I prefer the first option, but if you choose to go for the second one, heres another SO post that may help you: How to use Servlets and Ajax?

Also, if you opt for the second one, you should block the refresh button, while you dont receive the ajax response from the servlet. — Luis Alves, Feb 08 '15 at 23:31

refreshing mysql table in jsp with a button

1 Answers1