JDBC Tutorial

What is JDBC in Java? Complete Tutorial with Examples

JDBC stands for Java Database Connectivity. It is an API for accessing data in relational databases like Oracle, MySQL, etc. from Java in a standard way using SQL language.

Table of Contents

• What is JDBC?
• JDBC vs ODBC
• DBMS vs RDBMS
• JDBC Drivers - 4 Types
• 5 Steps to Connect Database
• execute() vs executeUpdate() vs executeQuery()
• Vendor Driver Classes
• MySQL Practical Example

What is JDBC?

• JDBC is an API that exists in java.sql package used to provide an interface between Java program and database.
• It is used to access tabular data stored in any relational database.
• It is part of Java Standard Edition from Oracle Corporation.

What is the Difference between JDBC and ODBC?

JDBC	ODBC
JDBC stands for Java Database Connectivity	ODBC stands for Open Database Connectivity
Used only for Java language	Used for any language like C, C++, Java etc.
Platform Independent	Platform Dependent
Can be used on any platform	Can be used only on Windows platform
Secure and robust	Not secure and robust
Drivers are developed in Java	Drivers are developed in C and C++
Recommended for Java apps for performance and portability	Not recommended for Java - performance down due to internal conversion

What is the Difference between DBMS and RDBMS?

DBMS	RDBMS
DBMS stands for Database Management System	RDBMS stands for Relational Database Management System
Stores data as file	Stores data in tabular form
Data stored in hierarchical or navigational form	Data stored in tabular form with primary key
Normalization is not present	Normalization is present
No relation between tables (uses file system)	Relation between tables exists
Does not apply security	Defines Integrity Constraints for ACID properties
Not support distributed database	Supports distributed database
Used for small data, single user	Used for large data, multiple users
Example: MS Access, FoxPro	Example: MySQL, Oracle, SQLite, SQL Server

JDBC Driver

JDBC Driver is a group of classes and interfaces that enables Java program to interact with database. Driver API is provided by vendors like MySQL, Oracle etc.

There are 4 types of JDBC Drivers:

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

1. Type 1 - JDBC-ODBC Bridge

• Uses ODBC driver to connect to database, easy to use
• Performance is degraded, ODBC must be installed on client
• Note: Removed in Java 8

2. Type 2 - Native API

• Converts JDBC call to Native API call, partly written in Java
• Performance better than Type 1, native driver must be installed on client

3. Type 3 - Network Driver

• Uses middleware (Application Server) that converts JDBC to vendor specific
• Fully written in Java, no client library needed, but network required

4. Type 4 - Thin Driver

• Converts JDBC call directly to vendor specific database, fully in Java
• Best performance, no software needed on client/server, but driver dependent on DB

What are the Steps to Connect with Database?

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

Practically:

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

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

execute()	executeUpdate()	executeQuery()
Used for any type of SQL query	Used for updating/modifying database	Used to fetch data from database
Used for both Select and Non-select	Used for non-select query only	Used for select query only
Returns boolean	Returns int (row count)	Returns ResultSet object
Can be used for any SQL statement	INSERT, DELETE, UPDATE, ALTER, CREATE	SELECT

Driver Class Provided by Vendor

MySQL: Driver: com.mysql.jdbc.Driver | URL: jdbc:mysql://localhost:3306/databasename | User: root

Oracle 10g: Driver: oracle.jdbc.driver.OracleDriver | URL: jdbc:oracle:thin:@localhost:1521:xe | User: system

MySQL Practical Example

Create database & table

mysql> create database mydatabase;
mysql> use mydatabase;
mysql> create table student (roll int primary key, name varchar(20), marks float);
mysql> desc student;

Java code to insert data

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

public class TestDatabase{
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","root");
        Statement smt = con.createStatement();
        int i = smt.executeUpdate("insert into student values (1,'deep singh',69.0)");
        con.close();
        if(i>0) System.out.println("Successfully Insert");
        else System.out.println("Insert Failed");
    }
}

Classpath Issue Fix

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

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

Conclusion

JDBC is the backbone of database connectivity in Java. Use Type 4 Thin Driver for best performance. Always follow 5 steps: Load Driver, Get Connection, Create Statement, Execute Query, Close Connection.