JDBC Drivers

 

JDBC Drivers

 

 

JDBC stands for Java Database Connectivity. It is a Java API that lets a Java application connect to a database, run SQL queries, and read or modify data in a database.

 

Interfaces and Classes of JDBC API

• DriverManager class − used to load a SQL driver to connect to database.
• Connection interface − used to make a connection to the database using database connection string and credentials.
• Statement interface − used to make a query to the database.
• PreparedStatement interface − used for a query with placeholder values.
• CallableStatement interface − used to called stored procedure or functions in database.
• ResultSet interface − represents the query results obtained from the database.
• ResultSetMetaData interface − represents the metadata of the result set.
• BLOB class − represents binary data stored in BLOB format in database table.
• CLOB class − represents text data like XML stored in database table

 

JDBC Drivers

The JDBC driver acts as a translator between Java/JDBC commands and the database's own communication protocol. JDBC drivers implement the defined interfaces in the JDBC API, for interacting with the database server.

 

Types of JDBC Drivers

There are 4 types of JDBC drivers:

Type	Name	Working
Type 1	JDBC-ODBC Bridge Driver	JDBC → ODBC → Database
Type 2	Native-API Driver	JDBC → Native Database API → Database
Type 3	Network Protocol Driver	JDBC → Middleware Server → Database
Type 4	Thin / Pure Java Driver	JDBC → Database directly

 

 

Type 1 – JDBC-ODBC Bridge Driver

The Type 1 JDBC driver acts as a bridge between JDBC and ODBC. It converts JDBC calls made by a Java program into ODBC calls, which are then sent to the database. Thus, the communication takes place through JDBC → ODBC → Database. It is an older type of driver and is no longer used in modern Java applications.

 

 

Type 2 – Native-API Driver

The Type 2 JDBC driver converts JDBC calls into the native API calls of a particular database. It uses database-specific native libraries installed on the system to communicate with the database. Thus, the communication takes place through JDBC → Native API → Database. The driver contains both Java code and native code.

 

 

Type 3 – Network Protocol Driver

The Type 3 JDBC driver uses a middleware server to communicate with the database. The Java application sends JDBC calls to the middleware server, which converts them into the appropriate database-specific requests. Thus, the communication takes place through JDBC → Middleware Server → Database. The middleware acts as an intermediate layer between the Java application and the database.

 

 

Type 4 – Thin / Pure Java Driver

The Type 4 JDBC driver is a pure Java driver that communicates directly with the database using its network protocol. It converts JDBC calls into the database-specific protocol without requiring an intermediate middleware server. Thus, the communication takes place through JDBC → Type 4 Driver → Database. MySQL Connector/J is an example of a Type 4 JDBC driver.

 

JDBC API

JDBC API

 

JDBC API (Java Database Connectivity API) is a collection of classes and interfaces provided by Java that allows a Java program to communicate with databases.

The JDBC API provides standard methods for connecting to a database, executing SQL statements, retrieving results, and handling database errors. It allows Java programs to work with different databases without changing the basic JDBC programming approach.

The main JDBC API components are:

Component	Purpose
DriverManager	Establishes a connection with the database
Connection	Represents the connection between Java and the database
Statement	Executes SQL statements
PreparedStatement	Executes parameterized SQL statements
ResultSet	Represents data returned by a SELECT query
SQLException	Represents database-related errors

 

1.   DriverManager

DriverManager is a class in the java.sql package that manages JDBC drivers. It is mainly used to establish a connection between a Java application and a database. The getConnection() method is used with the database URL, username, and password. It identifies a suitable JDBC driver and returns a Connection object.

 

2.   Connection

Connection is an interface that represents an active connection between a Java application and a database. It is obtained using the DriverManager.getConnection() method. The Connection object is used to create Statement and PreparedStatement objects for executing SQL commands. After completing the database operations, the connection should be closed.

 

3.   Statement

Statement is an interface used to execute SQL statements from a Java program. A Statement object is created using the Connection object. Methods such as executeQuery() and executeUpdate() are used to execute SQL commands. It is commonly used when the SQL statement does not require dynamic input values.

 

4.   PreparedStatement

PreparedStatement is an interface used to execute parameterized SQL statements. It allows placeholders (?) to be used in an SQL statement and values to be supplied separately. Methods such as setInt() and setString() are used to provide the values. It is commonly used when SQL statements need to be executed with different input values.

 

5.   ResultSet

ResultSet is an interface that represents the data returned by a SELECT query. It contains the rows and columns retrieved from the database. The next() method is used to move through the rows one at a time. Methods such as getInt() and getString() are used to retrieve column values.

 

6.   SQLException

SQLException is an exception class used to represent errors that occur during database operations. It can occur while connecting to the database, executing SQL statements, or retrieving results. JDBC programs generally handle it using try-catch or throws. The exception provides information about the database error.

 

  

AWT Controls - TextField

 //awt controls demo

import java.io.*;

import java.awt.*;

import java.awt.event.*;

class Awtcontrols_Demo extends Frame implements TextListener, ActionListener

{

Label L1, L2, L3;

Button b1, b2;

TextField tf1;

FlowLayout FL;

Awtcontrols_Demo()

{

L1 = new Label("Enter Name : ");

L2 = new Label();

L3 = new Label();

L2.setPreferredSize(new Dimension(200,50));

L3.setPreferredSize(new Dimension(200,50));

b1 = new Button("Submit");

b2 = new Button("Clear");

b1.setPreferredSize(new Dimension(50,20));

b2.setPreferredSize(new Dimension(50,20));

tf1 = new TextField();

tf1.setSize(100,50);

tf1.setText("Guest");

FL = new FlowLayout();

add(L1);

add(tf1);

add(L2);

add(L3);

add(b1);

add(b2);

tf1.addTextListener(this);

b1.addActionListener(this);

b2.addActionListener(this);

setLayout(FL);

setSize(300,200);

setTitle("AWT Controls Demo");

setVisible(true);

addWindowListener(new WindowAdapter(){

public void windowClosing(WindowEvent e)

{

System.exit(0);

}

});

}

public void textValueChanged(TextEvent te)

{

L2.setText(tf1.getText());

}

public void actionPerformed(ActionEvent ae)

{

if(ae.getSource()==b1)

{

L3.setText("Welcome "+tf1.getText());

}

else if(ae.getSource() == b2)

{

tf1.setText("");

L3.setText("");

}

}

public static void main(String as[])

{

new Awtcontrols_Demo();

}

}

Sample Output:

On submit

On clear

Entering text in TextField and on Submit

AWT Controls - List

 // awt controls demo

// List

import java.io.*;

import java.awt.*;

import java.awt.event.*;

class Awtcontrols_Demo extends Frame implements ItemListener

{

Label L1, L2;

List Dept, Yr;

FlowLayout FL;

Awtcontrols_Demo()

{

Dept = new List(4, true); // Multiselection

Yr = new List(4); // Single selection

Dept.add("CSE");

Dept.add("IT");

Dept.add("Cs-DS");

Dept.add("EEE");

Dept.add("ECE");

Dept.add("Mech");

Dept.add("Auto");

Dept.add("Civil");

Yr.add("1");

Yr.add("2");

Yr.add("3");

Yr.add("4");

L1 = new Label("List demo");

L1.setPreferredSize(new Dimension(275,50));

L2 = new Label();

L2.setPreferredSize(new Dimension(200,50));

FL = new FlowLayout();

add(Dept);

add(Yr);

add(L1);

Dept.addItemListener(this);

Yr.addItemListener(this);

setLayout(FL);

setSize(300,200);

setTitle("AWT Controls Demo");

setVisible(true);

addWindowListener(new WindowAdapter(){

public void windowClosing(WindowEvent e)

{

System.exit(0);

}

});

}

public void itemStateChanged(ItemEvent ie)

{

String s[] = Dept.getSelectedItems();

String str = "Departments selected : "; 

for(int i=0;i<s.length;i++)

str=str+" "+s[i];

L1.setText(str+ ".    Year : "+Yr.getSelectedItem());

}

public static void main(String as[])

{

new Awtcontrols_Demo();

}

}

Sample Output:

AWT Controls - Checkbox

 // awt controls demo

// Checkbox

import java.io.*;

import java.awt.*;

import java.awt.event.*;

class Awtcontrols_Demo extends Frame implements ItemListener

{

Label L1;

Checkbox cb1, cb2, cb3;

FlowLayout FL;

Awtcontrols_Demo()

{

cb1 = new Checkbox("CSE", false);

cb2 = new Checkbox("Cs-DS", false);

cb3 = new Checkbox("IT", false);

L1 = new Label("Checkbox demo");

L1.setPreferredSize(new Dimension(200,50));

FL = new FlowLayout();

add(cb1);

add(cb2);

add(cb3);

add(L1);

cb1.addItemListener(this);

cb2.addItemListener(this);

cb3.addItemListener(this);

setLayout(FL);

setSize(300,200);

setTitle("AWT Controls Demo");

setVisible(true);

addWindowListener(new WindowAdapter(){

public void windowClosing(WindowEvent e)

{

System.exit(0);

}

});

}

public void itemStateChanged(ItemEvent ie)

{

String msg="Selected : ";

if(cb1.getState()==true)

msg+=cb1.getLabel();

if(cb2.getState()==true)

msg+=" Cs-DS ";

if(cb3.getState()==true)

msg+=" IT ";

L1.setText(msg);

}

public static void main(String as[])

{

new Awtcontrols_Demo();

}

}

Sample output:

AWT Controls - CheckboxGroup

 // awt controls demo

// CheckboxGroup

import java.io.*;

import java.awt.*;

import java.awt.event.*;

class Awtcontrols_Demo extends Frame implements ItemListener

{

Label L1;

Checkbox cb1, cb2, cb3;

FlowLayout FL;

CheckboxGroup cbgroup;

Awtcontrols_Demo()

{

cbgroup = new CheckboxGroup();

cb1 = new Checkbox("CSE",cbgroup, false);

cb2 = new Checkbox("Cs-DS", cbgroup, false);

cb3 = new Checkbox("IT", cbgroup, false);

L1 = new Label("Checkbox demo");

L1.setPreferredSize(new Dimension(200,50));

FL = new FlowLayout();

add(cb1);

add(cb2);

add(cb3);

add(L1);

cb1.addItemListener(this);

cb2.addItemListener(this);

cb3.addItemListener(this);

setLayout(FL);

setSize(300,200);

setTitle("AWT Controls Demo");

setVisible(true);

addWindowListener(new WindowAdapter(){

public void windowClosing(WindowEvent e)

{

System.exit(0);

}

});

}

public void itemStateChanged(ItemEvent ie)

{

if(cb1.getState()==true)

L1.setText(" CSE Selected ");

if(cb2.getState()==true)

L1.setText(" Cs-DS Selected ");

if(cb3.getState()==true)

L1.setText(" IT Selected ");

}

public static void main(String as[])

{

new Awtcontrols_Demo();

}

}

Sample Output:

AWT Controls - Scrollbar

 // Scrollbar 

import java.awt.*;

import java.awt.event.*;

class Awtcontrols_Demo extends Frame implements AdjustmentListener

{

    Scrollbar sbv, sbh;

    Label L1, L2;

FlowLayout FL;

    Awtcontrols_Demo()

    {

        // Vertical Scrollbar

        sbv = new Scrollbar(

            Scrollbar.VERTICAL,

            0,      // initial position

            2,      // visible amount

            0,      // minimum

            20      // maximum

        );

sbh = new Scrollbar(Scrollbar.HORIZONTAL, 0, 2, 0,25);

sbv.setPreferredSize(new Dimension(10, 200));

sbh.setPreferredSize(new Dimension(200, 10));

        // Label to display position

        L1 = new Label();

L1.setPreferredSize(new Dimension(150,25));

L2 = new Label();

L2.setPreferredSize(new Dimension(150,25));

FL = new FlowLayout();

        add(L1);

add(sbv);

add(L2);

add(sbh);

        // Register AdjustmentListener

        sbv.addAdjustmentListener(this);

sbh.addAdjustmentListener(this);

        setLayout(FL);

        setTitle("Scrollbar Demo");

        setSize(300, 325);

        setVisible(true);

        // Window closing event

        addWindowListener(new WindowAdapter()

        {

            public void windowClosing(WindowEvent e)

            {

                System.exit(0);

            }

        });

    }

    // Handle scrollbar movement

    public void adjustmentValueChanged(AdjustmentEvent ae)

    {

        int positionv = sbv.getValue();

int positionh = sbh.getValue();

        L2.setText("H SB - "+positionh);

L1.setText("V SB - "+positionv);

    }

    public static void main(String args[])

    {

        new Awtcontrols_Demo();

    }

}

Sample Output:

Capture and Analysis of Network Packets Using Wireshark

 

Capture and Analysis of Network Packets Using Wireshark

 

Aim

To capture network packets using Wireshark and analyze the captured packets to study communication using TCP

 

Steps

 

 

1. Install TShark

sudo apt update

sudo apt install -y tshark wireshark-common

2. Verify TShark

tshark –version

3. Test TShark

sudo tshark -D

4. Start the TCP packet capture

sudo tshark -i eth0 -w /tmp/TCP_Analysis.pcapng

5. Stop packet capture

Ctrl + C

6. Check the capture file

ls -lh /tmp/ TCPPkts.pcapng

7. Change File permissions

sudo chmod 644 /tmp/ TCPPkts.pcapng

8. Analysing the file for TCP Three way handshake, Piggybacking and Connection Termination

tshark -r /tmp/TCPPkts.pcapng -Y "tcp.stream==21"

9. Execute python script to determine Packet statistics
1. Source and Destination IP address
2. Protocols
3. Plot Time Vs packets/Sec

// Python script to analyze the pcap file

 

import subprocess

import pandas as pd

import matplotlib.pyplot as plt

from io import StringIO

 

# PCAP FILE

pcap_file = "/tmp/TCPPkts.pcapng"

 

# EXTRACT PACKET INFORMATION USING TSHARK

cmd = [

    "tshark",

    "-r", pcap_file,

    "-T", "fields",

 

    "-E", "header=y",

    "-E", "separator=,",

    "-E", "quote=d",

 

    "-e", "frame.time_epoch",

    "-e", "ip.src",

    "-e", "ip.dst",

    "-e", "frame.protocols"

]

 

result = subprocess.run(

    cmd,

    capture_output=True,

    text=True

)

 

# CHECK TSHARK ERROR

if result.returncode != 0:

 

    print("\nTShark Error:")

    print(result.stderr)

    exit()

 

# READ TSHARK OUTPUT

df = pd.read_csv(

    StringIO(result.stdout)

)

 

# Remove empty timestamp rows

df = df.dropna(

    subset=["frame.time_epoch"]

)

 

# Convert timestamp

df["frame.time_epoch"] = pd.to_numeric(

    df["frame.time_epoch"],

    errors="coerce"

)

 

# Remove invalid timestamps

df = df.dropna(

    subset=["frame.time_epoch"]

)

 

# IP ADDRESS STATISTICS

print("\n")

print("IP ADDRESS STATISTICS")

print("\nSOURCE IP ADDRESSES")

print(

    df["ip.src"]

    .dropna()

    .value_counts()

)

 

print("\nDESTINATION IP ADDRESSES")

 

print(

    df["ip.dst"]

    .dropna()

    .value_counts()

)

 

# PROTOCOL STATISTICS

print("\n")

print("PROTOCOL STATISTICS")

 

protocols = (

    df["frame.protocols"]

    .dropna()

    .str.split(":")

    .explode()

    .value_counts()

)

print(protocols)

 

# TIME VS PACKETS / 5 SECONDS

 

# Relative time from first packet

df["relative_time"] = (

    df["frame.time_epoch"]

    - df["frame.time_epoch"].iloc[0]

)

 

# Group packets into 5-second intervals

df["time_sec"] = (

    (df["relative_time"] // 5) * 5

).astype(int)

 

# Count packets in every 5-second interval

packets_per_5sec = (

    df.groupby("time_sec")

    .size()

)

 

# PLOT

 

plt.figure(figsize=(10, 6))

 

plt.plot(

    packets_per_5sec.index,

    packets_per_5sec.values,

    marker="o"

)

 

plt.xlabel("Time (seconds)")

plt.ylabel("Packets / 5 Seconds")

 

plt.title(

    "TCP Packet Analysis: Time Vs Packets per 5 Seconds"

)

 

plt.grid(True)

plt.tight_layout()

 

opt_file = "/home/drranurekha/Time_vs_Packets.png"

 

plt.savefig(

    opt_file,

    dpi=300,

    bbox_inches="tight"

)

 

print("\nGraph saved:")

print(opt_file)

 

Study of TCP/UDP Performance Using NS-2

 

Study of TCP/UDP Performance Using NS-2

 

Aim: To study and compare the performance of TCP and UDP using the NS-2 simulation tool. (Packet transmission, Packet loss, Throughput and Packet Delivery Ratio (PDR))

 

Simulation setup

Parameter	TCP	UDP
Number of application packets	1000	1000
Packet size	1000 bytes	1000 bytes
Total application data	1 MB	1 MB
Bottleneck bandwidth	1 Mbps	1 Mbps

 

Steps to execute

1.    Install NS-2 in Google Colab

a.   Open Google Colab and create a new notebook

b.   Check the Linux environment

o   !uname -a

c.   Update the package list

o   !apt-get update -qq

d.   Install NS-2

o   !apt-get install -y ns2

e.   Check the NS-2 installation and verify

o   !which ns

2.   TCP UDP Performance analysis.

a.   Create the TCL file

b.   Run the TCL program

c.   Check the generated files

d.   Analyse the tracefile and plot the result

 

 

TCP UDP Performance analysis

 

Create the tcl file

%%writefile tcp_udp.tcl

 

# Create simulator

set ns [new Simulator]

 

# Create trace file

set tracefile [open tcp_udp.tr w]

$ns trace-all $tracefile

 

# Create nodes

set n0 [$ns node]       ;# TCP source

set n1 [$ns node]       ;# UDP source

set n2 [$ns node]       ;# Router

set n3 [$ns node]       ;# Router

set n4 [$ns node]       ;# Destination

 

# Create links

$ns duplex-link $n0 $n2 10Mb 10ms DropTail

$ns duplex-link $n1 $n2 10Mb 10ms DropTail

 

# Bottleneck link

$ns duplex-link $n2 $n3 1Mb 20ms DropTail

$ns duplex-link $n3 $n4 10Mb 10ms DropTail

 

# Queue limit

$ns queue-limit $n2 $n3 10

 

# TCP CONNECTION

# TCP Reno agent

set tcp [new Agent/TCP/Reno]

$tcp set packetSize_ 1000

 

# Attach TCP to n0

$ns attach-agent $n0 $tcp

 

# TCP receiver

set tcpsink [new Agent/TCPSink]

$ns attach-agent $n4 $tcpsink

$ns connect $tcp $tcpsink

 

# TCP traffic

set tcp_app [new Application/Traffic/CBR]

$tcp_app set packetSize_ 1000

$tcp_app set interval_ 0.01

$tcp_app set random_ false

 

$tcp_app attach-agent $tcp

 

# UDP CONNECTION

# UDP agent

set udp [new Agent/UDP]

$udp set packetSize_ 1000

$ns attach-agent $n1 $udp

 

# UDP receiver

set null [new Agent/Null]

$ns attach-agent $n4 $null

$ns connect $udp $null

 

# UDP traffic

set udp_app [new Application/Traffic/CBR]

$udp_app set packetSize_ 1000

$udp_app set interval_ 0.01

$udp_app set random_ false

 

$udp_app attach-agent $udp

 

# START TRANSMISSION

$ns at 0.5 "$tcp_app start"

$ns at 0.5 "$udp_app start"

 

# STOP TRANSMISSION

$ns at 10.5 "$tcp_app stop"

$ns at 10.5 "$udp_app stop"

 

# END SIMULATION

$ns at 11.0 "finish"

 

# Finish procedure

proc finish {} {

 

    global ns tracefile

 

    $ns flush-trace

 

    close $tracefile

 

    exit 0

}

 

# Run simulation

 

$ns run

 

Run the TCL program

!ns tcp_udp.tcl

 

Check the generated files

!ls -lh tcp_udp*

 

Analyze the trace file and plot the result

# TCP vs UDP Performance Analysis

 

TCP_GENERATED = 1000

UDP_GENERATED = 1000

 

tcp_received = 0

udp_received = 0

 

tcp_bytes = 0

udp_bytes = 0

 

tcp_dropped = 0

udp_dropped = 0

 

with open("tcp_udp.tr", "r") as f:

 

    for line in f:

 

        fields = line.split()

 

        if len(fields) < 6:

            continue

 

        event = fields[0]

        from_node = fields[2]

        to_node = fields[3]

        packet_type = fields[4]

 

        try:

            packet_size = int(fields[5])

        except:

            continue

 

        # TCP received at destination n4

        if (event == "r" and

            to_node == "4" and

            packet_type == "tcp"):

 

            tcp_received += 1

            tcp_bytes += packet_size

 

        # UDP received at destination n4

        if (event == "r" and

            to_node == "4" and

            packet_type == "cbr"):

 

            udp_received += 1

            udp_bytes += packet_size

 

        # Dropped packets

        if event == "d":

 

            if packet_type == "tcp":

                tcp_dropped += 1

 

            elif packet_type == "cbr":

                udp_dropped += 1

 

# Packet Loss

tcp_loss = TCP_GENERATED - tcp_received

udp_loss = UDP_GENERATED - udp_received

 

# PDR

tcp_pdr = (tcp_received / TCP_GENERATED) * 100

udp_pdr = (udp_received / UDP_GENERATED) * 100

 

# Throughput

duration = 10.0

 

tcp_throughput = (tcp_bytes * 8/(duration * 1000000))

udp_throughput = (udp_bytes * 8/(duration * 1000000))

 

# Display Results

print("             TCP vs UDP PERFORMANCE")

print(f"{'Metric':<25}{'TCP':<15}{'UDP':<15}")

 

print(f"{'Packets Generated':<25}"

      f"{TCP_GENERATED:<15}"

      f"{UDP_GENERATED:<15}")

 

 

print(f"{'Packets Received':<25}"

      f"{tcp_received:<15}"

      f"{udp_received:<15}")

 

print(f"{'Packet Loss':<25}"

      f"{tcp_loss:<15}"

      f"{udp_loss:<15}")

 

print(f"{'Packets Dropped':<25}"

      f"{tcp_dropped:<15}"

      f"{udp_dropped:<15}")

 

print(f"{'PDR (%)':<25}"

      f"{tcp_pdr:<15.2f}"

      f"{udp_pdr:<15.2f}")

 

print(f"{'Throughput (Mbps)':<25}"

      f"{tcp_throughput:<15.4f}"

      f"{udp_throughput:<15.4f}")

 

 

Packet Loss

import matplotlib.pyplot as plt

 

protocols = ["TCP", "UDP"]

loss = [tcp_loss, udp_loss]

 

plt.figure(figsize=(7,5))

plt.bar(protocols, loss)

 

plt.xlabel("Protocol")

plt.ylabel("Number of Packets")

plt.title("TCP vs UDP Packet Loss")

 

plt.show()

 

Throughput

throughput = [tcp_throughput, udp_throughput]

 

plt.figure(figsize=(7,5))

plt.bar(protocols, throughput)

 

plt.xlabel("Protocol")

plt.ylabel("Throughput (Mbps)")

plt.title("TCP vs UDP Throughput")

 

plt.show()

 

 

Packet Delivery Ratio

pdr = [tcp_pdr, udp_pdr]

 

plt.figure(figsize=(7,5))

plt.bar(protocols, pdr)

 

plt.xlabel("Protocol")

plt.ylabel("Packet Delivery Ratio (%)")

plt.title("TCP vs UDP Packet Delivery Ratio")

 

plt.ylim(0, 100)

plt.show()

 

 

Result: The performance of TCP and UDP was successfully studied using NS-2 simulation. TCP and UDP traffic were generated through a common bottleneck link, and their performance was analyzed using packet delivery, packet loss and throughput measurements.