Issue

This Content is from Stack Overflow. Question asked by Pherix

First, my apologies for asking what will probably end up being a simple solution question, but I am in school and this has got me stumped a little. I have reached out to my professor, but he is apparently too busy.

So, the setup here is I am trying to write some JAVA to send an SQL query and then return that data back as a table in a JSP page. The code in JAVA itself works fine outputting to the console, but when I attempt to put into the JSP page for a table, it just ignores it.

The following is the code I have for the JSP page:

<%@page import="entd481.week3_v2_miller.DB"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
     <body>
        
<table align="center" cellpadding="5" cellspacing="5" border="1">

<tr bgcolor="#A52A2A">
<td><b>Customer ID</b></td>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>E-Mail</b></td>
<td><b>Phone</b></td>

<%       
String url = "jdbc:mysql://XXX.XXX.XXX.XX:XXXX/XXXXXXXX";
String username = "XXXXXXX";
String password = "XXXXXXXXX";
String query = "SELECT * FROM XXXXXXX.Customers;";
             
try
        {
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next())
            {
                String customerID = rs.getString("Customer_ID");
                String firstName = rs.getString("FirstName");
                String lastName = rs.getString("LastName");
                String email = rs.getString("email");
                String phoneNumber = rs.getString("PhoneNumber"); 
                
                out.print("<tr>");
                out.print("<td>" + customerID + "</td>");
                out.print("<td>" + firstName + "</td>");
                out.print("<td>" + lastName + "</td>");
                out.print("<td>" + email + "</td>");
                out.print("<td>" + phoneNumber + "</td>");
                out.print("</tr>");                 
            }
        st.close();
        conn.close();
        }   
        catch (Exception e)
        {
            System.out.println(e);            
        }
%>
       
    </body>
</html>

Printing out to the console (from the JAVA code standpoint) works great – get all the contents of the DB. But I am stuck as to why when I run this, the resulting page is blank for results, and none of the HTML that I am telling it to out.print is coming through.

Any help from a beginner programmer would be appreciated, as the resources I have in this class are less than helpful.

Solution

Was missing a dependency in the pom.xml file, and resolved with the following:

 <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.39</version>
 </dependency>

Then, had to additionally add the following code on the JSP page for DB connectivity:

 Class.forName("com.mysql.jdbc.Driver");

All seems to be working now. Thanks to @DawoodibnKareem for pointing me in the right direction.

This Question was asked in StackOverflow by Pherix and Answered by Pherix It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.