--Before executing java program execute these database scripts >
create table EMPLOYEE(id number(4),name varchar2(22));
insert into EMPLOYEE values(1,'ankit');
insert into EMPLOYEE values(2,'rohit');
commit;
--If table already exists then execute the DROP command >
drop table EMPLOYEE;
Programs JDBC- Execute SELECT query using Statement's execute method, getResultSet method and ResultSet in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StatementSelectTest {
public static void main(String... arg) {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
// registering Oracle driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// getting connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"ankit", "Oracle123");
System.out.println("Connection established successfully!");
stmt = con.createStatement();
/*
execute() method returns true
if returned result is a ResultSet object and
execute() method returns false
if returned result is a update count
*/
//execute select query
boolean result = stmt.execute("select id, name from EMPLOYEE");
System.out.println(result ? "returned result is a ResultSet object"
: "returned result is a update count"); // true
rs = stmt.getResultSet();
while (rs.next()) {
System.out.print(rs.getInt("id")+" ");
System.out.println(rs.getString("name"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
if(rs!=null) rs.close(); //close resultSet
if(stmt!=null) stmt.close(); //close Statement
if(con!=null) con.close(); // close connection
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/*OUTPUT
Connection established successfully!
returned result is a ResultSet object
1 ankit
2 rohit
*/
|
NOTE : Statement is suitable for executing DDL commands - create, drop, alter and truncate. This article demonstrates that Statement can also be used to serve the purpose.
We must prefer PreparedStatement, as it is suitable for executing DML commands - SELECT, INSERT, UPDATE and DELETE.
RELATED LINKS>