Java Programming with Oracle SQLJ

Java Programming with Oracle SQLJ

5.0 5
by Jason Price
     
 

If you're a Java programmer working in an Oracle environment, you're probably familiar with JDBC as a means of accessing data within an Oracle database. SQLJ takes you further, allowing you to access a database using embedded SQL statements. Java Programming with Oracle SQLJ shows you how to get the most out of SQLJ.Layered on top of JDBC, SQLJ greatly

…  See more details below

Overview

If you're a Java programmer working in an Oracle environment, you're probably familiar with JDBC as a means of accessing data within an Oracle database. SQLJ takes you further, allowing you to access a database using embedded SQL statements. Java Programming with Oracle SQLJ shows you how to get the most out of SQLJ.Layered on top of JDBC, SQLJ greatly simplifies database programming. Rather than make several calls to the JDBC API just to execute a simple SQL statement, SQLJ executes that statement simply by embedding it within the Java code.In this book, Jason Price explains SQLJ programming from a task-oriented point of view. You'll learn how to:

  • Embed queries and other SQL statements within Java programs
  • Deploy SQLJ code not only on client machines, but also to JServer—Oracle's Java engine built into the database
  • Use advanced techniques for working with collections, streams, large objects, and database objects, all without leaving the comfort of the SQLJ environment
  • Tune SQLJ programs for maximum performance
Throughout the book, the exposition of SQLJ and SQLJ programming techniques reflects the author's many years of professional experience as a programmer and consultant. Examples are first-rate, enabling you to learn SQLJ in no time. If you're writing Java code to access an Oracle database, you can't afford not to know about SQLJ.

Read More

Editorial Reviews

The author, an Oracle certified database administrator and certified application developer, explains the use of SQLJ, a technology that he believes improves on the more often-used JDBC by operating at a higher level of abstraction and using a simpler, more concise syntax. Includes full descriptions of the various command-line tools, the requirements for using SQLJ, performance tuning tips, and numerous working code examples to illustrate the use of SQLJ. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Product Details

ISBN-13:
9780596000875
Publisher:
O'Reilly Media, Incorporated
Publication date:
08/28/2001
Pages:
400
Product dimensions:
7.00(w) x 9.30(h) x 0.99(d)

Read an Excerpt

Chapter 8: Contexts and Multithreading

There are two important objects used in SQLJ that affect the execution of database operations: connection contexts and execution contexts. Connection contexts are used to connect to a database. All embedded SQL statements within a SQLJ program run in a connection context. Connection contexts make it possible to create multiple connections to a database or to connect to more than one database at a time. An execution context is used to hold the number of rows affected by a SQL operation, along with any warnings generated by the database. Execution contexts are used to control certain aspects of how a SQL statement is executed. For example, you can use an execution context to control the timeout period after which a SQL operation is abandoned.

A multithreaded program is one that is able to carry out several tasks in parallel using Java threads. As you will see in this chapter, execution contexts are very important when writing a multithreaded SQLJ program.

Connection Contexts

A connection context represents a connection to a particular database schema. So far, all the SQLJ programs you have seen have used one database connection, established using a call to the Oracle.connect( ) method. For example, the program FundamentalExample1.sqlj described in Chapter 3 used the following call to connect to the fundamental_user schema:

Oracle.connect(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);

A call to the Oracle.connect( ) method creates an object of the sqlj.runtime.ref.DefaultContext class, which may then be used to access the database. This DefaultContext object is known as the default connection context, and it is the database connection that the rest of the program uses by default when performing SQL operations. You can create additional connection contexts in order to make multiple database connections.

Multiple Database Connections

Sometimes, a single database connection may not be enough for your program to accomplish its task. For example, you might need to connect to two or more schemas in the database at the same time to retrieve the data you need. It is possible to create multiple database connections by explicitly creating additional objects of the DefaultContext class.

The oracle.sqlj.runtime.Oracle class contains a method named getConnection( ) that creates and returns a DefaultContext object, which may then be stored in a DefaultContext object that you explicitly create. The syntax for the getConnection( ) method is the same as for the connect( ) method. For example, the following statement creates a DefaultContext object named conn_context1 that connects to the fundamental_user schema:

DefaultContext conn_context1 = Oracle.getConnection(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);

The statement in this example calls the getConnection( ) method to create and return a DefaultContext object. That object is then assigned to conn_context1. Multiple connections may be made to the same schema using multiple DefaultContext objects. For example, the following statement creates another connection context named conn_context2, which also accesses the fundamental_user schema:

DefaultContext conn_context2 = Oracle.getConnection(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);

An important point to note when working with multiple connection contexts is that each connection context establishes its own database session. As a result, each connection context has its own transaction in the database.

Specifying the connection context to use

Now that you have seen how to create connection contexts, you must be asking yourself, "How do I tell SQLJ which connection context to use?" There are two ways you can do this. The first is to include the name of the connection context in the SQLJ executable statement. This is done using the following syntax:

#sql [connection_context_name] { SQL_statement };

TIP:   The brackets around the connection context name in this syntax do not indicate an optional syntax element. They are actually part of the statement.

The syntax elements are as follows:

connection_context_name
The name of the connection context to use for the embedded SQL statement.

SQL_statement
The embedded SQL statement.

If a connection context name is not specified, then the default connection context is used to perform the SQL statement. The following example uses the connection context conn_context1 created earlier to update a row in the customers table:

#sql [conn_context1] {
  UPDATE
    customers
  SET
    first_name = 'John',
    last_name = 'Doe'
  WHERE
    id = 1
};

The second way to indicate which connection context to use is to call the DefaultContext.setDefaultContext( ) method. This method accepts a DefaultContext object, and any SQL statements that follow it will use this connection context by default. Of course, if a connection context is explicitly specified in an executable statement, then that connection context will be used: an explicitly specified execution context always overrides the default. The following example shows the setDefaultContext( ) method being used to set the default connection context for two different SQLJ statements:

DefaultContext.setDefaultContext(conn_context1);
#sql { SQL_statement };  // uses conn_context1
 
DefaultContext.setDefaultContext(conn_context2);
#sql { SQL_statement };  // uses conn_context2

In addition to setting a default context, you can also retrieve it. A call to the getDefaultContext( ) method returns the default connection context. For example:

DefaultContext default_context = DefaultContext.getDefaultContext(  );
Understanding transactions and multiple connection contexts

I have already mentioned that each connection context establishes its own database session and transaction. This is useful because it means you can have multiple transactions against the same schema. The code in this section illustrates a simple example of this powerful feature. The following example updates a row in the customers table using the connection context conn_context1. If a transaction were not already underway, this statement would cause a new transaction to be started.

#sql [conn_context1] {
  UPDATE
    customers
  SET
    first_name = 'John',
    last_name = 'Doe'
  WHERE
    id = 1
};

Until this database transaction is completed by conn_context1 (using a commit or rollback, for example), the row is locked. If another statement uses a different connection context to attempt to modify the same row, it must wait until conn_context1 ends the transaction and the lock on the row is freed.1 This locking behavior is not unique to connection contexts. If you connect to the fundamental_user schema using two instances of SQL*Plus to start two database sessions and then try to update the same row in a table from both sessions, one of the SQL*Plus sessions will wait until the other session ends its transaction.

In the following example, conn_context1 performs a rollback of the update, and conn_context2 is then able to modify the same row in the customers table:

#sql [conn_context1] { ROLLBACK };
 
#sql [conn_context2] {
  UPDATE
    customers
  SET
    first_name = 'Fred',
    last_name = 'Smith'
  WHERE
    id = 1
};
Making connections to multiple databases

You aren't limited to accessing one database in a SQLJ program. The Oracle.getConnection( ) method may be used to create connection contexts that access schemas in more than one database. For example, the following statement creates a connection context that accesses a schema named remote_user in the database identified by the Oracle SID orcl, which is running on a computer named remotehost:

DefaultContext remote_conn_context = Oracle.getConnection(
  "jdbc:oracle:thin:@remotehost:1521:orcl",
  "remote_user",
  "remote_password"
);

The connection context remote_conn_context may now be used in executable statements, just like any other connection context. In this way, your SQLJ programs can access as many databases as necessary.

Closing connection contexts

Once a connection context is no longer needed, it is good programming practice to either perform a commit or a rollback, and close the connection context using the close( ) method. Actually, when you close a connection context, an implicit commit is performed, but you should still perform an explicit commit instead of relying on this default behavior. If you don't close a connection context, an implicit rollback occurs when your SQLJ program ends, and any changes you made to the database in the transaction for that context will be lost.

The following statement closes the connection context conn_context1:

conn_context1.close(  );

The default connection context created by a call to the Oracle.connect( ) method may also be closed:

Oracle.close(  );

Example Program: ContextExample1.sqlj

This section contains a complete example program named ContextExample1.sqlj (Example 8-1) that illustrates the use of connection contexts to make multiple connections to a database. The program ContextExample1.sqlj performs the following major steps:

  1. Makes two database connections: one using a call to the connect( ) method to create a default connection context, and the other using a call to getConnection( ) to create a connection context named conn_context.
  2. Adds a row to the customers table using conn_context.
  3. Updates customer #1 using conn_context.
  4. Displays all the rows in the customers table using the default connection context. This is done by calling the program's displayCustomers( ) method. The changes made to the customers table in Steps 2 and 3 are not displayed in the output from displayCustomers( ) because those changes were made using conn_context. Remember, conn_context represents a separate connection, which has a database transaction separate from the default connection context used by the displayCustomers( ) method.
  5. Switches the default connection context to conn_context by making a call to the program's setDefaultContext( ) method.
  6. Displays all the rows in the customers table again via another call to displayCustomers( ). The changes are now visible because the default connection context has been switched to conn_context, and displayCustomers( ) uses the default context.
  7. Rolls back the changes.
  8. Closes both the conn_context connection context and the default connection context....

Read More

Meet the Author

Jason Price is currently employed as a product manager with Oracle Corporation. He has contributed to many products at Oracle, including the database server, the application server, and several CRM applications. Prior to working at Oracle, he was employed as a consultant at Logica PLC in London, a systems integration and technical consulting firm. He is a certified Oracle DBA and Application Developer, and has over ten years of experience in the software industry.

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >

Java Programming with Oracle SQLJ 5 out of 5 based on 0 ratings. 5 reviews.
Guest More than 1 year ago
My company started using Oracle SQLJ to write Java programs that access an Oracle database about 3 months ago. We used this book as the training material for programmers.
Guest More than 1 year ago
This book has everything. It takes you through simple database access in Java, and the use of advanced database features like large objects, dynamic SQL, and deploying Java programs to the database and internet application server. If you are using Java and Oracle, buy this book!
Guest More than 1 year ago
This is the only decent book on SQLJ.
Guest More than 1 year ago
I just started using SQLJ two weeks ago, using this book as my training. I am now a productive SQLJ programmer.
Guest More than 1 year ago
I've been using SQLJ for some time now, and was always hoping for a decent book on the subject. Now there finally is one - this book. It introduces SQL and SQLJ in an easy to understand manner, and also covers the advanced material in a comprehensable style. It even covers the Oracle9i SQLJ stuff too!