Please send questions to
st10@humboldt.edu .
CIS 480 - Advanced Java - Random Notes, 4-23-01
--------------------
INTRO to JDBC, continued
* References:
[1] Chapter 16, "Database Access with SQL", from Java Examples in a
Nutshell, Flanagan, O'Reilly, 1997, pp. 324-348.
[2] Chapter 5, "Oracle JDBC", from Oracle8 Programming: A Primer,
Sunderraman, Addison Wesley, 2000, pp. 225-276.
[3] Ann Burrough's notes for CIS 318 - Database Applications
[4] the Java Tutorial on JDBC, http://java.sun.com/docs/books/tutorial/jdbc
* (we know, from last time, how to set up a Connection object, and a
Statement object, and use Statement method executeQuery() to execute a
query;)
* now: for some NEW goodies:
* now, note that in the examples so far, we have used the Statement
class's executeQuery() method ---
* executeQuery(String sql_com) [from Java 1.2 API] "executes
a SQL command that returns a single ResultSet"
* but --- this method is appropriate only for those SQL
statements that return, well, ResultSet's...
* you use a different method for the SQL statements:
* insert
* update
* delete
* (and SQL statements that return nothing?!)
...you use executeUpdate() instead!
* executeUpdate(String sql_com) returns the number of
rows affected by the insert/update/delete, or 0 if the
SQL command is one that returns nothing.
* execute() can also be used when you don't know what SQL
statement is going to be used... (see API).
* so, for example, I could use executeUpdate() to execute a
create table statement, that doesn't return anything --- let's
create a table in the java database that we can have our way with:
* CreateTable480.java
* for fun: what happens if you run this twice?
* note that, if you run sqlplus on redwood, you can
verify that the table is, indeed there.
* but --- it is EMPTY! That's no good...
* when a statement will be executed more than once, it is often
more efficient (reduces execution time) to use a PreparedStatement
object (instead of the Statement object we have been using thus far):
PreparedStatement pstmt = con.prepareStatement(
"update employees" +
"set salary = ? " +
"where id = ?");
* (you'll note, the above looks very similar to
setting up a Statement, except:
* you can give the query string directly in
the prepareStatement() method
* you've got those ? marks)
// first argument is WHICH question mark in
// pstmt to replace...
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);
* (those ?'s are called IN parameters in Sunderraman)
numUpdated = pstmt.executeUpdate();
* this happens to be a SQL update command --- used to
change the data in tables.
UPDATE tblname
SET attrib = value_expression
WHERE conditn
* see TryPrepStmt.java --- it uses a PreparedStatement
to repeatedly insert rows into table 480 in java/java.
* advantage: the statement is, in a sense, precompiled ---
you just plug in parameters, essentially, on each execution;
* note that a SELECT statement doesn't muck with the data ---
it is non-destructive, doesn't CHANGE anything in the database.
* ah, but SQL UPDATE and DELETE statements --- they CAN change the
database!
* SQL also has concepts of commit and rollback, to
support transactions (to support atomicity of transactions ---
so they are either completely done, or completely NOT done)
* you probably know that a commit is automatically
done --- committing your data to the database ---
when you (for example):
* leave a SQL*Plus session
* execute a commit command
* create, drop, or alter a table;
* you probably remember that a rollback undoes
all changes up to the latest previous commit;
* how does this work in these JDBC connections, though?
* a connection is, by default, in auto-commit mode:
([2], p. 230): "all its SQL statements will be executed
and committed as individual transaction .... The commit
occurs when the statement completes or when the next
execute occurs, whichever comes first."
* Connection's setAutoCommit(boolean) method lets you explicitly
set auto-commit mode on or off;
* if off, then your SQL statements are considered
a transaction, in effect, and are expected to be
terminated by an explicit commit or rollback;
* Connection has commit() and rollback() methods,
that should ONLY be used if auto-commit mode is
set to false. They'll cause the desired commit or
rollback...
* we play with these, some, in TryPrepStmt2.java.
* note, then, that a rollback() might be very handy
in catching exceptions (if an exception is thrown,
rollback() --- and/or only commit() when you KNOW all
is well (if you reach a certain point without any exceptions
being thrown...!)
* (note --- it is perfectly all right to use either a Statement or
a PreparedStatement for a query, an update, a delete, a create, etc. ---
but if you are doing it more than once, the PreparedStatement has a chance
of being more efficient...)
*****************
Metadata
****************
* idea of metadata:
* data about data!
* and, JDBC provides a WEALTH of ways to obtain
database metadata --- data ABOUT the data obtained
from a database using JDBC;
* Both classes Connection and ResultSet have a method getMetaData()
that give you additional information about the Connection or the
ResultSet itself;
*********************
Connection MetaData
*********************
* let's talk about Connection's getMetaData() first:
* so, another interesting Connection class method: getMetaData()
* can give you information about the Connection's ([2], p. 231)
"...tables, its supported SQL grammar, its stored procedures,
the capabilities of this Connection, etc."
* it returns a DatabaseMetaData object...!
* ([2], p. 266) "The literally hundreds of methods that come
with the DatabaseMetaData object class retrieve all kinds of
information about the database system to which a connection is
made." ---
* "Many of these methods return the results in
a ResultSet object"
* why? Because, in a relational database, everything
is tables --- why not the metadata, too?!
* getDatabaseProductName(), getDriverName() return Strings;
* demo'd in example TryConnMetaData1.java
* getTables() returns a ResultSet; (info about the tables
in tabular form...!) But, we cannot "jump" right
into this one without a LITTLE more info;
* note: according to the Java 1.2 API, those
DatabaseMetaData tables that return ResultSet's either
throw a SQLException or they return a ResultSet;
* note, too --- in Oracle (and many database
programs), there can be restrictions on who can
see what, who can change what ---
* now, since we've logged onto a particular corner
of the Student database, we should be able to see all
available to that user --- but are there tables
that getTables() can get that this user cannot perform
select statements on?
allTablesAreSelectable()
...returns true if user can perform select
on all tables returned by getTables()
* interesting --- returns false for our
TryConnMetaData1's connection to Oracle's redwood
student database, for user java;
* now: getTables() has parameters! 4 of them;
* (note that these are the same meanings for
%, _ that you see in SQL pattern matching with
the "like" operator --- so, it is not surprising
to see them used here;)
* NOW we can talk about getTables()'s 4 parameters:
(1) String catalog
* a database catalog name;
* we'll usually drop this from the selection
criteria by specifying null (no quotes around it!!)
for this parameter;
(2) String schemaPattern
* a schema name pattern;
* will we need this?
* an empty String ("") (Java 1.2 API) "returns
those without a schema"
(3) String tableNamePattern
* a pattern to match names of tables in the
database;
* only tables that match this pattern in the
specified catalog and schema have information
returned about them;
(4) String[] types
* table types desired;
* some typical types: "TABLE", "VIEW", "SYSTEM TABLE",
and many more.
* null for this parameter causes all types to
be included;
* ResultSet returned by getTables() has FIVE columns:
(1) TABLE_CAT column type: String
table catalog, and may be null
(2) TABLE_SCHEM column type: String
table schema, and may be null
(3) TABLE_NAME column type: String
table name
(4) TABLE_TYPE column type: String
type of that database object --- "TABLE", "VIEW",
"SYSTEM TABLE", and many other possibilities;
(5) REMARKS column type: String
"explanatory comment on the table"
* NOW let's try getTables() in TryConnMetaData1.java, and
see what we get in the resulting ResultSet columns;
* hint: run it piped to UNIX more command,
so you can scroll through the long results
1 screen at a time:
java TryConnMetaData1 | more
* we will not get into those 100's of other method possibilities;
let me just pique your interest by mentioning a few of them (see the
Java 1.2 API, in package java.sql, following the DatabaseMetaData
interface link;)
* getMaxColumnsInTable()
* "What's the maximum number of columns in a table?"
* getMaxConnections()
* "How many active connections can we have at a time
to this database?"
* "a result of zero means that there is no limit or
the limit is not known" --- rats, that's what we
get with TryConnMetaData1.java;
* getSQLKeywords()
* because you are not always connecting to
the same kind of relational database...!
* and, because different database programs
sometimes ADD to the SQL92 standard;
* this method returns a String, "a comma-separated
list of all a database's SQL keywords that are
NOT also SQL92 keywords" ---
* interesting!
* getURL()
* "What's the url for this database?"
* (returned as a string, of course)
* interesting --- it is just what we used
as the 1st argument to the getConnection() method;
* oh, and MANY more --- about what the database supports
(supportsXXX() methods), and more!
* and, once I've obtained the information about specified
tables, I can always "loop" throught these trying to do something
with them --- and ANOTHER set of metadata can help with this:
*********************
ResultSet MetaData
*********************
* you see, class ResultSet has a getMetaData() method, too...!
* ([2], p. 243) "used to obtain information about the
ResultSet other than the rows"
* it returns a ResultSetMetaData object;
* some methods of a ResultSetMetaData object:([2], pp. 248-250)
* getColumnCount()
* "returns the number of columns in the result set"
* for example, now I can loop through them, if
desired...!
* isNullable(int col)
* "checks to see if a particular column can have a
null value or not"; note that it does not return a
boolean, but an int, one of 3 constants columnNoNulls,
columnNullable, or columnNullableUnknown"
* for example, this can answer the question: is it
safe to not set a value for this particular column
in an insert that I want to build?
* getColumnDisplaySize(int col)
* "returns the column's normal maximum width"
* aha! we could use this to build a "prettier"
tabular display?
* getColumnLabel(int col)
* "returns the suggested column title for use in
printouts and displays", as a String;
* getColumnLabel(int col): "returns the suggested
column title for use in printouts and displays",
as a String;
* getColumnName(int col)
* the actual column name, not the label set up in SQL...
* getPrecision(int col)
* "returns a column's number of decimal digits"
* could make sure, for example, that you are not
about to insert too large a value into that column;
avoid the SQLException;
* getScale(int col)
* "returns a column's number of digits to the right
of the decimal point"
* getTableName(int col)
* "returns the name of the table for the given column"
* perhaps you are using DatabaseMetaData information
about columns in a database --- you might find yourself
in need of knowing what table that column is in...!
* getColumnType(int col)
* "returns the column's SQL type. See java.sql.Types
for a list of SQL types"
* you could have logic to call the appropriate
getXXX() method for that column's type;
* findColumn()
* returns the integer index of a column in a
ResultSet given its name (in String form)
* in case you need it for one of the above methods
that wants the column's index in the ResultSet
as a parameter!
* let's try some: TryRSMetaData1.java
* subtitle 1: making use of some ResultSetMetaData
* subtitle 2: String manipulation city;
* subtitle 3: boy, does this make me appreciate what
SELECT statements do for me automatically
in Oracle SQL*Plus...!
* finally:
----------------
an APPLET that connects to the Oracle student database on redwood!
----------------
* hitch to note: we *are* having trouble getting the "newest" JDBC to
work; we are using an old Oracle JDBC library as a result;
* line that needs to be included within <applet> OR <embed> tag in
html file to run the above applet:
archive = "http://www.humboldt.edu/~aeb3/drivers/lib/classes111.zip"
* you'd think classes12.zip would work; they don't.
* DO you need to muck with your CLASSPATH? I don't know!
I have, in my .cshrc,
setenv CLASSPATH "$CLASSPATH":/home/faculty/st10/public_html/classes:/home/univ/oracle/products/jdbc/lib/classes12.zip:.
...but I have a lot of stuff later, too, for Servlets. Is this needed,
for compiling? I *think* it is.
* see TryJDBCApplet1.java, and TryJDBCApplet1.html;