Insert array data into database using Java

Question

I have problem with this code..I want to extract data from flat file and store it into database. flat file format is like this:-

DT|00000001|TMDWH|UNIFI|00380520160|MAH SIEW YIN|11  |JALAN PP 2/8|TAMAN PUTRA PRIMA|PUCHONG|SELANGOR|47100|MALAYSIA|801110-14-5498||||||VOBB||A||11|JALAN PP 2/8|||TAMAN PUTRA PRIMA

DT|00000002|TMDWH|UNIFI|00322012091|JUNITA BINTI JAMAL|6 10 KONDOMINIUM FAJARIA|JALAN PANTAI BARU|KUALA LUMPUR|KUALA LUMPUR|WILAYAH PERSEKUTUAN|59200|MALAYSIA|800129-09-5078||||||VOBB||A|||JALAN PANTAI BARU|6|KONDOMINIUM FAJARIA|KUALA LUMPUR

Code:

public void massageData(String tmp) {

        String RecordType = "";
        String RecordNumber = "";
        String sourceSystemId = "";
        String targetSystemId = "";
        String TelNo = "";
        String Name = "";
        String Addr1 = "";
        String Addr2 = "";
        String Addr3 = "";
        String TownCity = "";
        String State = "";
        String PostalCd = "";
        String Country = "";
        String NewICNo = "";
        String OldICNo = "";
        String PassportNo = "";
        String BRN = "";
        String Latitude = "";
        String Longitude = "";
        String ServiceType = "";
        String IndicatorType = "";
        //add
        String CreateDate = "";
        String Filler = "";
        String CRNL = "";

        String HouseNo = "";
        String LotNo = "";
        String StreetName = "";
        String AptNo = "";
        String BuildingName = "";
        //add
        String LowID = "";
        String HighID = "";

        String SectionName = "";

        tmp = tmp.replace("\""," ");            // remove " with blank
        tmp = tmp.replace("\'","\'\'"); 
        String[] recArray = tmp.split("\\|");

        RecordType = recArray[1].trim();    
        RecordNumber = recArray[2].trim();
        sourceSystemId = recArray[3].trim();
        targetSystemId = recArray[4].trim();
        TelNo = recArray[5].trim();
        Name = recArray[6].trim();
        Addr1 = recArray[7].trim();
        Addr2 = recArray[8].trim();
        Addr3 = recArray[9].trim();
        TownCity = recArray[10].trim();
        State = recArray[11].trim();
        PostalCd = recArray[12].trim();
        Country = recArray[13].trim();
        NewICNo = recArray[14].trim();
        OldICNo = recArray[15].trim();
        PassportNo = recArray[16].trim();
        BRN = recArray[17].trim();
        Latitude = recArray[18].trim();
        Longitude = recArray[19].trim();
        ServiceType = recArray[20].trim();
        IndicatorType = recArray[21].trim();
        //add
        CreateDate = recArray[22].trim();
        Filler = recArray[23].trim();
        CRNL = recArray[24].trim();
        //

        HouseNo = recArray[25].trim();
        LotNo = recArray[26].trim();
        StreetName = recArray[27].trim();
        AptNo = recArray[28].trim();
        BuildingName = recArray[29].trim();
        //add
        LowID = recArray[30].trim();
        HighID = recArray[31].trim();
        //
        SectionName = recArray[32].trim();

        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement stmt = null;
        logger.info("masuk messageData");
        // get actual telephone number
        String actualMSISDN = parseMSISDN(TelNo);

            String [] aNo = getAreaCode(actualMSISDN).split("\\|");
            String iCtr = getiCtr(actualMSISDN);
            iCtr = recArray[0].trim();
            String stateCode = lookupStateCode(State);
            String  sQuery = "insert into DATA_999 (ID,RecordType,RecordNumber,SourceSystemApplicationId,TargetApplicationId,TelNo,Name,HouseNo,StreetName,AppartmentSuite,TownCity,State,PostalCode,Country,NewIC,OldIC,PassportNo,BRN,LatitudeDecimal,LongitudeDecimal,ServiceType,IndicatorType,CreateDate,Filler,Cr_Nl,HouseNo_New,LotNo_New,StreetName_New,AptNo_New,BuildingName_New,LowIDRange,HighIDRange,SectionName) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try {   
                conn = ds.getConnection();
                // insert post process data to data_999 table
                logger.info("start Query");

                stmt = conn.prepareStatement(sQuery);
                stmt.setString(0,iCtr);
                stmt.setString(1,RecordType);
                stmt.setString(2,RecordNumber);
                stmt.setString(3,sourceSystemId);
                stmt.setString(4,targetSystemId);
                stmt.setString(5,TelNo);
                stmt.setString(6,Name);
                stmt.setString(7,Addr1);
                stmt.setString(8,Addr2);
                stmt.setString(9,Addr3);
                stmt.setString(10,TownCity);
                stmt.setString(11,State);
                stmt.setString(12,PostalCd);
                stmt.setString(13,Country);
                stmt.setString(14,NewICNo);
                stmt.setString(15,OldICNo);
                stmt.setString(16,PassportNo);
                stmt.setString(17,BRN);
                stmt.setString(18,Latitude);
                stmt.setString(19,Longitude);
                stmt.setString(20,ServiceType);
                stmt.setString(21,IndicatorType);
                //add
                stmt.setString(22,CreateDate);
                stmt.setString(23,Filler);
                stmt.setString(24,CRNL);
                //
                stmt.setString(25,HouseNo);
                stmt.setString(26,LotNo);
                stmt.setString(27,StreetName);
                stmt.setString(28,AptNo);
                stmt.setString(29,BuildingName);
                //add
                stmt.setString(30,LowID);
                stmt.setString(31,HighID);
                //
                stmt.setString(32,SectionName);

                //stmt = conn.prepareStatement(sQuery);
                int dbStat = stmt.executeUpdate();
                conn.close();
            } catch (SQLException s){
                logger.error(s.getMessage());
            }
            finally {
                try {if (stmt != null) stmt.close();} catch (SQLException e) {}
                try {if (conn != null) conn.close();} catch (SQLException e) {}
            }

I really2 hope anyone here can help me.

Current result:

No data store into database, the code was successfully compiled!

Expected result

All the data will store into database DATA_999.

You should post here an exception you get (instead of just logging the message, log the whole exception in logger.error(s.getMessage()) — Mark Bramnik
– Mark Bramnik, Commented Feb 2, 2012 at 7:05
Dear Mark Brammik, I am sorry because tag javascript..huhu..erm, I didnot get any exception. The log doesnot show anything. That is my big problem now. That is why I don't know what is going on and what is the problem with this code. — Siti
– Siti, Commented Feb 2, 2012 at 7:16

Bohemian · Accepted Answer · 2012-02-02 07:30:45Z

1

The SQL API, unlike every other java API I can think of which is zero-based, is one-based - meaning it starts counting from one. Your code is trying to set the zeroth field, which should be exploding.

As a side note, because there's hardy any special processing for each field, you could replace all that code with just a few lines by simply iterating over the fields and setting the stmt params - ie don't use variables for each field:

// fyi, the regex of this split trims automatically
String[] fields = tmp.replace("\""," ").replace("\'","\'\'").trim().split("\\s*\\|\\s*");
// Do any special field processing (most need none)
field[0] = getiCtr(parseMSISDN(field[5])); // for example - just do what you need
// Now set all the SQL params
int col = 0;
for (String field : fields) {
    stmt.setString(++col, field); // Note: SQL API is 1-based (not zero-based)
}

edited Feb 2, 2012 at 7:30

answered Feb 2, 2012 at 7:13

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Siti Over a year ago

Dear Bohemian, thank you for your suggestion but could you please tell me why the data cannot store into database?..what is the problem with this code?

Bohemian Over a year ago

@Siti See comments in my code: // Note: SQL API is 1-based (not zero-based). There is no field 0, it starts counting from field 1

Siti Over a year ago

Dear Bohemian, thank you for the info. I have 1 question. How to get length from each array. field[5].length?

Bohemian Over a year ago

field[5] is just a String, so field[5].length() would be its length

Siti Over a year ago

Dear Bohemian, I got 0 for the length..Something goes wrong maybe.what should I do?

hage · Accepted Answer · 2012-02-02 07:16:37Z

0

Indexes for prepared statements are 1-based:

Change stmt.setString(0,iCtr); to stmt.setString(1,iCtr);. (And adjust the following)

And please post the exception you get. It will give us more hints what might went wrong

EDIT:

Are all fields in your table of type varchar? There are values in your lines that might be modeled as ints.

edited Feb 2, 2012 at 7:16

answered Feb 2, 2012 at 7:08

hage

6,1933 gold badges36 silver badges46 bronze badges

7 Comments

Siti Over a year ago

Dear Hage, I already change it but the result is still same. The data is not store into DATA_999. any error in my code?..I really need help on this matter.. really appreciate it..:)

hage Over a year ago

What DBMS do you use? Are your running the program from command line? Maybe you can print the exception with s.printStackTrace(System.out); ?

Siti Over a year ago

Dear Hage, what is the different between print StackTrace with the logger.info from my code?

Siti Over a year ago

Dear Hage, I am using Sql plus.

hage Over a year ago

I don't know what your logger is... And printStackTrace prints the full stack trace (i.e. all method calls) instead of just the exception message. Also, does the message No data store into database,... come from SQL Plus or is it generated somewhere in your code?

|

Collectives™ on Stack Overflow

Insert array data into database using Java

2 Answers 2

5 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related