JDBC- Execute SELECT query using Statement's execute method, getResultSet method and ResultSet in java

--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>

JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC- Calling Oracle database FUNCTION - CallableStatement example in java


No comments:

Post a Comment