JDBC- Execute INSERT query using Statement's execute and getUpdateCount method in java

--Before executing java program execute these database scripts  >
create table EMPLOYEE(id number(4),name varchar2(22));

--If table already exists then execute the DROP command >
drop table EMPLOYEE;

Full Programs JDBC- Execute INSERT query using Statement's execute and getUpdateCount method in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StatementInsertTest {
   public static void main(String... arg) {
          Connection con = null;
          Statement stmt = null;
          try {
                 // registering Oracle driver class
                 // getting connection
                 con = DriverManager.getConnection(
                              "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 insert query
                 boolean result = stmt.execute("INSERT into EMPLOYEE (id, name) values (1, 'ankit') ");
                 System.out.println(result ? "returned result is a ResultSet object"
                              : "returned result is a update count"); // false
                 int numberOfRowsInserted = stmt.getUpdateCount();
                 System.out.println("numberOfRowsInserted = " + numberOfRowsInserted);
          } catch (ClassNotFoundException e) {
          } catch (SQLException e) {
                 try {
                       if(stmt!=null) stmt.close(); //close Statement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
Connection established successfully!
returned result is a update count
numberOfRowsInserted = 1

Must Know :

By default connection is auto-commit, so all insert queries will be committed automatically by java connection.
(Must know : insert is a DML(Data Manipulation Language) command, queries are not committed automatically in database)

We may stop connection from doing auto-commits by setting auto-commit to false using > con.setAutoCommit(false);
Well in that we may commit transactions any time using con.commit();

Also, We may set connection back to default behavior of doing auto-commit by using > con.setAutoCommit(true) or simply using con.setAutoCommit();

But what will be benefit of using con.setAutoCommit(false)?
If any transaction goes wrong than we may halt execution further related transactions before calling con.commit() and call con.rollback() in catch block.

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.


java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE

JDBC- Insert/Store/save FILE in database

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

Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java

Difference between CLOB and CLOB data type in Oracle

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...
//start paginationJs //end paginationJs