My DatabaseConnection getter isnt working/ I cant work this out?

Question

Essentially, I am creating football database software that uses MySQL and Java (via JDBC). I have created my database connection which works fine, but when I try to call it to another class it does not work. The getter is public and my Connection variable 'conn' is also public. I am getting nullpointers on the getter line of code when I use it in other classes, however, when I tested the getter it doesn't give me a nullpointer. Here are the two classes that I am testing - including the string of code giving me nullpointers:

CLASS 1:

package ns00790_footballproject;
import java.sql.*;

public class DatabaseConnection {

    public Connection conn;
    public DatabaseConnection() {


        try{

            String url = "jdbc:mysql://localhost:3306/?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
            String user = "root";
            String password = "";
        // 1. Get a connection do database
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println("Connected ok");
        // 2. Create a statement
        Statement st = conn.createStatement();

        st.execute("CREATE DATABASE IF NOT EXISTS FOOTBALL_LEAGUE;" );
        st.execute("USE FOOTBALL_LEAGUE");

        st.execute("CREATE TABLE TEAMS(" + 
                "     teamID INT (10) NOT NULL AUTO_INCREMENT," + 
                "     teamName VARCHAR (50)," + 
                "     homeWins INT," + 
                "     homeDraws INT," + 
                "     homeLoss  INT," + 
                "     awayWins INT," + 
                "     awayDraws INT," + 
                "     awayLoss INT," + 
                "     homeGoalsFor INT," + 
                "     homeGoalsAgainst INT," + 
                "     awayGoalsFor INT," + 
                "     awayGoalsAgainst INT," + 
                "     gamesPlayed INT," + 
                "     PRIMARY KEY (teamID)" + 
                "     );");

        st.execute(" CREATE TABLE GAME(" + 
                "    gameID INT (10) NOT NULL AUTO_INCREMENT," + 
                "    homeTeam VARCHAR(50)," + 
                "    teamID INT (10) NOT NULL," + 
                "    awayTeam VARCHAR(50)," + 
                "    homeScore INT," + 
                "    awayScore INT," + 
                "    PRIMARY KEY (gameID));");

        st.execute("CREATE TABLE DETAILS(" + 
                "   goalID INT NOT NULL AUTO_INCREMENT," + 
                "   gameID INT (10) NOT NULL," + 
                "   playerID INT (10)NOT NULL," + 
                "   PRIMARY KEY (goalID));");

        st.execute("CREATE TABLE PLAYERS(" + 
                "   playerID INT (10)NOT NULL AUTO_INCREMENT," + 
                "    teamID INT (10) NOT NULL," + 
                "   name VARCHAR(50)," + 
                "   goalsScored INT," + 
                "   penaltyCards INT," + 
                "   PRIMARY KEY (playerID));");

        st.execute("ALTER TABLE game ADD INDEX (`teamID`)");
        st.execute("ALTER TABLE `game` ADD FOREIGN KEY (`teamID`) REFERENCES `teams`(`teamID`) ON DELETE CASCADE ON UPDATE CASCADE;");

        st.execute("ALTER TABLE DETAILS ADD INDEX (`playerID`)");
        st.execute("ALTER TABLE `DETAILS` ADD FOREIGN KEY (`playerID`) REFERENCES `players`(`playerID`) ON DELETE CASCADE ON UPDATE CASCADE");

        st.execute("ALTER TABLE DETAILS ADD INDEX (`gameID`)");
        st.execute("ALTER TABLE `DETAILS` ADD FOREIGN KEY (`gameID`) REFERENCES `game`(`gameID`) ON DELETE CASCADE ON UPDATE CASCADE");

        st.execute("ALTER TABLE PLAYERS ADD INDEX (`teamID`)");
        st.execute("ALTER TABLE `PLAYERS` ADD FOREIGN KEY (`teamID`) REFERENCES `TEAMS` (`teamID`) ON DELETE CASCADE ON UPDATE CASCADE");



        }
        catch(Exception exc) {
            exc.printStackTrace();
        }
    }
    public Connection getConnection(){
        return conn;
    }
}

CLASS 2: (method that isnt working)

public void viewPlayerByTeam()  {

        try {

         conn.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        rs = st.executeQuery("SELECT * FROM PLAYERS");

        rs.last();
        System.out.println("Connected");
        System.out.println(rs.getRow());


        }catch(SQLException e){
            System.out.println("SQL Error");
        }



    }

The output should be the number of rows currently in the table, which is 4. I am only getting null-pointer errors.

You never assign something to `st`. This is exactly why you shouldn't use fields etc for things that should remain local. — Mark Rotteveel, May 08 '19 at 15:05
He does add a Statement: Statement st = conn.createStatement(); — Sunchezz, May 08 '19 at 15:07
Can you show where you make use of the class DatabaseConnection? — Sunchezz, May 08 '19 at 15:11
The first class does compile.For the second one, the part is missing where the first class is initiated and assigned to "conn". — Sunchezz, May 08 '19 at 15:16
Hello, would that mean I have to create a new object of DatabaseConnection in each method? sorry I've been at this all day, I appreciate your comments. The NullPointer is this line - conn.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); — ScanN98, May 08 '19 at 15:32
@Sunchezz That is local to the `DatabaseConnection` constructor, but in the second code fragment a statement is created that is never assigned, but subsequently a - likely `null` - `st` variable or field is used. — Mark Rotteveel, May 08 '19 at 15:56
remove the Connection type of the variable "conn" in the constructor. this should solve your problem. @MarkRotteveel I only see the connection which is assigned only locally in the constructor. Can't find an unassigned statement neither in the first or second snippet. — Sunchezz, May 08 '19 at 16:23

score 1 · Answer 1 · answered May 08 '19 at 15:07

1

You are initializing wrong conn reference. There are two objects in your code when there should be one. Do not declare conn locally.

answered May 08 '19 at 15:07

Deepak Khillare

129
8

Where exactly are two connections initialized? The second snippet is in a different class. – Sunchezz May 08 '19 at 15:17
first and last line in this snippet```public Connection conn; public DatabaseConnection() { try{ String url = "jdbc:mysql://localhost:3306/?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC"; String user = "root"; String password = ""; // 1. Get a connection do database Connection conn = DriverManager.getConnection(url, user, password);``` – Deepak Khillare May 08 '19 at 15:19
but the first line is a class variable which is assigned in the constructor and the last line is only a getter for that? Am i missing something? – Sunchezz May 08 '19 at 15:23
You should replace this code ```Connection conn = DriverManager.getConnection(url, user, password);``` with ```conn = DriverManager.getConnection(url, user, password);``` – Deepak Khillare May 08 '19 at 15:25
You are completly right.@ScanN98 this should fix your problem. PLease accept his answer if its solving your errors. – Sunchezz May 08 '19 at 15:43
Sorry to be a pain, I am still getting errors. I have changed the code to conn = DriverManager.getConnection(url, user, password); and am still getting this error: Exception in thread "main" java.lang.NullPointerException at ns00790_footballproject.PlayerQuery.viewPlayerByTeam(PlayerQuery.java:36) at ns00790_footballproject.Main.main(Main.java:12) (this is the same line as before) – ScanN98 May 08 '19 at 16:35

My DatabaseConnection getter isnt working/ I cant work this out?

1 Answers1