CODEWITHSUNDEEP
javaarraysdatabase
Nasir Shiraz
Nasir Shiraz

Reputation: 161

Only first row from array being added to database Java

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.

Upvotes: 2

Views: 102

Answers (2)

Arjit
Arjit

Reputation: 3456

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));
}

Upvotes: 0

jedison
jedison

Reputation: 976

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 ] + "')");
        }
    }

Upvotes: 2

Related Questions