1 / 8

Using Oracle JDBC

Using Oracle JDBC. How to Run JDBC on Your Account Communication Mechanism Using Metadata Building a Database Auto Commit v.s Atomic Transaction. # environment variables for Oracle setenv ORACLE_TERM vt100 setenv ORACLE_SID CIS setenv ORACLE_BASE /u01/home/dba/oracle

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