Chapter 3 : Oracle9iAS Building J2EE(tm) Applications

/*
*    Program Name:   ConnectToOracle.java
*
*    Purpose:        A simple Java application that
*                    demonstrates how to connect
*                    to an Oracle database using
*                    the Oracle Thin JDBC driver.
*/

package purchase.app;

// Step 1: Import the java.sql package
import java.sql.*;

public class ConnectToOracle
{
  public ConnectToOracle()
  {
  }

  public static void main(String[] args)
           throws SQLException {

    // Step 2: Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Step 3: Connect to the database
    // using the Oracle Thin JDBC drive
    // Use the following connection string to connect:
    // Connection conn = DriverManager.getConnection
    //                    ("jdbc:oracle:thin:@" +
    //                    yourDatabaseServer,
    //                    dbUserName, dbPassword);

    Connection conn = null;
    try {
         conn = DriverManager.getConnection
                        ("jdbc:oracle:thin:@" +
                           "data-i:1521:ORCL",
                            "Scott", "tiger");

         System.out.println("connected.");
    } // End try
    catch (SQLException e) {
       throw new SQLException ("Unable to Connect!!!"
                               +e.getMessage());
    }  //End catch
    finally {
        try {
             if (conn != null) conn.close();
        } catch (SQLException ex) {}
    } // End finally

    // Step 4: Please, close the connection
    conn.close();

  } // End of main()

} // End of ConnectToOracle class

-- Introduction Page XXIV : Oracle9iAS Building J2EE(tm) Applications
--
-- File Name: poexample.sql
--
-- This scripts create all necessary tables, constraints and sequences
-- for the Purchase Order example. See page xxiv
--
-- This script assumes that you created a database user and that an index
-- called INDX. Make sure that this user has a temp tablespace assigned.
--
-- Usage: sqlplus user/password
-- SQL> @poexample.sql
--
--
set termout on
set echo on
spool poexample.log
 

DROP TABLE DEPARTMENT CASCADE CONSTRAINTS;
CREATE TABLE DEPARTMENT(
deptno  NUMBER(5),
shortname VARCHAR2(6),
name         VARCHAR2(20));

DROP TABLE ACCOUNT CASCADE CONSTRAINTS;
CREATE TABLE ACCOUNT (
accountno  NUMBER(5),
projectno NUMBER(5),
deptno  NUMBER(5));

DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
CREATE TABLE EMPLOYEE(
employeeno NUMBER(7),
deptno  NUMBER(5),
type  VARCHAR2(30),
lastname VARCHAR2(30),
firstname VARCHAR2(30),
phone  VARCHAR2(10),
email  VARCHAR2(30));
 

DROP TABLE VENDOR CASCADE CONSTRAINTS;
CREATE TABLE VENDOR(
vendorno NUMBER(6),
name  VARCHAR2(30),
address  VARCHAR2(20),
city  VARCHAR2(15),
state  VARCHAR2(15),
zip  VARCHAR2(15),
country  VARCHAR2(15));

DROP TABLE PROJECT CASCADE CONSTRAINTS;
CREATE TABLE PROJECT (
projectno  NUMBER(5),
name   VARCHAR2(20),
start_date  DATE,
amt_of_funds  NUMBER);

DROP TABLE LINE_ITEM CASCADE CONSTRAINTS;
CREATE TABLE LINE_ITEM (
requestno NUMBER(10),
lineno  NUMBER(5),
projectno NUMBER(5),
quantity NUMBER(5),
unit  VARCHAR2(2),
cost         NUMBER(8,2),
actualcost NUMBER(8,2),
description VARCHAR2(30));

DROP TABLE PURCHASE_ORDER CASCADE CONSTRAINTS;
CREATE TABLE PURCHASE_ORDER (
requestno NUMBER(10),
employeeno NUMBER(7),
vendorno NUMBER(6));

DROP TABLE APPROVAL CASCADE CONSTRAINTS;
CREATE TABLE APPROVAL (
requestno NUMBER(10),
budgetofficer   NUMBER(7),
approved        CHAR(1),
reasons         VARCHAR2(30))
/

--
-- Add constraints to tables
--

ALTER TABLE ACCOUNT
  ADD CONSTRAINT accountno_pk PRIMARY KEY(accountno)
  USING INDEX TABLESPACE INDX;

ALTER TABLE DEPARTMENT
  ADD CONSTRAINT deptno_pk PRIMARY KEY(deptno)
  USING INDEX TABLESPACE INDX;

ALTER TABLE PROJECT
  ADD CONSTRAINT projectno_pk PRIMARY KEY(projectno)
  USING INDEX TABLESPACE INDX;

ALTER TABLE ACCOUNT
  ADD CONSTRAINT acc_deptno_fk
  FOREIGN KEY(deptno)
  REFERENCES DEPARTMENT(deptno);

ALTER TABLE EMPLOYEE
  ADD CONSTRAINT employeeno_pk PRIMARY KEY(employeeno)
  USING INDEX TABLESPACE INDX;

ALTER TABLE EMPLOYEE
  ADD CONSTRAINT emp_deptno_fk
  FOREIGN KEY(deptno)
  REFERENCES DEPARTMENT(deptno);

ALTER TABLE VENDOR
  ADD CONSTRAINT vendorno_pk PRIMARY KEY(vendorno)
  USING INDEX TABLESPACE INDX;

ALTER TABLE PURCHASE_ORDER
  ADD CONSTRAINT requestno_pk PRIMARY KEY(requestno)
  USING INDEX TABLESPACE INDX;

ALTER TABLE LINE_ITEM
  ADD CONSTRAINT lineno_pk
  PRIMARY KEY(requestno,lineno,projectno)
  USING INDEX TABLESPACE INDX;

ALTER TABLE LINE_ITEM
  ADD CONSTRAINT lineitem_requestno_fk
  FOREIGN KEY(requestno)
  REFERENCES PURCHASE_ORDER(requestno);

ALTER TABLE APPROVAL
ADD constraint requestno_fk FOREIGN KEY(requestno)
  REFERENCES PURCHASE_ORDER(requestno)
 /

--
-- Create Sequences
--

DROP SEQUENCE deptno_SEQ;
CREATE SEQUENCE deptno_SEQ
  START WITH 200
  INCREMENT BY 1;

DROP SEQUENCE projectno_SEQ;
CREATE SEQUENCE projectno_SEQ
  START WITH 300
  INCREMENT BY 1;

DROP SEQUENCE accountno_SEQ;
CREATE SEQUENCE accountno_SEQ
  START WITH 1000
  INCREMENT BY 1;

DROP SEQUENCE employeeno_SEQ;
CREATE SEQUENCE employeeno_SEQ
  START WITH 100
  INCREMENT BY 1;

DROP SEQUENCE vendorno_SEQ;
CREATE SEQUENCE vendorno_SEQ
  START WITH 400
  INCREMENT BY 1;

DROP SEQUENCE requestno_SEQ;
CREATE SEQUENCE requestno_SEQ
  START WITH 500
  INCREMENT BY 1;

DROP SEQUENCE lineno_SEQ;
CREATE SEQUENCE lineno_SEQ
  START WITH 1
  INCREMENT BY 1;

commit;

spool off

Exit to : Oracle Hints and Tips