Datasource and advantages

DataSource and the DriverManager are the two basic ways to connect to a database in a Java/J2EE applications. The DriverManager is older facility, DataSource is newer. It is recommended to use the new DataSource facility to connect to databases and other resources.

Datasource is a name given to the connection set up to a database from a server. The datasource name can be any name, would be good to have a application related name as it would be helpful in future to identify when there are several datasource setup done.The JNDI(Java Naming and  Directory Interface) name of the Datasource will be used when connecting and executing a query to the database in the application.

Typically, a Connection returned from a DataSource aggregates on a DriverManager Connection opened by the DataSource itself. Almost all the methods on the aggregating connection are passed along to the fronted Connection, with one exception. The close() method returns the Connection to the DataSource connection pool instead of actually closing the connection. That way the Connection will be ready to use when it is again retrieved from the pool. Rigorous implementations will also ensure that before the Connection is handed out again it's reset to a known state, but it's better not to assume that. 

This is where it's critical to intercept all JDBC errors and make sure you have explicitly closed all connections, including those where the exception would otherwise fly by the close done by non-error code. Otherwise you'll leak connections.

Because of its properties, a DataSource object is a better alternative than the DriverManager class for getting a connection. Programmers no longer have to hard code the driver name or JDBC URL in their applications, which makes them more portable and helps maintaining code much simpler. 

If there is a change, the system administrator can update data source properties and not be concerned about changing every application that makes a connection to the data source. For example, if the data source were moved to a different server, all the system administrator would have to do is set the serverName property to the new server name.

Aside from portability and ease of maintenance, using a DataSource object to get connections can offer other advantages. When the DataSource interface is implemented to work with a ConnectionPoolDataSource implementation, all of the connections produced by instances of that DataSource class will automatically be pooled connections. 

Similarly, when the DataSource implementation is implemented to work with an XADataSource class, all of the connections it produces will automatically be connections that can be used in a distributed transaction. 

Connection to MySQL DB using DriverManager :

static final String url="jdbc:mysql://localhost:3306/testdb";

static final String usr = "testuser";

static final String pwd = "testPassword";

static final String query="select * from employee";

try {   

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection(url, usr ,pwd);

Statement stmt = con.createStatement();

ResultSet res=stmt.executeQuery(query);

       /**

          * Do some operations

        **/

res.close();

stmt.close();

conn.close();

   }catch(SQLException se){ se.printStackTrace();}

finally{

   try{

         if(stmt!=null)  stmt.close();

         if(conn!=null)  conn.close();

      }catch(SQLException se){}}}

Connection to MySQL DB using Datasource :

static final String jndiName = "java/testjndi"; //Setup on the server.

static final String userName = "testuser";

static final String password = "testPassword";

static final String query = "select * from employee";

try

 {

Context ctx = new InitialContext();

DataSource ds = (DataSource)ctx.lookup("");

Connection con = ds.getConnection(userName, password);

Statement stmt = con.createStatement();

ResultSet res= stmt.executeQuery(query);

      /**

         * Do some operations

        **/

res.close();

stmt.close();

conn.close();

   }catch(SQLException se){ se.printStackTrace(); }

finally{

   try{

         if(stmt!=null)  stmt.close();

         if(conn!=null)  conn.close();

      }catch(SQLException se){}}}