[FIXED] How to make a field as optional in a JPA Entity?

Issue

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:

• @Basic
• @NotFound(action=NotFoundAction.IGNORE)
• @Column(nullable="true")
• @JsonProperty

But none of this annotations work for me.

Solution

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.

2. 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.

Answered By - SternK