Azure Java Web App adding libraries to Classpath

Question

I need to add sqljdbc42.jar library to classpath so I can connect to Azure SQL Database. How (if possible) can I do that in Azure Web Service - Web App (Java with Tomcat server). Is there any other way to add JDBC library?

Server error:

    java.sql.SQLException: No suitable driver found for jdbc:sqlserver://XXXXXXX.database.windows.net:1433;database=XXXXXX;user=XXXXX@XXXXX;password=XXXXXXXX;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:270)
    at common.DBConnection.<init>(DBConnection.java:23)
    at servlets.Login.doPost(Login.java:77)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Dec 30, 2015 3:04:48 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [servlets.Login] in context with path [/deviceweb] threw exception
java.lang.NullPointerException
    at common.DBConnection.addParameter(DBConnection.java:33)
    at servlets.Login.doPost(Login.java:78)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

Web page error:

    type: Exception report

    message:

    description: The server encountered an internal error that prevented it from fulfilling this request.

    exception:

    java.lang.NullPointerException
        common.DBConnection.addParameter(DBConnection.java:33)
        servlets.Login.doPost(Login.java:78)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

note: The full stack trace of the root cause is available in the Apache Tomcat/8.0.30 logs.

Connection string:

String connectionString = "jdbc:sqlserver://testnabaza.database.windows.net:1433;" + "database=testnabaza;"
            + "user=butterbeast@testnabaza;" + "password=1!qQaaaaaa;" + "encrypt=true;"
            + "trustServerCertificate=false;" + "hostNameInCertificate=*.database.windows.net;" + "loginTimeout=30;";

Copied from Portal: jdbc:sqlserver://testnabaza.database.windows.net:1433;database=testnabaza;user=butterbeast@testnabaza;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Answer 1

I get the reply from Azure team which said that I is not possibe to solve this with Web App, but I need to create Cloud Service:

Unfortunately JDBC libraries fall into an area where Tomcat pretty much forces you to put them in CATALINA_HOME\lib but then you need to make sure that the driver you put in will work for all your web apps in that Tomcat.

From what I remember this is tied to Tomcat memory leak prevention which makes Tomcat only scan CATALINA_HOME\lib once at Tomcat startup for database drivers. There are good reasons to do this tied to how the drivers register themselves with the JVM and how that impacts the classloader. Even if you wanted to disable this behaviro, you'd have to edit server.xml which would again drive you to have your own Tomcat install.

So the result of my rambling narrative is that you need to install Tomcat from the gallery or upload your own version and then drop your JDBC drivers in your Tomcat\lib directory. You are trying to do more than you can with the UI enablement of Java for your app.

https://azure.microsoft.com/en-us/documentation/articles/web-sites-java-add-app/

There are outdated tutorials on internet but can help to figured out what to do. subhash singh link helped me with general idea but the tutorial is outdated and a bit confusing.

Answer 2

Per my experience, the simple way for adding the sqljdbc42.jar library (please download it from the offical site https://www.microsoft.com/en-US/download/details.aspx?id=11774) to classpath is that drag or copy the jdbc driver file to the directory WebContent/WEB-INF/lib of Dynamic Web Project in Eclipse, please see the picture below.

When you export the war file from the project in Eclipse, the library file will be included into it. Then, you can deploy the war file into Azure WebApp for Java with Tomcat, and it will work fine.

---

The exception java.sql.SQLException: No suitable driver found in most situations is caused by the incorrect url. Please check your connection string and correctly use it for Java jdbc programming.

As reference， Here is my simple code.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String jdbcUrl = "jdbc:sqlserver://<host>.database.windows.net:1433;database=<database>;user=<username>@<host>;password=<password>;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
String user = "<username>@<host>";
String password = "<password>";
Connection conn = DriverManager.getConnection(jdbcUrl, user, password);
PreparedStatement statement = conn.prepareStatement("select 1 as id, 'peter' as name");
ResultSet rs = statement.executeQuery();
while(rs.next()) {
    System.out.println(rs.getLong("id")+","+rs.getString("name"));
}
rs.close();
statement.close();

Answer 3

Easiest way around this was to simply add the sqljdbc42.jar file (probably located in C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_3.X\enu) to the \lib\ext directory of my local Java installation before creating the .zip file.

for best help use the given link https://dzone.com/articles/java-access-sql-azure-jdbc