JSP won't display string return value

user_9358035 0
Viewed 40

I have a class called "Database" that is working perfectly well. It queries a database and returns the results as a string. When I call the class from my "Test" class it returns the results string and I can print it with System.out.println().

I'm trying to use this class on a JSP page using the same two lines of code to instantiate the class and get the string output. When I try to output on the JSP page I get nothing. What am I doing wrong? I'm completely stumped.

Class Code:

public class Database {

static String[][] reservations = new String[7][20];

public Database (String theDate) {
    String url = "jdbc:mysql://myurl.com:3306/";
    String driver = "com.mysql.cj.jdbc.Driver";
    String user = "myusername";
    String pass = "mypassword";
    String db = "class";
    
    String options = "?useSSL=false";
    
    try (Connection conn = DriverManager.getConnection(url + db + options, user, pass);
            Statement statement = conn.createStatement()) {
        String query = "select reservation.first, reservation.last, startday, numberofdays, guides.first as guidefirst, guides.last as guidelast, locations.location from reservation left join guides on reservation.guide = idguides left join locations on reservation.location = idlocations where StartDay >= " + theDate;
        ResultSet rs = statement.executeQuery(query);
        int row = 0;
        while (rs.next()) {
            reservations[0][row] =  rs.getString("first");
            reservations[1][row] =  rs.getString("last");
            row++;
            }
        rs.close();
        
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public String getStringRes () {
    
    String returnString = "";
    
    for(int i=0; i<20; i++) {
        if (reservations[0][i] != null) {           
        returnString += i + " " + reservations[0][i];
        returnString += " " + reservations[1][i] + "\n";
            
        }
    }
    return returnString;
} }

JSP Code:

<%@ page import="mypackage.Database" %>
<% 
Database db = new Database("2015-07-01");
String str = db.getStringRes();
%>
<%= str %>
jdbc jsp java
1 Answers

Your code contains 3 mistakes.

The most problematic

} catch (SQLException e) {
       e.printStackTrace();
   }

Don't ever do that. Go into your IDE settings and eliminate this template. It should be throw new RuntimeException("Unhandled", e); instead.

Here's what happened: Your SQL statement is erroneous (in two ways, even). This causes an exception. Your code handles this by ignoring it (it's printed, but, your code finishes normally). Hence, the string result remains blank (because it starts out that way and the code that is supposed to give it its real value never ran, due to the exception that you ignored).

Your SQL is broken.

The actual explanation is that date literals need to be in quotes, in SQL. Yours is not.

But that's not how you pass parameters into SQL.

The bigger issue is that passing any value like this into an SQL means your machine will be hacked in a matter of days. That's called 'SQL injection'. You don't want it. The solution is preparedstatements, where you let the JDBC driver and/or the database take care of escaping or otherwise passing string data without the SQL db engine trying to interpret it as SQL (Because, obviously, letting arbitrary users type stuff in that your DB engine then treats as SQL means you're just waiting for someone to construct some SQL such that your db engine ends up executing DROP TABLE reservation CASCADE; EXECUTE 'FORMAT C: /Y'; --.

Minor style nit

Doing business logic in constructors is a bad idea. The query should be done from getStringRes, most likely. That's also a bit of a crazy method name. Not very informative.

Which gets us to...

try (Connection conn = DriverManager.getConnection(url + db + options, user, pass);
 PreparedStatement statement = conn.prepareStatement("SELECT reservation.first, .... FROM .... WHERE StartDay >= ?")) {

  statement.setString(1, theDate);
  try (ResultSet rs = statement.executeQuery()) {
   ...
  }
} catch (SQLException e) {
  throw new RuntimeException("unhandled", e);
}
user_7686440
Related