?
Solved

using some values from a txt file and comparing info from DB and updating the DB

Posted on 2005-04-27
4
Medium Priority
?
183 Views
Last Modified: 2010-03-31
Hi

''  I only have these many points left ''
I want to
    read a property file
    read an input txt file
    connect to the DB and load values into a collection
    compare the value with the txt file and if OK update a table and if not Ok update another table.
 I need to run it on Unix with Oracle DB

Say  

I would like to know how to load these property file say p.prt which has the below values
JDBC=true
JDBC_URL=jdbc:oracle:peter:@peter:9999:orainstpeter
JDBC_DRIVER=oracle.jdbc.driver.OracleDriver
UNAME=peter      
PWD=peter
DB_UNAME=peter
DB_PWD=peter
LKL=true
LOGPATH=/logtest/log/
INPUTDIR=/input/
STDFILE =/std/

then read a file say test.txt from an input dir compare it with a std .xls file from the std dir for spaces and types

say test.txt file
John                         9999 22 USA     100.00
Kim                           8888 21  UK      800.00  
     
say std.xls file is as follows
description size  type
Name        30     A
id               4      A
age             3     A
country        3     A
amount      10     N

'A' for alphanumeric and 'N' for Numeric

and if the entire file pattern is not OK it should write to a log file and if OK for spaces and types then the log file should be updated as the file is OK  then

 read each record from the text.txt file  

and
connect to the ORACLE DB using the above property file

and read some values into a collection say
'select m.id , m.age , a.country from member m , address a where m.id = a.id'

and then compare the collection DB values with the test.txt record values for id, age and country and if the test.txt has a record which matches then

update  a  table uptblMatch with all the values which are matching from the  test.txt file

and the records which don't match I have to update in another table uptblMismatch in DB.
0
Comment
Question by:Richards
  • 2
4 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 13879620
And your question is ..?
0
 
LVL 16

Expert Comment

by:gnoon
ID: 13882573
To read property file for easy way, you can use java.util.Properties class.

  // Create Properties object
  java.util.Properties prop = new java.util.Properties();
 
  // Load property list from file into the Properties
  prop.load(new FileInputStream("p.prt"));

  // Get properties
  String inputDir = prop.getProperty("INPUTDIR");
  String stdDir = prop.getProperty("STDFILE");

To read a text file

  String filePath = inputDir + "test.txt";
  BufferedReader br;
  try
  {
    br = new BufferedReader( new FileReader( filePath ) );
    String line;
    while( ( line=br.readLine() ) != null )
    {
      // Process current line
    }
    br.close();
  }
  catch(IOException e)
  {
    try{ br.close(); }catch(Exception ex){}
    e.printStackTrace();
  }

To read excel file, you need to use Third Party package because it's not included in standard java package.
One of them is POI-HSSF project, you can download it for free here http://jakarta.apache.org/poi/hssf/index.html.

And here is where you can get tutorial about how to connect to Oracle, and do select, insert, update delete
http://www.javaalmanac.com/cgi-bin/search/find.pl?words=java.sql
0
 

Author Comment

by:Richards
ID: 13887656
Thanks !
How do we go around using the record set values stored in say result and then compare it with the entire input file data and if found and OK I need to update a table say  uptblMatch and if the input file record does not match any of the result then I need to update another table say  uptblMismatch

and read some values into a collection say
sqlquery = 'select m.id , m.age , a.country from member m , address a where m.id = a.id',

result = executeSQL(sqlquery);
0
 
LVL 16

Accepted Solution

by:
gnoon earned 500 total points
ID: 13891761
To compare file and db, I would suggest you to read each line of file, then get a field of that line to search in db because seaching in db is better performance than searching in file.

  String filePath = inputDir + "test.txt";

  // Create statement object from database connection
  Statement stmt = conn.createStatement();
  ResultSet rs = null;

  BufferedReader br;
  try
  {
    br = new BufferedReader( new FileReader( filePath ) );
    String line;
    String[] array = null, sql = null;
    while( ( line=br.readLine() ) != null )
    {
            array = line.split("\s"); // [0:name, 1:id, 2:age, 3:country, 4:amount]

            // Use 'id' field to search in db
            sql = "select * from member where id="+array[1];
            rs = stmt.executeQuery(sql);
            boolean found = rs.next();
            rs.close();

            if(found)
            {
              // found id in db, update uptblMatch
            }
            else
            {
              // not found id in db, update uptblMismatch
            }
    }
    br.close();
    stmt.close();
  }
  catch(Exception e)
  {
    try{ br.close(); }catch(Exception ex){}
    e.printStackTrace();
  }
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

839 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