/* * Copyright (c) 2001, Mohamed Lokbani. * All Rights Reserved. */ import java.sql.*; import java.io.*; import java.util.*; /** * Ce programme crée des tables dans la base de donnée oracle. * Ce programme doit être exécuté en premier. * * Vous devez compléter les champs: * moncompte = votre compte usager * monpasse = votre mot de passe oracle * ou bien modifier le code et introduire ces paramètres sur la ligne * de commande. * * * @version 1.1 2001/12/09 * @since 1.0 * @author Mohamed Lokbani * */ public class db_agence { public static final String TableSejour = "Sejour"; public static final String TableClient = "Client"; public static final String TableActivite = "Activite"; public static final String TableStation = "Station"; public static final String mondriver = "oracle.jdbc.driver.OracleDriver"; public static final String monurl = "jdbc:oracle:thin:@titan:1521:a99"; public static final String moncompte = "xxxx"; public static final String monpasse = "xxxx"; public static void dropcommand(String latable, Connection lacon, Statement lesta) throws SQLException { try { String dropString = "DROP TABLE " + latable; lesta.executeUpdate(dropString); System.out.println(latable + " a été retirée"); dropString = "DROP TABLE " + latable; lesta.executeUpdate(dropString); System.out.println(latable + " a été retirée"); dropString = "DROP TABLE " + latable; lesta.executeUpdate(dropString); System.out.println(latable + " a été retirée"); dropString = "DROP TABLE " + latable; lesta.executeUpdate(dropString); System.out.println(latable + " a été retirée"); } catch (SQLException ex){ if ( lacon != null ){ lacon.rollback() ; lacon.setAutoCommit(true) ; } System.out.println("SQLException caught"); System.out.println("---"); while ( ex != null ){ System.out.println("Message : " + ex.getMessage()); System.out.println("SQLState : " + ex.getSQLState()); System.out.println("ErrorCode : " + ex.getErrorCode()); System.out.println("---"); ex = ex.getNextException(); } } } public static void createcommand(Statement s) throws SQLException { String createString = "CREATE TABLE " + TableStation + " (nomStation VARCHAR2 (30)," + " capacite NUMBER (10) NOT NULL," + " lieu VARCHAR2(30) NOT NULL," + " region VARCHAR2 (30)," + " tarif NUMBER (10,2) DEFAULT 0," + " CONSTRAINT cle_station PRIMARY KEY (nomStation)," + " CONSTRAINT cle_lieu_region UNIQUE (lieu, region)," + " CONSTRAINT nom_region" + " CHECK (region IN ('Ocean Indien'," + " 'Antilles', 'Europe'," + " 'Ameriques', 'Extreme Orient'))" + " )"; s.executeUpdate(createString); System.out.println("La table: " + TableStation + " a été créée"); createString = "CREATE TABLE " + TableActivite + " (nomStation VARCHAR2 (30)," + " libelle VARCHAR2(30)," + " prix NUMBER (10,2) DEFAULT 0," + " PRIMARY KEY (nomStation, libelle)," + " FOREIGN KEY (nomStation) REFERENCES " + TableStation + " ON DELETE CASCADE" + " )"; s.executeUpdate(createString); System.out.println("La table: " + TableActivite + " a été créée"); createString = "CREATE TABLE " + TableClient + " (id NUMBER (10)," + " nom VARCHAR2(30) NOT NULL," + " prenom VARCHAR2 (30)," + " ville VARCHAR2 (30) NOT NULL," + " region VARCHAR2(30)," + " solde NUMBER (10,2) DEFAULT 0 NOT NULL,"+ " PRIMARY KEY (id)" + " )"; s.executeUpdate(createString); System.out.println("La table: " + TableClient + " a été créée"); createString = "CREATE TABLE " + TableSejour + " (idClient NUMBER (10)," + " station VARCHAR2 (30)," + " debut NUMBER (10)," + " nbPlaces NUMBER (4) NOT NULL," + " PRIMARY KEY (idClient, station, debut)," + " FOREIGN KEY (idClient) REFERENCES " + TableClient + "," + " FOREIGN KEY (station) REFERENCES " + TableStation + " ON DELETE CASCADE" + " )"; s.executeUpdate(createString); System.out.println("La table: " + TableSejour + " a été créée"); } static public void CloseConnection(Connection c) throws SQLException { c.close(); } public static void main(String args[]) throws SQLException, Exception { Connection c = null ; try { Class.forName(mondriver).newInstance(); c = DriverManager.getConnection(monurl,moncompte,monpasse); } catch (ClassNotFoundException ex){ System.out.println(ex); } Statement s = c.createStatement() ; c.setAutoCommit(false) ; dropcommand(TableSejour,c,s); dropcommand(TableClient,c,s); dropcommand(TableActivite,c,s); dropcommand(TableStation,c,s); createcommand(s); c.commit() ; c.setAutoCommit(true) ; CloseConnection(c); } }