Transcript Document
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