JDBC basic knowledge

1.     What is JDBC? How do you connect to a database?

JDBC stands for Java Database Connectivity. It is an API which provides easy connection to a wide range of databases. To connect to a database we need to load the appropriate driver and then request for a connection object. The Class.forName(….) will load the driver and register it with the DriverManager

Class.forName(“oracle.jdbc.driver.OracleDriver”); //dynamic class loading String url = jdbc:oracle:thin:@hostname:1526:myDB; Connection myConnection = DriverManager.getConnection(url, “username”, “password”);

 

The DataSource interface provides an alternative to the DriverManager for making a connection. DataSource makes the code more portable than DriverManager because it works with JNDI. This makes your application code easier to maintain. DataSource allows the use of connection pooling and support for distributed transactions. A DataSource is not only a database but also can be a file or a spreadsheet.

DataSource configuration properties are shown below:

JNDI Name:  jdbc/myDataSource URL:  jdbc:oracle:thin:@hostname:1526:myDB UserName, Password Implementation classname :  oracle.jdbc.pool.OracleConnectionPoolDataSource Classpath:  ora_jdbc.jar Connection pooling settings like: minimum pool size, maximum pool size, connection timeout, statement cache size

 

Once the DataSource has been set up, then you can get the connection object as follows:

Context ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("jdbc/myDataSource"); Connection myConnection = ds.getConnection(“username”,”password”);

 

2. Why should you prefer using DataSource?

Best practice: In a basic implementation a Connection obtained from a DataSource and a DriverManager are identical. But the J2EE best practice is to use DataSource because of its portability, better performance due to pooling of valuable resources and the J2EE standard requires that applications use the container’s resource management facilities to obtain connections to resources. Every major web application container provides pooled database connection management as part of its resource management framework.

Design Pattern: JDBC architecture decouples an abstraction from its implementation so that the implementation can vary independent of the abstraction. This is an example of the bridge design pattern. The JDBC API provides the abstraction and the JDBC drivers provide the implementation. New drivers can be plugged-in to the JDBC API without changing the client code.

 

3.What are JDBC Statements? What are different types of statements? How can you create them?

A statement object is responsible for sending the SQL statements to the Database. Statement objects are created from the connection object and then executed.

Statement stmt = myConnection.createStatement();

// to read

ResultSet rs = stmt.executeQuery(“SELECT id, name FROM myTable where id =1245”); or

//to insert/update/delete/create

stmt.executeUpdate(“INSERT INTO (field1,field2) values (1,3)”);

 

The types of statements are:

s           Statement (regular statement as shown above)

s           PreparedStatement (more efficient than statement due to pre-compilation of SQL)

s           CallableStatement (to call stored procedures on the database)

 

To use prepared statement:

PreparedStatement prepStmt =

myConnection.prepareStatement("SELECT id, name FROM myTable where id = ? ");

prepStmt.setInt(1, 1245);

 

Callable statements are used for calling stored procedures.

CallableStatement calStmt = myConnection.prepareCall("{call PROC_SHOWMYBOOKS}");

ResultSet rs = cs.executeQuery();