Spring JDBC returning null values for columns with values

Question

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=geek@mail.com, 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=geek@mail.com, 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.

To map to an object the mapping has to be correct, it looks like the mapping isn't correct. I also don't get why your `toString` of your `Staff` entity should be the same as a a `toString` of a `Map` instance. — M. Deinum, Dec 28 '21 at 12:49
@M.Deinum please check the edit, the mappings are correct unless there is a mistake I'm not noticing — Geek Guy, Dec 28 '21 at 18:03
You are using lombok to generate stuff. Generally not the best of ideas. AFAIK Spring Data JDBC doesn't work with a builder, and when using a constructor the order of the arguments is important, you don't know the order of the returned SQL so it will probably map random columns to random constructor arguments. I would refrain from using lombok and just write a plain DTO yourself (or just use `@Data` so you get getters/setters which Spring Data JDBC can use to properly set the columns). — M. Deinum, Dec 29 '21 at 09:26

score 1 · Accepted Answer · answered Dec 29 '21 at 15:12

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);

Spring JDBC returning null values for columns with values

1 Answers1