java.sql.Statement is used for executing a static SQL statement and returning the results it produces. Statement interface has two subinterfaces-
- PreparedStatement– Stores a precompiled SQL statement which can then be used to execute this statement multiple times by passing different values for the parameters. Read more about PreparedStatement in this post- JDBC PreparedStatement Interface
- CallableStatement– Used to execute SQL stored procedures. Read more about CallableStatement in this post- JDBC CallableStatement Interface
Methods in Statement interface
Some of the frequently used methods of the Statement interface are detailed here-
- execute(String sql)– Executes the given SQL statement, which may return multiple results. This method returns a boolean; true if the first result is a ResultSet object; false if it is an update count or there are no results.
- executeQuery(String sql)– Executes the given SQL statement returning a ResultSet object. Suited for running a SELECT query.
- executeUpdate(String sql)– Executes the specified SQL which may be an INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement (Create, Drop).
- addBatch(String sql)– If you are running a bunch of SQL statements as a batch, this method adds the given SQL command to the current list of commands for this Statement object.
- executeBatch()– Submits a batch of commands to the database for execution.
- close()– Releases this Statement object’s database and JDBC resources immediately.
Statement example in Java
In the example we’ll use all the three types of execute methods, execute(), executeQuery() and executeUpdate() to have a better understanding of how to use these methods.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCStatementDemo {
public static void main(String[] args) {
Connection connection = null;
try {
// Load driver
Class.forName("com.mysql.cj.jdbc.Driver");
// connection object
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// create Statement object
Statement statement = connection.createStatement();
/** execute method **/
boolean flag = statement.execute("UPDATE Employee SET DEPARTMENT = 'HR' where id = 15");
// flag false means not returning resultset
if(flag == false){
System.out.println("Updated rows " + statement.getUpdateCount() );
}
/** executeUpdate method **/
// Insert statement
int count = statement.executeUpdate("INSERT INTO Employee(FIRST_NAME, LAST_NAME, DEPARTMENT) "
+ "values('John', 'Trudaue', 'IT')");
System.out.println("Number of records Inserted " + count);
// update statement
count = statement.executeUpdate("UPDATE Employee SET DEPARTMENT = 'Finance' where id = 15");
System.out.println("Number of records Updated " + count);
//delete statement
count = statement.executeUpdate("Delete from Employee where id = 11");
System.out.println("Number of records Deleted " + count);
/** executeQuery method **/
// Executing Query
ResultSet rs = statement.executeQuery("Select * from Employee");
while(rs.next()){
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
}
}catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
//close connection
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Related Posts
- JDBC Driver Types
- JDBC DriverManager Class
- JDBC Driver Interface
- JDBC Connection Interface
- JDBC ResultSet Interface
- Apache DBCP Connection Pooling Java Example
- Java Reflection – Class Fields
- Serialization in Java With Examples
That’s all for the topic JDBC Statement Interface. If something is missing or you have something to share about the topic please write a comment.
You may also like
