Базе података 1/Лаб 1 семестрални 2020
Пређи на навигацију
Пређи на претрагу
Поставка
Банка путем својих филијала (прати се назив и адреса) у разним местима (прате се поштански број и назив) опслужује своје комитенте (прати се назив и адреса) који могу бити без места, а у тренутку првог појављивања у банци пријављују седиште у одређеном месту.
Сваки комитент може да има више рачуна у свакој од филијала (прате се статус, број ставки, дозвољени минус, и стање), а мора имати бар један рачун. Статус рачуна може бити активан, блокиран или угашен. Рачун постаје блокиран када пређе у недозвољени минус, а активира се када стање пређе у дозвољени минус.
Комитенти са својих рачуна врше трансакције путем ставки промета (прате се редни број, датум и време) које могу бити уплате (прати се основ и износ) или исплате (прате се износ и провизија), при чему је то могуће у било којој филијали.
ЕР дијаграм базе из поставке задатка.
Задатак
Комитент је решио да уплати минимално пара на своје рачуне, али при томе да на свим рачунима не буде у минусу након уплате. Написати функцију која врши уплату за једног корисника. Функција као аргументе прима idFil и idKom и враћа суму извршене уплате. Функција је потребно да буде отпорна на грешке.
Решење 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);
}
}
}
}
Решење C++
За решавање је потребна Banka.db база са странице предмета и SQLite C библиотека за комуникацију с базом. Оба су већ дата на лабораторијској вежби.
Следеће решење ради:
- Испис почетног стања ставки, рачуна комитента и уплата
- Дохватање свих рачуна комитента са негативним стањем, и затим за сваки рачун:
- Покретање трансакције у случају да нешто пође по злу
- Додавање ставке с количином уплаћеног новца (негираног стања рачуна)
- Додавање уплате с претходно додатом ставком и "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;
}