Title: Null Pointer Exception Traced to Line 90 of CommentPostgres.java, SQL works correctly in DBeaver, Test does not
Tags: amazon-rds;java-11;dbeaver;postgresql-12
Question: All code for entire project is available here
The database is PostgreSQL 12.7
The backend is Java 11.0.12
I am building my TDD tests with JUnit 5.8.1
Here is ```CommentDaoTest.java```
None of it is working, but I am specifically working on ```getAllNotNull```
Line one of the method gets an exception response that leads to a NullPointerException on line 90 of ```CommentPostgres.java```
```package com.revature.data;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotEquals;

import java.util.Set;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import com.revature.beans.Comment;
import com.revature.data.postgres.CommentPostgres;

public class CommentDaoTest {
    private CommentDAO cd = new CommentPostgres();
    
    @BeforeEach
    public void setup()
    {
        cd = new CommentPostgres();
    }
    
    @Test
    public void getByIdNotNull()
    {
        Comment actual = cd.getById(1);
        assertNotEquals(null, actual);
    }
    
    @Test
    public void getByIdValidComment()
    {
        String expected = &quot;Polarised methodical access&quot;;
        Comment one = cd.getById(1);
        String actual = one.getCommentText();
        assertEquals(expected, actual);
    }
    
    @Test
    public void getAllNotNull()
    {
        Set<Comment&gt; actual = cd.getAll();
        assertNotEquals(null, actual);
    }
    
}
```
Here is the relevant part of ```CommentPostgres.java```...
```    @Override
    public Set<Comment&gt; getAll() {
        Set<Comment&gt; comments = new HashSet<&gt;();
        try (Connection conn = connUtil.getConnection()) {
            String sql = &quot;select * from comment&quot;;
            Statement stmt = conn.createStatement();
            
            ResultSet resultSet = stmt.executeQuery(sql);
            
            while (resultSet.next()) {
                Comment comment = new Comment();
                comment.setCommentId(resultSet.getInt(&quot;comment_id&quot;));
                comment.setCommentText(resultSet.getString(&quot;comment_text&quot;));
**Line 90**     comment.getApprover().setEmpId(resultSet.getInt(&quot;approver_id&quot;));  
                comment.getRequest().setReqId(resultSet.getInt(&quot;req_id&quot;));
                comment.setSentAt(resultSet.getTimestamp(&quot;sent_at&quot;).toLocalDateTime());
                System.out.println(comment);
                comments.add(comment);
            }
        
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return comments;
    }
```
Here is the relevant SQL
```create table if not exists employee (
    emp_id serial unique primary key,
    first_name varchar(40),
    last_name varchar(40),
    username varchar(30),
    passwd varchar(25),
    role_id integer not null references user_role,
    funds real,
    supervisor_id integer,
    dept_id integer
);

create table if not exists reimbursement (
    req_id serial unique primary key,
    emp_id integer references employee,
    event_date date, 
    event_time time, 
    location varchar(50), 
    description varchar(75), 
    cost real,
    grading_format_id integer references grading_format,
    event_type_id integer references event_type,
    status_id integer references status, 
    submitted_at time
);

create table if not exists comment (
    comment_id serial unique primary key,
    req_id integer references reimbursement,
    approver_id integer references employee, 
    comment_text varchar(100), 
    sent_at time 
);

INSERT INTO comment
(req_id, approver_id, comment_text, sent_at)
VALUES
(1, 43, 'Polarised methodical access', '8:56:03'),
(2, 34, 'Decentralized 3rd generation encryption', '16:26:13'),
(3, 1, 'Open-architected asymmetric firmware', '3:52:08'),
(4, 34, 'Upgradable content-based synergy', '8:01:03'),
(6, 49, 'Progressive foreground frame', '7:35:55'),
(7, 43, 'Persevering didactic definition', '16:55:07'),
(8, 43, 'Proactive responsive success', '2:21:47'),
(9, 17, 'Devolved content-based task-force', '22:04:18'),
(10, 43, 'Self-enabling client-server orchestration', '9:03:46'),
(13, 49, 'Pre-emptive stable encoding', '12:47:36'), 
(14, 11, 'Streamlined asymmetric initiative', '17:45:58'),
(15, 43, 'Open-architected web-enabled leverage', '2:19:17'),
(17, 43, 'Innovative transitional alliance', '12:43:29'),
(19, 1, 'Organized didactic protocol', '3:54:43'),
(20, 17, 'Switchable 5th generation solution', '20:54:12');
```
The scripts holding the rest of it are on github in the link at the top.
When I do a
```SELECT * FROM comment;
```
in DBeaver I get all of the comments with ```approver_id's```
however when I run ```getAllNotNull``` I get a NullPointer pointing at the ```approver_id```.  I put a Sys.out trying to catch to comment a few lines below 90, but it doesn't hit so the NullPointer is happening on the first time through.
Here is the stacktrace.
```java.lang.NullPointerException
    at com.revature.data.postgres.CommentPostgres.getAll(CommentPostgres.java:90)
    at com.revature.data.CommentDaoTest.getAllNotNull(CommentDaoTest.java:42)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
    at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at java.util.ArrayList.forEach(ArrayList.java:1259)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at java.util.ArrayList.forEach(ArrayList.java:1259)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:95)
    at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:91)
    at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:60)
    at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:98)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
```
Please let me know if you want more files or code snippets added.  Thank you for your help.

Comment: Have you checked the value returned by `comment.getApprover()`? My suspicion is that this call returns `null`. --- It would also be beneficial if you [edit] the post and provide a command to reproduce the test error.

Comment: I put the `System.out.println(comment)` into the `getAll` method in `CommentPostgres.java`. It didn't reach that line.  I'm not sure how else to check it.

Comment: Paste the exception stackstrace as well.

Here is the accepted answer: This line creates a new, empty ```Comment``` object:
```Comment comment = new Comment();
```
After that line, you never call ```comment.setApprover()```, so the approver property inside the ```comment``` object is ```null```. This doesn't have anything to do with missing data in the database, it is a problem with the way you are initializing your Java objects.
Look at what you are doing here:
```comment.getApprover().setEmpId(resultSet.getInt(&quot;approver_id&quot;)); 
```
You're saying &quot;take the new comment object I just created, get the approver object from it, and then set the ID of that approver&quot;. Instead your code needs to look something like this:
```Approver approver = getApproverByEmpId(resultSet.getInt(&quot;approver_id&quot;));
comment.setApprover(approver);
```
Where ```getApproverByEmpId(Integer empId)``` is a new method you need to create that queries the ```employee``` table, and returns an ```Employee``` object.



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:01/01/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<amazon-rds><java-11><dbeaver><postgresql-12>', 'Title': 'Null Pointer Exception Traced to Line 90 of CommentPostgres.java, SQL works correctly in DBeaver, Test does not', 'ViewCount': 214, 'id': 70552436, 'user_AboutMe': '<p>I am a software engineer at Infosys.</p>\n<p>I have worked with Java, Ruby, Ruby on Rails, Spring, Java Script, React, Redux, and Angular.</p>\n<p>I am married and have adult children.</p>\n', 'user_AccountId': 18677920.0, 'user_DisplayName': 'Kim', 'user_Reputation': 23.0, 'user_Views': 33.0, 'user_id': 13615178.0}}