10.ppt

Size: px

Start display at page:

Download "10.ppt"

내상 육
5 years ago
Views:

1 : SQL. SQL Plus. JDBC.

2 SQL >> SQL create table : CREATE TABLE ( ( ), ( ),.. )

3 SQL >> SQL create table : id username dept birth id username dept birth CREATE TABLE member ( id NUMBER NOT NULL Primary Key, username VARCHAR2(20), dept VARCHAR2(7), birth DATE, VARCHAR2(40) )

4 SQL >> SQL alter table : ALTER TABLE [SQL ] ( )

5 SQL >> SQL alter table : id id username dept birth tel ALTER TABLE member add (tel varchar2(30) NOT NULL) ALTER TABLE member modify (username varchar2()) ALTER TABLE member DROP CONSTRAINT member_pk

6 SQL >> SQL delete table : DROP TABLE.

7 SQL >> SQL select : SELECT FROM WHERE

8 SQL >> SQL select : id username dept birth test@test.net tt@tt.net SELECT * FROM member WHERE id = SELECT id, username, birth FROM member WHERE id = AND name=

9 SQL >> SQL insert : INSERT INTO ( ) VALUES( )

10 SQL >> SQL insert : id username dept birth test@test.net tt@tt.net my@my.net INSERT INTO member values(200203,,, , my@my.net )!. INSERT INTO member(id,gwa,name) values(200201,, )!.

11 SQL >> SQL update : UPDATE set = 1, field = 2 WHERE

12 SQL >> SQL update : id username dept birth test@test.net tt@tt.net UPDATE member set id = , name= WHERE id =

13 SQL >> SQL delete : DELETE FROM WHERE

14 SQL >> SQL delete : id username dept birth test@test.net tt@tt.net DELETE FROM member WHERE id = DELETE FROM member WHERE id = AND name=

15 SQL >> SQL PL/SQL SQL.. SQL Plus!

16 JDBC >> JDBC.. DB JDBC.

17 JDBC >> JDBC JDBC JDBC JDBC MySql JDBC DBMS MySql DBMS JDBC DBMS

18 JDBC >> JDBC JDBC JDBC API JDBC JDBC API JDBC-ODBC Native-API Net-Protocol Native-Protocol ODBC JDBC DBMS DBMS DBMS DBMS DBMS

19 JDBC >> JDBC : 2.0 (3.0 ) JDBC CD\ojdbc14.jar ( ) %JAVA_HOME%\jre\lib\ext\. tomcat4\common\lib Context\WEB-INF\lib tomcat4\common\lib

20 JDBC >> JDBC

21 JDBC >> JDBC Class.forName( oracle.jdbc.driver.oracledriver ); DB : sun.jdbc.odbc.jdbcodbcdriver

22 JDBC >> JDBC Connection conn = DriverManger.getConnection(JDBC_url,, ); JDBC_URL = JDBC:oracle:thin:@IP : :SID DB = jdbc:odbc:song IP IP 1521 SID MySQL DB

23 JDBC >> JDBC Statement Statement stmt = conn.createstatement(); stmt.executeupdate( insert into test values('" +request.getparameter("username")+"','"+request.getparameter(" ")+"')");

24 JDBC >> JDBC PreparedStatement PreparedStatement pstmt = conn.preparestatement( insert into test values(?,?) ); pstmt.setstring(1,request.getparameter("username"); pstmt..setstring(2, request.getparameter(" "); pstmt.executeupdate();

25 JDBC >> JDBC ResultSet rs = pstmt.executequery( ); ResultSet next()

26 JDBC >> JDBC Select * from xxx where city= ; rs rs.next() rs.next() 0 ResultSet 1 4 name city ========================== test@test.net kk@kk.net tt@tt.net hh@hh.net...

27 JDBC >> JDBC ResultSet rs = pstmt.executequery(); while(rs.next()) { name = rs.getstring(1); // or rs.getstring( name ); age = rs.getint(2); // or rs.getint( ); } rs.close();

28 JDBC >> JDBC Connection close(). DB. <jsp:usebean id= mybean scope= request class= MyBean />

29 JDBC >> JDBC JDBC DB DB : server IP, DB userid, userpass, SID(DB) ( ) DB 1 DB 2 DB 3

30 JDBC >> JDBC Statement PreparedStatement

Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET

135-080 679-4 13 02-3430-1200 1 2 11 2 12 2 2 8 21 Connection 8 22 UniSQLConnection 8 23 8 24 / / 9 3 UniSQL 11 31 OID 11 311 11 312 14 313 16 314 17 32 SET 19 321 20 322 23 323 24 33 GLO 26 331 GLO 26