0

I have searched all over stackoverflow.com but I have not found this discussion.

I know my problem, I was just wondering if JPA can handle this in any way without a lot of work...

What I have is an @Entity that I want to use with multiple SQL-Native-Queries.

@Entity
public class TestEntity {
    @Id
    private String ID;
    private String ID1;
    private String ID2;
    private String ID3;
}

Names in the database are equal to Field names.

I then have two selects:

SELECT ID1, ID2 from DATABASE;
SELECT ID2 from DATABASE;

And two native queries for those selects:

    @Query(value = "SELECT ID2 from DATABASE", nativeQuery = true)
    List<TestEntity> testNativeQuery ();

And the Error I get:

[ERROR] 2021-11-30 10:37:20 [main] [SqlExceptionHelper] The column name ID1 is not valid.

How could I tell JPA that if it does not find the Column name that it should just replace it will

null ?

Would that be so difficult?

Some extra details:

I am using a Stored Procedure - which means I can not change the Query - The Stored Procedures are maintained by someone else.

Stored procedure (which works):

    @Query(value = "STORED_PROC :a, :b", nativeQuery = true)
    List<List<String>> execStoredProc(@Param("a") String a, @Param("b") String b);

Stored procedure that does not work, because I have more fields in the entity that the Procedure return, because I need those fields in other stored procedures:

@Query(value = "STORED_PROC :a, :b", nativeQuery = true)
    List<TestEntity> execStoredProc(@Param("a") String a, @Param("b") String b);

When I remove those field the function works - but I need those fields in other part of my program - and I hoped I could use the same @Entity

Database I am using is MSSQL Database:

driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

Jack
  • 129
  • 9
  • Your entity contains ID, ID1, ID2, ID3 so all these columsn have to exist in the database table – Simon Martinelli Nov 30 '21 at 15:33
  • They do - that is not the problem here - The problem is that the `SELECT` statement does not return all the IDs - If I do a: `SELECT ID, ID1, ID2, ID3 FROM DATABASE` then it works... I would like to have all the other values - that are not in SELECT - that they would be automaticaly "null" since they are declared as Strings – Jack Nov 30 '21 at 15:51
  • Why return TestEntity entity instances from the query? You should try returning a non-entity class if you want it partially populated/incomplete, otherwise it will cause inconsistencies with the persistence unit. See https://stackoverflow.com/a/31966870/496099 – Chris Nov 30 '21 at 16:27
  • That is my current solution - I am returning `List>` I find it rather annoying though - since I have a well organised @Entity and someone has just omitted one Field from a Stored Procedure I want to use - I do not know why does it need to Fail and Throw an Exception instead of just filling that Field with a Null value? Or maybe i could define the @Entity if the Column is not found then it is "null" or "columnNotFound" or sth? Why would that not be possible? What kind of inconsistencies could arise in that case? – Jack Nov 30 '21 at 17:33

1 Answers1

0

Simply add the missing columns as null:

@Query(value = "SELECT NULL AS ID, NULL AS ID1, ID2, NULL AS ID3 from DATABASE", nativeQuery = true)
List<TestEntity> testNativeQuery ();
Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • that would work... however the query in my case is actually a Stored Procedure and I am not allowed to change it - Is there a way where JPA would be so smart to automatically create a Null value if it does not find it? :) – Jack Nov 30 '21 at 16:15
  • No this is not possible. How do you call the stored procedure and which database are you using? – Simon Martinelli Dec 01 '21 at 11:55
  • Stored procedure (which works): `@Query(value = "STORED_PROC :a, :b", nativeQuery = true) List> execStoredProc(@Param("a") String a, @Param("b") String b);` Stored procedure that does not work, because I have more fields in the entity that the Procedure return, because I need those fields in other stored procedures: `@Query(value = "STORED_PROC :a, :b", nativeQuery = true) List execStoredProc(@Param("a") String a, @Param("b") String b);` Database I am using is MSSQL Database: `driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver` – Jack Dec 02 '21 at 09:20
  • I will add an answer I guess :) this is really dificult to format in a comment :) – Jack Dec 02 '21 at 09:21
  • Or better yet I have edited the Question :) I would also be nice if someone Upvoted the question so it could get more traction.. It is a valid question, or is it not? :) I tried to upvote your answer since it would work if I did not have that constraind of not changing the query.. but I can t because my rating is not high enough yet :( – Jack Dec 02 '21 at 09:27