1.26k likes | 1.45k Views
Introduction to JDBC Programming. Oracle Korea. 발 표 순 서. Basic JDBC Programming Advanced Programming JDBC 2.0 Features SQLJ Q&A. Basic JDBC Programming. Basic JDBC Programming. After completing this lesson, you should be able to do the following:
E N D
Introduction to JDBC Programming Oracle Korea
발 표 순 서 • Basic JDBC Programming • Advanced Programming • JDBC 2.0 Features • SQLJ • Q&A
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
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
Overview of Querying a Database With JDBC Connect Query Processresults Close
Stage 1: Connect Connect Register the driver Connect to the database Query Processresults Close
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
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)
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
Oracle JDBC Drivers: Thin Client Driver • Written entirely in Java • Applets must use this driver Applet JDBC Thin driver O7 or O8 Client Server
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
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
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
About JDBC URLs • JDBC uses a URL to identify the database connection. jdbc:<subprotocol>:<subname> Databaseidentifier Protocol Subprotocol jdbc:oracle:<driver>:@<database>
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>
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");
Using Connection java.sql.Connection Creating Statement createStatment() prepareStatment(String) prepareCall(String) Transaction Management commit() rollback() Get database metadata getMetaData() Conneciton related close() isClosed()
Demonstration Connection
Stage 2: Query Connect Create a statement Query Query the database Processresults Close
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
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);
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");
Stage 3: Process the Results Connect Query Step through the results Assign results to Java variables Processresults Close
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.
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}
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 }…}
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
Stage 4: Close Connect Query Close the result set Processresults Close the statement Close Close the connection
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();
Demonstration A Simple JDBC Program
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.
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();
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
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); … }
Demonstration Dynamic Query using MetaData
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.
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 = ?");
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();
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.
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);
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);
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
Oracle provides many extensions to standard JDBC; for example: Oracle JDBC Extensions Connection OracleConnection Statement OracleStatement PreparedStatement OraclePreparedStatement CallableStatement OracleCallableStatement ResultSet OracleResultSet
Advanced Programming • LOB Data type • Advanced data type
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
BLOB • Reading • getBinaryStream() 메서드 사용. • Return된 InputStream을 이용하여 처리(파일, 데이터베이스) • Writing • getBinaryOutputStream() 메서드 사용 • java.io.OutputStream 객체를 이용하여 Writing
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