Exclude column from resultset in controller | Spring data jpa

Question

I have a Users Entity:

public class SpringUsers implements Serializable {
    private String password;
    // other fields omitted
    @Basic
    @Column(name = "password")
    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

And its repository:

public interface SpringUsersRepository extends CrudRepository<SpringUsers, Integer> {
    SpringUsers findByUsername(String username);
    List<SpringUsers> findByUserId(Integer userId);
}

And I have a controller method that should get a list of all registered users with the same userId (is internally used) as the currently authenticated user:

public List<SpringUsers> getRegisteredUsers() {
    CustomUserDetails authenticatedUserDetails = getCustomUserDetails();
    List<SpringUsers> registered = springUsersRepository.findByUserId(
        authenticatedUserDetails.getUserMyId());
    return registered.stream().map(v -> {
        v.setPassword(null);
        return v;
    }).collect(Collectors.toList());
}

I don't want to pass the password (even though it's encrypted) to the frontend - so I stream through the users and set the password to null, as you can see above.

However, I'm wondering if there isn't a possibility to just not include the users' password in the query result in the first place?

---

Version Info:

Spring boot 1.4.0 & Hibernate 5.0.9.Final

Answer 1

You can use @Query to selectively include some fields:

// Include all fields you wanna query for using u.x syntax
// AFAIK there is no exclusion syntatic sugar
@Query("select u.id, u.username from SpringUsers u where u.id = ?1")
List<SpringUsers> findByUserId(Integer userId);

Also you can use Projections. First define the projection by introducing a projection interface:

interface NoPasswordUser {
    Long getId();
    String getUsername();
    // Do not include getPassword();
}

Then use it in your repository:

public interface SpringUsersRepository extends CrudRepository<SpringUsers, Integer> {
    NoPasswordUser findByUsername(String username);
    List<NoPasswordUser> findByUserId(Integer userId);
}

Anyway, It's better to not expose your entities through a REST or any remote interface. You can use DTOs for that matter, this post may be useful in this area.

Answer 2

Use @JsonIgnore, this will make it available in the back but will not return when transforming to json

@Size(min = 8)
@JsonIgnore
private String password;

Answer 3

I don't think there is a way to just ignore some fields when getting the entity from db, because those entities wouldn't be in a consistent state - their data would differ from the data in the database. Other options exist, though (custom query, projections).

One option is to use constructor expressions and fill some POJO with only needed data in the query directly

select new my.company.SafeUser(u.username, u.email) from User u

Anyway, I think you should send DTOs to the frontend, and in it expose just the attributes you need on the frontend. That approach has much advantages, weather you initialize them like in the example above, or in the loop over results.

Answer 4

The best way to exclude password from write to json and keep to read is Jackson annotation:

@JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
private String password;

Similary, for registered date:

@JsonProperty(access = JsonProperty.Access.READ_ONLY)
private Date registered = new Date();

See Jackson @JsonProperty examples

Answer 5

Using @NamedNativeQuery on your entity, you can write a custom query to select everything but the password, and then map the result to a DTO @SqlResultSetMapping. This article provides a more detailed explanation.

Answer 6

use

@Basic(fetch = FetchType.LAZY)

for example:

  //todo required annotation
    @Basic(fetch = FetchType.LAZY)
    private String password;

warning : don't forget to exclude from toString method