ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button Learning SQLJ

DDL Statements and Transactions

by Jason Price, author of Java Programming with Oracle SQLJ
03/06/2002

This final column in my Learning SQLJ series explores how to add SQLJ statements to your Java programs that use embedded SQL Data Definition Language (DDL) statements. DDL statements are used to create and modify database objects, and consist of statements such as CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, CREATE SQUENCE, and so forth.

These columns reference numerous SQL scripts, source code, and other files that are available for download at O'Reilly's Web site. For more specific information about the files used, or to get an introduction to SQLJ, read my first column, "Setting Up Your Environment to Develop SQLJ Programs."

Using Data Definition Language Statements

In an SQLJ program, you execute a DDL statement just as you would any other SQL statement - you enclose it within a #sql token. The following example illustrates the use of the CREATE TABLE statement to create a table named addresses that will be used to store customer addresses:

#sql {
  CREATE TABLE addresses (
    id           NUMBER
      CONSTRAINT addresses_pk PRIMARY KEY,
    customer_id  NUMBER
      CONSTRAINT addresses_fk_customers
      REFERENCES customers(id),
    street       VARCHAR2(255) NOT NULL,
    city         VARCHAR2(255) NOT NULL,
    state        CHAR(2) NOT NULL,
    country      VARCHAR2(255) NOT NULL
  )
};

Transactions

In my previous column, I explained the concept of database transactions, and how to use the SQL COMMIT statement to make any changes you make to the database permanent. I also showed how to use the ROLLBACK statement to undo changes made in a transaction. The same concepts apply to SQL statements executed from your SQLJ programs.

To issue the SQL COMMIT and ROLLBACK statements in a SQLJ statement, use the following syntax:

#sql { COMMIT [WORK] };
#sql { ROLLBACK [WORK] };

The syntax elements are as follows:

COMMIT

Commits a transaction, making the changes permanent.

ROLLBACK

Rolls back a transaction, returning the database to the state it was in when the transaction first began. The effects of all SQL statements issued during the transaction will be erased.

WORK

An optional word that is part of the supported SQL syntax.

The following example performs an INSERT statement, followed by a ROLLBACK statement:

#sql {
  INSERT INTO
    customers (id, first_name, last_name, dob, phone)
  VALUES
    ('7', 'John', 'Smith', '01-JAN-1970', '650-555-1212')
};

#sql { ROLLBACK };

In this example, one row will be inserted into the customers table. Then that insert will be undone as a result of the ROLLBACK statement.

Related Reading

Java Programming with Oracle SQLJ
By Jason Price

Auto-Commit

By default, you must issue a COMMIT statement at the end of each transaction in order to make the changes permanent. However, it is also possible to have SQLJ automatically perform a COMMIT after each DML statement. This can be convenient if your transactions consist of only one statement, or if you don't want to worry about transactions at all. This functionality is referred to as auto-commit, and it may be enabled when you initially connect to a database.

To enable auto-commit, you make use of an optional fourth parameter to the oracle.sqlj.runtime.Oracle class' connect() method; this boolean true/false parameter indicates whether the auto-commit feature is enabled, and defaults to false. You enable auto-commit by setting it to true. The following is an example that shows you how to set auto-commit using the Oracle.connect() method:

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

All SQL statements that you issue via this connection will be implicitly followed by a COMMIT. In other words, each statement will be a transaction in itself. You don't need to worry about sending COMMIT statements to the database, because SQLJ will do that for you.

The auto-commit feature may also be enabled when creating a default connection context object. Remember, the auto-commit feature is disabled by default, which means that you must, by default, commit or rollback each transaction explicitly. Commits are only automatic when auto-commit is enabled.

You have now seen how to write SQLJ statements that connect to a database schema, and that contain embedded SQL DML, DDL, and transaction control statements. The following section contains a complete SQLJ program that illustrates the use of such statements.

Pages: 1, 2

Next Pagearrow