JDBC in Java

JDBC in java



JDBC Introducton
JDBC stands for Java Database connectivity
The Java JDBC API enables Java applications to connect to relational database via standard API, so your Java applications become independent of the database the application uses.

Java application using JDBC to connect to a database.
JDBC standardizes how to connect to a database, how to execute queries against it, how to navigate the result of such a query, and how to execute updates in the database. JDBC does not standardize the SQL sent to the database. This may still vary from database to database.

The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage.
1). Making a connection to a database.
2). Creating SQL or MySQL statements.
3). Executing SQL or MySQL queries in the database.
4). Viewing & modifying the resulting records.

JDBC Overview
1). Core JDBC Components.
2). Common JDBC use cases.
3). A JDBC Component Interaction Diagram.

The JDBC API consists of the following core parts :-
1). JDBC Drivers
2). Connections
3). Statements
4). Result Sets

There are four basic JDBC use cases around which most JDBC work evolves :-
1). Query the database(read data from it).
One of the most common use cases is to read data from a database. Reading data from a database is called querying the database.
2). Query the database meta data.
Another common use case is to query the database meta data. The database meta data contains information about the database itself. For instance, information about the tables defines, the column in each table, the data type etc.
3). Update the database.
Another very commong JDBC use case is to update the database. Updating the database means writing data to it. In other words, adding new records or modifying(updating) exiting records.
4). Perform transactions.

Transaction is another common use case. An transaction groups multiple updates and possible queries into a single action. Either all of the actions are executed, or none of them are.

Core JDBC Components
JDBC Driver
A JDBC driver is a collection of Java classes that enables you to connect to a certain database. For instance, MySQL will have its own JDBC driver. A JDBC driver implements a lot of JDBC interfaces. When your code uses a given JDBC driver, it actually just uses the standard JDBC interfaces. The concrete JDBC driver used in hidden behind the JDBC interfaces. Thus you can plugin a new JDBC driver without your code noticing it.
Of course, the JDBC drivers may vary a little in the features they support.

Connection
Once a JDBC driver is loaded and initialized, you need to connect to the database. You do so by obtaining a connection to the database via the JDBC API and the loaded driver. All Communication with the database happens via a connection.
An Application can have more than one connection open to a database at a time. This is actually very common when you have web application.
Statement
A statement is what you use to execte queries and updates against the database. There are a few different types of statements you can use in JDBC.
ResultSet
When you perform a query against the database you get back a ResultSet. You can then traverse this ResultSet to read the result of the query.
What is JDBC Driver in JAVA ?
A driver is nothing but software required to connect to a database from java program. JDBC is just an API, which java has designed and to implementation of these APIs lies on different vendor because different database works in a different way, they internally use different protocols.

So mySQL gives its own implementation of JDBC, we call it MySQL JDBC driver and we use it when we want to cennect to MySQL database from Java Program.
Similarly Oracle, SQL Server, Sybase and postgreSQL have provided their own implementation of JDBC API to connect them.
Since Java Program uses JDBC API, they are portable across different database, all you need to do is change the JDBC driver, which is just a JAR file if you are using type 4 JDBC driver.

Type of JDBC Driver :- There are four types of JDBC drivers :- 1). JDBC-ODBC Bride Driver
2). JDBC-Native Driver
3). JDBC-Net pure Java or Network Protocol driver
4). All Java driver or Thin driver

Why many types of JDBC Drivers
The different types of JDBC driver comes from the fact how they work, which is basically driven by two factors, portability and performance. Type 1 JDBC driver is the poorest in terms of portability and performance while type 4 JDBC drivers are highly portable and gives the best performance.
Since the database is very important and almost all java application uses the database is some form or other, it's important to learn JDBC well.

What is type 1 driver in JDBC?
This is the oldest JDBC driver, mostly used to connect databaase like MS Access from Microsoft Windows operation System. Type 1 JDBC driver actually translate JDBC calls into ODBC(Object Database connectivity) calls, which in turn connects to the database. Since it acts as bridge between JDBC and ODBC, it is also know as JDBC ODBC bridge driver. This driver had very poor performance because of several layers of translation which took place before your program connects to database. It has also less portable because it relies on ODBC driver to connect to database which is platform dependent, It is now obsolete and ubly used for developmenet and testing, Java 8 even removed this driver from JDK.

What is type 2 driver in JDBC?
This was the second JDBC driver introduced by Java after Type 1, hence it known as type 2. In this driver, performance was improved by reducing communication layer. Instead of talking of ODBC driver, JDBC driver directly talks to DB client using native API. That's why it's also known as native API or partly Java driver. Since it required native API to connect to DB client it is also less portable and platform dependent. Performance of type 2 driver is slightly better than type 1 JDBC driver.
What is type 3 driver in JDBC?
This was the third JDBC driver introduced by Java, hence known as type 3. It was very different than type 1 and type 2 JDBC driver in sense that it was completely written in JAVA as opposed to previous two drivers which were not written in Java. That's why this is also knwon as all Java drivers. This driver uses 3 tier approach i.e. client, server and database. So you have a Java Client talking to a java server and Java Server talking to database. Java client and server talk to each other usiing net protocol hence this type of JDBC driver is also known as Net protocol JDBC driver. This driver never gained popularity because database vender was reluctant to rewrite their existing natve library which was mainly in C and C++.

What is type 4 driver in JDBC?
This is the driver you are most likely using to connect to modern database like Oracle, SQL Server, MySQL, SQLLite and postgreSQl etc. This driver is implemented in Java and directly talks to database using its native protocol. This driver includes all database call in one JAR file, which makes it very easy easy to use. All you need to do to connect a database from java program is to include JAR file of relevant JDBC driver, because of light weight this is also known as Thin JDBC driver. Since this driver is also written in pure Java, It's portable across all platforms, which means you can use same JAR file to connect to MySQL even if your Java program is running on Windows, Linux or Solaris.
Performance of this type of JDBC driver is also best among all of them because database vendor liked this type and all enhancement they make thay also port for type 4 drivers.