0
votes

I want create a web application and use JPA for model layer in MVC. But I'm having trouble. The program shows me this error :

Exception in thread "main" javax.persistence.RollbackException: Error while committing the transaction
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:92)
at model.bl.PersonManager.main(PersonManager.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:80)
... 6 more

Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3058)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3499)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:354)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:275)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:328)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1233)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:403)
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:75)
... 6 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)
... 20 more

I have tow entity : 1-person (user) 2-pictures . any person can have sevrale pictures .

Person class :

package model.entity;
import model.bl.PersonManager;
import javax.persistence.*;
import java.io.Serializable;
import java.util.List;

//mapping class to table
@Entity (name = "person")
@Table(name = "USERS")
@EntityListeners(value = PersonManager.class)

public class Person implements Serializable
{

@Id // create id and fill auto by sequence in database
@Column(name="UID" ,columnDefinition = "NUMBER" )
@SequenceGenerator(name = "mySeq" , sequenceName = "DB_MYSEQ")
@GeneratedValue(strategy=GenerationType.AUTO ,generator="mySeq")
private long uId;


@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "FK_PERSON",referencedColumnName = "UID")
private List<Pictures> picturesList;


@Basic
@Column (name = "USERNAME" , columnDefinition = "NVARCHAR2(30)" , nullable = false , unique = true)
private String username ;

@Basic
@Column (name = "PASSWORD" , columnDefinition = "NVARCHAR2(32)" , nullable = false , unique = true)
private String  password ;

@Basic
@Column (name = "EMAIL" , columnDefinition = "NVARCHAR2(40)" , nullable = false)
private String email;


@Basic
@Column (name = "SEX" , columnDefinition = "NVARCHAR2(20)")
private String sex ;

//--------------------------------------------------------

public Person() { }

public Person(String username, String password, String email, String sex, String userPic) {
    this.username = username;
    this.password = password;
    this.email = email;
    this.sex = sex;
    this.userPic = userPic;
}
public Person(String username, String password, String email ,String sex, String userPic,List<Pictures> picturesList ) {
    this.picturesList = picturesList;
    this.sex = sex;
    this.userPic = userPic;
    this.email = email;
    this.password = password;
    this.username = username;
}

//--------------------------------------------------------

public void setUsername(String username) {
    this.username = username;
}

public void setPassword(String password) {
    this.password = password;
}

public void setEmail(String email) {
    this.email = email;
}

public void setUserPic(String userPic) {
    this.userPic = userPic;
}

public void setSex(String sex) {
    this.sex = sex;
}

public void setuId(long uId) {this.uId = uId;}

//--------------------------------------------------------

public String getUsername() {
    return username;
}

public String getPassword() {
    return password;
}

public String getUserPic() {
    return userPic;
}

public String getEmail() {
    return email;
}

public String getSex() {
    return sex;
}

public long getuId() {return uId;}

}
}

Pictures class :

package model.entity;

import javax.persistence.*;
import java.io.Serializable;

@Entity(name = "picture")
@Table(name = "PICTURE")

public class Pictures implements Serializable
{
@Id // create id and fill auto by sequence in database
@Column(name="PID" ,columnDefinition = "NUMBER" )
@SequenceGenerator(name = "mySeq2" , sequenceName = "DB_MYSEQ2")
@GeneratedValue(strategy=GenerationType.AUTO ,generator="mySeq2")
private long pId;


@Basic
@Column (name = "PICADRESS" , columnDefinition = "NVARCHAR2(50)" , nullable = false)
private String  picAdress ;

@Basic
@Column (name = "CAPTION" , columnDefinition = "LONG")
private String caption;

@Basic // user picture for profile
@Column (name = "LIKES" , columnDefinition = "NUMBER")
private int likes;

//--------------------------------------------------------
public Pictures(){}

public Pictures( String picAdress, String caption, int likes) {
    this.picAdress = picAdress;
    this.caption = caption;
    this.likes = likes;
}
//--------------------------------------------------------

public void setPid(long pid) {
    this.pId = pid;
}

public void setLikes(int likes) {
    this.likes = likes;
}

public void setPicAdress(String picAdress) {
    this.picAdress = picAdress;
}

public void setCaption(String caption) {
    this.caption = caption;
}

//--------------------------------------------------------

public int getLikes() {
    return likes;
}

public String getCaption() {
    return caption;
}

public String getPicAdress() {
    return picAdress;
}

public long getPid() {
    return pId;
}
}

my JPA Provider is :

public class JPAProvider {

    private static final EntityManagerFactory     entityManagerFactory;//instate of session for connect to database
    static{
        entityManagerFactory  = Persistence.createEntityManagerFactory("MyConnection");
    }

    public static EntityManagerFactory getEntityManagerFactory() {
        return entityManagerFactory;
   }
}

PersonManager class is :

public class PersonManager {

    public static void main(String[] args) {

        EntityManager entityManager = JPAProvider.getEntityManagerFactory().createEntityManager();
        EntityTransaction entityTransaction = entityManager.getTransaction();
        entityTransaction.begin();

        Pictures pictures = new Pictures("aaa" , "akflkkgl" ,2);
        Pictures pictures2 = new Pictures("nnbnbn" , "affddA" ,5);
        List<Pictures> picturesList =new ArrayList<Pictures>();
        picturesList.add(pictures);
        picturesList.add(pictures2);
        Person person = new Person("midas" , "midas123" , "[email protected]", "female" ,"female-user.png",picturesList );

        entityManager.persist(person);
        entityTransaction.commit();
        entityManager.close();
    }
} 

and persistence.xml :

<persistence-unit name="MyConnection" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
        <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="hibernate.connection.username" value="midas"/>
        <property name="hibernate.connection.password" value="midas123"/>
        <property name="hibernate.connection.url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
        <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
        <property name="show_sql" value="true"></property>
    </properties>
</persistence-unit>

I used the following libraries :

1)hibernate-enverc-4.2.0.final

2)hibernate-jpa-2.0-api-1.0.1-final.jar

3)tomcat library

my JDK version = 1.8.0-172

I searched and checked similar questions but could not fix my problem because the reasons for the errors were different. please help me .

1
You tried to reference a column name, but the column name used is a reserved word in Oracle.ZaoTaoBao
@ZaoTaoBao Yes , thank you.farzane

1 Answers

1
votes

You get the error because of improper use of a reserved word.

password is a reserved word in Oracle, see this

You have to add this property in persistence.xml:

hibernate.globally_quoted_identifiers=true

or manually escape the field like this:

@Basic
@Column (name = "\"PASSWORD\"" , columnDefinition = "NVARCHAR2(32)" , nullable = false , unique = true)
private String  password ;

Since JPA 2.0, the above syntax is standardized.