JDBC

1. JDBC by Jon Pearce

2. DBase Concepts

3. Terms • Table • Row/Entity • Column/Field/Attribute • Key/Primary Key/Foreign Key

4. Person OID FIRST LAST ADDRESS PHONE Address OID STREET CITY STATE Phone OID AREA NUMBER Entity-Relation Diagram 1 1 * *

5. Person Table

6. Address Table

7. Phone Table

8. SQL • SELECT-FROM-WHERE • INSERT INTO • UPDATE • DELETE-FROM

9. Select SELECT field1, field2, field3 ... FROM table1, table2, table3 ... WHERE condition

10. Projection SELECT first, last FROM Person

11. Selection SELECT * FROM Person WHERE last LIKE 'D%'

12. Join SELECT * FROM Person, Address WHERE Person.Address = Address.OID

13. Insert INSERT INTO table (field, field, ...) VALUES (value, value, ...)

14. Update UPDATE table SET field = value, ... WHERE condition

15. Delete DELETE FROM table WHERE condition

16. Cloudview

17. java.sql

18. JDBC Driver Types • JDBC-to-ODBC Bridge • Native API partly Java drivers • JDBC-Net pure Java drivers • Native-protocol pure Java drivers

19. JDBC Driver Types II

20. Informix Cloudscape

21. An SQL Browser

22. SQL Browser Declaration public class SQLBrowser extends Console { protected String driverName; protected String dbaseName; protected Connection connection; protected Statement statement; protected ResultSet result; public SQLBrowser(String db) throws SQLException, ClassNotFoundException {...} public void finalize() throws SQLException {...} private String toString (ResultSet rs) throws SQLException {...} public String execute(String sql) throws AppError {...}}

23. Connecting public SQLBrowser(String db) throws SQLException, ClassNotFoundException { Class.forName(driverName); dbaseName = "jdbc:cloudscape:rmi:" + db; connection = DriverManager.getConnection(dbaseName); statement = connection.createStatement(); meta = connection.getMetaData();}

24. Executing a Query public String execute(String sql) throws AppError { String answer = "???"; try { result = statement.executeQuery(sql); answer = toString(result); } catch (SQLException e) { } return answer;}

25. Processing a Result Set private String toString (ResultSet rs) throws SQLException { StringBuffer results = new StringBuffer(); ResultSetMetaData metaData = rs.getMetaData(); int numCols = metaData.getColumnCount(); for(int i = 1; i <= numCols; i++) { // get column names results.append(metaData.getColumnName(i) + "\t"); } results.append("\n"); while(rs.next()) { // get next row for(int i = 1; i <= numCols; i++) { results.append(rs.getObject(i) + "\t"); } results.append("\n"); } return results.toString();}

26. Closing the Connection public void finalize() throws SQLException { statement.close(); connection.close();}

27. DBase DAO public class DBaseDAO { protected String driver; protected String url; // "protocol:subprotocol:dbase" protected Connection connection; public DBaseDAO(String d, String u) throws DAOException {...} protected void connect() throws Exception {...} public void close() throws DAOException {...} protected void finalize() {...}}

28. Connecting protected void connect() throws Exception { Class.forName( driver ); connection = DriverManager.getConnection( url ); connection.setAutoCommit( false );}

29. DAO Pattern

30. DBasePersonDAO