Introduction to SQL: Basic Concepts and JDBC Implementation

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, compoundkey, 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, QtyFromInvoiceLine; • 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 • 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

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

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: you’ll 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 = “jdbc:mysql://dbserver/mydatabase”;

19. Establish the Connection • Issue the following command to create a single connection to the database java.sql.Connection conn = DriverManager.getConnection(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 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.

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

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_INT_ARGB); WritableRaster raster = image.getRaster(); raster.setPixel(ival,jval,{rval,gval,bval,alphval}); or int pixel[4]; 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