Java Database Connectivity (JDBC): Merging Data from Multiple Tables: Joining,

Manipulating, Databases with JDBC, Prepared Statements, Transaction Processing, Stored

Procedures.

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and
execute the query with the database. It is a specification from Sun Microsystems that
provides a standard abstraction(API or Protocol) for Java applications to
communicate with various databases. It provides the language with Java database
connectivity standards. It is used to write programs required to access databases.
JDBC, along with the database driver, can access databases and spreadsheets. The
enterprise data stored in a relational database(RDB) can be accessed with the help of
JDBC APIs.

Definition of JDBC(Java Database Connectivity)

JDBC is an API(Application programming interface) used in Java programming to
interact with databases. The classes and interfaces of JDBC allow the
application to send requests made by users to the specified database

Purpose of JDBC
Enterprise applications created using the JAVA EE technology need to interact with
databases to store application-specific information. So, interacting with a database
requires efficient database connectivity, which can be achieved by using
the ODBC(Open database connectivity) driver. This driver is used with JDBC to
interact or communicate with various kinds of databases such as Oracle, MS Access,
Mysql, and SQL server database.

Components of JDBC

There are generally four main components of JDBC through which it can interact
with a database. They are as mentioned below:
1. JDBC API: It provides various methods and interfaces for easy communication
with the database. It provides two packages as follows, which contain the java SE
and Java EE platforms to exhibit WORA(write once run anywhere) capabilities.
The java.sql package contains interfaces and classes of JDBC API.

It also provides a standard to connect a database to a client application.

2. JDBC Driver manager: It loads a database-specific driver in an application to
establish a connection with a database. It is used to make a database-specific call to
the database to process the user request.
3. JDBC Test suite: It is used to test the operation(such as insertion, deletion,
updation) being performed by JDBC Drivers.
4. JDBC-ODBC Bridge Drivers: It connects database drivers to the database. This
bridge translates the JDBC method call to the ODBC function call. It makes use of
the sun.jdbc.odbc package which includes a native library to access ODBC
characteristics.

Architecture of JDBC

Description:
1. Application: It is a java applet or a servlet that communicates with a data
source.
2. The JDBC API: The JDBC API allows Java programs to execute SQL
statements and retrieve results. Some of the important classes and
interfaces defined in JDBC API are as follows:
3. DriverManager: It plays an important role in the JDBC architecture. It
uses some database-specific drivers to effectively connect enterprise
applications to databases.
4. JDBC drivers: To communicate with a data source through JDBC, you
need a JDBC driver that intelligently communicates with the respective
data source.

JDBC API uses JDBC Drivers to connect with the database.

JDBC Drivers
JDBC drivers are client-side adapters (installed on the client machine, not on the
server) that convert requests from Java programs to a protocol that the DBMS can
understand. There are 4 types of JDBC drivers:
1. Type-1 driver or JDBC-ODBC bridge driver
2. Type-2 driver or Native-API driver (partially java driver)
3. Type-3 driver or Network Protocol driver (fully java driver)
4. Type-4 driver or Thin driver (fully java driver)
Working of JDBC
Java application that needs to communicate with the database has to be programmed
using JDBC API. JDBC Driver supporting data sources such as Oracle and SQL
server has to be added in java application for JDBC support which can be done
dynamically at run time. This JDBC driver intelligently communicates the respective
data source.

//Java program to implement a simple JDBC application

package com.vinayak.jdbc;

import java.sql.*;

public class JDBCDemo {

public static void main(String args[])

throws SQLException, ClassNotFoundException

String driverClassName

= "sun.jdbc.odbc.JdbcOdbcDriver";

String url = "jdbc:odbc:XE";

String username = "scott";

String password = "tiger";

String query

= "insert into students values(109, 'bhatt')";

// Load driver class

Class.forName(driverClassName);

// Obtain a connection

Connection con = DriverManager.getConnection(

url, username, password);

 // Obtain a statement

Statement st = con.createStatement();

// Execute the query

int count = st.executeUpdate(query);

System.out.println(

"number of rows affected by this query= "

+ count);

// Closing the connection as per the

// requirement with connection is completed

con.close();

} // class

The above example demonstrates the basic steps to access a database using JDBC.
The application uses the JDBC-ODBC bridge driver to connect to the database. You
must import java.sql package to provide basic SQL functionality and use the classes
of the package.
What is the need of JDBC?
JDBC is a Java database API used for making connection between java applications
with various databases. Basically, JDBC used for establishing stable database
connection with the application API. To execute and process relational database
queries (SQL or Oracle queries), multiple application can connect to different types
of databases which supports both standard (SE) and enterprise (EE) edition of java.

Refer this youtube channel

Merging Data from Multiple Tables

https://www.youtube.com/watch?v=qDznViPYu-8&t=1444s

: Joining,
Manipulating, Databases with JDBC,

https://www.youtube.com/watch?v=VzSQTEsiIQ0&t=1132s
Types of Statements in JDBC
The statement interface is used to create SQL basic statements in Java it provides
methods to execute queries with the database. There are different types of statements
that are used in JDBC as follows:
 Create Statement
 Prepared Statement
 Callable Statement
1. Create a Statement: From the connection interface, you can create the object for
this interface. It is generally used for general–purpose access to databases and is
useful while using static SQL statements at runtime.
Syntax:
Statement statement = connection.createStatement();

Implementation: Once the Statement object is created, there are three ways to
execute it.

 boolean execute(String SQL): If the ResultSet object is retrieved, then it

returns true else false is returned. Is used to execute SQL DDL statements
or for dynamic SQL.
 int executeUpdate(String SQL): Returns number of rows that are
affected by the execution of the statement, used when you need a number
for INSERT, DELETE or UPDATE statements.
 ResultSet executeQuery(String SQL): Returns a ResultSet object. Used
similarly as SELECT is used in SQL.

2. Prepared Statement represents a recompiled SQL statement, that can be executed

many times. This accepts parameterized SQL queries. In this, “?” is used instead of
the parameter, one can pass the parameter dynamically by using the methods of
PREPARED STATEMENT at run time.
Illustration:
Considering in the people database if there is a need to INSERT some values, SQL
statements such as these are used:
INSERT INTO people VALUES ("Ayan",25);
INSERT INTO people VALUES("Kriya",32);
To do the same in Java, one may use Prepared Statements and set the values in the ?
holders, setXXX() of a prepared statement is used as shown:

String query = "INSERT INTO people(name, age)VALUES(?, ?)";

Statement pstmt = con.prepareStatement(query);
pstmt.setString(1,"Ayan");
ptstmt.setInt(2,25);
// where pstmt is an object name

Implementation: Once the PreparedStatement object is created, there are three ways
to execute it:
 execute(): This returns a boolean value and executes a static SQL
statement that is present in the prepared statement object.
 executeQuery() : Returns a ResultSet from the current prepared statement.
 executeUpdate() : Returns the number of rows affected by the DML
statements such as INSERT, DELETE, and more that is present in
the current Prepared Statement.

3. Callable Statement are stored procedures which are a group of statements that
we compile in the database for some task, they are beneficial when we are dealing
with multiple tables with complex scenario & rather than sending multiple queries to
the database, we can send the required data to the stored procedure & lower the logic
executed in the database server itself. The Callable Statement interface provided by
JDBC API helps in executing stored procedures.
Syntax: To prepare a CallableStatement
CallableStatement cstmt = con.prepareCall("{call
Procedure_name(?, ?}");
Implementation: Once the callable statement object is created
 execute() is used to perform the execution of the statement.