Solved

Loading flat file data into a Java HashMap then populating database table

Posted on 2004-08-09
17
2,707 Views
Last Modified: 2012-06-27
Hi,

I wonder if an expert can help. Thanks in advance.

I'm constructing a java class to read data line by line from a flat file into a java HashMap. I then want to use the hashMap to
apply the data to some Oracle tables.  Some code is given below:


START OF JAVA CLASS:

  String desFolder = "/home/dev/data";

  BufferedReader inString = new BufferedReader(new FileReader(desFolder  +  "instrument_" + sDate + "_v1.dat" ));

 //read ahead

  if      (inString.readLine() == null)

  {  error....
  }
 else
 {
      while  ( (str= inString.readLine()) != null)
    {
         String  testString = str.substring(0,7);
         if (testString.compareTo("! ERROR") != 0)
         { //9
               String[] fields = str.split("\\s*,\\s*");
              String inst_num = fields[0];
              String inst_price=fields[1]'
              .
              .
              //AT THIS POINT LOAD inst_num  and all other fields picked up in this row into a Java HashMap.
         }

   }

  // Now that we have loaded a Java HashMap with non error data want to use the HashMap to update
    ORACLE Price tables (one transaction)

  For each row in hashMap:

       UPDATE Price
       SET     price = inst_price (from hash map)
   
       UPDATE PriceHistory
       SET     price = inst_price (from hash map)

   End

   COMMIT TRANSACTION

END OF JAVA CLASS:
       
0
Comment
Question by:azsat
  • 6
  • 4
  • 4
  • +3
17 Comments
 
LVL 6

Expert Comment

by:rpnman
ID: 11754670
First of all it is not clear if you have a sepcific question... Just posting code and asking for "help" is not a good start. What problems are you struggling with? What problems do you have when you implement? Are you just looking for comments on your approach? ... Ask yourself: how will "an expert" know they have earned the points you've put up?

Youve posted a bit of Java-esque pseudo code and some SQL-esque pseudo code. What is your goal in posting?

Having said all that, I will  comment on your approach anyway.

Clearly you should store some object for the entire row and not each field separately as your comment might seem to indicate.  Otherwise you're in danger of losing the record integrity or making it wierdly complicated to maintain it.



0
 
LVL 13

Expert Comment

by:Webstorm
ID: 11754936
Hi azsat,

>>    //AT THIS POINT LOAD inst_num  and all other fields picked up in this row into a Java HashMap.
or you can directly update the database. you can build your request from this point instead of using a HashMap. Or you should use a Vector (of String[]) instead of HashMap, because you use in a loop.

0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 100 total points
ID: 11755290
You'd probably be much better off using a PreparedStatement:


PreparedStatement ps = conn.prepareStatement("update XTABLE set a = ?, b = ?, c = ?");
while  ((str = inString.readLine()) != null)
{
      String  testString = str.substring(0,7);
      if (testString.compareTo("! ERROR") != 0)
      { //9
            String[] fields = str.split("\\s*,\\s*");
            ps.setString(1, fields[0]);
            ps.setString(2, fields[1]);
            ps.setString(3, fields[2]);
      }
// executeUpdate
}      .
0
 

Author Comment

by:azsat
ID: 11755310
Hi,

I'm looking for a Java solution to perform the described functionality.

 rpnman, I've written bits in Java and bits an SQL et al because I don't know the Java and hence coming here to ask the question!

I do know how to read the flat file in Java, what I do not know is how to put the read in fields into a suitable data structure,
I thought a hashMap may do it.

Also I don't know how to use the data now held in the hashMap to update oracle tables in Java. Each row in the built up hashMap will have to be applied to a table.  Certain of the fields will be applicable to certain tables.

Hope this clarfies - thanks.

Of course reading into a hashMap may not be the best way to do it - I,m listening......

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11755343
Why do you need to read it into a HashMap first?
0
 
LVL 2

Expert Comment

by:jrocharodrigues
ID: 11758605
Is your app a multithreaded one? If so, then you should synchronize your HashMap or try Hashtable instead.
And I suggest you create a new object to handle your file content. This is much more maintainable and should not produce overbloated code (dont forget to sync it if you app is multithreaded).


0
 
LVL 92

Expert Comment

by:objects
ID: 11758668
How does the data in the flat file need to be used to construct the query, ie. what updates do you need to do, and what parameters are in the flat file?
0
 

Author Comment

by:azsat
ID: 11760246
Hi,

I iterate, using a HashMap may not be the best way to do it! Let me expand on Objects Questions:

I need help with the java class to do the following task.

1. Read into memory from an Oracle table a list of active stocks.
2. Read in a flat file line by line and store each row in memory. The flat file contains the stock and the latest price and date.
3. As each line is read compare to see if it is active or not. If not active reject.
4. If active, store the stock and its price plus date in memory.
5. Continue until all stocks read from flat file.
6. Write the Stock and prices held in memory to the STOCK_PRICE table.
7. Write the stock date to the STOCK_HISTORY table.

The flat file contains about 2500 rows with 3 three fields stock,price and date. Only about 400 of them will be active.
There is no requirement to held the flat file data in a DB.

As I said, I do know how the read in the flat file and split up the individula fields .  I'm not sure what data structure to use
and how to manipulate it to update the DB tables.

Hope this clarifies!

azsat
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 92

Expert Comment

by:objects
ID: 11760257
is this a duplicate q?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11760888
>>3. As each line is read compare to see if it is active or not. If not active reject.

What determines whether active?
0
 

Author Comment

by:azsat
ID: 11763921
Hi,

What determines whether active? -  this would be done by comparing with the stocks read in at step 1 above.

As a a bit of background, the flat file I read from is FTP'd from another site onto my server. I've done a bit of research and have noted that the data from the other server can be read direct into memory without storing
on disk .

So I'll start again, what I need is java and java data structures for:  

1.  A streaming FTP connection to a remote file.
2. Read into memory from an Oracle table a list of active stocks (to compare each stock with the instream stock see 3).
2. Read in from FTP stream  data containing a stock and the latest price and date.
3. As each row is read compare to see if stock it is active or not. If not active reject.
4. If active, store the stock and its price plus date in memory.
5. Continue until all stocks read from FTP stream.
6. Write the Stock and prices held in memory to the STOCK_PRICE table.
7. Write the stock date to the STOCK_HISTORY table.

That's about it.

Regards.

0
 
LVL 92

Expert Comment

by:objects
ID: 11768562
Do you rstock objects have a key that identifies them?  If so, then use that to store the list of active stocks in a map (eg. HashMap). (If you don't actually need the stock details, and just need to know if active or not then you could just store the key in a set)
Then open your ftp stream, and for each line parse it, and check against active.
If active then you can store the details in a map, or you could perform your db update at that point.
If you can post details of the data/updates required I can help you with implementing the jdbc code.
0
 

Author Comment

by:azsat
ID: 11770061
Hi Objects,

Thanks for that.  

Yes, there are 3 filelds stock price and date. The stock is a unique filed and serves as a key.

Yep, 'll be storing the active stocks in the DB into a hashmap to compare with the values read in.

OK, you mention storing the stock price and date (proved to be active) in a MAP - please give the the java for this.

Finally, after the MAP has been built up of valid stocks/price/date read in:

    Write the Stock and prices held in MAP to the STOCK_PRICE table.
  Write the stock and  date held in the MAP  to the STOCK_HISTORY table. (jbdc code here)

Regards

azsat



0
 
LVL 92

Accepted Solution

by:
objects earned 400 total points
ID: 11770093
> OK, you mention storing the stock price and date (proved to be active) in a MAP - please give the the java for this.

Once you have your result set:

Map active = new HashMap();
while (rs.next())
{
   String stock = rs.getString("stock");
   int price = rs.getInt("price");
   Date date = rs.getDate("stock");
   active.put(stock, new Stock(stock, price, date));
}


I'm just geussing what your data types are so you may need to change that.
And I've used a Stock class to storet the details for a stock, if you don't have a similiar class then you'll need to create one.
0
 
LVL 92

Expert Comment

by:objects
ID: 11770104
here's an example of how to insert rows into database:

http://www.javaalmanac.com/egs/java.sql/InsertPs.html
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11775628
8-)
0
 
LVL 92

Expert Comment

by:objects
ID: 11779251
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
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…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

707 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