Having trouble with SQL Query to JSP Page

Pherix 0
Viewed 42

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);            
                         }
                    %>
          </table>
     </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 would be appreciated.

jsp java html
1 Answers

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.

Pherix0
Related