JDBC Tutorial

What is JDBC ?

JDBC stand for Java Database Connectivity.

JDBC is an API for accessing data in relational databases such as Oracle, MySQL etc. from Java in a standard way using the SQL language.

JDBC is an API exist on java.sql package that is used to provide an interface between java program and database to store your data for future use.

JDBC API is used to access tabular data stored in any relational database.

It is part of the Java Standard Edition platform, from Oracle Corporation.

What is the Difference between JDBC and ODBC ?

There are following differences:

JDBC ODBC
JDBC stand for java database connectivity. ODBC stand for open database connectivity.
JDBC is used only for java language. ODBC is used for any language like C, C++, Java etc.
JDBC is introduced by Sun Micro System. ODBC is introduced by Microsoft.
JDBC is Platform Independent. ODBC is Platform Dependent.
JDBC can be used in any platform. ODBC can be used only windows platform.
JDBC is secure and robust. ODBC is not secure and robust.
JDBC drivers are developed in java. ODBC drivers are developed in C and C++.
JDBC is recommended for java application to increase the performance and Platform Independent. ODBC is not recommended for java application because performance down due to internal conversion and platform dependent.

What is the difference between DBMS and RDBMS ?


DBMS RDBMS
DBMS stand for Database Management System. RDBMS stand for Relational Database Management System.
DBMS store data as a file. RDBMS store data in tabluar form.
In DBMS data stored either hierarchical form or navigational form. In RDBMS data stored in tabluar form and have an identifier that is called primary key.
Normalization is not present. Normalization is present.
There is no relation between the tables because it uses file system. There is relation between the tables.
In DBMS does not apply any security. In RDBMS define the Integrity Constraints for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.
DBMS is not supported distributed database. RDBMS is supported distributed database.
It is used for small data. It is used for large data.
It is used by single user. It is used by multiple user.
Example : MS Access, FoxPro etc. Example : MySql, Oracle, SQLite, SQL Server etc.

JDBC Driver


JDBC Driver is a group of classes and interfaces that enables Java program intract with database.

JDBC Driver API provided by vendors such as MySql, Oracle etc.

There are four types of JDBC Drivers:

  1. Type 1 or JDBC-ODBC Bridge
  2. Type 2 or Native API
  3. Type 3 or Network Driver
  4. Type 4 or Thin Driver

1. Type 1 or JDBC-ODBC Bridge

JDBC-ODBC bridge driver uses ODBC driver to connect with the database.

It is easy to use.

It can easly connect with any Database.

Its performance is degraded.

The ODBC driver must be installed on client machine.

Note : In Java 8 JDBC-ODBC bridge is removed.

2. Type 2 or Native API

Native API driver converts JDBC call to Native API call to connect with database.

It is partly written in Java.

It performance is increased than JDBC-ODBC bridge driver.

The Native driver must be installed on client machine.

Vendors library must be installed on client machine.

3. Type 3 or Network Driver

It uses middleware (Application Server) that converts JDBC call to vendor specific database.

It is fully written in Java.

In this there is no need client side library.

Network support is required on client machine like internet.

Maintenance of Network Protocol driver becomes costly.

4. Type 4 or Thin Driver

Thin driver converts JDBC call to vendor specific database.

It is fully written in Java.

Its performance is better than others.

There is no need of software at client side or server side.

Drivers are dependent on database.

What are the steps to connect with database ?

There are following five steps to connect with database using JDBC:

  1. Load Driver Class
  2. Get Connection
  3. Create Statement
  4. Execute Query
  5. Close Connection

Lets see the Practicaly:

  1. Class.forName("fully qualified vendor driver class")
  2. Connection con=DriverManager.getConnection("url","username","pass")
  3. Statement smt=con.createStatement();
  4. smt.execute(); OR smt.executeUpdate(); OR smt.executeQuery();
  5. con.close();

There are basically three methods for executing sql query:

  • smt.execute()
  • smt.executeUpdate()
  • smt.executeQuery()

Difference between execute(), executeUpdate() and executeQuery():


execute() executeUpdate() executeQuery()
It is used for any type of sql query. It is used for update or modify the database. It is used to fetch data from database.
It is used for both Select or Non-select query. It is used for non-select query. It is used for select query.
Its return boolean type of value. Its return int type of value. Its return ResultSet type of object.
Example : It can be used any type of SQL statements. Example : INSERT, DELETE, UPDATE, ALTER, CREATE etc. Example : SELECT

Driver class provided by vendor specific database:


MySQL

Driver Class : com.mysql.jdbc.Driver

URL : jdbc:mysql://localhost:3306/databasename

Username : root

Password : your database password

Oracle 10 g

Driver Class : oracle.jdbc.driver.OracleDriver

URL : jdbc:oracle:thin:@localhost:1521:xe

Username : system

Password : your database password

Note : Database setup must be install on your machine either MySQL or Oracle or both as per your requirements.

MySQL Database :

After install MySQL 5.x you need to open MySQL Command Line Client and then enter password like below:

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.88-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

How to create a database :

mysql> create database mydatabase;
Query OK, 1 row affected (0.11 sec)

How to show database :

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

How to use a database:

mysql> use mydatabase;
Database changed

How to create a table:

mysql> create table student (roll int primary key,name varchar(20),marks float);
Query OK, 0 rows affected (0.22 sec)

Description of a table:

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| roll  | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| marks | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

In this layer you can perform all the SQL commands but we use java application layer to perform SQL operation because we dont want to access my database layer directly from end user.

How to store data in database using Java application:

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;

    public class TestDatabase{

        public static void main(String [] args)throws Exception{

        //load your driver class
        Class.forName("com.mysql.jdbc.Driver");

        //get connection
        Connection con=DriverManager
        .getConnection("jdbc:mysql://localhost:3306/mydatabase",
         "root","root");

        //create statement
        Statement smt=con.createStatement();

        //insert data into database
        int i=smt.executeUpdate("insert into student values
                (1,'deep singh',69.0)");

        //close connection
        con.close();

        if(i>0){
        System.out.println("Successfully Insert");
        }else{
        System.out.println("Insert Failed");
        }

        }
        }

If you compile and run then you can get result below:

C:\Users\Deep Singh\Desktop>javac TestDatabase.java

C:\Users\Deep Singh\Desktop>java TestDatabase
Exception in thread java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at TestDatabase.main(TestDatabase.java:7)

C:\Users\Deep Singh\Desktop>

You need to set class path of MySQL connector jar file like below:

C:\..\Desktop>set classpath=E:\Java Setup\MySql\mysql-connector-java-5.1.6.jar;

C:\Users\Deep Singh\Desktop>java TestDatabase
Successfully Insert

Result in MYSQL :

mysql> select * from student;
+------+------------+-------+
| roll | name       | marks |
+------+------------+-------+
|    1 | deep singh |    69 |
+------+------------+-------+
1 row in set (0.06 sec)

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)