CODEWITHSUNDEEP
javaspringoracle-databasehibernatejpa
Vishal Gajera
Vishal Gajera

Reputation: 4207

how to handle Hierarchical Queries(Oracle) through spring data JPA ? where SQL includes START WITH.... CONNECT BY

I am trying to achieving the following things, Link 1, Link 2 using spring data JPA.

I tried many times by unfortunatly I failed each time, so I thought let's ask a question to my friends, may be you guys can help me to get out from this issue.

Actually, I am getting the following an error at every time,

org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:127) [jdbc.spi.SqlExceptionHelper] [main] SQL Error: 17006, SQLState: null
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:129) [jdbc.spi.SqlExceptionHelper] [main] Invalid column name

My Code in JPA repository is :- 

String QUERY = "select * from ( select"
      + " student_id, student_activity_id, student_type_id, LEVEL,"
      + " SYS_CONNECT_BY_PATH(student_id, '/') \"Path\"" + " FROM student"
      + " where student_type_id = 1" + " START WITH student_id = ?1"
      + " connect by  NOCYCLE PRIOR student_activity_id = student_id order by level desc"
      + ") a where rownum = 1";

@Query(nativeQuery = true, value = QUERY)
Student getNextStudent(Long parentStudentId);

NOTE - Above mentioned SQL query is perfectly executing in Database.

Upvotes: 3

Views: 2003

Answers (1)

M. Rizzo
M. Rizzo

Reputation: 2271

This is tough to answer without seeing your Student entity and the mappings defined nor the layout of the Student table. Generally I have seen this type of issue if you have mapped more columns in your entity then you are trying to return in your native query. The reason is hibernate will still try to map the other columns that are mapped in your entity but it can't find the column name in the resultset returned by your native query. Check your Entity mapping and the columns that are being returned in your query.

Upvotes: 1

Related Questions