How to retrieve values from the database without reloading the JSP page

Question

I would like to know how to retrieve values from database without reloading the page. I only know a little of javascript and I am using data source jndi for my database. I am currently following the MVC2 model.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@page import = "java.sql.*" %>
<%@page import = "javax.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script src="https://code.jquery.com/jquery-1.10.2.js"></script>
<script>
    $(document).ready(function(){
        $( "button" ).click(function() {
            $("#users").change(function(){ //A function to execute each time the event is triggered.
                var value = $(this).val(); //allows you to pass an array of element values   
                $.get("index.jsp",{q:value},function(data){
                    $("#javaquery").html(data);
                });
            });
        });
    });
</script>
</head>
<body>
    <div id="users">
        <button value="1">1</button>
        <button value="2">2</button>
    </div>
    <br />
    <div id="javaquery"><b>Name will be displayed here</b></div>
<%
    String name = "";
    String q = request.getParameter("q");
    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.0.14:1433/demolangako", "demolangme", "demolangme");
        Statement smt = con.createStatement(); //Create Statement to interact
        ResultSet r = smt.executeQuery("select * from users where(id='" + q + "');");
        while (r.next()) {
            name = r.getString("name");
        }
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
 %>
Name:<%out.print(name);%>

</body>
</html>

Currently I have this code, but it doesn't work with my buttons. Any help is appreciated.

You use programming languages to write code that passes through a program and reads that code. You then write the code to tell that program to do what you want, like read from a database, display data to a view, or make 9,457,642 Goldfish Crackers, randomly located around the world, suddenly exploded with sticky cheese. Coincidentally, this is how Microsoft IE was built (using exploding Goldfish Crackers). *...* Alternately, you might want to look at something like [**this**](http://www.dotnetodyssey.com/2015/01/02/calling-asp-net-code-behind-using-jquery-ajax-simple-complete-example/) — SpYk3HH, Mar 28 '16 at 21:20
You would need to add jquery to query the database and dynamically update the page using ajax. If you're using a framework that supports RESTful services you can use that, but there are other ways to accomplish the same thing. — NAMS, Mar 28 '16 at 21:38
I was going to say "use ajax" but you already are. just you made the beginner mistake of ajaxing back to the same page. Solution: Make two pages. One for presentation that does an ajax call to a backend page. (And make the part that accesses the db a servlet rather than JSP) — developerwjk, Mar 28 '16 at 23:53

score 2 · Answer 1 · edited May 23 '17 at 12:15

Please avoid writing java code in jsp files. You can find a brief explanation How to avoid Java code in JSP files? from BalusC.

Please read MVC2 pattern. If you're using MVC2 it should have a clear segregation between business and view.

First we should create simple jsp-servlet structure. You can find my answer Creating a simple web page using servlet. Once you've done with above part,

For this you can use ajax. Since you've used jQuery-ajax, I'll explain using that. DO remember import jquery library.

Your index.jsp should be like this, I have one textbox and button when you enter value in textbox and click submit it should reach database through servlet and returns result without reloading the page. I am sending request to HelloServlet as in my example.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Submit and View Page</title>
<script src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
<script>
$(document).ready(function(){
  $("#newsubmit").click(function(e){
    $.ajax({
        type: "POST",
        url: "HelloServlet",
        data: { name: $("#myname").val() },
        success:function(result){
            alert(result);
        }
    });
  });
});
</script>
</head>
<body>
     <form>
        <label>Name: </label><input type='text' id="myname" name='name'/></br>
        <input type="button" id="newsubmit" value="Submit" />
    </form>
</body>
</html>

And in your servlet,

public class HelloServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {

        // Receive the username
        String name = req.getParameter("name");
        String result = "";
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.0.14:1433/demolangako", "demolangme", "demolangme");

            // I prefer using prepared statements to avoid SQL injections
            String selectSQL = "SELECT * FROM USERS WHERE NAME = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
            preparedStatement.setString(1, name);
            ResultSet rs = preparedStatement.executeQuery(selectSQL );
            while (rs.next()) {
                result = rs.getString("name");
            }
            String greetings = "Hello " + result;
            response.setContentType("text/plain");
            response.getWriter().write(greetings);
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Now you'll receive response in ajax. Hope this helps.

score 0 · Answer 2 · answered Mar 29 '16 at 09:52

First thing you must do have your database access code(only java) in a separate file, which will serve the ajax request from index.jsp.

Second point is, you need to modify your jQuery script. As you want to send the button click value to ajax, try this instead.

$("button").click(function() {
  $.get("jdbc.jsp", {
    q: $(this).val()
  }, function(data) {
    $("#javaquery").html(data);
  });
});

How to retrieve values from the database without reloading the JSP page

2 Answers2