Lesson 3

1. Lesson 3 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 High degree of control over performance Database is overkill Data is not that important

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

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, compound key, 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, Qty From InvoiceLine; 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). API lacks robustness in certain areas

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. General observations In executeXXX calls, SQL string need not end with any specific terminator st.execute(�Select * from TABLE�); 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)");