Why is my Java program not updating the associated postgresql table?

Question

A problem with my code has me stumped. I created a Java Application to test out different ways to manipulate a row in a table in postgresql. I am able to connect to the database fine, but my problem arises when I attempt to update the table. Firstly, this is my table in postgresql after executing the query select * from "testTable" order by "p_id" ASC; :

             |name       |age        |p_id       |salary
              character   integer     integer    character varying(10)
1            |Bob        |25        |1       |20
2            |Mary       |22        |2       |
3            |Abraham    |55        |3       |22
4            |Ellen      |31        |4       |

What I would like to do is give a salary to Mary and Ellen. I use the Java code below to attempt to accomplish this task:

import java.util.ArrayList;
import org.postgresql.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.Arrays;
import java.util.regex.*;
import java.io.IOException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.text.ParseException;
import java.util.Date;
import java.text.NumberFormat;
import java.text.DecimalFormat;
import java.util.*;
public class Test
{
public static void main(String[] args) throws IOException
{
{
    String s1 = "";
    String s2 = "";
    String s3 = ""; 
    String s4 = "";
    try{
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/myapp5_development", "alex", "");
        Statement myStmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet myRs = myStmt.executeQuery("select \"salary\", \"p_id\" from \"testTable\" where \"salary\" is null order by p_id ASC");
        int counter = 0;
        String[] salaries = {"45", "17"}; 

        while (myRs.next())
        {
            s3 = myRs.getString("p_id");
            s4 = myRs.getString("salary");
            System.out.println("ID: " + s3 +", salary: " + s4);
            myRs.updateString("salary", salaries[counter] );
            System.out.println(salaries[counter]);
            System.out.println(counter);
            counter++;
        }

    }
    catch(Exception exc){
        exc.printStackTrace();
    }
}
}
}

The code in the "while" loop is suppose to go through each row in the table in which the "salary" value is null. It then populates the null value with a value from the "salaries" array. The output shows that the rows with a null salary value are being found in the loop, and that the counter is incrementing accordingly. The corresponding values in the "salaries" array are also being found based on the value of the counter. However, the rows themselves are not being updated. What should I do to have the rows of the table update properly? Here is the output from the console:

ID: 2, salary: null
45
0
ID: 4, salary: null
17
1

Here is the resulting table when the same SQL query is sent:

             |name       |age        |p_id       |salary
              character   integer     integer    character varying(10)
1            |Bob        |25        |1       |20
2            |Mary       |22        |2       |
3            |Abraham    |55        |3       |22
4            |Ellen      |31        |4       |

It seems as though you aren't committing the transaction.

Mureinik
– Mureinik

2015-08-04 11:05:41 +00:00
Commented Aug 4, 2015 at 11:05 — Mureinik
– Mureinik, Commented Aug 4, 2015 at 11:05

Robbenu · Accepted Answer · 2015-08-04 11:09:26Z

1

After using myRs.updateString("salary", salaries[counter] ); you should update the row by using updateRow() like this:

myRs.updateString("salary", salaries[counter] );
myRs.updateRow();

answered Aug 4, 2015 at 11:09

Robbenu

4134 silver badges13 bronze badges

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

2 Comments

SJuan76 Over a year ago

+1. From the javadoc

The updater methods do not update the underlying database; instead the updateRow or insertRow methods are called to update the database.

docs.oracle.com/javase/7/docs/api/java/sql/…

Robbenu Over a year ago

Thanks for the source addition :)

Collectives™ on Stack Overflow

Why is my Java program not updating the associated postgresql table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related