MAD Unit4

UNIT IV Chapter-I
Record Management
System
Contents
 Record Management System – Introduction
 Record Storage in RMS
 Creating a new record and Reading existing record
 Writing & Reading records of Mixed Data Type
 Record Enumeration
 Searching Records –Single Data Type
 Sorting Mixed Data type records in a record enum
 Record Listener
Introduction
 RMS is used for Maintaining the database in small computing devices
 RMS maintains record-oriented database (RMS) stored in Flash mem
 Device-independent API
 Records are arrays of bytes that live in record stores
 Record stores are shared within MIDlet suite
 MIDP 2.0 allows for optional sharing of record stores between
MIDlet suites
 Support for enumeration, sorting, and filtering
 Atomic update for single records
Introduction (cont..)
RMS Classes and Interfaces

 Defined in javax.microedition.rms package
 RecordStore
 Collection of records
 RecordEnumerator
 RecordStore enumerator
 Interfaces
 RecordComparator
 RecordFilter
 RecordListener
Record Store in RMS
 Persistent storage in MIDP is based on record stores
 A record store is a collection of records.
 Each record is an array of bytes with a unique identification

number
 Actual storage of records depends on MIDP implementation. It
could be hard disk on the device, a battery-backed RAM etc.
 The package javax.microedition.rms provides API for handling
device datastores.
Record Store in RMS(cont..)
 It also consists of APIs such as addRecord(), deleteRecord(),
get and setRecord() for record manipulation.
 RecordStore allows eventlistener to be registered with itself.
 This eventlistener is a class which implements a RecordListener

interface to get notifications for addition, deletion and updating
of records in that RecordStore.
 J2ME also provides interfaces such as RecordComparator,

RecordEnumerator and RecordFilter for comparing, enumerating
and filtering of records from a RecordStore, respectively.
RecordStore Operations
 Standard operations you would expect:
 openRecordStore(name,create)
 removeRecordStore(name)
 Get list of all known record stores in a MIDlet suite

 listRecordStores()
 Get the size of the RecordStore

 int getSize() – returns the no of bytes used by the
RecordStore
 Int getSizeAvailable() – get amount of space available for
both record data and overhead

Creating a new record and
Reading existing record
 Small database that contains data called records
 Use javax.microedition.rms.RecordStore
 Record stores are identified by name
public static final String RS_NAME = "Tasks";
try {
m_RS = RecordStore.openRecordStore(RS_NAME,
bCreateIfNecessary);
}
catch (RecordStoreException ex) {
this.m_bRecordStoreError = true;
this.m_sRecordStoreError = ex.toString();
}
Closing/Deleting RecordStore
 Need to make sure that you release the resource
protected void destroyApp(boolean parm1) throws
javax.microedition.midlet.
MIDletStateChangeException {
try {
m_RS.closeRecordStore();
RecordStore.deleteRecordStore(RS_NAME);
}
catch (RecordStoreException ex) {
ex.printStackTrace();
System.out.println(ex.getMessage());
}
}
RMSMidlet for Read Write
import javax.microedition.rms.*;
import javax.microedition.midlet.*;
import javax.microedition.lcdui.*;
import javax.microedition.io.*;
public class ReadWrite extends MIDlet

{
private RecordStore rs = null;
static final String REC_STORE = "db_1";
public ReadWrite()
{
openRecStore(); // Create the record store
// Write a few records and read them back

writeRecord("J2ME and MIDP");
writeRecord("Wireless Technology");
readRecords();
closeRecStore(); // Close record store

deleteRecStore(); // Remove the record store
}
public void startApp()

{
// no user interface
}
public void pauseApp()

{
}
public void openRecStore()

{
try
{
// The second parameter indicates that the record store
// should be created if it does not exist
rs = RecordStore.openRecordStore(REC_STORE, true );
}
catch (Exception e)
{
db(e.toString());
}
}
public void closeRecStore()

{
try
{
rs.closeRecordStore();
}
catch (Exception e)
{
db(e.toString());
}
}
public void destroyApp( boolean unconditional )
{
}
public void deleteRecStore()
{
if (RecordStore.listRecordStores() != null)
{
try
{
RecordStore.deleteRecordStore(REC_STORE);
}
catch (Exception e)
{
db(e.toString()); } } }
public void writeRecord(String str)

{
byte[] rec = str.getBytes();
try
{
rs.addRecord(rec, 0, rec.length);
}
catch (Exception e)
{
db(e.toString());
}
}
 public void readRecords()

{
try
{
// Intentionally make this too small to test code below
byte[] recData = new byte[5];
int len;
for (int i = 1; i <= rs.getNumRecords(); i++)

{
if (rs.getRecordSize(i) > recData.length)
recData = new byte[rs.getRecordSize(i)];
len = rs.getRecord(i, recData, 0);

System.out.println("Record #" + i + ": " + new String(recData, 0, len));
System.out.println("------------------------------");
}
} catch (Exception e) { db(e.toString()); }
}
private void db(String str)
{
System.err.println("Msg: " + str);
}
}
Record Enumeration
The primary benefits of incorporating a record enumerator into
your code is that it includes options for searching the record
store,and for retrieving records in sorted order.
 The RecordEnumeration logically maintains a sequence of the recordId's

of the records in a record store.
 It provides birectional access to the records of a Record Store
 The enumerator will iterate over all (or a subset, if an optional record
filter has been supplied) of the records in an order determined by an
optional record comparator.
 By using an optional RecordFilter, a subset of the records can be chosen
that match the supplied filter. This can be used for providing search
capabilities.
Record Enumeration (cont..)
 By using an optional RecordComparator, the enumerator can index
through the records in an order determined by the comparator. This
can be used for providing sorting capabilities.
 The first call to nextRecord() returns the record data from the first
record in the sequence. Subsequent calls to nextRecord() return the
next consecutive record's data.
 To return the record data from the previous consecutive from any
given point in the enumeration, call previousRecord().
 If after creation, the first call is to previousRecord(), the record data
of the last element of the enumeration will be returned. Each
subsequent call to previousRecord() will step backwards through the
sequence until the beginning is reached.
Creating enumeration
 enumerateRecords() method is used to create Record

Enumeration of a RecordStore
RecordStore.enumerateRecords(RecordFilter filter, RecordComparator

comparator,boolean keepUpdated)
 First parameter is used to serach the records of a record store based
on criteria
 Second parameter is used to compare the records and arrange the
records in an order
 Third parameter is true or false for updating the record store
Methods Of Record Enumeration

public void destroy() Frees internal resources used by this
RecordEnumeration.

public boolean hasNextElement() Returns true if more elements
exist in the next direction. returntrue if more elements exist in
the next direction

public boolean hasPreviousElement() Returns true if more elements
exist in the previous direction.returntrue if more elements exist in
the previous direction

public boolean isKeptUpdated() Returns true if the enumeration
keeps its enumeration current with any changes in the
records.returntrue if the enumeration keeps its enumeration current
with any changes in the records
 public void keepUpdated(boolean keepUpdated) Used to set whether
the enumeration will be keep its internal index up to date with the
record store record additions/deletions/changes.
Methods Of Record Enumeration

public nextRecordId() Returns the recordId of the next record

public int numRecords() Returns the number of records
available in this enumeration's set.

public byte[]previousRecord() Returns a copy of
the previous record in this enumeration, where

public intpreviousRecordId() Returns the recordId of
the previous record in this enumeration,

public voidrebuild() Request that the enumeration be updated to
reflect the current record set.
Record Filter
 The Sorting with a comparator is one option when working with an enumerator,
searching with a filter is the other. A minor difference between a comparator
and a filter is that a comparator returns the entire record store in sorted order,
whereas a filter returns only those records that match a specified criteria.
 If you apply both a comparator and a filter, records that match the search
criteria will be returned in sorted order.
 Like the RecordComparator, the RecordFilter is implemented by the addition of a
single method, matches(), to the enumerator code.
 The enumerator calls the matches() method for each record in the store. Based
on the boolean return value, the record either becomes a member of the result
set or is tossed aside as a record that does not meet the search criteria.
 Method for RecordFilter API
boolean matches(byte[] candidate)
Searching Single Data Type records
Example
import javax.microedition.rms.*;
import java.io.*;
public class SearchExample extends MIDlet implements CommandListener

{
private Display display;
private Alert alert;
private Form form;
private Command exit;
private Command start;
private RecordStore recordstore = null;
private RecordEnumeration recordEnumeration = null;
private Filter filter = null;
Search Example (cont..)
public SearchExample ()
{
display = Display.getDisplay(this);
exit = new Command("Exit", Command.SCREEN, 1);
start = new Command("Start", Command.SCREEN, 1);
form = new Form("Mixed RecordEnumeration", null);
form.addCommand(exit);
form.addCommand(start);
form.setCommandListener(this);
}
{
display.setCurrent(form);
}
{
}
{
}
public void commandAction(Command command, Displayable displayable)
{
if (command == exit)
{
destroyApp(true);
notifyDestroyed();
}
else if (command == start)
{
try
{
recordstore = RecordStore.openRecordStore(
"myRecordStore", true );
}
catch (Exception error)
{
alert = new Alert("Error Creating",
error.toString(), null, AlertType.WARNING);
alert.setTimeout(Alert.FOREVER);
display.setCurrent(alert);
}
try
{
String outputData[] = {"Mary", "Bob", "Adam"};
for (int x = 0 ; x < 3; x++)
{
byte[] byteOutputData = outputData[x].getBytes();
recordstore.addRecord(byteOutputData, 0,
byteOutputData.length);
}
}
catch ( Exception error)
{
alert = new Alert("Error Writing",
}
try
{
filter = new Filter("Bob");
recordEnumeration = recordstore.enumerateRecords(
filter, null, false);
if (recordEnumeration.numRecords() > 0)
{
String string = new String(recordEnumeration.nextRecord());
alert = new Alert("Reading", string,
null, AlertType.WARNING);
}
}
{
alert = new Alert("Error Reading",
}
 try
{
recordstore.closeRecordStore();
}
{
alert = new Alert("Error Closing",
}
{
try
{
RecordStore.deleteRecordStore("myRecordStore");
recordEnumeration.destroy();
filter.filterClose();
}
{
alert = new Alert("Error Removing",
}
}
}
}
}
class Filter implements RecordFilter
{
private String search = null;
private ByteArrayInputStream inputstream = null;
private DataInputStream datainputstream = null;
public Filter(String search)
{
this.search = search.toLowerCase();
}
public boolean matches(byte[] suspect)
{
String string = new String(suspect).toLowerCase();
if (string!= null && string.indexOf(search) != -1)
return true;
else
return false;
}
public void filterClose()
{
try
{
if (inputstream != null)
{
inputstream.close();
}
if (datainputstream != null)
{
datainputstream.close();
}
}
catch ( Exception error) { } } }
Record Comarator
 Used to compare two records and stores the records in an order
 Methods/Static Data Fields for RecordComparator API

int compare(byte[] rec1, byte[] rec2)
static int EQUIVALENT- Both records are equal
static int FOLLOWS – Specifies rec1 follows rec2
static int PRECEDES -Specifies rec1 preceds rec2

Sorting Mixed Data Type records
 import javax.microedition.rms.*;
import java.io.*;
public class SortMixedRecordDataTypeExample

extends MIDlet implements CommandListener
{
private Display display;
private Alert alert;
private Form form;
private Command exit;
private Command start;
private RecordStore recordstore = null;
private RecordEnumeration recordEnumeration = null;
private Comparator comparator = null;
Sorting Mixed Data Type records (cont..)
public SortMixedRecordDataTypeExample ()
{
display = Display.getDisplay(this);
exit = new Command("Exit", Command.SCREEN, 1);
start = new Command("Start", Command.SCREEN, 1);
form = new Form("Mixed RecordEnumeration");
form.addCommand(exit);
form.addCommand(start);
form.setCommandListener(this);
}
{
display.setCurrent(form);
}
{
}
{
}
public void commandAction(Command command, Displayable displayable)
{
if (command == exit)
{
destroyApp(true);
notifyDestroyed();
}
else if (command == start)
{
try
{
recordstore = RecordStore.openRecordStore(
"myRecordStore", true );
}
{
alert = new Alert("Error Creating",
}
try
{
byte[] outputRecord;
String outputString[] = {"Mary", "Bob", "Adam"};
int outputInteger[] = {15, 10, 5};
ByteArrayOutputStream outputStream =
new ByteArrayOutputStream();
DataOutputStream outputDataStream =
new DataOutputStream(outputStream);
for (int x = 0; x < 3; x++)
{
outputDataStream.writeUTF(outputString[x]);
outputDataStream.writeInt(outputInteger[x]);
outputDataStream.flush();
outputRecord = outputStream.toByteArray();
recordstore.addRecord(outputRecord, 0,
outputRecord.length);
outputStream.reset();
}
outputStream.close();
outputDataStream.close();
}
catch ( Exception error)
{
alert = new Alert("Error Writing",
}
try
{
String[] inputString = new String[3];
int z = 0;
byte[] byteInputData = new byte[300];
ByteArrayInputStream inputStream =
new ByteArrayInputStream(byteInputData);
DataInputStream inputDataStream =
new DataInputStream(inputStream);
StringBuffer buffer = new StringBuffer();

comparator = new Comparator();
recordEnumeration = recordstore.enumerateRecords(
null, comparator, false);
while (recordEnumeration.hasNextElement())
{
recordstore.getRecord( recordEnumeration.nextRecordId(),
byteInputData, 0);
buffer.append(inputDataStream.readUTF());
buffer.append(inputDataStream.readInt());
buffer.append("\n");
inputDataStream.reset();
}
alert = new Alert("Reading", buffer.toString(), null,
AlertType.WARNING);
inputDataStream.close();
inputStream.close();
}
{
alert = new Alert("Error Reading",
}
try
{
recordstore.closeRecordStore();
}
{
alert = new Alert("Error Closing",
}
{
try
{
RecordStore.deleteRecordStore("myRecordStore");
comparator.compareClose();
recordEnumeration.destroy();
}
{
alert = new Alert("Error Removing",
}
}
}
}
}
class Comparator implements RecordComparator
{
private byte[] comparatorInputData = new byte[300];
private ByteArrayInputStream comparatorInputStream = null;
private DataInputStream comparatorInputDataType = null;
public int compare(byte[] record1, byte[] record2)
{
int record1int, record2int;
try
{
int maxlen = Math.max(record1.length, record2.length);
if (maxlen > comparatorInputData.length)
{
comparatorInputData = new byte[maxlen];
}
comparatorInputStream = new ByteArrayInputStream(record1);
comparatorInputDataType =
new DataInputStream(comparatorInputStream);
comparatorInputDataType.readUTF();
record1int = comparatorInputDataType.readInt();
comparatorInputStream = new ByteArrayInputStream(record2);
comparatorInputDataType =
new DataInputStream(comparatorInputStream);
comparatorInputDataType.readUTF();
record2int = comparatorInputDataType.readInt();
if (record1int == record2int)
{
return RecordComparator.EQUIVALENT;
}
else if (record1int < record2int)
{
return RecordComparator.PRECEDES;
}
else
{
return RecordComparator.FOLLOWS;
}
}
{
return RecordComparator.EQUIVALENT; } }
public void compareClose()
{
try
{
if (comparatorInputStream!= null)
{
comparatorInputStream.close();
}
if (comparatorInputDataType!= null)
{
comparatorInputDataType.close();
}
}
{
}
}
}
Record Listener
 public interface RecordListener
 A listener interface for receiving Record Changed/Added/Deleted events from a record
store.
 Method Summary
RecordAdded
 public void recordAdded(RecordStore recordStore, int recordId)
Called when a record has been added to a record store
Parameters:
recordStore - the RecordStore in which the record is stored.
recordId - the recordId of the record that has been added.
Record Listener(cont..)
 RecordChanged
public void recordChanged(RecordStore recordStore, int recordId)
Called after a record in a record store has been changed. If the implementation
of this method retrieves the record, it will receive the changed version.
Parameters:
recordStore - the RecordStore in which the record is stored.
recordId - the recordId of the record that has been changed.
 RecordDeleted
public void recordDeleted(RecordStore recordStore, int recordId)
Called after a record has been deleted from a record store. If the
implementation of this method tries to retrieve the record from the record store,
an InvalidRecordIDException will be thrown.
Parameters:
recordStore - the RecordStore in which the record was stored.
recordId - the recordId of the record that has been deleted.
Important Questions
1. Explain the features of RMS
2. Explain the operations of RMS with example
3. Explain Record Enumeration and its methods
4. Explain searching Mixed Data Type records of a
Record Store
5. Explain sorting single data type records of Record
Store
6. Explain RecordListener Interface
UNIT IV Chapter-I I
JDBC
Overview
 JDBC Objects – JDBC Drivers

 JDBC Packages
 Overview of JDBC Process
 Database connection ,Statement Objects
 Result Set
 Transaction Processing
 Meta Data ,Data Types & Exceptions
What is JDBC?
 It is a frontend tool which is used to connect

frontend java application to backend database
 “… access virtually any data source, from relational

databases to spreadsheets and flat files.”
46
GenaralArchitecture
JDBC Driver
Types
 Type 1
 JDBC-ODBC Bridge
 Type 2
 Native API, partially java
 Type 3
 JDBC Network Driver, partially java
 Type 4
 100% Java
Type 1 Driver
 Translate JDBC calls into ODBC calls
and use Windows ODBC built in drivers
 ODBC must be set up on every client
 Then ODBC calls are mapped to
respective DBMS specific calls
Type 1 Driver (cont.)
Client JDBC- ODBC DB

ODBC
Vendor
Library
Type 2 Drivers
 Converts JDBC to data base vendors
native SQL calls
Type 2 Drivers (cont.)
Client JDBC DB
Vendor
Library
Type 3 Drivers
 Translates JDBC to a DBMS
independent network protocol
 Typically talks directly with a
middleware product which in turn talks
to the RDBMS
 Most flexible driver type
 all java
Type 3 Drivers (cont.)
Client JDBC Vendor DB

Middleware
Tier 1 Tier 2 Tier 3

Type 4 Drivers
 Converts JDBC directly to native API
used by the RDBMS
JDBC Process
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
56
1. Establish a connection
 import java.sql.*;
 Load the vendor specific driver
 Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver");
 dynamically loads a driver class
 Make the connection
 Connection con = DriverManager.getConnection(
"jdbc:odbc:DSN", username, passwd);
 Establishes connection to database by obtaining

a Connection object
57
2. Create JDBC statement(s)
 Statement stmt = con.createStatement() ;
Creates a Statement object for sending SQL
statements to the database
58
3. Executing SQL Statements
 String query = “select * from emp";
ResultSet rs=stmt.executeQuery(query);
Resultset stores the records returned by the
query
59
4. Get ResultSet
while (rs.next()) {
int ssn = rs.getInt(“EMPNO");
String name = rs.getString(“ENAME");
}
60
5.Close connection
 stmt.close();
 con.close();
61
Sample program
import java.sql.*;
class Test {
public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //dynamic loading of driver
Connection con = DriverManager.getConnection( “jdbc:odbc:DSN”,”scott” “tiger”);

Statement s = con.createStatement();
s.execute("create table TEST12345 ( firstcolumn integer )");
s.execute("insert into TEST12345 values(1)");
s.execute("select firstcolumn from TEST12345");
62
Sample program(cont)
ResultSet rs = s.getResultSet();
while ( rs.next() )
{
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); }
}
}
63
Statement Objects
 Once a connection is obtained we can interact with the database.
 There are three types of Statement objects they are

 Statement
 PreparedStatement and
 CallableStatement interfaces
which define the methods and properties that enable you to send SQL
or PL/SQL commands and receive data from your database.
 Statement object is used for general-purpose access to your
database.
It Useful when you are using static SQL statements at runtime.
The Statement interface cannot accept parameters.
 PreparedStatement is used to execute the SQL statements many
times.
The PreparedStatement interface accepts input parameters at runtime.
It is a Precompiled object
 CallableStatement is used when you want to access database stored
procedures.
The CallableStatement interface can also accept runtime input
Statement
 Creating Statement Object using Connection object:
Statement stmt = null;

try { stmt = conn.createStatement( ); . . . } catch (SQLException e) { . . . }
finally{ stmt.close( ) }
Once you've created a Statement object, you can then use it to execute a SQL statement with one of its three
execute methods.
 boolean execute(String SQL) : Returns a boolean value of true if a ResultSet object can be retrieved;
otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly
dynamic SQL.
 int executeUpdate(String SQL) : Returns the numbers of rows affected by the execution of the SQL
statement. Use this method to execute SQL statements for which you expect to get a number of rows
affected - for example, an INSERT, UPDATE, or DELETE statement.
 ResultSet executeQuery(String SQL) : Returns a ResultSet object. Use this method when you expect to
get a result set, as you would with a SELECT statement.
 Close( ) method of statement object is used to close the Statement

PreparedStatement
 The PreparedStatement interface extends the Statement interface which gives
you added functionality with a couple of advantages over a generic Statement
object.
 This statement gives you the flexibility of supplying arguments dynamically.
 Creating PreparedStatement Object:

PreparedStatement pstmt = null;
try { String SQL = "Update Employees SET age = ? WHERE id = ?"; pstmt =
conn.prepareStatement(SQL); . . . } catch (SQLException e) { . . . }
finally {pstmt.close() ; }
All parameters in JDBC are represented by the ? symbol, which is known as

the parameter marker. You must supply values for every parameter before
executing the SQL statement.
PreparedStatement (cont..)
 The setXXX() methods bind values to the parameters, where XXX represents
the Java data type of the value you wish to bind to the input parameter.
 Each parameter marker is referred to by its ordinal position.
 The first marker represents position 1, the next position 2, and so forth.
 All of the Statement object's methods for interacting with the database (a)
execute(), (b) executeQuery(), and (c) executeUpdate() also work with the
PreparedStatement object.
 However, the methods are modified to use SQL statements that can take input
the parameters.
PreparedStatement Example
//Selecting Records using PreparedStatement
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "select deptno, deptname, deptloc from dept where deptno > ?";
pstmt = conn.prepareStatement(query); // create a statement

pstmt.setInt(1, 1001); // set input parameter
rs = pstmt.executeQuery();
// extract data from the ResultSet
while (rs.next()) {
int dbDeptNumber = rs.getInt(1);
String dbDeptName = rs.getString(2);
String dbDeptLocation = rs.getString(3);
System.out.println(dbDeptNumber + "\t" + dbDeptName + "\t" + dbDeptLocation);
}
} catch (Exception e) {
e.printStackTrace();
} finally {….}
CallableStatement
 It is used to execute Stored Procedures of a database
 It is created using prepareCall( ) method
Creating Callable Statement

CallableStatement cstmt =
connection.prepareCall(“{ call proce1() }”); ...
 There are three types of parameters for a stored procedure.
 IN
A parameter whose value is unknown when the SQL statement is created. We bind values
to IN parameters with the setXXX() methods.
 OUT
A parameter whose value is returned by the SQL statement. We can get the value from
the OUT parameters with the getXXX() methods.
 INOUT
A parameter acts as both input and output values. We can bind variables with
the setXXX() methods and retrieve values with the getXXX() methods.
CallableStatement Example
Stored Procedure:
CREATE OR REPLACE PROCEDURE getName(EMP_ID IN NUMBER, E_NAME
OUT VARCHAR) AS BEGIN SELECT name INTO E_NAME FROM Employee
WHERE ID = EMP_ID; END;
CallableStatement cstmt = null;

try { String SQL = "{call getEmpName (?,
?)}"; cstmt = conn.prepareCall (SQL); . . .
} catch (SQLException e) { . . . }
finally {
cstmt.close();
}
ResultSet
 There are two types of resultsets supported by JDBC
1.Scrollable Result Set 2. Updatable ResultSET

 The type of a ResultSet object determines the level of its functionality in two areas: the
ways in which the cursor can be manipulated, and how concurrent changes made to the
underlying data source are reflected by the ResultSet object.
The sensitivity of a ResultSet object is determined by one of three different ResultSet
types:
TYPE_FORWARD_ONLY:
The result set cannot be scrolled; its cursor moves forward only, from before the first
row to after the last row. The rows contained in the result set depend on how the
underlying database generates the results. That is, it contains the rows that satisfy the
query at either the time the query is executed or as the rows are retrieved.
ResultSet(cont..)
TYPE_SCROLL_INSENSITIVE:
The result can be scrolled; its cursor can move both forward and
backward relative to the current position, and it can move to an
absolute position. The result set is insensitive to changes made to the
underlying data source while it is open. It contains the rows that satisfy
the query at either the time the query is executed or as the rows are
retrieved.
TYPE_SCROLL_SENSITIVE:
The result can be scrolled; its cursor can move both forward and
backward relative to the current position, and it can move to an
absolute position. The result set reflects changes made to the
underlying data source while the result set remains open.
The default ResultSet type is TYPE_FORWARD_ONLY.
JDBC – 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
… 73
JDBC – 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();
}
74
Transaction Processing
 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.
75
Metadata
 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
 Stored procedure is a Group of SQL

statements that form a logical unit and
perform a particular task
This information is made available

through 76
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 );
…
77
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) );
}
} 78
Data Types
79
Exceptions
 java.sql.SQLException extends the java.lang.Exception class
 java.sql.SQLException is a core JDBC exception class that provides

information about database access errors and other errors. Most of the
JDBC API methods throw SQLException.
 java.sql.BatchUpdateException: a BatchUpdateException provides the

update counts for all commands that were executed successfully during
the batch update.
 java.sql.DataTruncation : reports a DataTruncation warning (on reads)

or throws a DataTruncation exception (on writes) when JDBC
unexpectedly truncates a data value.
 java.sql.SQLWarning : provides information about database access

warnings.
Important Questions
1. What is the concept of JDBC ? List the

different types of JDBC drivers
2. Explain JDBC process with an example
3. Explain different Statement objects of JDBC
4. Explain different types of ResultSet
5. Explain Transaction Processing with example
6. Explain Data types of JDBC
UNIT IV Chapter-I II
JDBC & Embedded SQL

Overview
 MODEL Programs
 Tables & Indexing.
 Inserting , Selecting , Deleting & Updating data of Tables
 Joining Tables
 Calculating Data
 Grouping and Ordering Data ,Sub Queries
 VIEWS
Model Programs
Many programming styles can be used to write the data-access
portion of a J2ME component.
 Two programming styles—
Model A and Model B programs
 The Model A program style is used to execute SQL requests

that use the execute() or executeUpdate() methods and don’t
return a ResultSet.
 The Model B program style is used to execute SQL requests that

use the executeQuery() method, which returns a ResultSet.
Here we use DownRow() method where J2ME application
interacts with the ResultSet.
Model A Program
import java.sql.*;
public class ModelA

{
private Connection Database;
private Statement DataRequest;
public ModelA ()
{
String url = "jdbc:odbc:CustomerInformation";
String userID = "jim";
String password = "keogh";
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");
Database = DriverManager.getConnection(url,userID,password);
}
catch (ClassNotFoundException error) {
System.err.println("Unable to load the JDBC/ODBC bridge." + error);
System.exit(1);
}
catch (SQLException error) {
System.err.println("Cannot connect to the database. "+ error);
if(Database != null) {
try {
Database.close();
}
catch(SQLException er){}
}
System.exit(2);
}
Model A Program(cont)
try {
// insert example code here
}
catch ( SQLException error ) {System.err.println("SQL error." + error);
try {
Database.close();
}
}
System.exit(3);
}if(Database != null) {
try {
Database.close();
}
}
}
public static void main ( String args [] )
{
final ModelA sql1 = new ModelA ();
System.exit ( 0 ) }
}
Model B Program
import java.sql.*;
public class ModelB
{
private Connection Database;
private Statement DataRequest;
private ResultSet Results;
public ModelB ()
{
String url = "jdbc:odbc:CustomerInformation";
String userID = "jim";
String password = "keogh";
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");
Database = DriverManager.getConnection(url,userID,password);
}
catch (ClassNotFoundException error) {
System.err.println("Unable to load the JDBC/ODBC bridge." + error);
System.exit(1);
}
catch (SQLException error) {
}
System.err.println("Cannot connect to the database." + error);
System.exit(2);
try {
// Enter example code here
}
catch ( SQLException error ){
System.err.println("SQL error." + error);
try {
Database.close();
}
}
System.exit(3);
Model B Program(cont..)
if(Database != null) {}
try {
Database.close();
}
}
private void DisplayResults (ResultSet DisplayResults)
throws SQLException
{
boolean Records = DisplayResults.next();
if (!Records ) {
System.out.println( "No data returned");
return;
}
try {
do {
DownRow( DisplayResults) ;
} while ( DisplayResults.next() );
}
catch (SQLException error ) {
System.err.println("Data display error." + error);
try {
Database.close();
}
Model B Program(cont..)
}
System.exit(4);
}
}
private void DownRow ( ResultSet DisplayResults )

throws SQLException
{
//Enter new DownRow() code here
}
public static void main ( String args [] )
{
final ModelB sql1 = new ModelB ();
System.exit(0);
}
}
Tables and Indexing
A database design is used as the basis for building tables and
indexes that make up a database.
Creating Table
try {
String query = "CREATE TABLE CustomerAddress (" + " CustomerNumber CHAR(30), " + " CustomerStreet
CHAR(30), " + " CustomerCity CHAR(30), " + " CustomerZip CHAR(30))";
}
Requiring Data in a Column using NOT NULL constraint
String query = "CREATE TABLE CustomerAddress ( " +
"CustomerNumber CHAR(30) NOT NULL," +
"CustomerStreet CHAR(30) NOT NULL," +....)”
Setting a Default Value for a Column

String query = "CREATE TABLE CustomerAddress ( " +
"CustomerNumber CHAR(30) NOT NULL," + "CustomerStreet CHAR(30) NOT NULL," +
"CustomerCity CHAR(30) NOT NULL," + "CustomerZip CHAR(30) NOT NULL DEFAULT '07660')";
Drop Table
String query = new String ("DROP TABLE CustomerAddress");

Indexing
 The database schema describes indexes along
with tables that are used in the database.
 An index is created by using the CREATE
INDEX statement in a query,
 CREATE INDEX statement uses the ON clauses
to identify the name of the table and the name
of the column whose value is used for the
index.
try {
String query = "CREATE UNIQUE INDEX
CustNum " +
"ON CustomerAddress (CustomerNumber)";
DataRequest = Database.createStatement();
Indexing(cont..)
Designating a Primary Key (primary index)
A primary key can be designated when a table is created by using the

Primary Key modifier,
String query = "Create Table Orders ( " +
"OrderNumber CHAR(30) NOT NULL, " +
"CustomerNumber CHAR(30), " +
"ProductNumber CHAR(30), " +
"CONSTRAINT ORDERS_PK PRIMARY KEY (OrderNumber))";
Creating a Secondary Index
Asecondary index is created by using the CREATE INDEX statement in a query

without the use of the UNIQUE modifier. This means that a secondary index
can have duplicate values. In this example, the index is called CustZip, and its
key is the CustomerZip column of the CustomerAddress table.
"CREATE INDEX CustZip " + "ON CustomerAddress (CustomerZip) ";
Drop an Index
An existing index can be removed from the database by using the DROP
Inserting data into Table
Insert a Row
The INSERT INTO statement is used to insert a new row into a table. The INSERT INTO
statement contains the name of the table into which the row is to be inserted and the name of
the columns in which values are inserted. The VALUES clause is used to define the values to be
placed into the row.
try {
String query = "INSERT INTO Customers " +
" (CustomerNumber, FirstName, LastName, DateOfFirstOrder) " +" VALUES
(1,'Mary','Smith','10/10/2001') ";
DataRequest.executeUpdate (query);
DataRequest.close();
}
Insert the System Date into a Column

You can place the system date into a column by calling the CURRENT_DATE function.
String query = new String ("INSERT INTO Customers (CustomerNumber ,FirstName, "+
" LastName, DateOfFirstOrder )" +" VALUES ( 4,'Mary','Jones', CURRENT_DATE)");
Call the CURRENT_TIME() function whenever a column requires the current time.
Insert a Timestamp into a Column
Atimestamp consists of both the current date and time and is used in applications where both date
and time are
Selecting Data
 Selecting Data from a Table
Retrieving information from a database
is the most frequently used routine of
J2ME components that interact with a
database.
 executeQuery() method is used to
retrieve the data and returns a
ResultSet.
 Therefore, use the Model B program
Selecting Data(cont..)
 Selecting All data
try {
String query = new String ("SELECT " +
" FirstName, LastName, Street, City, State, ZipCode " +
" FROM Customers");
Results = DataRequest.executeQuery (query);
DisplayResults (Results);
DataRequest.close(); }
private void DownRow ( ResultSet DisplayResults ) throws
SQLException
{
String FirstName= new String(); String LastName= new String();
String Street= new String(); String City = new String();
String State = new String(); String ZipCode= new String();
String printrow;
FirstName = DisplayResults.getString ( 1 ) ; LastName =
DisplayResults.getString ( 2 ) ;
Street = DisplayResults.getString ( 3 ) ; City = DisplayResults.getString
(4);
Select
The WHERE clause in a SELECT statement can evaluate values in more than one
column of a row by using the AND, OR, and NOT clauses to combine expressions.
Ex:
String query = new String ("SELECT FirstName, LastName " +" FROM Customers "
+" WHERE LastName = 'Jones' " +" AND FirstName = 'Bob'");
The rows are retrieved whose First name should be Bob and last name should be
Jones
AND Clause
The AND clause tells that the boolean value of both subexpressions must be true for
the compound expression to be true. If the compound expression is true, the
current row being evaluated by the DBMS is returned to your program.
OR Clause
The OR clause is used to create a compound expression using two subexpressions
in the same way as the AND clause. However, the OR clause tells that the
compound expression evaluates to a boolean true if either of the two
subexpressions evaluates to a boolean true.
NOT Clause
The NOT clause reverses the logic of the subexpression contained in the WHERE NOT clause.
If the subexpression evaluates to a boolean true, the NOT clause reverses the logic to return a
boolean false.
Equal, Not Equal, Less than , Greater
than, Between operators
Equal and Not Equal Operators
The equal and not equal operators are used to determine whether the value in
the WHERE clause expression is or isn’t in the specified column.
Ex:
"SELECT " +"FirstName, LastName, Street, City, State, ZipCode, Sales " +"FROM
Customers " +"WHERE NOT Sales = 50000 “
Retrieves those records whose sales are not 50000
Less Than and Greater Than Operators
The less than and greater than operators direct the DBMS to assess whether
or not the value in the specified column of the current row is less than or
greater than the value in the WHERE clause expression.
Less Than or Equal to and Greater Than or Equal To
Ex: Less Than or Equal To
String query = new String ("SELECT " + "FirstName, LastName, Street, City,
State, ZipCode, Sales " + "FROM Customers " +"WHERE Sales <= 50000 " );
Between Operator
Used to specify the range which includes the lower limit value and also
upper limit value
SELECT " +"FirstName, LastName, Street, City, State, ZipCode, Sales " +"FROM
Customers " +
Like ,IS NULL operators
LIKE Operator
The LIKE operator directs the DBMS to return a row in the ResultSet if a value in a specified
column partially matches the value of the WHERE clause expression.
 The WHERE clause expression must include a character that is an exact match and a wildcard
Underscore (_) A single-character wildcard character.
Percent (%) A multicharacter wildcard character used to match any number of characters.
For example, Smi% is used to match a value of a column where the first three characters are
Smi followed by any other character(s).
Ex:
"SELECT " +"FirstName, LastName, Street, City, State, ZipCode, Sales " + "FROM
Customers " +"WHERE LastName LIKE 'Smi%' ");
Retrieves the rows whose last name first 3 characters are Smi and the rest can be
any thing
IS NULL Operator
The IS NULL operator is used to determine whether a specified column does not contain any
value.
Ex:
"SELECT " +"FirstName, LastName, Street, City, State, ZipCode, Sales " + "FROM Customers " +
Metadata
Meta Data
 Metadata is data that describes data
 Metadata is returned with the ResultSet object and can be extracted from the ResultSet object by
creating a ResultSetMetaData.
 Metadata can be used in a J2ME application for various purposes,
such as to display the column name of a column and determine the data type of a column.
 The most commonly used metadata are.
Column name
Column number
Column data type
Column width
Number of Columns in ResultSet
Example code:
ResultSetMetaData metadata = DisplayResults.getMetaData ();
No. Of Columns
int NumberOfColumns;
NumberOfColumns = metadata.getColumnCount ();
Meta Data(cont..)
Data Type of Column
String ColumnType = new String();

ColumnType = metadata.getColumnTypeName ( 9 );
Name of Column
String ColumnName = new String();
ColumnName = metadata.getColumnLabel (9) ;
Column Size
The column size, also referred to as the column width, is called the display size
and represents the number of characters needed to display the maximum
value that might be stored in the column.
int ColumnWidth;
ColumnWidth = metadata.getColumnDisplaySize ( 9 ) ;
Update
 Update Multiple Rows
Multiple rows of a table can be updated by formatting the WHERE clause expressions to
include criteria that qualify multiple rows for the update.
Four common WHERE clause expressions are used to update multiple rows of a
table:
 The IN test– here the WHERE clause expression contains multiple values in the IN clause
that must match the value in the specified column for the update to occur in the row.
 The IS NULL test Rows that don’t have a value in the specified column are updated when
the IS NULL operator is used in the WHERE clause expression.
 The comparison test –Here the WHERE clause expression contains a comparison operator
that compares the value in the specified column with a value in the WHERE clause
expression.
 All rows A query can direct the DBMS to update the specified column in all rows of a table
by excluding the WHERE clause in the query.
Update(cont..)
Ex:
 IN Test
try {
String query = new String ("UPDATE Customers " + "SET
Discount = 25 " + "WHERE Discount IN (12,15)");
 IS NULL Test
try {
String query = new String ("UPDATE Customers " + "SET
Discount = 0 " + "WHERE LastName IS NULL ");
Update (cont..)
Update Based on Values in Column
try {
String query = new String ("UPDATE Customers " + "SET Discount = 20 " +
"WHERE Discount > 20 ");
DataRequest = Database.createStatement(); DataRequest.executeUpdate (query);
}
Update Every Row

try {
String query = new String ("UPDATE Customers " + "SET Discount = 0 ");
}
Update Multiple Columns

try {
String query = new String ("UPDATE Customers " +"SET Discount = 12, Street =
'Jones Street'" + "WHERE LastName = 'Jones'");
Delete
 Deleting Data from a Table
Deleting rows is necessary to purge erroneous information from

the database and to remove information that is no longer
needed.
 Before you delete a row from a table, you must be certain that
other tables are not negatively affected.
 Multiple rows can be deleted by including a WHERE clause in
the DELETE FROM statement,
try {
String query = new String ("DELETE FROM Customers " +
"WHERE LastName = 'Jones' and FirstName = 'Tom'");
}
Joining Tables
 How rows of data elements that are placed in tables are related to
each other by linking rows using a common value in each row of two
tables.
Linking rows is called joining tables.
Tables are joined in a query using a two-step process.

First, both tables that are being joined must be identified in the FROM
clause, where tables are listed one after the other and are separated
by a comma.
WHERE CustomerNumber = CustNum
Join Two Tables
try {
String query = new String ( "SELECT FirstName, LastName, City, State,
ZipCode " + "FROM Customers, ZipCode " +"WHERE Zip = ZipCode")
Results = DataRequest.executeQuery (query);

Joining(cont..)
Parent-Child Join
Aparent-child join is used to join tables that have a parent-child relationship.
This means rows in the parent table must exist for rows in the child table to exist.
try {
String query = new String (" SELECT OrderNumber, ProductName " +" FROM Orders, Products
" + " WHERE ProdNumber = ProductNumber");
}
 Multiple Comparison Join

The WHERE clause expression used to join tables can be a compound expression consisting of
two or more subexpressions. Each is evaluated separately,
A compound expression is used to specify more than one selection criteria used to join two
tables,
In this example, the DBMS is directed to join rows of the Products table and rows of the
Orders table. There are two components of the subexpression in this example. The first
requires the DBMS to match product numbers in both tables. The other subexpression
requires that the value in the
Quantity column is greater than 2.
try {
String query = " SELECT OrderNumber, ProductName, Quantity " +" FROM Orders, Products "
+" WHERE ProdNumber = ProductNumber " + " AND Quantity > 2";
DataRequest = Database.createStatement(); Results = DataRequest.executeQuery (query);
Joining(cont..)
 Multitable Join
More than two tables can be joined together by
using the name of each table in the join in the
FROM clause and by defining the join with the
appropriate column names in the WHERE clause
expression.
try {
String query = new String (
"SELECT FirstName, LastName,OrderNumber,
ProductName, Quantity " +" FROM Customers,
Orders, Products " +
" WHERE ProdNumber = ProductNumber " +" AND
CustNumber = CustomerNumber");
Column Name Qualifier
 Create a Column Name Qualifier
Column names should reflect the kind of data element stored in

the column.
However, there is likely to be a conflict when two or more
tables use the same column name to store the data element.
This is the case when both the Products table and the Orders
table contain product numbers.
try {
String query = new String ("SELECT Customers.CustNumber, "
+ " FirstName, LastName, OrderNumber, " +
" ProductName, Quantity " + " FROM Customers, Orders,
Products " + " WHERE ProdNumber = ProductNumber " +
" AND Customers.CustomerNumber =
Orders.CustomerNumber"); DataRequest =
Database.createStatement();
Results = DataRequest.executeQuery (query); DisplayResults
(Results);
Table name Alias
 Create a Table Alias
A query can be made readable by using table
aliases.
A table alias is an abbreviation for the name of
the table that is used in place of the table
name in the join and in the SELECT statement.
String query = new String ("SELECT

c.CustNumber , " + " c.FirstName, c.LastName,
o.OrderNumber, " +
" p.ProductName, o.Quantity " + " FROM
Customers c, Orders o, Products p" + "
WHERE o.ProdNumber = p.ProductNumber " +
Inner and OuterJoins
Inner and Outer Joins
The joins shown above link rows that have
matching values in the column specified in the
join.
 A row in a table that doesn’t match is excluded
from the join.
 There are two kinds of joins, each of which
either excludes or includes rows in both tables
of the join that don’t match.
 These are
· An inner join excludes rows of either
table that don’t have a matching value.

Outer Join(cont..)
Outer Join—Left, Right, Full
An outer join occurs when matching and nonmatching rows of either or both
tables are contained in the join.
 There are three kinds of outer joins:
Left outer join :All matched and unmatched rows of the first table and
matched rows of the second table are included in the join.
Right outer join Matched rows of the first table and matched and unmatched
rows of the second table are included in the join.
Full outer join Matched and unmatched rows of both tables are includedin the
join.
Ex:
Left outer join
try {
String query = new String (" SELECT FirstName, LastName,OrderNumber "
+" FROM Customers LEFT JOIN Orders " +
" ON Customers.CustNumber = Orders.CustomerNumber");
}
Right Outer Join
try {
String query = new String (" SELECT FirstName, LastName,OrderNumber" +"
Calculating Data
The DBMS can calculate values in a table and return the result
of the calculation in the ResultSet by using one of the five built-
in calculation functions:
SUM() tallies values in a column passed to the built-in function.
AVG() averages values in a column passed to the built-in

function.
MIN() determines the minimum value in a column passed to the
built-in function.
MAX() determines the maximum value in a column passed to
the built-in function.
COUNT() determines the number of rows in a column passed to
the built-in function
Ex:
SUM()

Grouping and Ordering
Columns are returned in the ResultSet in the order that the column names appear in the
SELECT statement of the query.
 The order in which rows appear in the ResultSet can be grouped into similar values or sorted
in ascending or descending order by using the GROUP BY clause or the ORDER BY clause,
GROUP BY
The GROUP BY clause specifies the name of the column whose values are used to group rows
in the ResultSet.
EX:
In this example the GROUP BY clause organizes the ResultSet by the value in the StoreNumber
column.
This means that the ResultSet contains the sum of the Sales column for each StoreNumber.
try {
String query = new String (" SELECT StoreNumber, SUM(Sales) " + " FROM Sales " +" Group
By StoreNumber");
DataRequest = Database.createStatement();Results = DataRequest.executeQuery (query);
System.out.println("Store Sales");System.out.println("----- -----");
DisplayResults (Results);DataRequest.close();
}
Group By (cont..)
Group Multiple Columns
The DBMS can create a subgroup within a
group in the ResultSet.
For example, the business unit may want to
see orders organized by store, and within
each store, orders are organized by
product.
try {
String query = new String (" SELECT
StoreNumber,SalesRepNumber,
SUM(Sales) " +
Group By(cont..)
 Conditional Grouping
The number of rows that are included in a
group can be limited by a conditional
expression in the query.
 The conditional expression is placed in the
HAVING clause of the query. The HAVING
clause sets the criteria for a row to be
included in a group.
try {
String query = new String ("SELECT
StoreNumber, SUM(Sales) " + " FROM
Order
Sorting Data
By Clause
The ResultSet can be placed in alphabetical or numerical order
by using the ORDER BY clause in the query.
 In this example, The ORDER BY clause specifies the
StoreNumber column, which sorts the value of the StoreNumber
column in ascending numerical order
try {
String query = new String ("SELECT StoreNumber, Sales " +"
FROM Sales " +
" ORDER BY StoreNumber");
DataRequest = Database.createStatement();Results =
DataRequest.executeQuery (query);
System.out.println("Store Sales");System.out.println("----- -----
");
DisplayResults (Results); DataRequest.close();
}
 By default the data is sorted in ascending order
Sub Queries
 Create a Subquery
A subquery is a query whose results are evaluated by an

expression in the WHERE clause of another query, the subquery
is defined below the first WHERE clause.
EX:
Here the subquery joins rows of the Sales table and the Order
table by store number value, as shown in the second WHERE
clause.
Once joined, the subquery totals the value of the Amount
column for each store number and returns the results to a
temporary table.
try {
String query = new String (" SELECT StoreNumber " +" FROM
Sales "+ " WHERE Estimate = (SELECT SUM(Amount) " + "
FROM Orders, Sales " +" WHERE StoreNum = StoreNumber) ");

VIEWS
You can reduce the complexity of your J2ME application by creating one or more views
of the database for each user ID that is passed to the J2ME application for data access.
 A VIEW is similar to creating a table that contains only data the user ID is permitted to
access. A VIEW limits columns and rows that can be queried to specific information
pertaining to a user ID.
 A VIEW is like a filter that hides information from a user ID.
 Each VIEW is uniquely identified with a name and contains selection criteria for columns
and rows that appear in the VIEW when the VIEW is used by a J2ME component.
 Once a VIEW is created, the J2ME application references a VIEW the same way that a
table is referenced in a query.
Rules for Using VIEWs

Views (cont..)
In many situations, using VIEWs increases the efficiency of interacting with tables in a
database. However, there are times when VIEWs are not appropriate for an application. The
following is a set of rules that govern how you should use VIEWs in your application:
· Create as many VIEWs as necessary to simplify access to a database.
· Restrict access to a table on need-to-know basis.
· Work with the owner of the data to establish reasonable restrictions.
· Classify users into groups that have similar access requirements to information.
· Create a VIEW for each classification of user rather than for each user.
· More than one column can be used in a VIEW.
· More than one table can be used in a VIEW.
· A VIEW is treated as a table in a query regardless of the number of columns and
tables
that are used to create the VIEW.
· Use a VIEW whenever your program accesses some columns in many tables.
· The view simplifies the number of tables that a J2ME application needs to access
directly.
 Beware of data security restrictions that affect the underlying columns and tables used to
create the VIEW.
 AVIEW inherits data security restrictions from tables used to create the VIEW. Therefore, if
the user ID doesn’t have rights to information in a table, the VIEW also has the same
restrictions.
Views (cont..)
 Create a VIEW
AVIEW is created by using the CREATE VIEW statement, as illustrated in
try {
String query = new String (" CREATE VIEW Store278 AS " +" SELECT * " +
" FROM Orders " +" WHERE StoreNum = 278");DataRequest = Database.createStatement();
}
 Select Columns to Appear in the VIEW

try {
String query = new String (" CREATE VIEW StoreProd AS " +" SELECT StoreNum, ProdNumber " +
" FROM Orders ");
}
 Create a Horizontal VIEW

try {
String query = new String (" CREATE VIEW cust901 AS " +" SELECT * " + " FROM Orders" + " WHERE
CustomerNumber = 901");
}
 Create a Multitable VIEW

try {
String query = new String (" CREATE VIEW ProdDesc AS " +" SELECT StoreNum, ProdNumber,
ProductName " + " FROM Orders, Products " +" WHERE ProdNumber = ProductNumber");
}

Views (cont..)
Modify a VIEW
A VIEW can be modified, and those modifications affect the underlying tables that are used to
create the VIEW.
There are three ways to modify a VIEW:

1) Update Values in one or more columns of a VIEW are changed to values supplied by the
query.
2)Insert A new row is added to the VIEW and indirectly to the underlying table(s) used to create
the VIEW.
3) Delete A row is removed from the VIEW and from the underlying table(s) used to create the
VIEW.
 Rules for Updating a View
 A View can be used to update columns and rows in the underlying tables that comprise the View.
However, updating a View is possible only if you adhere to a set of rules that govern the use of
updating a View.
 Calculation, expressions, and built-in column functions cannot be used in the SELECT
statement of the VIEW. Instead, only names of columns can be used.
 Exclude the GROUP BY clause and HAVING clause from the VIEW.
 Duplicate rows must be included in the modification.
 You cannot use the DISTINCT modifier in the VIEW if values of the view are going to be
modified.
Subqueries cannot be used in a VIEW if rows of the VIEW are to be modified.
Views (cont..)
Updating a View
try {
String query = new String (" UPDATE Store278 " + " SET Amount = 700 " +" WHERE
OrderNumber = 334 ");
}
Inserting a Row into a VIEW

try {
String query = new String (" INSERT INTO Store278 " +" (OrderNumber, ProdNum,
CustomerNumber, StoreNum, Amount) " +" VALUES (325, 9545 ,301 ,278 ,400) ");
}
Deleting a Row from a VIEW

try {
String query = new String (" DELETE FROM Store278 " +" WHERE OrderNumber = 555 ");
}
Views (cont..)
 Dropping a VIEW
 AVIEW can be removed by using the DROP VIEW

statement.
 The DROP VIEW statement requires the name of the
VIEW that is to be dropped.
 Two modifiers are used with the DROP VIEW statement:
1) CASCADE Remove all VIEWs that depend on the VIEW

specified in the DROP VIEW statement as well as the
specified VIEW.
2) RESTRICT Remove only the VIEW specified in the

DROP VIEW statement. All dependent VIEWs remain
intact.
Important Questions
1. Explain Model A and Model B programs
2. Write about the following with respect
to tables in J2ME
a) inserting data b) selecting data c)
updating data
3. Explain Indexes using embedded SQL
4. Explain Group BY and Order BY clause
5. Explain VIEWS
DISTINCT Modifier :
used to select unique values
Ex: SELECT DISTINCT " +
"FirstName, LastName, Street, City, State, ZipCode, Sales " + "FROM
Customers "
IN Modifier
The IN modifier is used to define a set of values used by the DBMS to
match values in a specified column. The set can include any number of
values and appear in any order.
Ex: "SELECT " + "FirstName, LastName, Street, City, State, ZipCode,
Sales " + " FROM Customers " +" WHERE Sales IN (20000, 30000,
40000) "
NOT IN Modifier
The NOT IN modifier is similar to the IN modifier, except the NOT IN

modifier reverses the logic. That is, it identifies a set of values that
shouldn’t match rows

MAD Unit4

Uploaded by

Copyright:

Available Formats

MAD Unit4

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MAD Unit4

Uploaded by

Copyright:

Available Formats

UNIT IV Chapter-I

RMS Classes and Interfaces

 A record store is a collection of records.

 Each record is an array of bytes with a unique identification

 RecordStore allows eventlistener to be registered with itself.

 This eventlistener is a class which implements a RecordListener

 J2ME also provides interfaces such as RecordComparator,

 Get list of all known record stores in a MIDlet suite

 Get the size of the RecordStore

both record data and overhead

public class ReadWrite extends MIDlet

// Write a few records and read them back

closeRecStore(); // Close record store

public void startApp()

public void pauseApp()

public void openRecStore()

public void closeRecStore()

public void writeRecord(String str)

 public void readRecords()

for (int i = 1; i <= rs.getNumRecords(); i++)

len = rs.getRecord(i, recData, 0);

 The RecordEnumeration logically maintains a sequence of the recordId's

 enumerateRecords() method is used to create Record

RecordStore.enumerateRecords(RecordFilter filter, RecordComparator

public class SearchExample extends MIDlet implements CommandListener

 Methods/Static Data Fields for RecordComparator API

static int EQUIVALENT- Both records are equal

static int FOLLOWS – Specifies rec1 follows rec2

static int PRECEDES -Specifies rec1 preceds rec2

public class SortMixedRecordDataTypeExample

StringBuffer buffer = new StringBuffer();

 JDBC Objects – JDBC Drivers

 It is a frontend tool which is used to connect

 “… access virtually any data source, from relational

Client JDBC- ODBC DB

Client JDBC Vendor DB

Tier 1 Tier 2 Tier 3

 Establishes connection to database by obtaining

Connection con = DriverManager.getConnection( “jdbc:odbc:DSN”,”scott” “tiger”);

 There are three types of Statement objects they are

Statement stmt = null;

 Close( ) method of statement object is used to close the Statement

 This statement gives you the flexibility of supplying arguments dynamically.

 Creating PreparedStatement Object:

All parameters in JDBC are represented by the ? symbol, which is known as

 Each parameter marker is referred to by its ordinal position.

pstmt = conn.prepareStatement(query); // create a statement

Creating Callable Statement

 There are three types of parameters for a stored procedure.

CallableStatement cstmt = null;

1.Scrollable Result Set 2. Updatable ResultSET

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

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

 Stored procedure is a Group of SQL

This information is made available

DatabaseMetaData dbmd = con.getMetaData();

String catalog = null;

 java.sql.SQLException is a core JDBC exception class that provides

 java.sql.BatchUpdateException: a BatchUpdateException provides the