java - Using JPA creates SQL Syntax Errors

Question

I'm programming a simple Java program which accesses a database (MariaDB).

When running, I always run into an error which says Table ... doesn't exist. This is the case because when JPA calls the CREATE TABLE statement it uses NUMBER(n) as a type for the fields in my class that are declared as an int. This causes a syntax error and the table will not be created. Due to that when trying to insert the new user into the db the error occurs. Replacing the NUMBER with INT or DECIMAL works fine.

I thought the MariaDB driver would take care of it.

The error:

Exception in thread "AWT-EventQueue-0" jakarta.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 3.0.0.v202012081010): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=68) Table 'dbtest.user' doesn't exist
Error Code: 1146
Call: INSERT INTO User (ID, eMail, username, password) VALUES (?, ?, ?, ?)

Full stack trace:

Exception in thread "AWT-EventQueue-0" jakarta.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 3.0.0.v202012081010): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=68) Table 'dbtest.user' doesn't exist
Error Code: 1146
Call: INSERT INTO User (ID, eMail, username, password) VALUES (?, ?, ?, ?)
    bind => [4 parameters bound]
Query: InsertObjectQuery(db.models.User@1b9b0b25)
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:163)
    at db.Connector.registerUser(Connector.java:15)
    at UI.Register_User$1.actionPerformed(Register_User.java:40)
    at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
    at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
    at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
    at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
    at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
    at java.desktop/java.awt.Component.processMouseEvent(Component.java:6614)
    at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
    at java.desktop/java.awt.Component.processEvent(Component.java:6379)
    at java.desktop/java.awt.Container.processEvent(Container.java:2263)
    at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:4990)
    at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
    at java.desktop/java.awt.Component.dispatchEvent(Component.java:4822)
    at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4919)
    at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4548)
    at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4489)
    at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307)
    at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2769)
    at java.desktop/java.awt.Component.dispatchEvent(Component.java:4822)
    at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
    at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
    at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
    at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
    at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 3.0.0.v202012081010): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=68) Table 'dbtest.user' doesn't exist
Error Code: 1146
Call: INSERT INTO User (ID, eMail, username, password) VALUES (?, ?, ?, ?)
    bind => [4 parameters bound]
Query: InsertObjectQuery(db.models.User@1b9b0b25)
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:334)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:908)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:970)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:640)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2045)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:312)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:282)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:268)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:421)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:175)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:191)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:507)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:83)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:94)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:319)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:61)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:912)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:811)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:112)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:89)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2984)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1844)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1826)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1776)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:248)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:147)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4329)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1496)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1586)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:288)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1223)
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:138)
    ... 37 more
Caused by: java.sql.SQLSyntaxErrorException: (conn=68) Table 'dbtest.user' doesn't exist
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
    at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)
    at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)
    at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:181)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898)
    ... 68 more
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Table 'dbtest.user' doesn't exist
    at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
    at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
    ... 71 more
Caused by: java.sql.SQLException: Table 'dbtest.user' doesn't exist
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1683)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1545)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1508)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318)
    ... 72 more

The create statement:

Call: CREATE TABLE User (ID NUMBER(10) NOT NULL, eMail VARCHAR NOT NULL UNIQUE, username VARCHAR(60) NOT NULL UNIQUE, password NUMBER(10) NOT NULL, PRIMARY KEY (ID))
Query: DataModifyQuery(sql="CREATE TABLE User (ID NUMBER(10) NOT NULL, eMail VARCHAR NOT NULL UNIQUE, username VARCHAR(60) NOT NULL UNIQUE, password NUMBER(10) NOT NULL, PRIMARY KEY (ID))")

The User class:

package db.models;

import jakarta.persistence.*;

@Entity
@Table(name = "User")
public class User {

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

    @Column(nullable = false, unique = true, length = 60

Answer 1

Waitting for answers