I want to sum up two columns on my database project, I am using Spring Boot - Java, and I don't know what I suppose to do

On database I have those columns
id, user, value_income, outcome_value, total and income_property

And what I'm trying to do is basically retrieving data from value_incomeand outcome_value from database and sum up on backend

My UserRepository:

package com.saturnssolutions.TakeMoney.repositories;

import com.saturnssolutions.TakeMoney.models.UserModel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.List;

public interface UserRepository extends JpaRepository<UserModel, Integer> {
    @Query("SELECT UserModel.id, (UserModel.valueIncome   UserModel.outcomeValue) as AddedValues FROM money")
    List<UserModel> findAllByTotal;
}

ps: I keeps getting error

package com.saturnssolutions.TakeMoney.controllers;

import com.saturnssolutions.TakeMoney.models.UserModel;
import com.saturnssolutions.TakeMoney.services.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.NoSuchElementException;
@RestController
@RequestMapping("/api/v1")
public class UserController {
    @Autowired
    UserService userService;

    @CrossOrigin
    @GetMapping("/users")
    public List<UserModel> listAllUsers() {
        return userService.listAllUsers();
    }

    @CrossOrigin
    @GetMapping("/users/{id}")
    public ResponseEntity<UserModel> users(@PathVariable Integer id) {
        try {
            UserModel user = userService.getUser(id);
            return new ResponseEntity<UserModel>(user, HttpStatus.OK);
        } catch (NoSuchElementException e) {
            return new ResponseEntity<UserModel>(HttpStatus.NOT_FOUND);
        }
    }
    @CrossOrigin
    @PostMapping("/users")
    public void users(@RequestBody UserModel user) {
        userService.saveUser(user);
    }
    @CrossOrigin
    @PutMapping("/users/{id}")
    public ResponseEntity<?> update(@RequestBody UserModel user, @PathVariable Integer id) {
        try {
            UserModel existUser = userService.getUser(id);
            user.setId(id);
            userService.saveUser(user);
            return new ResponseEntity<>(HttpStatus.OK);
        } catch (NoSuchElementException e) {
            return new ResponseEntity<>(HttpStatus.NOT_FOUND);
        }
    }
    @CrossOrigin
    @DeleteMapping("/users/{id}")
    public void delete(@PathVariable Integer id) {

        userService.deleteUser(id);
    }

    @CrossOrigin
    @GetMapping("/users/total")
    public Float getTotal(){
        return userService.getTotal();
    }
}

My service:

package com.saturnssolutions.TakeMoney.services;

import com.saturnssolutions.TakeMoney.models.UserModel;
import com.saturnssolutions.TakeMoney.repositories.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;

import java.math.BigDecimal;
import java.util.List;

@Service
@Transactional
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public List<UserModel> listAllUsers() {
        return userRepository.findAll();
    }

    public void saveUser(UserModel user) {
        userRepository.save(user);
    }

    public UserModel getUser (Integer id) {
        return userRepository.findById(id).get();
    }

    public void deleteUser (Integer id) {
        userRepository.deleteById(id);
    }

    public List<UserModel> getTotal() {
        return userRepository.findAllByTotal();
    }
}

package com.saturnssolutions.TakeMoney.models;

import javax.persistence.*;
import java.math.BigDecimal;

@Entity
@Table(name = "money")
public class UserModel {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String user;
    private Float valueIncome;
    private Float outcomeValue;
    private Float total;
    private Boolean incomeProperty;

    public Boolean getIncomeProperty() {
        return incomeProperty;
    }

    public void setIncomeProperty(Boolean incomeProperty) {
        this.incomeProperty = incomeProperty;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public Float getValueIncome() {
        return valueIncome;
    }

    public void setValueIncome(Float valueIncome) {
        this.valueIncome = valueIncome;
    }

    public Float getOutcomeValue() {
        return outcomeValue;
    }

    public void setOutcomeValue(Float outcomeValue) {
        this.outcomeValue = outcomeValue;
    }

    public Float getTotal() {
        return total;
    }

    public Float setTotal(Float total) {
        this.total = total;
        return total;
    }

    public UserModel() {
    }

    public UserModel(Integer id, String user, Float valueIncome, Float outcomeValue, Float total) {
        this.id = id;
        this.user = user;
        this.valueIncome = valueIncome;
        this.outcomeValue = outcomeValue;
        this.total = total;
    }

    public Integer getId() {
        return id;
    }

    public Integer setId(Integer id) {
        return this.id = id;
    }

}

CodePudding user response：

This version of your repository should work:

public interface UserRepository extends JpaRepository<UserModel, Integer> {
    @Query("SELECT id, valueIncome   outcomeValue AS AddedValues FROM money", nativeQuery=true)
    List<Object[]> findAllByTotal;
}

Note that referring to UserModel in your JPA query won't work, because JPA expects the table name. Also, there is no need for aliases as you are just selecting against a single table/entity.

Also, your query must execute as a native query, because it returns computed columns. You could also define a custom POJO with just the two fields in your select statement. Going with the former List<Object[]> option, your code should look something like:

List<Object[]> vals = userRepository.findAllByTotal();
for (Object[] row : vals) {
    int id = (Integer)row[0];
    double addedValues = (Double)row[1];
}

You may have to play around with the casting in the above for loop to make it work.