Problem while iterate in Two loops

hi all,
         in reference to my earlier post,  your comment "
         Additionally u can pass all ids as a string to the above qery and take the output in a Map and then iterate in a single loop. "
        Can you please let me know how can i do that ?

Awaiting your expert comment...
NASEEMAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Murali MurugesanFull stack Java developerCommented:
Something like this,

your value in variable tprID and seq_nr should be like this,

tprID : 'MN4676','4567TR','TU36363'
seq_nr: '2','4','6'  -- (i guess values should be unique since sequence number)

public Map getLogCounter(String tprID,String seq_nr) {
                                    Map counterMap = new HashMap();
                                     String sql="SELECT opn_seq_nr,COUNT(*)"
                                                       +" FROM   log"
                                                       +" WHERE  opn_id IN ("+tprID
                                                       +") AND opn_flag ='TPR'"
                                                       +" AND opn_seq_nr IN ("+seq_nr
                                                         +") AND upd_flag  IS NULL GROUP BY opn_seq_nr ";      
                               //PreparedStatement - implement this
                                         //resultset - implement this

                                   while(rs.next()){
                                          counterMap .put(rs.getString(1),rs.getInt(2));
                                    }      
                              return counterMap ;                              
                              }


Map values after iteration should look like below,

2  20
4  60
6  90


Now get the map values and get the count for each sequence number and iterate the second loop.

-Murali*

0
NASEEMAAuthor Commented:
Hi,
  Sorry for ver late reply.
   seq_nr is not unique. and i feel i couldnt be able to implement code.is  There any other possibilty in achieve my task
 
0
NASEEMAAuthor Commented:
HI All,
        Any other possibility in achieve my task..


0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Murali MurugesanFull stack Java developerCommented:
is your tprID is unique? If not then replace the select as follows,

SELECT opn_seq_nr || '~' ||opn_id,COUNT(*)

Now a combination of sequence and tprID  will be unique. Now u can implement the above code.

Even if this combination is not unique i would suggest u to use a bean class object (i hope u already have one) as the value and key for the map as some unique running number.

I feel this code would reduce you most of the delay and also we need to see if we can refactor the next loop as well once u implement this.

-Murali*
0
Murali MurugesanFull stack Java developerCommented:
did u try to implement this function ? or still facing problem in implementing it?
0
NASEEMAAuthor Commented:
Hi,
   i  couldnt be able to implemnt your last input.
  i appreciate if you can write the whole code so that i will copy and paste the code .
  sorry that i am asking u to write the code.

0
Murali MurugesanFull stack Java developerCommented:
I think its a very simple implementation. Here you go,


                   public Map getLogCounter(String tprID,String seq_nr) {
                                    Map counterMap = new HashMap();
                                     String sql="SELECT opn_seq_nr || '~' ||opn_id,COUNT(*)"
                                                       +" FROM   log"
                                                       +" WHERE  opn_id IN ("+tprID
                                                       +") AND opn_flag ='TPR'"
                                                       +" AND opn_seq_nr IN ("+seq_nr
                                                         +") AND upd_flag  IS NULL GROUP BY opn_seq_nr ";      
                                       Connection conn = ConnectionManager.getConnection();
                                       PreparedStatement pstmt = null;
                                       ResultSet rs= null;

                                      try{
                                               pstmt = conn.prepareStatement(sql);
                                               rs = pstmt.executeQuery();
                                                while(rs.next()){
                                                        counterMap .put(rs.getString(1),rs.getInt(2));
                                                 }      
                                         }catch(Exception e){}
                                         finally{
                               try {
                        if (!conn.isClosed()) {
                              ConnectionManager.releaseConnection(conn);
                        }
                                 
                         } catch (SQLException e1) {
                        e1.printStackTrace();
                         }
                          }                                                                            
                              return counterMap ;                              
                              }

Now u will get a map with values as ,

2~MN4676   20
4~4567TR    60
6~TU36363  90


Now iterate the map and get the count and use it in the next loop, thereby first loop can go away.
Hope u will try and do the remaining part.

-Murali*

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NASEEMAAuthor Commented:
HI,
  Thanks a lot for the help.i will accept ur comment .I appreciate if you can let me know the remaining code too....

sorry again to request to write code for me....:
0
Murali MurugesanFull stack Java developerCommented:
Sorry Naseema, this time you need to give your best try and make up the code on your own.
You do the code and fix the bugs and if you are caught in somewhere we might guide you.

Out of the two for loops i have showed you how to minimize one loop. You get the count from this and iterate the next loop.

Thanx
-Murali*
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.