Transcript JDBC

Java 7 Certification
JDBC & Related Design Pattern
Arthur Gober
Subject Matter Expert
www.NetComLearning.com
NetCom’s Average Instructor Rating: 8.7/9
www.netcomlearning.com
JDBC
• JDBC API provides a standard database –
independent interface to interact with RDMSs.
• Typically, you use the JDBC API to connect to a
database, query the data, and/or update data.
• Every database provides developers with
1. Standard SQL capabilities
2. Extended SQL capabilities
3. A proprietary programming language
to build database applications.
www.NetComLearning.com
JDBC
• Therefore, if you want to process a set of rows
in a database, you need to know the specific
syntax for a specific database-dependent
language.
• Using JDBC API relieves you of the effort to
learn specific syntaxes for different databases.
• Using JDBC API you write a query using
standard SQL and the Java API processes the
result in a database-independent manner.
www.NetComLearning.com
JDBC
• Using JDBC API to access data in a database hides
the implementation differences that exist in
different databases.
• It achieves the database transparency by defining
most of its API using interfaces and letting the
database vendors provide the implementations
for those interfaces.
• The collection of implementation classes that is
supplied by a vendor to interact with a specific
database is called a ‘JDBC driver’.
• The ‘JDBC driver’ is used to connect to the RDMS.
www.NetComLearning.com
JDBC
To connect to a database:
1. Obtain the JDBC driver class files and add
them to the CLASSPATH environment
variable.
2. Register the JDBC driver with the
‘DriverManager’.
3. Construct a connection URL.
4. Use the static ‘getConnection()’ method of
‘DriverManager’ to establish a connection.
www.NetComLearning.com
JDBC
• We will be working with the ‘APACHE DERBY’
database system which is included as part of
the Java JDK.
• We will be including three jar files in our
CLASSPATH.
www.NetComLearning.com
JDBC
1. First set an environment variable called
DERBY_HOME to the following:
C:\program files\java\jdk1.7.0_03\db\
2. Add to the PATH: %DERBY_HOME%\bin
3. Add to the CLASSPATH:
%DERBY_HOME%\LIB\DERBY.JAR;
%DERBY_HOME%\LIB\DERBYTOOLS.JAR;
%DERBY_HOME%\LIB\DERBYRUN.JAR;
www.NetComLearning.com
JDBC
• To connect to our APACHE DERBY database:
// Register the JDBC driver class
String driver="org.apache.derby.jdbc.EmbeddedDriver";
Class.forName(driver);
// set up the database name
String dbName="jdbcDemoDB";
// define the Derby connection URL to use
String connectionURL = "jdbc:derby:" + dbName +
";create=true";
// establish the connection to the database.
conn = DriverManager.getConnection(connectionURL);
www.NetComLearning.com
JDBC
JDBC connection project exercise.
www.NetComLearning.com
JDBC
• You can execute different types of SQL
statements using a JDBC driver.
• We use different JDBC ‘statement’ objects
depending upon the kind of SQL statement.
• An instance of ‘java.sql.Statement’ interface
represents a SQL statement in a java program.
• three interfaces to represent SQL statements:
1. Statement
2. Prepared Statement
3. Callable Statement
www.NetComLearning.com
JDBC
• If you have a SQL statement in the form of a string, you
can use a ‘Statement’ object to execute it. These SQL
statements are compiled each time they are executed.
• If you want to pre-compile a SQL statement once and
execute it multiple times, use a ‘PreparedStatement’
object. It lets you specify a SQL statement in the form
of a string that uses placeholders. You supply the
values of the placeholders before executing the
statement.
• Use a ‘CallableStatement’ object for a stored
procedure or function in a database.
www.NetComLearning.com
JDBC
• When you execute a SQL statement, the DBMS
may return zero or more results. The results
may include ‘update counts’, for example, the
number of records affected in the database, or
the results may include what are known as
‘result sets’, which consists of a group of
records that have been retrieved from the
database.
www.NetComLearning.com
JDBC
• To execute a SQL statement using a
‘Statement’ object:
1. Get a connection object.
Connection conn = … (get a Connection object)
2. Use the connection object to create a
‘Statement’ object:
Statement stmt = conn.createStatement();
www.NetComLearning.com
JDBC
3. Set up your SQL in a string:
String sql = “update person set income=income*1.1”;
4. Execute the statement by calling one of the
‘execute’ methods of the ‘Statement’ object:
int rowsUpdated = stmt.executeUpdate(sql);
5. Close the ‘Statement’ object to release
resources:
stmt.close();
6. Commit the transaction to the database:
conn.commit();
www.NetComLearning.com
JDBC
• ‘Statement’ interface ‘execute()’ method is used
to execute a SQL statement which does not
return a value, such as ‘CREATE TABLE’.
• ‘executeUpdate()’ method is used for SQL that
updates a database, as in ‘INSERT’, ‘UPDATE’ and
‘DELETE’ SQL statements. It returns the number
of rows affected.
• ‘executeQuery()’ is used for SQL that produces a
resultset, as in ‘SELECT’ SQL statements.
www.NetComLearning.com
JDBC
• When you connect to a database the ‘autocommit’ property for the ‘Connection’ object is
set to ‘true’ by default. If a connection is in autocommit mode, a SQL statement is committed
automatically after its successful execution.
• If a ‘Connection’ is not in auto-commit mode, you
must call the ‘commit()’ or ‘rollback()’ method of
the ‘Connection’ object to commit or rollback the
transaction.
www.NetComLearning.com
JDBC
•
•
•
•
•
•
•
•
•
•
•
•
try {
Connection conn = get the connection…
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String sql = “update person set income=income*1.1”;
int rowsUpdated = stmt.executeUpdate(sql);
stmt.close();
conn.commit();
conn.close();
}
catch (SQLException e)
{ conn.rollback(); e.printStackTrack(); conn.close();}
www.NetComLearning.com
JDBC
JDBC database update exercise
www.NetComLearning.com
JDBC
• The ‘PreparedStatement’ object pre-compiles
the SQL statement and reuses the precompiled SQL statement if the statement in
executed multiple times.
• A question mark (?) in a SQL string is a
placeholder for an input parameter, whose
value will be supplied before the statement is
executed.
www.NetComLearning.com
JDBC
String sql = “insert into person” +
“person_id,first_name,last_name,gender,”+
”values (?,?,?,?,?,?)”;
• Each of the ?s is a placeholder for a value. The first ?
is a placeholder for person_id, the second ? is for
first_name, etc. The first placeholder is given an
index of 1, the second placeholder is given an index
of 2, etc.
Prepared Statement pstmt = conn.preparedStatement(sql);
pstmt.setInt(1,801);
www.NetComLearning.com
JDBC
String sql = “insert into person” +
“person_id,first_name,last_name,gender,”+
”values (?,?,?,?,?,?)”;
Connection conn = get a connection object…;
Prepared Statement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,801);
pstmt.setString(2,”Tom”);
pstmt.setString(3,”Baker”);
pstmt.setString(4,”M”);
java.sql.Date dob = java.sql.Date.valueOf(“1970-01-25”);
pstmt.setDate(5,dob);
pstmt.setDouble(6,45900);
pstmt.executeUpdate();
www.NetComLearning.com
JDBC
JDBC PreparedStatement Exercise
www.NetComLearning.com
JDBC
• When you execute a query (a ‘SELECT’ statement)
in a database, it returns the matching records in
the form of a ‘ResultSet’.
• This is the data arranged in rows and columns.
• The ‘SELECT’ statement determines the number
of rows and columns that are contained in a
‘ResultSet’.
• The ‘Statement’ or ‘PreparedStatement’ or
‘CallableStatement’ object returns the result of a
query as a ‘ResultSet’ object.
www.NetComLearning.com
JDBC
• Scrollability
• A ‘ResultSet’ can be forward-only or bidirectional (move from row to row forwards
or backwards).
• A bi-directional ‘ResultSet’ can be ‘updatesensitive’ or ‘update-insensitive’ (whether
changes in the underlying database will be
reflected in the ‘ResultSet’ while you are
scrolling through its rows).
www.NetComLearning.com
JDBC
• Concurrency
• A ‘ResultSet’ may be ‘read-only’ or
‘updatable’.
• Holdability
• This property of a ‘ResultSet’ refers to its state
after a transaction which it is associated with,
has been committed. The ‘ResultSet’ may be
closed or kept open after commitment of the
transaction.
www.NetComLearning.com
JDBC
• You can get information about the properties
of a ‘ResultSet’ supported by a JDBC driver by
using methods of the ‘DatabaseMetaData’
interface.
• First we get a ‘DatabaseMetaData’ object as
follows:
1. Connection conn = get a connection….
2. DatabaseMetaData dmd = conn.getMetaData();
www.NetComLearning.com
JDBC
boolean b1 = dmd.supportsResultSetType(TYPE_FORWARD_ONLY);
boolean b2 = dmd.supportsResultSetType(TYPE_SCROLL_SENSITIVE);
boolean b3 = dmd.supportsResultSetType(TYPE_SCROLL_INSENSITIVE);
boolean b4 = dmd.supportsResultSetConcurreny(TYPE_FORWARD_ONLY,
CONCUR_READ_ONLY);
boolean b5 = dmd.supportsResultSetConcurreny(TYPE_FORWARD_ONLY,
CONCUR_UPDATABLE);
boolean b6 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_SENSITIVE,
CONCUR_READ_ONLY);
boolean b7 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_SENSITIVE,
CONCUR_UPDATABLE);
boolean b6 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_INSENSITIVE,
CONCUR_READ_ONLY);
boolean b7 = dmd.supportsResultSetConcurreny(TYPE_SCROLL_INSENSITIVE,
CONCUR_UPDATABLE);
boolean b8 = dmd.supportsResultSetHoldability(HOLD_CURSORS_OVER_COMMIT);
boolean b9 = dmd.supportsResultSetHoldability(CLOSE_CURSORS_AT_COMMIT);
www.NetComLearning.com
JDBC
• Some additional ‘DatabaseMetaData’
methods:
String dbName = dmd.getDatabaseProductName();
String dbVersion = dmd.getDatabaseProductVersion();
String driverName = dmd.getDriverName();
String driverVersion = dmd.getDriverVersion();
www.NetComLearning.com
JDBC
• JDBC DatabaseMetaData exercise
www.NetComLearning.com
JDBC
• Typical way to get a forward-only ‘ResultSet’:
Connection conn = get a Connection object …
Statement stmt = conn.getStatement();
String sql = “select person_id, first_name,”+
“last_name,dob,income from person”;
ResultSet rs = stmt.executeQuery(sql);
//now process the ‘ResultSet’ using the ‘rs’ variable.
www.NetComLearning.com
JDBC
• The returned ‘ResultSet’ object from the
‘executeQuery()’ method is ready to be
looped thru to get the associated data.
• At first, the row pointer(aka ‘cursor’) points to
before the first row of the ‘ResultSet’.
• We must move the cursor to a valid row
before accessing the column data for the row.
• The ‘next()’ method of the ‘ResultSet’ object
is used to move the cursor to the next row.
www.NetComLearning.com
JDBC
ResultSet rs = stmt.executeQuery(sql);
While (rs.next()==true)
{
//process the current row in rs…
}
//done with the ResultSet rs.
www.NetComLearning.com
JDBC
• A ‘ResultSet’ object lets you read the value of
a column from its current row using one of the
‘getXXX()’ methods, where ‘XXX’ is the data
type of the column.(e.g. ‘getInt()’, ‘getString()’)
• You must specify the index of column name in
the ‘getXXX()’ method whose value you want
to read.
int personID = rs.getInt(“person_id”);
String firstName = rs.getString(2);
www.NetComLearning.com
JDBC
Connection conn = get a Connection object …
Statement stmt = conn.getStatement();
String sql = “select person_id, first_name,”+
“last_name,dob,income from person”;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int personID=rs.getInt(“person_id”);
String firstName=rs.getString(“first_name”);
String lastName = rs.getString(“last_name);
java.sql.Date dob = rs.getDate(“dob”);
double income = rs.getDouble(“income”);
//do something with the retrieved values from the cols.
}
www.NetComLearning.com
JDBC
Connection conn = get a Connection object …
Statement stmt = conn.getStatement();
String sql = “select person_id, first_name,”+
“last_name,dob,income from person”;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int personID=rs.getInt(1);
String firstName=rs.getString(2);
String lastName = rs.getString(3);
java.sql.Date dob = rs.getDate(4);
double income = rs.getDouble(5);
//do something with the retrieved values from the cols.
}
www.NetComLearning.com
JDBC
• In a ‘ResultSet’ a column can have a null
value. When a column has a null value a
‘getXXX()’ method returns the default value
for the ‘XXX’ data type. For example, for
numeric data types (int, double, byte) a
‘getXXX()’ method returns 0.
• If you want to know if the col value read was
null, you need to call the ‘wasNull()’ method
immediately after the ‘getXXX()’ call.
www.NetComLearning.com
JDBC
ResultSet rs = get a ‘ResultSet’…
java.sql.Date dob = rs.getDate(“dob”);
If (rs.wasNull()) {
System.out.println(“dob is null”);
}
else {
System.out.println(“dob is:” +
dob.toString());
}
www.NetComLearning.com
JDBC
JDBC ‘ResultSet’ exercise
www.NetComLearning.com
JDBC
• To get a ‘ResultSet’ that scrolls forwards and
backwards, we specify the scrollable property
when you create a ‘Statement’ object, as
follows:
Connection conn = get Connection object…
Statement stmt = conn.getStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
www.NetComLearning.com
JDBC
• When you specify scrollability in creating a
‘Statement’ object, if the JDBC driver does not
support that type of scrollability, it will not
generate an error. Rather, the driver will
return a ‘ResultSet’ with a scrollability type
that it does support.
• We can check the scrollability type of a
returned ‘ResultSet’ object with method
‘getType()’.
www.NetComLearning.com
JDBC
ResultSet rs = stmt.executeQuery(sql);
//determine the scrollability type of the ResultSet
int cursorType = rs.getType();
if (cursorType == ResultSet.TYPE_FORWARD_ONLY) { … }
if (cursorType == ResultSet.TYPE_SCROLL_INSENSITIVE) { … }
if (cursorType == ResultSet.TYPE_SCROLL_SENSITIVE) { … }
//determine the concurrency of the ResultSet
int concurrency = rs.getConcurrency();
if (concurrency == ResultSet.CONCUR_READ_ONLY) { … }
if (concurrency == ResultSet.CONCUR_UPDATABLE) { … }
www.NetComLearning.com
JDBC
• To determine the number of rows in the set after retrieving the bi-directional ‘ResultSet’
object, call the ‘last()’ method to move the cursor
to the last row of the set, then call the ‘getRow()’
method to get the row number of the last row,
which will be the number of rows in the set.
• To process the ‘ResultSet’ after determining the
number of rows, call ‘beforeFirst()’ to scroll the
cursor before the first row and then loop thru.
www.NetComLearning.com
JDBC
• Cursor movement methods:
//Movements relative to current cursor position:
boolean next();
boolean previous();
boolean relative(int rows); // can move either direction
//Movements to absolute row numbers
boolean first();
boolean last();
void beforeFirst();
void afterLast();
boolean absolute(int row);//positive row# - from beginning
//negative row# - from end
www.NetComLearning.com
JDBC
• Knowing current cursor position
boolean isBeforeFirst()
boolean isFirst()
boolean isLast()
boolean isAfterLast()
int getRow() //returns 0 if the cursor is positioned
// before the first row,
// after the last row,
//or if the ResultSet is empty.
www.NetComLearning.com
JDBC
• You can close a ‘ResultSet’ object by calling its
‘close()’ method.
rs.close();
• Closing the ‘ResultSet’ frees the resources
associated with it.
• When the ‘Statement’ object that produces
the ‘ResultSet’ object is closed, it
automatically closes the ‘ResultSet’ object.
www.NetComLearning.com
JDBC
JDBC bi-directional ResultSet exercise.
www.NetComLearning.com
JDBC
• You can perform insert, update and delete
actions using a ‘ResultSet’.
• The concurrency for the ‘ResultSet’ object
must be ‘ResultSet.CONCUR_UPDATABLE’.
• There is an imaginary row in a ‘ResultSet’ that
is called an ‘insert-row’.
• Think of this row as an empty new row which
acts as a staging area for a new row to be
inserted.
www.NetComLearning.com
JDBC
• You position the cursor to the insert-row using
the ‘ResultSet’ object’s ‘moveToInsertRow()’
method.
• When the cursor moves to the insert-row, it
remembers its previous position.
• You can call the ‘moveToCurrentRow()’
method to move the cursor from the insertrow back to the previously current row.
www.NetComLearning.com
JDBC
• Once the cursor is positioned at the insertrow, you need to set the values for the
columns using one of the ‘updateXXX()’
methods of the ‘ResultSet’ object where ‘XXX’
is the type of the column.
• The first argument to ‘updateXXX()’ is either
the column index of the column name, and
the second argument is the value to be set.
www.NetComLearning.com
JDBC
• Next we must send the changes to the database
before our new row becomes part of our
‘ResultSet’ object.
• You can send the newly inserted row to the
database by calling the ‘insertRow()’ method of
the ‘ResultSet’ interface.
• If ‘auto-commit’ enabled for the ‘Connection’,
the transaction will be committed immediately.
• Otherwise, ‘commit()’ the transaction to make
the insert permanent (or ‘rollback()’ to cancel the
transaction).
www.NetComLearning.com
JDBC
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// Get the result set
ResultSet rs = stmt.executeQuery(SQL);
// Make sure our resultset is updatable
int concurrency = rs.getConcurrency();
if (concurrency != ResultSet.CONCUR_UPDATABLE) {
System.out.println("JDBC driver does not " + "support updatable result set.");return;}
// First insert a new row to the ResultSet
rs.moveToInsertRow();
rs.updateInt("person_id", 555);
rs.updateString("first_name", "Richard");
rs.updateString("last_name", "Castillo");
rs.updateString("gender", "M");
// Send the new row to the database
rs.insertRow();
www.NetComLearning.com
JDBC
To update an existing row in a ‘ResultSet’:
• Move cursor to valid row of ‘ResultSet’.
• Call an ‘updateXXX()’ method on a column.
• Call the ‘updateRow()’ method to send the
changes to the database.
• If ‘auto-commit’ enabled, changes sent to
database immediately.
• Otherwise, you commit the changes.
www.NetComLearning.com
JDBC
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
// Get the result set
ResultSet rs = stmt.executeQuery(SQL);
// Make sure our resultset is updatable
int concurrency = rs.getConcurrency();
if (concurrency != ResultSet.CONCUR_UPDATABLE) {
System.out.println("JDBC driver does not " + "support updatable result set.");return;}
// Give everyone a raise
while (rs.next()) {
double oldIncome = rs.getDouble("income");
double newIncome = 0.0;
If (rs.wasNull()) {
// The person with no income starts at 10000.00
oldIncome = 10000.00;
newIncome = oldIncome;}
else {// Increase income
newIncome = oldIncome + oldIncome * (raise / 100.0);}
// Update the income column for current row to newIncome
rs.updateDouble("income", newIncome);
// Send the changes to the database before we scroll to next row
rs.updateRow();
}
www.NetComLearning.com
JDBC
To delete a row from a ‘ResultSet’:
• Position the cursor at a valid row.
• Call the ‘deleteRow()’ method of the
‘ResultSet’ object.
• If ‘auto-commit’ is enabled, row will be
permanently deleted from the database.
• Otherwise, commit the transaction, or cancel
the delete by rolling back the transaction.
www.NetComLearning.com
JDBC
JDBC ResultSet Update Exercise
www.NetComLearning.com
JDBC
• The interface ‘RowSet’ from the ‘javax.sql’
package is a wrapper for ‘ResultSet’ and
inherits from the ‘ResultSet’ interface.
• A ‘RowSet’ allows for simpler JDBC
programming. You need not deal directly with
‘Connection’ and ‘Statement’ objects, all you
need to work with is the ‘RowSet’ object.
www.NetComLearning.com
JDBC
• A ‘ResultSet’ is not serializable, where as a
‘RowSet’ is serializable.
• A ‘ResultSet’ object is always connected to a
database, whereas a ‘RowSet’ object need not be
connected to a data source at all times. It can
connect to a database when required, e.g. when
retrieving/updating data in the database.
• A ‘RowSet’ object is by default scrollable and
updatable.
www.NetComLearning.com
JDBC
• The ‘javax.sql.RowSet’ package defines five
interfaces which inherit from the ‘RowSet’
interface, which itself inherits from the
‘ResultSet’ interface.
• Therefore, all the methods of the ‘ResultSet’
interface are available in these five types of
rowsets.
• Typically, database vendors provide
implementation classes for the five types of
rowsets.
www.NetComLearning.com
JDBC
The five types of rowsets are:
•
•
•
•
•
JdbcRowSet
CachedRowSet
WebRowSet
FilteredRowSet
JoinRowSet
www.NetComLearning.com
JDBC
• The ‘javax.sql.rowset.RowSetFactory’ interface
lets you create the different types of ‘RowSet’
objects.
• To get an instance of ‘RowSetFactory’ we use the
static ‘newFactory()’ method of the
‘javax.sql.RowSetProvider’ class.
• The ‘RowSetFactory’ interface has five methods
to create the five types of rowsets.
• They are named ‘createXXXRowset()’ where XXX
could be ’Jdbc’,‘Cached’, ‘Web’,‘Filtered’, and
’Join’.
www.NetComLearning.com
JDBC
try
{
RowSetFactory rsf = RowSetProvider.newFactory();
JdbcRowSet jrs = rsf.createJdbcRowSet();
//… work with the JdbcRowSet…
}
catch (SQLException e)
{
e.printStackTrace();
}
www.NetComLearning.com
JDBC
• A ‘RowSet’ object will need to connect to a data
source to retrieve and update data.
• A ‘RowSet’ will use a JDBC driver to connect to a
database. Set the connection properties as
follows:
RowSet rs = … get a RowSet object …;
Rs.setUrl = “jdbc:derby:jdbcDemoDb”;
Rs.setUsername = “user”;
Rs.setPassword = “password”;
• You don’t need to establish connection to the
database, the ‘RowSet’ object will take care of it.
www.NetComLearning.com
JDBC
• When you work with a ‘RowSet’ you need not worry
about ‘Statement’, ‘PreparedStatement’ etc. objects.
• However you must specify a command that will
generate the result set for the ‘RowSet’ object.
• The command would be in the form of a SQL statement
in a string.
• You can use a ‘?’ as a placeholder for any parameter to
be passed to the command at runtime.
• To set the parameter at runtime, use one of the
‘setXXX()’ methods of the ‘RowSet’ interface.
www.NetComLearning.com
JDBC
RowSet rs = … get a RowSet object …
String sql=“select person_id,first_name”+
“last_name from person”;
rs.setCommand(sql);
www.NetComLearning.com
JDBC
RowSet rs = … get a RowSet object …
String sql=“select person_id,first_name”+
“last_name from person”+
“where income between ? and ?”;
rs.setDouble(1,20000.0);
rs.setDouble(2,40000.0);
rs.setCommand(sql);
www.NetComLearning.com
JDBC
To populate a ‘RowSet’ object with data by
executing its command, you call its ‘execute()’
method:
rs.execute();
After the ‘execute()’ method is executed, the
‘RowSet’ object has data in it, and you need to
scroll to a row to read/update its column value.
www.NetComLearning.com
JDBC
• A ‘RowSet’ inherits all cursor movement methods
from the ‘ResultSet’ interface.
• By default, all ‘RowSet’ objects are bi-directional
scrollable and updateable.
rs.execute();
while (rs.next()) {
int personID = rs.getInt(“person_id”);
String firstname=rs.getString(“first_name”);
}
www.NetComLearning.com
JDBC
• Updating data in a ‘RowSet’ is similar to
updating data in a ‘ResultSet’ object.
• To update a columns value, you need to move
the cursor to a row and use one of the
‘updateXXX()’ methods to set the new value
for the column.
• Then you call the ‘updateRow()’ method of
the ‘RowSet’ object.
www.NetComLearning.com
JDBC
• To insert a new row, move the cursor to the
insert-row by calling ‘moveToInsertRow()’
method of the ‘RowSet’.
• Set the column values in the insert-row by
using the ‘updateXXX()’ methods of ‘RowSet’.
• Then call ‘insertRow()’ method of ‘RowSet’ to
insert the row into the ‘RowSet’.
www.NetComLearning.com
JDBC
• To delete a row, move the cursor to the row
you want to delete and call the ‘deleteRow()’
method of the ‘RowSet’.
www.NetComLearning.com
JDBC
How and when the changes to a ‘RowSet’ are
actually propagated to the database depends on
the type of ‘RowSet’.
www.NetComLearning.com
JDBC
The five types of rowsets are:
•
•
•
•
•
JdbcRowSet
CachedRowSet
WebRowSet
FilteredRowSet
JoinRowSet
www.NetComLearning.com
JDBC
• A ‘JdbcRowSet’ is also called a ‘connectedrowset’ because it always maintains a database
connection.
• As a ‘ResultSet’ object always maintains a
database connection so does the ‘JdbcRowSet’
• The ‘setAutoCommit()’ method enables or
disables auto-commit mode.
• Methods ‘commit()’ and ‘rollback()’ allow you to
commit or rollback changes to data.
www.NetComLearning.com
JDBC
• Updating data using a ‘JdbcRowSet’ object is
similar to updating for a ‘ResultSet’.
• All methods will be called on the ‘JdbcRowSet’
instead of the ‘ResultSet’ object.
• You must call the ‘updateRow()’ method of
the ‘JdbcRowSet’ after updating the column
value(s) and before you scroll to another row.
• Use the ‘JdbcRowSet’ object’s ‘commit()’ or
‘rollback()’ methods to commit or rollback.
www.NetComLearning.com
JDBC
JdbcRowSet Update exercise
www.NetComLearning.com
JDBC
• A ‘CachedRowSet’ is also called a
‘disconnected rowset’. It keeps the database
connection open only for the duration it needs
to interact with the database.
• It retrieves all data generated by the
command and caches it in memory.
• A ‘CachedRowSet’ is always serializable,
scrollable, and updateable.
www.NetComLearning.com
JDBC
• Methods to populate a ‘CachedRowSet’
object:
void execute()
void execute(Connection conn)
void populate(ResultSet data)
void populate (ResultSet rs, int startRow)
• All of the above throw ‘SQLException’
www.NetComLearning.com
JDBC
• If you have set the database connection
properties for a ‘CachedRowSet’ you can use the
‘execute()’ method with no parameters.
• Another version of the ‘execute()’ method
accepts a ‘Connection’ object which will be used
to populate the ‘CachedRowSet’ with data.
• The ‘populate()’ method will populate the
‘CachedRowSet’ with data from the ‘ResultSet’
object passed in.
• Another version of the ‘populate()’ method
accepts a starting row number of the ‘ResultSet’
www.NetComLearning.com
JDBC
• You can obtain the number of rows in a
‘CachedRowSet’ using its ‘size()’ method.
• Since a ‘CachedRowSet’ caches all its rows in
memory, it can provide you a count of rows at
any time.
int rowCount = myCachedRowSet.size();
• Retrieving rows from a database for a
‘CachedRowSet’ is similar to that for
‘JdbcRowSet’, except for the additional ‘size()’
method of ‘CachedRowSet’.
www.NetComLearning.com
JDBC
CachedRowSet exercise
www.NetComLearning.com
JDBC
• ‘CachedRowSet’ provides a feature called
‘paging’ to allow you to retrieve rows in chunks.
• Use the ‘setPageSize(int size)’ method to set the
pagesize.
• Use the ‘nextPage()’ method to get subsequent
pages, and use the ‘previousPage()’ method to
get prior pages.
• Both methods return ‘true’ if there are more
pages to retrieve, otherwise they return ‘false’.
• Use an outer loop to retrieve pages, and an inner
loop to retrieve rows of the page.
www.NetComLearning.com
JDBC
String sqlCommand = "select person_id, first_name, last_name from person";
cachedRs.setCommand(sqlCommand);
cachedRs.setPageSize(4); // Set page size
cachedRs.execute();
int pageCounter = 1;
// Retrieve and print person records one page at a time
do {
System.out.println("Page #" + pageCounter +
" (Row Count=" + cachedRs.size() +")");
// Loop and Print the records in the current page
// Increment the page count by 1
pageCounter++;
}
while (cachedRs.nextPage());
www.NetComLearning.com
JDBC
• You can update data in a ‘CachedRowSet’ and
save the changes back to the database.
• Use ‘updateRow()’, ‘insertRow()’,
‘deleteRow()’ methods as with ‘JdbcRowSet’
but in this case these methods DO NOT send
the changes to the database.
• Send changes to the database by calling the
‘acceptChanges()’ method.
www.NetComLearning.com
JDBC
• ‘WebRowSet’ inherits from ‘CachedRowSet’
and adds capabilities to read data from an
XML doct and write data to an XML doct.
• User ‘readXML()’ to read XML data into a
‘WebRowSet, and ‘writeXML’ to export XML
data from a ‘WebRowSet’.
www.NetComLearning.com
JDBC
WebRowSet wrs = … get a web rowset with data …;
//Create a StringWriter object to hold the exported XML
java.io.StringWriter sw = new java.io.StringWriter();
//Write the XML representation of wrs into sw
wrs.writeXML(sw);
//Get the String object from sw
String webRsXML = sw.toString();
• String webRsXML contains the XML
representation of the wrs object.
www.NetComLearning.com
JDBC
Importing XML into a ‘WebRowSet’:
//Create a StringReader object from an XML String
Java.io.StringReader sr = new java.io.StringReader(webRsXML);
//Create an empty WebRowSet object
RowSetFactory rsf = RowSetProvider.newFactory();
WebRowSet newWebRs = rsf.createWebRowSet();
// Import the XML content into the new WebRowSet
newWebRs.readXML(sr);
www.NetComLearning.com
JDBC
WebRowSetXml exercise
www.NetComLearning.com
JDBC
• ‘FilteredRowSet’ inherits from ‘WebRowSet’ and
provides filtering capabilities for a rowset.
• You view the rows based on a set of criteria which
is called a ‘filter’.
• Setting a ‘filter’ to the rowset does not delete
rows from the rowset, rather, you only access
those rows that satisfy the ‘filter’ criteria.
• You can reset the ‘filter’ to view all rows.
• A ‘filter’ is an object of a class that implements
the ‘javax.sql.rowset.Predicate’ interface.
www.NetComLearning.com
JDBC
• ‘JoinRowSet’ inherits from ‘WebRowSet’.
• It provides the ability to combine (or join) two or more
disconnected rowsets into one.
• You retrieve data in multiple rowsets, using
‘CachedRowSet’, ‘WebRowSet’ or ‘FilteredRowSet’.
• Create and empty ‘JoinRowSet’.
• Add all rowsets to the ‘JoinRowSet’ by calling its
‘addRowSet()’ method.
• You may specify the ‘JOIN’ columns in a rowset when
you add a rowset to the ‘JoinRowSet’.
www.NetComLearning.com
JDBC
cachedRs1 = ... get a CachedRowSet …
cachedRs2 = ... get a second CachedRowSet …
String sqlCommand1 = "select person_id, first_name from person " +
"where person_id in (101, 102)";
String sqlCommand2 = "select person_id, last_name from person " +
"where person_id in (101, 102)";
cachedRs1.setCommand(sqlCommand1);
cachedRs2.setCommand(sqlCommand2);
cachedRs1.execute();
cachedRs2.execute();
// Create a JoinRowSet for cachedRs1 and cachedRs2
joinRs = … get a JoinRowSet object …
// Add rowsets to the join rowset - join them based on the person_id column
joinRs.addRowSet(cachedRs1, "person_id");
joinRs.addRowSet(cachedRs2, "person_id");
www.NetComLearning.com
JDBC
• A database transaction consists of one or
more changes as a unit of work.
• A ‘savepoint’ in a transaction is like a marker
that marks a point in a transaction so that, if
needed, the transaction can be undone (rolled
back) up to that point.
www.NetComLearning.com
JDBC
Connection conn = … get a Connection object …
Statement stmt = conn.createStatement();
stmt.execute(“insert into person…”); // insert 1
stmt.execute(“insert into person…”); // insert 2
stmt.execute(“insert into person…”); // insert 3
stmt.execute(“insert into person…”); // insert 4
stmt.execute(“insert into person…”); // insert 5
• At this point we either commit the transaction, which will
insert all five records in the ‘person’ table:
conn.commit();
• Or we rollback the transaction so that no inserts will occur.
conn.rollback();
www.NetComLearning.com
JDBC
• A ‘savepoint’ will let you set a marker in
between any of the ‘INSERT’ statements.
• To mark a ‘savepoint’, call the ‘setSavepoint()’
method of a ‘Connection’ object.
• The ‘setSavepoint()’ method returns a
‘Savepoint’ object which is your marker, and
which you must keep for future use.
www.NetComLearning.com
JDBC
Connection conn = … get a Connection object …
Statement stmt = conn.createStatement();
stmt.execute(“insert into person…”); // insert 1
Savepoint sp1 = conn.setSavepoint();// savepoint 1
stmt.execute(“insert into person…”); // insert 2
Savepoint sp2 = conn.setSavepoint();// savepoint 2
stmt.execute(“insert into person…”); // insert 3
Savepoint sp3 = conn.setSavepoint();// savepoint 3
stmt.execute(“insert into person…”); // insert 4
Savepoint sp4 = conn.setSavepoint();// savepoint 4
stmt.execute(“insert into person…”); // insert 5
Savepoint sp5 = conn.setSavepoint();// savepoint 5
www.NetComLearning.com
JDBC
• We can use another version of the ‘rollback()’
method of the ‘Connection’ object, which
accepts a ‘Savepoint’ object.
• To undo all the changes which were made
after savepoint 4:
conn.rollback(sp4);
• To undo all changes made after savepoint 2:
conn.rollback(sp2);
www.NetComLearning.com
JDBC
• Once you rollback to a particular ‘savepoint’, all
savepoints which were created after the given
‘savepoint’ will be released, and you cannot refer
to them again.
• You can also release a ‘savepoint’ explicitly by
calling the ‘releaseSavepoint(Savepoint sp)’
method of a ‘Connection’ object.
• After a partial rollback, remember to ‘commit()’
the remaining portions of the transaction.
www.NetComLearning.com
JDBC
The purpose of the DAO pattern is to hide the persistence mechanism
of an application from its application logic. In other words, to turn this
sequence of calls
application logic --> persistence mechanism
into this
application logic --> DAO's --> persistence mechanism
The advantage of this abstraction is that you can change the
persistence mechanism without affecting the application logic. All you
need to change is the DAO layer which, if designed properly, is a lot
easier to do than changing all the application logic.
www.NetComLearning.com
JDBC
• In OOP, a useful and important design principle is
“separation of concerns.” The DAO design pattern
helps you comply with this design principle. If you are
not using DAO, then your business logic will be
exposed to the concrete implementation details of the
persistence mechanisms - an undesirable state of
affairs. Use of the DAO design pattern ensures that you
separate your core logic from your persistence
mechanism.
• If your database environment changes, all you need to
change is the DAO layer, which is a lot easier to do than
changing all the application logic.
www.NetComLearning.com
JDBC
//Student.java
{…. Fields, getters, setters….}
// StudentDAO.java
public interface StudentDAO
{
public void insertStudent( Student student);
public Student findStudent( int id);
public void deleteStudent( int id);
}
www.NetComLearning.com
JDBC
// RDBMSDAO.java
public class RDBMSDAO implements StudentDAO
{
public void insertStudent( Student student){
// insertStudent implementation
}
public Student findStudent( int id){
// findStudent implementation
return Student;
}
public void deleteStudent( int id) {
// deleteStudent implementation
}
}
www.NetComLearning.com
JDBC
// Main.java
public class Main
{
public static void main( String[] args)
{
Student student = new Student( ... );
StudentDAO studentDAO = new RDBMSDAO();
studentDAO.insertStudent(student);
}
}
www.NetComLearning.com
JDBC
// OODBMSDAO.java
public class OODBMSDAO implements StudentDAO
{
public void insertStudent( Student student){
// insertStudent implementation
}
public Student findStudent( int id){
// findStudent implementation
return Student;
}
public void deleteStudent( int id) {
// deleteStudent implementation
}
}
www.NetComLearning.com
JDBC
// Main.java
public class Main
{
public static void main( String[] args)
{
Student student = new Student( ... );
StudentDAO studentDAO = new OODBMSDAO();
studentDAO.insertStudent(student);
}
}
www.NetComLearning.com
JDBC
• The ‘DAO Design Pattern’ fits nicely with the
‘Factory Design Pattern’, and both techniques
are often used together.
• Let us first gain an understanding of the
‘Factory Design Pattern’, and then we will
examine how to use it in combination with the
‘DAO Design Pattern’.
www.NetComLearning.com
JDBC
• In real life, factories are manufacturing units that
produce multiple instances of a product. For
instance, a car factory produces cars. The main
responsibility of the car factory is to keep
producing cars of the required type and model.
• In the software environment, you can implement
a factory that returns the required type of
object(s) on demand in OOP. In this case, the
factory decides which classes to instantiate.
www.NetComLearning.com
JDBC
public interface Shape
{
public void drawTheShape();
public void colorTheShape();
}
www.NetComLearning.com
JDBC
public class Circle implements Shape {
private int xPos, yPos;
private int radius;
public Circle( int x, int y, int r) {
xPos = x;
yPos = y;
radius = r;
}
public void drawTheShape() { implementation here }
public void colorTheShape() { implementation here }
}
www.NetComLearning.com
JDBC
public class Rectangle implements Shape {
private int length, height;
public Rectangle( int l, int h){
length = l;
height = h;
}
public void drawTheShape(){ implementation code}
public void colorTheShape(){ implementation code }
}
www.NetComLearning.com
JDBC
public class ShapeFactory {
public static Shape getShape( String shapeType)
{
switch( shapeType) {
case "Circle": return new Circle( 10, 10,20);
case "Rectangle": return new Rectangle(10,20);
}
www.NetComLearning.com
JDBC
public class Test
{
public static void main( String[] args) {
String shapeType=“Circle”;
Shape shape = ShapeFactory.getShape(shapeType);
shape.drawTheShape();
shape.colorTheShape();
}
}
www.NetComLearning.com
JDBC
• You define a ‘Shape’ interface, which defines two public
methods, ‘drawTheShape()’ and ‘colorTheShape()’.
• Classes ‘Circle’ and ‘Rectangle’ implement this interface
and provide implementation of interface methods.
• The ‘factory’ class ‘ShapeFactory’ has a ‘getShape()’
method which, based upon the requested type of shape,
creates a new instance of the appropriate ‘Shape’ class.
• From the main() method of the ‘Test’ class, you supply a
String which indicates which type of ‘Shape’ you want. The
main() method then calls the getShape() method of
ShapeFactory class. The getShape() method creates and
returns a new instance based on the requested type.
www.NetComLearning.com
JDBC
• We will now look at the combination of the ‘DAO
Design Pattern’ with the ‘Factory Design Pattern’.
• Recall that the purpose of the DAO pattern is to
hide the persistence mechanism of an application
from its application logic.
• The advantage of this abstraction is that you can
change the persistence mechanism without
affecting the application logic. All you need to
change is the DAO layer which, if designed
properly, is a lot easier to do than changing all the
application logic.
www.NetComLearning.com
JDBC
//Student.java
{…. Fields, getters, setters….}
// StudentDAO.java
public interface StudentDAO
{
public void insertStudent( Student student);
public Student findStudent( int id);
public void deleteStudent( int id);
}
www.NetComLearning.com
JDBC
// RDBMSDAO.java
public class RDBMSDAO implements StudentDAO
{
public void insertStudent( Student student){
// insertStudent implementation
}
public Student findStudent( int id){
// findStudent implementation
return Student;
}
public void deleteStudent( int id) {
// deleteStudent implementation
}
}
www.NetComLearning.com
JDBC
// OODBMSDAO.java
public class OODBMSDAO implements StudentDAO
{
public void insertStudent( Student student){
// insertStudent implementation
}
public Student findStudent( int id){
// findStudent implementation
return Student;
}
public void deleteStudent( int id) {
// deleteStudent implementation
}
}
www.NetComLearning.com
JDBC
• So now we have two ‘StudentDAO’
implementations – RDBMSDAO and
OODBMSDAO.
• We will define a ‘Factory’ (using the ‘Factory
Design Pattern’), which will have the capability
to produce each type of DAO object.
www.NetComLearning.com
JDBC
public class DAOFactory {
public static StudentDAO getStudentDAO(String
type)
{
switch( type){
case “RDBMS”: return new RDBMSDAO();
Case “OODBMS”: return new OODBMSDAO();
}
}
www.NetComLearning.com
JDBC
• Now we will use our ‘factory’ to persist an
instance of a ‘Student’.
• First we will instantiate a new ‘Student’.
• Then we will use our factory to get a
‘StudentDAO’ of type ‘RDBMSDAO’.
• Then we will use our ‘StudentDAO’ object to
persist our ‘Student’ object.
www.NetComLearning.com
JDBC
public class Test {
public static void main( String[] args) {
Student s1 = new Student(…);
StudentDAO DAO =
DAOFactory.getStudentDAO("RDBMS");
DAO.insertStudent( s1);
}
}
www.NetComLearning.com
Localization
When adapting an application to an
international market, differences to be
accounted for include:
o Differences in language;
o Differences in display of numbers;
o Differences in display of date information.
120
Localization
The classes that take these differences into
account make use of a ‘Locale’, which is an
instance of the ‘Locale’ class which describes:
• A language
• A location (typically a country)
• Optionally, a variant, specifying features such
as dialects, or spelling rules.
121
Localization
Java uses codes that were defined by the
International Organization for Standardization
(ISO).
The local language is expressed as a lowercase
two-letter code.
The country code is expressed as an uppercase
two-letter code.
122
Localization
Examples of language codes:
English
French
German
Italian
en
fr
de
it
123
Localization
• Examples of country codes:
Canada
Germany
Great Britain
Italy
United States
CA
DE
GB
IT
US
124
Localization
Given a language code, or a language code and a
country code, we can construct a ‘Locale’ object:
Locale french = new Locale(“fr”);
Locale germanSwiss = new Locale(“de”,”CH”);
Local germanGermany = new Locale(“de”,”DE”);
125
Localization
Java has pre-defined ‘Locale’ objects, such as:
Locale.CANADA
Locale.CANADA_FRENCH
Locale.TAIWAN
Java also has pre-defined locales that just
specify a language without a location:
Locale.CHINESE
Locale.ENGLISH
126
Localization
• The static method ‘getDefault()’ gets the
currently default locale.
• The static method ‘setDefault()’ allows you to
change the default locale.
• The static method ‘getAvailableLocales()’
returns an array of locales they support.
127
Localization
Other methods of the Locale class provide
information about a particular Locale.
• String getDisplayName()
• String getDisplayLanguage()
• String getDisplayCountry()
128
Localization
Formatting of numbers in highly localedependent. Java provides ‘formatter objects’
that can format and parse numbers, based upon
the specified Locale.
NumberFormat NFormat =
NumberFormat.getInstance(locale);
NFormat.format(tenMillion);
NFormat.parse(String-number);
129
Localization
Formatting of monetary amounts is also highly
locale-dependent. Java similarly provides
currency formatter objects for this purpose:
NumberFormat nf =
NumberFormat.getCurrencyInstance(locale);
nf.format(tenMillion));
130
Localization
There are also methods for obtaining
information about currencies, such as the
currency symbol, and the currency name.
Currency currencyInstance =
Currency.getInstance(locale);
currencyInstance.getSymbol();
currencyInstance.getDisplayName();
131
Localization
When formatting date and time information,
some issues of concern include:
• Names of months and weekdays should be
presented in the local language.
• The order of the day, month, and year will be
determined by the particular locale in use.
132
Localization
We have methods to help us localize the display
of date information based upon the supplied
locale:
DateFormat dateFormat =
DateFormat.getDateInstance(DateFormat.FULL,
locale);
dateFormat.format(today)
133
Localization
In addition to locale information, there are
different styles for displaying dates, such as:
DateFormat.SHORT
DateFormat.MEDIUM
DateFormat.LONG
DateFormat.FULL
134
Localization
Furthermore, time information is often displayed based
upon localization:
DateFormat dtfmt =
DateFormat.getDateInstance(DateFormat.SHORT, locale);
DateFormat tmfmt =
DateFormat.getTimeInstance(DateFormat.SHORT, locale);
DateFormat dttmfmt =
DateFormat.getDateTimeInstance(DateFormat.MEDIUM,
DateFormat.FULL, locale);
135
Localization
• When localizing an application,you may have
many Strings (messages, button labels, etc)
that need to be translated to different
languages.
• We will define the message Strings in an
external location, called a ‘resource’.
• For different languages, work with the
external resource files without having to touch
the source code.
136
Localization
• In Java, you can use property files to specify
string resources, or you can implement
classes for resources of other types.
• For an application, you provide a set of
‘resource bundles’ where each bundle will
have the String messages, for example, for a
given locale.
• For each ‘bundle’, you provide a version for
each relevant locale.
137
Localization
We have a specific naming convention for these
bundles:
• Resources specific to Germany will go into a
file ‘bundleName_de_DE’
• Resources specific to USA will go into a file
‘bundleName_en_US’
• Resources shared by all French speaking
countries will go into ‘bundleName_fr’
138
Localization
Internationalization with property files:
• Place your Strings into a property file such as
‘StringProps.properties’.
• This is a text file with one key/value pair per
line.
• Name the file as per previous slide:
– StringProps_en_US.properties
– StringProps_de_DE.properties
– StringProps_fr.properties
139
Localization
In the code, you will:
• Load the bundle:
ResourceBundle bundle =
ResourceBundle.getBundle(“StringProps”,locale)
• To look up a specific String:
String label = bundle.getString(“Greeting”);
140
Localization
Internationalization with Bundle classes:
• To provide resources that are not Strings, create
classes that extend ‘ListResourceBundle’ class.
• Use the standard convention to name the classes:
bundleName_de_DE
bundleName_fr
• Load the class with the ‘getBundle()’ method:
ResourceBundle bundle =
ResourceBundle.getBundle(“MyBundle”,locale)
141
Localization
class MyBundle_en_US extends
ListResourceBundle{
private static final Object[][]contents=
{
{“phoneMaker”,”Apple”},
{“battery life”, new double(16.0)}
}
public Object[][] getContents()
{ return contents;}
}
142
Localization
class MyBundle_de extends ListResourceBundle{
private static final Object[][]contents=
{
{“phoneMaker”,”Siemens”},
{“battery life”, new double(8.5)}
}
public Object[][] getContents()
{ return contents;}
}
143
Java 7 Boot Camp
Java Boot Camp
•
•
6 days, 60 hours
Includes 2 test vouchers
•
•
•
•
•
1Z0-803 : Oracle Certified Associate, Java SE 7 Programmer
1Z0-804 : Oracle Certified Professional, Java SE 7 Programmer
Courseware included
In class Instructor-led or Live Online instructor led
Las Vegas Boot Camp is all inclusive
Upcoming Schedules:
• October 6th in Las Vegas or Live Online
• October 20th in NYC & Live Online
Locations: Attend in-class or Live Online Instructor-led
•
•
•
New York
Las Vegas (All Inclusive Travel Package)
Arlington VA
www.netcomlearning.com
Love of Learning
Empowering The Disadvantaged
Your class enrollment at
NetCom Learning will pay for
a child’s education for a year!
www.netcomlearning.com
Q&A
www.NetComLearning.com
1-888-5-NETCOM (563-8266)
[email protected]