JDBC Overview

1. JDBC Overview Autumn 2001 Lecturer: C. DeJong

2. Relational Databases • widespread use • used via SQL (Structured Query Language) • freely available • powerful • text-based

3. What is JDBC? • An API for Java database connectivity • A collection of Java classes from Sun • A set of interfaces for database programming • java.sql.*

4. Steps for setting up JDBC • install Java and JDBC (available from java.sun.com) • install a driver • install a relational database • MySQL, PostgreSQL (free!) • Oracle, Sybase, IBM’s DB2 (commercial)

5. Establishing a connection • Load the database driver • Make the connection

6. Loading the driver • driver should be provided with database • one line of code: Class.forName(<driver class name>); • ex: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

7. Make the connection • Returns a Connection object • Syntax: Connection con = DriverManager.getConnection( url, "myLogin", "myPassword");

8. URL for connecting • should be in documentation with JDBC driver • starts with “jdbc:” • can connect across a network

9. Statements • used to send SQL to the database • syntax: Statement stmt = con.createStatement();

10. Using statements • creating a table stmt.executeUpdate( "CREATE TABLE COFFEES " + "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " + "SALES INTEGER, TOTAL INTEGER)");

11. Using statements • inserting data stmt.executeUpdate( "INSERT INTO COFFEES " + "VALUES ('Colombian', 101, 7.99, 0, 0)");

12. Sample query • in SQL: SELECT COF_NAME, PRICE FROM COFFEES

13. Query result COF_NAME PRICE ------------------ ----- Colombian 7.99 French_Roast 8.99 Espresso 9.99 Colombian_Decaf 8.99 French_Roast_Decaf 9.99

14. Retrieving data with ResultSet • Statements can run a query on a database • This returns an object. • This object is a ResultSet.

15. Running the query in Java Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT COF_NAME, PRICE FROM COFFEES");

16. Inspecting the ResultSet while (rs.next()) { String s = rs.getString("COF_NAME"); float n = rs.getFloat("PRICE"); System.out.println(s + " " + n); }

17. Output • The output will look something like this: Colombian 7.99 French_Roast 8.99 Espresso 9.99 Colombian_Decaf 8.99 French_Roast_Decaf 9.99

18. alternative syntax while (rs.next()) { String s = rs.getString(1); float n = rs.getFloat(2); System.out.println(s + " " + n); } • note: starts at 1, not 0!

19. some ResultSet retrieval methods • getString() • getInt() • getFloat() • getObject() • … many others

20. Closing • When finished, call close() on • ResultSet objects • Statement objects • Connection objects • … or you may have a memory leak!

21. Exceptions • Most methods on classes in the java.sql package throw java.sql.SQLException • SQLException.getMessage() shows database error • SQLException is a checked exception • so: try/catch/finally or throws