Search code examples
javajsptomcatjdbchsqldb

HSQLDB does not work when running from JSP, but works when running the Java code alone

I'm currently working on a small project for my own learning purposes, where there is a database and a JSP page to access that database. I have a Java EE project on my Eclipse and I am using the following:

  • Tomcat 9
  • HSQLDB
  • Java JDK 15.0.1
  • Eclipse

When running the Java code below alone, it works and I get the output I want:

shop.RDB

package shop;

import java.sql.*;
import java.util.Collection;
import java.util.LinkedList;

public class RDB 
{
  private Connection con;
  
  public static void main(String[] args) 
  {
      try 
      {
          RDB test = new RDB();
          Collection<Product> ProductsInDB = test.getAllProducts();
      
          for (Product p : ProductsInDB)
          {
              System.out.println(p.toString());
          }
          test.shutdown();
      }
      catch(SQLException e) {e.printStackTrace();}
  }

  public RDB() 
  {
    try 
    {
      Class.forName("org.hsqldb.jdbc.JDBCDriver");
      con = DriverManager.getConnection("jdbc:hsqldb:file:shopDB;hsqldb.lock_file=false", "sa",  "");
      System.out.println("Created con");
    }
    catch (Exception e)
    { 
        System.out.println("Exception: " + e);
    }
  }

  public Collection<Product> getAllProducts()
  {
      return getProductCollection("Select * from Product");
  }
  
  public Collection<Product> getProductCollection(String query) 
  {
      LinkedList<Product> list = new LinkedList<Product>();
      try {
          Statement s = con.createStatement();

          ResultSet rs = s.executeQuery(query);
          while (rs.next())
          {
              Product product = new Product(
                      rs.getString("PID"),
                      rs.getString("name"),                                        
                      rs.getDouble("price")                );
              list.add(product);
          }
          return list;
      }
      catch (Exception e) 
      {
          System.out.println("Exception in getProducts(): " + e);
          return null;
      }
  }
  
  public void shutdown() throws SQLException
  {

      Statement st = con.createStatement();

      // db writes out to files and performs clean shuts down
      // otherwise there will be an unclean shutdown
      // when program ends
      st.execute("SHUTDOWN");
      con.close();    // if there are no other open connection
  }
}

However, when I try to do the exactly the same thing and present the data on a .jsp file, it does not work:

ProductList.jsp

<%@ page contentType = "text/html;charset=UTF-8" language = "java" %>
<%@ page import = "shop.Product, shop.RDB, java.sql.*, java.util.Collection" %>

<html>

    <head>  <title>Amazonius Shop</title>  </head>
    
    <body>
        
        <h3>Using Database</h3>
        
        <table>
        
            <tr style = "color: red">
            <th> ID </th>  <th> Product </th>  <th> Price </th>
            </tr>
            
            <%
                try {
                    RDB DB = new RDB();
                    Collection<Product> ProductsInDB = DB.getAllProducts();
                
                    for (Product p : ProductsInDB) 
                    {
            %>          
            <tr style = "color: blue">
                <td> <%= p.PID %> </td>
                <td> <%= p.name %> </td>
                <td> <%= p.price %> </td>
            </tr>
            
            <%      } 
                    DB.shutdown();
                }
                catch(SQLException e) {e.printStackTrace();}
            %>
            
        </table>
    
    </body>

</html>
  • shop is the package in Java resources folder
  • Product is a custom data structure.
  • ProductList is the JSP file inside WebContent folder

And gives this error:

Mar 15, 2021 10:22:36 AM org.hsqldb.persist.Logger logInfoEvent
INFO: Database closed
Exception: java.sql.SQLException: file input/output error: shopDB.log
Mar 15, 2021 10:22:36 AM org.hsqldb.persist.Logger logSevereEvent
SEVERE: could not reopen database
org.hsqldb.HsqlException: file input/output error: shopDB.log
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.persist.Log.openLog(Unknown Source)
    at org.hsqldb.persist.Log.open(Unknown Source)
    at org.hsqldb.persist.Logger.open(Unknown Source)
    at org.hsqldb.Database.reopen(Unknown Source)
    at org.hsqldb.Database.open(Unknown Source)
    at org.hsqldb.DatabaseManager.getDatabase(Unknown Source)
    at org.hsqldb.DatabaseManager.newSession(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
    at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
    at shop.RDB.<init>(RDB.java:32)
    at org.apache.jsp.lab6web.ProductList_jsp._jspService(ProductList_jsp.java:183)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:71)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:467)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:378)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:326)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:832)

Exception in getProducts(): java.lang.NullPointerException: Cannot invoke "java.sql.Connection.createStatement()" because "this.con" is null
Mar 15, 2021 10:22:36 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [jsp] in context with path [/Education_Tests_Project] threw exception [An exception occurred processing [lab6web/ProductList.jsp] at line [50]

47:                     RDB DB = new RDB();
48:                     Collection<Product> ProductsInDB = DB.getAllProducts();
49:                 
50:                     for (Product p : ProductsInDB) 
51:                     {
52:             %>          
53:             <tr style = "color: blue">


Stacktrace:] with root cause
java.lang.NullPointerException: Cannot invoke "java.util.Collection.iterator()" because "ProductsInDB" is null
    at org.apache.jsp.lab6web.ProductList_jsp._jspService(ProductList_jsp.java:186)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:71)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:467)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:378)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:326)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:832)

The server works fine for other kind of JSP code. In addition, I have my hsqldb.jar in Tomcat's lib folder (saw from a tutorial) and Tomcat is in the Java build path. I've tried several things like;

  • Putting the .jar file in Java build path
  • Putting .jar in Java bin and lib folder both separately and simultaneously
  • Putting .jar in WEB-INF lib folder (saw from a website)

And every attempt keeps giving the same error.

Additional Information

shop.Product

package shop;

public class Product
{
    public String PID;
    public String name;
    public double price;
    
    public Product(String PID, String name, double price)
    {
        this.PID = PID;
        this.name = name;
        this.price = price;
    }

    public String toString() 
    {
        return name + "\t " + price;
    }

}

Java Build Path

Java build path in project properties

Solution

  • Your connection URL is the problem. On Tomcat or other app servers, directories are typically read-only and you cannot create the database there. See http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_variables_url on how to point to your home directory or a directory that is defined in Tomcat properties as the data directory. You shouldn't use the hsqldb.lock_file=false on the URL as this is not recommended for normal usage. The example below uses the home directory for the database location.

    con = DriverManager.getConnection("jdbc:hsqldb:file:~/shopDB", "sa",  "");