Table of contents
1.
Introduction
2.
What is JDBC?
3.
Types of Statements in JDBC
3.1.
1. Create a statement
3.2.
2. Prepared statement
3.2.1.
Implementation
3.3.
3. Callable statement
4.
Frequently Asked Questions
4.1.
What type of statements does JDBC support for executing data manipulation operations?
4.2.
What protocol does JDBC use?
4.3.
Which JDBC statement type is most efficient for repeated queries with changing parameters?
4.4.
What is the default port for JDBC?
5.
Conclusion
Last Updated: Dec 2, 2024

Types of Statements in JDBC

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Java Database Connectivity is an application programming interface(API) for Java. This helps in connecting and executing a query with the database. Thus, it helps us in defining how a client accesses the database. This can be classified as a Java-based data access technology for connecting to a Database

When using JDBC, we will come across several other statements that define how we will be accessing the database and up to which level of access. 

In this blog, we will learn about the different types of statements in JDBC and how we can use them as an API to the database.

What is JDBC?

JDBC is used to create SQL statements in Java by providing methods to execute queries with the database. Thus, JDBC works as an API for Java-Based Programs. JDBC also provides universal data access from the Java programming language. These interfaces also define the methods that are generally known to assist the bridge data type differences that exist between Java and SQL data types used in a database.

Types of Statements in JDBC

Now that we know about JDBC, let's learn about the different types of statements that constitute the JDBC queries and enable them to connect to the database and use it.

There are basically three types of statements in JDBC: 

  1. Create statements
  2. Prepared statements
  3. Callable statements

1. Create a statement

This is usually used when we want a general-purpose access to the database and can be very helpful when running static SQL statements in the runtime.

Syntax

Statement statement = connection.createStatement();
You can also try this code with Online Java Compiler
Run Code

Implementation

Once an object is created it can be executed using three ways:

  • boolean execute(String SQL): This is used to execute SQL DDL statements or for dynamic SQL statements.
  • int executeUpdate(String SQL): This is used to execute INSERT, DELETE, UPDATE i.e. DML statements.
  • ResultSet executeQuery(String SQL): This is used to execute SELECT statements in SQL.

Example

Statement statement = con.createStatement();
String sql = "select * from orders";
ResultSet result = statement.executeQuery(sql);
You can also try this code with Online Java Compiler
Run Code

2. Prepared statement

These types of statements basically represent recompiled SQL statements that can be executed many times. Thus, instead of having actual parameters in the SQL Queries, it has “?” in place of the parameters to be passed dynamically by using the methods of prepared statements at the run time.

Syntax

String query = "INSERT INTO ORDERS(id, product) VALUES(?,?)";
Statement test = connection.createStatement();
test.setInt(1, 45);
test.setString(2, "Pen");
You can also try this code with Online Java Compiler
Run Code

Implementation

Once an object is created it can be executed using three ways:

  • boolean execute(String SQL): This is used to execute the static SQL statement present in the prepared statement.
  • int executeUpdate(String SQL): This is used to execute INSERT, DELETE, UPDATE i.e. DML statements.
  • ResultSet executeQuery(String SQL): This is used to execute SELECT statements in SQL.

Example

PreparedStatement test = con.prepareStatement("
SELECT product FROM ORDERS WHERE ID = ?");
test.setInt(1,45);
ResultSet result = test.executeQuery();
You can also try this code with Online Java Compiler
Run Code

3. Callable statement

In some cases, we need to work with multiple databases for some task and the scenario becomes very complex here. Thus, rather than sending multiple queries,s we can simply send the required data to the stored procedure. This will decrease the load on the database significantly. This is where callable statements work as the stored procedures which are essentially a group of statements that we compile in the database and are very beneficial.

Syntax

CallableStatement cs = con.prepareCall("{call procedure_name(?,?)}")
You can also try this code with Online Java Compiler
Run Code

Implementation

Once an object is created it can be executed using three ways:

  • boolean execute(String SQL): This is used to execute in case of callable statements.

Example

CallableStatement test = con.prepareCall("{call orderinfo(?,?)}");
test.setInt(1, 45);
test.setString(2, "Pen");
cs.execute();
You can also try this code with Online Java Compiler
Run Code

Frequently Asked Questions

What type of statements does JDBC support for executing data manipulation operations?

JDBC supports Statement, PreparedStatement, and CallableStatement for executing data manipulation operations such as SELECT, INSERT, UPDATE, and DELETE.

What protocol does JDBC use?

JDBC uses the TTC protocol developed by Oracle to access the Oracle Relational Database Management System.

Which JDBC statement type is most efficient for repeated queries with changing parameters?

PreparedStatement is the most efficient for repeated queries with changing parameters because it is precompiled and can be reused with different inputs.

What is the default port for JDBC?

JDBC has a default port of 1433 but can be made to listen to any port.

Conclusion

DBC provides essential methods for Java applications to interact with databases through three main types of statements: Create, Prepared, and Callable. Each statement serves specific purposes, from general database access to dynamic query execution and handling complex database tasks via stored procedures. Understanding these statements enhances database efficiency and performance in Java-based applications.In this blog, we discussed how we can use the different types of JDBC statements to connect to the database and use it.

Recommended Article:

Live masterclass