I'm trying to store a collection of orders made by a user. One user may have many orders, but one order has only one user. So a 1-M relationship is formed. The same relationship also exists between Items and ItemOrders, an ItemOrder consits of a Item and a amount of Items to order. So, an ItemOrder has one Item but one Item can have many ItemOrders.
In my test suite I've managed to create Items and ItemOrders properly. However, when I extend the test to also create Users and Orders I get an SQLSyntaxErrorException for "Order"... This is strange as it should be the exact same procedure to achieve both outcomes... And I can't figure out what I'm doing wrong here, any ideas?
@Entity
public class ItemEntity implements EntityInt {
@Id
@GeneratedValue(generator = "incrementor")
@Column(name = "item_id", unique = true)
public int id;
@OneToMany(mappedBy="item")
public Set<OrderItemEntity> orderItems = new HashSet<>();
}
@Entity
public class OrderItemEntity implements EntityInt {
@Id
@GeneratedValue(generator = "incrementor")
@Column(name = "order_item_id", unique = true)
public int id;
@Column(name = "amount", nullable = false)
public int amount;
@ManyToOne
@JoinColumn(name="item_id", nullable = false)
private ItemEntity item;
public OrderItemEntity(ItemEntity item, int amount) {
this.setItem(item);
this.amount = amount;
}
public void setItem(ItemEntity item) {
if (this.item != null)
this.item.orderItems.remove(this);
this.item = item;
this.item.orderItems.add(this);
}
}
@Entity
public class OrderEntity implements EntityInt {
@Id
@GeneratedValue(generator = "incrementor")
@Column(name = "order_id", unique = true)
public int id;
@ManyToOne
@JoinColumn(name="user_id", nullable = false)
public UserEntity user;
public UserEntity getUser() {
return user;
}
public void setUser(UserEntity user) {
if (this.user != null)
this.user.orders.remove(this);
this.user = user;
this.user.orders.add(this);
}
}
@Entity
public class UserEntity implements EntityInt {
@Id
@GeneratedValue(generator = "incrementor")
@Column(name = "user_id", unique = true)
public int id;
@OneToMany(mappedBy="user")
public Set<OrderEntity> orders = new HashSet<>();
}
TEST
@Test
public void testItemOrderEntity() throws Exception {
EntityManagerFactory factory = BasicDao.getEntityManagerFactory();
EntityManager em = factory.createEntityManager();
em.getTransaction().begin();
String itemName = Math.random() + "";
ItemEntity item = new ItemEntity(itemName, 0, 0);
em.persist(item);
OrderItemEntity orderItem = new OrderItemEntity(item, 5);
em.persist(orderItem);
String uname = "" + Math.random();
UserEntity user = new UserEntity(uname, uname);
em.persist(user);
// Error
OrderEntity order = new OrderEntity(user);
em.persist(order);
em.getTransaction().commit();
}
EntityInt contains methods used primarily by BasicDao to perform CRUD operations. It has stuff like getId(), getVersion(), createVerifyIsUnqieuQuery() and so on.
BasicDao is the primary repository access class and is extended by itemsDao, UsersDao and so on. This is the relevant parts of BasicDao that is used by the test case:
BasicDao.java
private static final String PERSISTENCE_UNIT_NAME = "org.hibernate.lab1_web_shop.jpa";
public static EntityManagerFactory getEntityManagerFactory() {
return Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
}
public static EntityInt insert(EntityInt entity) throws Exception {
EntityManagerFactory factory = getEntityManagerFactory();
EntityManager em = factory.createEntityManager();
try {
em.getTransaction().begin();
Query isUniqueQuery = entity.createVerifyIsUniqueQuery(em);
if (isUniqueQuery != null) {
List<EntityInt> resultList = isUniqueQuery.getResultList();
if (resultList.size() == 0) {
entity.beforeInsert(em);
em.persist(entity);
} else {
entity = null;
}
} else {
// There is no uniqueness filter so we insert it as is
entity.beforeInsert(em);
em.persist(entity);
}
em.getTransaction().commit();
// Returns the persistent entity along with any database modified attributes
return entity;
} catch (Exception e) {
em.getTransaction().rollback();
throw new Exception(e);
} finally {
em.close();
}
}
Note that beforeInsert() is not really used by the entities used in the test.
This is the stacktrace:
Caused by: javax.persistence.RollbackException: Error while committing the transaction
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Order (user_id, version, order_id) values (9, 0, 10)' at line 1
I also tested removing BasicDao and just doing everything in one commit and i still reproduce the same error.
