The program FundamentalExample1.sqlj is a complete
SQLJ program that uses SQLJ executable statements and host expressions to
access the fundamental_user database schema. The program performs the following tasks:
Connects to the fundamental_user schema using the
Oracle.connect() method.
Adds a new row to the customers table using an INSERT statement.
Uses a SELECT statement to retrieve the column values for the new row that was added to the customers table, and then displays those values.
Removes the new row from the customers table using a DELETE
statement.
Permanently records the transaction, which consists of the INSERT and
DELETE statements, using a COMMIT statement.
Modifies the price column of a row in the products table, using an UPDATE statement.
Uses a SELECT statement to retrieve the column values for the modified row in the products table, and then displays those values.
Undoes the transaction, which consists of the UPDATE statement, using a
ROLLBACK statement.
Creates a new table named addresses using a CREATE TABLE statement.
Drops the addresses table using a DROP TABLE statement.
Disconnects from the database using the Oracle.close() method from within a finally block.
Here's the code for FundamentalExample1.sqlj (you can also download this code here):
/*
The program FundamentalExample1.sqlj illustrates how to
connect to a database, how to embed SQL DML operations
in SQLJ executable statements, and how to use host
expressions.
*/
// import required packages
import java.sql.*;
import oracle.sqlj.runtime.Oracle;
public class FundamentalExample1 {
public static void main(String [] args) {
try {
Oracle.connect(
"jdbc:oracle:thin:@localhost:1521:orcl",
"fundamental_user",
"fundamental_password"
);
// add a new customer
int customer_id = 6;
String first_name = "Jerry";
String last_name = "Fieldtop";
Date dob = new Date(80, 1, 1);
String phone = "650-555-1222";
#sql {
INSERT INTO
customers (id, first_name, last_name, dob, phone)
VALUES
(:customer_id, :first_name, :last_name, :dob, :phone)
};
// display new customer
#sql {
SELECT
first_name, last_name, dob, phone
INTO
:first_name, :last_name, :dob, :phone
FROM
customers
WHERE
id = :customer_id
};
System.out.println("Customer with id " + customer_id +
" has the following details:");
System.out.println(" First name: " + first_name);
System.out.println(" Last name: " + last_name);
System.out.println(" DOB: " + dob);
System.out.println(" Phone: " + phone);
// delete the customer
#sql {
DELETE FROM
customers
WHERE
id = :customer_id
};
// commit the transaction
#sql { COMMIT };
// update the first product price
int product_id = 1;
double product_price = 11.25;
#sql {
UPDATE
products
SET
price = :product_price
WHERE
id = :product_id
};
// display the first product
int type_id = 0;
String name = null;
String description = null;
double price = 0.0;
#sql {
SELECT
type_id, name, description, price
INTO
:type_id, :name, :description, :price
FROM
products
WHERE
id = :product_id
};
System.out.println("Product with id " + product_id +
" has the following details: ");
System.out.println(" Type id: " + type_id);
System.out.println(" Name: " + name);
System.out.println(" Description: " + description);
System.out.println(" Price: " + price);
// rollback the update
#sql { ROLLBACK };
// create a table to hold 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
)
};
System.out.println("Successfully created addresses table.");
// drop the addresses table
#sql {
DROP TABLE addresses
};
} catch ( SQLException e ) {
System.err.println("SQLException " + e);
} finally {
try {
Oracle.close();
} catch ( SQLException e ) {
System.err.println("SQLException " + e);
}
}
} // end of main()
}
To translate and compile the FundamentalExample1.sqlj program, type the following command at your operating system command prompt:
sqlj FundamentalExample1.sqlj
DML Statements
Database Connections and SQLJ Statements
Setting Up Your Environment to Develop SQLJ Programs
The sqlj command-line utility translates the FundamentalExample1.sqlj file into a file named FundamentalExample1.java that contains calls to the SQLJ runtime library. Next, the sqlj command-line utility calls the Java complier (javac) to compile the FundamentalExample.java file into the class file FundamentalExample.class. My first column in this series discusses the sqlj command-line utility and this translation process in detail. When you execute the resulting FundamentalExample class, you should get the following output:
Customer with id 6 has the following details:
First name: Jerry
Last name: Fieldtop
DOB: 1980-02-01
Phone: 650-555-1222
Product with id 1 has the following details:
Type id: 1
Name: Beyond Understanding
Description: The frontiers of human knowledge
Price: 11.25
Successfully created addresses table.
I hope you've enjoyed reading this series on learning SQLJ. For more information on SQLJ, pick up a copy of Java Programming with Oracle SQLJ.
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.