JDBC –�Java DataBase Connectivity

JDBC

• “An API that lets you access virtually any tabular data source from the Java programming language”
• JDBC Data Access API – JDBC Technology Homepage

​

MM Database

2

General Architecture

Unit 3

3

Anatomy

MM Database

4

Basic steps to use a database in Java

• 1.Establish a connection
• 2.Create JDBC Statements
• 3.Execute SQL Statements
• 4.GET ResultSet
• 5.Close connections

MM Database

5

1. Establish a connection

• import java.sql.*;
• Load the vendor specific driver
• Class.forName("oracle.jdbc.driver.OracleDriver");
• What do you think this statement does, and how?
• Dynamically loads a driver class, for Oracle database
• Make the connection
• Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@oracle-prod:1521:OPROD", username, passwd);
• What do you think this statement does?
• Establishes connection to database by obtaining �a Connection object

​

​

6

2. Create JDBC statement(s)

• Statement stmt = con.createStatement() ;
• Creates a Statement object for sending SQL statements to the database

7

Executing SQL Statements

• String createLehigh = "Create table Lehigh " +

"(SSN Integer not null, Name VARCHAR(32), " + "Marks Integer)";

stmt.executeUpdate(createLehigh);

//What does this statement do?

​

• String insertLehigh = "Insert into Lehigh values“ + "(123456789,abc,100)";

stmt.executeUpdate(insertLehigh);

​

8

Get ResultSet

String queryLehigh = "select * from Lehigh";

​

ResultSet rs = Stmt.executeQuery(queryLehigh);

//What does this statement do?

​

while (rs.next()) {

int ssn = rs.getInt("SSN");

String name = rs.getString("NAME");

int marks = rs.getInt("MARKS");

}

9

Close connection

• stmt.close();
• con.close();

10

Transactions and JDBC

• JDBC allows SQL statements to be grouped together into a single transaction
• Transaction control is performed by the Connection object, default mode is auto-commit, I.e., each sql statement is treated as a transaction
• We can turn off the auto-commit mode with con.setAutoCommit(false);
• And turn it back on with con.setAutoCommit(true);
• Once auto-commit is off, no SQL statement will be committed until an explicit is invoked con.commit();
• At this point all changes done by the SQL statements will be made permanent in the database.

11

Handling Errors with Exceptions

• Programs should recover and leave the database in a consistent state.
• If a statement in the try block throws an exception or warning, it can be caught in one of the corresponding catch statements
• How might a finally {…} block be helpful here?
• E.g., you could rollback your transaction in a �catch { …} block or close database connection and free database related resources in finally {…} block

12

Another way to access database�(JDBC-ODBC)

13

Sample program

import java.sql.*;

class Test {

public static void main(String[] args) {

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //dynamic loading of driver

String filename = "c:/db1.mdb"; //Location of an Access database

String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";

database+= filename.trim() + ";DriverID=22;READONLY=true}"; //add on to end

Connection con = DriverManager.getConnection( database ,"","");

Statement s = con.createStatement();

s.execute("create table TEST12345 ( firstcolumn integer )");

s.execute("insert into TEST12345 values(1)");

s.execute("select firstcolumn from TEST12345");

​

14

Sample program(cont)

ResultSet rs = s.getResultSet();

if (rs != null) // if rs == null, then there is no ResultSet to view

while ( rs.next() ) // this will step through our data row-by-row

{ /* the next line will get the first column in our current row's ResultSet

as a String ( getString( columnNumber) ) and output it to the screen */

System.out.println("Data from column_name: " + rs.getString(1) );

}

s.close(); // close Statement to let the database know we're done with it

con.close(); //close connection

}

catch (Exception err) { System.out.println("ERROR: " + err); }

}

}

15

Mapping types JDBC - Java

16

JDBC 2 – Scrollable Result Set

…

Statement stmt =

con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_READ_ONLY);

​

String query = “select students from class where type=‘not sleeping’ “;

ResultSet rs = stmt.executeQuery( query );

​

rs.previous(); / / go back in the RS (not possible in JDBC 1…)

rs.relative(-5); / / go 5 records back

rs.relative(7); / / go 7 records forward

rs.absolute(100); / / go to 100th record

…

​

17

JDBC 2 – Updateable ResultSet

…

Statement stmt =

con.createStatement(ResultSet.TYPE_FORWARD_ONLY,

ResultSet.CONCUR_UPDATABLE);

String query = " select students, grade from class

where type=‘really listening this presentation☺’ “;

ResultSet rs = stmt.executeQuery( query );

…

while ( rs.next() )

{

int grade = rs.getInt(“grade”);

rs.updateInt(“grade”, grade+10);

rs.updateRow();

}

​

18

Metadata from DB

• A Connection's database is able �to provide schema information �describing its tables, �its supported SQL grammar, �its stored procedures �the capabilities of this connection, and so on
• What is a stored procedure?
• Group of SQL statements that form a logical unit and perform a particular task

This information is made available through �a DatabaseMetaData object.

19

Metadata from DB - example

…

Connection con = …. ;

​

DatabaseMetaData dbmd = con.getMetaData();

​

String catalog = null;

String schema = null;

String table = “sys%”;

String[ ] types = null;

​

ResultSet rs =

dbmd.getTables(catalog , schema , table , types );

…

​

20

JDBC – Metadata from RS

public static void printRS(ResultSet rs) throws SQLException

{

ResultSetMetaData md = rs.getMetaData();

// get number of columns

int nCols = md.getColumnCount();

// print column names

for(int i=1; i < nCols; ++i)

System.out.print( md.getColumnName( i)+",");

/ / output resultset

while ( rs.next() )

{ for(int i=1; i < nCols; ++i)

System.out.print( rs.getString( i)+",");

System.out.println( rs.getString(nCols) );

}

}

21

JDBC and beyond

• (JNDI) Java Naming and Directory Interface
• API for network-wide sharing of information about users, machines, networks, services, and applications
• Preserves Java’s object model
• (JDO) Java Data Object
• Models persistence of objects, using RDBMS as repository
• Save, load objects from RDBMS
• (SQLJ) Embedded SQL in Java
• Standardized and optimized by Sybase, Oracle and IBM
• Java extended with directives: # sql
• SQL routines can invoke Java methods
• Maps SQL types to Java classes

​

22

SQLJ

// SQLJ

int n;

#sql { INSERT INTO emp VALUES (:n)};

​

// vs. straight JDBC

int n;

Statement stmt = conn.prepareStatement

(“INSERT INTO emp VALUES (?)”);

stmt.setInt(1,n);

stmt.execute ();

stmt.close();

​

23

DriverManager class

​

The DriverManager class acts as an interface between user and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver().

​

24

Useful methods of DriverManager class

​

1) public static void registerDriver(Driver driver):is used to register the given driver with DriverManager.

2) public static void deregisterDriver(Driver driver):is used to deregister the given driver (drop the driver from the list) with DriverManager.

3) public static Connection getConnection(String url):is used to establish the connection with the specified url.

4) public static Connection getConnection(String url,String userName,String password):is used to establish the connection with the specified url, username and password.

​

25

Connection interface

​

A Connection is the session between java application and database. The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e. object of Connection can be used to get the object of Statement and DatabaseMetaData. The Connection interface provide many methods for transaction management like commit(), rollback() etc.

26

Commonly used methods of Connection interface:

​

​

1) public Statement createStatement(): creates a statement object that can be used to execute SQL queries.

2) public Statement createStatement(int resultSetType,int resultSetConcurrency): Creates a Statement object that will generate ResultSet objects with the given type and concurrency.

3) public void setAutoCommit(boolean status): is used to set the commit status.By default it is true.

4) public void commit(): saves the changes made since the previous commit/rollback permanent.

5) public void rollback(): Drops all changes made since the previous commit/rollback.

6) public void close(): closes the connection and Releases a JDBC resources immediately.

​

27

Statement interface

The Statement interface provides methods to execute queries with the database. The statement interface is a factory of ResultSet i.e. it provides factory method to get the object of ResultSet.

28

Commonly used methods of Statement interface:

1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns the object of ResultSet.

2) public int executeUpdate(String sql): is used to execute specified query, it may be create, drop, insert, update, delete etc.

3) public boolean execute(String sql): is used to execute queries that may return multiple results.

4) public int[] executeBatch(): is used to execute batch of commands.

​

29

ResultSet interface

The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points to before the first row.

30

Commonly used methods of ResultSet interface

1) public boolean next():is used to move the cursor to the one row next from the current position.

2) public boolean previous():is used to move the cursor to the one row previous from the current position.

3) public boolean first():is used to move the cursor to the first row in result set object.

4) public boolean last():is used to move the cursor to the last row in result set object.

5) public boolean absolute(int row):is used to move the cursor to the specified row number in the ResultSet object.

​

31

6) public boolean relative(int row):is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.

7) public int getInt(int columnIndex):is used to return the data of specified column index of the current row as int.

8) public int getInt(String columnName):is used to return the data of specified column name of the current row as int.

9) public String getString(int columnIndex):is used to return the data of specified column index of the current row as String.

10) public String getString(String columnName):is used to return the data of specified column name of the current row as String.

​

32

PreparedStatement interface

The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query.

​

33

Method

public void setInt(int paramIndex, int value)-sets the integer value to the given parameter index.

public void setString(int paramIndex, String value)-sets the String value to the given parameter index.

public void setFloat(int paramIndex, float value)-sets the float value to the given parameter index.

public void setDouble(int paramIndex, double value)-sets the double value to the given parameter index.

public int executeUpdate()-executes the query. It is used for create, drop, insert, update, delete etc.

public ResultSet executeQuery()-executes the select query. It returns an instance of ResultSet.

​

34

Multimedia Database

​

​

What is a Multimedia DBMS?

• A multimedia database management system (MM-DBMS) is a framework that manages different types of data potentially represented in a wide diversity of formats on a wide array of media sources.
• Like the traditional DBMS, MM-DBMS should address requirements:
• Integration
• Data items do not need to be duplicated for different programs
• Data independence
• Separate the database and the management from the application programs
• Concurrency control
• allows concurrent transactions

MM Database

36

Requirements of Multimedia DBMS

• Persistence
• Data objects can be saved and re-used by different transactions and program invocations
• Privacy
• Access and authorization control
• Integrity control
• Ensures database consistency between transactions
• Recovery
• Failures of transactions should not affect the persistent data storage
• Query support
• Allows easy querying of multimedia data

MM Database

37

Requirements of Multimedia DBMS (cont.)

• In addition, an MM-DBMS should:
• have the ability to uniformly query data (media data, textual data) represented in different formats.
• have the ability to simultaneously query different media sources and conduct classical database operations across them.

⇒ query support

• have the ability to retrieve media objects from a local storage device in a smooth jitter-free (i.e. continuous) manner.

⇒ storage support

• have the ability to take the answer generated by a query and develop a presentation of that answer in terms of audio-visual media.
• have the ability to deliver this presentation in a way that satisfies various Quality of Service requirements.

⇒ presentation and delivery support

MM Database

38

A Sample Multimedia Scenario

• The following types of data
• Video data captured by surveillance cameras that record the activities taking place at various locations.
• Audio data captured by legally authorized telephone wiretaps.
• Image data consisting of still photographs taken by investigators.
• Document data seized by the police when raiding one or more places.
• Structured relational data containing background information, back records, etc., of the suspects involved.
• Geographic information system data remaining geographic data relevant to the drug investigation being conducted.

MM Database

39

Possible Queries

Image Query (by example):

• Police officer Rocky has a photograph in front of him.
• He wants to find the identity of the person in the picture.
• Query: “Retrieve all images from the image library in which the person appearing in the (currently displayed) photograph appears”

​

Image Query (by keywords):

• Police officer Rocky wants to examine pictures of “Big Spender”.
• Query: "Retrieve all images from the image library in which “Big Spender” appears."

MM Database

40

Possible Queries (cont.)

Video Query:

• Police officer Rocky is examining a surveillance video of a particular person being fatally assaulted by an assailant. However, the assailant's face is occluded and image processing algorithms return very poor matches. Rocky thinks the assault was by someone known to the victim.
• Query: “Find all video segments in which the victim of the assault appears.”
• By examining the answer of the above query, Rocky hopes to find other people who have previously interacted with the victim.

​

Heterogeneous Multimedia Query:

• Find all individuals who have been photographed with “Big Spender” and who have been convicted of attempted murder in South China and who have recently had electronic fund transfers made into their bank accounts from ABC Corp.

MM Database

41

MM Database Architectures

Based on Principle of Autonomy

• Each media type is organized in a media-specific manner suitable for that media type
• Need to compute joins across

different data structures

• Relatively fast query

processing due to

specialized structures

• The only choice for legacy

data banks

​

MM Database

42

MM Database Architectures (cont.)

Based on Principle of Uniformity

• A single abstract structure to index all media types
• Abstract out the common part of different media types (difficult!) - metadata
• One structure - easy implementation
• Annotations for different

media types

MM Database

43

MM Database Architectures (cont.)

Based on Principle of Hybrid Organization

• A hybrid of the first two. Certain media types use their own indexes, while others use the "unified" index
• An attempt to capture

the advantages of the

first two

• Joins across multiple

data sources using their

native indexes

​

MM Database

44

Organizing Multimedia Data Based on the�Principle of Uniformity

• Consider the following statements about media data and they may be made by a human or may be produced by the output of an image/video/text content retrieval engine.
• The image photol.gif shows Jane Shady, “Big Spender” and an unidentified third person, in Sheung Shui. The picture was taken on January 5, 1997.
• The video-clip videol.mpg shows Jane Shady giving “Big Spender” a briefcase (in frames 50-100). The video was obtained from surveillance set up at Big Spender’s house in Kowloon Tong, in October, 1996.
• The document bigspender.txt contains background information on Big Spender, a police’s file.

​

MM Database

45

Example to store image in Oracle database

• You can store images in the database in java by the help of PreparedStatement interface.
• The setBinaryStream() method of PreparedStatement is used to set Binary information into the parameterIndex.
• Signature of setBinaryStream method
• The syntax of setBinaryStream() method is given below:
• 1) public void setBinaryStream(int paramIndex,InputStream stream)  throws SQLException  
• 2) public void setBinaryStream(int paramIndex,InputStream stream,long length)  throws SQLException  

MM Database

46

For storing image into the database, BLOB (Binary Large Object) datatype is used in the table.

• For example:
• CREATE TABLE  "IMGTABLE“ ( "NAME" VARCHAR2(4000),  "PHOTO" BLOB  )/  

MM Database

47

Java Example to store image in the database

• import java.sql.*;  
• import java.io.*;  
• public class InsertImage {  
• public static void main(String[] args) {  
• try{  
• Class.forName("oracle.jdbc.driver.OracleDriver");  
• Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
•  PreparedStatement ps=con.prepareStatement("insert into imgtable values(?,?)");  
• ps.setString(1,"sonoo");  
• FileInputStream fin=new FileInputStream("d:\\g.jpg");  
• ps.setBinaryStream(2,fin,fin.available());  
• int i=ps.executeUpdate();  
• System.out.println(i+" records affected");  
• con.close();  
• }catch (Exception e) {e.printStackTrace();}  } }

MM Database

48