JDBC/Oracle tutorialTopics coveredBefore InstallationAccessing Oracle 10g at NJITAccessing University Computing SystemsTesting JDBCTest Program (1)Test Program (2)Test Program (3)Test Program (4)Execute Test ProgramErrorsIntroduction to OracleTools of OracleIntroduction to SQLIntroduction to SQL(Cont’d)Benefits of SQLSQL*PlusOracle BasicsOracle Internal DatatypesData Definition LanguageData Definition Language(Cont’d)Data manipulation LanguageTransaction Control LanguageTransaction Control Language(Cont’d)Data Control languageIntroduction to JDBCWhat Does JDBC Do ?JDBC Driver TypesJDBC BasicsJDBC Basics (cont’d)Slide 32Slide 33Slide 34Slide 35Slide 36SummaryVery ImportantProblems EncounteredSlide 40References1NJITJDBC/Oracle tutorialUsing Oracle on NJIT Computersby George Blank, Yong Hong Wu, Luv Tulsidas, and Bijal Desai2Topics coveredResourcesSetup Oracle environment on NJITInstall Oracle JDBC driverIntroduction to Oracle 9Oracle SQL*Plus BasicsIntroduction to JDBCJDBC BasicsA sample JDBC programSummaryReferences3Before InstallationYou cannot connect to Prophet using JDBC unless you do it from AFS. This is for security.You must use the version of Java installed on AFS and the JDBC drivers in the [ORACLE_HOME]/jdbc directory.You use the Oracle Thin Driver (not the OCI Driver). This is an all Java driver. Every time Oracle or the JDK is upgraded, there may be some changes to this document.4Accessing Oracle 10g at NJITIf you have an AFS account and are registered for this class, you should automatically have a Prophet account with Oracle at NJIT. Access prophet using Aqua Data Studio. See information at http://web.njit.edu/info/limpid/Oracle_on_prophet_njit_edu.html#ADS You should have received your username and password in the email already. Use 'course' as Connection Identifier. If you don't have username and password, contact ([email protected])For help, go to http://web.njit.edu/ and select Databases from the menu on the left.5Accessing University Computing SystemsYou can get to UCS using SSHYou can use any system from afs1 to afs36See http://web.njit.edu/~gblank/Help.ppt for information on SSH and http://csd.njit.edu/accounts/afs.phpfor information on UCS.6Testing JDBCUsing your Prophet username and password, login to one of the afsnn computers to enter, compile and execute the Java program on the next four slides.7Test Program (1)***************Test.java*****************import java.sql.*;class Test{public static void main (String args [])throws SQLException{// Load the Oracle JDBC driverDriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());8Test Program (2)String url = "jdbc:oracle:thin:@prophet.njit.edu:1521:course";try {String url1 = System.getProperty("JDBC_URL");if (url1 != null)url = url1;} catch (Exception e) {// If there is any security exception, ignore it// and use the default}9Test Program (3)// Connect to the databaseConnection conn = DriverManager.getConnection (url,"username","password");// Create a StatementStatement stmt = conn.createStatement ();// Select the SYSDATE column from the dual tableResultSet rset = stmt.executeQuery ("select SYSDATE from dual");10Test Program (4)// Print the resultwhile (rset.next ())System.out.println (rset.getString (1));// Close the ResultSetrset.close();// Close the Statementstmt.close();// Close the connectionconn.close();}}11Execute Test ProgramSave the file and exit, i.e. if using pico type CTRL X and press yes.Compile the java code by typing the following at the command prompt: javac test.javaOnce the program is successfully compiled, execute it by typing:java testThe program will identify whether or not the connection to Oracle was successful.12ErrorsIf the program reports that your connection to oracle was unsuccessful or you receive java exceptions, it may be a problem specific to your AFS account.13Introduction to OracleOracle is an Object Relational Database Management System(ORDBMS).It offers capabilities of both relational and object-oriented database systems.14Tools of OracleThe tools provided by Oracle are so user friendly that a person with minimum skills in the field of computers can access them with ease.The main tools are: -SQL *Plus-PL/SQL-Forms-Reports15 Introduction to SQLSQL was invented and developed by IBM in early 1970’s. SQL stands for Structured Query Language.Oracle’s database Language is SQL, which is used for storing and retrieving information in Oracle.A table is a primary database object of SQL that is used to store data.16 Introduction to SQL(Cont’d) In order to communicate with the database, SQL supports the following categories of commands:-Data Definition Language- create, alter,drop commands.Data Manipulation Language- insert, select, delete and update commands.Transaction Control Language- commit, savepoint and rollback commands.Data Control Language- grant and revoke commands.17 Benefits of SQLNon-procedural language, because more than one record can be accessed rather than one record at a time.It is common language for all relational databases. In other words it is portable and it requires very few modifications so that it can work on other databases.Very simple commands for querying, inserting and modifying data and objects.18 SQL*Plus SQL*Plus is an Oracle specific program which accepts SQL commands and PL/SQL blocks and executes them. SQL*Plus enables manipulation of SQL commands and PL/SQL blocks. It performs many additional tasks as well.19Oracle Basics You must have an existing database instance before you can create an oracle relation (table). If you use NJIT Oracle account, you are already given a database instance when DBA opens the account for you. Note that the following discussion is generic, and not specific to NJIT’s Prophet account.20Oracle Internal DatatypesCharacter datatypes:- char datatype- varchar2 datatype- Long datatypeNumber datatypeDate datatypeRaw datatypeLong raw datatypeLOB datatype21Data Definition LanguageCreate tables SQL> CREATE TABLE <table_name> ( <column1_name> <data_type> <[not]null>, <column2_name> <data_type> <[not]null>, . . . . CONSTRAINT pk_name PRIMARY KEY (column_name); CONSTRAINT fk_name FOREIGN KEY (column_name)); REFERENCE name1(name2) ON DELETE CASCADE);Alter the existing table SQL> ALTER TABLE <table_name> MODIFY/ADD (column