0

I have Mysql table and I want to push returned values from query into java hashmap. I am very beginner in java. I took some help from available answers and wrote a code. Summary: I have list of country codes as connectedCountryList =[40,203,250,528] which should be the Key of the hashmap and I want to iterate over these country codes and store return values from sql table which has milions of rows. Mysql table sample data looks like-

utc                     date            area_in area_out    value
2015-12-05T03:00Z   05.12.2015 03:00    276     40          3059
2015-12-04T03:00Z   04.12.2015 03:00    276     40          2523
2015-12-07T03:00Z   07.12.2015 03:00    276     40          2873
2015-12-06T03:00Z   06.12.2015 03:00    276     40          3386
2015-12-01T03:00Z   01.12.2015 03:00    276     40          2936
2015-12-03T03:00Z   03.12.2015 03:00    276     40          2564
2016-04-01T19:00Z   01.04.2016 19:00    276     203         -291
2016-05-02T05:00Z   02.05.2016 05:00    276     203         -276
2016-05-03T05:00Z   03.05.2016 05:00    276     203         -123
2016-04-03T19:00Z   03.04.2016 19:00    276     203         -899
2016-04-02T19:00Z   02.04.2016 19:00    276     203         -1100
2016-05-01T05:00Z   01.05.2016 05:00    276     203         -45
2015-01-16T13:00Z   16.01.2015 13:00    276     203         -339
2016-04-05T19:00Z   05.04.2016 19:00    276     203         734
2015-01-17T13:00Z   17.01.2015 13:00    276     203         -300
2017-04-27T19:00Z   27.04.2017 19:00    276     250         -75
2016-09-25T23:00Z   25.09.2016 23:00    276     250         -174
2015-03-18T12:00Z   18.03.2015 12:00    276     250         338
2016-10-11T09:00Z   11.10.2016 09:00    276     250         1093
2015-03-17T12:00Z   17.03.2015 12:00    276     250         190
2017-05-24T05:00Z   24.05.2017 05:00    276     250         -454
2015-03-14T03:00Z   14.03.2015 03:00    276     528         3336
2016-05-09T00:00Z   09.05.2016 00:00    276     528         2705
2016-11-12T19:00Z   12.11.2016 19:00    276     528         3030
2015-03-15T03:00Z   15.03.2015 03:00    276     528         3182            
2016-10-15T05:00Z   15.10.2016 05:00    276     528         2111

Actually I have big code and this is a small part I am working on it. 

connectedCountryList =[40,203,250,528];
String tablename= "mytable";
MarketAreacode = "276"
    public class Exchangeflows{
        private String utc;
        private int value;
        public Exchangeflows(){}
        public Exchangeflows(String utc,int value ){
        this.utc =utc;
        this.value=value;
        }

        public String getutc(){
            return utc;
        }

        public void setutc(String UTC) {
            this.utc = UTC;
        }
        public int getvalue(){
            return value;
        }
        public void setvalue(int value) {
            this.value = value;
        }               
    }
final Map<Integer, Exchangeflows> CbExchangePower = new HashMap<Integer, Exchangeflows>();
        for (final Integer countryCode : connectedCountryList) {        
            final String sqlquery = "select * from "  + tableName +" where ((area_in = '" + marketAreaCode +"' "+" and area_out in ('"+countryCode+"'))"+ 
                                  "or (area_out = '"+marketAreaCode+"' "+"and area_in in ('" + countryCode +"'))) order by utc ASC ";       
            conn.setResultSet(sqlquery);
            // SQL query Executes perfect
            while (conn.resultSet.next()){   
                String utc = conn.resultSet.getString("utc");
                int value = conn.resultSet.getInt("value");
                PowerExchange = new Exchangeflows (utc, value);
                CbExchangePower.put(countryCode,PowerExchange);
            }

When I run my code it reads data correclty in each iteration but every time it overwrites the values and I am getting latest values according to UTC ASC as per sql query. e.g. [2015-12-07T03:00Z 07.12.2015 03:00 276 40 2873 ]. I do not understand how I should iterate my sql in a loop to save all content of table into hashmapin the form of <countryCode <utc, value>>. The hashmap sould iterate through list of countries, hence country code will be unique in each iteration as per for loop and have to add each row from sql table in while loop. Can any one suggest me any changes in my code?

mitedu
  • 49
  • 8
  • Instead of using HashMap `countryCode` you can use `countryCode`. So, that everytime you'll have a different key and the same value (that won't be overwritten) – Procrastinator Sep 14 '17 at 09:10
  • @procrastinator `>` is just my idea to save the data according to country code so that I will use this hashmap next time and use the hashmap in next step. `Map CbExchangePower = new HashMap();` is my hashmap. So how can I modify it ? – mitedu Sep 14 '17 at 09:16
  • I understood it incorrect. Seems like the HashMap `CbExchangePower ()` is having teh repeated keys and that's why it gets overwritten. You can fill it by swapping the keys and values accordingly. So, it should be `CbExchangePower`. – Procrastinator Sep 14 '17 at 09:23
  • So, to make it working the last statement should be changed from `CbExchangePower.put(countryCode,PowerExchange);` to `CbExchangePower.put(PowerExchange,countryCode);` – Procrastinator Sep 14 '17 at 09:24
  • @procrastinator I think It will not helpful because powerExchange is an object and countryCode is a key so if I change the position it throws me an error like `The method put(Integer, ExchangeNodal.Exchangeflows) in the type Map is not applicable for the arguments (ExchangeNodal.Exchangeflows, Integer)` – mitedu Sep 14 '17 at 09:32
  • Did you use the comment above as well? `CbExchangePower` – Procrastinator Sep 14 '17 at 09:35
  • @procrastinator Yes, I got your point and i implemented I guess It works fine now. But I have some doubts about use of this type of hashmap. I mean how can I use this hashmap if I want to iterate over key (now it will be Exchangeflows) if it is not a country code now (`connectedCountryList =[40,203,250,528]`) ? – mitedu Sep 14 '17 at 10:03
  • you can iterate over the values as well. The same way you are doing it for keys, you can change it to the values and get your List or Array of desired values. So, in this case `connectedCountryList = [40, 203, 250, 528]` – Procrastinator Sep 14 '17 at 10:07

3 Answers3

1

To avoid the overwriting you can swap the key-values with each other. So, your Hashmap would be CbExchangePower<Exchangeflows, Integer> and then to put the values from DB by CbExchangePower.put(PowerExchange,countryCode).

In order to read the Map according to the values, you can perform like following:

Map<Integer, List<Exchangeflows>> countryCodes = new HashMap<Integer, List<Exchangeflows>>();
       for(Entry<Exchangeflows,Integer> entry : CbExchangePower.entrySet()){
               List<Exchangeflows> list = new ArrayList<Exchangeflows>();
               if(countryCodes.containsKey(entry.getValue()))
                   list = countryCodes .get(entry.getValue());
               list.add(entry.getKey());
               countryCodes .put(entry.getValue(), list);
       }
       System.out.println(countryCodes);

This should be able to print the list with the desired values of yours.

Procrastinator
  • 2,526
  • 30
  • 27
  • 36
  • Thanks it works fine! I need to change my further code to use `countryCodes` hashmap. thanks a lot ! – mitedu Sep 15 '17 at 12:34
  • @ procrastinator A small question but it is very important. Although this code works fine, it doesn't save the data as I want in sql query. I noticed that it suppose to save according to ascending order of utc. Ex. If we looked for the data with country code `40`, it should save it like `2015-12-01T03:00Z 01.12.2015 03:00 276 40 2936 2015-12-03T03:00Z 03.12.2015 03:00 276 40 2564` and so on.... If I run with breakpoint it works in correct order but when I see final map `CbExchangePower` or `countryCodes`, it looks very different. What I suppose to do ? – mitedu Sep 19 '17 at 11:29
  • What is the end result of `CbExchangePower` and `countryCodes` could you please post it? – Procrastinator Sep 19 '17 at 11:52
  • `CBExchangePower gives [0] Key >> date= 04.12.2015 03:00 utc = 2015-12-04T03:00Z value=2523 Value >> 40 CountryCodes gives [0] Key >> 40 Value >> [0] date= 04.12.2015 03:00 utc = 2015-12-04T03:00Z value=2523` This is typical output structure. Return values in `CbExchangePower` are in same order as in `countryCodes` but it doesn't returns in ascending UTC. – mitedu Sep 19 '17 at 12:39
  • To sort you hashmap you can check [here](https://stackoverflow.com/questions/109383/sort-a-mapkey-value-by-values-java#answer-1283722) – Procrastinator Sep 19 '17 at 12:45
0

Behavior of hashmap is that if you try to insert the same key again then the value will be overridden.

I am not still clear on your requirement. Do you want to store the country code as key and the value as list from the sql output.

eg:

40            2015-12-05T03:00Z          3059
              2015-12-04T03:00Z          2523

more or less like grouping UTC and value by country code.

Prasanna
  • 1
  • yes exactly ! This is what I am expecting. So that i will get the UTC and values according to each country code `connectedCountryList =[40,203,250,528]` . – mitedu Sep 14 '17 at 09:26
0

Here's the pseudocode code which you have to implement:

1. Loop through country code list. 
2. Use stringbuffer to form the country code as comma separated values. 
3. Pass the string to SQL query. 
4. Iterate through the resultset. 
5. Pass the country code in CbExchangePower and get the value.  
6. If the value is null then    
        create a new list and add PowerExchange object 
   else     
        create a new PowerExchange object and add it to the existing list
Vlad
  • 18,195
  • 4
  • 41
  • 71
Prasanna
  • 1