Introduction to Basic JDBC Programming with Oracle: Features, SQLJ, and Q&A

2. Introduction to JDBC Programming Oracle Korea

3. 발 표 순 서 • Basic JDBC Programming • Advanced Programming • JDBC 2.0 Features • SQLJ • Q&A

4. Basic JDBC Programming

5. Basic JDBC Programming • After completing this lesson, you should be able to do the following: • Connect to a database using Java Database Connectivity (JDBC) • Create and execute a query using JDBC • Invoke prepared statements • Commit and roll back transactions • Use the Oracle JDBC extensions to improve performance

6. JDBC • JDBC is a standard interface for connecting to relational databases from Java. • The JDBC classes and interfaces are in the java.sql package. • JDBC 1.22 is part of JDK 1.1; JDBC 2.0 is part of Java 2

7. Overview of Querying a Database With JDBC Connect Query Processresults Close

8. Stage 1: Connect Connect Register the driver Connect to the database Query Processresults Close

9. A JDBC Driver • Is an interpreter that translatesJDBC method calls to vendor-specific database commands • Implements interfaces in java.sql • Can also provide a vendor’s extensions to the JDBC standard Database commands JDBC calls Driver Database

10. Oracle JDBC Driver Oracle 8i JDBC “Thin” driver Java Socket SQL*Net Java Store Procedure Java Engine JDBC “Server- Side Internal” driver JDBC “OCI” driver SQL & PL/SQL Engines SQL*Net OCI C Lib database Lib • JDBC “Thin” driver (also available in server) • JDBC “OCI” driver • JDBC “Server-Side Internal” driver (Kernal PRogram Bundled Calls driver)

11. Oracle JDBC Drivers • Thin driver • a 100% Java driver for client-side use without an Oracle installation, particularly with applets • OCI drivers (OCI8 and OCI7) • for client-side use with an Oracle client installation • server-side Thin driver • which is functionally the same as the client-side Thin driver, but is for code that runs inside an Oracle server and needs to access a remote server, including middle-tier scenarios • server-side internal driver • for code that runs inside the target server

12. Oracle JDBC Drivers: Thin Client Driver • Written entirely in Java • Applets must use this driver Applet JDBC Thin driver O7 or O8 Client Server

13. Oracle JDBC Drivers: OCI Client Drivers • Written in C and Java • Must be installed on the client Application JDBC OCI driver O7 or O8 ocixxx.dll Client Server

14. JDBC Server side driver Oracle JDBC Drivers: 3. Server-Side Driver • Runs inside the database • Java stored procedures must use this driver Stored procedure Oracle8i SQLEngine C library

15. Other JDBC Drivers • JDBC-ODBC Bridge • Translates JDBC into open database connectivity (ODBC) calls • Allows communication with existing ODBC drivers when no JDBC driver is available • Oracle Lite Driver For communication with an Oracle Lite database

16. About JDBC URLs • JDBC uses a URL to identify the database connection. jdbc:<subprotocol>:<subname> Databaseidentifier Protocol Subprotocol jdbc:oracle:<driver>:@<database>

17. JDBC URLs with Oracle Drivers • Thin driver • OCI driver • Server-side driver: Use the default connection jdbc:oracle:thin:@<host>:<port>:<SID> jdbc:oracle:oci8:@<TNSNAMES entry>

18. How to Make the Connection 1. Register the driver. DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); 2. Connect to the database. Connection conn = DriverManager.getConnection (URL, userid, password); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");

19. Using Connection java.sql.Connection Creating Statement createStatment() prepareStatment(String) prepareCall(String) Transaction Management commit() rollback() Get database metadata getMetaData() Conneciton related close() isClosed()

20. Demonstration Connection

21. Stage 2: Query Connect Create a statement Query Query the database Processresults Close

22. The Statement Object • A Statement object sends your SQL statement to the database. • You need an active connection to create a JDBC statement. • Statement has three methods to execute a SQL statement: • executeQuery() for QUERY statements • executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements • execute() for either type of statement

23. How to Query the Database 1. Create an empty statement object. 2. Execute the statement. Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(statement); int count = stmt.executeUpdate(statement); boolean isquery = stmt.execute(statement);

24. Querying the Database: Examples • Execute a select statement. Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery ("select RENTAL_ID, STATUS from ACME_RENTALS"); • Execute a delete statement. Statement stmt = conn.createStatement(); int rowcount = stmt.executeUpdate ("delete from ACME_RENTAL_ITEMS where rental_id = 1011");

25. Stage 3: Process the Results Connect Query Step through the results Assign results to Java variables Processresults Close

26. The ResultSet Object • JDBC returns the results of a query in a ResultSet object. • A ResultSet maintains a cursor pointing to its current row of data. • Use next() to step through the result set row by row. • getString(), getInt(), and so on assign each value to a Java variable.

27. How to Process the Results • 1. Step through the result set. • 2. Use getXXX() to get each column value. while (rset.next()) { … } String val = rset.getString(colname); String val = rset.getString(colIndex); while (rset.next()) { String title = rset.getString("TITLE"); String year = rset.getString("YEAR"); … // Process or display the data}

28. How to Handle SQL Null Values • Java primitive types cannot have null values. • Do not use a primitive type when your query might return a SQL null. • Use ResultSet.wasNull() to determine whether a column has a null value. while (rset.next()) { String year = rset.getString("YEAR"); if (rset.wasNull() { … // Handle null value }…}

29. ResultSet maps database types to Java types. Mapping Database Types to Java Types ResultSet rset = stmt.executeQuery ("select RENTAL_ID, RENTAL_DATE, STATUS from ACME_RENTALS"); int id = rset.getInt(1); Date rentaldate = rset.getDate(2); String status = rset.getString(3); Col Name RENTAL_ID RENTAL_DATE STATUS Type NUMBER DATE VARCHAR2

30. Stage 4: Close Connect Query Close the result set Processresults Close the statement Close Close the connection

31. How to Close the Connection 1. Close the ResultSet object. 2. Close the Statement object. 3. Close the connection (not necessary for server-side driver). rset.close(); stmt.close(); conn.close();

32. Demonstration A Simple JDBC Program

33. The DatabaseMetaData Object • The Connection object can be used to get a DatabaseMetaData object. • This object provides more than 100 methods to obtain information about the database.

34. How to Obtain Database Metadata 1. Get the DatabaseMetaData object. 2. Use the object’s methods to get the metadata. DatabaseMetaData dbmd = conn.getMetaData(); DatabaseMetaData dbmd = conn.getMetaData(); String s1 = dbmd getURL(); String s2 = dbmd.getSQLKeywords(); boolean b1 = dbmd.supportsTransactions(); boolean b2 = dbmd.supportsSelectForUpdate();

35. The ResultSetMetaData Object • The ResultSet object can be used to get a ResultSetMetaData object. • ResultSetMetaData object provides metadata, including: • Number of columns in the result set • Column type • Column name

36. How to Obtain Result Set Metadata 1. Get the ResultSetMetaData object. 2. Use the object’s methods to get the metadata. ResultSetMetaData rsmd = rset.getMetaData(); ResultSetMetaData rsmd = rset.getMetaData(); for (int i = 0; i < rsmd.getColumnCount(); i++) { String colname = rsmd.getColumnName(i); int coltype = rsmd.getColumnType(i); … }

37. Demonstration Dynamic Query using MetaData

38. The PreparedStatement Object • A PreparedStatement object holds precompiled SQL statements. • Use this object for statements you want to execute more than once. • A prepared statement can contain variables that you supply each time you execute the statement.

39. How to Create a Prepared Statement 1.Register the driver and create the database connection. 2.Create the prepared statement, identifying variables with a question mark (?). PreparedStatement pstmt = conn.prepareStatement("update ACME_RENTALS set STATUS = ? where RENTAL_ID = ?"); PreparedStatement pstmt = conn.prepareStatement("select STATUS from ACME_RENTALS where RENTAL_ID = ?");

40. How to Execute a Prepared Statement 1. Supply values for the variables. 2. Execute the statement. pstmt.setXXX(index, value); pstmt.executeQuery(); pstmt.executeUpdate(); PreparedStatement pstmt = conn.prepareStatement("update ACME_RENTALS set STATUS = ? where RENTAL_ID = ?"); pstmt.setString(1, "OUT"); pstmt.setInt(2, rentalid); pstmt.executeUpdate();

41. The CallableStatement Object • A CallableStatement object holds parameters for calling stored procedures. • A callable statement can contain variables that you supply each time you execute the call. • When the stored procedure returns, computed values (if any) are retrieved through the CallabableStatement object.

42. How to Create a Callable Statement • Register the driver and create the database connection. • Create the callable statement, identifying variables with a question mark (?). CallableStatement cstmt = conn.prepareCall("{call " + ADDITEM + "(?,?,?)}"); cstmt.registerOutParameter(2,Types.INTEGER); cStmt.registerOutParameter(3,Types.DOUBLE);

43. 1. Set the input parameters. 2. Execute the statement. 3. Get the output parameters. How to Execute a Callable Statement cstmt.setXXX(index, value); cstmt.execute(statement); var = cstmt.getXXX(index);

44. Using Transactions • The server-side driver does not support autocommit mode. • With other drivers: • New connections are in autocommit mode. • Use conn.setAutoCommit(false) to turn autocommit off. • To control transactions when you are not in autocommit mode: • conn.commit(): Commit a transaction • conn.rollback(): Roll back a transaction

45. Oracle provides many extensions to standard JDBC; for example: Oracle JDBC Extensions Connection OracleConnection Statement OracleStatement PreparedStatement OraclePreparedStatement CallableStatement OracleCallableStatement ResultSet OracleResultSet

46. Advanced Programming

47. Advanced Programming • LOB Data type • Advanced data type

48. LOB Data type • oracle.sql.BLOB • java.sql.Blob • Processing locator : getBLOB(), setBLOB() methods using jdk1.2.x • oracle.sql.CLOB • java.sql.Clob • Processing locator : getCLOB(), setCLOB() methods using jdk1.2.x • oracle.sql.BFILE • Oracle Specific Datatype

49. BLOB • Reading • getBinaryStream() 메서드 사용. • Return된 InputStream을 이용하여 처리(파일, 데이터베이스) • Writing • getBinaryOutputStream() 메서드 사용 • java.io.OutputStream 객체를 이용하여 Writing

50. BLOB : Reading Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery (“select blob_column from blob_table”); while (rs.next()) { BLOB blob = ((OracleResultSet)rs).getBLOB(1); InputStream is = blob.getBinaryStream(); int read = 0; while ( (read = is.read()) != -1) { // to do like writing a file using the stream } is.close(); } 1. create statement 2. create resultset 3. get Blob locator 4. get InputStream 5. InputStream 처리 6. InputStream close