JDBC/Oracle tutorial

1
JDBC/Oracle tutorial

• Using Oracle
• on NJIT Computers
• by George Blank, Yong Hong Wu,
• Luv Tulsidas, and Bijal Desai

2
Topics covered

• Resources
• Setup Oracle environment on NJIT
• Install Oracle JDBC driver
• Introduction to Oracle 9
• Oracle SQLPlus Basics
• Introduction to JDBC
• JDBC Basics
• A sample JDBC program
• Summary
• References

3
Before 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.

4
Accessing 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/Ora
  cle_on_prophet_njit_edu.htmlADS 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
  (sys_at_oak.njit.edu)
• For help, go to http//web.njit.edu/ and select
  Databases from the menu on the left.

5
Accessing 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/account
  s/afs.phpfor information on UCS.

6
Testing 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.

7
Test Program (1)

• Test.javaimport
  java.sql.class Testpublic static void main
  (String args )throws SQLException// Load
  the Oracle JDBC driverDriverManager.registerDrive
  r(new oracle.jdbc.driver.OracleDriver())

8
Test Program (2)

• String url "jdbcoraclethin_at_prophet.njit.edu1
  521course"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

9
Test Program (3)

• // Connect to the databaseConnection conn
  DriverManager.getConnection (url,"username","passw
  ord")// Create a StatementStatement stmt
  conn.createStatement ()// Select the SYSDATE
  column from the dual tableResultSet rset
  stmt.executeQuery ("select SYSDATE from dual")

10
Test 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()

11
Execute 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.

12
Errors

• 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.

13
Introduction to Oracle

• Oracle is an Object Relational Database
  Management System(ORDBMS).
• It offers capabilities of both relational and
  object-oriented database systems.

14
Tools 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 -For
  ms -Reports

15
Introduction to SQL

• SQL was invented and developed by IBM in early
  1970s. SQL stands for Structured Query Language.
• Oracles 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(Contd)

• 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
SQLPlus

• SQLPlus is an Oracle specific program which
  accepts SQL commands and PL/SQL blocks and
  executes them. SQLPlus enables manipulation of
  SQL commands and PL/SQL blocks. It performs many
  additional tasks as well.

19
Oracle 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 NJITs Prophet account.

20
Oracle Internal Datatypes

• Character datatypes
• - char datatype
• - varchar2 datatype
• - Long datatype
• Number datatype
• Date datatype
• Raw datatype
• Long raw datatype
• LOB datatype

21
Data Definition Language

• Create tables
• SQLgt CREATE TABLE lttable_namegt
• ( ltcolumn1_namegt ltdata_typegt
  ltnotnullgt,
• ltcolumn2_namegt ltdata_typegt ltnotnullgt,
• . . . .
• CONSTRAINT pk_name PRIMARY KEY
  (column_name)
• CONSTRAINT fk_name FOREIGN KEY
  (column_name))
• REFERENCE name1(name2) ON DELETE
  CASCADE)
• Alter the existing table
• SQLgt ALTER TABLE lttable_namegt MODIFY/ADD
  (column definition)

22
Data Definition Language(Contd)

• When there is no further use of records in a
  table and the structure has to be retained, then
  the records alone can be deleted. SQLgtTRUNCATE
  table lttable_namegt
• Drop a table
• SQLgtDROP lttable_namegt

23
Data manipulation Language

• Insert a tuple into a table
• SQLgtINSERT lttable_namegt VALUES ( value_1,
  value_2,
• value_3 ..)
• Request for information stored in a table
• SQLgt SELECT column_names FROM table_name
• Change the existing records in the table
  
  
  SQLgtUPDATE lttable_namegt SET ltfieldgtvalue,..
  WHERE condition
• Delete the rows in the table
• SQLgtDELETE FROM lttable_namegt WHERE condition

24
Transaction Control Language

• Transaction Changes can be made permanent to a
  database only by committing. Commit command is
  used to end a transaction and make the changes
  permanent.
• SQLgtCOMMIT
• Savepoints are like markers to divide a very
  lengthy transaction to smaller ones.
• SQLgt SAVEPOINT savepoint_id

25
Transaction Control Language(Contd)

• Rollback command is used to undo the work done in
  the current transaction.
• SQLgt ROLLBACK
• SQLgt ROLLBACK TO SAVEPOINT savepoint_id

26
Data Control language

• Grant privilege command Object privileges can be
  granted to others using the SQL command GRANT
• SQLgtGRANT privileges onltobject_namegt to
  ltusernamegt
• To withdraw the privilege that has been granted
  to a user, we use REVOKE command.
• SQLgtREVOKE privileges onltobject_namegt from
  ltusernamegt

27
Introduction to JDBC

• What is JDBC ? JDBC is a Java API for
  executing SQL statements. It consists of a set of
  classes and interfaces written in Java
  programming language. JDBC provides a standard
  API for tool/database developers and makes it
  possible to write database applications using
  pure Java API.

28
What Does JDBC Do ?

• JDBC makes it possible to do three things
• Establish a connection with a database
• Send SQL statements
• Process the results.

29
JDBC Driver Types

• The JDBC Drivers that we are aware of at this
  time fit into one of four categories 1)
  JDBC-ODBC bridge plus ODBC driver 2)Native-API
  partly_Java driver 3)JDBC-Net pure Java
  driver 4)Native-protocol pure Java driver
• Categories 3 and 4 are the preferred way to
  access databases from JDBC.

30
JDBC Basics

• First you need to establish a connection with
  Oracle database. This involves two steps (1)
  load the driver (2) make the connection.
• To load the driver
• for NJIT Oracle account
• DriverManager.registerDriver(new
  oracle.jdbc.driver.OracleDriver())

31
JDBC Basics (contd)

• Make a connection to have the appropriate driver
  connect to Oracle by calling
• Connection Con DriverManager.getConnection(url,
  userID, password)
• This method returns an open connection you can
  use to create JDBC statements that pass your SQL
  statements to the Oracle.
• For NJIT Oracle account
• String url "jdbcoraclethin_at_prophet.njit.edu1
  521course

32
JDBC Basics (contd)

• The second step is to create a Statement object
  to send SQL statement to Oracle. It takes an
  instance of an active connection to create a
  Statement object. A Statement has two methods,
  executeUpdate() and excuteQuery().
• Statement stmt con.createStatement()
• ResultSet rs stmt.executeQuery(query_name)
  
• stmt.executeUpdate(DDL_statement)
• ExcuteQuery() executes a prepared SQL statement
  and returns the result set in a ResultSet object.

33
JDBC Basics (contd)

• ExecuteUpdate() is used for updates and DLL
  statements only. Nothing should be returned for
  executeUpdate() method. A SQL exception will be
  thrown if executeUpdate() returns anything, or
  executeQuery() returns nothing.
• The third step is to create an instance of
  ResultSet to hold query results, as show below.
• ResultSet rs stmt.executeQuery(query_name)
• ResultSet has several useful methods

34
JDBC Basics (contd)

• next() method works like a pointer moving from
  the beginning of the result set towards the end.
  Each time it is invoked, the next row becomes the
  current row
• getXXX() method, where XXX is data type,
  retrieves the value in each column of the result
  set.
• The following code accessed the values in the
  current row of ResultSet rs and prints out the
  coffee name and price.
• String query "select COF_NAME, PRICE from
  COFFEES"
• ResultSet rs stmt.executeQuery(query)

35
JDBC Basics (contd)

• while (rs.next())
• String s rs.getString(Coffee
  Name")
• Float f rs.getFloat("Price")
• System.out.println(s " " f)
• Using Prepared Statements
• Prepared Statement object contains a
  precomplied SQL statement, and it cost less
  excution time than Statement object.
• String query Update Customer set Phone ?
  
• 
  Where CID ?

36
JDBC Basics (contd)

• PreparedStatement updatePhone
  con.prepareStatement(query)
• UpdatePhone.setString(1, 991-5668)
• UpdatePhone.setInt(2, 4567)
• UpdatePhone.excuteUpdate()
• The above code will change the phone number
  for the customer whose ID is 4567. SetXXX()
  method takes two arguments, the first one
  indicates which question mark is to be set, the
  second one supplies value to it. After the
  setXXX() method, the SQL statement is equivalent
  to
• query Update Customer set Phone [phone]
• Where CID 4567

37
Summary

• JDBC makes it very easy to connect to DBMS and to
  manipulate the data in it.
• You have to install the oracle driver in order to
  make the connection.
• It is crucial to setup PATH and CLASSPATH
  properly

38
Very Important

• Make certain you close your connection
• con.close()
• If you end your program, but do not specifically
  close the connection, Oracle keeps a connection
  open. In April of 2002, Oracle had to be shut
  down and restarted due to hundreds of abandoned
  opened connections.
• In the corporate world, creating a problem that
  caused the database to be shut down would result
  in disciplinary action.
• The Database log tells who left the connection
  open.

39
Problems Encountered

• What causes the "No suitable driver" error?
• "No suitable driver" is an error that usually
  occurs during a call to the DriverManager.getConne
  ction method. The cause can be failing to load
  the appropriate JDBC drivers before calling the
  getConnection method, or it can be specifying an
  invalid JDBC URL--one that isn't recognized by
  your JDBC driver. Your best bet is to check the
  documentation for your JDBC driver or contact
  your JDBC driver vendor if you suspect that the
  URL you are specifying is not being recognized by
  your JDBC driver.

40
Problems Encountered

• What causes the "No suitable driver" error?
  (continued) Do not install or upgrade drivers in
  your AFS account or modify your .profile or
  .login files in an attempt to get JDBC to work.
  It is very strictly configured to work with only
  the particular version of the thin driver and
  Prophet. Students who tried upgrading the drivers
  or modifying their profiles have been totally
  unable to connect.

41
References

• White,Seth and Maydene Fisher.1999. JDBC API
  Tutorial and Reference, 2nd Ed. Addison-Wesley
• Graham Hamilton,Rick Cattell, Maydene Fisher.
  JDBC Database Access With Java, A Tutorial and
  Annotated Reference
• Holowczak, Richard. ORACLE SQLPLUS
• An Introduction and tutorial. At
  www.cimic.rutgers.edu/holowcaz/oracle/sqlplus.
• Instructions for Prophet can be found at
• http//web.njit.edu/ under Databases on the
  menu