org.postgresql.util.PSQLException:类型int的值不正确:(简单JDBC插入)

Question

我正试图通过jdbc在postgresql中新创建的表中插入数据，这个表就是

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

邮政编码：

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

主要分类代码：

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 

输出说明：

我的输入：

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

输出：

org.postgresql.util.PSQLException：类型int的值不正确：fares

注意：在用java刷新我的数据库后，它会输出以下内容：

full_name: Fares Essayehstate: nullbase_salary

Answer 1

该表没有自动生成的id。首先，您应该将其更改为

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

id字段也可以有GENERATED AS IDENTITY，但在给出答案之前，我可以测试的数据库是旧的，不支持这种语法。

我建议您将查询更改为以下内容：

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

在使用其他语言的连接API时，不需要使用终止分号;。