CODEWITHSUNDEEP
springspring-bootspring-data-jpa
user3796427
user3796427

Reputation: 11

how to read clob data by using jpa native query? and how the jpa interface and service class should be?

public interface StudentRepo extends JpaRepository<Student, Integer> {

    @Query(value = "SELECT Name FROM STUDENT WHERE UNIQID=?", nativeQuery = true)
     public abstract _______ getStudentByUniqueKey(String uniqueKey);
}

Here what is the return type?

Upvotes: 1

Views: 6396

Answers (3)

Harshit Sharma
Harshit Sharma

Reputation: 302

I was facng the same issue and looking around for the solution. In case of Oracle DB (worked in case of 12c version) we can use in built function to convert Clob into String. Here is the code specific to the question

public interface StudentRepo extends JpaRepository<Student, Integer> {

@Query(value = "SELECT dbms_lob.substr(Name) FROM STUDENT WHERE UNIQID=?", nativeQuery = true)
 public abstract String getStudentByUniqueKey(String uniqueKey);  
}

Upvotes: 0

Abder KRIMA
Abder KRIMA

Reputation: 3688

Your StudentRep must look like :

public interface StudentRepo extends JpaRepository<Student, Integer> {

    @Query(value = "SELECT name FROM Student WHERE uniqid=:uniqid", nativeQuery = true)
     public String getStudentNameByUniqueKey(@Param("uniqid")String uniqueKey);
}

And if you want to get Student object :

 @Query(value = "SELECT student FROM Student student WHERE student.uniqid=:uniqid", nativeQuery = true)
  public Student findStudentByUniqueKey(@Param("uniqid")String uniqueKey);

And for more details you can check the official doc

Upvotes: 0

Jonathan JOhx
Jonathan JOhx

Reputation: 5978

The answer is String and also you need to consider in your Student entity put these annotations for CLOB:

   @Column(name="name", columnDefinition="CLOB NOT NULL") 
   @Lob 
   private String name;

UPDATE:

Value of columnDefinition="..." depends on your database. E.g. for PostreSQL it must be columnDefinition="text" instead of columnDefinition="clob"

Upvotes: 1

Related Questions