CODEWITHSUNDEEP
javamysql
Jury A
Jury A

Reputation: 20052

Repetition in updating rows in MySQL table

I am trying to update a column in already exists MySQL table. The values must be read from a text file, and then they must be inserted into the specified column.

I have tried the following:

int counter=0;
        while((fileLine=in.readLine())!=null) 
        { 
            System.out.println("Line read is: "+fileLine);

                //execute db insertion
                try {
                    //insert in the database
                    Query= "update db.table set col4=?";    //database
                    preparedStmt3 = DBConnection.con.prepareStatement(Query); 
                    preparedStmt3.setString (1, fileLine);
                    preparedStmt3.executeUpdate();
                    System.out.println("Complete update statement for row: "+counter);
                    counter++;
                } catch (Exception e) {
                    System.out.println("DB_Error:_"+ e.toString());
                }

        } //end while loop

I have tried to output fileLine value and it seems correct and change correctly with every loop round. But, after I run the program and check the database, I find that the value inserted is the last line only (and this repeated in all records, it is not what I am supposed to do, which is insert every line in records consequently). what is the cause for this problem ?

EDIT: The text file contains the following lines: aaa bbb ccc ddd eee fff

The run output after I added some printline statements to see debug is as the following:

enter image description here

while the DB contains the last line inserted only

enter image description here

Upvotes: 0

Views: 804

Answers (3)

PermGenError
PermGenError

Reputation: 46398

try this, 

Connection conn = ConnectionBean.getInstance().getDBConnection();
int counter=0;
String fileLine;
BufferedReader in;
String query1 = "select id from test"; // get all the id's from the table you wanna do  the update
ArrayList<Integer> al = new ArrayList<Integer>(); // store them in an arraylist
try {
PreparedStatement stmnt = conn.prepareStatement(query1);
ResultSet rs = stmnt.executeQuery();
while(rs.next()) {
    al.add(rs.getInt("id"));
}
}
catch(Exception ex) {
    ex.printStackTrace();
}

try {
    in = new BufferedReader(new FileReader("file1.txt"));
    int h=1;
    while((fileLine=in.readLine())!=null && al.size()>0)  
    {   

        String query="UPDATE chaitu.test SET col=? WHERE id="+h ;
        PreparedStatement statement = conn.prepareStatement(query);

        statement.setString(1, fileLine);
        statement.executeUpdate();
        h++;
    } //end while loo


} catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
    }

Upvotes: 0

Hitesh Jain
Hitesh Jain

Reputation: 408

you are using update query to insert values. Use

INSERT INTO db.table (col4) VALUES (?)

Upvotes: 0

SJuan76
SJuan76

Reputation: 24780

Your SQL does not add a WHERE clause to specify which rows to update. So, at every iteration, all the records in the column have their col4 field updated to the value. The last one is the update that remains.

Upvotes: 1

Related Questions