M Laiho JDBC API Java application Java application

M Laiho

Χρήση του JDBC API από Java application για σύνδεση σε βάση δεδομένων Java application JDBC API ORACLE/my. SQL 5

Η διεπαφή JDBC API περιλαμβάνει 4 κύρια μέρη (Core JDBC Components) • • JDBC Drivers Connections Statements Result Sets 7

JDBC Use Cases Υπάρχουν 4 κύρια JDBC σενάρια (use cases): • • Query the database meta data. Update the database. Perform transactions 12

Perform transactions • Transaction. Ομαδοποίηση πολλαπλών δηλώσεων σε μία «δράση» (A transaction groups multiple updates and possibly queries into a single action) που εκτελείται με τη λογική όλα ή τίποτα. 16

Java JDBC: Interaction of the core JDBC components during the execution of a database query (select). 17

Άνοιγμα (Opening the) Connection • Για να ανοίξετε μια database connection χρησιμοποιείτε την κλάση java. sql. Driver. Manager. Καλείτε τη μέθοδό της (method) get. Connection(): String test. Database = “jdbc: mysql: //localhost: 3306/mydb? user=admin&password=1234"; Connection my. Connection = Driver. Manager. get. Connection(test. Database); 19

Update Rows Statement statement = connection. create. Statement(); String sql="update people set sal=sal*1. 1"; int rows. Affected=statement. execute. Update(sql); • Η rows. Affected που επιστρέφεται από την κλήση της statement. execute. Update(sql) «λέει» πόσες γραμμές στη βάση άλλαξαν από την SQL statement. 24

Delete Rows Statement statement = connection. create. Statement(); String sql= "delete from employee where sal>2000"; int rows. Affected= statement. execute. Update(sql); • Η rows. Affected που επιστρέφεται από την κλήση της statement. execute. Update(sql) «λέει» πόσες γραμμές διαγράφηκαν από την SQL statement. 25

M Laiho

Βασική αρχή Η βασική αρχή χρήσης του API είναι απλή: Statement my. Statement = myconnection. create. Statement(); Τα αντικείμενα της βάσης δεδομένων ορίζονται σαν συμβολοσειρές (string). Ακολουθούν παραδείγματα: String test. Database = “jdbc: mysql: //localhost: 3306/mydb? user= root &password=c 125"; Προσοχή! Η παρακάτω εντολή είναι λανθασμένη String sql. String = "INSERT INTO USERS VALUES('user 1', 'password 1')"; Η σωστή διαμορφώνεται ως εξής: … VALUES('"+user 1+"', '"+password 1+"') String sql= "insert into users values ('"+user 1+"', '"+password 1+"')";

Απλά παραδείγματα χρήσης του API: Η Driver. Manager Class παρέχει τη μέθοδο get. Connection. Μπορούμε να ορίσουμε ένα αντικείμενο, για παράδειγμα το αντικείμενο my. Connection, τύπου Connection ως εξής: Connection my. Connection = Driver. Manager. get. Connection(test. Database); Αυτό προϋποθέτει ότι έχουμε ορίσει ένα αντικείμενο test. Database τύπου string που «ορίζει» τον driver που χρησιμοποιούμε, το URL, τη βάση μας, username και password. String test. Database = “jdbc: mysql: //localhost: 3306/mydb? user=admin&password=1234"; Connection my. Connection = Driver. Manager. get. Connection(test. Database); Αν δεν υπάρχει password “jdbc: mysql: //localhost: 3306/mydb? user=admin";

4. Σύνδεση στη βάση δεδομένων String test. Database = “jdbc: mysql: //localhost: 3306/mydb? user=root&password=c 125"; Ορίζουμε για τη βάση δεδομένων μας (mydb) το αντικείμενο test. Database τύπου συμβολοσειράς (string) της μορφής: “jdbc: mysql: //Server. Name: Server. Port/Database. Name? user=my. U sername&password=my. Password"; Connection my. Connection = Driver. Manager. get. Connection(test. Database); Δημιουργούμε ένα αντικείμενο τύπου Connection. Tο αντικείμενό μας, my. Connection, είναι μια ανοιχτή σύνδεση με τη βάση δεδομένων

Κάποια παραδείγματα διαχείρισης δηλώσεων SQL Για να γράψουμε SQL χρησιμοποιούμε (διορθώστε τα διαγραμμένα) my. Statement. method. Name(sql. String); insert statement my. Statement. execute. Update(sql. String); όπου sql. String είναι μια συμβολοσειρά της μορφής: String sql. String = "INSERT INTO MYTABLE VALUES ('value 1', 'value 2')"; delete statement my. Statement. execute. Update(sql. String); όπου το sql. String είναι της μορφής: String sql. String = "DELETE FROM MYTABLE WHERE PARAM = 'param. Value'"; update statement my. Statement. execute. Update(sql. String); -- όπου το sql. String είναι: String sql. String = "UPDATE MYTABLE SET PARAM 1='value 1' WHERE PARAM 2='value 2'";

43

45

46

47

48

49

Mythical Car JSP pages 50

Project name 51

menu. jsp 53

54

55

sales_index. jsp 56

57

58

sales. Insert. jsp 59

60

61

select. Sales. jsp 62

63

64

65

66

Add my. SQL driver 67

User Interface 68

71

DROP DATABASE personnel; CREATE DATABASE personnel; USE proj; CREATE TABLE Dept(DEPTNO INT(2) NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(14), NO_OF_EMPLOYEES INT(3), PRIMARY KEY(DEPTNO)); CREATE TABLE Emp(EMPNO INT(4) NOT NULL AUTO_INCREMENT, ENAME VARCHAR(10), JOB VARCHAR(9), SAL FLOAT(7, 2), DEPTNO INT(2), PRIMARY KEY(EMPNO), FOREIGN KEY(DEPTNO) REFERENCES Dept(DEPTNO));

INSERT INTO Dept(DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO Dept(DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO Dept(DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO Dept(DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');

INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'SMITH', 'CLERK', 800, 20); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'ALLEN', 'SALESMAN', 1600, 30); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'WARD', 'SALESMAN', 1250, 30); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'JONES', 'MANAGER', 2975, 20); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'MARTIN', 'SALESMAN', 1250, 30); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'BLAKE', 'MANAGER', 2850, 30); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'CLARK', 'MANAGER', 2450, 10); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'SCOTT', 'ANALYST', 3000, 20); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'KING', 'PRESIDENT', 5000, 10); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'TURNER', 'SALESMAN', 1500, 30); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'ADAMS', 'CLERK', 1100, 20); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'JAMES', 'CLERK', 950, 30); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'FORD', 'ANALYST', 3000, 20); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'MILLER', 'CLERK', 1300, 10); INSERT INTO Emp( ENAME, JOB, SAL, DEPTNO) VALUES ( 'BATES', 'ANALYST', 1300, NULL);

UPDATE dept SET no_of_employees = (SELECT COUNT(*) FROM emp WHERE emp. deptno = deptno); Select * from dept; Select * from emp; Στη συνέχεια θα γίνει διαχείριση των τιμών της στήλης no_of_employees με triggers

CREATE TABLE user( uname text, upass text, Uid int(11), Uphone varchar(45), Ucity varchar(45)); INSERT INTO `user` VALUES ('admin', '1234', 1, NULL);

DROP TRIGGER emp_insert; DROP TRIGGER emp_delete; DROP TRIGGER emp_update; DELIMITER // CREATE TRIGGER emp_insert AFTER INSERT ON emp FOR EACH ROW BEGIN UPDATE dept SET no_of_employees = IFNULL(no_of_employees, 0) + 1 WHERE deptno = NEW. deptno; END // DELIMITER ;

DELIMITER // CREATE TRIGGER emp_delete AFTER DELETE ON emp FOR EACH ROW BEGIN UPDATE dept SET no_of_employees = IFNULL(no_of_employees, 0) - 1 WHERE deptno = OLD. deptno; END // DELIMITER ;

DELIMITER // CREATE TRIGGER emp_update AFTER UPDATE ON emp FOR EACH ROW BEGIN UPDATE dept SET no_of_employees = IFNULL(no_of_employees, 0) + 1 WHERE deptno = NEW. deptno; UPDATE dept SET no_of_employees = IFNULL(no_of_employees, 0) - 1 WHERE deptno = OLD. deptno; END // DELIMITER ;

Ακολουθούν δοκιμές / έλεγχοι INSERT INSERT SELECT INTO dept VALUES(11, 'Belle Epoque', 'PARIS', 0); INTO dept VALUES(12, 'Rasors edge', 'PARIS', 0); INTO emp(empno, ename, deptno) VALUES (102, 'Luers', 11); INTO emp(empno, ename, deptno) VALUES (103, 'Atwood', 11); INTO emp(empno, ename, deptno) VALUES (104, 'Gennick', 12); * FROM dept WHERE deptno IN (11, 12); DELETE FROM emp WHERE empno = 103; SELECT * FROM dept WHERE deptno IN (11, 12); UPDATE emp SET deptno = 11 WHERE empno = 104; SELECT * FROM dept WHERE deptno IN (11, 12); SELECT * FROM dept;

Δημιουργία procedure DELIMITER $$ DROP PROCEDURE IF EXISTS Cursor. Proc$$ CREATE PROCEDURE Cursor. Proc() BEGIN DECLARE no_more_depts, available_employees INT DEFAULT 0; DECLARE dept_code VARCHAR(255); DECLARE cur_dept CURSOR FOR SELECT deptno FROM dept; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_depts = 1; /* for logging information */ OPEN cur_dept; FETCH cur_dept INTO dept_code; REPEAT SELECT no_of_employees INTO available_employees FROM dept WHERE deptno = dept_code; IF available_employees < 5 THEN INSERT INTO infologs(msg)VALUES (dept_code); END IF; FETCH cur_dept INTO dept_code; UNTIL no_more_depts = 1 END REPEAT; CLOSE cur_dept; SELECT * FROM infologs; END$$

DELIMITER ; CALL CURSORPROC();

Σύνδεση Τα στοιχεία της σύνδεσης είναι: • IP: localhost • Port: 3306 • Database: personnel • User=root • Password=1234

Index. jsp

Κώδικας της index. jsp <%@page content. Type="text/html" page. Encoding="UTF-8"%> <!DOCTYPE html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>JSP Page</title> </head> <body> WELCOME<p></p> LOGIN<p></p> <form name="form. Name" method="post" action="check. jsp" > Name: <input type="text" name="y"><p></p> Password: <input type="text" name="k"><P></P> <input type="submit" value="LOGIN"><p></p> </form> </body> </html>

Index. jsp <form name="form. Name" method="post" action="check. jsp" > Name: <input type="text" name="y"><p></p> Password: <input type="text" name="k"><P></P> <input type="submit" value="LOGIN"><p></p> </form> check. jsp String x =request. get. Parameter("y"); String p = request. get. Parameter("k");

<%@page import="java. sql. *" %> <%@page content. Type="text/html" page. Encoding="UTF-8"%> <!DOCTYPE html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>JSP Page</title> </head> <body> <% Boolean found; int j=0; String s=""; int ps; String x =request. get. Parameter("y"); String p = request. get. Parameter("k"); String URL; Class. for. Name("com. mysql. jdbc. Driver"); String DB = "jdbc: mysql: //localhost: 3306/personnel? user=root&password=1234"; Connection my. Connection = Driver. Manager. get. Connection(DB); Statement SMT = my. Connection. create. Statement(); String sql="SELECT * FROM user WHERE Uname='"+x+"'AND Upass='"+p+"' "; Result. Set rs=SMT. execute. Query(sql); found= rs. first(); check. jsp

Check. jsp if (found){ URL = "home. jsp? p 1="+x+""; response. send. Redirect(URL); } else { out. println("INCORRECT TRY AGAIN"); %><P></P> <a href="index. jsp">Try Again</a><% } SMT. close(); my. Connection. close(); %> </body> </html>

Result. Set - απόσπασμα της home. jsp <% String sql="SELECT * FROM dept "; Result. Set rs=SMT. execute. Query(sql); String dname; int count=0; while (rs. next()) { i=rs. get. Int("deptno"); String sql 1="SELECT * FROM emp WHERE deptno='"+i+"' "; Result. Set rs 1=SMT 1. execute. Query(sql 1); dname=rs. get. String("dname"); %>