JDBC

1. JDBC IST 411 / INFSY 436 Spring 2006

2. JDBC • Steps to be programmed to access a relational database using Java • Load a driver which is compatible with the database being used. • Establish a connection to the database • Associate an SQL statement with this connection • Execute the SQL statement • SQL statement will produce a table stored in a ResultSet object.

3. JDBC • When processing is complete, database is closed • Connection to the database is closed

4. API Summary • The most important methods are: • Class.forName (String DriverName) • DriverManager • getConnection (String Driver, String DatabaseURL) • getConnection (String Driver, String DatabaseURL, String Username, String Password) • Connection • createStatement( )

5. API Summary • Statement • executeQuery (String SQLQuery) • ResultSet • getXXX (String SQLQueryColumnName) • getXXX (int SQLQueryColumnNumber) • next( )

6. Update a Database • Insert data into a database via INSERT INTO employee(name, salary, department) VALUES (‘Sam’, 20000, ‘Payroll’); • executeUpdate • returns an int rather than a result set • int is the number of rows affected by insertion • remember to specify strings within single quotes

7. Update a Database • Commit statement • Rollback statement

8. Prepared Statements • SQL statement is executed • Syntax analysis • Determine processing required • Statement optimized • Repeated each time statement is executed • Prepared Statements • Processed once

9. Prepared Statements PreparedStatement ps = bdConnection.prepareStatement (“SELECT employeeID, salary FROM EMPLOYEES WHERE salary > ?”);

10. Prepared Statements • Part of query is sent to the database • Tables, column names • Parts of where clause can be omitted • DBMS will cache information about query allowing subsequent queries to run faster • ? is placeholder • setXXX( ) methods • ps.setInt(1, salValue); • 1 = argument position within SQL statement • salValue = value of the argument such as 23500

11. Metadata • Metadata • Data which describes other data • Extract metadata from database • java.sql.DatabaseMetaData package • Find name of a driver • Check whether ANSI92 SQL is supported

12. Metadata • Check ResultSet metadata • Number of columns in result set • Database name for a column • Display name of a column • Precision of numbers in a column • etc.

13. Metadata ResultSet rs; ResultSetMetaData rsmd = rs.getMetaData( ); System.out.println(“The result set contains “ + rsmd.getColumnCount( ) + “ columns”);