SQL exception : could not prepare statement

Question

I'm learning Spring boot and i have some exceptions when i want to create a "Lot" that i don't understand.

** Utilisateur entity **

@JsonIgnoreProperties
@Entity
@Data
public class Utilisateur {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@Column
private String nom;

@Column
private String prenom;

@Column
private String adresse;

@Column
private String numeroTelephone;

@Column
private String email;

@OneToMany(cascade = CascadeType.ALL)
private List<Lot> lotList;

@OneToMany(cascade = CascadeType.ALL)
private List<Tentative> tentativeList;

}

The "utilisateur" (means User) is related to n "Lot" (means prize), and can do three tries ("Tentative") per day.

** Lot entity **

@JsonIgnoreProperties
@Entity
@Data
public class Lot {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@NotNull
@Column(name = "nom")
private String nom;

@NotNull
@Column(name = "description")
private String description;

@Column(name = "date_time_declenchement")
private LocalDateTime dateTimeDeclenchement;

@NotNull
@Column(name = "is_gagne")
private boolean isGagne;

@ManyToOne(cascade = CascadeType.ALL)
private Utilisateur utilisateur;

}

** Tentative entity **

@JsonIgnoreProperties
@Entity
@Data
public class Tentative {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@NotNull
@Column
private LocalDateTime dateTentative;

@ManyToOne(cascade = CascadeType.ALL)
private Utilisateur utilisateur;
}

And my create.sql !

CREATE TABLE Utilisateur
(
 id INT PRIMARY KEY AUTO_INCREMENT,
 nom VARCHAR(100) NOT NULL,
 prenom VARCHAR(100) NOT NULL,
 adresse VARCHAR(100) NOT NULL,
 numero_telephone VARCHAR(100) NOT NULL,
 email VARCHAR(100) NOT NULL UNIQUE,
);

CREATE TABLE Lot
(
 id INT PRIMARY KEY AUTO_INCREMENT,
 nom VARCHAR(100) NOT NULL,
 description VARCHAR(255) NOT NULL,
 date_time_declenchement DATE,
 is_gagne BOOLEAN NOT NULL,
);

CREATE TABLE Tentative
(
 id INT PRIMARY KEY AUTO_INCREMENT,
 id_utilisateur INT,
 id_lot INT,
 date_tentative DATE NOT NULL,
);

ALTER TABLE Tentative
ADD CONSTRAINT fk_id_utilisateur_tentative FOREIGN KEY (id_utilisateur) 
REFERENCES Utilisateur(id);

ALTER TABLE Tentative
ADD CONSTRAINT fk_id_lot_tentative FOREIGN KEY (id_lot) REFERENCES Lot(id);

When i want to create a "LOT" with the body :

{
 "nom":"TV Samsung",
 "description":"Télévision Plasma Samsung 106cm"
}

I get this exception :

{
"timestamp": 1510068319912,
"status": 500,
"error": "Internal Server Error",
"exception": 
"org.springframework.dao.InvalidDataAccessResourceUsageException",
    "message": "could not prepare statement; SQL [insert into lot (id, 
date_time_declenchement, description, is_gagne, nom, utilisateur_id) values 
(null, ?, ?, ?, ?, ?)]; nested exception is 
org.hibernate.exception.SQLGrammarException: could not prepare statement",
    "path": "/creerlot"
}

I don't see why i've this error. Firsty, i've done my project with only hibernate / Jpa. With the interface CRUDRepository it was easy to find / save my objects. But now that i have sql, all my methods return an exception, except the one where i create an "Utilisateur".

Thanks for all !

as what i see, the id should not be in the insert as it is auto_increment. — Stefan LoKran Dotti, Nov 07 '17 at 15:48
possible duplicate: https://stackoverflow.com/questions/4102449/how-to-annotate-mysql-autoincrement-field-with-jpa-annotations — Stefan LoKran Dotti, Nov 07 '17 at 15:52
I already have @Id @GeneratedValue(strategy=GenerationType.IDENTITY) private Long id; — Steven Klinger, Nov 07 '17 at 15:53
Your json is incomplete. For example the column isGagne is not nullable. You must define it. — chouk, Nov 07 '17 at 15:54
Indeed i didn't saw that. But i still have the same error. Thanks for that anyway ! — Steven Klinger, Nov 07 '17 at 15:58
Which rdbms are you using? If oracle, you cannot use GenerationType.IDENTITY as strategy. — chouk, Nov 07 '17 at 15:58
Do you have more exceptions in the log that are more specific what is wrong? — Stefan LoKran Dotti, Nov 07 '17 at 15:59
Yes Stefan : Column "utilisateur_id" not found; Except that i don't have an utilisateur_id in my Lot... — Steven Klinger, Nov 07 '17 at 16:02
But you have: @ManyToOne(cascade = CascadeType.ALL) private Utilisateur utilisateur; in the Entitiy but the Table does not — Stefan LoKran Dotti, Nov 07 '17 at 16:04
you might need to use @ManyToOne(cascade = CascadeType.ALL, nullable=true), if you're not inserting the user at the same time. — chouk, Nov 07 '17 at 16:05
Yes, because my Lot is related to an Utilisateur. Maybe i didn't use that correctly? — Steven Klinger, Nov 07 '17 at 16:05
I guess that when you insert a lot, you don't want to provide the full user then? You have 2 ways to proceed: 1) fetch the user, attach the lot and persist it 2) change your mappings and remove the association to user from the lot (you can leave an unidirectional onetomany mapping from user to lots) — chouk, Nov 07 '17 at 16:08
I just want to link the user who will won the Lot. But when i created a Lot, there is no Utilisateur yet — Steven Klinger, Nov 07 '17 at 16:09
But chouk if i remove the association to user from the lot how will i know which user won the Lot? — Steven Klinger, Nov 07 '17 at 16:13
Just put the association as nullable=true, since it is not yet associated with a winner. — chouk, Nov 08 '17 at 07:14

SQL exception : could not prepare statement

0 Answers0