Lesson 5
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

Lesson 5 PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

Lesson 5. JNI, cont 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.

Download Presentation

Lesson 5

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Lesson 5

Lesson 5

JNI, cont


Intro to Graphics – Image Processing

Lesson 5


Using Java to issue SQL commands

Basic database concepts

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?



  • 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

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, compoundkey, artificial key, foreign key.

Object oriented databases

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.

Lesson 5


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

Lesson 5


  • Lets you define and revise the structure of relational databases. Examples:

    Create Database name


    Create Table name

    ( columname datatype, … )

  • Only simple datatypes supported.

Lesson 5


  • Lets user specify data security and integrity mechanisms that safeguard data

  • Not very standardized – varies from vendor to vendor.

Lesson 5


  • Functionality for retrieving, manipulating, deleting, sorting, searching data.

    Examples just to get flavor:

    • Select * From table;

    • Select columns From tables [Where condition];

    • Select ItemNo, QtyFromInvoiceLine;

    • Insert Into InvoiceLine;

      (InvoiceNo, LineNo, CustomerNo)

      Values (101, 100, 10);

How to use sql

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

Lesson 5


  • 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

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

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

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

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

Loading the driver

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)


    • note: you’ll need a copy of


      in your classpath.

Define the connection

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

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

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

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

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

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

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

Transactions with JDBC

  • By default, each command is independently executed and commited.

  • To change this, execute the following command on a connection object con:





Java sql datatype mapping

Java/SQL datatype mapping

Java sql datatype mapping1

Java/SQL datatype mapping

Other methods of interest

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

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

Introduction to awt Graphics

Reading, displaying images

Awt image processing

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

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

  • 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

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



    int pixel[4];


Transforming images

Transforming images

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

  • See ImageProcessor.java example

  • Login