Spring web NullPointer exception for optional

Question

I've followed an open Course on Spring web. Written some code to list all orders from a database and return them through a rest api. This works perfectly. Now I'm writing some code to give the ID of the order in the request, find 0 or 1 orders and return them. However, when there is no Order find with the given ID, a nullpointerexception is given. I can't find out what is causing this. I'm assuming the .orElse(null) statement. Please advise

Controller:

@RequestMapping("api/V1/order")
@RestController
public class OrderController {

    private final OrderService orderService;

    @Autowired
    public OrderController(OrderService orderService) {
        this.orderService = orderService;
    }
        
    @GetMapping(path = "{id}")
    public Order getOrderById(@PathVariable("id") int id) {
        return orderService.getOrderById(id)
                .orElse(null);
    }   
}

Service:

@Service
public class OrderService {

    private final OrderDao orderDao;

    @Autowired
    public OrderService(@Qualifier("oracle") OrderDao orderDao) {
        this.orderDao = orderDao;
    }

    public Optional<Order> getOrderById(int orderNumber) {
        return orderDao.selectOrderById(orderNumber);
    }    
}

Dao:

@Override
public Optional<Order> selectOrderById(int searchedOrderNumber) {
    final String sql = "SELECT \"order\", sender, receiver, patient, orderdate, duedate, paymentref, status, netprice from \"ORDER\" where \"order\" = ?";
    Order order = jdbcTemplate.queryForObject(sql, new Object[] {searchedOrderNumber}, (resultSet, i) -> {
        int orderNumber = resultSet.getInt( "\"order\"");
        String sender = resultSet.getString("sender");
        String receiver = resultSet.getString("receiver");
        String patient = resultSet.getString("patient");
        String orderDate = resultSet.getString("orderdate");
        String dueDate = resultSet.getString("duedate");
        String paymentRef = resultSet.getString("paymentref");
        String status = resultSet.getString("status");
        int netPrice = resultSet.getInt("netprice");
        return new Order(orderNumber,sender,receiver,patient,orderDate,dueDate,paymentRef,status,netPrice);
    });

    return Optional.ofNullable(order);
}

org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:97) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:784) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE] at com.example.demo.dao.OrderDataAccessService.selectOrderById(OrderDataAccessService.java:60) ~[classes/:na] at com.example.demo.dao.OrderDataAccessService$$FastClassBySpringCGLIB$$65d7a86f.invoke() ~[classe — Sunfile, Aug 30 '20 at 18:37
Really weird, it now doesn't throw the nullpointer but the incorrect result size error — Sunfile, Aug 30 '20 at 18:38

Pankaj · Answer 1 · 2020-09-02T12:19:44.650

Editing based on comment about stack trace

For your error please check - Jdbctemplate query for string: EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

To solve problem associated with orderService.getOrderById(id) returning null you can return ResponseEntity.ResponseEntity gives you more flexibility in terms of status code and header. If you can change your code to return ResponseEntitythen you can do something like

 @GetMapping(path = "{id}")
  public ResponseEntity<?> getOrderById(@PathVariable("id") int id) {
    return orderService
        .getOrderById(id)
        .map(order -> new ResponseEntity<>(order.getId(), HttpStatus.OK))
        .orElse(new ResponseEntity<>(HttpStatus.NOT_FOUND));
  }

You can even write generic Exception handler using @ControllerAdvice and throw OrderNotFoundException as .orElse(throw new OrderNotFoundException);. Check more information here.

score 0 · Answer 2 · answered Aug 30 '20 at 19:20

For the Jdbcexception, use general query instead of the queryForObject, or use try/catch to convert the Jdbc related exception, else Spring itself will handle these internally using ExceptionTranslater, ExceptionHandler etc.
To handle optional case in controllers, just throw an exception there, for example PostController.java#L63 And handle it in the PostExceptionHandler.

Spring web NullPointer exception for optional

2 Answers2