Monday, 4 March 2013

What does Class.forName() method do?

Method forName() is a static method of java.lang.Class.
This can be used to dynamically load a class at run-time.
Class.forName() loads the class if its not already loaded.
It also executes the static block of loaded class.
Then this method returns an instance of the loaded class.
So a call to Class.forName('MyClass') is going to do following

-Load the class MyClass.

-Execute any static block code of MyClass.

-Return an instance of MyClass.

JDBC Driver loading using Class.forName is a good example of best use of this method.

The driver loading is done like this

Class.forName("org.mysql.Driver");

All JDBC Drivers have a static block that registers itself with DriverManager and DriverManager has static initializer method registerDriver() which can be called in a static blocks of Driver class.
A MySQL JDBC Driver has a static initializer which looks like this:

static {

try {

java.sql.DriverManager.registerDriver(new Driver());

} catch (SQLException E) {

throw new RuntimeException("Can't register driver!");

}

Class.forName() loads driver class and executes the static block and the Driver registers itself with the DriverManager.

Sunday, 3 March 2013

What are the main steps in java to make JDBC connectivity?

Load the Driver:

· First step is to load the database specific driver which communicates with database.

Syntax: Class.forName(…);

Make Connection:

· Next step is get connection from the database using connection object, which is used to send SQL statement also and get result back from thedatabase.

Syntax: Connection con=DriverManager.getConnection(…);

Get Statement object:

· From connection object we can get statement object which is used to query the database

Syntax: Statement stmt=con.createStatement();

Execute the Query:

· Using statement object we execute the SQL or database query and get result set from the query.

Syntax: stmt.executeQuery(…);

Close the connection:

· After getting resultset and all required operation performed the last step should be closing the database connection.

Syntax: con.close();

What is Connection pooling? What are the advantages of using a connection pool?

A connection pool operates by performing the work of creating connections ahead of time.
In the case of a JDBC connection pool, a pool of Connection objects is created at the time the application server starts.
These objects are then managed by a pool manager that disperses connections as they are requested by clients and returns them to the pool when it determines the client is finished with the Connection object.
When the connection pool server starts, it creates a predetermined number of Connection objects.
A client application would then perform a JNDI lookup to retrieve a reference to a DataSource object that implements the ConnectionPoolDataSource interface.
When the client application requests a connection from the ConnetionPoolDataSource, the data source implementation would retrieve a physical connection to the client application.
The ConnectionPoolDataSource would return a Connection object that implemented the PooledConnection interface.
The PooledConnection interface dictates the use of event listeners.
These event listeners allow the connection pool manager to capture important connection events, such as attempts by the client application to close the connection.
When the driver traps a close-connection event, it intercedes and performs a pseudo-close operation that merely takes the Connection object, returns it to the pool of available connection.
The operation of the connection pool should be completely transparent to the client application.
The triggering of connection events, the manipulation of the object pool, and the creation and destruction of physical connections are all managed by the pool manager.

Context.xml

//This should be added in the servers context,xml file. For example if you are using apache server then the context.xml will be found in C:\apache-tomcat-6.0.26\conf\Context.xml

web.xml

<resource-ref>

<description>DB Connection</description>

<res-ref-name>jdbc/TestDB</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>

//This should be added in the web.xml of the local project. (Not in server's web.xml).

For implementation example please refer to:

Download DB values as CSV file with JNDI Connection Pool Method

What are the disadvantages of JDBC?

The process of creating a connection, always an expensive, time-consuming operation, is multiplied in these environments where a large number of users are accessing the database in short, unconnected operations.
Creating connections over and over in these environments is simply too expensive.
When multiple connections are created and closed it affects the performance.

How to call stored procedure using JDBC API?

Stored procedures can be called using CallableStatement class in JDBC API. Below code snippet shows how this can be achieved.

Code Snippet:

CallableStatement cs = con.prepareCall("{call MY_STORED_PROC_NAME}");

ResultSet rs = cs.executeQuery();

How to create a stored procedure?

Creating a Stored Procedure:

CREATE PROCEDURE uspGetAddress // Either PROCEDURE of PROC can be used

SELECT * FROM AdventureWorks.Person.Address

Call in SQL:

EXEC uspGetAddress

--or just simply

uspGetAddress

Creating a Stored Procedure with parameters:

CREATE PROCEDURE uspGetAddress @City nvarchar(30)

SELECT *

FROM AdventureWorks.Person.Address

WHERE City = @City