org.postgresql.util.PSQLException: Bad value for type int : (simple JDBC insert)

Question

I'm trying to insert data through jdbc in my newly created table in postgresql which is this

public class Workers  {
private int id;
private String first_name; 
private String last_name;
private String state;
private Integer base_salary;

public void Workers(int id, String first_name,String last_name,String state,Integer base_salary) {
    this.id=id;
    this.first_name=first_name;
    this.last_name=last_name;
    this.state=state;
    this.base_salary=base_salary;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getFirst_name() {
    return first_name;
}

public void setFirst_name(String first_name) {
    this.first_name = first_name;
}

public String getLast_name() {
    return last_name;
}

public void setLast_name(String last_name) {
    this.last_name = last_name;
}

public String getState() {
    return state;
}

public void setState(String state) {
    state = state;
}

public Integer getBase_salary() {
    return base_salary;
}

public void setBase_salary(Integer l) {
    this.base_salary = l;
}

}

Postegres code:

create table worker
(
    first_name varchar(20) ,
    last_name varchar(20)  ,
    state varchar(20)  ,
    base_salary integer
);

Java:JDBC:

 String query ="INSERT into worker(first_name,last_name,state,base_salary) values (?,?,?,?);";
    ResultSet keys = null;

    try(
            Connection conn = DB.getConnection();
            PreparedStatement stmt = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
    ) {
        stmt.setString(1, worker.getFirst_name());
        stmt.setString(2, worker.getLast_name());
        stmt.setString(3, worker.getState());
        stmt.setInt(4, worker.getBase_salary());
        
        int affected= stmt.executeUpdate();
        
        if(affected==1) {
        keys=stmt.getGeneratedKeys();
        keys.next();
        int newKey = keys.getInt(1);
        worker.setId(newKey);
        } else {
            System.out.println("No rows affected please try again");
            return false;
        }
    } catch (SQLException e) {
        System.err.println(e);
        return false;
    } finally {
        if(keys !=null) keys.close();
    }

    return true;
}

Main class code:

   Workers worker = new Workers();
    
   EmployeesManipulation.displayAllRows();
    
   worker.setFirst_name(InputHelper.getInput("User name: "));//First_name is String
   worker.setLast_name(InputHelper.getInput("Last name: "));//last_name is String
   worker.setState(InputHelper.getInput("State: "));// State is String
   worker.setBase_salary(InputHelper.getIntegerInput("base Salary:"));//Base_salaryInteger

Output explanation:

My input:

User name:  Fares
Last name:  Essayeh
State:      New york
Base Salary: 12345

Output:

org.postgresql.util.PSQLException: Bad value for type int : fares

Notice: after refreshing my database in java it outputs this:

full_name: Fares Essayehstate: nullbase_salary

It looks as if your query parameters are getting jumbled up, somewhere along the way. Have you tried tracing with a debugger, to see what values are ending up being passed the various setXXX() methods? — Kevin Boone
i add the class it's seems something not matching with data type values — Fares_Essayeh
Yes. Maybe somebody more eagle-eyed than me will spot the problem straight away. But if this were my code, I'd break out the debugger. One breakpoint and couple of single-steps, and most likely you'll have the answer. — Kevin Boone
I am not sure, but try to send the intValue of baseSalary like that : stmt.setInt(4, worker.getBase_salary().intValue()); — Ayoub Mk

coladict coladict · Accepted Answer · 2020-09-18T18:26:40

The table doesn't have an auto-generated id. First you should change it to

create table worker(
 id serial primary key not null,
 first_name varchar(20) ,
 last_name varchar(20)  ,
 state varchar(20)  ,
 base_salary integer
);

The id field could also have GENERATED AS IDENTITY but I the databases I have available to test before giving the answer are old and don't support that syntax.

I would suggest you change the query to the following:

INSERT INTO worker(first_name,last_name,state,base_salary) VALUES (?,?,?,?) RETURNING id

You don't need the terminating semicolon ; when using connectivity APIs like this from other languages.

org.postgresql.util.PSQLException: Bad value for type int : (simple JDBC insert)

1 Answers