Enterprise Computing - JDBC





  Home
  Servlet
  JDBC
  EJB
  XML
  JSP
  RMI
  JavaIDL
  Protocols
  UML

  Books

  Contact

JDBC


Introduction
JDBC is an API layered on top of vendor specific database systems. This API connects to the database through driver that supports the database system. There are four types of drivers. They are, JDBC-ODBC bridge drivers, Native API drivers, Net protocol drivers and Native-protocol drivers. These drivers use JDBC URL to identify and connect to a particular database. After the connection we can execute SQL statements. JDBC API provides support to handle errors such as database connection errors, malformed SQL statements and insufficient database privileges. The API let the developer to know about the underlying database informations called metadata. It provides support to transaction that uses multiple statements

Advantage
The JDBC API provides universal data access from the Java programming language. Using the JDBC 2.0 API, you can access virtually any data source, from relational databases to spreadsheets and flat files. In other words, with the JDBC API, it isn't necessary to write one program to access a Sybase database, another program to access an Oracle database, another program to access an IBM DB2 database, and so on. One can write a single program using the JDBC API, and the program will be able to send SQL or other statements to the appropriate data source. And, with an application written in the Java programming language, one doesn't have to worry about writing different applications to run on different platforms. The combination of the Java platform and the JDBC API lets a programmer write once and run anywhere.

JDBC Basics

The JDBC 2.0 API is comprised of two packages:

  1. the java.sql package
  2. the javax.sql package
Steps involved in writting a database access program,
  1. Load the DB2 for OS/390 JDBC Driver Class.forName("ibm.sql.DB2Driver");
  2. Create Connection instance Connection con = DriverManager.getConnection (url); where URL is in the form of "jdbc:db2OS390:location_name"
  3. Create a Statement instance Statement stmt = con.createStatement();
  4. Execute a Query and generate a ResultSet instance ResultSet rs = stmt.executeQuery("SELECT * FROM EMP);
  5. Print column 1 (employee name) to console String s = rs.getString(1); System.out.println("Employee NAME = " + s);
  6. Close the statement stmt.close();
  7. Close the connection con.close();

Example

(The following code is a servlet, respond to the GET method, connect with the mySQL table, excute a query and format the result in HTML)
import javax.servlet.*;                                    
import javax.servlet.http.*;                               
import java.io.*;                                          
import java.util.*;                                        
import java.sql.*;                                         
                                                           
public class JDBCservlet extends HttpServlet  {          

   public void service(HttpServletRequest req, HttpServletResponse     res)    
         throws ServletException, IOException  {                               
                                                                               
     String location_name = new String();                                     
                                                                               
     String  queryString = new String();                                       
                                                                               
     queryString = ("SELECT * FROM Test ORDER BY 1");           
                                                                               
                                                                               
  /* set the output content type and get                                       
     an output stream for the response                                         
  */                                                                           
     res.setContentType("text/html");                                          
                                                                               
     ServletOutputStream out = res.getOutputStream();                          
     out.println("<HTML> <HEAD> <TITLE> JDBC CONNECTION to mySQL.</TITLE>"); 
     out.println("<h1> ****  JDBCServlet ****</h1>");                            
                                                                               
  /* load the Driver to mySQL..........                                          
  */                                                                           
                                                                               
         try  {                                                                
              Class.forName("org.gjt.mm.mysql.Driver");                              
              }  catch (ClassNotFoundException e) {                            
                 out.println(e.toString());                                    
           }  // end catch                                                     
                                                                               
  /* create the connection ..........                                          
  */                                                                           
                                                                               
     try {                                                                        
         String url = "jdbc:mysql:location";                          
         Connection con = DriverManager.getConnection (url);                
         Statement stmt = con.createStatement();                                  
                                                                                
         out.println("<P> RETRIEVE ALL ROWS FROM TEST TABLE</P>");   
                                                                                
         ResultSet rs = stmt.executeQuery(queryString);                                
                                                                                
         out.println("<PRE>");                                                    
                                                                                
         while (rs.next()) {                                                         
                                                                                
         String s = rs.getString(2);                                              
	   int i = rs.gerInt(1);
         out.println(s, i);                                   
     }                                                                          
                                                                                
     out.println("</PRE>");                                                   
                                                                                
     out.println(" </BODY> </HTML>");                                             
                                                                                
  /* flush and close the OutputStream*/                                        
     out.flush();                                                      
     out.close();                                                      
                                                                      
  /* Close the result set */                                          
                                                                      
     rs.close();                                                      
                                                                      
  /* Close the statement  */                                         
                                                                      
     stmt.close();                                                
                                                                      
  /* Close the connection */                                         
     con.close();                                               
                                                                      
  }  // end  try                                                      
                                                                      
  catch (Exception e) {                                           
     out.println(e.toString());                                     
  }   // end catch                                                
 } // end of service method           
                                        
} // end of class JDBCServlet

You can get this code from here
You can run this code click here
more to come.....



Click on the image to order the book from Amazon.com

JDBC(TM) API Tutorial and Reference - Seth White, et. al.
cover
Search books!

Search Now:
In Association with Amazon.com