Java and Databases. COMP514 Distributed Information Systems. Java Database Connectivity. Standards and utilities. Java and Databases

(1)

COMP 514

COMP514

Distributed Information Systems

Java Database Connectivity

COMP 514

Java and Databases

• One of the problems in writing Java, C, C++, …, applications is that the programming languages cannot provide persistence of data (at least on their own)

Example: Java (and applets) can be used for developing simple graphical interfaces where an user can input data and retrieve other data. Unfortunately if we stop the Java application all that the data that user has provided are lost.

• Solution: Persistence is available by storing and accessing data held by a database management system.

COMP 514

Java and Databases

Problem: how can my Java program access different kinds of databases?

Java

programs Relational Database Client

Client

COMP 514

Standards and utilities

Without standardized database interfaces, integrating diverse data would be extraordinarily difficult.

Standardized interfaces include ODBC and JDBC.

These interfaces provide drivers that act as intermediary between the application and the DBMS.

The interfaces process our “Java requests” and submit SQL statements to a data source.

(2)

COMP 514

Java and Databases

Java programs Client JDBC

Client

Relational Database

COMP 514

Database Access in Java - JDBC

JDBC provides:

• SQL access

• a driver manager

Vendors provide drivers to plug into the managers:

• Oracle vendors will provide Oracle drivers

• MySQL vendors will provide MySQL drivers

• …...

The programmer (us) has to provide

• The location of the database

• The name of a Driver.

• These two things are provided in the form of a URL

Building an Application

• The Driver Manager load an appropriate Driver

• The Connectionis made

• Queriescan then be executed as required

• Data is returnedas objects that can be manipulated by the Java program

Connecting to different databases

All the methods that we will use to query/modify a database are the same, regardless of the type of database involved.

IE. JDBC provides an homogeneous standard for accessing different kinds of databases

But… drivers are different. So, first we have to learn how to connect to different databases:

• Access

• Oracle

(3)

COMP 514

Specifying the Connection

We have to provide:

• The location of the database (URL)

• Driver name

A database URL has the following form

• jdbc:<subprotocol>:<subname>

We need to know:

• the subprotocol. This enables the driver manager to identify the driver

• the subname. The subname can vary, depending on the driver. It usually includes information like the name of the database, the location of the database, the port used in the communication, your username and password, etc etc

COMP 514

Specifying the Connection in Oracle

Oracle uses interfaces provided by the Oracle company.

There are different kinds of interfaces for different usages. We will see the “thin” interface.

Oracle database Java thin

program

Database connectivity

COMP 514

Specifying the Connection in Oracle

Using the thin driver, the syntax for the URL is jdbc:oracle:thin:@<data-source-name>

<data-source-name> is composed by:

• the name of the machine where the Oracle server runs;

• the port used by the JDBC connection;

• the name of the database schema we want to access In our case:

jdbc:oracle:thin:@indian:1521:c514 jdbc:oracle:thin:@indian.csc.liv.ac.uk:1521:c514

COMP 514

Specifying the Connection in Oracle

So, assume I have created an Oracle database called c514. Can I connect to it?

No, first we have to tell the java compiler where the thin drivers and the appropriate Java libraries are.

It depends, of course, on particular configuration of the system. See details on the next two slides for our particular setting

(4)

COMP 514

Specifying the Connection in Oracle

On a linux machine 1) open your .profile file

2) Add (if it is not already there):

# Oracle setup

export ORACLE_BASE=/net/oracle/app/oracle

export ORACLE_HOME=/net/oracle/app/oracle/product/8.1.7 export PATH=${PATH}:${ORACLE_HOME}/bin

export SHLIB_PATH=${ORACLE_HOME}/lib export TWO_TASK=DVLP.csc.liv.ac.uk

COMP 514

Specifying the Connection in Oracle

# JDBC setup

CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/classes111.zip:$ORA CLE_HOME/jdbc/lib/nls_charset11.zip

export CLASSPATH

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdbc/lib

Log out and log in again

Connecting to a database

Assume that I have created a database called c512 (Access or Oracle, it doesn’t matter) containing the following relations:

Beers(name, brewery)

Bars(name, addr, licence, barC#) Drinkers(name, gender) Likes (drinker, beer) Sells(beer, bar, price) Frequents(drinker, bar)

How can I connect and query it with a Java program?

SQL Statement Execution in Java

Four steps corresponding to four primary classes from the java.sql package:

• load a driver

– (java.sql.DriverManager)

• connect to the DB – (java.sql.Connection)

• create a SQL statement – (java.sql.Statement)

• execute a SQL statement – (java.sql.ResultSet)

(5)

COMP 514

An example

We want a Java program that executes the query:

SELECT name FROM Bars

WHERE licence = ‘pub’

COMP 514

The Java code (Oracle version)

// You need to import the java.sql package to use JDBC import java.sql.*;

class myfirstquery {

public static void main (String args []) throws SQLException

{

// Load the ORACLE driver

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

String url = "jdbc:oracle:thin:@indian:1521:c514";

String username = ”yourusername";

String password = ”yourpassword";

String query = "SELECT name FROM Bars WHERE license = ‘pub’"

COMP 514

The Java code (Oracle version)

// Connect to the database Connection conn =

DriverManager.getConnection (url, username, password);

// Create a Statement

Statement SelectStatement = conn.createStatement ();

// Execute the SQL statement

ResultSet rset = SelectStatement.executeQuery (query);

// Iterate through the result and print the bars names while (rset.next ())

System.out.println (rset.getString (1));

}}

COMP 514

The Java code explained

// You need to import the java.sql package to use JDBC import java.sql.*;

// Load the ODBC driver

Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);

// Load the ORACLE driver

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

• java.sql is the package we have to use in order to access SQL databases.

• Load the appropriate driver.

(6)

COMP 514

The Java code explained

String url = "jdbc:odbc:mydatabase";

String username = "";

String password = "";

String url = "jdbc:oracle:thin:@indian:1521:c302";

String username = ”yourusername";

String password = ”yourpassword";

// Connect to the database Connection conn =

DriverManager.getConnection (url, username, password);

• Connect to the database

COMP 514

The Java code explained

Statement SelectStatement = conn.createStatement ();

• Associate one (or more) statements with this connection;

ResultSet rset = SelectStatement.executeQuery (query);

• Execute the statement(s);

The Java code explained

// Iterate through the result and print the bars names while (rset.next())

System.out.println (rset.getString (1));

• rset contains the table which is the result of the query.

• This object contains a reference to the rows of this table.

We can use .next() to processe all the rows in rset one by one.

• Also we can use methods like getString to process the columns.

getString takes an int argument which represents the column number of the rset table.

Another example

String query = "SELECT name, address FROM Bars WHERE licence = ‘pub’”;

// Iterate through the result and print the bars names and addresses while (rset.next())

System.out.println (rset.getString (1)+ rset.getString (2));

OR, if you want to add some extra information:

// Iterate through the result and print the bars names and addresses while (rset.next())

System.out.println (``Name: ’’ + rset.getString (1) +

``Address: ’’ + rset.getString (2));

(7)

COMP 514

Another example

String query = "SELECT beer,price FROM Sells WHERE bar = ‘The Polar Bar’”;

// Iterate through the result and print the beers names and prices while (rset.next())

System.out.println ``Beer: ’’ + rset.getString (1)+

``Price: ’’ + rset.getFloat (2));

Since price is a Float we need the appropriate method!!

COMP 514

ResultSet Class

• next

• close

• wasNull

• getString

• getBoolean

• getByte

• getShort

• getInt

• getLong

• getFloat

• getDouble

• getNumeric

• getBytes

• getDate

• getTime

• getTimeStamp

• getAsciiStream

• getUnicodeStream

• getBinaryStream

COMP 514

An example of updating: insert rows

Statement UpdateStatement = conn.createStatement ();

//Create an update string

String update = “INSERT INTO Sells (bar, beer, price)” +

“VALUES (‘The Bar’, ‘Bud’, 2.0)”;

UpdateStatement.executeUpdate(update);

The only difference with the previous code is the use of the executeUpdate method.

Also we do not need to store any result set!!

COMP 514

An example of updating: modify rows

Set the price of Stella to 2.30 in the table Sells

The SQL statement is UPDATE Sells SET price = 2.30

WHERE beer = ‘Stella Artois’

(8)

COMP 514

An example of updating: modify rows

The Java code:

String update = “UPDATE Sells SET price = 2.30” +

“WHERE beer = ‘Stella Artois’”;

COMP 514

Table creation

The last missing bit: Create and drop objects (tables, views…)

Example: we want to create the table Sells

SQL statement CREATE TABLE Sells(

bar CHAR(20), beer VARCHAR(20), price REAL)

Table creation

The Java code

String update = “CREATE TABLE Sells(bar CHAR(20),” +

“beer VARCHAR(20),price REAL)”

Managing transactions with Java

Concurrent updates on the same database can generate problems.

This is because by default different SQL statements contained in a Java program are considered different transactions.

Consider the following code.

This code provides a simple interface for booking seats in a cinema.

(9)

COMP 514

Managing transactions with Java

The database contains a table

Cinema

Screen Seats

a 150

b 40

c 1

COMP 514

Managing transactions with Java

The code queries the database for seats in a given screen and book the seat if there is one.

The query is executed and the result is put into rs:

String query = "SELECT seats FROM Cinema WHERE screen = " + "'"+screenName+"'";

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(query);

COMP 514

Managing transactions with Java

The number of seat is extracted from rs and assigned to seats:

while(rs.next()) {

seats = rs.getInt(1);

System.out.println (seats);

}

COMP 514

Managing transactions with Java

An if-block tests if there are still seats. If yes, a seat is booked and the database is updated. If no, a “sorry”

message is printed.

if (seats > 0){

System.out.println ("There are still seats available");

String update = "UPDATE CINEMA SET seats = seats - 1 WHERE screen = " + "'"+screenName+"'";

Statement updateStmt = conn.createStatement ();

updateStmt.executeUpdate(update);

System.out.println ("seat booked");

} else

{System.out.println ("There are no seats available. Sorry.");}

(10)

COMP 514

Managing transactions with Java

What happens if two people try to book a seat in screen c at the same time?

The query "SELECT seats FROM Cinema WHERE screen = " +

"'"+screenName+"'”

will give answer ‘1‘ to both, and seats will be assigned to 1 in both cases.

Therefore both people will receive (and pay) a ticket for only one seat.

COMP 514

The setAutoCommit mode

The problem in the previous example was due to the fact that the execution of the query, and the execution of the update generated two different transactions.

Sometimes we need to group different SQL statements in a single transaction. That is we want that:

• either all of the statements are executed, or

• none of the statements is executed.

We group statements using the setAutoCommint method.

The setAutoCommit mode

conn.setAutoCommit(false);

SQL statements conn.commit();

conn.setAutoCommit(true);

All the SQL statements after the setAutoCommit(false) are do not commit until a comit() method is used.

This enables us to group SQL statements into a single transaction.

2-Tier Client Server applications

JDBC Enabled Database Java Client

Application using JDBC interface

network

(11)

COMP 514

2-Tier Client Server applications

This application architecture is simple, but …..

• Client often has to do all the processing

• Client has to know everything about the database

• where it is

• current access controls

• access permissions etc.

• Database has to be visible to the client

• Databases are not meant to handle large numbers of simultaneous invocations

COMP 514

3-Tier Client Server

Assume that different clients want to access to the DB.

Possibly asking the same queries….

Client

Server DB

Sockets JavaIDL JavaRMI

JDBC

COMP 514

3-Tier Client Server - advantages

• Server can manage database connections efficiently

• Client can focus on processing data retrieved

• Running server on high performance hardware can improve client perceived performance

• Database only has to be visible to the server - not the rest of the machines!!

• Can be hidden behind a firewall

• Operational changes only have to be notified to the server - not all the clients

• Much better security

COMP 514

(Firewall)

• Firewall - a security gateway that protects an

organization from unauthorized access via the Internet

• Consists of software and sometimes hardware components.

(12)

COMP 514

3-Tier Client Server - an example

Write a server that provides a remote method invocation for a method getGeneralTable(String tableName)

The example is contained in:

• Query.java (the RMI interface)

• QueryImpl.java (the Server)

• QueryClient.java (the Client)

COMP 514

3-Tier Client Server - an example

QueryClient

Query

QueryImpl DB

JavaRMI

JDBC

QueryClient

3-Tier Client Server - an example

queryClient does not need any knowledge of the database.

Only the names of the tables (more in general the queries) that it wants to submit to the database.