Assignment

• One assignment (30% of course)– nothing for parallel programming

• Build a 3-tier system using Java, CORBA and JDBC

• Instructions on www.ecs.soton.ac.uk/notes/cm319• Due Tuesday 21st March 16:00.

CORBA and Databases• Servers tend not to work in isolation

– rely on a persistent data store

• Persistent store could be– standard files

– database• Object Oriented

• Relational

• Object-Relational

Client

Client

Server Persistent Store ?

Protocol ??Protocol: IIOP

And finally, the model is...

Client

Client

Server

Oracle SQL Server IBM DB2 ...

Protocol: JDBC

Protocol: IIOP

• This is a 3-tier model– 1st tier - client

– 2nd/middle tier - server

– 3rd tier - database

CORBA

JDBC

2-tier vs 3-tier (I)

• 3-tier benefits– Less network traffic

• Client can get processed results from server, rather than large amounts of raw data

– Flexible distribution of client/server logic• Computationally intensive work can be placed on high

performance servers (Thin/Fat Clients)

– Security Advantages• protocol between the tiers one and two is user defined, and can

use encryption

• designer can specify legal operations on data, rather than allowing uncontrolled access to the raw data

2-tier vs 3-tier (II)– Load balancing and scheduling

• server objects can be duplicated and distributed across multiple server platforms

• Middle tier logic can incorporate load balancing and scheduling

– Application Reuse• Distributed object technology allows the development of

reusable components with well defined interfaces

Writing a Java/JDBC program• Program accessing the database must perform the

following tasks:• Load the appropriate JDBC driver

• Connect to the database

• Issue SQL queries/updates to the database

• Process results returned by the database

• disconnect from the database

• We first consider a 2-tier model:

Client

Oracle SQL Server IBM DB2 ...

Protocol: JDBC

Step1 - Load the appropriate JDBC driver

• Each JDBC compliant database will have a driver– Java Class

– IBM DB2 driver - “COM.ibm.db2.jdbc.net.DB2Driver”

– mSQL driver - “COM.imaginary.sql.msql.MsqlDriver”

• Driver must be loaded by the client– recommended method is using “Class.forName”

method which explicitly loads and links specified driver

– e.g., Class.forName (“COM.ibm.db2.jdbc.net.DB2Driver”);

– => class must be installed on client

Step 2 - Connect to the Database• Identify the machine and database to connect to• Database connections are based on URL naming

– jdbc:<subprotocol>:<subname>• jdbc - indicates protocol being used

• subprotocol - driver name or connectivity mechanism (db2/msql/odbc)

• subname - machine name and port number of database machine, and the database name

– //hostname:port/databaseName

– e.g., a complete JDBC URL:String url=“jdbc:db2://wombat.ecs.soton.ac.uk:1114/sample”;

• Connection to database made by:String userid = “yourUserID”;

String passwd = “yourPassword”;

Connection myDbCon = Drivermanager.getConnection (url, userid, passwd);

Step 3 - Issuing Queries to Database (I)

• To issue a query need a Statement object (simple statement)Statement myStmt = myDbCon.createStatement();

• Alternative statements are “prepared” and “callable”• Statement provides 3 methods for executing SQL

statements depending on the returned value:– ResultSet myResults = myStmt.executeQuery(String);

• returns a table of results (“select” statements)

– int numResults = myStmt.executeUpdate(String);• returns number of rows affected (“insert”, “update”, “delete”…)

– boolean resultSetExists = myStmt.execute(String);• not commonly used - if query could return multiple resultSet’s

Step 3 - Issuing Queries to Database (II)

• E.g., ResultSet myRs = myStmt.executeQuery (“Select title,

author from BOOKS where price < 10”);

int numChanges = myStmt.executeUpdate (“UPDATE BOOKS SET price = 10.99 WHERE ISBN = ‘ISBN0-13-766957-7’”);

• Note that can only have one “ResultSet” object per Statement– => cannot interleave reading multiple resultSets with a

single statement

• By default, all executeUpdate methods commit after executing each statement (auto-commit)– auto-commit can be switched off in Connection class,

but it is then the users responsibility to commit changes

Step 4 - Processing the results

• ResultSet contains rows and columns of data• Access provided by:

– getXXX () methods to access column data in current row

• mapping between datatypes in SQL and Java

• Access data given a column name or number (starting at 1)

• Column names are not case sensitiveString s = rs.getString (“title”);

String s = rs.getString (2);

– next() method - moves cursor to next row of results• returns false when no more results

T I N Y I N T

S M A L L I N T

I N T E G E R

B I G

N T

R E A L

F L O A T

D O U B L E

D E C I

M A L

N U M E R I C

B I T

C H A R

V A R C H A R

L O N G V A R C H A R

B I N A R Y

V A R B I N A R Y

L O N G V A R B I N A R Y

D A T E

T I

M E

T I

M E S T A M P

getByte X x x x x x x x x x x x x

getShort x X x x x x x x x x x x x

getInt x x X x x x x x x x x x x

getLong x x x X x x x x x x x x x

getFloat x x x x X x x x x x x x x

getDouble x x x x x X X x x x x x x

getBigDecimal x x x x x x x X X x x x x

getBoolean x x x x x x x x x X x x x

getString x x x x x x x x x x X X x x x x x x x

getBytes X X x

getDate x x x X x

getTime x x x X x

getTimestamp x x x x X

getAsciiStream x x X x x x

getUnicodeStream x x X x x x

getBinaryStream x x X

getObject x x x x x x x x x x x x x x x x x x x

Step 5 - Closing resources

• Should close statements and connection after use– Database permits only a limited number of open

statements and connectionsmyrs.close();

myStmt.close();

myDbCon.close();

Assignment

Using Java, build a 3-tier system consisting of a graphical client that uses CORBA to invoke methods on a middle layer which in turn uses JDBC to connect to a database. The core system will be minimal, based on given IDL and with a specified database structure. You must implement this minimal system and also extend it in some way.

Assignment: Minimal System

The minimal system contains a database with a single table consisting of two columns of floating point numbers. The IDL is provided and contains CORBA operations that control connection and disconnection from the database along with an operation that mimics "business logic" by performing a certain check on the data. The graphical client provides buttons that control these operations and a way of displaying the numerical result of the check.

Assignment: Minimal System: IDL

module CM319{ interface minimal { boolean Connect(in string url); void Disconnect(); double Check(); };};

Assignment: Extended System

You must also extend this minimal system in some way. This extension must involve some change to the IDL and for example:

• A more functional middle layer

• Use of alternative ORB's, programming languages or JDBC drivers

Use of a nameserver

An Applet client

A factory pattern.

Building 3-tier applications (I)

• No fixed rule for how the middle-tier accesses the database. 3 Options:

• single database connection– middle-tier accesses the database through a single

connection

– if clients require access to database, it is via a shared, synchronized object model

– applicable when only the server requires direct access to the database. Clients may access a shared object that contains processed database information

• e.g., Server uses database to store marks, and creates a local object to compute standard deviation, average, etc functions. As clients request the same information, clients issue requests to the shared object.

Building 3-tier applications (II)

• multiple database connections, created by server on startup (Connection Pool)– server creates a pool of connections on startup

• synchronized object holds all connections

– clients are allocated a connection when needed and return connection to the pool when no longer needed

– clients could be queued or refused a connection if no free database connections exist (or pool could be expanded)

– issues• clients all have same access privileges, but

• no overhead in creating a database connection - best performance

• supports random database queries by clients

Building 3-tier applications (III)

• multiple database connections, created on demand for clients– middle-tier uses factory model to create a database

connection object for each client

– used when each client needs to access the database directly with client specific privileges

– issues• most flexible, but most expensive - high overhead in creating

connections initially

• server could also define and ensure that the number of connections do not exceed a specified number (Similar to Pooling connections)