Db1 Cheatsheet
ADVERTISEMENT
1CREATE TABLE BankAccount(
private Collection<BankAccount>
DB1 CheatSheet #HSR HS 2013
JDBC by Example: SQL-Queries mit Java
AccountId SERIAL NOT NULL PRIMARY KEY,
accounts = new ArrayList<>();
Emanuel Duss, Marcel Loop
Start Connection Try-Block
Account_CustomerId INTEGER NOT NULL,
Aggregation / Komposition
Balance DOUBLE PRECISION NOT NULL,
SQL
final String u = "username";
→ Aggregation (Leere Raute)
Currency TEXT NOT NULL DEFAULT 'CHF');
final String p = "password";
DDL: Data Definition Language
CREATE TABLE BankManager(
@OneToMany(cascade =
final String d =
ManagerId SERIAL NOT NULL PRIMARY KEY,
CascadeType.PERSIST, ...)
"jdbc:postgresql://localhost/db_name";
Create Table mit Column Constraints
Name TEXT NOT NULL,
private Collection<BankAccount>
try(Connection c =
CREATE TABLE Projekt(
Manager_AddressId INTEGER);
accounts = new ArrayList<>();
DriverManager.getConnection(d,u,p)){
ProjNr INTEGER PRIMARY KEY,
CREATE TABLE CustomerManager(
→ Komposition (Ausgefüllte Raute)
c.setAutoCommit(false);
Bezeichnung VARCHAR(20) NOT NULL UNIQUE,
CustomerId INTEGER NOT NULL,
c.setTransactionIsolation
@OneToMany(cascade =
Start DATE NOT NULL DEFAULT CURRENT_DATE,
ManagerId INTEGER NOT NULL,
{CascadeType.PERSIST,
(Connection.TRANSACTION_SERIALIZABLE);
Dauer INTEGER NULL
PRIMARY KEY(CustomerId, ManagerId));
CascadeType.REMOVE }, ...)
Plain SQL: Normales Statement (SQLi Gefahr!)
CHECK(DAUER BETWEEN 10 AND 100),
private Collection<BankAccount>
Java-Klassen (keine Klasse CustomerManager)
AbtNr INTEGER NOT NULL REFERENCES Abt);
try(Statement s = conn.createStatement()){
accounts = new ArrayList<>();
Tabellen und Attribute Mappen
ResultSet rs1 = stmt.executeQuery
Table statt Column Constraints:
Vererbung in JPA (RetailB.M extends B.M)
("SELECT CURRENT_USER");
@Entity
CREATE TABLE Projekt(... --- vgl. oben
while (rs1.next())
Single Table (Tabelle für Oberklasse)
@Table(name = "bankcustomer")
PRIMARY KEY (ProjNr),
System.out.println(rs1.getString(1));
public class BankCustomer {
→ BankCustomer.java
FOREIGN KEY (AbtNr) REFERENCES Abt);
rs1.close(); }
@Id
@Entity
Constraint mit Name
Prepared Statement (SQLi Schutz)
@Column (name = "customerid")
@Inheritance(strategy =
ALTER TABLE Projekt
private Integer id;
InheritanceType.SINGLE_TABLE)
final String query = "SELECT *
private String name; // Auto Mapping
ADD CONSTRAINT fk_ProjAng
@DiscriminatorColumn(name = "type")
FROM customers WHERE name LIKE ?"
@Temporal(TemporalType.TIMESTAMP)
FOREIGN KEY (ProjLeiter) REFERENCES
public abstract class BankCustomer { ...
try (PreparedStatement ps =
Angestellter(PersNr);
private Date birthdate;
→ RetailBankCustomer.java (erbt von BankCustomer)
c.prepareStatement(query)){
@Transient // Nicht Mappen
Referenzielle Integrität
@Entity
ps.setString(1, readInput());
private Integer foo;
@DiscriminatorValue("Retail")
ResultSet rs2 = ps.executeQuery();
CREATE TABLE foo( ...
Beziehung: ManyToOne (B.Account – B.Customer)
public class RetailBankCustomer extends ..
while (rs.next())
ON DELETE [CASCADE | RESTRICT | SET NULL |
String n = rs2.getString("NAME");
→ BankAccount.java
SET DEFAULT]);
Joined Table (Tabelle pro Klasse: Jede Klasse)
float g = rs2.getFloat("GEHALT");
@ManyTo One(optional = false)
Tabelle löschen
→ BankManager.java
System.out.println(n + ": CHF " + g);
@JoinColumn(name = "account_customerid")
@Entity
DROP TABLE foo [CASCADE]
}}
private BankCustomer bankcustomer;
@Inheritance(strategy =
DML: Data Manipulation Language
Ende Connection Try-Block
→ BankCustomer.java
InheritanceType.JOINED)
@OneToMany
INSERT INTO Abteilung (Name, AbtrNr)
@DiscriminatorColumn(name = "type")
} catch (SQLException e) {
@JoinColumn(name = "account_customerid",
VALUES ('Entwicklung', 20);
Connection.rollback();
public abstract class BankCustomer { ...
referencedColumnName = "customerid")
System.err.println(e.getMessage()); }
→ RetailBankCustomer.java
DQL: Data Query Language
private Collection<BankAccount>
@Entity
OR Mapping mit JPA
accounts = new ArrayList<>();
WHERE, ORDER BY, LIKE
@DiscriminatorValue("Retail")
Beziehung: OneToOne (Address – B.Customer)
Domain Model
public class RetailBankCustomer extends ..
SELECT Name, Wohnort FROM Angestellter
→ Address.java
WHERE AbtNr = 1
Table per Class (Tabelle pro Subklasse)
AND (Salaer > 500)
@OneToOne(mappedBy = "address") // Member
→ BankManager.java
OR WOHNORT IN ('Luzern', 'Zug') -- = any
private BankManager bankmanager;
@Entity
OR FOO LIKE 'Zu% OR FOO LIKE 'L____n'
→ BankManager.java
@Inheritance(strategy =
ORDER BY Name;
@OneToOne(optional = true)
InheritanceType.TABLE_PER_CLASS)
Window Function
@JoinColumn(name = "manager_addressid")
public abstract class BankCustomer { ...
private Address address;
SELECT DISTINCT abtnr, SUM(salaer)
→ RetailBankCustomer.java
Beziehung: ManyToMany (B.Customer – B.Manager)
OVER (PARTITION BY abtnr)
@Entity
FROM angestellter ORDER BY 1;
public class RetailBankCustomer
→ BankManager.java (Attribut managerid zuerst!)
extends BankCustomer { ...
Common Table Expressions (CTE)
@ManyToMany
@JoinTable(name = "customermanager",
JPQL (Abfragen auf Entities statt DB Modell)
WITH tmp_tbl AS (SELECT * FROM tabelle
joinColumns =
WHERE number = 23),
Query q =
{@JoinColumn(name = "managerid")},
SELECT name, foo from tmp_tmp_tbl;
db_schema.sql
em.createQuery("select c from
inverseJoinColumns =
Weitere Keywords
BankCustomer c join c.accounts a");
CREATE TABLE BankCustomer(
{@JoinColumn(name = "customerid")})
Diverses
CustomerId SERIAL NOT NULL PRIMARY KEY,
DISTINCT: Duplikate unterdrücken
private Collection<BankCustomer>
Name TEXT NOT NULL, Birthdate DATE,
customers = new ArrayList<>();
Transaktionen
Last Change
Customer_AddressId INTEGER);
→ BankCustomer.java
CREATE TABLE Address(
→ Bei Repeatable Read wird nichts geschrieben, was
2013-01-17
@OneToMany
AddressId SERIAL NOT NULL PRIMARY KEY,
auf andere Transaktionen Einfluss hat.
@JoinColumn(name = "account_customerid",
Street TEXT NOT NULL, Zip INTEGER,
→
referencedColumnName = "customerid")
City TEXT NOT NULL);
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education