Transcript Lesson5 - People.cs.uchicago.edu

Lesson 5
1. JNI, cont
2. JDBC
3. Intro to Graphics – Image
Processing
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
– Other?
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.
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?
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();
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