JDBC Review

1
JDBC Review
Celsina Bignoli bignolic_at_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-Net Driver (Type3)
JDBC-ODBC Bridge Driver (Type1)
Native-API partly Java Driver (Type2)
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)ct
  x.lookup("javacomp/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
  ownerLucy 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 _at_pet_id1
• EXECUTE sel_stmt USING _at_pet_id

15
JDBC PreparedStatement

• PreparedStatement ps con.prepareStatement(
• select name from pet
  where id ? )
• for int(i1 ilt10 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