alapok probléma - megoldás BadSqlGrammarException batch DataAccessException dokumentáció DTO EmptyResultDataAccessException exception IncorrectResultSizeDataAccessException map result oracle blob/clob postgres jsonb query ➔ 1 adat query ➔ 1 rekord query ➔ több rekord

RowCallbackHandler SimpleJdbcCall SimpleJdbcInsert SQL-paraméter standard jdbc @Transactional UncategorizedSQLException update()

• 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.

• 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.

• 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.

• 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:

• A DataAccessException az SQL-parancsok végrehajtásánál keletkező exception-ök ős osztálya.

• Data access with JDBC
• Tutorialspoint: Spring JDBC

• 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.

• 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 hierarhia:

• 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.

• 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:

• 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!

• 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:

• 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.

• Rekord beolvasása DTO-ba:
• Rekord beolvasása Map-be:
• Ha az eredmény nem 1 rekord, akkor EmptyResultDataAccessException vagy IncorrectResultSizeDataAccessException keletkezik.

• 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.

• Ó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:

• 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

• 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:

• 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:

• 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:

• 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

• 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!

• DML, és DDL parancsok végrehajtása.
• Rekordok törlése. Visszaadja az érintett sorok számát:
• Tábla létrehozása: