In this article, we will discuss commonly used JDBC Statement interface methods with examples.
Example to insert multiple records in a table using Statement interface.
Example to update a record in a table using Statement interface.
Example to retrieve records from a table using Statement interface.
Example to delete a record from a table using a Statement interface.
Example to insert records in a batch process via Statement interface.
Example to update records in a batch process via Statement interface.
Example to update a record in a table using the PreparedStatement interface.
Example to retrieve records from a table using the PreparedStatement interface.
Example to pass a list of values to IN clause using PreparedStatement interface.
Example to insert records in a batch process via PreparedStatement interface.
Example to update records in a batch process via PreparedStatement interface.
Statement interface Overview
Statement interface object used for executing a static SQL statement and returning the results it produces.
Statement interface extends Wrapper, AutoCloseable interfaces.
By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a current ResultSet object of the statement if an open one exists.
Statement interface Class Diagram
Statement interface Commonly used methods
The important methods of Statement interface are as follows:
- boolean execute(String sql) - Executes the given SQL statement, which may return multiple results.
- int[] executeBatch() - Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
- ResultSet executeQuery(String sql) - Executes the given SQL statement, which returns a single ResultSet object.
- int executeUpdate(String sql) - Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
Statement interface Examples
Let's see a few examples of using Statement interface methods.
JDBC Statement Create a Table Example
In this example, we will use the execute(SQL) method to create a users table in a MySQL database.
Steps to process create SQL statement with JDBC:
- Establishing a connection
- Create a statement
- Execute the query
- Using try-with-resources Statements to Automatically Close JDBC Resources
package com.javaguides.jdbc.statement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Create Statement JDBC Example
* @author Ramesh Fadatare
*
*/
public class CreateStatementExample {
private static final String createTableSQL = "create table Users1(\r\n" + " id int(3) primary key,\r\n" +
" name varchar(20),\r\n" + " email varchar(20),\r\n" + " country varchar(20),\r\n" +
" password varchar(20)\r\n" + " );";
public static void main(String[] argv) throws SQLException {
CreateStatementExample createTableExample = new CreateStatementExample();
createTableExample.createTable();
}
public void createTable() throws SQLException {
System.out.println(createTableSQL);
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement statement = connection.createStatement();) {
// Step 3: Execute the query or update query
statement.execute(createTableSQL);
} catch (SQLException e) {
// print SQL exception information
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
JDBC Statement Select Records Example
In this example, we will use the executeQuery() method to select records from MySQL database table using the JDBC Statement interface.
package com.javaguides.jdbc.statement.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Select Statement JDBC Example
* @author Ramesh Fadatare
*
*/
public class SelectStatementExample {
private static final String QUERY = "select id,name,email,country,password from Users";
public static void main(String[] args) {
// using try-with-resources to avoid closing resources (boilerplate code)
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement stmt = connection.createStatement();
// Step 3: Execute the query or update query
ResultSet rs = stmt.executeQuery(QUERY)) {
// Step 4: Process the ResultSet object.
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
String country = rs.getString("country");
String password = rs.getString("password");
System.out.println(id + "," + name + "," + email + "," + country + "," + password);
}
} catch (SQLException e) {
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Output:
1,Ram,tony@gmail.com,US,secret
3,Pramod,pramod@gmail.com,India,123
4,Deepa,deepa@gmail.com,India,123
5,Tom,top@gmail.com,India,123
JDBC Statement Interface Examples
Example to create a table using a Statement interface.Example to insert multiple records in a table using Statement interface.
Example to update a record in a table using Statement interface.
Example to retrieve records from a table using Statement interface.
Example to delete a record from a table using a Statement interface.
Example to insert records in a batch process via Statement interface.
Example to update records in a batch process via Statement interface.
JDBC PreparedStatement Interface Examples
Example to insert a record in a table using the PreparedStatement interface.Example to update a record in a table using the PreparedStatement interface.
Example to retrieve records from a table using the PreparedStatement interface.
Example to pass a list of values to IN clause using PreparedStatement interface.
Example to insert records in a batch process via PreparedStatement interface.
Example to update records in a batch process via PreparedStatement interface.
Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours
Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course