Can't automatically obtain ID for a record in database-CodePudding

My entity is:

@Entity(name = "studios")
public class Studio {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "studio_id", nullable = false)
    private Integer id;
    private String name;
    private String phone;
    private String url;

    public Studio() {

    }

    public Studio(String name, String phone, String url) {
        this.name = name;
        this.phone = phone;
        this.url = url;
    }

    public String getUrl() {
        return url;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public String getPhone() {
        return phone;
    }

}

Method to write a record to the database. I just want to send it to the database and automatically obtain an ID for every record in this table, but every time when I do this - "status": 500, "error": "Internal Server Error". And nothing changing in table.

@Override
public Studio save(Studio studio) {
    final String SQL_INSERT = "insert into studios values (?, ?, ?)";
    jdbcTemplate.update(
            con -> {
                PreparedStatement preparedStatement = con.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1, studio.getName());
                preparedStatement.setString(2, studio.getPhone());
                preparedStatement.setString(3, studio.getUrl());

                try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        studio.setId(generatedKeys.getInt(1));
                    }
                    else {
                        throw new SQLException("Creating user failed...");
                    }
                }

                return preparedStatement;
            }
    );
    return studio;
}

My table is:

CREATE TABLE "studios" (
    "studio_id" serial NOT NULL UNIQUE ,
    "name" varchar(128) NOT NULL UNIQUE,
    "url" varchar(255) NOT NULL,
    "phone" varchar(12) NOT NULL,
    CONSTRAINT "studios_pk" PRIMARY KEY ("studio_id")
);

Why it doesn't work so?

CodePudding user response：

You need to specify the columns you want to populate in your insert statement. Using an insert without explicit columns is always a bad idea because the assumption about column order could be wrong, but in this case it causes the insert to fail as your insert specifies three values while the table has four columns (studio_id, name, url, phone).

You need to use:

insert into studios (name, phone, url) values (?, ?, ?)

As an aside, even if your table had had only three, the columnless insert would have had the wrong result: you're setting in order name, phone, url, while your table definition has order name, url, phone.