javasql-serverjspjdbcglassfish

no suitable driver found in jsp but not as java application


So I'm starting a web application and I want to use JSP pages (I've used them before) to access dynamically to the database and retrieve data from different tables.

I have by now a basic DatabaseHelper class, an App class that only tests the DB helper connection and a simple query, and a jsp file. The problem I'm facing is that the Database connection works great if run from the App.java class, but if I run it from the JSP file it will throw a SQLException saying it didn't find a suitable driver. (I'll leave code and error message below)

I've tried different options I've read here in StackOverflow and other pages: I put the driver in the server's classpath, in the WEB-INF/lib, in the project's build path as external JAR (this one works for the Java app class mentioned before)...

And here is the environment:

So any hint on how can I solve this problem and use the Database helper from the jsp file will be happily welcome.

Thanks!

Error message:

[2018-03-28T12:39:48.883+0200] [glassfish 4.0] [SEVERE] [] [pl.mais.db.DBHelper] [tid: _ThreadID=21 _ThreadName=http-listener-1(3)] [timeMillis: 1522233588883] [levelValue: 1000] [[

java.sql.SQLException: No suitable driver found for jdbc:sqlserver://localhost;databaseName=campus_db;user=******;password=*****
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:270)
    at pl.mais.db.DBHelper.open(DBHelper.java:41)
    at org.apache.jsp.index_jsp._jspService(index_jsp.java:58)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
    at java.lang.Thread.run(Thread.java:748)
]]

App.java

package pl.mais.general;

import pl.mais.db.DBHelper;

public class App {
    public static void main (String[] args) {
        DBHelper db = new DBHelper();
        db.open();
        db.testSelectFaculties();
        db.close();
    }
}

DBHelper.java

package pl.mais.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * 
 * @author sergi
 *
 */
public class DBHelper {
    // JDBC driver name and database URL

    private final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";  
    private final String DB_URL = "jdbc:sqlserver://localhost;databaseName=campus_db;";

    //  Database credentials
    private static final String DB_USER = "*****";
    private static final String DB_PASS = "*****";

    private Connection conn = null;
    private Statement stmt = null;

    public DBHelper() {
        try {
            Class.forName(JDBC_DRIVER);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void open() {
        try {
            String connectionUrl = DB_URL + "user=" + DB_USER + ";password=" + DB_PASS;
            //System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(connectionUrl);
            //System.out.println("Creating statement...");
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void close() {
        try {
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String[] testSelectFaculties() {
        try {  
            // Create and execute an SQL statement that returns some data.  
            String SQL = "SELECT * FROM faculties";  
            stmt = conn.createStatement();  
            ResultSet rs = stmt.executeQuery(SQL);  
            ArrayList<String> results = new ArrayList<String>();
            // Iterate through the data in the result set and display it.  
            while (rs.next()) {  
               results.add(rs.getString(1) + " - " + rs.getString(2)); 
               System.out.println(rs.getString(1) + " - " + rs.getString(2));
            }  

            return (String[])results.toArray();
         }  

         catch (Exception e) {  
            e.printStackTrace();  

         }  
        return null;
    }
}

index.jsp

<%@page import="pl.mais.db.DBHelper"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>

<body>
<%
    DBHelper db = new DBHelper();
    db.open();
    String[] faculties = db.testSelectFaculties();
    db.close();
    for (int i = 0; i < faculties.length; i++) {
        %>
        <h2>
        <%=
            faculties[i]
        %>
        </h2>
        <%
    }
%>

</body>
</html>

Solution

  • Making the JDBC Driver JAR Files Accessible:

    To integrate the JDBC driver into a GlassFish Server domain, copy the JAR files into the domain-dir/lib directory, then restart the server. This makes classes accessible to all applications or modules deployed on servers that share the same configuration.

    Source: Oracle documentation.