Lesson 3

  JDBC 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 – High degree of control over performance – Database is overkill – Data is not that important

Advantages of 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.

 Much harder to corrupt data, 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-to manipulate 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).

 API lacks robustness in certain areas

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 terminator auto matically added  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)");

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.

 My have only one open ResultSet object per Statement! That is, if you want to open two ResultSets at once (not common), you need to create a new Statement object.

Closing statements, etc.

 When you are finished with a ResultSet, Statement, or Connection, you should call

close

() immediately.

 The

close()

statement of a Statement automatically closes related Connection and ResultSet objects, and likewise for a Connection object and its associated ResultSet.

Sample of ResultSet Conversions

 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

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 pre-compilation.

 Example PreparedStatement updateSales = con.prepareStatement

updateSales.setInt( 1, 75); updateSales.setString

(2, "Colombian"); updateSales.executeUpdate

(): ( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

Prepared statements, cont.

 Can dramatically improve performance  Can call the

set

method as many times as you like for same

PreparedStatement

object  Makes it easier also to script multiple updates in a loop  Good idea in general!

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

Transactions – marker points

 Previous technique rolls back all command since previous

commit

 You can explicitly add

save points

code as an alternative, as: to your – Statement stmt = conn.createStatement() – stmt.executeUpdate(command1) – Savepoint svpt = conn.setSavepoint(); – Stmt.executeUpdate(command2); – if (…) conn.rollback(svpt);

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 and sensitive to DB changes TYPE_SCROLL_INSENSITIVE: backward scrollable and NOT sensitive to DB changes TYPE_FORWARD_ONLY (default): not backward scrollable arg2: CONCUR_READ_ONLY (default): cannot update directly CONCUR_UPDATABLE: can be used to update 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_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

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 ARRAY boolean java.sql.Date

java.sql.Time

java.sql.Timestamp

java.sql.Blob

java.sql.Clob

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

Also see

 RowSet (pp 230 of Horstman)

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 procedures

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

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