1 / 8

Using Oracle JDBC

This guide provides comprehensive insights into using Oracle JDBC for database operations. Learn how to establish connections, send SQL statements, and retrieve results using `ResultSet` and `ResultSetMetaData`. Explore how to efficiently build databases through prepared statements, manage transactions with auto-commit vs. atomic transactions, and properly configure environment variables. This resource is essential for developers looking to leverage Oracle's JDBC capabilities, ensuring robust database interaction and maintenance.

kelii
Download Presentation

Using Oracle JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Oracle JDBC • How to Run JDBC on Your Account • Communication Mechanism • Using Metadata • Building a Database • Auto Commit v.s Atomic Transaction

  2. # environment variables for Oracle setenv ORACLE_TERM vt100 setenv ORACLE_SID CIS setenv ORACLE_BASE /u01/home/dba/oracle setenv ORACLE_HOME /u01/home/dba/oracle/product/7.1.4 setenv NLS_LANG AMERICAN_AMERICA.US7ASCII set path=($ORACLE_HOME/bin $path) # environment variables for Java class files setenv CLASSPATH ./:/pkg/java1.1.6/lib/classes.zip: /pkg/cis550/JDBC/lib/classes111.zip # path to find the Java compiler and VM set path=(/pkg/java-1.1.6/bin . $path ) # path to find libraries for Oracle JDBC drivers setenv LD_LIBRARY_PATH /pkg/cis550/JDBC/lib setenv EPC_DISABLED TRUE

  3. class JdbcTest { public static void main (String args []) throws SQLException, IOException { try { Class.forName ("oracle.jdbc.driver.OracleDriver"); } catch (/*ClassNotFound*/ Exception e) { e.printStackTrace (); } Connection conn = DriverManager.getConnection("jdbc:oracle:oci7:/@cisora"); Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery ("select table_name from tabs"); while (rset.next ()) { System.out.println (rset.getString (1)); }; stmt.close(); conn.close();} }

  4. Database Accessing Mechanism • Registering a database driver. • Using DriverManager to obtain a Connection that represents a database connection. • Sending a SQL statement to the database using Statement. • Retrieving the query result with a ResultSet.

  5. Using Metadata Look into the database itself and its contents. Class.forName ("oracle.jdbc.driver.OracleDriver"); Connection conn= DriverManager.getConnection("jdbc:oracle:oci7:/@cisora"); Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery(sql_query_string); ResultSetMetaData metadata = rset.getMetaData(); int numcols = metadata.getColumnCount(); // how many columns String labl1 = metadata.getColumnLabel(1); // get the first label int size = metadata.getColumnDisplaySize(1); // get the column width

  6. insertSailor = conn.prepareStatement(“INSERT INTO Sailor VALUES(?, ?, ?); //sailor_name, sailor_rating, sailor_age Building a Database • Repeatedly use INSERT INTO • Increase the efficiency by providing PreparedStatement: database interprets the prepared statement and creates its template just once.

  7. Atomic Transactions • A newly-created Connection is “auto commit” • Each update is as a separate transaction and automatically committed to the database. • How to “group” several updates into a single “atomic” transaction? • Take Connection out of “auto commit” mode. • Explicitly call commit() to a batch of transactions • call rollback() to abort a batch of transactions.

  8. Class.forName ("oracle.jdbc.driver.OracleDriver"); Connection conn= DriverManager.getConnection("jdbc:oracle:oci7:/@cisora"); conn.setAutoCommit(FALSE); conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); Statement stmt = conn.createStatement (); try{ stmt.executeUpdate(sql_str_Insert_Account_Table); stmt.executeUpdate(sql_str_Create_History_Table); stmt.executeUpdate(sql_str_Insert_History_Table); conn.commit(); // commit the updates } catch(SQLException e) { conn.rollback(); // removes all updates from the database }

More Related