JDBC for DUMMY

JDBC stands for Java DataBase Connectivity. It is a standard API for Java applications to interact with different set of databases.

In the above sentence we need to understand two things. i) what is API? and ii) How the two application interacts?

First let’s understand What is an API?

APIs, or Application Programming Interfaces, are like a set of rules and protocols that allow different software programs to talk to each other and share data or functionality. One of the Example of API is, when we use a weather app on our phone. it's probably using an API to get the current weather data from a service(i.e weather department).

Here, those software programs are JAVA and DATABASE. We can also say that API is a group of predefine classes and interfaces which helps Java to interact with Database.

(Interface can be thought of as a contract of service between two applications. This contract defines how the two communicate with each other using requests and responses.)

Now, let’s see how JDBC helps java interact with Database.

How it interacts?

By interact means it (JDBC API) connect and execute the query with the database. If we want to insert or fetch data from Database with Java code, we can do that with the help of JDBC API. For example, we can insert values in the SQL table by writing code in java, instead of writing query in MySQL. We can use JDBC API to access tabular data stored in any relational database. By the help of JDBC API, we can save, update, delete and fetch data from the database.

You can think JDBC as a translator, which translate java’s language to Database language and vice-versa.

What are JDBC Drivers ?

JDBC API doesn’t directly interact with the database. It uses JDBC driver of that particular database with which it wants to interact.

JDBC drivers are nothing but the implementations of classes and interfaces provided in the JDBC API. These implementations are provided by a particular database vendor and supplied along with the database. These implementations are used by the JDBC API to interact with that database.

(A translator must know both the language. We can say that JDBC drivers are kind of DB language for the JDBC API. And JDBC Driver Manager is Java language for JDBC API.)

JDBC API uses JDBC drivers to connect with the database of JDBC. There are four types drivers:

• JDBC-ODBC Bridge Driver

• Native Driver,

• Network Protocol Driver, and

• Thin Driver

Thin Driver is the latest that are being used in JDBC API, now a days.

Every type of DB has their own Drivers. Those drivers are type of thin driver.

We can use JDBC API to access tabular data stored in any relational database. By the help of JDBC API, we can save, update, delete and fetch data from the database. We can use JDBC API to handle database using Java program and can perform the following activities:

1. Connect to the database

2. Execute queries and update statements to the database

3. Retrieve the result received from the database.

JDBC API contains java.sql package. In this package, there are some important classes and interfaces. A list of popular interfaces of JDBC API are given below:

• Driver interface

• Connection interface

• Statement interface

• PreparedStatement interface

• CallableStatement interface

• ResultSet interface

• ResultSetMetaData interface

• DatabaseMetaData interface

• RowSet interface

These interfaces have few methods in them. When we write code in Java for SQL query, we can directly call those methods by making ‘object’ of these classes/interfaces.

There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:

• Register the Driver class.

• Create connection.

• Create statement.

• Execute queries.

• Close connection

Example:

In this example we are using MySql as the database. So we need to know following informations for the mysql database:

1. Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.

2. Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the database, localhost is the server name on which mysql is running, we may also use IP address, 3306 is the port number and sonoo is the database name. We may use any database, in such case, we need to replace the sonoo with our database name.

3. Username: The default username for the mysql database is root.

4. Password: It is the password given by the user at the time of installing the mysql database. In this example, we are going to use root as the password.

import java.sql.*;

class MysqlCon{

public static void main(String args[]){

try{

Class.forName("com.mysql.jdbc.Driver"); // 1) Register the driver class

Connection con=DriverManager.getConnection(

"jdbc:mysql://localhost:3306/sonoo","root","root"); //2) Create the connection object

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

Statement stmt=con.createStatement(); //3) Create statement

ResultSet rs=stmt.executeQuery("select * from emp"); //4) Execute queries

while(rs.next())

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

con.close(); // 5) Close connection

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

}

}