Solved

Trouble with Spring batchupdate with List of HashMaps

Posted on 2013-01-10
7
2,767 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 36

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 36

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
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 

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 36

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 36

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

691 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