I am developing a REST web service in Java EE I am using: Glassfish 5.0 (build 25), MariaDB 10.4 and eclipselink (JPA 2.1) here is my code:
CREATE TABLE IF NOT EXISTS `cooldb`.`commande_line` (
`id` INT NOT NULL AUTO_INCREMENT,
`quantity` INT NULL,
`discount` INT NULL,
`dish` INT NOT NULL,
`commande` INT NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
INDEX `fk_commande_line_dish1_idx` (`dish` ASC),
INDEX `fk_commande_line_commande1_idx` (`commande` ASC),
CONSTRAINT `fk_commande_line_dish1`
FOREIGN KEY (`dish`)
REFERENCES `cooldb`.`dish` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_commande_line_commande1`
FOREIGN KEY (`commande`)
REFERENCES `cooldb`.`commande` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.2" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
<!-- Define Persistence Unit -->
<persistence-unit name="my_persistence_unit" transaction-type="JTA">
<jta-data-source>jdbc/mariadb</jta-data-source>
<class>com.yac.model.Address</class>
<class>com.yac.model.Commande</class>
<class>com.yac.model.CommandeLine</class>
<class>com.yac.model.Dish</class>
<class>com.yac.model.Dishtype</class>
<class>com.yac.model.Ingredient</class>
<class>com.yac.model.Payement</class>
<class>com.yac.model.Profil</class>
<class>com.yac.model.Restaurant</class>
<class>com.yac.model.Userapp</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
</properties>
</persistence-unit>
</persistence>
public class CommandeLine implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Column(name = "quantity")
private Integer quantity;
@Column(name = "discount")
private Integer discount;
@JoinColumn(name = "commande", referencedColumnName = "id")
@ManyToOne(optional = false)
private Commande commande;
@JoinColumn(name = "dish", referencedColumnName = "id")
@ManyToOne(optional = false)
private Dish dish;
//Constructor
// Setter and Getter
}
@Stateless
@Path("commandeline")
public class CommandeLineFacadeREST extends AbstractFacade<CommandeLine> {
@PersistenceContext(unitName = "my_persistence_unit")
private EntityManager em;
public CommandeLineFacadeREST() {
super(CommandeLine.class);
}
@POST
@Override
@Consumes(MediaType.APPLICATION_JSON)
public void create(CommandeLine entity) {
super.create(entity);
}
@Override
protected EntityManager getEntityManager() {
return em;
}
}
public abstract class AbstractFacade<T> {
private Class<T> entityClass;
public AbstractFacade(Class<T> entityClass) {
this.entityClass = entityClass;
}
protected abstract EntityManager getEntityManager();
public void create(T entity) {
getEntityManager().persist(entity);
}
}
The problem is when I test my web service with Postman and I try to insert a record with a POST request here is what I receive as error message:
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.0.v20170811-d680af5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=158) Table 'cooldb.sequence' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?") ...
I don't understand why the problem with SEQUANCE when I use @GeneratedValue (strategy = GenerationType.IDENTITY). When I change with @GeneratedValue (strategy = GenerationType.SEQUENCE) and I create the table with the following script:
CREATE SEQUENCE SEQUANCE START WITH 1 INCREMENT BY 1;
by applying the solution shown in : Table 'customerjpa.sequence' doesn't exist JPA but the same probleme
thank you in advance for your help.
The problem is solved using Chris comments, i just add the following line in my persistence.xml file:
<property name="eclipselink.target-database" value="MySQL"/>
Thank you very much Chris. So my new persistence.xml file is:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.2" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
<!-- Define Persistence Unit -->
<persistence-unit name="my_persistence_unit" transaction-type="JTA">
<jta-data-source>jdbc/mariadb</jta-data-source>
<class>com.yac.model.Address</class>
<class>com.yac.model.Commande</class>
<class>com.yac.model.CommandeLine</class>
<class>com.yac.model.Dish</class>
<class>com.yac.model.Dishtype</class>
<class>com.yac.model.Ingredient</class>
<class>com.yac.model.Payement</class>
<class>com.yac.model.Profil</class>
<class>com.yac.model.Restaurant</class>
<class>com.yac.model.Userapp</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
<property name="eclipselink.target-database" value="MySQL"/>
</properties>
</persistence-unit>
</persistence>
I just specified the database platform in MySQL in the persistence.xml file of the moment MariaDB is based on it, because MariaDB is not mentioned in the list. If there are other suggestions do not hesitate thank you.