1 / 30

JDBC

JDBC. by Jon Pearce. DBase Concepts. Terms. Table Row/Entity Column/Field/Attribute Key/Primary Key/Foreign Key. Person OID FIRST LAST ADDRESS PHONE. Address OID STREET CITY STATE. Phone OID AREA NUMBER. Entity-Relation Diagram. 1. 1. *. *. Person Table. Address Table.

lamar
Download Presentation

JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related