JDBC Architektur DBA Zusammenfassung Java Applikation Standards - ODBC Open Database Connectivity - JDBC Java Database Connectivity JDBC Kategorien Type 1: JDBC-ODBC-Bridge Type 2: herstellerabhängiger Treiber (Client) Type 3: serverseitige Middleware Installation Type 4: JDBC API Befehle direkt in DBMS übersetzt Ablauf JDBC Prinzip JDBC Treiber laden Verbindung zur DB erstellen Execute Statments Process Query Commit or Rollback Verbindung schliessen JDBC API JDBC Driver Manager JDBC Treiber JDBC Treiber DBMS DBMS Ablauf JDBC Code Class.forName(„org.postgressql.Driver“); // ClassNotFoundException String username = „dba“; String password = „pwd“; String urlString = “jdbc:postgresql://labts004.zhaw.ch:<port>/<dbname>”; Connection con = DriverManager.getConnection(urlString, username, password) Statement s = con.createStatement(); // PreparedStatement, CallableStatement ResultSet res = s.executeQuery(); // executeUpdate(), execute() While (res.next()) { System.out.println(res.getString(1)) } con.close(); Transactions con.commit()/con.rollback() ACID: Atomic, Consistent, Independent, Durable Auto-Commit!: Connection.setAutoCommit(false) Connection Pooling: DataSources wiederverwenden Credentials sind in JNDI hinterlegt MetaData DatabaseMetaData dbMeta = con.getMetaData(); dbMeta.getTables(null, null, null, null); ResultSetMetaData resMeta = res.getMetaData(); resMeta.getColumnCount(); resMeta.getColumnName(int i); Isolationslevel: conn.setTransactionIsolation(int i) TRANSACTION_NONE .._READ_UNCOMMITED: Zugriff auf noch nicht festgeschriebene Daten (DIrty Data) ...READ_COMMITED: nicht immer gleiches Res:: lesen REPEATABLE_READ: Phantomdata insert update SERIALIZABLE: alle autonom ist standard PreparedStatement PreparedStatement prep = con.prepareStatement(“INSERT INTO Data VALUES (?,?)”); prep.setString(1, “Jim”); prep.setInt(2, 70); if(prep.executeUpdate() != 1){throw new Exception(“BadRequest”)} JDBC Exceptions -SQLException -SQLWarning -DataTrunction .getNextException() Server Programmierung SQL Function CREATE OR REPLACE FUNCTION insertStudent(int, varying character,int) RETURNS void AS ‘INSERT INTO studenten VALUES($1,$2,$3);’ LANGUAGE ‘sql’ SELECT insertStudent(50000, varchar ‘Andy’, 3); PLjava Jar erzeugen (cmd) jar cvf test.jar test.class Auf Server installieren select sqlj.install_jar(‚file:///C:/jars/test.jar‘, ‘ClassName‘, false); select sqlj.replace_jar(‚<jar_url>‘, ‘<jar_classname>‘, false); !! Neustart Session File zeigen select sqlj.set_classpath(‘schema’, ‘jarName’); CREATE OR REPLACE FUNCTION …RETURNS varchar AS ‘KLASSE.methode’ LANGUAGE ‘java’ EmbeddedSQL EXEC SQL BEGIN DECLARE SECTION char cid[5], cname[14], city[21]; float discount; EXEC SQL END DECLARE SECTION; EXEC SQL select cname into :cname from customers where cid = :cid; EXEC SQL insert into customers values (:cid, :cname, :city, :discount); Escape Sequence (Driver vorbehandelt) %,_ {escape ‚escape character‘} Skalarfnk : {fn functionName(arguments)} {fn rtrim(name)} Date: general: {d 'yyyy-mm-dd'} example: {d '1997-05-03'} Time: general: {t 'hh:mm:ss'} example: {t '14:03:15'} SQL Postgres Typumwandlung -CAST(Ausdruck AS Typ) -Ausdruck::Typ -Typname(ausdruck) Nach der Ausführung steht in sqlca ob Exceptions geworfen wurden. Trigger CREATE TRIGGER name {BEFORE / AFTER} {INSERT OR UPDATE OR DELETE} ON tabelle FOR EACH ROW EXECUTE PROCEDURE function ([argument]) Funktion muss Rückgabetyp “trigger” haben. Löschen: DROP TRIGGER name; Anwendung: Sicherheit, Auditing, Datenintegrität, Referentielle Integrität, Tabellen-Replikation, Abgeleitete Daten, Ereignisprotokollierung DAO (Data Access Object Pattern) Wie speichere ich POJO (Plain Old Java Objects)? - Trennt Business-Logik von Persistenz-Logik Java Persistence API - Ziele: Transparente Persistenz (reine Businesslogik), Automatische Persistenz: API übernimmt Speicherung, Vereinfachung Entity Bean - Java-Beans: Software Komponenten ein Pojo - ORM Object Relation Mapping: Abbildung von Entity Bean auf einen Tupel - Was mappen: Namen (falls nicht übereinstimmung), Typen, Schlüssel, Beziehungen - Wie mappen: Ausserhalb von Code XML Hibernate, Innerhalb mit Annotationes JPA JDBC Exceptions and Warnings try { Statement stmt = con.createStatement (); ResultSet rs = stmt.executeQuery (stmtSource); SQLWarning rsWarn; while (rs.next()) { System.out.println (rs.getInt (1)); rsWarn = rs.getWarnings(); // Display any warnings while (rsWarn != null) { System.out.println (rsWarn.getMessage()); rsWarn = rsWarn.getNextWarning(); } } } catch (SQLException sqe) { // Display all SQLExceptions with a while statement while (sqe != null) { System.out.println (sqe.getMessage()); sqe = sqe.getNextException(); } } Trigger DROP TRIGGER name ; CREATE TRIGGER name {BEFORE / AFTER} {INSERT OR UPDATE OR DELETE} Mehrere Events INSERT, UPDATE ON tabelle FOR EACH ROW Statement Trigger durch weglassen FOR EACH ROW EXECUTE PROCEDURE function ([argument]) Anwendung: Sicherheit, Auditing, Datenintegrität, Referentielle Integrität, Tabellen-Replikation, Abgeleitete Daten, Ereignisprotokollierung //Student der nicht mehr als 6 Vorlesungen hören darf static void belastung(TriggerData td) throws SQLException { ResultSet rsnew = td.getNew(); Statement statement; statement = DriverManager.getConnection("jdbc:default:connection") .createStatement(); String select = "select count(*) from hoeren where matrnr = " + rsnew.getInt("matrnr"); ResultSet myrs = statement.executeQuery(select); myrs.next(); int anzahl = myrs.getInt(1); if (anzahl > 6) {throw new SQLException("Zu viele Vorlesungen");}; } Funktion definieren ResultSet getNew() throws SQLException; // Werte nach der Aenderung ResultSet getOld() throws SQLException; // Werte vor der Aenderung String[] getArguments() throws SQLException; String getName() throws SQLException; // Name des Triggers String getTableName() throws SQLException; // Name der Tabelle des Triggers String getSchemaName() throws SQLException; boolean isFiredAfter() throws SQLException; boolean isFiredBefore() throws SQLException; boolean isFiredForEachRow() throws SQLException; boolean isFiredForStatement() throws SQLException; boolean isFiredByDelete() throws SQLException; boolean isFiredByInsert() throws SQLException; boolean isFiredByUpdate() throws SQLException; CREATE OR REPLACE FUNCTION studentenbelastung() RETURNS "trigger" AS 'DBP.belastung' LANGUAGE 'java' VOLATILE; Java Persistence API import javax.persistence.*; @Entity @Table(name = "MESSAGES") public class Message implements Seriliziable { @Id @GeneratedValue @Column(name ="MESSAGE_ID") private Long id; @Column(name ="MESSAGE_TEXT") private String text; @ManyToOne(cascade = CascadeType.ALL) @JoinColumn(name = "NEXT_MESSAGE_ID") private Message nextMessage; public Message() {} @Table(name =““, catalog =““, schema=““, uniqueconstraints=““) @SecondaryTable(name = „“) @Transient //Instanz-Variablen die nicht in DB sollen @Column(name=“ “,length=““, unique=““, nullable““, insertable/updateable) @Enumerated(EnumType.STRING/ORDINAL) @Lob //LargeObject String @Temporal(DATE/TIME/TIMESTAMP) @Version(name= „dbname“) //Optimistischs Locking @Id@GeneratedValue(strategy=GenerationType.AUTO) //Wert von DB @Inheritance(strategy=InheritanceType.SINGLE _TABLE) // Vererbungsart @DiscriminatorColumn(name= „“, discriminatorType=DiscriminatorType.STRING) Java public Message(String text) { this.text = text;} public Long getId() {return id;} private void setId(Long id) {this.id = id;} public String getText() {return text;} public void setText(String text) {this.text = text;} public Message getNextMessage() {return nextMessage;} public void setNextMessage(Message nextMessage) {this.nextMessage = nextMessage;} } Einstellungen finden sich in der persistence.xml @OneToOne(cascade = CascadeType.ALL/MERGE/PERSIST/REFRESH/REMOVE) @OneToMany( cascade = „“ , fetch= FetchType.EAGER/LAZY) @ManyToOne(cascade = „“ ) @ManyToMany (cascade = „“ ) EAGER/LAZY : holt sofort aufch referenzen/holt nur gerade das was andgefordert Vererbung mit SINGLE_TABLE //------------------------Abstraktion--------------------------// @Entity @Inheritance(strategy = InheritanceType.SINGLE_TABLE) @DiscriminatorColumn( name = "BILLING_DETAILS_TYPE", discriminatorType = DiscriminatorType.STRING) public abstract class BillingDetails { @Id @GeneratedValue @Column(name = "BILLING_DETAILS_ID") private Long id = null; @Column(name = "OWNER", nullable = false) private String owner; ... } //-----------------------CreditCard----------------------------// @Entity @DiscriminatorValue("CC") public class CreditCard extends BillingDetails { @Column(name = "CC_NUMBER") private String number; ... } //-----------------------BankAccount------------------------// @Entity @DiscriminatorValue("AA") public class BankAcount extends BillingDetails { @Column(name = "BA_ACCOUNT") private String number; ... } 1. 2. 3. 4. Die Abstrakte Klasse wird mit dem Verfahren benannt. Eine Attribute für die Typunterscheidung definiert Die Subklassen erhalten einen Passenden TypWert Attribute werden mit Column bezeichnet und den entsprechenden DB Namen. Vererbung mit JOINED //------------------------Abstraktion--------------------------// @Entity @Table(name=“billing_details“); @Inheritance(strategy = InheritanceType.JOINED) public abstract class BillingDetails { @Id @GeneratedValue @Column(name = "BILLING_DETAILS_ID") private Long id = null; private String owner; } //-----------------------CreditCard----------------------------// @Entity @PrimaryKeyJoinColumn(name = "CREDIT_CARD_ID") public class CreditCard extends BillingDetails{ ... } //-----------------------BankAccount------------------------// @Entity @PrimaryKeyJoinColumn(name = "Bank_ACCOUNT_ID") public class BankAccount extends BillingDetails{ .... } Entity Manager //Erstelle Entität public void createNewOrder(Order order){ em.getTransaction().begin(); em.persist(order); em.getTransaction().commit(); } //Update Entity public void alterOrderQuantity(long orderId, int newQuantity){ em.getTransaction().begin(); Order order = em.find(Order.class, orderId); order.setQuantity(newQuantity); em.getTransaction().commit(); } persist() stellt Objekt unter Verwaltung Entity Manager refresh() Objekt neu aus DB lesen remove() wird als gelöscht markiert, kann aber noch geholt werden bis commit merge() nicht geführtes Objekt das aber eigentlich (ID) in DB ist erzeugt am bei commit //Lese Entität public Order getOrderById(long orderId){ return em.find(Order.class, orderId); } //Delete public void requestCancelOrder(long orderId){ em.getTransaction().begin(); Order order = em.find(Order.class, orderId); em.remove(order); em.getTransaction().commit(); } flush() Änderungen im Objekt werden in DB geschrieben find() Objekt mit entsprechendem Primärschlüssel wird geholt createQuery(„select“) query.getResultList erhält man Ergebnis als Java Objekte Close() schliesst entityManager Relationen @Entity @Table(name="KUNDEN") public class Kunde implements Serializable { @Id int id; String name; Adresse adresse; 1:1 Unidirektional KUNDEN mit den Spalten ID (PK), NAME, ADRESSE_ID (FK) ADRESSEN mit den Spalten ID (PK), ORT, STRASSE @OneToOne(optional=false) @JoinColumn(name="ADRESSE_ID") public Adresse getAdresse() { return adresse; } ... } @Entity @Table(name="ADRESSEN") public class Adresse implements Serializable { @Id int id; String ort; String strasse; ... } @Entity @Table(name="ADRESSEN") public class Adresse implements Serializable { @Id int id; String ort; String strasse; @OneToOne(mappedBy=“adresse“); //Name von Oben für das andere Kunde kunde; ... } @Entity @Table(name="KUNDE") @SecondaryTable (name="ADRESSE") public class Kunde implements Serializable { @Id private int id; private String name; private String strasse, ort; ... @Column(name="NAME") public String getName() { return name; } @Column(name="STRASSE", secondaryTable="ADRESSE") public String getStrasse() { return strasse; } } 1:1 Bidirektional KUNDEN mit den Spalten ID (PK), NAME, ADRESSE_ID (FK) ADRESSEN mit den Spalten ID (PK), ORT, STRASSE Die Umkehrsichtbarkeit erzeugt man indem man ein entsprechendes Objekt definiert und dieses über den mappedBy parameter auf die bereits oben definierte Beziehung setzt (Variablennamen). Multitable KUNDE mit den Spalten ID (PK), NAME ADRESSE mit den Spalten ID (PK), KUNDE_ID (FK), STRASSE Felder aus anderen Tabellen gibt man über dein Parameter an mit der dazugehörigen Nebentabelle die man Oben definiert. Relationen @Entity @Table(name="KUNDE") public class Kunde implements Serializable { @Id int id; String name; Collection<Adresse> adressen; @OneToMany @JoinTable( name="xyz", joinColumns={@JoinColumn(name="KUNDE_ID")}, inverseJoinColumns ={@JoinColumn(name=" ADRESSE_ID ")}) public Collection<Adresse> getAdressen() { return adressen;} ... } @Entity @Table(name="ADRESSE") public class Adresse implements Serializable { @Id int id; String strasse; ... } @Entity @Table(name="KUNDE") public class Kunde implements Serializable { @Id int id; String name; Collection<Adresse> adressen; @OneToMany(mappedBy=“kunde“); @JoinTable( name="xyz", joinColumns={@JoinColumn(name="KUNDE_ID")}, inverseJoinColumns={@JoinColumn(name="ID")}) public Collection<Adresse> getAdressen() { return adressen;} ... } @Entity @Table(name="ADRESSE") public class Adresse implements Serializable { ... @ManyToOne Kunde kunde; ... } 1:n Unidirektional (implizite/explizit Join-Tabelle) KUNDE mit den Spalten ID (PK), NAME ADRESSE mit den Spalten ID (PK), ORT, STRASSE Join-Tabelle KUNDE_ADRESSE mit den Spalten ADRESSE_ID (PK), KUNDE_ID 1:n Beziehungen werden Über Collections gemacht beim implizieten Verfahren kann man den Roten Teil weglassen. 1:n Bidirektional (implizit/explizit) KUNDE mit den Spalten ID (PK), NAME ADRESSE mit den Spalten ID (PK), ORT, STRASSE Join-Tabelle KUNDE_ADRESSE mit den Spalten ID (PK), KUNDE_ID Im Prinzip dasselbe Prinzip wie vorher. Man gibt unten nur noc hein Umkehrtag an. Relationen @Entity @Table(name="KUNDE") public class Kunde implements Serializable { @Id int id; String name; Collection<Adresse> adressen; @OneToMany(mappedBy=”kunde”); public Collection<Adresse> getAdressen() { return adressen;} ... } @Entity @Table(name="ADRESSE") public class Adresse implements Serializable { ... @ManyToOne @JoinColumn(name=“KUNDE_ID“) Kunde kunde; .......... //Adresse besitzt die Relation } @Entity @Table(name="KUNDE") public class Kunde implements Serializable { @Id int id; String name; ........... Set<Reservierung> reservierungen = new Set<Reservierung>(); @ManyToMany @JoinTable( name="KUNDE_RESERVIERUNG", joinColumns={@JoinColumn(name="K_ID")}, inverseJoinColumns={@JoinColumn(name="RES_ID")}) public Set<Reservierung> getReservierungen() {…} } @Entity public class Reservierung implements Serializable { @ID int id; double betrag; Date when; ........... List<Kunde> kunden = new List<Kunde>(); @ManyToMany(mappedBy=”reservierungen”); public List<Kunde> getKunden() {return kunden;} public void setKunden(List<Kunde> kunden){this.kunden = kunden; } … } 1:n Ohne JOIN-Tabelle KUNDE mit den Spalten ID (PK), NAME ADRESSE mit den Spalten ID (PK), ORT, STRASSE, KUNDE_ID 1:n Beziehungen werden Über Collections gemacht beim implizieten Verfahren kann man den Roten Teil weglassen. n:n Bidirektional (explizit) KUNDE mit den Spalten ID (PK), NAME RESERVIERUNG mit den Spalten ID (PK), BETRAG, DATUM Join-Tabelle KUNDE_RESERVIERUNG mit den K_ID, RES_ID Bei M :n und 1 :n(ähnlich) Beziehungen ist noch darauf zu achten das bei den add/remove Funktionen das Element beidseitig manipuliert wird : public void addKunde(Kunde x) { this.kunden.add(x); x.getReservierungen().add(this); } PL/java StoredProcedure import java.sql.*; public class InsertStudent { public static String updateStudent(int matrnr, String name, int semester) { try { Statement statement; statement = DriverManager.getConnection("jdbc:default:connection").createStatement(); String query = "UPDATE studenten SET name = '" + name + "', semester = " + semester + " WHERE matrnr = " + matrnr + " ;"; int result = statement.executeUpdate(query); return "Student aktualisiert: " + name; } catch (SQLException e){ return "Fehler beim update: " + e.getMessage(); } } } Erzeugen des jar Files auf cmd Jar dem DBMS bekanntgeben/installieren Session File zeigen Aufruf Deklaration jar cvf student.jar InsertStudent.class Select sqlj.install_jar(‚file:///some/directory/Student.jar‘ ‚InsertStudent‘, false) Select sqlj.replace_jar(‚<jar_url>‘, <jar_classname>‚ false) Neustart!!! Select sqlj.set_classpath(‘schema’, ‘logical_jarName/InsertStudent’); Select updateStudent(); CREATE OR REPLACE FUNCTION updateStudent(int, varying character, int) RETURNS varchar AS ‘InsertStudent.updateStudent’ LANGUAGE java DAO RaumInterfaceDAO rDAO = new PostgresRaumDAO(); Raum r = new Raum("KI Labor"); r.setEtage(2); r.setGebaeude("TB"); r.setSitzplaetze(12); rDAO.insertRaum(r); r.setEtage(5); r.setGebaeude("TH"); r.setSitzplaetze(15); rDAO.updateRaum(r); Raum findRaum = rDAO.findRaum(2); System.out.println("Gefundener Raum: " + findRaum.getName() ); LinkedList<Raum> list = (LinkedList<Raum>)(rDAO.findRaumMitPlatz(30)); Iterator<Raum> iterator = list.iterator(); while(iterator.hasNext()) { Raum raum = iterator.next(); System.out.println("Raum"); System.out.println( "RaumNr: " + raum.getRaumnr() + " Name: " + raum.getName()); } public interface RaumInterfaceDAO { public int insertRaum(Raum raum); public boolean deleteRaum(Raum raum); public Raum findRaum(int raumnr); public boolean updateRaum(Raum raum); public Collection<Raum> findRaumMitPlatz(int sitzplaetze); } public class Raum { private int raumnr; private String name; private int sitzplaetze; private int etage; private String gebaeude; //----------------Constructor-----------------------// public Raum(){…} public Raum(String name){…} public Raum(String name, int sitzplaetze, int etage, String gebaeude){...} //--------------------------------------------------// //----------------Properties------------------------// public void setRaumnr(int raumnr) {...} public int getRaumnr() {…} public void setSitzplaetze(int sitzplaetze) {...} public int getSitzplaetze() {...} public void setName(String name) {…} public String getName() {…} public void setEtage(int etage) {…} public int getEtage() {…} public void setGebaeude(String gebaeude) {…} public String getGebaeude() {…} //--------------------------------------------------// } public class PostgresRaumDAO implements RaumInterfaceDAO { public boolean deleteRaum(Raum raum) { try { Connection c = DataBaseConnectionPool.getConnection(); Statement deleteState = c.createStatement(); String sqlString = "DELETE FROM raum WHERE raumnr = '" + raum.getRaumnr() + "'"; int rows = deleteState.executeUpdate(sqlString); DataBaseConnectionPool.closeConnection(c); if(rows < 1) { return false; } return true; } catch(SQLException sqlEx) { return false; } } public Raum findRaum(int raumnr) { try { Connection c = DataBaseConnectionPool.getConnection(); Statement findStatement = c.createStatement(); String sqlString = "SELECT * FROM raum WHERE raumnr = " + raumnr; ResultSet rset = findStatement.executeQuery(sqlString); DataBaseConnectionPool.closeConnection(c); Raum currentRaum = null; if(rset.next()) { Integer currentRaumnr = rset.getInt(1); String currentGebaeude = rset.getString(2); Integer currentSitzplaetze = rset.getInt(3); Integer currentEtage = rset.getInt(4); String currentName = rset.getString(5); currentRaum = new Raum(currentName); currentRaum.setEtage(currentEtage); currentRaum.setGebaeude(currentGebaeude); currentRaum.setSitzplaetze(currentSitzplaetze); currentRaum.setRaumnr(currentRaumnr); return currentRaum; } return null; } catch(SQLException sqlEx) { return null; } } public Collection<Raum> findRaumMitPlatz(int sitzplaetze) { try { Connection c = DataBaseConnectionPool.getConnection(); Statement findStatement = c.createStatement(); String sqlString = "SELECT * FROM raum WHERE sitzplaetze >= " + sitzplaetze ; ResultSet rset = findStatement.executeQuery(sqlString); DataBaseConnectionPool.closeConnection(c); LinkedList<Raum> currentRaume = new LinkedList<Raum>(); while(rset.next()) { Integer currentRaumnr = rset.getInt(1); String currentGebaeude = rset.getString(2); Integer currentSitzplaetze = rset.getInt(3); Integer currentEtage = rset.getInt(4); String currentName = rset.getString(5); Raum currentRaum = new Raum(currentName); currentRaum.setEtage(currentEtage); currentRaum.setGebaeude(currentGebaeude); currentRaum.setSitzplaetze(currentSitzplaetze); currentRaum.setRaumnr(currentRaumnr); currentRaume.add(currentRaum); } return currentRaume; } catch(SQLException sqlEx) { return null; } } public int insertRaum(Raum raum) { try { //-------------------RaumNummer ermitteln--------------------------// Connection c = DataBaseConnectionPool.getConnection(); Statement autoNumberingStatement = c.createStatement(); String lastNumberSQL = "SELECT max(raumnr) FROM raum"; ResultSet rset = autoNumberingStatement.executeQuery(lastNumberSQL); Integer currentRaumnr = 0; rset.next(); currentRaumnr = rset.getInt(1); Integer autoNumber = currentRaumnr + 1; //-----------------------------------------------------------------// //-------------------Insert----------------------------------------// Statement insertStatement = DataBaseConnectionPool.CURRENTCONNECTION .createStatement(); String sqlString = "INSERT INTO raum (raumnr, name, sitzplaetze, gebauede, etage) VALUES (" + autoNumber + ", '" + raum.getName() + "', " + raum.getSitzplaetze() +", '"+ raum.getGebaeude() + "', " + raum.getEtage() + ");"; int rows = insertStatement.executeUpdate(sqlString); DataBaseConnectionPool.closeConnection(c); return rows; //-----------------------------------------------------------------// } catch(SQLException sqlEx) { return 0; } } public boolean updateRaum(Raum raum) { try { Connection c = DataBaseConnectionPool.getConnection(); Statement updateStatement = c.createStatement(); String sqlString = "UPDATE raum SET name = '" + raum.getName() + "', " + "sitzplaetze = " + raum.getSitzplaetze() + ", etage = " + raum.getEtage() + ", gebauede = " + raum.getGebaeude() + " WHERE raumnr = " + raum.getRaumnr(); int rows = updateStatement.executeUpdate(sqlString); DataBaseConnectionPool.closeConnection(c); if(rows == 1) { return true; } return false; } catch(SQLException sqlEx) { return false; } } }