JNI, cont

1
Lesson 5

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

2
JDBC

• Using Java to issue SQL commands

3
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
• Other?

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

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

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

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

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

9
DCL

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

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

11
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

12
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

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

14
Disadvantages of JDBC

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

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

16
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

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

18
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
  

19
Establish the Connection

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

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

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

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

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

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

25
Transactions

• Transactions are sequences of commands that are
  only executed if all commands in sequence
  successfully complete.
• If the commands complete successfully, the are
  commited.
• If any command fails, the commands are rolled
  back.
• Fundamental to databases/SQL. How to do with JDBC?

26
Transactions with JDBC

• By default, each command is independently
  executed and commited.
• 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()

27
Java/SQL datatype mapping
28
Java/SQL datatype mapping
29
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.

30
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

31
Introduction to awt Graphics

• Reading, displaying images

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

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

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

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

36
Transforming images

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