Custom Query H2 - Spring Boot

Question

I'm trying to do Spring Boot Application when trying for custom query on H2 DB and I'getting an error:

localhost:8080/uniqueInvestmentDetails

{
    "timestamp": 1626502823614,
    "status": 500,
    "error": "Internal Server Error",
    **"exception": "org.springframework.dao.InvalidDataAccessResourceUsageException",
    "message": "could not prepare statement; SQL [SELECT new UniqueInvestments(portal as portal, sum(amount) as amount) from INVESTMENTS group by portal]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement",**
    "path": "/uniqueInvestmentDetails"
}

Investment.java:

package com.spring.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table
public class Investments {

    @Id
    @Column
    public String investment_id;
    @Column
    public String portal;
    @Column
    public String investment_date;
    @Column
    public float amount;
    public String getInvestment_id() {
        return investment_id;
    }
    public void setInvestment_id(String investment_id) {
        this.investment_id = investment_id;
    }
    public String getPortal() {
        return portal;
    }
    public void setPortal(String portal) {
        this.portal = portal;
    }
    public String getInvestment_date() {
        return investment_date;
    }
    public void setInvestment_date(String investment_date) {
        this.investment_date = investment_date;
    }
    public float getAmount() {
        return amount;
    }
    public void setAmount(float amount) {
        this.amount = amount;
    }
    
}

UniqueInvestments.java:

package com.spring.model;

public class UniqueInvestments {

    public String portal;
    public float amount;
    
    public String getPortal() {
        return portal;
    }
    public void setPortal(String portal) {
        this.portal = portal;
    }
    public float getAmount() {
        return amount;
    }
    public void setAmount(float amount) {
        this.amount = amount;
    }
    
    public UniqueInvestments(String portal, float amount) {
        this.portal = portal;
        this.amount = amount;
    }
    
}

Repository:

package com.spring.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.spring.model.Investments;
import com.spring.model.UniqueInvestments;

public interface InvestmentsRepo extends JpaRepository<Investments, Integer> {

    @Query(nativeQuery = true, value = "SELECT "
            + "new UniqueInvestments(portal as portal, sum(amount) as amount) "
            + "from INVESTMENTS group by portal")
    List<UniqueInvestments> getUniqueInvestmentsList();

}

Controller:

package com.spring.Controllers;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import com.spring.model.Investments;
import com.spring.model.UniqueInvestments;
import com.spring.services.InvestmentsService;

@RestController
public class InvestmentController {

    @Autowired
    InvestmentsService investmentService;
    
    @PostMapping
    public void insertInvestmentDetails(){
        
    }
    
    @GetMapping("/showInvestments")
    public List<Investments> showInvestments(){
        return investmentService.showAllInvestments();
    }
    
    @GetMapping("/uniqueInvestmentDetails")
    public List<UniqueInvestments> showUniqueInvestments(){
        return investmentService.showUniqueInvestments();
    } 
    
}

Service:

package com.spring.services;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.spring.model.Investments;
import com.spring.model.UniqueInvestments;
import com.spring.repository.InvestmentsRepo;

@Service
public class InvestmentsService {

    @Autowired
    InvestmentsRepo investmentRepo;  
    
    public List<Investments> showAllInvestments() {
        List<Investments> investments = new ArrayList<Investments>();  
        investmentRepo.findAll().forEach(investment -> investments.add(investment));  
        return investments;  
    }

    public List<UniqueInvestments> showUniqueInvestments() {
        List<UniqueInvestments> uniqueInvestments = new ArrayList<UniqueInvestments>();  
        for(UniqueInvestments inv : investmentRepo.getUniqueInvestmentsList()){
            uniqueInvestments.add(inv);
        }
        return uniqueInvestments;
    }

}

application.properties:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

# Enabling H2 Console
spring.h2.console.enabled=true
 
# Custom H2 Console URL
spring.h2.console.path=/h2

spring.jpa.hibernate.ddl-auto=none

spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl

Answer 1

In the InvestmentsRepo class, remove your native query and place that into your Entity class.

@Entity
@Table
@Data
@NamedNativeQuery(
    name = "find_unique_investments",
    query =
            "SELECT "
                    + "i.portal as portal, sum(i.amount) as amount "
                    + "from INVESTMENTS i group by i.portal",
    resultSetMapping = "unique_investments"
)
@SqlResultSetMapping(
    name = "unique_investments",
    classes = @ConstructorResult(
            targetClass = UniqueInvestments.class,
            columns = {
                    @ColumnResult(name = "portal", type = String.class),
                    @ColumnResult(name = "amount", type = Float.class)
            }
    )
)
public class Investments {
........
}

And in your repository class, just call this namedQuery:

@Query(nativeQuery = true, name = "find_unique_investments")
List<UniqueInvestments> getUniqueInvestmentsList();

You current issue will be resolved. And if you run this curl:

curl --location --request GET 
'http://localhost:8080/uniqueInvestmentDetails' \
--header 'Content-Type: application/json' \
--data ''

You will get the below result (These are from dummy data I used in the Application startup for testing pupose):

[
    {
        "portal": "portal1",
        "amount": 600.0
    },
    {
        "portal": "portal2",
        "amount": 250.0
    },
    {
        "portal": "portal3",
        "amount": 350.0
    }
]