Spring Boot Native Query Example

Spring Boot Native Query Example:

In Spring boot, database operations can be performed by implementing CrudRepository<Object, Long> in our @Repository interface. Here Object is our entity class.

@Query Annotation:

We can execute nativeQueries using @Query annotation. Need to make the nativeQuery boolean to true and provide the native query to value parameter for @Query annotation.

 

Syntax:

@Query(nativeQuery=true, value=”select s.store_name, s.store_url from store s”)

 

Sample @Repository Interface:

Store is our object entity, where we need to perform find/findall/deleteby/count etc. @CrudRepository supports the mostly required operations like

• save()
• findOne()
• exists()
• findAll()
• count()
• delete()
• deleteAll()

 

[java]
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository

@Repository
public interface StoreDao extends CrudRepository<Store, Long>{

@Query(nativeQuery=true, value=”select s.store_id, s.store_name, s.store_desc, s.store_slug, s.store_url, s.store_logo, s.store_is_featured, ”
+ ” s.store_is_active, s.store_aff_url, count(c.store_store_id) from store s, coupon c where s.store_id =c.store_store_id group by (c.store_store_id)” )
List<Object[]> getAllStoresWithCount();
}
[/java]

 

Above nativeQuery joins the store table with the hibernate generated one to many mapping table by id and returns the count. @Query annotation returns List<Object[]> and list is iterated and each object is retrieved using the position of the fields in the nativeQuery.

We also need to typecast the fields to the correct java datatypes like long/string etc..

Below is our @Service class where we called our @Repository interface defined above and iterating/setting in the entity object.

[java]
@Service
public class StoreServiceImpl implements StoreService{

@Autowired
public StoreDao storeDao;

@Override
public List getAllStoresWithCount() {
List storesWithCounntsLst = new ArrayList();
List<Object[]> allStoresWithCount = storeDao.getAllStoresWithCount();
/*select s.store_id, s.store_name, s.store_desc, s.store_slug, s.store_url, s.store_logo, s.store_is_featured, s.store_is_active, s.store_aff_url,
count(c.store_store_id) from store s, coupon c where s.store_id =c.store_store_id group by (c.store_store_id)*/
for(Object[] storesObjArray:allStoresWithCount){
Store store = new Store();
store.setStoreId(((BigInteger)storesObjArray[0]).longValue());
store.setStoreName((String)storesObjArray[1]);
store.setStoreDesc((String)storesObjArray[2]);
store.setStoreSlug((String)storesObjArray[3]);
store.setStoreUrl((String)storesObjArray[4]);
store.setStoreLogo((String)storesObjArray[5]);
store.setStoreIsFeatured((Boolean)storesObjArray[6]);
store.setStoreIsActive((Boolean)storesObjArray[7]);
store.setStoreAffUrl((String)storesObjArray[8]);
store.setCouponCount(((BigInteger)storesObjArray[9]).longValue());
storesWithCounntsLst.add(store);
}
return storesWithCounntsLst;
}
}
[/java]

 

Here StoreService is an interface just provides the abstract methods of the implemented methods. Which can be called from the controllers.

 

Feel free to post your questions to help you better!