Only first row from array being added to database Java

Question

I am trying to add an array to a database. I could get the first row into the database but it doesn't add the remaining rows.

Here is the values array string:

[1, Ola, Hansen, Timoteivn, Sandnes , 2, Tove, Svendson, Borgvn, Stavanger , 3, Kari, Pettersen, Storgt, Stavanger]

Here is my code to add:

try {
        BufferedReader br = new BufferedReader(new FileReader(
                "newoutfile.txt"));
        // String line;
        for (String line = br.readLine(); line != null; line = br
                .readLine()) {
            String newline = line.substring(2);
            String newline2 = newline.trim();
            String[] value = newline2.split(",");

            System.out.println("the array" + Arrays.toString(value));

            connection.createStatement().execute(
                    "insert into person10(personid,first_name,last_name,street,city)values('"
                            + value[0] + "','" + value[1] + "','"
                            + value[2] + "','" + value[3] + "','"
                            + value[4] + "')");
        }

I have been pulling my hair all day trying to get this to work. Any help would be great. Thanks.

Perhaps I'm blind, but where does it increment the line that the buffered reader is looking at? — DerStrom8
– DerStrom8, Commented Apr 18, 2014 at 19:56
How many lines do you have? Or is it all in one line as in your sample? — shree.pat18
– shree.pat18, Commented Apr 18, 2014 at 19:56
hi that's what I have been trying to do but I don't know how.. any suggestions — Nasir Shiraz
– Nasir Shiraz, Commented Apr 18, 2014 at 19:57
Try a while loop as Arjit suggested and see if it works better — DerStrom8
– DerStrom8, Commented Apr 18, 2014 at 20:01

jedison · Accepted Answer · 2014-04-18 21:53:54Z

2

Your values array string (the input file) should look like this:

[1, Ola, Hansen, Timoteivn, Sandnes] 
[2, Tove, Svendson, Borgvn, Stavanger]
[3, Kari, Pettersen, Storgt, Stavanger]

Then the reader and while (or for) loop will handle reading each line. Stripping the leading record number, splitting the string based on the ",", and then executing the createStatement.

If your values are in the file all on a single line:

[1, Ola, Hansen, Timoteivn, Sandnes , 2, Tove, Svendson, Borgvn, Stavanger , 3, Kari, Pettersen, Storgt, Stavanger]

Then you will need more logic to split the line into 3 separate sets of data.

If you have three separate lines of data in the file as shown at top (rather than a single line of data), then this code should work.

try {
    BufferedReader br = new BufferedReader(new FileReader( "newoutfile.txt"));
    // String line;
    while( ( line = br.readLine() ) != null ) {
        String[] value = line.trim().split(",");

        System.out.println("the array" + Arrays.toString(value));

        connection.createStatement().execute(
                "insert into person10(personid,first_name,last_name,street,city)values('"
                        + value[0] + "','" + value[1] + "','"
                        + value[2] + "','" + value[3] + "','"
                        + value[4] + "')");
    }

If all the data is on a single line in the file, then something like this.

try {
    BufferedReader br = new BufferedReader(new FileReader( "newoutfile.txt"));
    // String line;
    while( ( line = br.readLine() ) != null ) {
        String[] value = line.trim().split(",");

        System.out.println("the array" + Arrays.toString(value));
        int rows = ( value.length / 5);
        for ( int i = 0; i < rows; i++) {
           String personid = value[ i * 5 ];
           String first_name = value[ i * 5  + 1 ];
           String last_name = value[ i * 5 + 2 ];
           String street = value[ i * 5  + 3 ];
           String city = value[ i * 5  + 4 ];
           connection.createStatement().execute(
                "insert into person10(personid,first_name,last_name,street,city)values('"
                        + personid + "','" + first_name + "','"
                        + last_name + "','" + street + "','"
                        + city + "')");
        }
    }

I am SURE that someone can improve on that ugly for loop, but let's call it "good enough". Without the variables personid, first_name, last_name, street, city: try { BufferedReader br = new BufferedReader(new FileReader( "newoutfile.txt")); // String line; while( ( line = br.readLine() ) != null ) { String[] value = line.trim().split(",");

        System.out.println("the array" + Arrays.toString(value));

        connection.createStatement().execute(
                "insert into person10(personid,first_name,last_name,street,city)values('"
                        + value[0] + "','" + value[1] + "','"
                        + value[2] + "','" + value[3] + "','"
                        + value[4] + "')");
    }

If all the data is on a single line in the file, then something like this.

try {
    BufferedReader br = new BufferedReader(new FileReader( "newoutfile.txt"));
    // String line;
    while( ( line = br.readLine() ) != null ) {
        String[] value = line.trim().split(",");

        System.out.println("the array" + Arrays.toString(value));
        int rows = ( value.length / 5);
        for ( int i = 0; i < rows; i++) {
           connection.createStatement().execute(
                "insert into person10(personid,first_name,last_name,street,city)values('"
                        + value[ i * 5 ] + "','" + value[ i * 5  + 1 ] + "','"
                        + value[ i * 5 + 2 ] + "','" + value[ i * 5  + 3 ] + "','"
                        + value[ i * 5  + 4 ] + "')");
        }
    }

edited Apr 18, 2014 at 21:53

answered Apr 18, 2014 at 21:12

jedison

9766 silver badges16 bronze badges

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

11 Comments

Nasir Shiraz Over a year ago

im getting this the array[Ola, Hansen, Timoteivn, Sandnes ] java.lang.ArrayIndexOutOfBoundsException: 4 it missing the 1

jedison Over a year ago

You're missing the 1 due to this String newline = line.substring(2);

jedison Over a year ago

I doubt it. Open up the file "newoutfile.txt" and see what is inside. Everything on one line, or separate lines of data. If separate, then we can work with this. If not, then we are in trouble.

Nasir Shiraz Over a year ago

what ive done is outside the for loop +value[0] .. to value[14] and it adds all the records to the database but tis not the right way to do i need to loop through the array i think

jedison Over a year ago

See the latest version posted: i increments from 0 to (number of fields found in the input divided by 5). value[ i * 5 ], value[ i * 5 + 1 ], value[ i * 5 + 2 ], value[ i * 5 + 3 ], value[ i * 5 + 4 ]

|

Arjit · Accepted Answer · 2014-04-19 06:38:44Z

0

Instead of your for loop try this

while((line = (br.readLine())) != null) {
   System.out.println(line);
}

Edit (Try this):

String line = br.readLine(); //read the line in a string
String[] strArray = line.split("[\\d],"); //split a string with regex = integer followed by a comma

for (int i = 1; i < strArray.length; i++) { //start from 1 because zero index is empty
   String newline = strArray[i].substring(1);
   String newline2 = newline.trim();
   String[] value = newline2.split(",");
   System.out.println("the array" + Arrays.toString(value));
}

edited Apr 19, 2014 at 6:38

answered Apr 18, 2014 at 19:57

Arjit

3,4961 gold badge20 silver badges18 bronze badges

7 Comments

Nasir Shiraz Over a year ago

I originally had that code in there but that didn't work so i tried the for loop

Nasir Shiraz Over a year ago

Nasir Shiraz Over a year ago

it is at the start but when it gets to the for loop the lines have been taken out by code , and it ends up on 1 line is this br.readLine the problem ?

Arjit Over a year ago

i have tested that code with while loop ..its working for me..separate all the lines by enter key...

Nasir Shiraz Over a year ago

i cant physically edit the file i can only edit file with code , its a text file to start of with , which has to be formatted to be entered into a database

|

Collectives™ on Stack Overflow

Only first row from array being added to database Java

2 Answers 2

11 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related