I am getting null pointer exception in connection establishment

Question

Here I am using jdbcHelper to establish connection with database but some times(not all the times) I am getting exception as null pointer in connection establishment but that is not all the time. here is my code.This code is used to insert data into database..

 Connection conn = JDBCHelper.getConnection();
        PreparedStatement ps1 = null;
        System.out.println("In Side DATA BASE");
        System.out.println("in side database deviceid=" + s);
        System.out.println("in side database rfide=" + s1);
        System.out.println("in side database longitude=" + lati);
        System.out.println("in side database latitude= " + lot);
        System.out.println("in side database datalength=" + data_length);
        Date date = new Date();
        java.sql.Timestamp dt = new java.sql.Timestamp(date.getTime());
        int flag = 1;
        System.out.println("date=" + dt);
        System.out.println("flag=" + flag);
        try {
            String hql = "insert into gpsData1(dateTime,deviceid,latitude,longitude,rfid,flag,datalength) values(?,?,?,?,?,?,?)";
            ps1 = conn.prepareStatement(hql);
            ps1.setString(1, dt.toString());
            ps1.setString(2, s);
            ps1.setFloat(3, lati);
            ps1.setFloat(4, lot);
            ps1.setString(5, s1);
            ps1.setInt(6, flag);
            ps1.setInt(7, data_length);
            ps1.executeUpdate();
            conn.commit();
            System.out.println("DATA BASE Inserting Completed");
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            JDBCHelper.close(ps1);
            JDBCHelper.close(conn);
        }

This is my JDBC code..

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCHelper {

    public static final String url = "jdbc:mysql://localhost:3306/treamisdemo";
    public static final String uid = "root";
    public static final String pwd = "myserver";

    /**
     * @param
     * args
     */
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("connection is sucessful");
        } catch (ClassNotFoundException e) {
            System.err.println("ERROR: failed to load mysql JDBC driver.");
            e.printStackTrace();
        }
    }

    public static void close(ResultSet c) {
        try {
            if (c != null) {
                c.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement c) {
        try {
            if (c != null) {
                c.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection c) {
        try {
            if (c != null) {
                c.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, uid, pwd);
            System.out.println("obtain connection =" + con);
            con.setAutoCommit(false);
            return con;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }


    }
}

This is the stack trace..

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data sourc
e rejected establishment of connection,  message from server: "Too many connecti
ons"
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct
orAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
onstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1128)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2336)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
369)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2153)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.GeneratedConstructorAccessor39.newInstance(Unknown Source
)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
onstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
:305)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at com.treamis.transport.vehicle.JDBCHelper.getConnection(JDBCHelper.jav
a:71)
        at com.treamis.transport.vehicle.MyTimerTask.getRoutePointList(MyTimerTa
sk.java:639)
        at com.treamis.transport.vehicle.MyTimerTask.sendSms(MyTimerTask.java:10
8)
        at com.treamis.transport.vehicle.MyTimerTask.run(MyTimerTask.java:71)
        at java.util.TimerThread.mainLoop(Timer.java:555)
        at java.util.TimerThread.run(Timer.java:505)

Answer 1

Use a real connection pool like HikariCP or BoneCP.

Answer 2

You can use a single connection for this Timer task. Commit on sucessful update but dont close the connection, reuse it.

Something like this:

boolean doInsert(){
  // your insert code
  // String hql = "insert into gpsData1(dateTime,deviceid,latitude,longitude,rfid
   return ps1.executeUpdate(); // this returns true or false
}


boolean isInsert = doInsert();
if(isInsert){
   conn.commit();
}   

Answer 3

The exception states: "Too many connections"

You must close every connection after you finished using it.

Sometimes you don't close the connection you open. You should move getConnection call inside the try block so you will always close if in finally statement.

Another possible reason is that you try to connect too many times simultaneously.

Suggestions:

1. The connection should be opened only when needed. No need to open it immediately at the beginning of the function.
2. The NPE you get is because getConnection() returns null on error. I think it is better to throw an exception in this case rather than return null.
3. If you use Java 7, you can use try-with-resources to close resources after finished using them.
4. Make sure you use reasonable amount of DB connections. If you need more than available connection, consider using one connection for all your DB accesses.