Assume that ABC Ltd is an automobile sales part manufacturing company that has many branches across the world. The corporate office at New York maintains MSAccess database for the sales details of various products, stock with each branch, personnel details etc.
The New Delhi office wants to establish connection with this remote database via Java Applet and sends in the above data to the corporate office. The solution for the above issues lies with JDBC or Java Database Connectivity. Before proceeding further, let us take a quick view regarding Microsoft’s ODBC and the preference of JDBC over ODBC.
Microsoft ODBC API offers connectivity to almost all databases on almost all platforms and is the most widely used Programming interface for accessing relational databases. But ODBC cannot be used directly with Java Programs due to various reasons described below.
(1) ODBC Cannot be used directly with Java because it uses a C interface. This will have drawbacks in the Security, implementation, robustness.
(2) ODBC makes use of Pointers which have been removed from Java.
Hence, JDBC came into existence. If you had done Database Programming with Visual Basic, then you will be familiar with ODBC. You can connect a VB Application to MSAccess Database or an Oracle Table directly via ODBC. Since Java is a product of Sun Microsystems, you have to make use of JDBC with ODBC in order to develop Java Database Applications.
Steps required to implement a Java Database Program
JDBC is a set of Java API for executing SQL Statements. This API Consists of a set of classes and interfaces to enable programmers to write pure Database Applications. It is possible to access various relational databases like Sybase, Oracle, Informix using JDBC.
STEP 1: Loading Drivers
First you have to load the appropriate driver. You can use one driver from the available four drivers. However, JDBC-ODBC Driver is the most preferred driver among developers. In order to load the driver, you have to give the following syntax:
STEP 2: Making the Connection
The getConnection() method of the Driver Manager class is called to obtain the Connection Object. The syntax looks like this:Connection conn = DriverManager.getConnection("jdbc:odbc:<DSN NAME>");
Here note that getConnection() is a static method, meaning it should be accessed along with the class associated with the method. The DSN Name is the name which you gave in the Control Panel while registering the Database or Table.
STEP 3: Creating JDBC Statements
A Statement object is what send your SQL Query to the Database Management System. You simply create a statement object and then execute it. It takes an instance of active connection to create a statement object. We have to use our earlier created Connection Object "conn" here to create the Statement object "stmt". The code looks like this:Statement stmt = conn.createStatement();
STEP 4: Executing the Statement
In order to execute the query, you have to obtain the Result Set object similar to Record Set in Visual Basic and call the executeQuery() method of the Statement interface. You have to pass a SQL Query like select * from students as a parameter to the executeQuery() method. Actually, the RecordSet object contains both the data returned by the query and the methods for data retrieval. The code for the above step looks like this:ResultSet rs = stmt.executeQuery("select * from student");
If you want to select only the name field you have to issue a SQL Syntax likeSelect Name from Student
The executeUpdate() method is called whenever there is a delete or an update operation.
STEP 5: Looping through the ResultSet
The ResultSet object contains rows of data that is parsed using the next() method like rs.next(). We use the getXXX method of the appropriate type to retrieve the value in each field.
If the first field in each row of ResultSet is Name (Stores String value), then getString method is used. Similarly, if the Second field in each row stores int type, then getInt() method is used like:System.out.println(rs.getInt("ID"));
STEP 6: Closing the Connection and Statement Objects
After performing all the above steps, you have to close the Connection and RecordSet Objects appropriately by calling the close() method. For example, in our above code we will close the object asconn.close()
and statement object asstmt.close()