All Java JDBC tips

1) There are three levels of JDBC API

2) The getConnection method is synchronized

3) Multiple SQL exceptions are chained

4) Type 1 are the JDBC-ODBC drivers

5) Type 2 are the Native API driver

6) Type 3 driver JDBC calls are first translated to DBMS independent net protocol and then translated to DBMS specific protocol

7) Type 4 drivers are pure java classes that directly interact with the DBMS and are quite fast.

8) DataSource interface was introduced in JAVA 2.0 and supports connection pooling, distributed tansactions

9) Datasource uses JNDI to find the datasource on the server and bind itself to it.

10) The context interface provides the facility to look up, bind and unbind the datasource

11) The JDBC url has three parts: jdbc::

12) The Statement object can be used to pass the SQL commands to the database (con.createStatement())

13) The prepared statement can be passed parameters and hence can be used with different values and is usually faster than statement. (con.prepareStatement)

14) The callable statement can be used to call stored procedures. (con.prepareCall)

15) The different versions for execute method are:
a. executeUpdate(String sql) – Statements which return a count
b. executeQuery(String sql) – Statements which return a single resultset
c. execute(String sql) – Statements which return multiple resultsets (Advanced Funda)

16) While using the statement object, the query is passed when it is executed

17) When using prepared statement, the query is passed when creating the statement object from connection object. This helps in pre-compilation

18) The callable statement also passes the call statement while creating the statement object from the connection object.

19) Callable statement extends prepared statement

20) Callable statement can also take output parameters too. But that parameter must be registered using registerOutParameter() method before calling the execute method. E.g callstmt.registerOutParameter(3, java.sql.Types.VARCHAR); if the third parameter is supposed to be the OUT parameter.

21) The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed. In advanced cases, a single statement may return multiple results as well as output parameter values. Here, the commit occurs when all results and output parameter -values have been retrieved.