GOVT WOMEN ENGINEERING COLLEGE , AJMER

JAVA DATABASE CONNECTIVITY

BY:
PALAK SHARMA (Roll No. = 23CSE077)
TO:
GAURAV GUPTA SIR
 LIST OF CONTENTS
1. What is JDBC
2. JDBC Architecture
3. Key advantages and features
4. Need of JDBC
5. Prerequisites Of JDBC
6. Steps to connect java application with database
7. Packages used in JDBC
8. JDBC Cycle
9. Operations to perform
10. How to create database
11. JDBC code to insert data
12. JDBC code to select from database
 WHAT IS JDBC?
• JDBC stands for Java Database Connectivity. JDBC is
a Standard API that enables Java applications to interact
with databases like (MYSQL, PostgreSQL, etc). This API
consists of classes and interfaces written in Java, In
other words, we can also say that JDBC acts as a bridge
between your Java application(frontend) and the
database(backend), allowing you to send and retrieve
data between the two systems.
 JDBC ARCHITECTURE
• Components of JDBC:
1. JDBC API: Provides the application-to-JDBC Manager connection.
2. JDBC Driver Manager: Manages the different drivers.
3. JDBC Drivers: Specific implementations for different databases.

 Types of JDBC Drivers:

• Type 1: JDBC-ODBC Bridge Driver: Translates JDBC calls into ODBC calls.
• Not recommended for production use due to performance issues.
• Type 2: Native-API Driver: Converts JDBC calls into database-specific calls using native libraries.
• Requires native database client libraries.
• Type 3: Network Protocol Driver: Converts JDBC calls into a database-independent protocol.
• Requires a middleware server.
• Type 4: Thin Driver: Pure Java driver that converts JDBC calls directly into the database
protocol.
• Most commonly used due to its portability and performance.
 KEY ADVANTAGES AND FEATURES
 Standardized API:
• JDBC provides a standard Java API for database interaction, making it easier
for developers to write database-independent code.
 Database Agnostic:
• JDBC allows applications to work with different database systems (like MySQL,
Oracle, SQL Server) without needing to rewrite the code for each database.
 Portability and Flexibility:
• JDBC applications can run on any machine with a Java Virtual Machine (JVM),
offering flexibility and portability.
 Simplified Database Interaction:
• JDBC simplifies the process of connecting to databases, executing SQL queries,
and handling results.
 Efficient Data Management:
• JDBC enables developers to efficiently manage data by providing a
standardized approach to database communication.
 NEED OF JDBC
• Database Communication: JDBC enables Java applications to communicate with relational
databases (like MySQL, PostgreSQL, Oracle, etc.) using standard SQL queries. Without JDBC,
Java applications would not be able to directly access and manipulate data stored in
databases.
• Data Access Layer: It provides a standardized way to access and manipulate data, enabling
developers to separate the database access code from the business logic. This makes
applications more modular and maintainable.
• Platform Independence: Since JDBC is part of the Java Standard Edition, it offers platform-
independent access to databases. This means that applications using JDBC can run on any
platform that supports Java, and they can connect to any database that provides a JDBC
driver.
 PREREQUISITES OF JDBC
 Java Development Kit (JDK) Installation:
• You need the JDK installed on your system to compile and run Java applications that use
JDBC.
 Database System:
• You'll need a database system (e.g., MySQL, PostgreSQL, Oracle, SQL Server) where you
want to connect and interact with.
 JDBC Driver:
• You need the correct JDBC driver for the database you are using.
 Basic Knowledge:
• Java programming , SQL , database concepts
 Steps to Connect Java Applications with Database
Step 1: Import the Packages
Step 2: Load the drivers using the forName( )
method
Step 3: Register the driver using
DriverManager
Step 4: Establish a connection using the
Connection class object
Step 5: Create a statement
Step 6: Execute the query
Step 7: Close the connections
Step 1: Import the Packages :
First, we need to import the packages.
import java.sql.*;

Step 2: Loading the drivers :

JDBC drivers are loaded because they act as a crucial intermediary between
Java applications and database systems, enabling communication and data
exchange. They translate Java calls into a language understood by the database,
allowing applications to execute queries and perform other database operations.
 Class.forName()
Here we load the driver’s class file into memory at the runtime. No need of using
new or create objects. The following example uses Class.forName() to load the
Oracle driver as shown below as follows:
Class.forName(className:"com.mysql.cj.jdbc.Driver");
Step 3: Establish a connection using connection class object
After loading the driver, establish connections as shown below as follows:
Connection con = DriverManager.getConnection(url , user , password);
•con: It is a reference to the Connection interface.
•user: Username from which your SQL command prompt can be accessed.
private static final String username = "root";
•password: password from which the SQL command prompt can be accessed.
private static final String password = "palak@123";
•Url: Uniform Resource Locator which is created as shown below:
private static final String url = "jdbc:mysql://localhoast:3306/db";
(mysql = database , localhost = IP address where database is stored , 3306 = port
number , db = name of database)
Step 4: Create a statement
Once a connection is established you can interact with the database. The
JDBCStatement , CallableStatement , and PreparedStatement interfaces define the
methods that enable you to send SQL commands and receive data from your
database.
Use of JDBC Statement is as follows:
• Statement st = con.createStatement();
It has two methods :
1. executeQuery() = to select data from database
2. executeUpdate() = to insert , delete and update data in database
These methods are used to execute the various queries
Step 5: Execute the query
•Now comes the most important part i.e executing the query. The query here is an SQL
Query. The executeQuery() method of the Statement interface is used to execute
queries of retrieving values from the database. This method returns the object of
ResultSet that can be used to get all the records of a table.
• ResultSet rs = st.executeQuery("SELECT * FROM users");

Step 6: Closing the Connections

•So, finally we have sent the data to the specified location and now we are on the verge
of completing our task. By closing the connection, objects of Statement and ResultSet
will be closed automatically. The close() method of the Connection interface is used to
close the connection. It is shown below as follows:
• con.close();
 PACKAGES USED IN JDBC
1. JAVA.SQL:
• (i) DriverManager
1. getConnection(String url): Establishes a connection to the database specified by the URL.
2.getConnection(String url, String user, String password): Establishes a connection to the database
with the specified user credentials.

• (ii). Connection
1.Statement createStatement(): Creates a Statement object for sending SQL statements to
the database.
2.PreparedStatement prepareStatement(String sql): Creates a PreparedStatement object for
sending parameterized SQL statements to the database.
3.CallableStatement prepareCall(String sql): Creates a CallableStatement object for
executing stored procedures.
4.void close(): Closes the connection to the database.
5.rollback(): Rolls back the current transaction.
• (iii) Statement
1.ResultSet executeQuery(String sql): Executes a SQL query and returns the result as
a ResultSet.
2.int executeUpdate(String sql): Executes a SQL statement that may change the database (e.g.,
INSERT, UPDATE, DELETE) and returns the number of affected rows.
3.Close(): Closes the statement.

• (iv) PreparedStatement
1.void setInt(int parameterIndex, int x): Sets the value of the designated parameter to the
given Java int value.
2.void setString(int parameterIndex, String x): Sets the value of the designated parameter to
the given Java String value..
3.int executeUpdate(): Executes the prepared statement that may change the database and
returns the number of affected rows.
4.executeQuery(): Executes a SELECT query.
5.execute(): Executes general SQL statements.
• (vi) CallableStatement
1.void registerOutParameter(int parameterIndex, int sqlType): Registers
the designated parameter as an output parameter.
2.ResultSet executeQuery(): Executes the stored procedure and returns the
result as a ResultSet.
3.int executeUpdate(): Executes the stored procedure that may change the
database and returns the number of affected rows.

• (vii) ResultSet
1.boolean next(): Moves the cursor to the next row in the ResultSet.
2.int getInt(String columnLabel): Retrieves the value of the designated column as an int.
3.String getString(String columnLabel): Retrieves the value of the designated column as
a String.
JDBC CYCLE:
 OPERATIONS TO PERFORM:
Following operations can be performed:
• SELECT FROM DATABASE
• INSERT IN DATABASE
• UPDATE IN DATABASE
• DELETE FROM DATABASE
 HOW TO CREATE DATABASE
To create database:
create database db;
=> Query OK , 1 row affected
To use that database:
Use db;
=> Database changed
To create table:
create table students(id int , age int, name varchar(50) , marks int);
=> Query OK , 0 rows affected
To insert items in table:
insert into students(id , age , name , marks) values (01 , 23 , “nayak” , 45);
=> Query OK , 1 row affected
To retrieve items from table:
select * from students;
=> 1 row in set
OUTPUT
 JDBC CODE TO SELECT DATA FROM DATABSE
import java.sql.*;
public class App
{
private static final String url = "jdbc:mysql://localhoast:3306/db";
private static final String username = "root";
private static final String password = "priyanshi";
public static final String Query = "SELECT id, name FROM students";
public static void main(String ar[])
{
try{
Class.forName(className:"com.mysql.cj.jdbc.Driver");
} catch(ClassNotFoundException e) {
System.out.println(e.getMessage());
}
 try{
Connection con = DriverManager.getConnection(url , username , password);
Statement st = con.createStatement();
ResultSet rst= st.executeQuery(Query);
while(rs.next()){
System.out.println("id: " + rst.getInt("id"));
System.out.println(", name: " + rst.getString(“name"));
}
rst.close();
st.close();
con.close();
} catch (SQLException e){
System.out.println(e.getMessage());
}
}
}
 OUTPUT

• id: 01,age: 23,name: nayak,marks: 45

• id: 02,age: 23,name: Rahul,marks: 74
 JDBC CODE TO INSERT DATA IN DATABASE
import java.sql.*;
public class Insert
{
private static final String url = "jdbc:mysql://localhoast:3306/db";
private static final String username = "root";
private static final String password = "palak@123";
private static final String Query=“insert into students values(03 , ‘Aarya’)”;
public static void main(String ar[])
{
try{
Class.forName(className:"com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try{
Connection con = DriverManager.getConnection(url , username , password);
Statement st = con.createStatement();
int rowsAffected = st.executeUpdate(Query);
 if(rowsAffected > 0){
System.out.println(x:"Data Inserted Successfully");
} else {
System.out.println(x:"Data Not Inserted");
}
st.close();
con.close();
} catch (SQLException e){
System.out.println(e.getMessage());
}
}
}
OUTPUT