Unable to connect to pgsql database with Java REST

Question

I am developing a REST-API and currently trying to fix an issue. The issue is that the REST-API is Unable to connect to pgsql database, when I go to my web page in chrome. To show this issue, I'll post a stripped down version of my stack with some explanation, describing the origin and functionality of the involved Java classes and XML. Afterwards, I'll show some code of the involved XML files and java classes. I'll look forward to your answers and feedback on my first post.

UPDATE: at the end of the post I'll disclose my major progress towards finding the answer.

Up till now I'm using the following tools:

• Eclipse EE (Oxygen II at the moment)
• Apache Tomcat 8.5.14
• Maven
• Chrome
• PG-admin 4

the stack begins with this line, which probably means Jersey runs into some difficulties.

WARNING: The following warnings have been detected: WARNING: Unknown HK2 failure detected:

My stack continues with java.lang.ExceptionInInitializerError shown below, originating from my database connection broker.

MultiException stack 1 of 2
java.lang.ExceptionInInitializerError
at nl.hu.v1wac.PostgresDAO.webservices.WorldResource.<init (WorldResource.java:25) 
Caused by: java.lang.RuntimeException: javax.naming.NameNotFoundException: Name [jdbc/PostgresDS] is not bound in this Context. Unable to find [jdbc].
at nl.hu.v1wac.PostgresDAO.jdbc.BaseDAO.<init>(BaseDAO.java:38)
at nl.hu.v1wac.PostgresDAO.jdbc.CountryDAO.<init>(CountryDAO.java:13)
at nl.hu.v1wac.PostgresDAO.model.WorldService.<init>(WorldService.java:9)
at nl.hu.v1wac.PostgresDAO.model.ServiceProvider.<clinit>(ServiceProvider.java:4)
... 61 more

the second exception in the multistack is the IllegalStateException, originating from my WorldResource class, containing the Unified Resource Identifiers and JSON interpreter for parsing json messages to my front-end.

MultiException stack 2 of 2
java.lang.IllegalStateException: Unable to perform operation: create on nl.hu.v1wac.PostgresDAO.webservices.WorldResource
Caused by: java.lang.RuntimeException: javax.naming.NameNotFoundException: 
at org.jvnet.hk2.internal.ClazzCreator.create(ClazzCreator.java:393)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

Below I will show my web.xml.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
  <display-name>MavenJersey</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
    <servlet-name>Jersey REST Service</servlet-name>
    <servlet-class>org.glassfish.jersey.servlet.ServletContainer</servlet-class>
    <init-param>
      <param-name>jersey.config.server.provider.packages</param-name>
      <param-value>nl.hu.v1wac.PostgresDAO.webservices</param-value>
    </init-param>
    <init-param>
      <param-name>jersey.config.server.provider.classnames</param-name>
      <param-value>org.glassfish.jersey.server.filter.RolesAllowedDynamicFeature</param-value>
    </init-param>
    <load-on-startup>2</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>Jersey REST Service</servlet-name>
    <url-pattern>/restservices/*</url-pattern>
  </servlet-mapping>
</web-app>

Below Ill show my BaseDAO. This class is a connection broker for my Data Access Objects.

import java.sql.Connection;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.net.URI;
import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;

public class BaseDAO {
    private static final String URL = "jdbc:postgresql://";
    private static final String JDBC_DRIVER = "org.postgresql.Driver";
    private static final String CONTEXT = "java:comp/env/jdbc/PostgresDS";
    private DataSource connectionPool;
    public BaseDAO() {
    try {
        final String DATABASE_URL_PROP = System.getenv("DATABASE_URL");
        if (DATABASE_URL_PROP != null) {
            URI dbUri = new URI(DATABASE_URL_PROP);
            String dbUrl = URL + dbUri.getHost() + dbUri.getPath();
            BasicDataSource pool = new BasicDataSource();

            if (dbUri.getUserInfo() != null) {
                pool.setUsername(dbUri.getUserInfo().split(":")[0]);
                pool.setPassword(dbUri.getUserInfo().split(":")[1]);
                }
                pool.setDriverClassName(JDBC_DRIVER);
                pool.setUrl(dbUrl);
                pool.setInitialSize(1);

                connectionPool = pool;

        } else{
            InitialContext ic = new InitialContext();
            connectionPool = (DataSource) ic.lookup(CONTEXT);
        }
    } catch (Exception e) {

      throw new RuntimeException(e);
    }
    }
  protected final Connection getConnection() {
      try {
          return connectionPool.getConnection();
      } catch (Exception ex) {
          throw new RuntimeException(ex);
      }
  }
}

Finally, I'll conclude this post with my WorldResource class. please note that most code is excluded.

public class WorldResource{
    private WorldService service = ServiceProvider.getWorldService();    
@GET
@RolesAllowed({"admin"})
@Path("/getAll")
@Produces(MediaType.APPLICATION_JSON)
public String getCountries() {
        JsonArrayBuilder jab;
        jab = Json.createArrayBuilder();
        for (Country c : service.getAllCountries()) {
            jab.add(makeCountryObj(c));
        }
        return jab.build().toString();
}

Edit: After @Alok Sinha posted that the context.xml is missing, I added my already existing context.xml to give some extra insight.

<?xml version="1.0" encoding="UTF-8"?> 
<Context> <Resource name="jdbc/PostgresDS" 

url="jdbc:postgresql://localhost:5432/mydb" driverClassName="org.postgresql.Driver" 
auth="Container" 
type="javax.sql.DataSource" 
username="javaduser" 
password="javadude" 
/> 
<ResourceLink name="jdbc/PostgresDS" global="jdbc/PostgresDS" type="javax.sql.DataSource" 
/>
</Context>

UPDATE: after adding a resource references to my web.xml, recommended by this manual of Apache, I was able to get some new insight in my connection problems.

  <resource-ref>
    <description>PostgreSQL Datasource example</description>
    <res-ref-name>jdbc/PostgresDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

As shown in the stack below, it seems that adding a resource ref helps pointing my Application into the right direction. however, the name and url seem to be empty.

Cannot create JDBC driver of class '' for connect URL 'null'
java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getDriver(Unknown Source)
at nl.hu.v1wac.PostgresDAO.jdbc.BaseDAO.getConnection(BaseDAO.java:47)
at nl.hu.v1wac.PostgresDAO.webservices.WorldResource.getCountries(WorldResource.java:34)

UPDATE2: I've tried another approach. I began with an initial maven project, and build my application up from there. I got rid of the IllegalstateException. I've implemented your context.xml exactly as Alok Sinha described. So, no Resourcelink. I've also undressed the BaseDAO to a point where it only has an Initialcontext and a Datasource. Now I can deploy the application as a war file without the basedao. If I try to deploy it with BaseDAO, I can't run due to the following error in my tomcat log:

org.apache.catalina.deploy.NamingResourcesImpl.cleanUp Failed to retrieve
JNDI naming context for container [StandardEngine[Catalina].StandardHost[localhost].StandardContext[/holidayboardapp]] so no cleanup was performed for that container 
  javax.naming.NameNotFoundException: Name [comp/env] is not bound in this Context. Unable to find [comp].`

Answer 1

It appears that you have not bound your datasource in tomcat's JNDI. You would need to make an entry in context.xml , like the below -

<Resource name="jdbc/PostgresDS" auth="Container" type="javax.sql.DataSource"
           maxActive="100" maxIdle="30" maxWait="10000"
           username="javauser" password="javadude" driverClassName="org.postgresql.Driver"
           url="jdbc:postgresql:localhost..."/>

Answer 2

In contrast to my original problem, the solution depends upon how I deploy my application. starting points. Below I’ll describe two starting points that I learned along the way.

One starting point is deploying the application with Eclipse EE, then Eclipse will deploy it on wtpwebapps on my Tomcat root folder. Eclipse deploys your applications in the wtpwebapps folder. Each application will then be referenced with as a subordinate of <Host> in server.xml. This answer has also been mentioned on this stackoverflow post.

<Host appBase="webapps" autoDeploy="true" name="localhost" unpackWARs="true">

The other starting point is deploying my application directly on tomcat webapps folder or use the tomcat manager, I’ll include the context.xml in my maven build, just like Alok Sinha told me properly. Tomcat will then do the deployment for me.

In my personal experience, you have to decide on which starting point you prefer. If you want to use Eclipse to do it, you can’t undeploy or redeploy applications with the tomcat manager, because the context of the applications will be hardcoded in the server.xml. If you still wanted to do that, you have to manually remove the context from your application from the server.xml.