JDBC - Statement Object Example


Advertisements

Following is the example, which makes use of the following three queries along with the opening and closing statement −

  • boolean execute(String SQL) : Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use the truly dynamic SQL.

  • int executeUpdate(String SQL): Returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements, for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.

  • ResultSet executeQuery(String SQL)− Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

This sample code has been written based on the environment and database setup done in the previous chapters.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";
   static final String UPDATE_QUERY = "UPDATE Employees set age=30 WHERE id=103";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         ) {
         // Let us check if it returns a true Result Set or not.
         Boolean ret = stmt.execute(UPDATE_QUERY);
         System.out.println("Return value is : " + ret.toString() );

         // Let us update age of the record with ID = 103;
         int rows = stmt.executeUpdate(UPDATE_QUERY);
         System.out.println("Rows impacted : " + rows );

         // Let us select all the records and display them.
         ResultSet rs = stmt.executeQuery(QUERY);		      

         // Extract data from result set
         while (rs.next()) {
            // Retrieve by column name
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Now let us compile the above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample
Return value is : false
Rows impacted : 1
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mehnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
C:\>
Advertisements