JDBC w 5 minut

JDBC (Java Database Connector) to niskopoziomowe operacje na bazie danych przy pomocy sterownika producenta.
Daje większe możliwości niż popularne frameworki wykorzystujące mapowanie obiektowo-relacyjne, jednak obsługa zapytań odbywa się większym nakładem pracy. Warto zacząć od nich nauke, bo bywają przydatne tam gdzie ORM nie dają rady 🙂

Tworzymy klasę  do połączenia z bazą stosując wzorzec Singleton. Dlaczego zastosujemy ten wzorzec? Potrzebujemy tylko jednej instancji tego obiektu, więc tworzymy prywatny konstruktor.

Obiekt stworzymy przy użyciu statycznej (czyli nie potrzebującej obiektu) metody. Tworzymy też warunek, w którym sprawdzamy czy obiekt istnieje.

Wczytujemy parametry połączenia z naszego pliku db.properties* do zmiennych przy użyciu FileReader

I rejestrujemy sterownik przy pomocy Class.forName oraz przekazujemy DriverManager.getConnection ze szczegółami połączenia do obiektu connection.

Zwracamy obiekt

*zawartość pliku db.properties

Ważne: nie zapominamy o dołączeniu sterownika bazy Oracle w IDE Intelij IDEA.
https://wilamowski.it/index.php/2019/07/28/how-to-import-jdbc-oracle-driver-to-intelij-idea-project-ojdbc6-jar/

 

Przejdźmy do klasy testowej. Tworzymy obiekt  connection przy pomocy metody statycznej:

Do wykonania zapytania SQL i połączenia z bazą tworzymy obiekt statement (interfejs Statement, java.sql). Potrzebny nam jest do wysłania zapytania do bazy. PS Jeżeli potrzebujemy zapytań zparametryzowanych lub zapytanie będzie używane wielokrotnie warto zainteresować się PreparedStatment.

Metoda executeQuery zwraca wynik zapytania. W tym samym momencie może być otwarty jeden Statement i jeden ResultSet.

Mapujemy encje i wyświetlamy rezultat wiersz po wierszu (.next() zwraca wartość logiczną)


 —————————————————–

Projekt przedstawiający przykład połączenia z plikiem parametrów wzorcem singleton i prostym zapytaniem. Myślę, że dobrze to ukazuje idee bez zbędnego kodu

https://github.com/connoisseur0/JDBC-example

Source:
1. https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html#creating_statements
2. https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html

 

Tworzenie użytkownika i nadanie uprawnień użytkownikowi bazy

Tworzenie użytkownika bazy Oracle:

Nadanie uprawnień użytkownikowi:

Typowe role dla połączenia i tworzenia obiektów w swoim schemacie:
CONNECT, RESOURCE

Uprawnienia do wszystkich tabel.
Stwórzmy role support:
create role support;
Następnie dodajmy do niej prawa

Uprawnienia do wszystkich obiektów w poleceniu

Przykład:
GRANT support TO test2;

Podgląd uprawnień w widokach
user_sys_privs dla uzytkownika:

dla usera sys:

src: http://www.nazmulhuda.info/grant-select-on-all-tables-in-a-specific-schema-to-a-user

Procedura PL/SQL wyliczająca wartość kolumny B na podstawie kolumny A

Cel: Wyliczenie kolumny Bna podstawie wartości z kolumny tabeli z dla wszystkich wierszy.
Kontekst: Hurtownia danych. Tabela faktów zawiera już przeliczony wiek pacjenta, dodałem jeszcze jeszcze jedną kolumne GRUPA_WIEK aby wstawić w nią klucz obcy do mini wymiaru zawierającego grupy wiekowe.
Uwaga: złamana 3 postać normalna, kolumna jest zależna od drugiej.

Krótki wywód o poszukiwaniu optymalnej długości typu danych. Część 1 – atrybut NAZWISKO.

Weźmy pod lupę atrybut NAZWISKO.
varchar2(30), (50), (100) a dlaczego nie(4000)? (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1145132537055)

Używając poniższych zapytań, po przebrnięciu przez masę kwiatków dobrnąłem do elitarnego grona ludzi z najdłuższymi nazwiskami w „mojej” bazie. Znalazłem obcokrajowca z 29 znakami w tym trzema białymi.
Najdłuższe polskie to kombinacje dwóch nazwisk z myślnikiem i jedno ze spójnikiem „vel”. Natomiast jeśli chodzi o imiona to dość popularne są podwójne co oznacza dla nas ponad 20 znaków.
Nieźle. Ale co mówią statystyki?
Znalazłem panią Achmistrowicz-Wachmistrowicz. Idąc tym tropem, zrobiłem szybki research na portalach genealogicznych. Podpowiadają one nazwiska 15 znakowe, co potencjalnie daje szanse na uzasadnione varchar2(31) …
Bezpieczna wartoscia biorac pod uwage obcokrajowcow bedzie 100.

Klucz obcy odwołujący się do tej samej tabeli

Co zrobić w sytuacji gdy w tabeli Pracownicy masz mieć zawartą informacje o przełożonym tej osoby? Przeciez przełożony jest też pracownikiem. Nie trzeba tworzyć zawiłych redundatnych konstrukcji, z pomocą przychodzi rekurencja – możemy odwołać się do tej samej encji.

Analiza relacji tabel

Mały tool wyświetlający referencje do podrzędneych tabelu.  Select wykonany w Developerze wyświetli okienko wprowadzenia zmiennej (‚&tableOfInterest’) dla nazwy tabeli parent. Przydatny do szukania relacji, analizy struktury bazy.
Szybsza opcja niz szukanie w GUI po foreign key, ale raczej do wspomagania niz jako substytut Data Modelera 🙂

1. Mamy FK sprawdzmy do których PK odwoluja sie:

2. I odwrotnie. Mamy PK i chemy znalezc tabele, ktore sie do niego odwoluja.

Klucze główne w Oracle, autoinkrementacja oraz funkcja sys_guid

W odróżnieniu od innych baz, w Oraclu do wersji 11g, do tworzenia inkrementacji klucza głównego nie mogliśmy użyć zwyczajnie autonumeracji jak na poniższym przykładzie w MySQL

Zamiast tego trzeba było stosować wyzwalacz, który pobierał wartość z sekwencji.

Lata pózniej w wersji 12c wprowadzono możliwość określenia takiej właściwości przy tworzeniu tabeli, w prostszy sposób.

Funkcja sys_guid.

W niektórych przypadkach przydatna może być funkcja sys_guid generująca i zwracająca globalny, unikalny ID (RAW, do 16 bajtów). Składa się z id hosta, procesu lub id wątku oraz niepowtarzalnej wartości. Nie jest to inkrementacja tzn. nie są to kolejne „user-friendly” liczby, a wartości heksadecymalne (vide wynik przykładu 1. poniżej). Poza tym może być używana jako wartość domyślna w CREATE TABLE (DEFAULT)

 

Przykłady sys_guid():
1. Aktualizacja kolumny.

Wynik:

UID_COL
—————————————-
7CD5B7769DF75CEFE034080020825436
7CD5B7769DF85CEFE034080020825436
7CD5B7769DF95CEFE034080020825436
7CD5B7769DFA5CEFE034080020825436

2. Zastosowanie przy uzyciu wyzwalacza

Oracle Apex 5: odblokowanie admina bez @apxchpwd

Uruchamiamy SQLPLUS as sysdba.
1. Sprawdzamy id:

2. Zmieniamy hasło.

3. Odblokowujemy konto.

SQLPLUS: Command not found

Przyczyną może być brak ustawionych zmienych.

Kopiujemy je z instalacji Oracle za pomocą…

lub
Uruchamiamy narzedzie:
/usr/bin/oraenv
Ewentualnie:

CentOS – podstawowa konfiguracja sieci

Przykładowe narzędzia/polecenia:
nmcli d – wyświetlenie nazwy urządzenia, typu (np. Ethernet), stan połączenia,
nmtui –  Network Manager pozwala edytować ustawienia IP połączenia, IPv4, IPv6, maske, brame, zmienić hostname i aktywować połączenie.
ip address show – wyświetlenie adresu IP
ifconfig – wyświetlenie adresacji wszystkich kart *1
etc/sysconfig/network – ściażka do hostname DNS
systemctl status network – sprawdzenie statusu usługi
systemctl restart network – restart usługi

*1 Gdy system zwraca „polecenie nieznane” (np. gdy używasz wersji minimalnej) oznacza to ze brakuje pakietu net tools zainstaluj go poleceniem:
yum install net-tools -y