JNI, cont

1
Lesson 5

• JNI, cont
• JDBC
• Intro to Graphics Image Processing

2
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

3
JNI, cont.
4
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!

5
Smoothing test

• Consider algorithm to filter 1d data (see course
  examples).
• Well compare the following
• pure java version
• native version
• Well do this on several different platforms and
  look at relative timings.

6
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!!

7
Releasing Arrays

• To determine if it is a copy, the flag parameter
  is used (see example).
• This is obviously crucial for performance some
  jvms implement this way, others dont.
• Well see both examples today.
• Finally, when finished use
• releaseXxxArrayElements(Xxx, jXxxArray, flag)

8
JDBC

• Using Java to issue SQL commands

9
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

10
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.

11
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.

12
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.

13
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)

14
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.

15
DCL

• Lets user specify data security and integrity
  mechanisms that safeguard data
• Not very standardized varies from vendor to
  vendor.

16
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)

17
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

18
JDBC

• Javas version of Embedded SQL
• Interface fully specified in the standard Java
  language (ie J2SE).
• Independent of database vendors specific SQL
  implementation.
• Vendor supplies middleware driver to convert JDBC
  calls to native db hooks.
• Similar to Microsofts ODBC

19
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-to-
  manipulate java objects, with simple methods,
  java exceptions, etc.

20
Disadvantages of JDBC

• Slower
• Cannot take advantage of all SQL extensions of a
  particular vendor (though it can take advantage
  of many).

21
Using JDBC on cluster

• To use JDBC on the cs cluster, youll need to
  either install a database or use one of our dbase
  servers (mysql or sybase).
• In this example Ill 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.

22
Using JDBC

• Basic steps for connecting to dbase server
• Load JDBC driver
• Define the connection object
• Establish the connection
• Create the statement object
• Execute a query or update with statement object
• Process the returned ResultSet
• Close the Connection

23
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 youll need a copy of
• mysql-connector-java-3.0.7-stable-bin.jar
• in your classpath.

24
Define the Connection

• Each vendor supplies info on what connection URL
  to use.
• For mysql installed on cluster the following
  works
• String conURL jdbcmysql//dbserver/mydatabase
  

25
Establish the Connection

• Issue the following command to create a single
  connection to the database
• java.sql.Connection conn DriverManager.getConnec
  tion(URL)

26
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()

27
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)

28
Process the Results

• The ResultSet object is javas 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.

29
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)

30
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.

31
General observations

• In executeXXX calls, SQL string need not end with
  any specific terminator
• st.execute(Select from TABLE)
• Statement objects can be reused for many queries
  no need to create new one each time
• 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)")

dbms-specific terminator auto- matically added
32
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.

33

• 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

34

• 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

35

• 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

36

• 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

37

• 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

38

• 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

39

• 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

40

• 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

41
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

42

• 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

43

• 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

44

• getDouble FLOAT, DOUBLE (both recommended)
• Can also read TINYINT, SMALLINT, INTEGER, BIGINT,
  REAL, DECIMAL, NUMERIC, BIT, CHAR, VARCHAR,
  LONGVARCHAR

45

• 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

46

• 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

47

• 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

48

• getAsciiStream LONGVARCHAR (recommended)
• Can also read CHAR, VARCHAR, BINARY, VARBINARY,
  LONGVARBINARY
• getUnicodeStream LONGVARCHAR (recommended)
• Can also read CHAR, VARCHAR, BINARY, VARBINARY,
  LONGVARBINARY

49

• 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

50
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()

51
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?

52
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()

53
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)

54
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 dbms-specific sql.

55
JDBC2.0 features

• JDBC2.0 features are automatically available in
  any version of j2sdk gt 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

56
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.
57
Methods for scrollable ResultSets

• Many new methods to navigate
• afterLast() //position pointer after last row
• previous() //opposite of next()
• absolute(i)//go to the ith row
• absolute(-i)//go to the ith 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

58
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.TYP
  E_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE)

59
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

60
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!

61
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
62
Deleting rows

• Simply call deleteRow() on current row

63
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

64
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

65
Java/SQL datatype mapping
66
Java/SQL datatype mapping
67
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.

68
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

69
Introduction to awt Graphics

• Reading, displaying images

70
Awt Image processing

• Java has recently added many classes for
  simplifying image manipulation.
• Well start by looking at some of these in the
  context of howtos 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)

71
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.

72
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()

73
Manipulating image bytes

• It is possible to set/access each image pixel
  independently
• image new BufferedImage(w,h,BufferedImage.TYPE_I
  NT_ARGB)
• WritableRaster raster image.getRaster()
• raster.setPixel(ival,jval,rval,gval,bval,alphval
  )
• or
• int pixel4
• raster.getPixel(ival,jval,pixel)

74
Transforming images

• It is also possible to transform images without
  accessing pixels using classes that implement the
  ImageOp interface.
• See ImageProcessor.java example