Java Database Connectivity(JDBC)

-------------------------------------------------------------
Sample Queries:
1) I want to create Course table with columns
Course_id course_name Duration start_date

CREATE TABLE COURSE (

COURSE_ID NUMBER(8),
COURSE_NAME VARCHAR2(20),
DURATION VARCHAR2(20),
START_DATE DATE,
PRIMARY KEY(COURSE_ID)
);

2) I want to create STUDENT table with columns

Roll_number section name marks

CREATE TABLE STUDENT (

ROLL_NUMBER NUMBER(6),
SECTION VARCHAR2(5),
NAME VARCHAR2(10),
MARKS NUMBER(5)
);

In the above table we can’t use roll_number as a

primary key, because same roll_number can exist in
Another section also.
In this case we have to use composite primary key.
Composite primary key means, combination of two or
more columns are acting as a primary key in the table.
The correct query for the above.
 CREATE TABLE STUDENT (
ROLL_NUMBER NUMBER(6),
SECTION VARCHAR2(5),
NAME VARCHAR2(10),
MARKS NUMBER(5),
PRIMARY KEY(ROLL_NUMBER, SECTION)
);

PRIMARY KEY = UNIQUE + NOT NULL

STUDENT
ROLL_NUMBER SECTION NAME MARKS
101 A SUNIL 699
102 A ANIL 599
101 B KETHAN 489
102 B SURAJ 571

3) I want to insert record into COURSE table.

INSERT INTO COURSE VALUES(1001, ‘JAVA FS’, ‘6

MONTHS’, ’21-FEB-24’);
(or)
INSERT INTO COURSE(COURSE_ID, DURATION,
COURSE_NAME, START_DATE) VALUES(
1002, ‘5 MONTHS’, ‘PYTHON FS’, ’25-FEB-24’);
4) I want to update duration of all courses which are
starting in the month of February.
 Query-1:
UPDATE COURSE SET DURATION = ‘4 MONTHS’
WHERE START_DATE LIKE ‘%FEB%’;
Query-2:
UPDATE COURSE SET DURATION = ‘4 MONTHS’
WHERE START_DATE BETWEEN ‘1-FEB-24’ TO ’29-FEB-
24’;
Query-3:
UPDATE COURSE SET DURATION = ‘4 MONTHS’ WHERE
TO_CHAR(START_DATE, ‘MON’) = ‘FEB’;

5) I want to display the student name and section who got

the highest marks in each section.
SELECT NAME, SECTION, MAX(MARKS) AS ‘HIGHEST
MARKS’ FROM STUDENT GROUP BY SECTION;

Date: 22/02/2024

Installing Oracle Database:

1. Enterprise Edition
2. Express Edition
Enterprise Edition has more features compared to
Express Edition. Enterprise Edition occupies more
memory and impacts on your system performance.
So, for learning/practicing, you can install light weight
Software called express edition.
Visit:
https://www.oracle.com/database/technologies/xe-
downloads.html
Click on
Oracle Express Edition 21c windows x64
OracleXE213_Win64.zip file will be downloaded.
Extract the zip file, then go to the extracted folder and
Double click on setup.exe file to install the software.

Display the list of employees whose salary is more than

the salary of ALLEN

SELECT * FROM EMP WHERE SAL > (

SELECT SAL FROM EMP WHERE
ENAME = 'ALLEN'
);

Display the name of the employee who is getting

highest salary.

SELECT ENAME FROM EMP WHERE SAL IN (

SELECT MAX(SAL) FROM EMP
);

Create a table Hotel with columns hotel_id

hotel_name phone and rating
Create a table Rooms with columns room_id
room_type rent and hotel_id
 CREATE TABLE HOTEL (
HOTEL_ID NUMBER(8) PRIMARY KEY,
HOTEL_NAME VARCHAR2(20),
PHONE NUMBER(10),
RATING NUMBER(5,2)
);
CREATE TABLE ROOMS (
ROOM_ID NUMBER(8) PRIMARY KEY,
ROOM_TYPE VARCHAR2(20),
RENT NUMBER(8),
HOTEL_ID NUMBER(9) REFERENCES
HOTEL(HOTEL_ID)
);

Q) create dept table with columns deptno,dname and

loc
Q) create emp table with columns empno, ename, sal,
hiredate and deptno.
Insert some sample records into both the tables.

Introduction to JDBC:

 Data in a java program can exist in variables or objects.

 Data exist in variables or objects is a temporary data.
Which means, whenever the scope of that variable or
object is completed then we lose the data.
 So, to use the data in future, we have to store it in a
permanent memory. Which is nothing but a file or a
database.

Date : 23/02/2024
 What are the issues with files?
1. Searching for a specific data in a file will be a time
consuming task.
2. Same data can be stored multiple times in a file. So it
leads to redundant data/duplicate data. The problem
is memory space will be wasted.
3. When data is stored across multiple files then
retrieving the related information/data from the files
is a tedious task.
4. When data exist in multiple files, if the data is
modified at one file and not modified at another file
then it leads to data inconsistency.
5. The data stored in files is not secured. Because, once
a file a opened all the data of that file will be visible.
 All the above issues we can overcome with the
Databases.

What operations can be done on the data?

CRUD operations.
C – create ( creating the data / inserting the data)
R – Read ( retrieving the data/selecting the data)
U – Update ( updating the data/modifying the data)
D – Delete ( deleting the data / removing the data)
 JDBC Technology:
Technology  refers to the tools and techniques to
solve the problems.
API  Java Library or Java API both are same.
Java API refers to a collection of
pre-defined/pre-existing programs given by the
programming language to develop user-defined
applications.
Date: 24/02/2024
 JDBC Technology refers to a Java API that enables Java
applications to interact with the Databases.
 A Java Application will execute SQL queries on a
database and gets the results from the database using
JDBC API.
 Java Applications uses JDBC Drivers as intermediaries
to connect with database and to execute the queries.
 JDBC Drivers will translate the JDBC calls to Database
calls, executes queries and retrieves the result from the
database to the Java Application.
 JDBC Technology has provided 4 types of drivers.

1. JDBC-ODBC Bridge drivers

2. Native API partly Java Drivers
3. Net-protocol Pure Java Drivers
4. Native Protocol Pure Java Drivers
 In real-time type-4 drivers are used to communicate Java
Applications with Databases.
 Type-4 driver applications are written in Java only and
hence they are platform-independent.
 Type-4 drivers connect Java Applications directly with
the Databases. So they improve the performance of the
Java Applications.

 Type-4 drivers makes Java applications as database-

dependent. It means, whenever the database is changed
then we also need to change the type-4 driver in our
application.

 JDBC API consists two packages

1. java.sql
2. javax.sql
 The difference between java.sql and javax.sql is,
javax.sql contains extended/enhancement for the
features that are available java.sql package.

Date: 26/02/2024
 The Three key components of JDBC technology are,
 1. JDBC API
2. DriverManager
3. Driver
 DriverManager is a utility class from JDBC Api, which
manages the drivers which are loaded into the JVM.
 A Java program provides a database URL to the
DriverManager and based on the URL, it will pickup
appropriate driver and establishes connection with a
database.
 A Java program has to call getConnection() method of
DriverManager class to pass Database URL, username
and password.
 Driver applications/software will be installed along with
the Database software, in the form of jar files.
 Jar stands for Java Archive files.
 A jar file contains a collection of programs/classes.
 A Java programmer has to know the connection
properties to establish a connection with a database.
1. Driver class name
2. Database url
3. Username
4. Password
 For ex, to connect with Oracle Database, the connection
properties are,
Driver classname-> oracle.jdbc.driver.OracleDriver
Database url-> jdbc:oracle:thin:@<host>:1521:<listener>
jdbc:oracle:thin:@localhost:1521:XE
Note: XE is the listener for Oracle Express Edition
 ORCL is the listener for Oracle Enterprise Edition
How to find the listener if you don’t know?
Open windows Run  Type Services.exe  find
OracleServiceXE / OracleServiceORCL
Username -> system
Password -> tiger
 For ex, the connection properties to connect with
MySQL are,
Driver classname-> com.mysql.cj.jdbc.Driver
Database url -> jdbc:mysql://<host>:<port>/<database>
Jdbc:mysql://localhost:3306/test
Username -> root
Password -> root

Steps to write a JDBC program

----------------------------------------
1. Load the JDBC driver
2. Establish the connection
3. Create Statement
4. Execute SQL queries
5. Process the results
6. Close the connection
 From Java8, Loading the Jdbc Driver became optional.
 Suppose, if you want to load the driver explicitly then
You have to call static forName() method java.lang.Class.
Class.forName(“fully qualified driver classname”);
For ex:
Class.forName(“oracle.jdbc.driver.OracleDriver”);
 Establish connection:
 Call getConnection() method of DriverManager class.
 getConnection() is a static method which returns a
Connection object. So we have to store that object into
Connection variable.
Connection conn=DriverManager.getConnection(url,
username, password);
For ex,
Connection conn =
DriverManager.getConnection(“jdbc:oracle:thin:@localh
ost:1521:XE”, “system”, “tiger”);

Create Statement:
 call createStatement() method of Connection object. So
that it will create a Statement object.
Statement stmt = conn.createStatement();

Date : 27/02/2024
Note: DriverManager is a class, Connection and
Statement are the interfaces from JDBC API.
 For the interfaces provided in JDBC API, the
implementation classes exist in Driver
software/application.

Executing queries:
 For executing the queries, mostly we need to call one of
the below methods of Statement object.
1. executeUpdate(String query)
2. executeQuery(String query)
 For insert/update/delete operations, call
executeUpdate() method.
 For select operations, call executeQuery() method.
 executeUpdate() method returns an integer value, which
indicates the number of rows effected in the database.
For ex:
String query = “ DELETE FROM EMP WHERE SAL >
5000”;
int count = stmt.executeUpdate(query);
 When you run a select query, from database the
program receives the records. These records must be
stored into a ResultSet object.
 ResultSet is an interface, and its implementation is exist
in driver software/application.
For ex,
String query = “ SELECT * FROM EMP WHERE DEPTNO =
20 “;
ResultSet rs = stmt.executeQuery( query );

Close the connection:

stmt.close();
conn.close();

example program
 Open a notepad and type the following.
// This program is to insert one record into the table
EMP

import java.sql.*;

class Insert_Emp {

public static void main( String... args ) throws

Exception {

//step-1: load the jdbc driver (optional)

Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Driver is loaded......");

//step-2: establish connection

String databaseURL =
"jdbc:oracle:thin:@localhost:1521:XE";
String username = "system";
String password = "tiger";
Connection conn =
DriverManager.getConnection(databaseURL, username,
password);
System.out.println("connection is
established.......");

//step-3: create Statement object

Statement stmt = conn.createStatement();
System.out.println("Staement object is
created..........");
 // step-4: execute the sql query
String query = "INSERT INTO EMP VALUES( 7818,
'JACK', 5999.0, '29-MAR-17', 30)";
int count = stmt.executeUpdate(query);
System.out.println("insert query is executed ");

//step-5: process the result

System.out.println( count + " row inserted...........");

//step-6: close the connection

stmt.close();
conn.close();
System.out.println("connection is closed..........");
}
}
 Save the code to D: drive with file name
Insert_Emp.java
 Open command prompt, then compile
D:\> javac Insert_Emp.java
 Set the classpath
 D:\> set classpath=C:\app\Admin\product\21c\
dbhomeXE\jdbc\lib\ojdbc8.jar;.
 D:\> java Insert_Emp
 o/p:
Driver is loaded….
Connection is established……
Statement object is created…..
Insert query is executed…….
1 row inserted……
Connection is closed……