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

package com.example.cookbook.repository;


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

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

@Repository
public class NarackiRepository {


    public List<Naracka> findAll() throws SQLException {

        Connection connection = DB.getConnection();
        Statement stm = connection.createStatement();
        String query = "select vreme, telefon from naracki;";

        ResultSet result = stm.executeQuery(query);
        List<Naracka> naracki = new ArrayList<>();

        while (result.next()){
            Naracka naracka = new Naracka();
            naracka.setVreme(result.getObject("vreme", LocalDateTime.class));
            naracka.setTelefon(result.getString("telefon"));
            naracki.add(naracka);
        }

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

    public List<Stavka> findByTelAndVreme(String telefon, LocalDateTime vreme) throws SQLException {

        Connection connection = DB.getConnection();
        String query = "select sk.vreme, sk.telefon, r.rec_ime, nacin from so_koi sk\n" +
                "        left join recepti r on r.rec_id = sk.rec_id\n" +
                "where vreme = ? and telefon = ?;";
        PreparedStatement prepStm = connection.prepareStatement(query);

        prepStm.setObject(1, vreme);
        prepStm.setString(2, telefon);

        ResultSet result = prepStm.executeQuery();
        List<Stavka> stavki = new ArrayList<>();
        while (result.next()){
            Stavka stavka = new Stavka();
            stavka.setRecIme(result.getString("rec_ime"));
            stavka.setTelefon(result.getString("telefon"));
            stavka.setNacin(result.getString("nacin"));
            stavka.setVreme(result.getObject("vreme", LocalDateTime.class));
            stavki.add(stavka);
        }
        DB.closeConnection();
        result.close();
        prepStm.close();
        return stavki;
    }

    public List<DostavaDTO> findDataForDostava(String telefon, LocalDateTime vreme) throws SQLException {
        Connection connection = DB.getConnection();
        String query = "select r.rec_ime, l.adresa, 'Готово' sostojka from so_koi sk\n" +
                "            left join recepti r on\n" +
                "                        r.rec_id = sk.rec_id\n" +
                "                left join opsluzuva o on\n" +
                "                        o.rec_id = r.rec_id\n" +
                "                left join restorani r2 on\n" +
                "                        r2.res_id = o.res_id\n" +
                "                left join se_naogja1 sn on\n" +
                "                        sn.res_id = r2.res_id\n" +
                "                left join lokacii l on\n" +
                "                        l.l_id = sn.l_id\n" +
                "        where vreme = ? and telefon = ? and sk.nacin = 'Готово'\n" +
                "union\n" +
                "select r.rec_ime, l.adresa, s2.s_naziv sostojka  from so_koi sk\n" +
                "        left join recepti r on\n" +
                "                        r.rec_id = sk.rec_id\n" +
                "                left join sodrzi s on\n" +
                "                        r.rec_id = s.rec_id\n" +
                "                left join sostojki s2 on\n" +
                "                        s2.s_id = s.s_id\n" +
                "                left join ja_ima ji on\n" +
                "                        ji.s_id = s2.s_id\n" +
                "                left join prodavnici p on\n" +
                "                        p.pro_id = ji.pro_id\n" +
                "                left join se_naogja2 sn on\n" +
                "                        sn.pro_id = p.pro_id\n" +
                "                left join lokacii l on\n" +
                "                        l.l_id = sn.l_id\n" +
                "        where vreme = ? and telefon = ? and sk.nacin = 'Состојки'";
        PreparedStatement prepStm = connection.prepareStatement(query);

        prepStm.setObject(1, vreme);
        prepStm.setString(2, telefon);
        prepStm.setObject(3, vreme);
        prepStm.setString(4, telefon);

        ResultSet result = prepStm.executeQuery();

        List<DostavaDTO> dostavaDTOS = new ArrayList<>();
        while (result.next()){
            DostavaDTO stavka = new DostavaDTO(
                    result.getString("rec_ime"),
                    result.getString("adresa"),
                    result.getString("sostojka")
            );

            dostavaDTOS.add(stavka);
        }
        DB.closeConnection();
        result.close();
        prepStm.close();
        return dostavaDTOS;
    }
}