Please send questions to
st10@humboldt.edu .
CIS 480 - Advanced Java - Random Notes, 4-16-01
--------------------
INTRO to JDBC
* JDBC - Java DataBase Connectivity
* provided by the java.sql package;
* 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
* [1], p. 324: "JDBC is an API that allows a Java program to
communicate with a database server using SQL ... commands."
* "The java.sql package provides a fairly straightforward
mechanism for
* ...sending SQL queries to a database and for
* ...receiving query results."
* "...in order to run the examples in this chapter, you need
* ...access to a database and
* ...you have to obtain and install a JDBC driver for it."
* hmm! We're set for the student database on redwood, since
CIS 318 already uses it with JDBC; if I succeed in getting
this set up on my laptop's copy of Access, I will pass
that information on to you (and we'll see if we can indeed
build a Java example that works for either!)
* [2], p. 227: "The following basic steps are involved in
developing JDBC applications:
1. Import the JDBC classes (java.sql.*).
2. Load the JDBC drivers.
3. Connect to the database.
4. Interact with the database using JDBC.
5. Disconnect from the database."
* so --- SIMPLE EXAMPLE TIME --- see TryJDBC1.java!
* TryJDBC1.java
* a simple non-GUI Java application
* uses account java/java on Oracle redwood student database
* (can easily modify it to use YOUR now-created-or-reactivated
Oracle accounts on the redwood Oracle student database;
thanks to Peter Johnson!!
* NEED to add
/home/univ/oracle/products/jdbc/lib/classes12.zip
...to your CLASSPATH on sorrel;
* now, we are still having trouble getting applets to connect to the
redwood Oracle db, so we will stick with applications running on sorrel
for now (a platform we KNOW can work)
* so, overview of a Java application on sorrel using JDBC so far:
* import java.sql.*;
* load the JDBC driver:
Class.forName("oracle.jdbc.driver.OracleDriver");
* create a connection to Oracle student database on
redwood, using account name java and password java:
'thin' is the drivertype
|
| port number
v |
Connection con = DriverManager.getConnection v
("jdbc:oracle:thin:@redwood:1521:student",
"java", "java"); ^ ^
| |
* note that this is address of machine? |
^
|
* note that student is name of database ---
* note that our applet's connection string will look
very different --- it has to be able to connect via
who-knows-where...!
* via the Connection object you have just created, you
can create a statement object using createStatement():
Statement myStmt = con.createStatement();
* (a Connection can ALSO send a PreparedStatement
(see below) or a CallableStatement (used for executing
` stored procedures))
* build a String containing the SQL command:
String myQuery = "select ename, job from emp";
* (do not forget to CLOSE Statement and Connection objects!!)
myStmt.close(), con.close() --- it is IMPORTANT!)
* via the Statement object just created, use the executeQuery()
method to send the query string to be executed --- results are
returned in the form of a ResultSet:
ResultSet myResultSet = myStmt.executeQuery(myQuery);
* some ResultSet methods of note:
* first, note that a ResultSet can be thought
of as containing rows of data, arranged in
columns;
* there is a concept of a CURSOR --- it allows you
to access the rows in a multi-row result, like a
ResultSet, one row at a time;
* it marks where you are in the ResultSet,
and what you are allowed to access;
* cursor starts out JUST ABOVE the first
row of a ResultSet;
* next() - this method, then, [4] "moves what is
called a cursor to the next row [in the ResultSet]
and and makes that row (called the current row) the
one upon which we can operate."
* so, the FIRST call to next() [4] "moves the
cursor to the first row and makes it the current
row"
* returns false when the end is reached ---
when there is no row to move the cursor to.
* so:
while (myResultSet.next() != false)
{
}
...allows you to act on EACH row in a
ResultSet.
* getXXX() - there are a whole collection of
methods (getFloat(), getString(), getInt(), etc.)
that let you grab column values from the current
ResultSet row.
* are versions with an integer column
number parameter, and versions with a
String column name (as we used with
getString() in TryJDBC1.java)
* that column number is in reference to
the ResultSet columns, not the original table(s)
* note --- from [4]: "JDBC allows a lot of
latitude as far as which getXXX methods you use
to retrieve the different SQL types" ---
but some work better/more reasonably than others
in this regard...
* for example: [4] "getInt() can be used to
to retrieve any of the numeric or character
types. The data it retrieves will be converted to
an int..." (so, it would try to parse an integer
out of the char...)
* it is only *recommended* for
use with SQL INTEGER types, though;
and it CANNOT be used with (among others!)
SQL BINARY, DATE, TIME...
* it is possible to use getString() to retrieve
any of the basic SQL types, but, of course,
that value WILL be converted to a String...
* a partial list (!) of getXXX() methods
(from [4])
ResultSet.getXXX method REC'd for JDBC type
----------------------- -------------------
getByte() tinyint
getShort() smallint
getInt() integer
getLong() bigint
getFloat() real
getDouble() float, double
getBigDecimal() decimal, numeric
getBoolean() bit
getString() char, varchar2
getBytes() binary, varbinary
getDate() date
getTime() time
getTimestamp() timestamp
getAsciiStream() varchar2
getUnicodeStream() varchar2
getBinaryStream() longvarbinary
getObject() can be used for all,
but not esp'ly rec'd?
* consider the emp table in java/java:
SQL> describe emp
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
* let's try to handle reading in all of these attributes;
use DemoGetXXX.java
* used getDouble for sal, comm --- seems to look OK
* interesting --- the dates are 2081, etc...? 8-)
* hmmm --- the null commission fields are 0.0...hmmm...
* DemoGetXXXByPos.java is very similar, except it uses the
positional parameters (getString(2), etc.) instead of
String column name parameters. And it works just the same...
* note that there are many options, even on a command-line level ---
build a flat file of Oracle data, interactive input to determine what
information to grab, etc.
* in BuildEmpFile.java, we put the emp table into comma-delimited
flat file form --- might then be importable into another DBMS, for
example (Oracle could read it in using SQL*Loader, for example)
* and, of course, we are not limited to a hard-coded query string!
AskForCol.java builds the query string column name based on user
choice. You can imagine that it would be lovely to have checkboxes
or some such GUI to let users choose desired columns, tables, etc...
* 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.
* 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