Re: [MonetDB-users] Java executeBatch() behavior

8 Mar 2010

      Hi Fabian,

 Here i am including a Sample test program which i wrote to test the batch
Execution 

 Script to create the test table

[SQL]

CREATE TABLE TEST.instance_test (
    c_208  BIGINT 
  , c_207  BIGINT
  , property SMALLINT
  , proptype BOOLEAN
  , ns SMALLINT
  , CONSTRAINT uuid_key PRIMARY KEY(c_208,c_207,property,ns)
);
[/SQL]

 [CODE]
import java.sql.BatchUpdateException;
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 BatchInsertTests {

	/**
	 * @param args
	 */

	private boolean autoCommitOn = true;

	public static void main(String[] args) {

		BatchInsertTests test = new BatchInsertTests();
		try {
			test.testBatch();
                        // test.setAutoCommitOn(false);
		} catch (Exception e) {
			System.out.println("Exception =[" + e + "]");
			e.printStackTrace();
		}

	}

	private void testBatch() throws Exception {
		System.out.println("In testBatch()");
		Connection connection = getConnection();
		if(!this.autoCommitOn)
		{
			connection.setAutoCommit(false);
		}
		System.out.println("In testBatch() con=["+connection+"]");
		PreparedStatement statement = null;
		String query = getInstanceTableInsertQuery();
		statement = connection.prepareStatement(query);
		int[] updatedValues = null;
		try {

			printTableCount(connection);

			statement.setLong(1, new Long("3751548521200044480").longValue());
			statement.setLong(2, new Long("-9164647932261081852").longValue());
			statement.setInt(3, 12);
			statement.setInt(4, 115);
			statement.setBoolean(5, false);

			statement.addBatch();
			System.out.println("After adding the First Query ");

			statement.setLong(1, new Long("3751548521200044480").longValue());
			statement.setLong(2, new Long("-9164647932261081852").longValue());
			statement.setInt(3, 12);
			statement.setInt(4, 115);
			statement.setBoolean(5, false);

			statement.addBatch();
			System.out.println("After adding the Second Query ");

			statement.setLong(1, new Long("3751548521200044481").longValue());
			statement.setLong(2, new Long("-9164647932261081853").longValue());
			statement.setInt(3, 12);
			statement.setInt(4, 117);
			statement.setBoolean(5, false);

			statement.addBatch();
			System.out.println("After adding the Third Query ");

			updatedValues = statement.executeBatch();
			System.out
					.println("After executing the First Batch................");
			System.out.println("updatedValues =[" + updatedValues + "]");
			printUpdatedValues(updatedValues);
			commitTransaction(connection);
			statement.clearBatch();
		}
		catch (BatchUpdateException bue)
		{
			System.out.println("BatchUpdateException =["+bue+"] and
updatedValues=["+updatedValues+"]");
			System.out.println("NextException 		 =["+bue.getNextException()+"]");
			printUpdatedValues(updatedValues);
			statement.clearBatch();
			commitTransaction(connection);
		}
		try {
			statement.setLong(1, new Long("3751548521200044482").longValue());
			statement.setLong(2, new Long("-9164647932261081853").longValue());
			statement.setInt(3, 12);
			statement.setInt(4, 119);
			statement.setBoolean(5, false);
			statement.addBatch();
			System.out.println("After adding the Fourth Query ");
			updatedValues = statement.executeBatch();
			System.out
					.println("After executing the Second Batch................");
			printUpdatedValues(updatedValues);
			commitTransaction(connection);
		}
		catch (BatchUpdateException bue) {
			System.out.println("BatchUpdateException =["+bue+"]");
			commitTransaction(connection);
		}

		printTableCount(connection);

		statement.close();
		connection.close();

	}

	private void printTableCount(Connection connection) {
		Statement statement = null;
		ResultSet rs = null;
		try{
			statement = connection.createStatement();
			rs = statement.executeQuery(getInstanceTableSelectQuery());
			if(rs.next()){
				System.out.println("No.of Records in the Table
=["+rs.getString("NO_OF_RECORDS")+"]");
			}
		}
		catch(SQLException sqle){
			sqle.printStackTrace();
		}
	}

	private void commitTransaction(Connection connection){
		if(!this.autoCommitOn)
		{
			try
			{
				connection.commit();
			}
			catch(SQLException sqle){
				sqle.printStackTrace();
			}
		}
	}

	public void printUpdatedValues(int[] updatedValues) {
		if (updatedValues != null && updatedValues.length > 0) {

			for (int i = 0; i < updatedValues.length; i++) {
				System.out.println("Updated Values[" + i + "] ="
						+ updatedValues[i]);
			}
		} else {
			System.out.println("No Updated Values !!");
		}
	}

	public Connection getConnection() {
		Connection con = null;
		String url = "jdbc:monetdb://localhost:50000/MYDB";
		try {
			Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
			con = DriverManager.getConnection(url, "monetdb", "monetdb");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	private String getInstanceTableInsertQuery() {
		StringBuilder strQuery = null;
		strQuery = new StringBuilder();
		strQuery.append("INSERT INTO ");
		strQuery.append(" TEST.instance_test");
		strQuery.append("(c_208,c_207,ns,property,proptype) VALUES(?,?,?,?,?)");
		return strQuery.toString();
	}

	private String getInstanceTableSelectQuery() {
		StringBuilder strQuery = null;
		strQuery = new StringBuilder();
		strQuery.append("SELECT COUNT(*) AS NO_OF_RECORDS ");
		strQuery.append(" FROM TEST.instance_test");
		return strQuery.toString();
	}

	public boolean isAutoCommitOn() {
		return autoCommitOn;
	}

	public void setAutoCommitOn(boolean autoCommitOn) {
		this.autoCommitOn = autoCommitOn;
	}

}

 [/CODE]

 When auto commit is On then none of the records are getting inserted into
the Table (instance_test)

[OUTPUT]
In testBatch()
In testBatch() con=[MonetDB Connection (jdbc:monetdb://localhost:50000/MYDB)
disconnected]
No.of Records in the Table =[0]
After adding the First Query 
After adding the Second Query 
After adding the Third Query 
BatchUpdateException =[java.sql.BatchUpdateException: Error(s) occurred
while executing the batch, see next SQLExceptions for details] and
updatedValues=[null]
NextException 		 =[java.sql.SQLException: SQLException:assert:INSERT INTO:
PRIMARY KEY constraint 'instance_test.uuid_key' violated
current transaction is aborted (please ROLLBACK)]
No Updated Values !!
After adding the Fourth Query 
After executing the Second Batch................
Updated Values[0] =0
No.of Records in the Table =[0]
[/OUTPUT]

When the Auto Commit is set to False then also i am getting the same
results!!..
-- 
View this message in context: http://old.nabble.com/Java-executeBatch%28%29-behavior-tp27783879p27824321.h...
Sent from the monetdb-users mailing list archive at Nabble.com.

Re: [MonetDB-users] Java executeBatch() behavior

sateesh