1 / 18

Connecting to Oracle using Java

Connecting to Oracle using Java. March 10, 2009 David Goldschmidt, Ph.D. goldschd@strose.edu. Java program. database. JDBC. JDBC is a platform-independent Java API for executing SQL statements Use JDBC to: Connect to a database Send SQL statements Receive results

moesha
Download Presentation

Connecting to Oracle using Java

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. Connecting to Oracle using Java March 10, 2009 David Goldschmidt, Ph.D. goldschd@strose.edu

  2. Javaprogram database JDBC • JDBC is a platform-independent Java API for executing SQL statements • Use JDBC to: • Connect to a database • Send SQL statements • Receive results • i.e. rows of data • Add or update existing rows of data • Call PL/SQL procedures, functions, etc. JDBC

  3. JDBC Drivers • Before connecting to a database,a driver class must first be loaded intothe Java Virtual Machine (or JVM) • A driver is simply a class in Java • MySQL: "com.mysql.jdbc.Driver" • Oracle: "oracle.jdbc.driver.OracleDriver" • Oracle driver is located within the ojdbc14.jar JAR file of the Oracle distribution

  4. JDBC Drivers • Sun defines four categories of JDBC drivers: • JDBC bridge driver – uses native code to connect a Java client to a third-party API (e.g. JDBC-ODBC) • Native API (part Java driver) – wraps native code with Java classes (e.g. Oracle Call Interface (OCI) driver) • Network protocol (pure Java driver) – Java classes communicate via a network protocol to a middle-tier server, which communicates with the database • Native protocol (pure Java driver) – Java classes communicate directly with the database (e.g. Thin)

  5. JDBC-ODBCdriver ODBCdriver Oracle CallInterface(OCI) JDBC OCIdriver JDBC Thindriver Oracle Listener JDBC Drivers Oracle RDBMS

  6. JDBC Connection Strings • Connect to a database using its connect string(i.e. its Connection URL): • Access: "jdbc:odbc:dataSource" • MySQL: "jdbc:mysql://hostname/dbname" • Oracle: "jdbc:oracle:thin:@hostname:port#:SID" • Use a Connection object: Connection connection = DriverManager.getConnection( dbConnectURL );

  7. Connection Connection Statement Statement Statement Statement ResultSet ResultSet ResultSet ResultSet JDBC Interfaces Driver

  8. Using JDBC

  9. Building Statements • Statement createStatement() • Prepare a query without parameters • Result set is read-only and forward-only • For repeated queries, gain performance speedup by using prepareStatement() instead

  10. Building Statements • PreparedStatement prepareStatement( String sql ) • Prepare a parameterized query • Result set is read-only and forward-only

  11. Building Statements • CallableStatement prepareCall( String sql ) • Prepare a call to a stored procedure • Register any OUT (or INOUT) parameters • Set any IN (or INOUT) parameters • Results are read-only and forward-only

  12. might be emptyif zero rows! Processing Statements • boolean execute( String sql ) • Use this method to execute DDL statements and stored procedures • Return value indicates whethera ResultSet object is available

  13. Processing Statements • ResultSet executeQuery( String sql ) • Use this method to execute DDL statements you expect to receive results from • i.e. Use for your SELECT statements

  14. Processing Statements • int executeUpdate( String sql ) • Use this method to execute INSERT, UPDATE, and DELETE statements • Return value is the number of rows affected

  15. ResultSet ResultSet sensitivity refers to whetherdatabase changes made whilethe ResultSet object is openare visible Processing Results • Default ResultSet behavioris read-only and forward-only • Change default using resultSetType and resultSetConcurrency parameters • For resultSetType: • TYPE_FORWARD_ONLY • TYPE_SCROLL_INSENSITIVE • TYPE_SCROLL_SENSITIVE scrolling uses a client-side memory cache

  16. ResultSet ROWID is a proprietary SQL data typethat uniquely identifies each row ofthe database Processing Results • For resultSetConcurrency: • CONCUR_READ_ONLY • CONCUR_UPDATABLE • To implement updateability, all queries request the ROWID for each row

  17. Sensitivity and Updateability • A scroll-sensitiveResultSet must: • Perform SELECT against only one table • Explicitly specify columns (i.e. not SELECT*) • Not use an ORDERBY clause • An updateable ResultSet must also: • Include all nonnullable columns (for INSERT)

  18. Batching • Improve performance by combining multiple SQL statements into a batch • Disable auto-commit • Oracle supports PreparedStatement batching only • Call addBatch() instead of executeUpdate() • Repeat! • Then call executeBatch() and commit() • Tune frequency of commits • Use clearBatch() to cancel your batch

More Related