Download
1 / 16
160 likes | 275 Views
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.
E N D
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 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
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.
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
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();
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();
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()
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)
Returns either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing
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’ ”);
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
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
Prepared Statement - Example PREPARE sel_stmt FROM “SELECT name FROM pet WHERE id=?”; SET @pet_id=1; EXECUTE sel_stmt USING @pet_id
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!
