How to Use Raw SQL Within a Spring Repository

Is it possible to use raw SQL within a Spring Repository

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.

How to run a native SQL query in Spring without an entity and a JPA Repository?

You should be using JDBC instead of an Entity Manager. Under the JPA uses JDBC but it requires defined entites to work. JDBC allows you to manage the connection and run the raw SQL queries.

Here's a link for how to do it in Spring:
https://spring.io/guides/gs/relational-data-access/#_store_and_retrieve_data

Spring Boot: Execute SQL for non-entity Classes

Basically as @dunni said you can use the JdbcTemplate with your own mapper to convert the SQL result to Java POJO:

public CustomResult getCustomResult(){
    final String complexSql = "SELECT SUM(distance) as distanceSum....";
    final CustomResult customResult = (CustomResult) jdbcTemplate.queryForObject(complexSql, new CustomResultRowMapper());

    return customResult;
}

public class CustomResultRowMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        CustomResult customResult = new CustomResult();
        customResult.setDistanceSum(rs.getInt("distanceSum"));
        ...
        return customResult;
    }
}

Also in Spring Boot you don't need to do anything just add your jdbcTemplate to your Dao class:

@Autowired
private JdbcTemplate jdbcTemplate;

How to map sql native query result into DTO in spring jpa repository?

You can define the following named native query with appropriate sql result set mapping:

import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.ConstructorResult;
import javax.persistence.ColumnResult;

@Entity
@NamedNativeQuery(
    name = "find_stock_akhir_dto",
    query =
        "SELECT " + 
        "  stock_akhir.product_id AS productId, " + 
        "  stock_akhir.product_code AS productCode, " + 
        "  SUM(stock_akhir.qty) as stockAkhir " + 
        "FROM book_stock stock_akhir " + 
        "where warehouse_code = :warehouseCode " + 
        "  AND product_code IN :productCodes " + 
        "GROUP BY product_id, product_code, warehouse_id, warehouse_code",
    resultSetMapping = "stock_akhir_dto"
)
@SqlResultSetMapping(
    name = "stock_akhir_dto",
    classes = @ConstructorResult(
        targetClass = StockAkhirDto.class,
        columns = {
            @ColumnResult(name = "productId", type = Long.class),
            @ColumnResult(name = "productCode", type = String.class),
            @ColumnResult(name = "stockAkhir", type = Integer.class)
        }
    )
)
public class SomeEntity
{
}

and then use it:

@Repository
public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {

   @Query(name = "find_stock_akhir_dto", nativeQuery = true)
   List<StockAkhirDto> findStockAkhirPerProductIn(
      @Param("warehouseCode") String warehouseCode,
      @Param("productCodes") Set<String> productCode
   );
}

---

Related Topics