Sum all of DB numbers that has a similar email address (spring security - java)

Question

So im building a web application on spring boot security.

my application currently allows you to register & login locally, or you can login using a server provider that is google & linkedin. after registering, it asks you to submit a number of bitcoins, then it saves it to DB in your profile.

every time you register with different provider, it will save in the DB a new profile, the common thing is the email. but the bitcoins will differ since every profile is separated.

what i want to do is sum up the attribute "bitcoin" for those who has the same email (different providers, one email) to display it in each page with same value.

i'm using this class to access my DB

public interface UserRepository extends JpaRepository<User, Long> {
User findByRegName(String regName);

}

and this is in the main controller to view the bitcoins in the authenticated user page.

    public String wallet(Model model) {
    Authentication authentication = SecurityContextHolder.getContext().getAuthentication();

    String regName = authentication.getName();

    User user = userRepository.findByRegName(regName);
    model.addAttribute("user", user);
    return "wallet";
}

the DB looks like this

please note that im a beginner in coding. a simple explaination would be appreciated

also if you need extra code to be displayed let me know

You could achieve that using native queries: https://stackoverflow.com/questions/15948795/is-it-possible-to-use-raw-sql-within-a-spring-repository. You'd just have to sum all the bitcoins of the accounts which have the same email, something like `select sum(u.bitcoins) from user u where u.email = '?1'` where `?1` would be provided by parameter — Lino, Jan 12 '21 at 08:15
my idea is to do this query, as u noticed i have wrote it in the screenshot. my main request was how to implement it into my code, knowing that im using spring security for authenticating — Alz, Jan 12 '21 at 09:04

Asaf Ben-Natan · Answer 1 · 2021-01-12T14:01:20.593

Assuming you would like to do this entire calculation in a query without requiring any in memory calculation you can do the following (with/without JPA):

with JPA:

@PersistenceContext
    private EntityManager em;

    public Double sumBitCoinForUser(String email) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Double> q = cb.createQuery(Double.class);
        Root<User> r = q.from(User);
        q.select(cb.sum(r.get(User_.bitcoinSum))).where(cb.equal(r.get(User_.email), email));
        return em.createQuery(q).getSingleResult();
    }

where User_ is the metamodel for User , if you dont have a metamodel you can just use the field name so "email" instead of User_.email

if you are not using jpa:

@Query("SELECT sum(u.bitcoinSum) FROM User u WHERE u.email = ?#{[0]}")
    Double findAllActiveUsers( String email);

Kirit · Accepted Answer · 2021-01-13T07:37:43.593

Your repository should look like this -

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByEmail(String email);
}

That will return you all the users with matching email. In your controller, you can then loop through all the users returned and add up the Bitcoin values.

List<User> users = userRepository.findByRegName(regName);
int total = 0;
for(User user:users) {
    total = total + user.getBitcoin()
}
model.addAttribute("totalBitcoin", total);

Sum all of DB numbers that has a similar email address (spring security - java)

2 Answers2