Java @ Desk: Sql | Java at your desk

JDBC Batch/Bulk Insert, Update, Delete Java Example

SQL JDBC Batch/Bulk Insert, Update, Delete Java Example

JDBC Statement/PreparedStatament Batch/Bulk Insert, Update and Delete operation in Java.

PreparedStatement allows insert a record one by one using the execute() method. Say for example, there is a batch of records that need to be inserted into the database.

There will be a big performace hit as shown in the below implementation because in order to insert 2 records in the database the execute() method is getting called twice. This is a real performance hit in case of insertion of million records.

Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String insertQuery = "Insert Into Employee(name, address) Values (? , ?)";
try {
 dbConnection = getDBConnection();
 preparedStatement = dbConnection.prepareStatement(insertQuery
   .toString());
 preparedStatement.setString(1, "John");
 preparedStatement.setString(2, "New York");
 preparedStatement.execute();
 
 preparedStatement.setString(1, "Andy");
 preparedStatement.setString(2, "Canada");
 preparedStatement.execute();
 
} catch (SQLException e) {
} 


Instead of using the above implemention, we will use the addBatch() and executeBatch() implementation.

Steps to do so :

1) Create the PreparedStatement Object
2) For each insert record perform SQL Injection and call addBatch() method on PreparedStatement Object
3) Once done, make a call to executeBatch() method

Below is sample implementation

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchUpdateInsertDelete {
 public static void main(String[] argv) {
  try {
   Connection dbConnection = null;
   PreparedStatement preparedStatement = null;
   String insertQuery = "Insert Into Employee(name, address) Values (? , ?)";
   try {
    dbConnection = getDBConnection();
    preparedStatement = dbConnection.prepareStatement(insertQuery
      .toString());

    for (int i = 0; i < 100; i++) {
     preparedStatement.setString(1, "John");
     preparedStatement.setString(2, "New York");
     preparedStatement.addBatch();
    }
    preparedStatement.executeBatch();

   } catch (SQLException e) {
   } finally {
    if (preparedStatement != null) {
     preparedStatement.close();
    }
    if (dbConnection != null) {
     dbConnection.close();
    }
   }
  } catch (SQLException e) {
  }

 }

 private static Connection getDBConnection() {
  Connection dbConnection = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   dbConnection = DriverManager.getConnection(
     "jdbc:oracle:thin:@localhost:1521:javacodeimpl", "user",
     "password");
   return dbConnection;
  } catch (SQLException e) {
   System.out.println(e.getMessage());
  } catch (ClassNotFoundException e) {
   System.out.println(e.getMessage());
  }
  return dbConnection;
 }
}


The above code has a small limitation. In case of million records, if executeBatch() will be called then it will throw OutOfMemoryError. In order to resolve this, batch update should/must be done in a batch of 1000's or 2000's. Add 1000 records in a batch and call executeBatch().

Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String insertQuery = "Insert Into Employee(name, address) Values (? , ?)";
try {
 dbConnection = getDBConnection();
 preparedStatement = dbConnection.prepareStatement(insertQuery
   .toString());
 int batchSize = 1000;
 int count = 0;
 for (int i = 0; i < 100000; i++) {
  preparedStatement.setString(1, "John");
  preparedStatement.setString(2, "New York");
  preparedStatement.addBatch();

  if (++count % batchSize == 0) {
   preparedStatement.executeBatch();
  }
 }
 preparedStatement.executeBatch();// Insert rest of the records

} catch (SQLException e) {
}

Java JDBC SQL Update PreparedStatement a record

Java JDBC SQL Update a record example
To insert a record in database in SQL using JDBC, you need to perform below mentioned steps:
1) Create the database connection. There are two ways to get the DB connection. One is through java.sql.DriverManager and another is through javax.sql.DataSource. Click on below links for specific configuration.
DriverManager DB connection
DataSource DB Connection

2) Create a PreparedStatement object using sql query:
PreparedStatement fielStatement = connection.prepareStatement(SQL_STRING);

3) Execute sql statement: This is done using execute() method of PreparedStatement object
fielStatement.execute();

Java JDBC SQL Sorting Column Order By Clause

Java JDBC SQL Sorting Column Order By Clause
Sorting a column is done using the Order By clause in SQL. Sorting a column can be done using following keyword:
1) Order By <COLUMN_NAME> asc - Display the result in ascending order for the <COLUMN_NAME>
2) Order By <COLUMN_NAME> desc - Display the result in descending order for the <COLUMN_NAME>


SELECT col_name FROM table_name ORDER BY col_name ASC:
SELECT col_name FROM table_name ORDER BY col_name DESC:

Consider the below table:
Customer
CustId CustName
1 Cynthia
2 Thanh
3 Peter
4 Eric

Java JDBC SQL Delete example

Java JDBC SQL Delete example
To delete a record in database in SQL using JDBC, you need to perform below mentioned steps:
1) Create the database connection. There are two ways to get the DB connection. One is through java.sql.DriverManager and another is through javax.sql.DataSource. Click on below links for specific configuration.
DriverManager DB connection
DataSource DB Connection

2) Create a PreparedStatement object using sql query:
PreparedStatement fielStatement = connection.prepareStatement(SQL_STRING);

3) Execute sql statement: This is done using execute() method of PreparedStatement object
fielStatement.execute();

Java JDBC SQL Insert example

Java JDBC SQL Insert example
To insert a record in database in SQL using JDBC, you need to perform below mentioned steps:
1) Create the database connection. There are two ways to get the DB connection. One is through java.sql.DriverManager and another is through javax.sql.DataSource. Click on below links for specific configuration.
DriverManager DB connection
DataSource DB Connection

2) Create a PreparedStatement object using sql query:
PreparedStatement fielStatement = connection.prepareStatement(SQL_STRING);

3) Execute sql statement: This is done using execute() method of PreparedStatement object
fielStatement.execute();

Difference between Union, Union All in Sql

Difference between Union and Union All

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

1) UNION removes duplicate records, UNION ALL does not
2) UNION is slower than UNION ALL since there is an additional tasks need to be performed to remove the duplicate rows i.e. UNION selects the distinct results
3) In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION
4) UNION is used to select distinct values from two tables where as UNION ALL is used to select all values including duplicates from the tables

Difference between Equi Join, Natural Join and Cross Join in sql

Different types of Inner Join are :
1) Equi Join
2) Natural Join
3) Cross Join

One of the most common interview questions that is being asked is explain Equi Join, Natural Join and Cross Join in sql. I will break the question in different meaningful easy to understand concepts.

Consider the below tables
Employee
EmpId EmpName
1 John
2 David
3 Peter
4 Eric

Difference between Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql

One of the most common interview questions that is being asked is explain Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql. I will break the question in different meaningful easy to understand concepts.

What is sql join?
Join, as the name suggests, is used to combine data from two tables based on some common features(columns). The resulting data is the temporary table created by comparing two tables.

Consider the below tables

Difference between delete, truncate, drop in sql

SQL Delete Statement
It is a DML command
This command can be used to remove some or all rows from the table
After deletion, it does free the space containing the table
While deleting rows, it applies lock on each row
Where condition can be used
It deletes specified rows if where clause exists in the query
Rollback is possible
Triggers are fired
It is used to manipulate/modify the table data. It does not change the property of a table
Slower in performance since it maintains logs

JDBC PreparedStatement

JDBC Prepared Statement/PreparedStatement in java is used for execution of SQL statements. If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.

SQL query execution involves :
1) Parsing of SQL query string
2) Compilation
3) Execute the SQL query

JDBC Statement

JDBC Statement is used for execution of SQL statements. Statement interface provides basic method for SELECT, INSERT, UPDATE, DELETE operations in the database.

SQL query execution involves :
1) Parsing of SQL query string
2) Compilation
3) Execute the SQL query

Types of statements in JDBC

Three types of statements in JDBC:

1. Statement(Click for implementation)
2. Prepared Statement(Click for implementation)
3. Callable Statement

SQL query execution involves :

1) Parsing of SQL query string
2) Compilation
3) Execute the SQL query

Datasource JNDI connectivity steps in java

JNDI Datasource connection in JBOSS

1) Create a dataSource-ds.xml in $jboss_home/server/default/deploy folder and edit it:
2)
<datasources>
<local-tx-datasource>
<jndi-name>MyDSJndi</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/DATASERVICE </connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>userName</user-name>
<password>password</password>
</local-tx-datasource>
</datasources>


JDBC connection steps in java

JDBC Connection steps:

1) Register a database driver: This is done in order to register which type of database drivers going to use.




2) Load database driver: This is done to get the connection object using DriverManager class



DriverManager uses the first two terms of the URL (jdbc:msql) to locate a suitable driver from among those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application.