Retrieve Data from Database in Java Server Page

Question

i am trying to print out a table in the browser with fetching data from a database. The code is in a jsp-file.

My output looks the following:

It is still missing the values from the table. Now i am wondering why the data from the table is not fetched. I checked the data, and the table is filled with values.

Here is my command-line output with data (blurred):

This is my code in the jsp-file so far:

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%
    String id = request.getParameter("userid");
    String driver = "com.mysql.jdbc.Driver";
    String connectionUrl = "jdbc:mysql://localhost:3306/";
    String database = "store";
    String userid = "root";
    String password = "";
    try {
    Class.forName(driver);
    } catch (ClassNotFoundException e) {
    e.printStackTrace();
    }
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
%>


<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Retrieve data from database in jsp</h1>
        <table border="1">
            <tr>
                <th>Name</th>
                <th>Strasse</th>
                <th>PLZ</th>
                <th>Ort</th>
            </tr>
            
            <%
                try{
                    connection = DriverManager.getConnection(connectionUrl+database, userid, password);
                    statement=connection.createStatement();
                    String sql ="select * from kunde";
                    resultSet = statement.executeQuery(sql);
                    while(resultSet.next()){
                        %>
                        <tr>
                            <td><%=resultSet.getString("name") %></td>
                            <td><%=resultSet.getString("strasse") %></td>
                            <td><%=resultSet.getString("plz") %></td>
                            <td><%=resultSet.getString("ort") %></td>
                        </tr>
                        
                        <%
                    }
                    connection.close();
                }
                catch (Exception e) {
                    e.printStackTrace();
                }
            %>
            </table>
    </body>
</html>

In the log-files i found two of the following error messages:

An error occurred at line: [41] in the jsp file: [/index.jsp]
userid cannot be resolved to a variable
38:             
39:             <%
40:                 try{
41:                     connection = DriverManager.getConnection(connectionUrl+database, userid, password);
42:                     statement=connection.createStatement();
43:                     String sql ="select * from kunde";
44:                     resultSet = statement.executeQuery(sql);


Stacktrace:] with root cause
org.apache.jasper.JasperException: Unable to compile class for JSP: 

An error occurred at line: [41] in the jsp file: [/index.jsp]
userid cannot be resolved to a variable
38:             
39:             <%
40:                 try{
41:                     connection = DriverManager.getConnection(connectionUrl+database, userid, password);
42:                     statement=connection.createStatement();
43:                     String sql ="select * from kunde";
44:                     resultSet = statement.executeQuery(sql);

And

[Warning] Access denied for user 'XY'@'localhost' (using password: NO)

Just a reminder that Scripplets are not recommended anymore. — Spectric, Sep 14 '20 at 16:20
@Swati yes i triple checked and added a picture where you can see the data in the table. — lobster, Sep 14 '20 at 16:25
I checked the log, i don't get permission to the database content: [Warning] Access denied for user 'XY'@'localhost' (using password: NO) Normally it should connect with root not XY. Do you know how to change this? — lobster, Sep 14 '20 at 17:01
Furthermore in a different log it says this: An error occurred at line: [41] in the jsp file: [/index.jsp] userid cannot be resolved to a variable 38: 39: — lobster, Sep 14 '20 at 17:14
Please add the error description to the question. It's hard to read it in a comment. — Spectric, Sep 14 '20 at 17:15
Also, try removing line 8 because it seems to be unnecessary. — Spectric, Sep 14 '20 at 17:17
hi check [this](https://stackoverflow.com/a/59911445/10606400) answer might help . — Swati, Sep 15 '20 at 03:54

score 1 · Accepted Answer · answered Sep 15 '20 at 15:55

1

I solved the problem. I needed to add the mysql-connector-java-5.1.25-bin.jar file to my tomcat's lib.

answered Sep 15 '20 at 15:55

lobster

147
8

Congratulations! – Spectric Sep 15 '20 at 21:25

Retrieve Data from Database in Java Server Page

1 Answers1