0315_F1_8iJDBC-SQLJ.PDF

Size: px

Start display at page:

Download "0315_F1_8iJDBC-SQLJ.PDF"

ともなりしのしま
4 years ago
Views:

1 Page 1 1

2 Page 2 2

3 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 3 3

4 JDBC Java JDBC RDBMS JDBC ODBC JDBC SQL JDBC SQL Java API Java JDBC Java API RDBMS Java Java JDBC JDBC ODBC Javasoft SQL99 JDBC Oracle Page 4 4

5 JDBC JDBC OCI Type Oracle JDBC Thin Type JDBC2.0 JDBC API Oracle Oracle JDBC JDBC API Oracle JDBC ROWID REFCURSOR object-relational types Oracle Java PL/SQL Page 5 5

6 ResultSet ResultSet JNDI /XA SQL3(SQL99) JDBC2.0 ResultSet Java JNDI /XA SQL3(SQL99) Page 6 6

7 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 7 7

8 C/S Java Java JDBC Net8 Java Program JDBC OCI Driver OCI C Library OCI lib Net8 Oracle Net8 Net8 SQL Engine PL/SQL Engine OCI OCI JDBC OCI Net8 Oracle OCI Net8 BEQ Net8 IPC Named Pipes TCP/IP IPX/SPX Page 8 8

9 Thin TCP/IP Net8 OCI Java Program JDBC Thin Driver Java Sockets Net8 TCP/IP Listener Thin SQL Engine PL/SQL Engine Net8 TCP/IP Thin Java Java Applet OCI Thin TCP/IP Oracle Net8 Java Socket Net8 TTC(Two-Task Common: ) TCP/IP TNS TCP/IP Applet 1. HTML Java Applet 2. JDBC Thin Applet 3. Applet Net8 Page 9 9

10 Oracle8i JavaVM Java SQL Java Program Engine JavaVM JDBC Server Driver KPRB C Library KPRB C SQL SQL Engine PL/SQL Engine Java Stored Procedure CORBA Enterprise JavaBeans (EJB) Java SQL, PL/SQL SQL SQL JDBC SQL Net8 Page 10 10

11 OCI C/S Java Thin Oracle8i Java Stored Procedure, CORBA EJB Object Java Applet JDBC Applet Thin OCI Thin Thin Oracle OCI Oracle8i Page 11 11

12 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 12 12

13 CLASSPATH [Oracle Home]/jdbc/lib/classes12.zip NLS [Oracle Home]/ jdbc/lib/nls_charset12.zip OCI Solaris: LD_LIBRARY_PATH=[Oracle Home]/lib Windows NT: PATH=[Oracle Home] bin Thin CLASSPATH JDBC JDBC CLASSPATH JDK CLASSPATH Thin CLASSPATH OCI Solaris Windows NT LD_LIBRARY_PATH [Oracle Home]/lib PATH [Oracle Home] bin Page 13 13

14 1. 2. JDBC Statement ResultSet Statement 8. Oracle JDBC JDBC Java Page 14 14

15 1. import java.sql.* JDBC import oracle.jdbc.driver.* import oracle.sql.* 2. JDBC JDBC DriverManager DriverManager.registerDriver( new oracle.jdbc.driver.oracledriver()); 1. JDBC import Oracle JDBC Oracle 2. JDBC DriverManager static registerdriver() java.lang.class forname() JDBC DriverManager 1 try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) {...} Class.forName() Class.forName() JDBC JDBC JDBC Page 15 15

16 3. DriverManager getconnection(string URL,String user,string password); URL (OCI) Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@ myhoststring", "scott", "tiger"); (Thin) Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger"); 4. Statement Statement stmt = conn.createstatement(); 3. DriverManager static getconnection() Connection URL ID getconnection(string URL, String user, String password); URL jdbc:oracle:type:@<database> type thin oci8 database OCI TNS thin host : :SID ID URL getconnection(string URL); OCI Connection conn = DriverManager.getConnection( jdbc:oracle:oci8:scott/tiger@ ); 4. Statement Connection Connection createstatement() Statement Page 16 16

17 5. Statement executequery() executequery() 1 SQL ResultSet ResultSet rset = stmt.executequery("select * from EMP"); 5. Statement executequery() SELECT ResultSet INSERT, UPDATE, DELETE, DDL executeupdate() INSERT stmt.executeupdate( insert into EMP(EMPNO, ENAME) values(100, jserver ); UPDATE stmt.executeupdate( update EMP set SAL = where EMPNO = 100 ); DELETE stmt.executeupdate( delete from EMP where SAL > ); DDL stmt.executeupdate( create table test(id number(3), name(12)) ); Page 17 17

18 6. ResultSet SQL getxxx() Java while(rset.next()) { } String ename = rset.getstring(1); 6. ResultSet next() next() false ResultSet getxxx() XXX Java String Java SQL getstring() java.lang.string VARCHAR2 getbigdecimal() java.math.bigdecimal NUMBER getbyte() byte CHAR getdate() java.sql.date DATE getint() int NUMBER getlong() long NUMBER Page 18 18

19 7. ResultSet, Statement ResultSet Statement rset.close(); stmt.close(); 8. conn.close(); 7. ResultSet, Statement ResultSet Statement JDBC ResultSet Statement JDBC ResultSet Statement close() ResultSet 8. Connection close() Connection close() Page 19 19

20 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 20 20

21 JDBC import java.sql.*; class emp { public static void main(string args[]) throws SQLException { // JDBC DriverManager.registerDriver(new oracle.jdbc.driver.oracledriver()); // Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger"); // Statement Statement stmt = conn.createstatement(); // EMP ResultSet rset = stmt.executequery("select * from EMP"); // ResultSetMetaData rsmd = rset.getmetadata(); // int numcols = rsmd.getcolumncount(); // for (int i=1; i<=numcols; i++) { } if (i > 1) System.out.print(","); System.out.print(rsmd.getColumnLabel(i)); System.out.println(""); } } // while (rset.next()) { } for (int i=1; i<=numcols; i++) { } if (i > 1) System.out.print(","); // System.out.print(rset.getString(i)); System.out.println(""); // ResultSet Statement rset.close(); stmt.close(); // conn.close(); Page 21 21

22 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 22 22

23 SQL Java SQL SQLJ Pro*C Java SQL SQLJ JDBC Java SQLJ JDBC SQL Java SQLJ JDBC SQLJ Java SQLJ SQL Java SQL SQL JDeveloper SQLJ Page 23 23

24 Oracle JavaVM SQLJ Oracle IBM Tandem Sybase Infomix Javasoft ANSI/ISO SQLJ Oracle SQLJ ANSI SQLJ SQL ANSI SQLJ SQL92 Oracle SQLJ Oracle SQL LOB(BLOB, CLOB, BFILE) ROWID REF CURSOR Oracle Java VM SQLJ Java Page 24 24

25 java.sql.preparedstatement stmt; ResultSet results; stmt = conn.preparestatement ( SELECT ename FROM emp WHERE sal >? AND deptno =? ); stmt.setint(1, salparam); stmt.setint(2, deptnoparam); results = stmt.executequery(); SQLJ ResultSetIterator results; #sql results = {SELECT ename FROM emp WHERE sal > :salparam AND deptno = :deptnoparam}; SQLJ JDBC SQLJ JDBC SQL SQL Java default connection JDBC Statement JDBC SQL Page 25 25

26 JDBC+SQLJ SQLJ SQL SQL JDBC 1 SQLJ JDBC SQLJ JDBC SQL SQL JDBC SQLJ JDBC SQLJ JDBC SQLJ JDBC SQLJ JDBC ResultSet JDBC Oracle Oracle JDBC Page 26 26

27 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 27 27

28 SQLJ SQLJ SQLJ Application SQLJ runtime Any JDBC Driver SQL JDBC Any RDBMS Oracle SQLJ 2 Oracle SQLJ SQLJ Java SQLJ.sqlj Oracle SQLJ SQLJ JDBC SQLJ Oracle SQLJ SQLJ Java Thin JDBC SQLJ SQLJ Java SQLJ SQLJ SQL SQLJ.ser Page 28 28

29 SQLJ JDBC SQLJ Java Java Oracle8i Java JDBC Java SQL SQLJ SQLJ SQLJ JDBC Java SQLJ SQL SQLJ 1. SQLJ 2. sqlj SQLJ 3. SQL 4. SQL SQLJ Java SQLJ 5. Java Sun Microsystems JDK javac 4 Java 6. Page 29 29

30 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 30 30

31 CLASSPATH JDBC [Oracle Home]/sqlj/lib/translator.zip SQLJ [Oracle Home]/sqlj/lib/runtime.zip PATH [Oracle Home]/bin Oracle SQLJ Java Sun Microsystems JDK JDK 1.1.x 1.2.x JDBC java.sql JDBC JDBC JDBC Oracle SQLJ CLASSPATH PATH Page 31 31

32 1. SQLJ 2. SQLJ 3. SQLJ Page 32 32

33 SQLJ SQLJ Java SQL SQLJ SQLJ #sql {} SQL SELECT DML DDL SQL Java : SQLJ SQL SQL #sql { INSERT INTO EMP(ENAME) VALUES ( Joe ) }; PL/SQL #sql { DECLARE n NUMBER; BEGIN n := 1; WHILE n <= 100 LOOP INSERT INTO EMP(EMPNO) VALUES ( n); n := n + 1; END LOOP; END; }; String name = SMITH ; double salary = ;... #sql { UPDATE EMP SET SAL = :salary WHILE ENAME = :name }; Page 33 33

34 SQLJ SQLJ SQL SQLJ 2. SQLJ SQLJ SQLJ SQLJ Java sample.sqlj > sqlj sample.sqlj Date d = new Date(1993, 3, 11); #sql {insert into EMP values (:d)} JDBC 3. SQLJ SQLJ SQLJ SQLJ SQL SQLJ JDBC SQL JDBC Page 34 34

35 SQLJ SELECT SQLJ SQLJ SELECT next() SQL FETCH INTO Page 35 35

36 // #sql iterator EmpIter(String ENAME, double SAL);... // EmpIter emps; #sql emps = { select SAL, ENAME from EMP }; // while(emps.next()) { String ename = emps.ename(); // ENAME, SAL double sal = emps.sal(); // ename, sal... } emps.close(); // #sql iterator EmpIter(String, double);... // String name = null; double salary = 0; // EmpIter emps; #sql emps = { select ENAME, SAL from EMP }; // while(true) { #sql { FETCH :emps INTO :name, :salary }; // endfetch() // FETCH INTO if (emps.endfetch()) break; System.out.println( Name is + name);... } emps.close(); Page 36 36

37 NULL sjt_ SQLJ java sqlj oracle NULL Java int, double, float NULL SQL NULL Java NULL SQL NULL Java java.lang.boolean java.lang.byte java.lang.short java.lang.integer java.lang.long java.lang.double java.lang.float SQLJ JDBC Java SQLJ SQLJ SELECT Page 37 37

38 JDBC JDBC JDBC JDBC JDBC SQLJ SQLJ SQLJ SQLJ SQLJ Page 38 38

39 SQLJ import java.sql.*; import sqlj.runtime.*; import oracle.sqlj.runtime.oracle; class sample { public static void main(string args[]) throws SQLException { // 'sqlj.properties' Oracle.connect(sample.class, "sqlj.properties"); // ResultSet ResultSetIterator rsiter; #sql rsiter = { SELECT * FROM EMP }; // ResultSet ResultSet rset = rsiter.getresultset(); // ResultSetMetaData rsmd = rset.getmetadata(); // int numcols = rsmd.getcolumncount(); // for (int i=1; i<=numcols; i++) { if (i > 1) System.out.print(","); System.out.print(rsmd.getColumnLabel(i)); } System.out.println(""); } } // while (rset.next()) { for (int i=1; i<=numcols; i++) { if (i > 1) System.out.print(","); // System.out.print(rset.getString(i)); } System.out.println(""); } // ResultSetIterator // ResultSet rsiter.close(); Page 39 39

40 SQLJ (sqlj.properties) ### ### SQLJ ### 8 ### ### ### Settings to establish a database connection for online checking ### ### turn on checking by uncommenting user ### or specifying the -user option on the command line sqlj.user=scott sqlj.password=tiger ### add additional drivers here sqlj.driver=oracle.jdbc.driver.oracledriver ### Oracle JDBC-OCI7 URL #sqlj.url=jdbc:oracle:oci7:@ ### Oracle JDBC-OCI8 URL sqlj.url=jdbc:oracle:oci8:@ ### Oracle JDBC-Thin URL #sqlj.url=jdbc:oracle:thin:@<host>:<port>:<oracle_sid> #sqlj.url=jdbc:oracle:thin:@localhost:1521:orcl ### Warning settings ### Note: All settings must be specified TOGETHER on a SINGLE line. ### Report portability warnings about Oracle-specific extensions to SQLJ #sqlj.warn=portable ### Turn all warnings off #sqlj.warn=none ### Turn informational messages on #sqlj.warn=verbose ### Online checker ### # JDBC-generic checker: sqlj.online=sqlj.semantics.jdbcchecker ### Offline checker ### #sqlj.cache=on Page 40 40

41 Page 41 41

Microsoft Word - 430_15_Developing_Stored_Procedure.doc

Microsoft Word - 430_15_Developing_Stored_Procedure.doc Java Oracle 1998 11 Java 3 Java Web GUI Java Java Java Oracle Java Oracle8i Oracle / Oracle Java Virtual Machine VM CORBA Enterprise JavaBeans Oracle Java Java Java Oracle Oracle Java Virtual Machine Oracle