JDBC API Java application Java application JDBC API

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

M Laiho

Απλά παραδείγματα χρήσης του 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);

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'";

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 = NVL(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; SELECT * FROM emp;

Δημιουργία 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

<%@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

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>

<% 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"); %> (απόσπασμα της home. jsp)

<% Class. for. Name("com. mysql. jdbc. Driver"); String DB = "jdbc: mysql: //localhost: 3306/personnel? user=root"; Connection my. Connection = Driver. Manager. get. Connection(DB); Statement SMT = my. Connection. create. Statement(); String name=request. get. Parameter("ename"); String job=request. get. Parameter("job"); String sal=request. get. Parameter("sal"); int deptno=Integer. parse. Int(request. get. Parameter("deptno")); String sql="insert into emp(ename, job, sal, deptno) values ('"+name+"', '"+job+"', '"+sal+"', "+deptno+")"; SMT. execute. Update(sql); String URL = "index. jsp"; response. send. Redirect(URL); %> <% SMT. close(); my. Connection. close(); %> (απόσπασμα της insubmit. jsp)

Παράδειγμα JSP Session • Μία συνεδρία είναι ένα αντικείμενο συνδεδεμένο με έναν επισκέπτη του ιστοτόπου μας <HTML> <BODY> <FORM METHOD=POST ACTION="Save. User. Name. jsp"> What's your name? <INPUT TYPE=TEXT NAME=username SIZE=20> What's your age? <INPUT TYPE=TEXT NAME=userage SIZE=20> <P><INPUT TYPE="SUBMIT" value="Click"><p></p> </FORM> </BODY> </HTML>

Save. User. Name. jsp <% String name = request. get. Parameter("username"); String age = request. get. Parameter("userage"); session. set. Attribute( "the. Name", name ); session. set. Attribute( "the. Age", age ); %> <HTML> <BODY> <A HREF="Welcome. Page. jsp">Continue</A> </BODY> </HTML>

Welcome. Page. jsp <HTML> <BODY> Hello, <%= session. get. Attribute( "the. Name" ) %> Your age is <%= session. get. Attribute("the. Age")%> </BODY> </HTML>

Panorama. Summer. Soccer. com camp Registration is now OPEN for Panorama Summer Soccer program in Panorama. Summer. Soccer. com camp. Date: Play will begin June 14 2014 Στην οθόνη του login θα υπάρχει το εξής κείμενο: Time: Start times range from 9: 00 am – 12: 00 on Monday and Wednesday mornings. Possible sessions on 9: 00 am – 10: 00 Saturday Mornings (as per demand/interest min 20 players registered) Season: More than 12 sessions with small games June 14 2014 to July 27 2014. Programs: LEARN 2 PLAY, SOCCER PLUS, LEARN BY EXAMPLE Address: Sounio (Thisseos’ fields) Players will be split into the following groups: 3 -5 years old (mini STARS) 11 -12 AM 6 -8 years old (RISING STARS) 10 -11 AM 9 -12 years old (SHINING STARS) 9 -10 AM PLEASE NOTE WE HAVE VERY LIMITED SPACES, so register early… ALL participants will receive uniforms…

Οι πίνακες της εφαρμογής θα μπορούσαν να συμπεριλαμβάνουν και τους εξής: Programs Accno Name 10 LEARN 2 PLAY 20 SOCCER PLUS 30 LEARN BY EXAMPLE Time 9. 00 10. 00 11. 00 Start 14/06/2014 14/07/2014 Group mini STARS RISING STARS SHINING STARS Participants Pno Firstname Lastname Gender 1 2 3 JIM ARTHUR MARCIA ADAMS CLARKE BATES Date of birth MALE 4/06/2004 MALE 14/03/2005 FEMALE 14/07/2009 Address Postal code THISSEUS 12345 ARIADNE 23456 Accno 20 20 30

Select * from user;