Java Database Connectivity (JDBC Tutorial)

7:58 PM No Comments

Java Database Connectivity:

JDBC (Java Database Connectivity) is designed to allow users to use SQL(Structured Query Language) to query databases. It makes the tasks of the developers easy as it handles all low-level concerns about particular database types.

JDBC is similar to Microsoft’s ODBC with the plus point “Platform Independence”. To use JDBC, you need to have database driver to communicate with the database. Normally drivers are installed while installing the database. Like if you install MS SQL Server, Oracle or DB2, database drivers will be installed. If you are working with MySQL, PostgreSQL or some third party database, you need to put its driver (Jar fileI into the class path.

JDBC Drivers
            JDBC drivers can be broadly divided into four categories depending upon the driver implementation. The four categories/types are:

            1: JDBC-ODBC Bridge
            2:  Native-API/partly Java driver
            3: Net-protocol/all-Java driver
            4: Native-protocol/all-Java driver

I will briefly talk about each type:
            JDBC-OBC bridge driver is pure Java and is include in java.sql.*. The client needs ODBC driver manager and ODBC driver for data source. It is ideal in situations, when ODBC driver is available for the database and is already installed on the client machine.

            Type-2 is Native code driver. It implements native JDBC interfaces using language functions in the DBMS product’s API. Type 2 drivers need platform specific library, so client and server both may run on same host. Type 2 drivers offer better performance than Type 1 drivers.

Type 3 drivers are pure Java drivers and they use middleware network protocol. They need DBMS server to implement the standard protocol to be middleware specific. The advantage is that there is no nee for any vendor database library to be present on client machines. Interesting thing is, there is no JDBC standard network protocol yet.

Type 4 drivers are pure Java drivers and they use vendor specific network protocol. These use DBMS specific network protocol (Oracle SQL Net, etc).
For the beginners, Type 1 drivers are suitable. Users simply have to make a DSN and start interacting with the database.

Using JDBC-ODBC Bridge

The beginners should start with JDBC-ODBC Bridge since it is simple and easy to work with. Consider that you have a database with tables and data and you want to connect to it in order to carry out operations.
            First step is to create an ODBC dsn. It is done from Control panel > Data Sources (ODBC).
            Now you have to load the JDBC driver. This is done using static method forName(…) of class called Class. Static method forName(…) takes name of the driver as parameter.

Code:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

DriverManager.getConnection() is used to connect to the database. Its signature is as follows:

Code:

static Connection getConnection(String url, String user, String password)



Continue... 
31/01/2011

Connection time:

            Sometimes, it is interesting to know how much time it takes to connect to the database. The code sample below calculates the time it takes to connect to a database referred by dsn.

Code:

long connection_time; Date start = new Date();  //get start time String stUrl_= "jdbc:odbc:myDSN"; connection_ = DriverManager.getConnection(stUrl,"sa","sa"); Date end = new java.util.Date();  //get end time connection_time = end.getTime()-start.getTime();

 

 

Getting the Warnings:

            Sometimes it is a wise decision to retrieve the first warning reported by calls on this Connection object. This can be done using getWarnings() method. The code sample below shows how to print all the warnings with their sates and messages.

Code:

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ; Connection conn = DriverManager.getConnection( "jdbc:odbc:Database" ) ; // Print all warnings for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() ) { System.out.println( "SQL Warning:" ) ; System.out.println( "State  : " + warn.getSQLState()  ) ; System.out.println( "Message: " + warn.getMessage()   ) ; System.out.println( "Error  : " + warn.getErrorCode() ) ; } Adding records in the database tables:             Statement object is used to add enteries into the tables. The method used is executeUpdate(…). Code: Statement st = conn.createStatement(); st.executeUpdate("INSERT INTO customers VALUES (100, 'Laiq', 'Mr.', 'Paris', 2008)"); Using Resultset:             ResultSet is an interface found in java.sql package. It actually represents a table in the memory containing all the records fetched from the database in result of a query. Code: String name, brand ; float price; ResultSet rs = stmt.executeQuery("SELECT * FROM customers"); while ( rs.next() ) { name = rs.getString("name"); brand = rs.getString("brand"); price = rs.getFloat("price"); } Getting number of rows updated             Statement’s executeUpdate(…) method return no of row modified. So you can easily know how many rows were modified by your update query. Code: int rows = stmt.executeUpdate( "UPDATE customer SET cust_name = ‘Laiq’ WHERE cust_id = 100" ) ; System.out.println( rows + " Rows modified" ) ;