JDBC Connection

JAVA DATABASE CONNECTIVITY

Ø  JDBC-: Java database connectivity (jdbc) is an application programming interface which allow the programmer to connect and interact with databases.

Ø  It provides methods to query and update data in the database through update statement like SQL create,update and insert and query statement such as select.

Ø  Like java ,jdbc is compatible with many platforms like UNIX and MAC OS.

Ø  JDBC API uses jdbc drivers to connect with the database.

WHY USE JDBC?

Before JDBC,ODBC API was the database API to connect and execute query with the database .But ,ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured ).That is why java has defined its own API that uses JDBC drivers.

WHAT IS API?

API(Application programming interface )is a document that contains description of all the features of a product or software.It represents classes and interfaces that software programs can follow to communicate with each other.

JDBC DRIVER:-

JDBC driver is a software component that enables java application to interact with the database. JDBC driver implementations vary because of the wide variety of operating systems and hardware platforms in which Java operates. Sun has divided the implementation types into four categories, Types 1, 2, 3, and 4, which is explained below.There are four types of JDBC drivers.

1.JDBC-ODBC bridge driver

2.Native-API driver

3.Network Protocol driver

4.Thin driver(fully java driver)

1.   TYPE-1 JDBC –ODBC bridge driver:- In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client machine. Using ODBC, requires configuring on your system a Data Source Name (DSN) that represents the target database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls.This is now discouraged because of thin driver. The JDBC-ODBC Bridge that comes with JDK 1.2 is a good example of this kind of driver.

Advantages:-

Ø  Easy to use.

Ø  Can be easily connected to any database.

      Disadvantages:-

Ø  Performance degraded because JDBC method call is converted into the ODBC function calls.

Ø  The ODBC driver needs to be installed on the client machine.

Ø  Slow execution time.

Ø  Dependent on ODBC driver.

Ø  Use java Native Interface to make ODBC call.

2.Native –API driver:-

In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls, which are unique to the database. These drivers are typically provided by the database vendors and used in the same manner as the JDBC-ODBC Bridge. The vendor-specific driver must be installed on each client machine.It is not written entirely in java. If we change the Database, we have to change the native API.

Advantages:-

Ø  Performance upgraded than JDBC-ODBC bridge driver.

Ø  Contain additional features.

Disadvantage:-

Ø  The native driver needs to be installed on the each client machine.

Ø  The vendor client library needs to be installed on client machine.

Ø  Increased cost pf application.

3.Network Protocol Driver:-

In a Type 3 driver, a three-tier approach is used to access databases. The JDBC clients use standard network sockets to communicate with a middleware application server. The socket information is then translated by the middleware application server into the call format required by the DBMS, and forwarded to the database server.

This kind of driver is extremely flexible, since it requires no code installed on the client and a single driver can actually provide access to multiple databases.

Advantages:-

Ø  No client side library is required because of application server that can perform many tasks like auditing ,logging etc.

Disadvantages:-

Ø  Network support is required on the client machine.

Ø  Requires database specific coding to be done in the middle tier.

Ø  Maintenance of network protocol driver becomes costly because it requires database specific coding to be done in the middle ware.

4.THIN DRIVER:-

In a Type 4 driver, a pure Java-based driver communicates directly with the vendor's database through socket connection. This is the highest performance driver available for the database and is usually provided by the vendor itself.

This kind of driver is extremely flexible, you don't need to install special software on the client or server. Further, these drivers can be downloaded dynamically.

Advantages:-

Ø  Better performance than all other drivers.

Ø  No software is required at client side or server side.

Disadvantages:-

Ø  Drivers depemds on the database.

JDBC DATABASE CONNECTION:-

After you've installed the appropriate driver, it is time to establish a database connection using JDBC.

The programming involved to establish a JDBC connection is fairly simple. Here are these simple six steps −

·        Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code.

·        Register JDBC Driver: This step causes the JVM to load the desired driver implementation into memory so it can fulfill your JDBC requests.

·        Database URL Formulation: This is to create a properly formatted address that points to the database to which you wish to connect.

·        Create Connection Object: Finally, code a call to the DriverManagerobject's getConnection( ) method to establish actual database connection.

·        Create statement object

·        Execute the query

·        Close the connection

1.Import JDBC Packages

The Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code.

To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your source code −

import java.sql.* ;  // for standard JDBC programs

import java.math.* ; // for BigDecimal and BigInteger support

2.Register JDBC Driver

You must register the driver in your program before you use it. Registering the driver is the process by which the Oracle driver's class file is loaded into the memory, so it can be utilized as an implementation of the JDBC interfaces.

You need to do this registration only once in your program. You can register a driver in one of two ways.

Approach I - Class.forName()

The most common approach to register a driver is to use Java's Class.forName() method, to dynamically load the driver's class file into memory, which automatically registers it. This method is preferable because it allows you to make the driver registration configurable and portable.

The following example uses Class.forName( ) to register the Oracle driver −

try {

   Class.forName("oracle.jdbc.driver.OracleDriver");

}

catch(ClassNotFoundException ex) {

   System.out.println("Error: unable to load driver class!");

   System.exit(1);

}

Approach II - DriverManager.registerDriver()

The second approach you can use to register a driver, is to use the static DriverManager.registerDriver() method.

You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

The following example uses registerDriver() to register the Oracle driver −

try {

   Driver myDriver = new oracle.jdbc.driver.OracleDriver();

   DriverManager.registerDriver( myDriver );

}

catch(ClassNotFoundException ex) {

   System.out.println("Error: unable to load driver class!");

   System.exit(1);

}

3.Database URL Formulation

After you've loaded the driver, you can establish a connection using the DriverManager.getConnection() method. For easy reference, let me list the three overloaded DriverManager.getConnection() methods −

·        getConnection(String url)

·        getConnection(String url, Properties prop)

·        getConnection(String url, String user, String password)

Here each form requires a database URL. A database URL is an address that points to your database.

4.Create connection object:-

The getConnection() method of DriverManager class is used to establish connection with the database. 

Syntax of getConnection() method:-

1.)         public static Connection getConnection(String url)throws SQLException

2.)         public static Connection getConnection(String name,String password)throws SQLException

Example to establish connection with the Oracle database:-

Connection conn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”,”system”,”password”);

5. Create statement object:-

The createStatement() metod of Connection interface is used to create statement.The object of statement is responsible to execute queries with the database.

 

Syntax of createStatement() method:-

Public Statement createStatement ()throws SQLException

Example to createStatement() method:-

Statement stmt=con.createStatement();

6.Execute the query:-

The executeQuery() method of Statement interface is used to execute queries to the database.This method returns the object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method:-

 Public ResultSet executeQuery(String sql)throws SQLException

Example of execute query:-

ResultSet rs= stmt.executeQuery(“select*from emp”);

While(rs.next())

{

System.out.println(rs.getInt(1)+” “+rs.getString(2));

}

7.Close the connection object:-

By closing the connection object statement and ResultSet will be closed automatically .The close() method of Connection interface is used to close the connection.

Syntax of close() method:-

Con.close();

 

 

EXAMPLE OF JDBC:-

Example to Connect Java Application with mysql database

In this example, sonoo is the database name, root is the username and password.

1.    import java.sql.*;  

2.    class MysqlCon{  

3.    public static void main(String args[]){  

4.    try{  

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

6.    Connection con=DriverManager.getConnection(  

7.    "jdbc:mysql://localhost:3306/sonoo","root","root");  

8.    //here sonoo is database name, root is username and password  

9.    Statement stmt=con.createStatement();  

10. ResultSet rs=stmt.executeQuery("select * from emp");  

11. while(rs.next())  

12. System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  

13. con.close();  

14. }catch(Exception e){ System.out.println(e);}  

15. }  

16. }