Baze podataka 1/Lab 1 semestralni 2020

Izvor: SI Wiki
Pređi na navigaciju Pređi na pretragu

Postavka

Banka putem svojih filijala (prati se naziv i adresa) u raznim mestima (prate se poštanski broj i naziv) opslužuje svoje komitente (prati se naziv i adresa) koji mogu biti bez mesta, a u trenutku prvog pojavljivanja u banci prijavljuju sedište u određenom mestu.

Svaki komitent može da ima više računa u svakoj od filijala (prate se status, broj stavki, dozvoljeni minus, i stanje), a mora imati bar jedan račun. Status računa može biti aktivan, blokiran ili ugašen. Račun postaje blokiran kada pređe u nedozvoljeni minus, a aktivira se kada stanje pređe u dozvoljeni minus.

Komitenti sa svojih računa vrše transakcije putem stavki prometa (prate se redni broj, datum i vreme) koje mogu biti uplate (prati se osnov i iznos) ili isplate (prate se iznos i provizija), pri čemu je to moguće u bilo kojoj filijali.

ER dijagram baze iz postavke zadatka.

Zadatak

Komitent je rešio da uplati minimalno para na svoje račune, ali pri tome da na svim računima ne bude u minusu nakon uplate. Napisati funkciju koja vrši uplatu za jednog korisnika. Funkcija kao argumente prima idFil i idKom i vraća sumu izvršene uplate. Funkcija je potrebno da bude otporna na greške.

Rešenje Java

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;

public class SemestralniLab2020 {
    private Connection conn = null;
    private Map<Integer, Integer> zaUplatu = new HashMap<>();
    private int idFil;
    private int idKom;
    public void connect(){
        //ako postoji neka konekcija od pre
        disconnect();
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:Banka.db");
            System.out.println("Uspesna konekcija");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void disconnect(){
        if(conn==null) return;
        try {
            conn.close();
            conn = null;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    private void stampajSveRacunaZaKomitentaUFilijali(){
        String sql = "SELECT * FROM Racun WHERE idFil=? AND idKom=? AND Stanje<0";

        try(PreparedStatement ps = conn.prepareStatement(sql);){
            ps.setInt(1, idFil);
            ps.setInt(2, idKom);

            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                System.out.println(rs.getString("Status") + "\t" + rs.getInt("Stanje"));
                System.out.println("\n");
            }
        } catch (SQLException e) {
            //
        }
    }
    public void odradi(int idfil, int idkom){
        this.idFil = idfil;
        this.idKom = idkom;
        System.out.println("IdFil je" + idFil + " a idKom je " + idKom + "\nPre");
        stampajSveRacunaZaKomitentaUFilijali();
        dohvatiIznoseRacunaUMinusu();
        azuriraj();
        System.out.println("Posle");
        stampajSveRacunaZaKomitentaUFilijali();


    }

    //
    private void dohvatiIznoseRacunaUMinusu(){
        String sql = "SELECT * FROM Racun WHERE idFil=? AND idKom=? AND Stanje<0";
        try(PreparedStatement ps = conn.prepareStatement(sql);){
            ps.setInt(1, idFil);
            ps.setInt(2, idKom);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                zaUplatu.put(rs.getInt("IdRac"), -rs.getInt("Stanje"));
                System.out.println("Dodatu u zaUplatu " + rs.getInt("IdRac") + "|" + -rs.getInt("Stanje"));
            }
        } catch (SQLException e) {
            //
        }
    }
    private int dohvatiRedBr(int IdRac){
        String sql = "SELECT COALESCE(MAX(RedBroj), 0) + 1 FROM Stavka WHERE IdRac = ?";
        try(PreparedStatement ps = conn.prepareStatement(sql);){
            ps.setInt(1, IdRac);

            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            //
        }
        return -1;

    }
    private void azuriraj(){
        String sql = "UPDATE Racun SET Stanje = ?, Status='A' WHERE IdRac = ?";

        String sql3 = "INSERT INTO Uplata(IdSta, Osnov) VALUES (?,?)";
        try(PreparedStatement ps = conn.prepareStatement(sql);) {
            conn.setAutoCommit(false);
            for (Map.Entry<Integer, Integer> set : zaUplatu.entrySet()) {
                ps.setInt(2, set.getKey());
                ps.setInt(1, 0);
                ps.executeUpdate();
                System.out.println("Azurirano je stanje racuna " + set.getKey());

            }
            String sql2 = "INSERT INTO Stavka(RedBroj, Datum, Vreme, Iznos, IdFil, IdRac) VALUES (?,DATE('now'), TIME(), ?, ?, ?)";
            try(PreparedStatement ps2 = conn.prepareStatement(sql2);) {
                for (Map.Entry<Integer, Integer> set : zaUplatu.entrySet()) {
                    ps2.setInt(3, idFil);
                    ps2.setInt(4, set.getKey());
                    ps2.setInt(2, set.getValue());
                    ps2.setInt(1, dohvatiRedBr(set.getKey()));
                    if(ps2.executeUpdate() != 0){
                        try(ResultSet keysRs = ps.getGeneratedKeys()){
                            if(keysRs.next()){
                                int id = keysRs.getInt(1);
                                try(PreparedStatement ps3 = conn.prepareStatement(sql3);) {
                                        ps3.setInt(1, id);
                                        ps3.setString(2, "Uplata");
                                        ps3.executeUpdate();
                                    System.out.println("Izvrsena uplata sa id " + id);
                                }
                            }
                        }catch(SQLException e){
                            //
                        }

                    }
                }
            }

            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            //

        } finally{
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

Rešenje C++

Za rešavanje je potrebna Banka.db baza sa stranice predmeta i SQLite C biblioteka za komunikaciju s bazom. Oba su već data na laboratorijskoj vežbi.

Sledeće rešenje radi:

  1. Ispis početnog stanja stavki, računa komitenta i uplata
  2. Dohvatanje svih računa komitenta sa negativnim stanjem, i zatim za svaki račun:
    1. Pokretanje transakcije u slučaju da nešto pođe po zlu
    2. Dodavanje stavke s količinom uplaćenog novca (negiranog stanja računa)
    3. Dodavanje uplate s prethodno dodatom stavkom i "Uplata" kao osnovom
    4. Ažuriranje broja stavki, statusa i stanja na računu
    5. Završavanje transakcije ukoliko je sve prošlo kako treba, ili obustavljanje ako nije
  3. Ispis krajnjeg stanja stavki, računa komitenta i uplata
#include <iostream>
#include <string>
#include "sqlite3.h"

sqlite3 *otvoriBazu(const char *imeBaze) {
    sqlite3 *baza = nullptr;
    int kod = sqlite3_open(imeBaze, &baza);
    if (kod != SQLITE_OK) {
        throw std::string("Kod pri otvaranju: ") + std::to_string(kod);
    }
    return baza;
}

void zatvoriBazu(sqlite3 *baza) {
    int kod = sqlite3_close(baza);
    if (kod != SQLITE_OK) {
        throw std::string("Kod pri zatvaranju: ") + std::to_string(kod);
    }
}

void pripremi(sqlite3 *baza, const char *sql, sqlite3_stmt *&stmt) {
    int kod = sqlite3_prepare(baza, sql, -1, &stmt, nullptr);
    if (kod != SQLITE_OK) {
        std::string greska = std::string("Greška pri pripremanju upita: ") + sqlite3_errmsg(baza);
        sqlite3_finalize(stmt);
        throw greska;
    }
}

void izvrsiBezParametara(sqlite3 *baza, const char *sql, int (*callback)(void *, int, char **, char **)=nullptr) {
    char *errmsg = nullptr;
    int kod = sqlite3_exec(baza, sql, callback, nullptr, &errmsg);
    if (kod != SQLITE_OK) {
        std::string greska = std::string("Greška pri izvršavanju: ") + errmsg;
        sqlite3_free(errmsg);
        throw greska;
    }
}

int ispisSvega(void *, int colCount, char **rows, char **) {
    for (int i = 0; i < colCount; ++i) {
        if (rows[i] == nullptr) {
            std::cout << "null\t";
        } else {
            std::cout << rows[i] << "\t";
        }
    }
    std::cout << std::endl;
    return 0;
}

void sveStavke(sqlite3 *baza) {
    std::cout << "IdSta\tRedBroj\tDatum\t\tVreme\tIznos\tIdFil\tIdRac" << std::endl;
    izvrsiBezParametara(baza, "SELECT * FROM Stavka", ispisSvega);
}

void sveUplate(sqlite3 *baza) {
    std::cout << "IdSta\tOsnov" << std::endl;
    izvrsiBezParametara(baza, "SELECT * FROM Uplata", ispisSvega);
}

void sviRacuniKomitenta(sqlite3 *baza, int idKom) {
    const char *sql = "SELECT IdRac, Status, BrojStavki, DozvMinus, Stanje, IdFil, IdKom "
                      "FROM Racun "
                      "WHERE IdKom = ?";
    sqlite3_stmt *stmt = nullptr;
    int kod;
    pripremi(baza, sql, stmt);
    sqlite3_bind_int(stmt, 1, idKom);
    std::cout << "IdRac\tStatus\tBrojStavki\tDozvMinus\tStanje\tIdFil\tIdKom" << std::endl;
    while ((kod = sqlite3_step(stmt)) == SQLITE_ROW) {
        for (int i = 0; i < 7; ++i) {
            const unsigned char *red = sqlite3_column_text(stmt, i);
            if (red == nullptr) {
                std::cout << "null" << "\t";
            } else {
                std::cout << red << "\t";
            }
        }
        std::cout << std::endl;
    }
    if (kod != SQLITE_DONE) {
        std::string greska = std::string("Greška prilikom ispisivanja računa: ") + sqlite3_errmsg(baza);
        sqlite3_finalize(stmt);
        throw greska;
    }
    sqlite3_finalize(stmt);
}

void proknjiziStavku(sqlite3 *baza, int iznos, int idFil, int idRac) {
    const char *sql = "INSERT INTO Stavka (RedBroj, Datum, Vreme, Iznos, IdFil, IdRac) "
                      "SELECT COALESCE(MAX(S.RedBroj), 0) + 1, DATE(), TIME(), ?, ?, ? "
                      "FROM Stavka S "
                      "WHERE S.IdRac = ?";
    sqlite3_stmt *stmt = nullptr;
    int kod;
    pripremi(baza, sql, stmt);
    sqlite3_bind_int(stmt, 1, iznos);
    sqlite3_bind_int(stmt, 2, idFil);
    sqlite3_bind_int(stmt, 3, idRac);
    sqlite3_bind_int(stmt, 4, idRac);
    kod = sqlite3_step(stmt);
    if (kod != SQLITE_DONE) {
        std::string greska = std::string("Greška pri proknjižavanju stavke: ") + sqlite3_errmsg(baza);
        sqlite3_finalize(stmt);
        throw greska;
    }
    sqlite3_finalize(stmt);
}

void proknjiziUplatu(sqlite3 *baza) {
    const char *sql = "INSERT INTO Uplata (IdSta, Osnov) VALUES (?, 'Uplata')";
    sqlite3_stmt *stmt = nullptr;
    int kod;
    pripremi(baza, sql, stmt);
    sqlite3_bind_int(stmt, 1, sqlite3_last_insert_rowid(baza));
    kod = sqlite3_step(stmt);
    if (kod != SQLITE_DONE) {
        std::string greska = std::string("Greška pri proknjižavanju uplate: ") + sqlite3_errmsg(baza);
        sqlite3_finalize(stmt);
        throw greska;
    }
    sqlite3_finalize(stmt);
}

void azurirajRacun(sqlite3 *baza, int idRac) {
    const char *sql = "UPDATE Racun "
                      "SET Status = (CASE Status "
                                       "WHEN 'B' THEN 'A' "
                                       "ELSE Status "
                                    "END),"
                          "BrojStavki = (SELECT MAX(IdSta) FROM Stavka WHERE IdRac = ?),"
                          "Stanje = 0 "
                      "WHERE IdRac = ?";
    sqlite3_stmt *stmt = nullptr;
    int kod;
    pripremi(baza, sql, stmt);
    sqlite3_bind_int(stmt, 1, idRac);
    sqlite3_bind_int(stmt, 2, idRac);
    kod = sqlite3_step(stmt);
    if (kod != SQLITE_DONE) {
        std::string greska = std::string("Greška pri ažuriranju računa: ") + sqlite3_errmsg(baza);
        sqlite3_finalize(stmt);
        throw greska;
    }
    sqlite3_finalize(stmt);
}

void resi(int idKom, int idFil, sqlite3 *baza) {
    const char *sql = "SELECT IdRac, Stanje "
                      "FROM Racun "
                      "WHERE IdKom = ? AND Stanje < 0";
    sqlite3_stmt *stmt = nullptr;
    int kod;
    pripremi(baza, sql, stmt);
    sqlite3_bind_int(stmt, 1, idKom);
    while ((kod = sqlite3_step(stmt)) == SQLITE_ROW) {
        izvrsiBezParametara(baza, "BEGIN TRANSACTION");
        try {
            int idRac = sqlite3_column_int(stmt, 0);
            int stanje = sqlite3_column_int(stmt, 1);
            proknjiziStavku(baza, -stanje, idFil, idRac);
            proknjiziUplatu(baza);
            azurirajRacun(baza, idRac);
            izvrsiBezParametara(baza, "COMMIT");
        } catch (std::string &greska) {
            izvrsiBezParametara(baza, "ROLLBACK");
            sqlite3_finalize(stmt);
            throw;
        }
    }
    if (kod != SQLITE_DONE) {
        std::string greska = std::string("Greška pri dohvatanju računa komitenta: ") + sqlite3_errmsg(baza);
        sqlite3_finalize(stmt);
        throw greska;
    }
    sqlite3_finalize(stmt);
}

void ispisiStanje(int idKom, sqlite3 *baza) {
    sveStavke(baza);
    std::cout << std::endl;
    sveUplate(baza);
    std::cout << std::endl;
    sviRacuniKomitenta(baza, idKom);
    std::cout << std::endl;
}

int main(void) {
    try {
        sqlite3 *baza = otvoriBazu("Banka.db");
        int idKom, idFil;
        std::cout << "Unesi ID komitenta: ";
        std::cin >> idKom;
        std::cout << "Unesi ID filijale: ";
        std::cin >> idFil;
        std::cout << "Trenutno stanje:" << std::endl;
        ispisiStanje(idKom, baza);
        resi(idKom, idFil, baza);
        std::cout << "Novo stanje:" << std::endl;
        ispisiStanje(idKom, baza);
        zatvoriBazu(baza);
    } catch (std::string &greska) {
        std::cout << "Desila se greška." << std::endl << greska << std::endl;
        return EXIT_FAILURE;
    }
    return EXIT_SUCCESS;
}
Preuzeto iz „https://siwiki.rs/w/index.php?title=Базе_података_1/Лаб_1_семестрални_2020&oldid=5162”