fbpx
Pages Navigation Menu

Coding is much easier than you think

JDBC Batch Updates

Posted by on Aug 29, 2013 in JDBC, JDBC Tutorials | 0 comments

 
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.
 

Note : Batch Update is not limit to Insert statement, it’s applied for Update and Delete statement as well.

 

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

 

dwd2
Download It – BatchUpdates

 

Advertisements
Read More

How to retrieve SQLWarning in java?

Posted by on Aug 21, 2013 in JDBC | 0 comments

 
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();
	}

 

Note: clearWarning() method is used to clear all the warning reported by the call of Connection, ResultSet, and Statement objects.

 

Read More

SQL Script to create a backup of a table

Posted by on Aug 13, 2013 in JDBC, MySQL | 0 comments

 
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

Posted by on Aug 10, 2013 in JDBC | 0 comments

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

  1. Easy to insert parameters into the SQL statement.
  2. Easy to reuse the PreparedStatement with new parameters.
  3. Increase performance of executed statements  because query is compiled only once.
  4. Enables easier batch updates.

 

 

JDBC & Stored Procedure

 

JDBC Batch Update example

 

JDBC Performance Tuning

 

FAQ

 

Read More

What is SQL Injection?

Posted by on May 5, 2013 in J2EE, Java, JDBC, Security, SQL Injection | 0 comments

 
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.
 

Read More

How to get Primary Key Of Inserted Record in JDBC?

Posted by on Mar 26, 2013 in Java, JDBC | 0 comments

 
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