Can't execute MySQL stored procedure in JDBC getting nullpointer exception

Question

im not sure where to go from here i have messed with my code and i still get a null pointer exception. leading me to believe that maybe my connection is messed up... anyway unsure any help would be cool.

package callassstatement;
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Scanner;


    public class CallAssStatement {
    private static Connection conn;


    public static void printOptions() {

    System.out.println("enter 1 to get employee");
    System.out.println("enter 2 to get department");
    System.out.println("enter 3 to exit program");

}

public static String getEmployeeMethod(String id) {
    String abc = null;
    try {

        CallableStatement cs = conn.prepareCall(" { call sp_GetEmployee(1)}");


        cs.setString(1, id);
        //register the OUT parameter before calling the stored procedure
         cs.registerOutParameter(2, java.sql.Types.VARCHAR);
        cs.registerOutParameter(3, java.sql.Types.VARCHAR);
        cs.registerOutParameter(4, java.sql.Types.VARCHAR);
        cs.registerOutParameter(5, java.sql.Types.VARCHAR);

        cs.execute();

        //read the OUT parameter now
        String employeeId = cs.getString(1);
        String lastName = cs.getString(2);
        String firstName = cs.getString(3);
        String departmentId = cs.getString(4);
        String startDate = cs.getString(5);



            abc = ("EmplyeeID: " + employeeId + " " + lastName + "," + firstName + "" + " in " 
                    + departmentId + " Department "+ ", StartDate:"+ startDate);
            return abc;

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    return abc;
}

public static void main(String[] args) {

    CandDLoader.createConn();

    printOptions();
    Scanner s = new Scanner(System.in);
    Scanner id = new Scanner(System.in);

    String input = s.nextLine();


    switch (input) {
        case "1":
            System.out.println("calling get employee");
            System.out.println(" Enter employeeID:");
            String ab = id.nextLine();
            getEmployeeMethod(ab);
            break;
        case "2":
            System.out.print("calling get department");
            break;
        case "3":
            System.out.print("exiting");
            System.exit(0);
        default:
            System.out.print("what are you trying to do");
            printOptions();




    }
    }

here is my connection class.

package callassstatement;

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


   public class CandDLoader {


   public static Connection createConn(){
Connection conn = null;
try{

Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/timeclock", "timeclockuser", "password_1234");
    }
    catch(SQLException | ClassNotFoundException ex){}


    return conn;
    }
    }

i couldnt get my main in that post for some reason.... im trying to create an app that will call my sp and functions. here is my run time in main method. run: enter 1 to get employee enter 2 to get department enter 3 to exit program 1 calling get employee Enter employeeID: 123456 Exception in thread "main" java.lang.NullPointerException at callassstatement.CallAssStatement.getEmployeeMethod(CallAssStatement.java:40) at callassstatement.CallAssStatement.main(CallAssStatement.java:94) Java Result: 1 BUILD SUCCESSFUL (total time: 13 seconds) } — Steven Barbra
– Steven Barbra, Commented Oct 23, 2015 at 19:46
Can you check the port no in the database url. I guess its missing — Vivek Singh
– Vivek Singh, Commented Oct 23, 2015 at 19:48
You couldn't get the main in the question because that's too much code. You should cut things off of it to make it display only your problem, creating a minimal reproducible example. The stack trace should be in the question, not in a comment, nor should any lengthy code be in the comments. And finally, never have an empty catch clause in your code. — RealSkeptic
– RealSkeptic, Commented Oct 23, 2015 at 19:54
now i am getting Parameter index out of range (1 > number of parameters, which is 0) error. can someone point me to a link :) — Steven Barbra
– Steven Barbra, Commented Oct 27, 2015 at 6:47

OneCricketeer · Accepted Answer · 2015-10-23 20:11:17Z

1

In createConn() you do return conn, however in main(), you say

CandDLoader.createConn();

Therefore doing nothing with the returned conn and essentially throwing it away.

Either change that line to this

conn = CandDLoader.createConn();

Or change

private static Connection conn;

to this

private static Connection conn = CandDLoader.createConn();

When you declare object fields without assignment, they are assigned as null.

For example

private static Connection conn;

Is the exact same as saying

private static Connection conn = null;

answered Oct 23, 2015 at 20:11

OneCricketeer

193k20 gold badges146 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vivek Singh · Accepted Answer · 2015-10-23 19:51:31Z

0

Check the below line of your codebase

conn = DriverManager.getConnection("jdbc:mysql://localhost/timeclock", "timeclockuser", "password_1234");

The url provided here does not contain the port no, it should be as below:

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/timeclock", "timeclockuser", "password_1234");

answered Oct 23, 2015 at 19:51

Vivek Singh

2,07313 silver badges24 bronze badges

Comments

Paweł Chorążyk · Accepted Answer · 2015-10-23 19:51:42Z

0

private static Connection conn; in your CallAssStatement class is always null, you create a connection in CandDLoader.createConn(); but you do not actually assign it to your conn variable in CallAssStatement.

answered Oct 23, 2015 at 19:51

Paweł Chorążyk

3,6634 gold badges29 silver badges33 bronze badges

2 Comments

Steven Barbra Over a year ago

CandDLoader.createConn(); is in my main method... can u elaborate what you mean

Paweł Chorążyk Over a year ago

Please change private static Connection conn; just below the public class CallAssStatement to private static Connection conn = CandDLoader.createConn(); and see if it works.

Collectives™ on Stack Overflow

Can't execute MySQL stored procedure in JDBC getting nullpointer exception

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related