Pages

Tuesday, November 8, 2011

jdbc

Guys JDBC is a JAVA based technology to provide communication between a JAVA application and any database. 

Note: JDBC is not an acronym for JAVA DATABASE CONNECTIVITY it never was,it is a trademark from sun microsystem for its technology called JDBC. 

JDBC is used whenever the java windows based or Web based application u are working on needs data to be stored and accessed from a database server.
JDBC have following advantages: 
  •  it is database independent. 
  •  it is Operating System independent. 
But JDBC can only be used in JAVA based application.

Any java process or program which communicate with database using JDBC APIs is Known as JDBC client. 

A JDBC client has to: 

1.) Establish connection with database server. 
2.) Submitting Appropriate SQL statement to the database management system. 
3.) Receiving the response from the DBMS and processing it for presentation to end user. 
4.) Dealing with exception if any occurs. 
5.) Dealing with transactions whenever required. 
6.) last and very important closing the connection. 



These are the basic components of JDBC technology:
*JDBC driver:most important part of jdbc which acts as mediator between java programs(clients) and DBMS.
*JDBC API: is a collection of library interfaces and classes whose methods are used to perform CRUD(create,retrieve,update and delete) operations with database.
JDBC APIs are distributed into two packages in JAVA
1. java.sql;
2. javax.sql;
*JDBC driver manager: is a java based application we use to make our JDBC applications driver independent,because driver manager plays the role of choosing jdbc driver for our application at the run time.
*JDBC test suits: are used to test whether the selected JDBC driver is supported by the given DBMS features or not.


JDBC driver architectures:
 type 1 driver.
 type 2 driver.
 type 3 driver.
 type 4 driver.

type 1 driver architecture is believed to be the simplest architecture but not the most reliable one, so 98% of the Business applications of the world don't go for type 1 drivers.
the one and only driver available in type 1 architecture is JdbcOdbcDriver and is already present with the JAVA provided by sun microsystems upto java 1.6 version.
    The JDBC:ODBC Bridge is installed automatically with the Java 2 SDK, Standard Edition, as package sun.jdbc.odbc.,and this is enough to start database connectivity for now.
    you don't need to bother about other drivers.                                                                                                 
    DSN(data source name): to create a dsn for your database created with DBMS. follow these steps:-

    • go to control panel > system and security > admin tools > data sorces(ODBC)
    • a new window will appear.
    • select user DSN in this window.
    • click on add.
    • select the driver from the list shown.
    • click on finish.
    • enter the dsn name in the new opened pop-up window & select the database.
    • and finish

    Now you are done with creating DSN. your DSN is ready.

    Now that you have fullfilled basic requirements and have basic knowledge required to proceed you can start coding and follow these seven steps in your program:
    seven basic steps in JDBC
    1. load the driver Class.
    2. define the connection URL.
    3. establish the connection.
    4. create  a statement object.
    5. execute a querry.
    6. process the result.
    7. close the connection if not needed any more.

    here is an example to clarify the above steps :


    
    
    import java.sql.* ;
    
    class JDBC
    {
     public static void main( String args[] )
     {
      try
         {
          // Load the driver
          Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
    
          // establish a connection to the dsn
          Connection conn = 
          DriverManager.getConnection( "jdbc:odbc:DSNname","username","password");
    
          // create a statement object using the connection object
          Statement stmt = conn.createStatement() ;
    
          // Execute the query
          ResultSet rs = stmt.executeQuery( "SELECT * FROM Cust" ) ;
    
          // process the result set
          while( rs.next() )
             {
                  System.out.println( rs.getString(column name)) ;
             }
    
          // Close the connection
         
          conn.close() ;
         }
      catch( SQLException se )
         {
          System.out.println( "SQL Exception:" ) ;
          System.out.println( e ) ;
         }
     }
    }
    
    
    
    
     
    This was an Application using JdbcOdbcDriver which is a type-1 Driver.
    If you want to learn about type-4 the most effective architecture used in realtime
    have a look at my new post Type-4 based JDBC applications.
    
    
    I hope this will be helpful to you guys 
     
    i'll appreciate your feedback.