Executing a SQL command and outputting results in a JSP page

Question

I am trying to connect to a database, execute a select statement, and then output the results of that statement in a JSP page. I have been able to connect to the DB, and (I think) execute the command, but either my ResultSet has nothing in it, or I am not displaying the results properly.

I have looked at some examples of how this is done, and my code is pretty much identical. When I run the code the resulting web page only has these empty lines where the output data should be. It does however, have three rows of empty lines, which is the number of tuples the query should return. My code is:

            String query = "SELECT L.O_ID, L.INV_ID, L.OL_QUANTITY, L.OL_PRICE "
                    + "FROM ORDERS O, ORDER_LINE L, ORDERSOURCE S "
                    + "WHERE S.OS_DESC = 'Web Site' "
                    + "AND O.OS_ID = S.OS_ID "
                    + "AND L.O_ID = O.O_ID";
            System.out.println(query);
            ResultSet results = statement.executeQuery(query);
            %>

            <table border="1" width="50%" cellspacing="1" cellpadding="0" bordercolor="black" border="1">
                <tr>
                    <th><font size="2"/>Order ID</th>
                    <th><font size="2"/>Inventory ID</th>
                    <th><font size="2"/>Quantity</th>
                    <th><font size="2"/>Price</th>
                </tr>
            <%while(results.next()) { %>
            <tr>
                <td><% results.getString("O_ID"); %></td>
                <td><% results.getString("INV_ID"); %></td>
                <td><% results.getString("OL_QUANTITY"); %></td>
                <td><% results.getString("OL_PRICE"); %></td>
            </tr>
            <% } %>

            </table>
    </body>
</html>

Also, when I tried to run the query with a ; at the end, it would not run. But when I removed it it ran. Is there some reason you don't need to terminate queries with a ; when using JDBC?

Roman C · Accepted Answer · 2014-11-10T18:56:16.403

0

You are using only one statement, and not using a script to execute. ; used as a delimiter between SQL statements. So, in you case it is useless. If you are using some client like SQL Developer, you might achieve the same effect of a delimiter if you press a button to run a script in stead of run a current statement. You should know that. And the code you highlighted execute only those statements that highlight a last delimiter.

edited Nov 10 '14 at 18:56

answered Nov 10 '14 at 18:51

Roman C

49,761
33
66
176

"And the code you highlighted execute only those statements that highlight a last delimiter." Huh? Could you elaborate on that? I don't understand. – ThomYorkkke Nov 10 '14 at 19:27

score 0 · Answer 2 · answered Nov 10 '14 at 22:13

You need to use the out.print function to display these results.

So your code to display should be:

<td><% out.print(results.getString("O_ID")); %></td>
<td><% out.print(results.getString("INV_ID")); %></td>
<td><% out.print(results.getString("OL_QUANTITY")); %></td>
<td><% out.print(results.getString("OL_PRICE")); %></td>

score 0 · Answer 3 · edited May 23 '17 at 11:44

I have been able to connect to the DB, and (I think) execute the command, but either my ResultSet has nothing in it, or I am not displaying the results properly

First of all , you are not printing anything in the jsp, add out.print() as Larry suggested.Also check whether you have formed the query correctly, you can check it in the interface like sql developer. It is advised to print the sql query before you execute it to avoid the syntax errors

Also, when I tried to run the query with a ';' at the end, it would not run. But when I removed it it ran. Is there some reason you don't need to terminate queries with a ';' when using JDBC?

You need not terminate the sql queries with the semicolon, jdbc does that for you in case of single query. Have a look at Is the semicolon necessary in SQL?

Lastly, don't write the java codes inside the jsp file ,try to use servlets (even it is not advisable in MVC). Refer How to avoid Java code in JSP files?

Executing a SQL command and outputting results in a JSP page

3 Answers3