Is it possible to use raw SQL within a Spring Repository

Question

I need to use raw SQL within a Spring Data Repository  is this possible   Everything I see around  Query is always entity based

User · Answer

It is also possible to use Spring Data JDBC repository, which is a community project built on top of Spring Data Commons to access to databases with raw SQL, without using JPA.

It is less powerful than Spring Data JPA, but if you want lightweight solution for simple projects without using a an ORM like Hibernate, that a solution worth to try.

User · Answer

The  Query annotation allows to execute native queries by setting the nativeQuery flag to true    Quote from Spring Data JPA reference docs   Also  see this section on how to do it with a named native query

User · Answer

YES  You can do this on bellow ways   1  By CrudRepository  Projection   Spring Data Repositories usually return the domain model when using query methods  However  sometimes  you may need to alter the view of that model for various reasons    Suppose your entity is like this        import javax persistence        import java math BigDecimal        Entity      Table name    USER INFO TEST       public class UserInfoTest           private int id          private String name          private String rollNo           public UserInfoTest                        public UserInfoTest int id  String name            this id   id          this name   name                      Id          GeneratedValue strategy   GenerationType IDENTITY           Column name    ID   nullable   false  precision   0          public int getId                 return id                     public void setId int id                this id   id                      Basic          Column name    name   nullable   true          public String getName                 return name                     public void setName String name                this name   name                      Basic          Column name    roll no   nullable   true          public String getRollNo                 return rollNo                     public void setRollNo String rollNo                this rollNo   rollNo                    Now your Projection class is like bellow  It can those fields that you needed   public interface IUserProjection        int getId         String getName         String getRollNo        And Your Data Access Object Dao  is like bellow    import org springframework data jpa repository Query  import org springframework data repository CrudRepository   import java util ArrayList   public interface UserInfoTestDao extends CrudRepository lt UserInfoTest Integer gt         Query value    select id name roll no from USER INFO TEST where rollNo    1   nativeQuery   true      ArrayList lt IUserProjection gt  findUserUsingRollNo String rollNo       Now ArrayList lt IUserProjection gt  findUserUsingRollNo String rollNo  will give you the list of user   2  Using EntityManager   Suppose your query is  select id name from users where roll no   1001     Here query will return a object with id and name column  Your Response class is like bellow   Your Response class is like   public class UserObject          int id          String name          String rollNo           public UserObject Object   columns                this id    columns 0     null    BigDecimal columns 0   intValue   0              this name    String  columns 1                      public int getId                 return id                     public void setId int id                this id   id                     public String getName                 return name                     public void setName String name                this name   name                     public String getRollNo                 return rollNo                     public void setRollNo String rollNo                this rollNo   rollNo                    here UserObject constructor will get a Object Array and set data with object   public UserObject Object   columns                this id    columns 0     null    BigDecimal columns 0   intValue   0              this name    String  columns 1               Your query executing function is like bellow    public UserObject getUserByRoll EntityManager entityManager String rollNo             String queryStr    select id name from users where roll no    1           try               Query query   entityManager createNativeQuery queryStr               query setParameter 1  rollNo                return new UserObject  Object    query getSingleResult               catch  Exception e                e printStackTrace                throw e                    Here you have to import bellow packages   import javax persistence Query  import javax persistence EntityManager    Now your main class  you have to call this function  First get EntityManager and call this getUserByRoll EntityManager entityManager String rollNo  function  Calling procedure is given bellow   Here is the Imports   import javax persistence EntityManager  import javax persistence PersistenceContext    get EntityManager from this way    PersistenceContext private EntityManager entityManager   UserObject userObject   getUserByRoll entityManager  1001      Now you have data in this userObject   Note   query getSingleResult   return a object array  You have to maintain the column position and data type with query column position   select id name from users where roll no   1001    query return a array and it s  0  -- gt  id and  1  - gt  name   More info visit this thread and this Thread  Thanks

User · Answer

It is possible to use raw query within a Spring Repository           Query value    SELECT A IS MUTUAL AID FROM planex AS A               INNER JOIN planex rel AS B ON A PLANEX ID B PLANEX ID                WHERE B GOOD ID    goodId  nativeQuery   true         Boolean mutualAidFlag  Param  goodId  Integer goodId

User · Answer

we can use createNativeQuery  Here Nagitive SQL Query      for Example    Query q   em createNativeQuery  SELECT a firstname  a lastname FROM Author a    List lt Object   gt  authors   q getResultList

[java] Is it possible to use raw SQL within a Spring Repository

Examples related to java

Examples related to hibernate

Examples related to spring-data-jpa