Inserting two records at once results in two queries

[Sytematic]

Dear SmartGWT,

I run this (quite old I know) version of SGWT Power
v8.3p_2013-01-09/PowerEdition Deployment 2013-01-09

I use this in combination with MySQL.

If i want to insert a List of Maps, in this server-side code, it creates multiple queries, one for each record. Is it possible to create one SQL query inserting multiple records? I'm not interested in any client side interaction, or fetching the inserted id's; it runs in a cronjob.

DSRequest req = new DSRequest("ProductProperty","add");
req.setValues(propertyData); //propertyData = ArrayList of HashMaps
req.execute();

Why does this generate multiple insert queries? And how do I make sure it's just one big one?

[Isomorphic]

Presumably you mean statements of the form:

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

.. which are supported by some DB engines, with some issues and limitations (eg maximum 1000s rows, triggers don't execute normally, etc).

If this type of query is supported by your engine and none of the issue affect your usage, this sample shows how to directly execute SQL while still taking advantage of automatic transactions.

SQLDataSource.getSQLClause() may allow you to have SmartGWT generate some parts of the statement for you as well.

[Sytematic]

Thanks, that looks like what I need.

One follow-up question:

If i want to run this from a unit test,how can i get the Connection object?

Ie, in the following code, the conn variable is null.

@Test
public void testBatchAdd() throws Exception {
	DSResponse resp = new DSResponse();
	DSRequest req = new DSRequest("ProductProperty", "add"); 
        req.setPartOfTransaction(true);  
	Connection conn = (Connection)((BasicDataSource)req.getDataSource()).getTransactionObject(req);  
        if (conn == null) {  
	        resp.setStatus(-1);  
	        resp.setData("No transaction to join.  Please make some changes before clicking " +  
	                         "the Save buttons"); 
	        throw new RuntimeException("connection is null");
	}  
	PreparedStatement stmt = conn.prepareStatement("INSERT INTO ProductProperty (SaasCustomer_id, propertyKey, propertyValue, Product_id )" +
				" VALUES (?, ?, ?, ? ), VALUES (?, ?, ?, ? ) ");  
	Map<String,Object> rec1 = new HashMap<String,Object>();
	rec1.put("SaasCustomer_id", 1);
	rec1.put("propertyKey", "aKey");
	rec1.put("propertyValue", "aValue");
	rec1.put("Product_id", 1);
	Map<String,Object> rec2 = new HashMap<String,Object>();
	rec2.put("SaasCustomer_id", 1);
	rec2.put("propertyKey", "aKey2");
	rec2.put("propertyValue", "aValue2");
	rec2.put("Product_id", 1);
	
	stmt.setInt(1, (Integer) rec1.get("SaasCustomer_id"));
	stmt.setString(2, (String) rec1.get("propertyKey"));
	stmt.setString(3, (String) rec1.get("propertyValue"));
	stmt.setInt(4, (Integer) rec1.get("Product_id"));
	
	stmt.setInt(5, (Integer) rec2.get("SaasCustomer_id"));
	stmt.setString(6, (String) rec2.get("propertyKey"));
	stmt.setString(7, (String) rec2.get("propertyValue"));
	stmt.setInt(8, (Integer) rec2.get("Product_id"));
		
        stmt.executeUpdate();  
        stmt.close();  
        resp.setStatus(DSResponse.STATUS_SUCCESS);  
}

[Isomorphic]

The automatic transactions system is not active in standalone mode (no servlet engine), because it creates transactions automatically based on the HTTP request lifecycle.

So this probably doesn't make sense as a unit test that is run without a servlet engine. You could of course get your own SQLConnection using standard Java techniques, but at that point you'd be testing something else entirely.

[Sytematic]

Okay, fair enough.

Yeah now for this particular cronjob query I just used normal Java means. Since it's an isolated cronjob it's not a problem that it isn't expressed in terms of SmartGWT.

Thanks for the advice.