[FIXED] Spring JDBC returning null values for columns with values

Issue

I am having a very weird experience with spring-boot-starter-data-jdbc:2.6.2. Two similar queries returning different results. When I use @Query in the repository as shown below, I get back a few columns but the rest returned as null.

@Repository
public interface StaffRepository extends CrudRepository<Staff, Long> {
    @Query(value = "select * FROM staff WHERE email = :email and deleted = false")
    Staff findStaffByEmail(String email);
}

Returns:

Staff(id=2, firstName=null, middleName=null, lastName=null, [email protected], password=$2a$10$sa.48mjYIYa/0Z9ck8J.ReYAxu8qzA062zUCUWZfYWs/h7BwTh28G, ntkenNo=null, phoneNumber=null, branch=Nairobi branch, roles=[], loginDisabled=false, emailVerified=false, deleted=false, failedLoginAttempts=0, createdAt=null, updatedAt=null)

But when I implement it using jdbcTemplate.queryForMap as shown below, I get correct result all columns having their values

Map<String, Object> map = jdbcTemplate.queryForMap("select * FROM staff WHERE email = ? and deleted = false", email);
        System.out.println(map);

Returning:

{id=2, firstName=John, middleName=Doe, lastName=Doesto, [email protected], password=$2a$10$sa.48mjYIYa/0Z9ck8J.ReYAxu8qzA062zUCUWZfYWs/h7BwTh28G, ntkenNo=1234, phoneNumber=+14255550100, branch=Nairobi branch, roles=[], deleted=false, createdAt=2021-12-27 19:32:36.0, updatedAt=null, loginDisabled=false, emailVerified=false, failedLoginAttempts=0}

What could be wrong with my code above.

I'm using Mysql database.

Below is the Staff entity whose mapping is not working

package com.myapp.models;

import lombok.*;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

@Data
@Table("staff")
@Builder(toBuilder = true, builderClassName = "Builder")
@NoArgsConstructor
@AllArgsConstructor
public class Staff {
    @Id
    Integer id;
    Long bankId;
    String firstName, middleName, lastName, email, password, ntkenNo, phoneNumber, branch, roles;
    boolean loginDisabled, emailVerified, deleted;
    int failedLoginAttempts;
    String createdAt, updatedAt;
}

Yet here is an entity with same definitions and query but working correctly:

package com.myapp.models;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

@Data
@Table("sys_admins")
@Builder(toBuilder = true, builderClassName = "Builder")
@NoArgsConstructor
@AllArgsConstructor
public class SysAdmins {
    @Id
    Integer id;
    String name, email, password, role;
}

It's repository query:

package com.rmauth.repositories;

import com.rmauth.models.SysAdmins;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface SysAdminsRepository extends CrudRepository<SysAdmins, Long> {

    @Query(value = "select * FROM sys_admins WHERE email = :email and deleted = false")
    SysAdmins findByEmail(String email);

}

I see no difference why the first one is not working.

Solution

As mentioned by M. Deinum. Mapping failure is the issue, however, because I want to keep taking advantage of Lombok to make things simple with the POJOS. I have decided to use jdbcTemplate directly with BeanPropertyRowMapper instead of using @Query which requires me to implement Mapper class hence increasing boilerplate code. I just need simple and dynamic approach.

Staff staff =  jdbcTemplate.queryForObject("select * FROM staff WHERE email = ? and deleted = false", new BeanPropertyRowMapper<>(Staff.class), email);

Answered By - Geek Guy