JDBC Batch Updates
You can send multiple queries to the database at a time using batch update feature of Statement/PreparedStatement objects this reduces the number of JDBC calls and improves performance.
Statement Batch Updates
To issue a Batch update , call the addBatch() and executeBatch() methods as shown below.
dbConnection.setAutoCommit(false); statement = connection.createStatement(); statement.addBatch(insertTableQuery1); statement.addBatch(insertTableQuery2); statement.addBatch(insertTableQuery3); statement.executeBatch(); dbConnection.commit();
Full example – Batch Update using JDBC Statement
package com.simplecode.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCStmtBatchUpdate
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl="jdbc:oracle:thin:@"+serverName+":"+portNumber+":"+sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";
public static void main(String[] argc)
{
try
{
batchInsertRecordsIntoTable();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
private static void batchInsertRecordsIntoTable() throws SQLException
{
Connection connection = null;
Statement statement = null;
String insertTableQuery1 = "INSERT INTO EMPLOYEE"
+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
+ "(1001,'Jamil','admin')";
String insertTableQuery2 = "INSERT INTO EMPLOYEE"
+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
+ "(1002,'Ameer','admin')";
String insertTableQuery3 = "INSERT INTO EMPLOYEE"
+ "(USER_ID, USERNAME, CREATED_BY) VALUES"
+ "(1003,'Nilafar','admin')";
try
{
connection = getDBConnection();
statement = connection.createStatement();
connection.setAutoCommit(false);
statement.addBatch(insertTableQuery1);
statement.addBatch(insertTableQuery2);
statement.addBatch(insertTableQuery3);
statement.executeBatch();
connection.commit();
System.out.println("Records are inserted into EMPLOYEE table!");
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
finally
{
if (statement != null)
{
statement.close();
}
if (connection != null)
{
connection.close();
}
}
}
private static Connection getDBConnection()
{
Connection dbConnection = null;
try
{
Class.forName(dbDriver);
}
catch (ClassNotFoundException e)
{
System.err.println(e.getMessage());
}
try
{
dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
return dbConnection;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
return dbConnection;
}
}
PreparedStatement Batch Updates
You can also use a PreparedStatement object to execute batch updates. The PreparedStatement enables you to reuse the same SQL statement, and just insert new parameters into it, for each update to execute. This method also avoids SQL Injection issue.
Here is an example (Batch Update using PreparedStatement):
package com.simplecode.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCPstmtBatchUpdate
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl="jdbc:oracle:thin:@"+serverName+":"+portNumber+":"+sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";
public static void main(String[] argc)
{
try
{
batchInsertRecordsIntoTable();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
private static void batchInsertRecordsIntoTable() throws SQLException
{
Connection connection = null;
PreparedStatement preparedStmt = null;
String insertTableQuery="INSERT INTO EMPLOYEE (USER_ID,USERNAME,CREATED_BY) VALUES (?,?,?)";
try
{
connection = getDBConnection();
preparedStmt = connection.prepareStatement(insertTableQuery);
connection.setAutoCommit(false);
preparedStmt.setInt(1, 1000);
preparedStmt.setString(2, "Nilafar");
preparedStmt.setString(3, "admin");
preparedStmt.addBatch();
preparedStmt.setInt(1, 1001);
preparedStmt.setString(2, "Azar");
preparedStmt.setString(3, "admin");
preparedStmt.addBatch();
preparedStmt.setInt(1, 1002);
preparedStmt.setString(2, "Gokul");
preparedStmt.setString(3, "admin");
preparedStmt.addBatch();
preparedStmt.setInt(1, 1003);
preparedStmt.setString(2, "Haripriya");
preparedStmt.setString(3, "admin");
preparedStmt.addBatch();
preparedStmt.executeBatch();
connection.commit();
System.out.println("Record is inserted into EMPLOYEE table!");
}
catch (SQLException e)
{
System.out.println(e.getMessage());
} finally
{
if (preparedStmt != null)
{
preparedStmt.close();
}
if (connection != null)
{
connection.close();
}
}
}
private static Connection getDBConnection()
{
Connection dbConnection = null;
try
{
Class.forName(dbDriver);
}
catch (ClassNotFoundException e)
{
System.out.println(e.getMessage());
}
try
{
dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
return dbConnection;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
return dbConnection;
}
}
 |
|
Read More
How to retrieve SQLWarning in java?
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warning simply alerts the user when something did not happen as planned. A warning can be reported on a Connection, ResultSet and Statement object. Each of these classes has a getWarnings() method, which we must invoke in order to see the warning reported on the calling object.
E.g.
SQLWarning statementWarning = statement.getWarnings();
while (statementWarning != null)
{
System.out.println("Error code: " + statementWarning.getErrorCode());
System.out.println("SQL State: " + statementWarning.getSQLState());
System.out.println("Warning Message: " + statementWarning.getMessage());
statementWarning = statementWarning.getNextWarning();
}
Read More
SQL Script to create a backup of a table
The below are the MYSQL script, which takes a backup/copy of the entire table into a temp table. This makes sure that you have a full backup of the data and table format, These query will be quiet useful in case something goes wrong.
You can use either of these queries to create a backup.
1) CREATE TABLE employees_backup AS ( SELECT * from employees); 2) CREATE TABLE SCHEMA.employees_backup LIKE SCHEMA.employees; INSERT SCHEMA.employees_backup SELECT * FROM SCHEMA.employees;
Read More
JDBC Tutorial for beginners
JDBC is a Java API that enables Java application to connect and execute query to the database. JDBC API uses jdbc drivers to connect to the database.
This tutorial will not cover every single detail of the JDBC API, but focus on the most commonly used features. Here you will learn many step by step examples on using JDBC Statement, PreparedStatement, CallableStatement, and JDBC Transaction.
The rest you can read about in the JavaDoc afterwards.
Happy learning :-)
Quick Start
Some quick guides to show how JDBC interact with databases like MySQL, Oracle and PostgreSQL.
JDBC & Statement interface
JDBC & PreparedStatement interface
The PreparedStatement interface is a sub interface of Statement. It is used to execute parameterized query.
Why use PreparedStatement?
You can use a PreparedStatement instead of a Statement and benefit the following features of the PreparedStatement.
The PreparedStatement’s primary features are:
- Easy to insert parameters into the SQL statement.
- Easy to reuse the PreparedStatement with new parameters.
- Increase performance of executed statements because query is compiled only once.
- Enables easier batch updates.
JDBC & Stored Procedure
JDBC Batch Update example
JDBC Performance Tuning
FAQ
Read More
What is SQL Injection?
SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker) – Wikipedia
SQL injection errors occur when:
1. Data enters a program from an untrusted source.
2. The data is used to dynamically construct a SQL query.
Now, let us see some of the practical Scenario of SQL injection. For example consider an application which has a login page, it is possible that the application uses a dynamic SQL Statement as below.
SELECT * FROM Employee WHERE Employee_Name = 'strEmployeeName' AND Password = 'strPassword';
This statement is expected to return at least a single row with the employee details from the Employee table as the result set when there is a row with the employee name and password entered in the SQL statement.
If the attacker would enter Gokul as the strEmployeeName (in the textbox for employee name) and Krish as strPassword (in the textbox for password), the above SQL statement would become:
SELECT * FROM Employee WHERE Employee_Name = 'Gokul' AND Password = 'Krish';
If an attacker enters the string ‘Gokul’ OR ‘a’=’a’ for strEmployeeName, and ‘Krish’ OR ‘a’=’a’ for strPassword then the query becomes the following:
SELECT * FROM Employee WHERE Employee_Name = 'Gokul' OR 'a'='a' AND Password = 'Krish' OR 'a'='a';
Since ‘a’=’a’ condition is always true, the result set would consist of all the rows in the Employee table. The application could allow the attacker to log in as the first employee in the Employee table.
If the attacker would enter ‘Gokul'; DROP table Employee; as strEmployeeName and anything as strPassword, the SQL statement would become like the one below.
SELECT * FROM Employee WHERE Employee_Name = 'Gokul'; DROP table Employee; AND Password = 'Krish'
This statement could cause the table Employee to be permanently deleted from the database.
Solution
So inorder to avoid SQL inject errors it is better to use prepare statement instead of normal statement.
How to get Primary Key Of Inserted Record in JDBC?
When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically. The below example shows how to get this key after insert statement.
After perfoming executeUpdate() method on PreparedStatement, call getGeneratedKeys() method on PreparedStatement.
The getGeneratedKeys() provide a standard way to make auto-generated or identity column values available to an application that is updating a database table without a requiring a query and a second round-trip to the server. SQL Server allows only a single auto increment column per table.
The ResultSet that is returned by getGeneratedKeys method will have only one column, with the returned column name of GENERATED_KEYS.
If generated keys are requested on a table that has no auto increment column, the JDBC driver will return a null result set.
When you insert rows by executeUpdate or execute an INSERT statement or an INSERT within SELECT statement, you need to indicate that you will want to retrieve automatically generated key values. You do that by setting a flag in a Connection.prepareStatement, Statement.executeUpdate, or Statement.execute method call. The statement that is executed must be an INSERT statement or an INSERT within SELECT statement, Otherwise, the JDBC driver ignores the parameter that sets the flag.
For example:
package com.simplecode.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCGetAutoIncKeys
{
private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION
="jdbc:oracle:thin:@<hostname>:<port num>:<DB name>";
private static final String DB_USER = "username";
private static final String DB_PASSWORD = "password";
static
{
try
{
Class.forName(DB_DRIVER);
}
catch (ClassNotFoundException e)
{
System.out.println("Please add Oracle JDBC Driver in your classpath ");
e.printStackTrace();
}
System.out.println("Oracle JDBC Driver Registered!");
}
private static Connection getConnection()
{
Connection connection = null;
try
{
connection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
}
catch (Exception e)
{
e.printStackTrace();
}
return connection;
}
public static void main(String[] arg)
{
PreparedStatement stmt = null;
ResultSet rs = null;
Connection conn = null;
try
{
conn = getConnection();
stmt = conn.prepareStatement("DROP TABLE IF EXISTS EMPLOYEE");
stmt.executeUpdate();
stmt.executeUpdate("CREATE TABLE EMPLOYEE ("
+ "EMP_ID INT NOT NULL AUTO_INCREMENT, "
+ "name VARCHAR(64), dept VARCHAR(64),"
+ " salary INT, PRIMARY KEY (EMP_ID))");
String query = "INSERT INTO EMPLOYEE (name, dept, salary) values (?,?,?)";
stmt = conn
.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, "Mahi");
stmt.setString(2, "Automobile");
stmt.setInt(3, 1000000);
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if (rs != null && rs.next())
{
System.out.println("Generated Emp Id: " + rs.getInt(1));
}
rs.close();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
if (rs != null)
{
try
{
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
}
Read More