Databaskopplingar och SQL Genomgng av SQL JDBC My

Databaskopplingar och SQL Genomgång av: SQL JDBC My. SQL på DSV Om föreläsningen Är praktiskt inriktad Försöker inte täcka in allt © Michail Årman

SQL – exempeldatabas Kund(Pnr, Namn) Kundorder(Ordernr, Kundnr, Summa, Datum) Orderrad(Artikelnr, Ordernr, Antal) Produkt(Artikelnr, Pris, Bild) Kundorder. Kundnr << Kund. Pnr Orderrad. Ordernr << Kundorder. Ordernr Orderrad. Ordernr << Produkt. Artikelnr © Michail Årman

SQL – intro SQL har funktioner för att hantera: Databeskrivning SQL-DDL (Data Definition Language) Databearbetning SQL-DML (Data Manipulation Language) Databehörighet SQL-DCL (Data Control Language) Vi kommer fokusera på SQL-DML © Michail Årman

SQL – intro SELECT – specificerar kolumner WHERE – specificerar rader FROM – vilka tabeller som ska användas © Michail Årman

SQL – join Användning av flera tabeller i SQL görs genom att man matchar primärnyckel med främmande nyckel SELECT Namn, Ordernr FROM Kund AS k, Kundorder AS ko WHERE k. Pnr=ko. Kundnr #JOIN © Michail Årman

SQL – aggregatfunktioner COUNT(*) – räknar antalet rader i en tabell COUNT(kolumnnamn) – räknar antalet värden i en kolumn SUM(kolumnnamn) – summerar värdena i en kolumn AVG(kolumnnamn) – genomsnittet av värdena i en kolumn MAX(kolumnnamn) – största värdet i en kolumn MIN(kolumnnamn) – minsta värdet i en kolumn © Michail Årman

SQL – aggregatfunktioner Räkna antalet order per kund SELECT Namn, COUNT(Ordernr) AS Antal_order FROM Kund AS k, Kundorder AS kor WHERE k. Pnr = kor. Kundnr GROUP BY Namn; © Michail Årman

SQL – aggregatfunktioner Räkna ut den totala ordersumman för en order med ordernummret 1. SELECT SUM(Pris * Antal) AS Totalt FROM Produkt AS p, Orderrad AS orad WHERE Ordernr=1 AND orad. Artikelnr=p. Artikelnr; © Michail Årman

SQL – textsträngsmatchning SQL har två mönstermatchningssymboler: % - Representerar 0–n tecken (wildcard) _ - Representerar ett tecken När strängar jämförs används inte de vanliga operatorerna såsom =, < m. m. Man använder LIKE och NOT LIKE istället © Michail Årman

SQL – textsträngsmatchning Lista kunder vars namn börjar på ’a’ SELECT Namn FROM Kund WHERE Namn LIKE ’a%’; Lista alla kunder vars namn är minst fyra tecken långt samt inte slutar på ’l’ SELECT Namn FROM Kund WHERE Namn LIKE ’____%’ AND Namn NOT LIKE ’%l’; © Michail Årman

SQL - INSERT Lägga in värden i en databas är mycket vanligt förekommande även i de enklaste av databasapplikationer. Syntaxen är följande: INSERT INTO Tabellnamn(lista_på_kolumner) VALUES (lista_på_värden); Lägger in en ny produkt i databasen INSERT INTO Produkt(Artikelnr, Pris, Bild) VALUES (104, 400, NULL); © Michail Årman

SQL - UPDATE Syntaxen för uppdatering av tabeller: UPDATE Tabellnamn SET Kolumnnamn=värde [WHERE sökkriteria] #Frivilligt © Michail Årman

SQL - UPDATE Höja priserna med 5% UPDATE Produkt SET Pris=Pris*1. 05; Höja priserna med 5% för de produkter som kostar under 200 kronor UPDATE Produkt SET Pris=Pris*1. 05; WHERE Pris < 200; © Michail Årman

SQL - DELETE Radering av rader görs på följande sätt: DELETE FROM Tabellnamn [WHERE sökkriteria] #Frivilligt Ta bort alla produkter som kostar under 70 kronor: DELETE FROM Produkt WHERE Pris < 70; © Michail Årman

SQL – sortera resultat För att sortera en resultattabell använder man sig av ORDER BY Raderna kan sorteras stigande (ASC) eller fallande (DESC) Man kan sortera på mer än en kolumn. Kolumnnamnen separeras då med komma. Exempel: SELECT Namn, Summa, Ordernr FROM Kund AS k, Kundorder AS kor WHERE k. Pnr = kor. Kundnr ORDER BY Namn, Summa DESC; © Michail Årman

JDBC – intro Vi kommer att gå igenom: Lite kort om JDBC-drivers Uppkoppling mot databaser Exekvering av SQL-satser Statement Prepared Statement Hantering av resultat Transaktioner © Michail Årman

JDBC – intro JDBC är ett programmeringsgränssnitt mot vilket programmerare kan skapa en uppkoppling mot databasen, skicka sin SQLfråga, och ta emot svar i ett slags tabellform. JDBC hanterar den faktiska uppkopplingen, skickandet av frågan och data till och från databasen. För att JDBC ska kunna prata med enskilda databaser så måste det finnas en ”driver”. © Michail Årman

JDBC – drivers Typ 1 – Det är en JDBC-ODBC-brygga. Smidigt men långsamt och ingenting för stora system Typ 2 – Konverterar JDBC-anropen till anrop till klientmaskinens API för en specifik DBHS. Snabbare än typ-1, men måste bl. a. ha kompilerad kod på varje operativsystem som applikationen ska köras på. © Michail Årman

JDBC – drivers © Michail Årman

JDBC – drivers Typ 3 – En ren Java-driver, skickar anropen till en middleware-server som konverterar de till databasspecifika anrop. Typ 4 – Också en ren Java-driver, med direktkoppling mot databasen. Tillåter direkta anrop från klienten till databasen. Snabb men kan inte utnyttja operativsystemspecifika funktioner. Skriva en egen. Om man behöver mer funktionalitet än vad ovan angivna drivers ger möjlighet till, så kan man skriva en egen JDBC-driver. © Michail Årman

JDBC – drivers © Michail Årman

JDBC – drivers Var kan jag hitta drivers till min databas? A På följande sida har Sun listat olika drivers (både kommersiella och icke-kommersiella): Q http: //servlet. java. sun. com/products/jdbc/drivers © Michail Årman

JDBC – uppkoppling Hur kopplar man upp sig mot en databas? A Kräver två saker: i. Ladda en driver ii. Etablera uppkoppling Q © Michail Årman

JDBC – uppkoppling i. Ladda driver try { //Laddar Suns JDBC-ODBC brygga Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); } catch (java. lang. Class. Not. Found. Exception) { } Q A Vad betyder detta? Class. for. Name() är en statisk metod som initierar angiven klass. Drivern laddas in i minnet och registrerar sig hos Driver. Manager genom att anropa Driver. Manager. register. Driver(Driver) © Michail Årman

JDBC – uppkoppling Q A Hur kan detta se ut om man använder DSVs My. SQLserver? Det beror på vilken driver du använder: i. Från Pierres hemsida (MM. My. SQL 2. 0. 14): Class. for. Name("org. gjt. mm. mysql. Driver"); ii. Från My. SQLs hemsida (My. SQL Connector/J 3. 0): Class. for. Name("com. mysql. jdbc. Driver"); © Michail Årman

JDBC – uppkoppling ii. Etablera en uppkoppling String db. Url = ”jdbc: odbc: dbhost”; String username; String password; Connection con; . . . try { con = Driver. Manager. get. Connection(db. Url, username, password); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC – uppkoppling Q Hur kan detta se ut om man använder DSVs My. SQLserver? String db. Host = "atlas. dsv. su. se"; String db. Name = "db_03_username"; String db. Url = "jdbc: mysql: //" + db. Host + "/" + db. Name; String username = "db_03_username"; String password = "123456"; Connection con; . . . try { con = Driver. Manager. get. Connection(db. Url, username, password); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC – uppkoppling Man stänger en uppkoppling genom att anropa close() När man stänger en uppkoppling frigörs databas- och javaresurser. Att tänka på: Tillhörande Statement och deras Result. Set stängs Det som tar tid är att skapa uppkopplingar, varför återanvändning av databaskopplingar kan vara smart. © Michail Årman

JDBC – SQL-satser Q A Hur exekverar man SQL-satser i databasen? Man kan använda sig av tre olika klasser: i. Exekvera godtycklig SQL-kod java. sql. Statement ii. Exekvera förkompilerad SQL-kod java. sql. Prepared. Statement iii. Anropa lagrade procedurer (avancerat) java. sql. Callable. Statement © Michail Årman

JDBC - Statement Ett Statement-objekt sänder din SQL-sats till databasen. Skapas på följande sätt: Connection con; . . . try { Statement stmt = con. create. Statement(); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Statement När man är klar är det en bra vana att släppa databas - och JDBC-resurserna så fort som möjligt. Connection con; . . . try { Statement stmt = con. create. Statement(); . . . stmt. close(); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Statement Hur man exekverar SQL-satsen beror på huruvida man förväntar sig ett svar eller ej. i. För SELECT-satser görs följande: String query = ”SELECT Namn FROM Kund”; Statement stmt; . . . try { Result. Set result = stmt. execute. Query(query); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Statement ii. För UPDATE, INSERT eller DELETE där inget svar förväntas görs följande: String sql = ”DELETE FROM Kund WHERE Namn LIKE ’Adam’”; Statement stmt; . . . try { int rows. Modified = stmt. execute. Update(sql); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Statement iii. Om du inte vet vad det är för SQL-sats du skickar till databasen: String sql; Statement stmt; . . . try { if (stmt. execute(sql)) { Result. Set result = stmt. get. Result. Set(); } else { int rows. Modified = stmt. get. Update. Count(); } } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Result. Set Resultatet på en SQL-fråga returneras i ett Result. Set -objekt. Man kan se objektet som en tabell som man läser en rad i taget, med början på rad 0. Använd metoden next() för att byta till nästa rad. Ett Result. Set kan endast användas om dess Statement-objekt är öppet. © Michail Årman

JDBC - Result. Set Det finns massor av metoder för att hämta data från aktuell rad. De vanligaste är get. String() och get. Object() Alla get. XXX()-metoderna fungerar med både kolumnindex eller kolumnnamn som inparameter. Andra vanliga metoder: Datatyp SQL Datatyp Java Metod DATE java. sql. Date get. Date() INTEGER Integer get. Int() TIMESTAMP java. sql. Timestamp get. Timestamp() VARCHAR String get. String() BLOB java. sql. Blob get. Blob() CLOB java. sql. Clob get. Clob() © Michail Årman

JDBC - Result. Set Exempel String query = ”SELECT Ordernr, Datum, Kundnr FROM Kundorder”; Statement stmt; . . . try { Result. Set result = stmt. execute. Query(query); while (result. next()) { System. out. print(result. get. Int(1) + ”t”); System. out. print(result. get. Timestamp(”Datum”) + ”t”); System. out. println(result. get. Int(”Kundnr”)); } stmt. close(); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Prepared. Statement För att snabba upp exekvering av SQL-satser kan databaser förkompilera SQL-satsen. Bra att använda i applikationer som kör samma SQLkommando många gånger. Även när SQL-satsen är kompilerad kan man förändra fördefinierade parametrar. © Michail Årman

JDBC - Prepared. Statement Skapas på liknande sätt som Statement, med skillnaden att SQL-satsen anges vid skapandet istället för vid exekveringen. String sql; Connection con; . . . try { Prepared. Statement pstmt = con. prepare. Statement(sql); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Prepared. Statement Hur anger man värden i SQL-satsen? A Det görs genom att man använder sig av ? -tecknet för att indikera var värdena ska vara. Q String sql = ”INSERT INTO Kund (Pnr, Namn) VALUES (? , ? )”; Prepared. Statement pstmt; . . . try { pstmt. clear. Parameters(); //Tar bort gamla värden pstmt. set. Int(1, 770101); //Sätter Pnr pstmt. set. String(2, ”Kalle”); //Sätter Namn pstmt. execute. Update(); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Prepared. Statement Ett annat exempel: String sql = ”SELECT Namn FROM Kund WHERE Pnr = ? ”; Prepared. Statement pstmt; . . . try { pstmt. clear. Parameters(); pstmt. set. Int(1, 770101); Result. Set result = pstmt. execute. Query(); } catch (java. sql. SQLException e) { } © Michail Årman

JDBC - Prepared. Statement Q Finns det några problem med att återanvända Prepared. Statement? A Ja, i multitrådade program uppstår problem, t. ex i Servlets. Vad händer om en tråd anropar clear. Parameters() precis innan en annan anropar execute()? Endast ett Result. Set i taget kan vara öppet per Prepared. Statement. © Michail Årman

JDBC – Transaktioner Som standard hanteras varje enskild SQL-sats som en transaktion i Java. Det är däremot möjligt att gruppera flera SQL-satser i en transaktion. När ska man använda sig av detta? A När det är viktigt att alla SQL-satser utförs samtidigt, alternativt inte utförs alls om fel uppstår. T. ex. någon avancerad banktransaktion Q © Michail Årman

JDBC – Transaktioner Exempel: Connection con; . . . try { con. set. Auto. Commit(false); //Slå på transaktioner Statement stmt = con. create. Statement(); stmt. execute. Update(”INSERT INTO Kundorder (Ordernr, Summa, Datum, Kundnr) VALUES (5, 20040101, 2)”); stmt. execute. Update (”INSERT INTO Orderrad (Artikelnr, Ordernr, Antal) VALUES (101, 5, 2)”); con. commit(); } © Michail Årman

JDBC – Transaktioner Fortsättning… catch (Exception e) { try { con. rollback(); //Rollback om något fel har uppstått } catch (java. sql. SQLException ignored_e) {} } finally { //Detta behöver inte vara nödvändigt con. set. Auto. Commit(true); //Slå av transaktioner } © Michail Årman

My. SQL på DSV Hur gör jag för att skapa ett My. SQL-konto på DSV? A Gör enligt följande: Gå in på denna sida: Q http: //atlas. dsv. su. se/~pierre/mysql/ Skriv in din e-postadress. Måste sluta på dsv. su. se eller kth. se Databasnamn, användarnamn och lösenord sänds via e-post © Michail Årman

Verktyg för My. SQL Control Center Url: http: //www. mysql. com/products/mysqlcc/ DBDesigner 4 Lite buggigt! Url: http: //www. fabforce. net/dbdesigner 4/index. php © Michail Årman