Unlocking Database Power: A Comprehensive Guide to JDBC in Java by Pallavi Gaikwad

+91-9764326834

Unlocking Database Power: A Comprehensive Guide to JDBC in Java

Pallavi Gaikwad

Posted on 13th Apr 2024 12:10 PM | 10 min Read | 60 min Implementation

##Drivers ##java ##JDBC ##Database ##connection ##MySQL

In this blog, we’re going to take a look at JDBC (Java Database Connectivity) which is an API for connecting and executing queries on a database.

Understanding the JDBC:

JDBC serves as a bridge between Java applications and databases, allowing seamless communication. It provides a standardized interface for Java developers to interact with various database management systems (DBMS) like MySQL, PostgreSQL, Oracle, and more.

JDBC (Java Database Connectivity) is a java API for connecting and interacting with database.

JDBC Drivers are software components that provide necessary functionality to connect java applications to connect different types of Databases.

when we are using JDBC there are several key Components like below and we briefly know about the component:

1.DriverManager: DriverManager is a Class .It manages a list of Database drivers. It helps establish a connection with the appropriate driver based on the database URL provided.

2.Connection: Connection is an Interface. It represents a connection with a specific database. It allows executing SQL statements and managing transactions.

3.Statements: The Statements is an Interface. It perform run or execute SQL queries against the database. There are different types of statements available, such as Statement, PreparedStatement, and CallableStatement, catering to various needs.

4.ResultSet: The ResultSet is an Interface. It represents the result set of a database query.

Basic Program flow or Workflow of JDBC:

Note: Make sure in your Machine installed one database like PostgreSQL, MySQL etc..

Here we know about ...

Note: Connection strings are used to establish connections to Databases. without connection string you can't connect to your database.

CONNECTION STRING AND IT'S TYPE:

The connection string is used to connect from the java application to the specified database server. We need database hostname, database name, database username, database user password, and database port number to connect the database server using the connection string. We need a database driver to connect the specified database.

Type 1: Simple Connection String: This is a basic connection string format that typically includes the database URL, username, and password.

Syntax: jdbc:mysql://localhost:3306/mydatabase?user=username&password=password

Example:

private static final String url="jdbc:mysql://localhost:3306/?user=root";

private static final String username="root";

private static final String password="xyz";

Type 2: Connection String with Properties: Some databases allow additional properties to be specified in the connection string, such as connection timeout, SSL settings, etc. These properties are appended to the URL.

Syntax: "jdbc:mysql://localhost:3306/mydatabase?user=username&password=password&autoReconnect=true&connectTimeout=3000"

Example:

// JDBC URL for MySQL database

String url = "jdbc:mysql://localhost:3306/?user=root?user=root&password=password&autoReconnect=true&connectTimeout=3000";

Type 3: Connection String with connection Pooling: The connection string might include parameters related to connection pooling configuration, such as maximum pool size, idle timeout, etc.

Syntax: "jdbc:mysql://localhost:3306/mydatabase?user=username&password=password&maxTotal=50&maxIdle=10

Example:

// Database URL

String url = "jdbc:mysql://localhost:3306/root?user=username&password=password&maxTotal=50&maxIdle=10";

// Database credentials

String username = "your_username";

String password = "your_password";

// Establishing the connection

Type 4: URL less connection String: Some JDBC drivers allow connections to be established without specifying a URL. Instead, you provide the necessary connection details programmatically.

Syntax: DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");

Example:

String url = "jdbc:mysql://localhost:3306/mydatabase";

String username = "your_username";

String password = "your_password";

Type 5: URL less connection String: Instead of directly specifying the connection details in the URL, you can use a DataSource object to manage connections. In this case, the connection string might just specify the JNDI (Java Naming and Directory Interface) name of the DataSource.

Syntax: "jdbc:oracle:thin:@(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data=(service_name=myorcldbservicename)))";

Example:

// JDBC URL

String url = "jdbc:oracle:thin:@(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data= (service_name=myorcldbservicename)))";

// Database credentials

String username = "your_username";

String password = "your_password";

" When we Establish a Connection the code remain same for all connection string:"

// Establishing the connection

try {

Connection connection = DriverManager.getConnection(url, username, password);

if (connection != null) {

System.out.println("Connected to the database!");

// Do your database operations here

// Remember to close the connection when done

connection.close();

} else {

System.out.println("Failed to make connection!");

}

} catch (SQLException e) {

System.err.println("Error connecting to the database!");

e.printStackTrace();

}

DRIVERMANAGER AND IT'S USE:

The DriverManager is a class in Java is part of the JDBC (Java Database Connectivity) API.It manages a list of database drivers. The DriverManager class helps in loading the appropriate driver when a connection to a database is requested.

some important methods in the DriverManager class:

Connection connection = DriverManager.getConnection(url, user, password);

1.DriverManager. You typically don't need to call this method explicitly, as the drivers are automatically registered when their class is loaded.

2.getConnection(String url, String user, String password): This method attempts to establish a connection to the database specified by the given URL. It returns a Connection object representing the connection to the database.

3.getDrivers(): This method returns an Enumeration of all the currently loaded JDBC drivers.

Note: Above Establish connection is the example of DriverManager.

STATEMENTS:

A statement is an interface used to execute SQL queries against a database.

There are three main types of statements in JDBC:

1.Statement:

A) Statement interface is typically used to execute simple SQL queries.

B) It's created by calling createStatement() method on a Connection object.

C) It executes SQL queries using executeQuery() method for SELECT statements,

and executeUpdate() method for INSERT, UPDATE,DELETE.

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery("SELECT * FROM TableName");

2.PreparedStatement:

A) PreparedStatement interface is used when you need to execute the same SQL statement multiple times with different parameter values.

B) It's created by calling prepareStatement() method on a Connection object with a parameterized SQL query.

C) It's precompiled, which makes it more efficient for repeated executions.

PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO employees(name, age) VALUES(?, ?)");

preparedStatement.setString(1, "John");

preparedStatement.setInt(2, 30);

int rowsAffected = preparedStatement.executeUpdate();

3.CallableStatement:

A) CallableStatement interface is used to execute stored procedures, functions, or anonymous PL/SQL blocks.

B) It's created by calling prepareCall() method on a Connection object with a callable SQL statement.

C) It allows passing input and output parameters to the stored procedure or function.

CallableStatement callableStatement = connection.prepareCall("{call getEmployeeDetails(?, ?)}");

callableStatement.setInt(1, employeeId);

callableStatement.registerOutParameter(2, Types.VARCHAR);

callableStatement.execute();

String employeeName = callableStatement.getString(2);

RESULTSET :

In JDBC (Java Database Connectivity), the ResultSet interface represents the result set of a database query.

A) Retrieving Data: The primary purpose of ResultSet is to provide access to the rows of data returned by a query. You can retrieve data from the result set using methods like getInt, getString, getDouble, etc., corresponding to the data types of the columns in the result set.

B) Traversing Rows: ResultSet maintains a cursor that points to the current row of data. You can move the cursor forward using methods like next(), previous(), first(), last(), absolute(int row), relative(int rows), etc., to navigate through the rows of the result set.

C) Retrieving Metadata: ResultSet provides methods to retrieve metadata about the columns in the result set, such as the column names, data types, and properties.

D) Updating Data: Depending on the type of ResultSet (either TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE, or TYPE_FORWARD_ONLY), you may or may not be able to update the data in the result set and reflect those changes back to the database.

try (Connection connection = DriverManager.getConnection(url, username, password)) {

String sql = "SELECT id, name, age FROM Table";

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(sql)

// Iterate over the result set and retrieve data

while (resultSet.next()) {

int id = resultSet.getInt("id");

String name = resultSet.getString("name");

int age = resultSet.getInt("age");

System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);

}

} catch (SQLException e) {

System.out.println(e.getMessage());

}

NOTE: when we retrive data from table then that time we use ResultSet.

Git Repository: https://github.com/PallaviGajananGaikwad/JDBCDemo.git

Exploring Random Numbers in Java

Canary Trap - information security and detecting leaks

Manage Competing Work Demand Prioritization Framework - MoSCoW and Eisenhower

Returning Multiple Values from Functions: A Comparison of Java, Python, and GoLang

GoLang, Go Fast - Digitalizing Faster World

Emulators and Simulators

Introduction of Keys in DBMS

A Step-by-Step Guide to Creating Service Accounts in Google APIs

Reading files effectively in Java

Implement Logger in your java framework

Baking Up Quality: The Test Pyramid for Software Success

Selenium 4 Architecture

Git Command - best cheat sheet

Reading Excel files in Java

Understanding Java's Substring Method in Java

Read Data From Google-Sheet

@DataProvider and @Factory Annotation in TestNG

𝗦𝗲𝘁𝘂𝗽 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗖𝗿𝗲𝗱𝗲𝗻𝘁𝗶𝗮𝗹𝘀 𝗢𝗔𝘂𝘁𝗵 𝟮.𝟬 𝗶𝗻 𝗼𝗿𝗱𝗲𝗿 𝘁𝗼 𝗮𝗰𝗰𝗲𝘀𝘀 𝗚𝗼𝗼𝗴𝗹𝗲 𝗦𝗵𝗲𝗲𝘁𝘀 𝗔𝗣𝗜𝘀

"Exploratory Testing" is like preparing a new recipe without strict guidelines:

XPath Experts

All Comments ()

Do You want to add Comment in this Blog? Please Login ?

Course

Services