source: src/main/java/com/example/cookbook/repository/ReceptRepository.java

package com.example.cookbook.repository;


import com.example.cookbook.dbConfig.DB;
import com.example.cookbook.model.Recept;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

@Repository
public class ReceptRepository {


    public List<Recept> findAll() throws SQLException {
        Connection connection = DB.getConnection();

        Statement stm = connection.createStatement();
        String query = "select r.rec_id, r.rec_ime , r.postapka,  avg(k.ocena) average\n" +
                "from  recepti r\n" +
                "    left join komentari k\n" +
                "        on r.rec_id = k.rec_id\n" +
                "group by r.rec_id, r.rec_ime";
        ResultSet result = stm.executeQuery(query);

        List<Recept> recepti = new ArrayList<>();
        while (result.next()){
            Recept recept = new Recept();

            recept.setRecId(result.getLong("rec_id"));
            recept.setRecIme(result.getString("rec_ime"));
            recept.setPostapka(result.getString("postapka"));
            recept.setSrednaOcena(result.getFloat("average"));

            recepti.add(recept);

        }
        stm.close();
        result.close();
        DB.closeConnection();
        return recepti;
    }

    public Optional<Recept> findById(Long recId) throws SQLException {
        Connection connection = DB.getConnection();

        String query = "select * from recepti r where r.rec_id = ?";
        PreparedStatement prepStm = connection.prepareStatement(query);
        prepStm.setLong(1, recId);

        ResultSet result = prepStm.executeQuery();
        Recept recept = null;
        if(result.next()){
            recept = new Recept();
            recept.setRecId(result.getLong("rec_id"));
            recept.setRecIme(result.getString("rec_ime"));
            recept.setPostapka(result.getString("postapka"));
        }
        result.close();
        prepStm.close();
        DB.closeConnection();
        return Optional.ofNullable(recept);
        
    }
}