jdbc template
Tartalomjegyzék
alapok
-
A JDBC Template használatához a következők szükségesek:
-
1. pom.xml kiegészítés:
-
2. pom.xml kiegészítés: az adatbázis eléréséhez szükséges adatbázis driver, például:
-
Egy vagy több datasource definíció létrehozása az application.yml fájlban, például mydb előtaggal:
Ha viszont egy alkalmazásszerver jndi-ként biztosítja az adatbázis elérést,
akkor csak egy jndi-re hivatkozó sor kell:
Beállíthatjuk a default séma nevet az SQL-parancsok használatához, de ezt felül lehet bírálni
az SQL-parancsba beleírt séma névvel.
Ide csak egyetlen séma név írható, tehát nem úgy működik mint a postgres "set search_path" parancsa:
Szabályozható a pool-ban lévő adatbázis kapcsolatok számának minimuma és maximuma:
Ezek megadása nélkül 10 adatbázis kapcsolattal indul a rendszer.
-
Az application.yml fájlban létrehozott datasource definiciókra DataSource bean-eket kell építeni.
A @ConfigurationProperties annotáció definiálja hogy a "jdbc-url", "username", "password", ...
bejegyzések előtt milyen előtagot kell figyelembe venni:
-
A Datasource bean-re rá kell építeni a NamedParameterJdbcTemplate bean-t:
-
Az így kapott NamedParameterJdbcTemplate bean-t be lehet húzni egy DAO osztályba:
-
Példa: teszt és eles környezetben is futó alkalmazás oracle adatbázisból adatokat másol
postgres adatbázisba.
Ehhez elkészítjük a következő fájlokat:
és mindegyikben csak egy oracle, és egy postgres elérést definiálunk, hiszen futáskor csak az egyik
yml-fájlt fogjuk aktiválni. Az előtagok a yml-fájlban:
A 2 DataSource bean:
Erre a 2 bean-re kell ráépíteni a 2 npjt bean-t:
az adatmásoló DAO osztályokba pedig behúzható a 2 npjt bean:
-
A spring nem ellenőrzi a datasource létrehozáskor, hogy valósak-e az adatok, vagyis elérhető-e az adatbázis.
Ez azért jó, mert minden datasource-t létre lehet hozni, azokat is amik nem érhetők el az adott környezetben.
De vigyázzunk, hogy a valótlan datasource-ból ne kérjünk adatbázis connection-t, mert az exceptiont okoz.
-
Nem okoz problémát ha az SQL-parancs Postgres féle "::" -tal jelölt cast-ot tartalmaz,
vagy stringen belül fordul elő ":x", vagy "?". Ezeket nem tekinti SQL paraméternek.
-
Ha szeretnénk hogy több egymás utáni SQL-parancs is ugyanabban az adatbázis connection-ben hajtódjon végre,
akkor használjuk a @Transactional annotációt, vagyis helyezzük őket egy tranzakcióba.
Ezesetben a spring kénytelen ugyanabban a connection-ben végrehajtani őket.
-
Ha az SQL-parancsban szerepel egy paraméter, akkor azt mindenképp meg kell adni az SqlParameterSource osztályban,
különben exception keletkezik.
-
Ha az SqlParameterSource osztályban megadunk olyan paramétert ami nem szerepel az SQL-parancsban,
az figyelmen kívül marad.
-
Ha a BeanPropertyRowMapper.newInstance() metódusban megadott osztály tartalmaz olyan tagot amit az
SQL-parancs nem tartalmaz, az figyelmen kívül marad.
-
Ha a BeanPropertyRowMapper.newInstance() metódusban megadott osztály nem tartalmaz olyan tagot amit az
SQL-parancs oszlopként tartalmaz, az nem okoz hibát.
Tehát nem probléma ha "SELECT * FROM ..." paranccsal olvas be a program, és a tábla később új oszloppal bővül.
-
Ha a BeanPropertyRowMapper.newInstance() metódusban megadott osztály tagja szerepel az SQL-parancsban oszlopként,
akkor lehessen sikeresen az értéket átkonvertálni, különben exception keletkezik.
Tehát exception keletkezik ha SQL VARCHAR típust java Long típusba szeretnénk fogadni.
-
A BeanPropertyRowMapper.newInstance() metódusban megadott osztály tagjaihoz kell lennie getter,
és setter metódusoknak, csak ezekbe tudja a JdbcTemplate az értékeket beolvasni.
Ellenkező esetben exception nem keletkezik, de ezek a tagok null értéket fognak tartalmazni.
probléma - megoldás
Tranzakción belül szeretnénk, hogy egy adatbázis művelet ne legyen része a tranzakciónak.
-
Elméletileg ez úgy valósítható meg, hogy a külső metódus (controller-metódus) el van látva ezzel az annotációval:
a belső metódus (dao-metódus), pedig felfüggeszti a tranzakciót, ezzel az annotációval:
Sajnos ez csak bizonyos tranzakció manager-ek esetén működik, nem mindig megoldás.
-
A megoldás:
Abban a dao-metódusban, melyben tranzakción kívüli műveletet szeretnénk végezni,
olyan DataSource-t (NamedParameterJdbcTemplate-et) kell alkalmazni,
melyet nem használ a tranzakció manager (nem adtunk meg a tranzakció manager-nek)!
Így például lehetséges, hogy a yaml-ban csak 1 adatbázis van leírva, mégis 2 DataSource-t készítünk hozzá,
de csak az egyiket adjuk meg a tranzakció manager-nek.
A másikat csak akkor használjuk, ha tranzakción kívüli műveletet hajtunk végre.
Példa: adatbázis műveletek esetén, ha hiba keletkezik, akkor írjunk be az adatbázis hibanapló táblájába.
Ez utóbbit nem szabad, hogy a spring rollback-elje, hiszen nem lenne nyoma a hibának.
BadSqlGrammarException
-
Ilyen esetekben váltódik ki:
-
Nemlétező adatbázis függvény meghívása.
-
Adatbázis függvény meghívása nem megfelelő típusú/számú paraméterrel.
-
Létező adatbázis függvény meghívása, melyhez hiányzik a szükséges jogosultság.
batch
-
A batch módszerrel egyben lehet végrehajtani ugyanazt az SQL-parancsot más-más paraméterekkel.
A módszernek az az előnye, hogy az SQL-parancsot csak egyszer kell az adatbázisnak parse-olni.
-
Oracle esetén csak kb. 20% időt lehet vele nyerni, mivel az oracle parser felismeri ha ugyanaz az
SQL-parancs sokszor ismétlődik, és ezért fel tudja használni az előző parse-olás eredményét.
-
Példa:
DataAccessException
-
A DataAccessException az SQL-parancsok végrehajtásánál keletkező exception-ök ős osztálya.
dokumentáció
DTO
-
private + getter/setter
A DTO osztály tagjai private-ok legyenek, és getter/setter metódusokkal legyenek elérhetők.
-
Ha a DTO-tagok public-kal ellátottak, és nincs hozzájuk getter/setter,
akkor a jdbctemplate nem tudja feltölteni a DTO-t.
Exception nem keletkezik, de a DTO-tagok null értéket vesznek fel.
-
AC/DC szabály
Nem probléma ha a DTO kevesebb, vagy több taggal rendelkezik, mint az SQL-select parancs oszlopai.
EmptyResultDataAccessException
-
A queryForMap(), queryForObject(), ... metódusok esetén pontosan egy sor a várt eredmény,
ezért EmptyResultDataAccessException keletkezik ha nincs eredmény sor.
exception
IncorrectResultSizeDataAccessException
-
A queryForMap(), queryForObject(), ... metódusok esetén pontosan egy sor a várt eredmény,
ezért IncorrectResultSizeDataAccessException keletkezik ha az eredmény egynél több vagy kevesebb sor.
map result
-
Postgres adatbázis lekérdezés esetén, ha az eredményt Map-ben kapjuk, akkor a következő típusok fognak
a map-ben keletkezni:
oracle blob/clob
-
BLOB mező írása:
-
Az SqlLobValue típust kell használni, és az adat-típusát is meg kell adni, mely Types.BLOB
-
Példa egy fájl feltöltésére BLOB mezőbe:
-
Az SqlLobValue segítségével byte[] is feltölthető a konstruktorában byte[] megadásával.
-
CLOB mező írása:
-
Az SqlLobValue típust kell használni, a konstruktorában String megadásával,
és az adat-típusát is meg kell adni, mely Types.CLOB
-
BLOB mező olvasása:
-
A queryForMap() metódus byte[] -ben adja vissza a BLOB adatot.
-
BeanPropertyRowMapper esetén is byte[] típusba tudjuk megkapni a BLOB adatot.
Meg lehetne kapni SqlLobValue objektumban is, de abból az adat nem nyerhető vissza!
-
CLOB mező olvasása:
-
A queryForMap() metódus String-ben adja vissza a CLOB adatot.
-
BeanPropertyRowMapper esetén is String típusba tudjuk megkapni a CLOB adatot.
Meg lehetne kapni SqlLobValue objektumban is, de abból az adat nem nyerhető vissza!
postgres jsonb
-
Ha postgres adatbázisból jsonb típusú tábla oszlopot kérdezünk le,
akkor azt org.postgresql.util.PGobject típusként kapjuk meg.
Ebből lehet kivenni a valódi json-stringet:
-
Ha a postgres adatbázis jsonb típusú objektumot fogad, de csak String-ként van meg az adatunk,
akkor cast-olni kell az adatot jsonb-re:
query ➔ 1 adat
-
Egyetlen oszlop/kifejezés lekérdezése, az oszlop típusának megfelelő osztályba, például:
Integer, Long, String, Date, ...
-
Adatbázis függvény hívása, mely boolean típussal tér vissza:
-
Ha az eredmény nem 1 rekord, akkor EmptyResultDataAccessException vagy
IncorrectResultSizeDataAccessException keletkezik.
query ➔ 1 rekord
-
Rekord beolvasása DTO-ba:
-
Rekord beolvasása Map-be:
-
Ha az eredmény nem 1 rekord, akkor EmptyResultDataAccessException vagy
IncorrectResultSizeDataAccessException keletkezik.
query ➔ több rekord
-
Rekordok beolvasása List<DTO>-ba:
-
Rekordok beolvasása List<Map>-be:
-
Csak 1 oszlopból álló rekordok beolvasása List<XXX>-be:
-
Ha az eredmény 0 rekord, akkor üres listát kapunk (nem pedig null-t).
Így tehát EmptyResultDataAccessException sem keletkezik.
RowCallbackHandler
-
Óriási méretű táblán is végig lehet haladni kurzor szerűen,
így elkerülhető az out of memory probléma.
Nem kérjük ki egyben az egész rekord-halmazt, de valamilyen műveletet végzünk a rekordokon.
A query() második paramétere implementálja a RowCallbackHandler funkcionális interface-t,
ezért a rekord adatait ResultSet-ként kapjuk meg:
SimpleJdbcCall
-
A SimpleJdbcCall osztállyal adatbázis függvény hívható meg.
A használata körülményesebb mint a JdbcTemplate metódusait használni, ezért ha lehet kerüljük.
Az adatbázis függvények úgy is meghívhatók, hogy SQL-SELECT parancsba ágyazzuk őket.
Az olyan függvény mely egyetlen adattal tér vissza, simán beágyazható SQL-SELECT parancsba:
Az összetett adatszerkezetet visszaadó függvényt (például: RETURNS record) táblaként ágyazzuk be az
SQL-SELECT parancsba,
így az összetett adatszerkezetét tábla oszlopokként kapjuk vissza:
-
A SimpleJdbcCall létrehozásakor a konstruktorban vagy a DataSource-t, vagy JdbcTemplate-et kell megadni,
és be kell állítani a séma/function/procedure nevet, amely meghívásra kerül.
-
Az adatbázisban ne használjunk egy sémán belül több ugyanolyan nevű function-t vagy procedure-t,
különben ezt az üzenetet kapjuk:
Ez lehet az adatbázis driver hibája is. Nem találtam rá megoldást a google keresőben, ami működne.
-
A hívás paraméterei megadható Map-pel, SqlParameterSource-szal, vagy varargs megoldással.
-
1. példa:
Egy olyan osszeado() function kerül meghívásra mely a szam1 és szam2 nevű
integer típusú értékeket várja, és ezek összegét adja vissza:
-
2. példa:
Egy olyan function kerül meghívásra, melynek 2 darab IN, és 2 darab OUT paramétere van.
Az execute() előtt csak az IN paramétereket kell megadni, az OUT paramétereket egy Map-ben kapjuk meg:
-
Elképzelhető, hogy az adatbázis (driver) érzékeny rá, hogy a híváskor csak az input paramétereket adjuk meg,
és nem definiáljuk az output paramétereket.
Ekkor hibajelzést kapunk az output paraméterre, melyet hibásan inputnak feltételez:
Ebben az esetben kénytelenek vagyunk az összes paramétert definiálni a declareParameters() metódussal:
Az SqlParameter() metódusban a paraméter nevét és típusát kell megadni.
Postgres esetén néhány típus megfeleltetés:
Postgres típus |
Java típus |
bytea |
Types.BINARY |
bpchar |
Types.CHAR |
character |
Types.VARCHAR |
int4 |
Types.INTEGER |
int8 |
Types.BIGINT |
jsonb |
Types.OTHER |
SimpleJdbcInsert
-
SQL-insert végrehajtása egyszerűen, az SQL-parancs leírása nélkül.
A SimpleJdbcInsert létrehozásakor a konstruktorban vagy a DataSource-t, vagy JdbcTemplate-et kell megadni,
és be kell állítani a séma/táblanevet, ahová az SQL-insert beszúrja a rekordot.
-
Az SQL-insert oszlopai és értékei megadható Map-pel, vagy SqlParameterSource-szal.
Kevesebb oszlop is megadható, illetve a táblában nem létező, de megadott oszlopnevek sem okoznak problémát,
ezek figyelmen kívül maradnak.
-
Az execute() metódusa többször is végrehajtható!
-
Oszlopnevek/értékek megadása BeanPropertySqlParameterSource-szal:
-
Oszlopnevek/értékek megadása Map-pel:
SQL-paraméter
-
NamedParameterJdbcTemplate
Az SQL-parancs paramétereit ":paraméterNév" jelzi,
így a paraméterekhez tartozó értékeket tetszőleges sorrendben lehet megadni,
paraméterNév - paraméterÉrték adatpárokkal.
Az SQL-parancsban felsorolt ":paraméterNév" -hez kötelező értéket megadni, különben exception keletkezik.
Ha Map-et használunk akkor kell lennie ilyen map kulcsnak, még akkor is ha null-értéket rendelünk hozzá.
Ha pedig BeanPropertySqlParameterSource-t használunk akkor a POJO-ban kell lennie getter metódusnak.
A paraméterek megadása a következő módszerekkel lehetséges:
-
Map<String, Object>
Nem adhatók meg a paraméterek típusai.
-
MapSqlParameterSource
Ez jobb megoldás mint a sima Map használata, mivel paraméter típust is beállíthatunk hozzá,
és az addValue() metódusai láncba köthetők!
-
BeanPropertySqlParameterSource
Szükséges hozzá egy POJO osztály, mely tagnevei megegyeznek a paraméter nevekkel, és getter metódusuk is van.
-
JdbcTemplate
Az SQL-parancs paramétereit "?" jelzi, így a paraméterekhez tartozó értékeket ugyanilyen
sorrendben kell megadni.
A paraméterek megadása a következő módszerekkel lehetséges:
-
Varargs
Ezt a legkönnyebb leírni, viszont így nem adhatók meg a paraméterek típusai:
-
Object[]
Megadhatók a paraméterek típusai is:
standard jdbc
-
Npjt használatakor is van lehetőségünk a standard jdbc-objektumok (PreparedStatement, ResultSet, ...) használatára .
Ehhez meg kell kapnunk a Connection objektumot.
-
1. megoldás:
Az npjt objektumból kapjuk meg:
-
2. megoldás:
@Autowired-del behúzzuk a DataSource objektumot.
Ennek biztos meg kell lennie, hiszen az npjt objektum is erre épül:
@Transactional
-
A @Transactional csak akkor működik, ha az általa annotált metódus public elérhetőséggel rendelkezik:
-
A @Transactional annotáció controller metódus, vagy @Service osztály metódusra is tehető.
-
A DataSource bean-re épített JdbcTemplate bean esetén közvetlenül nem férünk hozzá a Connection objektumhoz.
Ez a Connection objektum defaultban AUTOCOMMIT ON módban van,
így minden DDL/DML parancs rögtön commit-álódik is.
Ha ezt nem szeretnénk, akkor az adatbázis műveleteket végző DAO metódusokat el kell látni a
@Transactional annotációval, így ezek a metódusok AUTOCOMMIT OFF módban futnak.
-
Ha a @Transactional annotációval ellátott metódus sikeresen lefut, akkor a végén automatikusan egy SQL-commit
is végrehajtódik, de ha bármilyen exception-t dob kifelé a metódus, akkor automatikusan egy SQL-rollback
hajtódik végre. Ez az általános működés, de ez átállítható a @Transactional paramétereivel.
-
A @Transactional egyik paramétere a propagation, ami defaultban REQUIRED értéken áll.
A REQUIRED jelentése, hogy tranzakciót kell létrehozni, de csak akkor ha ez a metódus nincs beágyazva egy
olyan metódusba, ami szintén el van látva @Transactional annotációval. Ha be van ágyazva akkor nincs külön
tranzakció nyitás, hanem a hívó metódusban nyitott tranzakción belül fut a belső metódus is.
-
Spring boot esetén a @Transactional működéséhez nem szükséges az @EnableTransactionManagement
annotáció, nélküle is működik.
-
A @Transactional annotáció akkor is jó, ha szeretnénk hogy több egymás utáni SQL-parancs is ugyanabban az
adatbázis connection-ben hajtódjon végre. Ezeket az SQL-parancsokat helyezzük egy @Transactional annotációval
ellátott metódusba. Ezesetben a spring kénytelen ugyanabban a connection-ben végrehajtani őket.
-
Ha a spring alkalmazás egy DataSource-ot tartalmaz:
Automatikusan létrejön egy transaction manager (TM), ilyen típussal:
Ez úgy ellenőrizhető, hogy az alkalmazás indulásakor lekérdezzük a TM-t:
-
Ha a spring alkalmazás több DataSource-t tartalmaz:
Nem jön létre automatikusan TM. A TM lekérdezése is exception-t okoz.
Így tehát nekünk kell létrehozni egy vagy több TM-t egy-egy DataSource-ra vonatkozóan:
Több TM is létrehozható. Több TM esetén viszont a @Transactional annotációban meg kell nevezni melyik TM-et
akarjuk használni. Csak egyetlen TM nevezhető meg:
Egy másik lehetőség hogy egy TM több DataSource-t is kezel:
ChainedTransactionManager megjegyzések:
- A ChainedTransactionManager osztály deprecated, egyelőre nincs alternatívája, ezért még ez használandó.
- Ha a ChainedTransactionManager-t használni akarjuk, akkor ez is kell a pom.xml-be:
-
Több DataSource esetén, ha az egyik DataSource-szal végzünk adatbázis műveletet, és a másik dataource-ra van beállítva
a tranzakciókezelés, akkor nem lesz tranzakciónált a műveletünk, tehát a megfelelő TM-t kell használni!
-
TM-ben csak olyan DataSource-t szabad megadni ami valós, tehát lehet vele az adatbázishoz kapcsolódni,
különben a spring exception-t dob. A TM létrehozza a valós DB connection-t, és beállítja setAutoCommit(false)-ra.
-
Dokumentáció a tranzakciókezeléséről
UncategorizedSQLException
-
Ilyen esetekben váltódik ki:
-
Adatbázis függvény meghívása, mely raise exception műveletet hajt végre.
-
Hozzáférés a hiba részletekhez:
Figyelem!
Postgres esetén a raise exception-nél beállított errcode-t,
a java-ban az SQL-state-nél kapjuk vissza!
update()
-
DML, és DDL parancsok végrehajtása.
-
Rekordok törlése. Visszaadja az érintett sorok számát:
-
Tábla létrehozása: