Todays Objectives

1
Todays Objectives

• Object Design for RDB
• JDBC
• Creating Database Connections in JDeveloper

2
ISMG 6020 Employee Class Hierarchy
3
Convert OO Design to Database Design

• Create an entity for each class.
• Develop whole-part relationships by using a
  foreign key to connect the whole entity and the
  part entity or, place the attributes of the part
  entity in columns of the whole entity.
• Develop association relationships using foreign
  keys.
• Develop child-parent (inheritance) relationships
  by connecting the child and parent entities using
  foreign keys. Alternatively, collapse the
  hierarchy into a single entity, introducing
  "object type" attributes.

4
Map the Object to RDB

• Define a class that corresponds to the table
• Class Methods -- for example
• Employee.updateRow(emp) -- SQL update
• Employee.insertRow(emp) -- SQL insert
• Employee.dropRow(key) -- SQL delete
• Employee.getRow(key) -- SQL select, returns emp
• Define a class that corresponds to a row
• Object Methods -- for example
• emp.store ()-- SQL update
• emp.insert ()-- SQL insert
• emp.delete ()-- SQL delete
• emp.load (key) -- SQL select
• One-to-one correspondence between object
  attributes and entity attributes.

5
JDBC
Client
Database
Database
6
JDBC for Dept Table

• Dept Table
• DeptNo -- Two digit integer
• DeptName -- String e.g. Accounting
• DeptLocation -- String e.g. New York
• Dept Class Methods
• connect() -- create network connection to
  database
• createTable() -- create dept table schema
• insertRow(int id, String name, String location)
  -- create a new row in the dept table
• commit() -- commits database changes
• close() -- closes the network connection to
  database
• displayAllRows() -- display the contents of the
  dept table

7
Database Connect to RDB on Ouray

• // SETTINGS TO ACCESS REMOTE DATABASE
• import java.sql.
• class Dept
• private static final String driver
  "oracle.jdbc.driver.OracleDriver"
• private static final String url
  "jdbcoraclethin_at_ouray.cudenver.edu1521oracle"
  
• private static final String username
  "jgerlach"
• private static final String password
  "ora999999999"
• private static Connection connection
• private static Statement statement
• private static PreparedStatement
  preparedInsertStatement
• Your oracle username is same as ouray username
• Your oracle password is ora followed by student
  number

8
main

• public static void main(String args)
• Dept.connect()
• Dept.createTable()
• Dept.insertRow(10, "Accounting", "New York")
• Dept.insertRow(20,"Research","Dallas")
• Dept.insertRow(30,"Sales","Chicago")
• Dept.insertRow(40,"Operations","Boston")
• Dept.commit()
• Dept.close()
• Dept.connect()
• Dept.displayAllRows()
• Dept.close()

9
connect

• public static void connect()
• try
• Class.forName(driver)
• System.out.println("JDBC Driver loaded
  successfully")
• catch(ClassNotFoundException cnfe)
  cnfe.printStackTrace()
• try
• connection DriverManager.getConnection(url,use
  rname,password)
• connection.setAutoCommit(false)
• statement connection.createStatement()
• preparedInsertStatement
  connection.prepareStatement("INSERT INTO
  DEPT(DEPTNO,DNAME,LOC)VALUES(?,?,?)")
• System.out.println("Connection established
  successfully")
• catch(SQLException sqle)
  sqle.printStackTrace()

10
Create Table Schema

• public static void createTable()
• try
• statement.executeUpdate("DROP TABLE
  DEPT")
• System.out.println("DEPT Table Deleted
  Successfully.")
• catch(SQLException sql) sql.printStackTrace()
  
• try
• statement.executeUpdate("CREATE TABLE
  DEPT(DEPTNO NUMBER(2)
• PRIMARY KEY,DNAME VARCHAR2(14),LOC
  VARCHAR2(13))")
• System.out.println("DEPT Table Created
  Successfully.")
• catch(SQLException sqle)
  sqle.printStackTrace()

11
Setting Database Constraints

• statement.executeUpdate("CREATE TABLE EMP(EMPNO
  NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(10), JOB
  VARCHAR2(9),MGR NUMBER(4), HIREDATE DATE,SAL
  NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2)
  CONSTRAINT FK_DEPTNO REFERENCES DEPT)")

12
Parameterize a Query

• Method One (Statement)
• Statement mySQLstmt connection.createStatement()
  
• String limit "50" // or int
  limit 50
• String query "SELECT FROM DEPT WHERE DEPTNO gt
  '"limit"'"
• ResultSet rs mySQLstmt.executeQuery(query)
• Method Two (Prepared Statement)
• String query "SELECT FROM DEPT WHERE DEPTNO gt
  ?"
• PreparedStatement mySQLstmt connection.prepareSt
  atement(query)
• mySQLstmt.setInt(1, 50) //
  substitutes int value
• mySQLstmt.setString(1,"50") // substitutes
  string value
• ResultSet rs mySQLstmt.executeQuery()

13
Method One Coding

• String limit "50"
• String t1 "SELECT FROM DEPT WHERE DEPTNO gt "
• String sq "'" // single quote
• String query1 t1 sq limit sq
• System.out.println(query1)
• String query2 "SELECT FROM DEPT WHERE DEPTNO
  gt '"limit"'"
• System.out.println(query2)
• Output
• SELECT FROM DEPT WHERE DEPTNO gt '50'
• SELECT FROM DEPT WHERE DEPTNO gt '50'

14
Database Update

• Update -- Method One Coding
• Statement mySQLstmt connection.createStatement()
  
• String query "UPDATE DEPT SET DNAME
  '"newname"' WHERE DEPTNO '"deptno"'"
• mySQLstmt.executeUpdate(query)
• con.commit()
• Insert -- Method Two Coding
• preparedInsertStatement connection.prepareStatem
  ent("INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(?,?,
  ?)")
• preparedInsertStatement.setInt(1, id)
• preparedInsertStatement.setString(2, name)
• preparedInsertStatement.setString(3, location)
• preparedInsertStatement.executeUpdate()
• con.commit()

15
Insert Row Into Table

• public static void insertRow(int id, String name,
  String location )
• try
• preparedInsertStatement.clearParameters()
• preparedInsertStatement.setInt(1, id)
• preparedInsertStatement.setString(2, name)
• preparedInsertStatement.setString(3,
  location)
• preparedInsertStatement.executeUpdate()
• System.out.println("Row inserted
  successfully")
• catch(SQLException sqle) sqle.printStackTrace(
  )

16
Commit and Close

• public static void commit()
• try
• connection.commit()
• System.out.println("Database modified
  sucessfully.")
• catch (SQLException e) e.printStackTrace()
• public static void close()
• try
• statement.close()
• preparedInsertStatement.close()
• connection.close()
• statement null
• preparedInsertStatementnull
• connection null
• System.out.println("Connection closed
  sucessfully.")
• catch (SQLException e) e.printStackTrace()

17
Display Contents of Dept Table

• public static void displayAllRows()
• ResultSet rs
• try
• String query "SELECT FROM DEPT"
• rs statement.executeQuery(query)
• while (rs.next())
• System.out.println(rs.getInt(1) " "
  rs.getString(2) " " rs.getString(3))
• catch (Exception e) System.out.println(e.getMe
  ssage()) e.printStackTrace() return

18
JDeveloper Setup

• Specify Oracle JDBC driver
• Develop and execute your database load programs.
• Within JDeveloper, create your Ouray database
  connection.
• Open database connection to view your database
  table schemas.

19
Add Oracle JDBC Driver to Project
20
(No Transcript)
21
For next class, bring a printout of JDeveloper's
documentation on "Developing Enterprise
JavaBeans." Be certain to drill-down and print
out all of the relevant pages shown here.