1 / 16

JDBC Review

JDBC Review. Celsina Bignoli bignolic@smccd.net. What is JDBC. Industry standard for database-connectivity between the Java language and a wide range of databases Generic Leverage existing database APIs Simple. Driver Implementation Alternatives. Java Application. JDBC API.

adie
Download Presentation

JDBC Review

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 Review Celsina Bignoli bignolic@smccd.net

  2. What is JDBC • Industry standard for database-connectivity between the Java language and a wide range of databases • Generic • Leverage existing database APIs • Simple

  3. Driver Implementation Alternatives Java Application JDBC API JDBC Driver Manager JDBC Driver API JDBC-ODBC Bridge Driver (Type1) Native-API partly Java Driver (Type2) JDBC-Net Driver (Type3) Native-protocol pure Java Driver (Type4) JDBC Middleware Protocol Proprietary database access protocols

  4. DataSource Connections • A factory for connections to the physical data repository that this DataSource object represents. • Creates and manages a pool of connections • An object that implements the DataSource interface will typically be registered with a naming service based on the JavaTM Naming and Directory (JNDI) API.

  5. What to do? • Acquire a Connection through the DataSource • Run the SQL Statement and get back the results in a ResultSet object • Process the ResultSet object • Release the Connection

  6. getConnection() – Example Context ctx = new InitialContext(); javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/bmp-account"); Connection conn = ds.getConnection();

  7. Queries – Statement Object • used to send a SQL statement to the database • executes the SQL statement • returns back the results of the SQL statement Statement stmt = conn.createStatement();

  8. Executing a Statement - Example ResultSet rs = stmt.executeQuery(“select name from pet”); while (rs.next()) { System.out.println(rs.getString((1))); } ResultSet: Initial cursor position next() next()

  9. Basic Getter Methods • int getInt(int columnIndex) • int getInt(String columnName) • String getString(int columnIndex) • String getString(String columnName) • Date getDate(int columnIndex) • Date getDate(String columnName)

  10. Returns either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing

  11. executeUpdate() method Insert int i= stmt.executeUpdate(“INSERT INTO pet VALUES(12, ’minou’, ’Gwen’, ’cat’)”); Update Statement stmt = connection.createStatement(); int i= stmt.executeUpdate(“UPDATE pet SET owner=‘Lucy’ where owner= ‘Gwen’ ”); Delete Statement stmt = connection.createStatement(); int i= stmt.executeUpdate(“DELETE FROM pet WHERE owner= ‘Gwen’ ”);

  12. Prepared Statements - SQL • ability to set up a statement once, and then execute it many times with different parameters. • replace building ad hoc query strings, and do so in a more efficient manner. • First implemented in the C API • Available in Connector/J server-side starting from version 3.1

  13. How databases execute queries • parse the query • invoke the optimizer to determine best query execution plan • caches the plan – query is the key to fetch plan from cache

  14. Prepared Statement - Example PREPARE sel_stmt FROM “SELECT name FROM pet WHERE id=?”; SET @pet_id=1; EXECUTE sel_stmt USING @pet_id

  15. JDBC – PreparedStatement PreparedStatement ps = con.prepareStatement( “select name from pet where id =?“ ); for int(i=1; i<=10; i++){ ps.setInt(1, i); -- variable binding ResultSet rs = ps.executeQuery(); while (rs.next()){ System.out.println(rs.getString(1)); } rs.close(); } • ? is called placeholder • query is parsed only once and only 1 execution plan is created and caches for it • executed many times after binding variables • MUCH MORE EFFICIENT!

  16. Placeholders- Setter methods

More Related