Notes - People.cs.uchicago.edu
Download
Report
Transcript Notes - People.cs.uchicago.edu
Lesson 5
1. JNI, cont
2. JDBC
3. Intro to Graphics – Image
Processing
Announcements
Homework due tonight midnight!
Between tonight and Thurs at midnight =
10% penalty
After Thurs midnight = no credit
New homework announced Wed.
– Graphics + JDBC + JNI + RMI
– two week deadline
JNI, cont.
JNI Arrays
Finish up reading Lesson4 JNI notes
Today, we will finish by discussing the most
practical topic – arrays.
Multi-dimensional arrays are a complete
headache! In java, these map to arrays of
jobjects, which have to be accessed a row at
a time.
Best to use 1d array even for multid type
calculations!
Smoothing test
Consider algorithm to filter 1d data (see
course examples).
We’ll compare the following:
– pure java version
– native version
We’ll do this on several different platforms
and look at relative timings.
Issues with JNI arrays
Arrays in java mapped to opaque C types:
– jintArray
– jdoubleArray
– jcharArray
– jobejctArray
– etc.
Best method to access elements
Xxx* getXxxArrayElements(JNIEnv, jxxxArray,flag);
Returns pointer to block of array elements
But wait! this pointer may be a pointer to a copy!!
Releasing Arrays
To determine if it is a copy, the flag
parameter is used (see example).
This is obviously crucial for performance –
some jvm’s implement this way, others
don’t.
We’ll see both examples today.
Finally, when finished use:
releaseXxxArrayElements(Xxx*, jXxxArray, flag);
JDBC
Using Java to issue SQL commands
Basic Database Concepts
When to use flat files vs. database?
– Data is simple, static, volume is small, accessed
by one process at a time on single system.
– Cost of database software is prohibitive
– Extremely high performance
– Database is overkill
Databases
Built-in methods to source, access, search
data.
Application independent of internal data
representation – much lower maintenance
costs.
Run in server mode, provides security.
Built-in support for transactions,
concurrency, etc.
Relational Databases
Composed of tables each of which has rows
and columns.
Each row or record represents an entity.
Each column or field represents an attribute.
Like an array of structures in C or Java.
Other concepts: primary key, compound
key, artificial key, foreign key.
Object-Oriented Databases
Not clear exactly when a db officially
becomes OO.
Provide direct support for managing objects
and relationships among them – data +
methods.
Gaining popularity but still far less common
than relational counterpart.
Many SQL vendors support some object
extensions.
SQL
Used to stand for “Structured Query
Language”.
Standard language for conversing with
relational databases.
Composed of three sub-languages:
– Data Definition Language (DDL)
– Data Control Language (DCL)
– Data Manipulation Language (DML)
DDL
Lets you define and revise the structure of
relational databases. Examples:
Create Database name
[options]
Create Table name
( columname datatype, … )
Only simple datatypes supported.
DCL
Lets user specify data security and integrity
mechanisms that safeguard data
Not very standardized – varies from vendor
to vendor.
DML
Functionality for retrieving, manipulating,
deleting, sorting, searching data.
Examples just to get flavor:
–
–
–
–
Select * From table;
Select columns From tables [Where condition];
Select ItemNo, Qty From InvoiceLine;
Insert Into InvoiceLine;
(InvoiceNo, LineNo, CustomerNo)
Values (101, 100, 10);
How to use SQL
Database vendor typically supplies GUI front-end
for issuing SQL queries.
Also usually supplies a scripting front-end for
issuing SQL commands.
– Called Interactive SQL, good for developing and
debugging queries
– Of limited use because cannot share data with program
variables.
From within a programming language
– Embedded SQL
JDBC
Java’s version of Embedded SQL
Interface fully specified in the standard Java
language (ie J2SE).
Independent of database vendor’s specific
SQL implementation.
Vendor supplies middleware driver to
convert JDBC calls to native db hooks.
Similar to Microsoft’s ODBC
Advantages to JDBC model
Application can fairly easily migrate from
one DBMS to another. Almost no code
needs to be rewritten.
Easy to use since db requests return easy-tomanipulate java objects, with simple
methods, java exceptions, etc.
Disadvantages of JDBC
Slower
Cannot take advantage of all SQL
extensions of a particular vendor (though it
can take advantage of many).
Using JDBC on cluster
To use JDBC on the cs cluster, you’ll need to
either install a database or use one of our dbase
servers (mysql or sybase).
In this example I’ll show how to use the myql
server.
First, you must register for a mysql account
https://www.cs.uchicago.edu/info/services/mysql
After registering, try logging on and creating a few tables.
You should have a database under your login name in
which you can create the tables.
Using JDBC
Basic steps for connecting to dbase server
1. Load JDBC driver
2. Define the connection object
3. Establish the connection
4. Create the statement object
5. Execute a query or update with statement
object
6. Process the returned ResultSet
7. Close the Connection
Loading the Driver
Each DBMS vendor must supply the driver class
which converts JDBC calls to their own native db
calls.
This needs to be loaded only once per application.
When loaded, its static initializer is called and the
driver is registered with the DriverManager.
Best technique (assuming our sql driver)
Class.forName(“org.gjt.mm.mysql.Driver”);
– note: you’ll need a copy of
mysql-connector-java-3.0.7-stable-bin.jar
in your classpath.
Define the Connection
Each vendor supplies info on what
connection URL to use.
For mysql installed on cluster the following
works:
String conURL = “jdbc:mysql://dbserver/mydatabase”;
Establish the Connection
Issue the following command to create a
single connection to the database
java.sql.Connection conn =
DriverManager.getConnection(URL);
Create a Statement Object
Once a connection object is obtained, you
must use it to create a Statement.
import java.sql.Statement;
Statement st = conn.createStatement();
Execute Query
To execute standard SQL commands, you
need to pass a valid SQL String to the
executeQuery method of the statement
object. A java object of type ResultSet is
returned.
Import java.sql.ResultSet;
String query = “SELECT * FROM table”;
ResultSet res = st.executeQuery(query);
Process the Results
The ResultSet object is java’s representation of the
data returned from the db query. The most typical
way of manipulating the ResultSet is something
like:
While (res.next()) {
System.out.println(res.getString(1) + “ “ +
res.getString(2) + …);
Study the ResultSet API to see all of the ways in
which the data can be accessed, modified,
modified locally/globally, etc.
ResultSet in more detail
Like an Iterator or Enumerator.
However, must call next() once to move to first
row.
Each call to next then moves to subsequent row.
For the current ResultSet row, there are two ways
to access the values of the columns:
– by String name
• Xxx getXxx(int columnNumber);
– by column number (starting at 1)
• Xxx getXxx(String columName);
Execute update
To execute an update, pass appropriate SQL string
to executeUpdate method:
– e.g.
st.executeUpdate(“UPDATE Books SET Price = Price – 5.00”);
Note that execute can be used for both updates
and queries, though it is clearer to use one or the
other.
executeUpdate returns count of rows modified by
update procedure.
General observations
In executeXXX calls, SQL string need not end
with any specific terminator
st.execute(“Select * from TABLE”);
dbms-specific
Statement objects can be reused for many queries – no
terminator autoneed to create new one each time
matically added
st.execute(blah1);
st.execute(blah2);
Indicate nesting with altnerate single/double quotes
stmt.executeUpdate( "INSERT INTO COFFEES " + "VALUES
('Colombian', 101, 7.99, 0, 0)");
General Observations, cont.
When using ResultSet object, Java will try
to convert to requested type whenever
possible. For example, rs.getString(...) is
valid for any sql type, rs.getInt can also
retrieve floats, etc.
TINYINT: getByte (recommended)
– Can also be read using getShort, getInt,
getLong, getFloat, getDouble, getBigDecimal,
getBoolean, getString, getObject
SMALLINT: getShort (recommended)
– Can also be read using getByte, getInt,
getLong, getFloat, getDouble, getBigDecimal,
getBoolean, getString, getObject
INTEGER: getInt (recommended)
– Can also be read using getByte, getShort,
getLong, getFloat, getDouble, getBigDecimal,
getBoolean, getString, getObject
BIGINT: getLong (recommended)
– Can also be read using getByte, getShort,
getInt, getFloat, getDouble, getBigDecimal,
getBoolean, getString, getObject
REAL: getFloat (recommended)
– Can also be read using getByte, getShort,
getInt, getLong, getDouble, getBigDecimal,
getBoolean, getString, getObject
FLOAT: getDouble (recommended)
– Can also be read using getByte, getShort,
getInt, getLong, getFloat, getBigDecimal,
getBoolean, getString, getObject
DOUBLE: getDouble (recommended)
– Can also be read using getByte, getShort, getInt,
getLong, getFloat, getBigDecimal, getBoolean,
getString, getObject
DECIMAL: getBigDecimal (recommended)
– Can also be read using getByte, getShort, getInt,
getLong, getFloat, getDouble, getBoolean, getString,
getObject
NUMERIC: getBigDecimal (recommended)
– Can also be read using getByte, getShort, getInt,
getLong, getFloat, getDouble, getBoolean, getString,
getObject
BIT: getBoolean (recommended)
– Can also be read using getByte, getShort,
getInt, getLong, getFloat, getDouble,
getBigDecimal, getString, getObject
CHAR: getString (recommended)
– Can also be read using getByte, getShort,
getInt, getLong, getFloat, getDouble,
getBigDecimal, getBoolean, getDate, getTime,
getTimestamp, getAsciiStream,
getUnicodeStream, getObject
VARCHAR: getString (recommended)
– Can also be read using getByte, getShort, getInt,
getLong, getFloat, getDouble, getBigDecimal,
getBoolean, getDate, getTime, getTimestamp,
getAsciiStream, getUnicodeStream, getObject
LONGVARCHAR: getAsciiStream,
getUnicodeStream (both recommended)
– Can also be read using getByte, getShort, getInt,
getLong, getFloat, getDouble, getBigDecimal,
getBoolean, getString, getDate, getTime,
getTimestamp, getObject
BINARY: getBytes (recommended)
– Can also be read using getString, getAsciiStream,
getUnicodeStream, getBinaryStream, getObject
VARBINARY: getBytes (recommended)
– Can also be read using getString,
getAsciiStream, getUnicodeStream,
getBinaryStream, getObject
LONGVARBINARY: getBinaryStream
(recommended)
– Can also be read using getString, getBytes,
getAsciiStream, getUnicodeStream, getObject
DATE: getDate (recommended)
– Can also be read using getString, getTimestamp,
getObject
TIME: getTime (recommended)
– Can also be read using getString, getTimestamp,
getObject
TIMESTAMP: getTimestamp (recommended)
– Can also be read using getString, getDate, getTime,
getObject
Version 2: Which types each
ResultSet.getXXX method can read
getByte: TINYINT (recommended)
– Can also read SMALLINT, INTEGER,
BIGINT, REAL, FLOAT, DOUBLE,
DECIMAL, NUMERIC, BIT, CHAR,
VARCHAR, LONGVARCHAR
getShort: SMALLINT (recommended)
– Can also read TINYINT, INTEGER, BIGINT,
REAL, FLOAT, DOUBLE, DECIMAL,
NUMERIC, BIT, CHAR, VARCHAR,
LONGVARCHAR
getInt: INTEGER (recommended)
– Can also read TINYINT, SMALLINT, BIGINT,
REAL, FLOAT, DOUBLE, DECIMAL,
NUMERIC, BIT, CHAR, VARCHAR,
LONGVARCHAR
getLong: BIGINT (recommended)
– Can also read TINYINT, SMALLINT,
INTEGER, REAL, FLOAT, DOUBLE,
DECIMAL, NUMERIC, BIT, CHAR,
VARCHAR, LONGVARCHAR
getFloat: REAL (recommended)
– Can also read TINYINT, SMALLINT,
INTEGER, BIGINT, FLOAT, DOUBLE,
DECIMAL, NUMERIC, BIT, CHAR,
VARCHAR, LONGVARCHAR
getDouble: FLOAT, DOUBLE (both
recommended)
– Can also read TINYINT, SMALLINT,
INTEGER, BIGINT, REAL, DECIMAL,
NUMERIC, BIT, CHAR, VARCHAR,
LONGVARCHAR
getBigDecimal: DECIMAL, NUMERIC (both
recommended)
– Can also read TINYINT, SMALLINT, INTEGER,
BIGINT, REAL, FLOAT, DOUBLE, BIT, CHAR,
VARCHAR, LONGVARCHAR
getBoolean: BIT (recommended)
– Can also read TINYINT, SMALLINT, INTEGER,
BIGINT, REAL, FLOAT, DOUBLE, DECIMAL,
NUMERIC, CHAR, VARCHAR, LONGVARCHAR
getString: CHAR, VARCHAR (both
recommended)
– Can also read TINYINT, SMALLINT, INTEGER,
BIGINT, REAL, FLOAT, DOUBLE, DECIMAL,
NUMERIC, BIT, LONGVARCHAR, BINARY,
VARBINARY, LONGVARBINARY, DATE, TIME,
TIMESTAMP
getBytes: BINARY, VARBINARY (both
recommended)
– Can also read LONGVARBINARY
getDate: DATE (recommended)
– Can also read CHAR, VARCHAR, LONGVARCHAR,
TIMESTAMP
getTime: TIME (recommended)
– Can also read CHAR, VARCHAR, LONGVARCHAR,
TIMESTAMP
getTimestamp: TIMESTAMP (recommended)
– Can also read CHAR, VARCHAR, LONGVARCHAR,
DATE, TIME
getAsciiStream: LONGVARCHAR
(recommended)
– Can also read CHAR, VARCHAR, BINARY,
VARBINARY, LONGVARBINARY
getUnicodeStream: LONGVARCHAR
(recommended)
– Can also read CHAR, VARCHAR, BINARY,
VARBINARY, LONGVARBINARY
getBinaryStream: LONGVARBINARY
(recommended)
– Can also read BINARY, VARBINARY
getObject: (no recommended type)
– Can read TINYINT, SMALLINT, INTEGER,
BIGINT, REAL, FLOAT, DOUBLE,
DECIMAL, NUMERIC, BIT, CHAR,
VARCHAR, LONGVARCHAR, BINARY,
VARBINARY, LONGVARBINARY, DATE,
TIME, TIMESTAMP
Prepared Statements
A statement that is executed many times can be
optimized by instead using a PreparedStatement
PreparedStatement objects are sent to the dbms for
precompilation.
Example
PreparedStatement updateSales =
con.prepareStatement( "UPDATE COFFEES SET
SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate():
Transactions
Transactions are sequences of commands
that are only executed if all commands in
sequence successfully complete.
If the commands complete successfully,
they are commited.
If any command fails, the commands are
rolled back.
Fundamental to databases/SQL. How to do
with JDBC?
Transactions with JDBC
By default, each command is independently
execute and commit.
To change this, execute the following
command on a connection object con:
con.setAutoCommit(false);
st.executeUpdate(command1);
st.executeUpdate(command2);
con.commit()/con.rollback();
Stored Procedures
To create, use executeUpdate with dbms-specific
String to define stored procedure. Example:
String createProcedure = "create procedure SHOW_SUPPLIERS "
+ "as " + "select SUPPLIERS.SUP_NAME,
COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by
SUP_NAME";
Statement stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
Calling stored procedured
Stored procedures can be called using
CallableStatement object. Example:
CallableStatement cs = con.prepareCall("{call
SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
Notice the {} around the prepared statement call. This
is required for java to translate into appropriate dbmsspecific sql.
JDBC2.0 features
JDBC2.0 features are automatically available in
any version of j2sdk >= 2.0
List of JDBC2.0 features
– Scroll forward and backward in a result set or move to a
specific row
– Make updates to database tables using methods in the
Java programming language instead of using SQL
commands
– Send multiple SQL statements to the database as a unit,
or batch
– Use the new SQL3 datatypes as column values
Scrollable result sets
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
Notice two new arguments. Possible values for first are as follows:
arg1: TYPE_SCROLL_SENSITIVE: backward scrollable
TYPE_SCROLL_INSENSITIVE: backward scrollable
TYPE_FORWARD_ONLY (default): not backward scrollable
arg2: CONCUR_READ_ONLY (default): cannot update directly
CONCUR_UPDATABLE: can be used to udpate directly
Focus on arg1 first: for first two types, new set of methods defined
to navigate more easily through results.
Methods for scrollable ResultSets
Many new methods to navigate:
– afterLast(); //position pointer after last row
– previous(); //opposite of next()
– absolute(i);//go to the i’th row
– absolute(-i);//go to the i’th row from end
– relative(i);//go i rows away from this point
– getRow();/returns current row
– isFirst(), isLast(), isBeforeFirst(), is
AfterLast();/ boolean pos query functions
Updatable ResultSets
Can use Java methods to update table rather
than sql strings.
Must create statements with
CONCUR_UPDATABLE flag:
Statement stmt =
con.createStatement(ResultSet.TYPE_SCRO
LL_SENSITIVE,ResultSet.CONCUR_UPDAT
ABLE);
Updating a value
To update a value in a Table, use the updateXXX
procedure. Example:
stmt.executeUpdate("UPDATE COFFEES SET PRICE = 10.99" +
"WHERE COF_NAME = FRENCH_ROAST_DECAF");
uprs.last(); //move to last row
uprs.updateFloat("PRICE", 10.99); //change PRICE column to new
value
Note that updateXXX works implictly on current row
Note that updateXXX can operate on col numbers as
well as col names
Committing an updated value
After previous call to updateXXX, database
is still not updated, only ResultSet is.
Must call updateRow() to complete
operation with dbms.
call cancelRowUpdates() to cancel an
update that you no longer wish to commit
Note that all row references refer to
ResultSet object, which may differ from
database table modulo updates!
Inserting rows
Also can insert values directly in Java.
Must move cursor to special row called insert row
and then call insert method.
uprs.moveToInsertRow();
uprs.updateString("COF_NAME", "Kona");
uprs.updateInt("SUP_ID", 150);
uprs.updateFloat("PRICE", 10.99);
uprs.updateInt("SALES", 0);
uprs.updateInt("TOTAL", 0);
uprs.insertRow(); updates both dbase and ResultSet
at same time
Deleting rows
Simply call deleteRow() on current row
Seeing Changed ResultSets
Always visible to others once you close and
reopen (or new query executed).
May or may not be visible to others while
ResultSet still open. Depends on:
– dbms
– driver
– flags used in creating ResultSet
• TypeScrollSensitive see all updates, might or
might not see inserts/deletes
• Need to use metadata methods to be sure
MetaData methods
Provide user with rich array of info about
database itself, tables, etc.
java.sql.DatabaseMetaData
– info about database as a whole. version,
features supported, vendor, etc.
java.sql.ResultSetMetaData
– data about specific ResultSet – names of
columns, size of types, types of data, etc.
Study API for more details
Java/SQL datatype mapping
SQL data type
INTEGER
SMALLINT
NUMERIC(m,n)
FLOAT(n)
REAL
DOUBLE
CHARACTER(n)
VARCHAR(n)
Java data type
int
short
java.sql.Numeric
double
float
double
String
String
Java/SQL datatype mapping
BOOLEAN
DATE
TIME
TIMESTAMP
BLOB
CLOB
boolean
java.sql.Date
java.sql.Time
java.sql.Timestamp
java.sql.Blob
java.sql.Clob
ARRAY
java.sql.Array
Other methods of interest
java.sql.Statement
– void cancel();
Aysnchronously cancels an executing SQL
request.
java.sql.ResultSet
– int findColumn(String columName);
gives the column index for column columName
- void close();
closes the current result set.
SQLException methods
java.sql.SQLException
– String getSQLState();
– int getErrorCode()
gets the vendor-specific exception code
– SQLException getNextException();
gets the Exception chained to this one for more
specific information
Introduction to awt Graphics
Reading, displaying images
Awt Image processing
Java has recently added many classes for
simplifying image manipulation.
We’ll start by looking at some of these in the
context of howto’s for simple things
– reading a jpg, gif, etc. from a file
– displaying jpg, gif, etc. to a graphics window
– constructing an image from raw pixels
– manipulating individual pixesl of an image
– writing an image to a file
(see course examples)
Reading an image
Easiest way to read an image file. Use static
read method in javax.image.ImageIO class:
BufferedImage img = ImageIO.read(new File(“name”));
Note that “name” can be name of one of
many standard Image file formats.
Writing an image
Writing an image is as easy as reading it. Simple
use the ImageIO.write method:
BufferedImage image;
ImageIO.write(new File(name), “gif”,image);
List of supported output file types is can be obtain
from:
– String[] ImageIO.getWriterFormatNames();
Manipulating image bytes
It is possible to set/access each image pixel
independently:
image = new
BufferedImage(w,h,BufferedImage.TYPE_INT_ARGB);
WritableRaster raster = image.getRaster();
raster.setPixel(ival,jval,{rval,gval,bval,alphval});
or
int pixel[4];
raster.getPixel(ival,jval,pixel);
Transforming images
It is also possible to transform images
without accessing pixels using classes that
implement the ImageOp interface.
See ImageProcessor.java example