Solved

Trouble with Spring batchupdate with List of HashMaps

Posted on 2013-01-10
7
2,452 Views
Last Modified: 2013-01-17
I am have trouble setting up a batch update using Springs JDBCTemplate.  Here is the simple/basic scenario….  I have a ArrayList of HashMaps to update the DB with. Is there Spring call for batch update that will handle a HashMap? Have not been able to find a spring example that applies to my scenario?

Thanks
0
Comment
Question by:West100
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:mccarl
ID: 38766032
There is no method that accepts a List of Maps but there IS a method that accepts an ARRAY of Maps. So you should be able to either call .toArray() on your list and pass that in or at least iterate through your list and store each Map into an array and pass that to the batchUpdate() method.

If you aren't quite following the above, then post some code and it will be easier to explain the process!
0
 

Author Comment

by:West100
ID: 38778693
Ok here it goes...... the following is a sample method in a DAO class to handle the batchUpdate:

public int[] batchUpdate(final List<LinkedHashMap>hMapList){

String sql ="INSERT INTO TEST_INSERT_TABLE " +
        "(ID_NUMBER,OBJECT_TYPE,UUID,VERSION_NUMBER,CONTENT_TYPE_IDS, " +
        "EN_CREATED_DT,DESCRIPTION,DISPLAY_NAME,EN_MODIFIED_DT,USER_NAME, " +
"TYPE_ID,USER_ID,CONTENT_TYPES,MEMBER_COUNT,GROUP_TYPE,FOLLOWER_COUNT,VIEW_COUNT) " +  "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(hMapList.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql,batch);

 return updateCounts;

}

Open in new window


To provide you with more details …. as you might remember from a previous post regarding using Springs RestTemplate I needed to consume REST web services with XML & JSON content and update a Oracle table with that content.

Now to make it a bit more interesting ….. One of the requirements for this app was to architect it in a way that it could handle running against different Web Services with differing content, updating different tables without having to recompile new objects for new content, that a properties/configuration file could drive that type functionality. This particular properties file includes …. param's that include sql insert statements(For simplicity the above code sample does not use the properties file insert statement) and a parm which holds an array of elements that represents the web services elements.

Here is a sample of that parm in the properties file:
#WebService Content Node Elements
rest.service.node.elements.xml=ID,objectType,uuid,version,contentTypesIDs,creationDate,description,displayName,modificationDate,name,typeID,userID
rest.service.node.elements.json=id,type,creationDate,description,displayName,modificationDate,name,contentTypes,memberCount,groupType,followerCount,viewCount

Open in new window


The Insert statements columns match the array of elements expected in the web service content. So to continue with a generic as possible approach as the content was consumed using the XMLDOM approach instead of loading an List of Objects I am loading an array of linkedhashmaps based on the properties file parm that holds the array of elements found in the web service content.  Hopefully that is clear enough. By using parms in a properties file together with passing arg's into an app indicating content type(xml,json, etc..) when there are new web services with differing content to handle we can use/configure the properties file without needing for development/recompiling.

The namedParameterJdbcTemplate.batchUpdate(sql,batch) call above is inserting all nulls in to the table.  11 rows are being inserted into the table as expected, but all records/columns inserted are appearing as (null).  Not sure why?  I have not been able to resolve the issue yet. Are there any other better ways to approach handling a batch update with a List of LinkedHashMaps in Spring?  Any overall suggestions would be appreciated.

Thanks
0
 
LVL 35

Expert Comment

by:mccarl
ID: 38781033
The namedParameterJdbcTemplate.batchUpdate(sql,batch) call above is inserting all nulls in to the table.
This is because you are only using '?' as the placeholders in your SQL string. How does Spring know which parameter from each Map should go into which placeholder? (I know that you are using a LinkedHashMap which means that the entries have a definitive order BUT the method accepts ANY Map implementation, even ones that don't have any defined order)

For the above to work you need to modify the SQL statement to use "Named Placeholders" instead of the unnamed '?' placeholders. The names of the placeholders need to match the keys in your HashMaps. eg.
INSERT INTO table (ID, TYPE, DISPLAY_NAME) VALUES (:id, :type, :displayName)

Open in new window



On to the second part of your question, how do you make these queries from property files? What you wrote above is not 100% clear (at least to me) so I have made some assumptions, please correct me if they are wrong. You say that you have one entry in properties file for the INSERT SQL statement and at least one for the list of fields returned by the web service (and if there are more than one of those, you have a way of selecting that when you run the app, ie. content-type XML vs JSON), is that correct? In that case if you FULL example props file (reduced only in number of columns) is the below, or at least if the below would be acceptable...
#SQL statement
insert.sql=INSERT INTO table (ID, TYPE, DISPLAY_NAME)

#WebService Content Node Elements
rest.service.node.elements.xml=:id, :type, :displayName

Open in new window

Then all you have to do in your DAO is the following...
public int[] batchUpdate(final List<LinkedHashMap>hMapList){

String propFileInsertStatement = ""; // Somehow this gets loaded from the property file above
String propFileParamList = ""; // Same... this is from property file above

String sql = propFileInsertStatement +  " values (" + propFileParamList + ")";

int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql,hMapList.toArray());

 return updateCounts;

}

Open in new window

Note also, that I have removed the line that creates the SqlParameterSource[] as there is a "batchUpdate()" method that already does this, ie. you can just pass the Map[] "as is" as a parameter.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:West100
ID: 38784832
Below is the code(work around) that I put together yesterday that is working with unnamed placeholders '?' …..

   
public void batchUpdate(final List<HashMap>hMapList)
          {
       
        String sql ="INSERT INTO TEST_INSERT_TABLE " +
        "(ID_NUMBER,OBJECT_TYPE,UUID,VERSION_NUMBER,CONTENT_TYPE_IDS, " +
        "EN_CREATED_DT,DESCRIPTION,DISPLAY_NAME,EN_MODIFIED_DT,USER_NAME, " +
"TYPE_ID,USER_ID,CONTENT_TYPES,MEMBER_COUNT,GROUP_TYPE,FOLLOWER_COUNT,VIEW_COUNT) " +  
"values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
       
           
    int[] updateCounts =
                getJdbcTemplate().batchUpdate(sql,
                                              new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps,
                                          int i) throws SQLException {
                        HashMap map = new HashMap();
                        map = hMapList.get(i);
                        int x=0;
                        
                        for (Object key: map.keySet()) {
                            x = x +1;
                            ps.setString(x, (String)map.get(key));
                        } 

                    }

                    public int getBatchSize() {
                        return hMapList.size();
                    }
                });
           
               System.out.println("updateCounts = " +  updateCounts.length);
               
           }

Open in new window


Your assumptions are about correct. By passing a string (the content type) into the Main args ('XML' or 'JSON') at run time, I am able to use one of this strings to pull the appropriate values out of the properties file(sql.insert.xml or sql.insert.json) via concatenation ("sql.insert." + contentType), etc …

I have tried your suggestion which looks much leaner and maintainable etc. just have not been able to get this to work. Here is the error  that I am getting :

"No value supplied for the SQL parameter 'ID': Invalid property 'ID' of bean class [java.util.LinkedHashMap]: Bean property 'ID' is not readable or has an invalid getter method: Does the return type of the getter match the parameter type of the setter?"

Open in new window


I have checked the batch and it is populated as expected with property ID with a value etc… Not sure what is missing yet?


Have not been able proceed with testing though as the Web Service providers URL changed to a https URL.  Now I am getting the following error:
org.springframework.web.client.ResourceAccessException: I/O error on GET request for THE https:// site HERE :peer not authenticated; nested exception is javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated

Open in new window


Again I am using Springs RestTemplate and have been establishing connectivity to a http based site via a ContextAwareHttpComponentsClientHttpRequestFactory which extends
org.springframework.http.client.HttpComponentsClientHttpRequestFactory.  Not sure what needs to be in place for the httpclient to avoid this problem? Have you dealt with https:// URL's with Spring and a REST Template scenario?

Thanks
0
 
LVL 35

Accepted Solution

by:
mccarl earned 500 total points
ID: 38785903
Argh, I hate trying to use Java generics and arrays together... but Spring forces you. Anyway, change line 8 of the DAO code that I posted above to the following...
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql,hMapList.toArray(new Map[0]));

Open in new window

THe problem is that the ".toArray()" method with no arguments returns just a plain Object[], and so that line would have been giving you compile errors. So I am guessing that you went back to your original way of using SqlParameterSourceUtils.createBatch() which does have a method for handling Object[]'s but this method is NOT what you want because it creates BeanPropertySqlParameterSource's rather than MapSqlParameterSource's and hence why you got errors about 'Bean property XXX not set'.

As for HTTPS, I haven't done a great deal with it, but from the looks of it you may need to add an SSL certificate to your JRE's key chain. I don't remember the specifics of it, but that last sentence and your error message should have enough Googleable terms to get you a solution. Actually, just found *something* at this link. However, this totally disables certificate checking so maybe NOT what you want in anything production like, but for testing it may be enough.
0
 

Author Closing Comment

by:West100
ID: 38789450
Ok got it .... Yes I did try SqlParameterSourceUtils.createBatch() without any luck. By adding or changing the toArray call resolved the problem with that batchUpdate method.  I was able test with a string rather than wait to iron out the HTTPS issue.

I have found a couple of solid google leads regarding HTTPS. I have also posted that question separately on this site.   I have been able to determine based our specific needs that avoiding or disabling certificate checking will not be a problem.

Thanks again for the help.
0
 
LVL 35

Expert Comment

by:mccarl
ID: 38789924
Glad to help!!  ;)

I also posted a reply in your other question regarding the HTTPS issue.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now