0

So i am facing the following problem. I have developed an web app that has the following connection to a SQL Server database. (db connection code attached)

public class DBConnection
{
    private DatabaseMetaData dma;

    private static Connection con;

    private static DBConnection  instance = null;

    private static String  security = "integratedSecurity=true;";

    private DBConnection()
    {

        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (Exception e) {
            System.out.println("Can not find the driver");
            System.out.println(e.getMessage());
        }

        try{
            con = DriverManager.getConnection("jdbc:jtds:sqlserver://<Machine>;databaseName=<DBName>; useNTLMv2=true;");
            //set autocommit
            con.setAutoCommit(true);
            dma = con.getMetaData(); // get meta data
            System.out.println("Connection to " + dma.getURL());
            System.out.println("Driver " + dma.getDriverName());
            System.out.println("Database product name " + dma.getDatabaseProductName());
        }
        catch(Exception e){

            System.out.println("Problems with the connection to the database");
            System.out.println(e.getMessage());
            System.out.println(con);
        }
    }
    public static void closeConnection()
    {
        try{
            con.close();
            System.out.println("The connection is closed");
        }
        catch (Exception e){
            System.out.println("Error trying to close the database " + e.getMessage());
        }
    }


    public  Connection getDBcon()
    {
        return con;
    }

    public static DBConnection getInstance()
    {
        if (instance == null)
        {
            instance = new DBConnection();
        }
        return instance;
    }

    public static void startTransaction()
    { try{
        con.setAutoCommit(false);
    }
    catch(Exception e){
        System.out.println("fail start transaction");
        System.out.println(e.getMessage());
    }
    }
    public static void commitTransaction()
    { try{
        con.setAutoCommit(true);
    }
    catch(Exception e){
        System.out.println("fail commit transaction");
        System.out.println(e.getMessage());
    }
    }
    public static void rollbackTransaction()
    {
        try
        {
        con.rollback();
        con.setAutoCommit(true);
        }
    catch(Exception e){
        System.out.println("fail rollback transaction");
        System.out.println(e.getMessage());
    }
    }

I am using a Tomcat 8 on InteliJ IDE for running the app and debugging. Which works fine. (DB connection is established)

The problem is that when i take the war file and deploy it in the same Tomcat Server i get no DB Connection. (No DB connection) I have checked all the .jar files in the tomcat and the project and I have added all the needed files. Can't seem to find what is causing this issue. Maybe there is someone who got stuck with the same issue

I can't get a proper undertanding what is causing this issue and how to fix it

**EDIT: Following I have added the error displayed when trying to load data

type Exception report

message Request processing failed; nested exception is java.lang.NullPointerException

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

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.NullPointerException root cause

java.lang.NullPointerException com.lifeletapp.business.dataLayer.DbLogIn.isValidUser(DbLogIn.java:27) com.lifeletapp.business.HelloController.verifyLogin(HelloController.java:33) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

DbLogin class:

public class DbLogIn implements ILogIn
{
    private Connection conn;

    public DbLogIn()
    {
        conn = DBConnection.getInstance().getDBcon();
    }

    public Staff isValidUser(String userName, String password)
    {
        Staff staff = new Staff();
        try {
            String query = "SELECT * FROM Staff WHERE userName=? AND pass=?";
            PreparedStatement preparedStatement = conn.prepareStatement( query );
            preparedStatement.setString(1, userName);
            preparedStatement.setString(2, password);
            ResultSet resultSet = preparedStatement.executeQuery();
            while( resultSet.next() ) {
                staff.setUserID(resultSet.getInt("userID"));
                staff.setfName(resultSet.getString("fName") );
                staff.setlName(resultSet.getString("lName") );
                staff.setUserName(resultSet.getString("userName") );
                staff.setPass(resultSet.getString("pass") );
                staff.setEmail(resultSet.getString("email") );
            }
            resultSet.close();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return staff;
    }
}

This is more a config or server issue. Not a code issue. As mentioned in the comments I have tested if var conn == null -> resulted true. And the connection dose not get nulled anywhere. Please view code. Then again the above code works when run from the InteliJ debugger.

Cillian
  • 69
  • 1
  • 9
Victor Carmocanu
  • 1
  • 5
  • DbLogIn.java line 27 - can you post it? – Fildor Jan 17 '17 at 10:51
  • Can you please mark which line is #27? – Fildor Jan 17 '17 at 10:55
  • that will be : 'PreparedStatement preparedStatement = conn.prepareStatement( query );' – Victor Carmocanu Jan 17 '17 at 10:57
  • to be noted is that this code works perfect when RUN from IDE debugger – Victor Carmocanu Jan 17 '17 at 10:58
  • Add a check for `conn == null`. – Fildor Jan 17 '17 at 10:59
  • 1
    Possible duplicate of [What is a NullPointerException, and how do I fix it?](http://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) – rkosegi Jan 17 '17 at 10:59
  • If DBConnection CTOR encounters an exception, you handle them without telling the client something bad happened. This can lead to `getDBCon()` return `null`. You should either throw an exception in such a case or always check the result for `null`. But in either case, you have to setup a valid DB Connection first or all code depending on having one cannot execute. – Fildor Jan 17 '17 at 11:05
  • Also, after `public static void closeConnection()` has been called, all calls to getDBCon() will return an invalid ( not usable ) object. You should not allow this to happen. – Fildor Jan 17 '17 at 11:11
  • Maybe there is a misunderstanding. My database connection is valid. (machine name and DB name have been removed on purpose in the post). – Victor Carmocanu Jan 17 '17 at 11:11
  • 1
    How do you know? "com.lifeletapp.business.dataLayer.DbLogIn.isValidUser(DbLogIn.java:27) " - so in this line `PreparedStatement preparedStatement = conn.prepareStatement( query );` something is `null`. There are only 2 candidates: query and conn. Spoiler: It's not query ... – Fildor Jan 17 '17 at 11:11
  • So you have to check if it is really `conn` that is null. If so, then you'll have to ask yourself "how could that happen?" - It could if you either have an exception in DBConnection CTOR (check output) or maybe something else I haven't seen yet. – Fildor Jan 17 '17 at 11:16
  • I am checking this as you can see in the DBConnection class. Line 56, respective System.out.println("Problems with the connection to the database"); This error is prompted in the TomcatServer. EDITING ANSWER – Victor Carmocanu Jan 17 '17 at 11:16
  • OK, if you do not have that output, then it must be somewhere else. I suggest: 1. Adding output to DBLogin CTOR to check if the conn-Field is actually set to a non-NULL object. 2. Check if anywhere in the code the Field is reset to null ... – Fildor Jan 17 '17 at 11:18
  • https://i.stack.imgur.com/u3p5w.png – Victor Carmocanu Jan 17 '17 at 11:20
  • This is more of a server configuration issue. Or connection management issue. I do not believe its based on the code due to the fact that as I have mentioned before when running the app from the IDE using the same tomcat server attached it works perfectly fine. – Victor Carmocanu Jan 17 '17 at 11:21
  • I am very sure that I am not getting a connection to the database. I don't know why. Indeed that the var conn == null. – Victor Carmocanu Jan 17 '17 at 11:27
  • Exactly. So it would have been helpful for you if you haven't been able to even call getDBCon() in the first place - if you know what I mean. An Exception leading to an output "Could not connect to DB" would have lead you directly to the problem. – Fildor Jan 17 '17 at 11:40
  • There could be two reasons , 1) May be JDBC Driver class not on Tomcat's class path 2) Database server not accessible from that physical machine – Anil Agrawal Jan 17 '17 at 12:37
  • Share full server logs for more clarification – Anil Agrawal Jan 17 '17 at 12:38

1 Answers1

0

So finally figured out what was the issue. Why was it working on my IDE config: 1. I was using java JDK 1.7 as JAVA_HOME 2. I was using an older version of Tomcat as a build 3. In order to work with JDTS you need to extract the nlmauth.dll from the .jar archive and copy it to the configured env JDK(jdk1.7-->>bin->>copy here)

Why was it not working on Tomcat server: 1.I was using Tomcat 8.xxx 2.Tomcat 8.xx require JDk 8 3. In the JDK 8 i have not past the nlmauth.dll ( once i have done this everything was working)

In order to approach this issue the first clue will be looking into the tomcat server logs.

On my presumption this issue is prom to occur only when you have an Database connection establish via Integrated Security. My presumption is related to the fact that in the tomcat log the main error that was denying the JDBC driver was based on the integrated security credentials.

Best of luck to you all out there.

Victor Carmocanu
  • 1
  • 5