CODEWITHSUNDEEP
javasql-serverhibernatejpa
Paryush Jain
Paryush Jain

Reputation: 65

How to make a field as optional in a JPA Entity?

I have Table User in SQL Server DB with close to 30 columns. I have also created an JPA Entity for corresponding DB Table. The JPA entity looks something like this:

@Entity
@Table(name="USER")
@Setter
@Getter
public class User{

@Id
@Column(name="ID",nullable=false)
private Integer id;

@Column(name="NAME")
private String name;

@Column(name="ADDRESS")
private String address;

@Column(name="FATHER_NAME")
private String fathersName;

}

Now I am trying to create a native query which will only fetch id and name from the User Table. The Repository class looks something like this.

@Repository
public interface UserDao extends JpaRepository<User,Integer>{

@Query(value=" SELECT u.ID,u.NAME from USER u", nativeQuery=true)
List<User> fetchUser();

}

Now when this is being executed, it is throwing an exception as below:

com.microsoft.sqlserver.jdbc.SqlServerException: The column name address is not valid

When I add the address column to my query this exception goes but then it come for another field.

com.microsoft.sqlserver.jdbc.SqlServerException: The column name father_name is not valid

So, now I want to modify the JPA Entity in such a way that this fields became optional. I have tried using @Transient but if some other query is using this address field then it will come as null over there. I have also tried all the below annotations:

But none of this annotations work for me.

Upvotes: 4

Views: 15579

Answers (3)

Christian Beikov
Christian Beikov

Reputation: 16452

I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(User.class)
public interface UserDto {
    @IdMapping
    Integer getId();
    String getName();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

UserDto a = entityViewManager.find(entityManager, UserDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<UserDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

Upvotes: 0

SternK
SternK

Reputation: 13111

You can define additional constructor for the User entity:

@Entity
@Table(name="USER")
public class User {

   public User(){
   }

   public User(Integer id, String name){
      this.id = id;
      this.name = name;
   }

   // ...
}

and then write the following query:

@Query("select new your.package.User(u.id, u.name) from User u")
List<User> fetchUser();

Note that this is not native, but jpql query.

Please also note that according to the hibernate documentation:

The projection class must be fully qualified in the entity query, and it must define a matching constructor. The class here need not be mapped. It can be a DTO class. If it does represent an entity, the resulting instances are returned in the NEW state (not managed!).

EDIT

You can also try to use spring data jpa projections in the following way:

  1. Define an interface:
interface UserIdWithName {

  Integer getId();
  String getName();
}

The important thing here is that the properties defined here exactly match properties in the aggregate entity.

  1. Use this interface in the query method:
@Query("select u from User u")
List<UserIdWithName> fetchUserIdWithName();

The query execution engine creates proxy instances of that interface at runtime for each element returned and forwards calls to the exposed methods to the target object. Please note that you can not select only some fields in the query here.

Upvotes: 6

talex
talex

Reputation: 20542

You can use

@Query(value=" SELECT u.ID,u.NAME, '' as address, '' as father_name from USER u", nativeQuery=true)
List<User> fetchUser();

It will set those fields to empty string.

Upvotes: 1

Related Questions