• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2815
  • Last Modified:

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

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
azsat
Asked:
azsat
  • 6
  • 4
  • 4
  • +3
2 Solutions
 
Roger NeymanRetired Software Engineer, Author, TeacherCommented:
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
 
WebstormCommented:
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
 
CEHJCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
azsatAuthor Commented:
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
 
CEHJCommented:
Why do you need to read it into a HashMap first?
0
 
jrocharodriguesCommented:
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
 
objectsCommented:
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
 
azsatAuthor Commented:
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
 
objectsCommented:
is this a duplicate q?
0
 
CEHJCommented:
>>3. As each line is read compare to see if it is active or not. If not active reject.

What determines whether active?
0
 
azsatAuthor Commented:
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
 
objectsCommented:
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
 
azsatAuthor Commented:
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
 
objectsCommented:
> 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
 
objectsCommented:
here's an example of how to insert rows into database:

http://www.javaalmanac.com/egs/java.sql/InsertPs.html
0
 
CEHJCommented:
8-)
0
 
objectsCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now