Transcript week11 - Arms-A


Indexes

JDBC

JDBC in J2EE (Java 2 Enterprise Edition)
1
© D. Wong 2003
Indexes

An index on an attribute A is a data structure to
improve query performance efficiency

Reason: not efficient to scan all tuples (for large
relations) in order to find the few that meet a
given condition

E.g.
SELECT * FROM Movie
WHERE studioName = ‘Disney’ AND year = 1990;

Not part of SQL standard
2
© D. Wong 2003
Indexes Typical Syntax

To create index
CREATE INDEX indexName ON R(A1,…An)
E.g.
1. CREATE INDEX YearIndex ON Movie(year);
2. CREATE INDEX KeyIndex ON Movie(title, year);

Delete Index
DELETE INDEX yearIndex;
3
© D. Wong 2003
Selection of Indexes

Index design requires an estimate of the typical
mix of queries and other operations on the db

Example of good use of indexes:
1. An attribute frequently compared to constant
in a where clause of a query
2. Attribute that appear frequently in join
operations
e.g. SELECT name FROM Movie, MovieExec
WHERE title = ‘status’ AND producerC#
= cert#;
4
© D. Wong 2003
Decision factors

Important to strike a balance.

Factors:
1. Given attribute A, and index on A will:
–
–
Greatly speed up queries with a condition
on that attribute
May speed up joins involving A
2. Index make insertion, deletion, and updates
more complex and time-consuming
5
© D. Wong 2003
Indexes (continued)

Techniques to execute SQL queries are intimately
associated with storage structures. Typically, a
relation is stored in many disk blocks.

An index is an auxiliary structure, perhaps stored
in a separate file, that support fast access to the
rows of a table.

Main cost of a query or modification is I/O:
No. of disk blocks to be read into memory and
write onto disk
6
© D. Wong 2003
Index Selection Example

Given Relation: StarsIn(movieTitle, movieYear, starName)

3 operations to perform:
1. Q1: select movieTitle, movieYear From StarsIn
where starName = s;
2. Q2: select starName from StarsIn where movieTitle
= t and movieYear = y;
3. I: insert into StarsIn values(t, y, s);
where s, t, y are some constants
7
© D. Wong 2003
Example's assumptions
1.
Cost for examining 1 disk block = 1 unit
2.
StarsIn is stored in 10 disk blocks
3.
Average no. of stars in a movie = 3
4.
Average no. of movies that a star appeared in = 3
5.
Tuples for a given star or movie are likely to be
spread over the 10 disk blocks of StarsIn
6.
One disk access is needed to read a block of the
index every time when the index is used to locate
tuples with a given value for the indexd
attribute(s)
8
© D. Wong 2003
Example's Estimated Cost of actions
Action No Index
Star Index
Movie Index Both Indexes
Q1
10
4
10
4
Q2
10
10
4
4
I
2
4
4
6
Cost of 3
actions
2+8p1+8p2
4+6p2
4+6p1
6-2p1-2p2
Costs associated with the three actions, as a function of which
indexes are selected (Ref. Fig. 6.17 2nd ed.)
Star index is an index on StarName,
Movie index is an index on MovieTitle and movieYear.
The numbers in rows 2-5 of the table are no. of disk accesses for the action.
9
© D. Wong 2003
Example's usage scenarios

The fraction of the time to do Q1 = p1, Q2 = p2,
I=1-p1-p2

Consider:
– Case 1: p1 = p2 = 0.1
– Case 2: p1 = p2 = 0.4
– Case 3: p1=0.5, p2=0.1
What is the best index strategy for each case?

Create only the index that helps the most
frequently used query type (e.g. query about stars
=> create Star index)
10
© D. Wong 2003
JDBC (Java DataBase Connectivity)

An API to the database driver manager

Provides call-level interface for the execution of
SQL statements from a Java language program

Developed by SUN Microsystems

An integral part of the Java language

Java applications use the JDBC dialect of SQL,
independent of the DBMS used

Support applications to request information about
the schema from the DBMS at run time.
11
© D. Wong 2003
JDBC Interaction with SQL DB
1.
Make connection to the database
2.
Create SQL statement
3.
Execute the SQL statement
4.
Result table from the SQL "select" statement is
returned as a Java object. JDBC provide
methods to access the rows and columns.
OR
5.
SQL statements return simple integer results that
represent the number of affected rows (e.g.
insert)
12
© D. Wong 2003
Connecting to a db through JDBC
Driver 1
Application
Driver
Manager
Driver 2
Driver 3
DBMS
server
JDBC Modules
13
© D. Wong 2003
JDBC - java.sql package


Defines a collection of interfaces and classes that allow
programs to interact with db.
Interfaces for primary SQL execution:
–
Driver: supports data connection creation
–
Connection: represents connection between a java client
and and SQL database server
–
Statement: includes methods for executing text queries
–
PreparedStatement: represents a precompiled and stored
query
–
CallableStatement: used to execute SQL stored procedures
–
ResultlSet: contains the results of a query
–
ResultSetMetaData: information about a ResultSet,
including the attribute names and types
14
© D. Wong 2003
Client-server Architectures
User Interface /
Application
User Interface /
Application
User and
Application tier
Middleware
Middle tier
Application
Database
Server
Database
Server
15
Database
Server tier
© D. Wong 2003
JDBC in J2EE

J2EE – Java 2 Enterprise Edition, a middle layer server

Connection to DBMS using JDBC (e.g. Cloudscape,
Oracle, MS SQL)

J2EE Platform – services and architecture

Enterprise JavaBeans (EJB)
– Session Beans vs. Entity Beans

EJB access to databases using JDBC
– Database connection
– Persistence management (Entity Bean e.g.)
– Transaction management (Session Bean e.g.)
16
© D. Wong 2003
J2EE Services

HTTP - enables Web browsers to access servlets
and JavaServer PagesTM (JSP) files

EJB - allows clients to invoke methods on
enterprise beans

Authentication - enforces security by requiring
users to log in

Naming and Directory - allows programs to locate
services and components through the Java
Naming and Directory InterfaceTM (JNDI) API
17
© D. Wong 2003
J2EE Architecture
Ref. JavaTM 2 Enterprise Edition Developer's Guide, Figure 1-2
18
© D. Wong 2003
Enterprise JavaBeans (EJB)

Server-side Java components

Contain the business logic of enterprise application

Support database access

Transactional

Multi-user secure

Managed by the EJB container

Prohibited from a set of operations
19
© D. Wong 2003
Session Bean vs. Entity Bean
Session Bean
Entity Bean
Purpose
Performs a task for a Represents a business
client
entity object that exists in
persistent storage.
Shared
Access
May have one client.
May be shared by multiple
clients.
Persistence
Not persistent.
Persistent. Entity state
remains in a database.
Ref. JavaTM 2 Enterprise Edition Developer's Guide, Table 1-1
20
© D. Wong 2003
EJB Access to Databases Using JDBC API

J2EE uses
1. JDBC 2.0 (java.sql) and
2. JDBC 2.0 Optional package (javax.sql)

To make a connection to database in J2EE :
1. Should not hardcode the actual name (URL) of the
database in EJB
2. Should refer to the database with a logical name
3. Use a JNDI lookup when obtaining the database
connection.
21
© D. Wong 2003
Driver and Data source properties
In J2EE configuration file, resource.properties, specify:
 Driver
e.g. 1 Cloudscape that is packaged with Sun’s J2EE
jdbcDriver.0.name=COM.cloudscape.core.RmiJdbcDriver
e.g. 2 Oracle
jdbcDriver.0.name= oracle.jdbc.driver.OracleDriver
 JDBC URL
e.g. 1 Cloudscape
jdbcDataSource.0.name=jdbc/Cloudscape
jdbcDataSource.0.url=jdbc:cloudscape:rmi:CloudscapeDB;create=true
e.g. 2 Oracle
jdbcDataSource.0.name=jdbc/Oracle
jdbcDataSource.0.url= jdbc:oracle:thin:@bigoh.cis.uab.edu:1521:cs610
22
© D. Wong 2003
Making a connection to database example
1. Specify the logical database name.
private String dbName = "java:comp/env/jdbc/AccountDB";
2. Obtain the DataSource associated with the logical name.
InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup(dbName);
3. Get the Connection from the DataSource.
Connection con = ds.getConnection(username, password);
23
© D. Wong 2003
Specifying JNDI name for deployment Step 1: Enter the code name
24
© D. Wong 2003
Step 2: Map the coded name to the JNDI name
25
© D. Wong 2003
Persistence Management
Container-Managed Persistence
• Entity bean code does not contain database access calls.
• The EJB container generates the SQL statements.
Bean-Managed Persistence
• Entity bean code contains the database access calls
(SQLs) (i.e. you write the code!)
26
© D. Wong 2003
Container Managed example: Product entity bean
ProductEJB.java
ProductHome.java
Product.java
ProductClient.java
Bean Managed example: Account entity bean
AccountEJB.java
AccountHome.java
Account.java
AccountClient.java
27
© D. Wong 2003
28
© D. Wong 2003
29
© D. Wong 2003
30
© D. Wong 2003