Data Management Using Java (JDBC) In Oracle

This code shows how to use java technology (jdbc) in managing data in an Oracle database. The code and objects can be enhanced to suit the needs of applications, such as use of triggers, constraints etc. This will work on version Oracle 8i and higher. This shows how java and PL/SQL are used together in data management. In version 9i and higher, SQLJ can also be used. This code has been tested/implemented in Oracle 9i.

The code is divided into three steps:

1. Creation of database objects

2. Java Objects - code/class and

3. PL/SQL procedures and functions

This code basically shows how to create a table, execute DML such a SELECT, INSERT, UPDATE, DELETE and also to call a stored procedure with parameters using java class and PL/SQL code.

To make a call to the database from an external application, the code has to be modified to have a database connection (login information or proper connection string). This code uses the default or currently open connection.

1. Database Objects

CREATE TABLE customer
(customer_id	NUMBER(6),
customer_name	VARCHAR2(50),
customer_phone	VARCHAR2(20),
created_date	DATE DEFAULT SYSDATE,
updated_date	DATE);
CREATE SEQUENCE cust_seq
START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE update_customer(p_cust_id IN NUMBER) AS

BEGIN

UPDATE customer

SET customer_name = UPPER(customer_name),

updated_date = SYSDATE

WHERE customer_id = p_cust_id;

COMMIT;

EXCEPTION

WHEN OTHERS THEN RAISE;

END;

2. Java Objects - code/class

3. PL/SQL Code (Functions and Procedures)

For corrections or feedback, other useful links please contact webmaster

  74530