I am trying to run hibernate on a PostgreSQL 8.4.2 DB. Whenever I try to run a simple java code like:
List<User> users = service.findAllUsers();
I get the following error:
PSQLException: ERROR: relation "TABLE_NAME" does not exist
Since I have option hibernate.show_sql option set to true, I can see that hibernate is trying to run the following SQL command:
select this_.USERNAME as USERNAME0_0_, this_.PASSWORD as PASSWORD0_0_
from "TABLE_NAME" this_
When in reality, it should at least run something like:
select this_."USERNAME" as USERNAME0_0_, this_."PASSWORD" as PASSWORD0_0_
from "SCHEMA_NAME"."TABLE_NAME" as this_
Does anyone know what changes I need to make for Hibernate to produce the right SQL for PostgreSQL?
I have set up the necessary postgreSQL datasource in applicationContext.xml file:
<!-- Use Spring annotations -->
<context:annotation-config />
<!-- postgreSQL datasource -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url"
value="jdbc:postgresql://localhost/DB_NAME:5432/SCHEMA_NAME" />
<property name="username" value="postgres" />
<property name="password" value="password" />
<property name="defaultAutoCommit" value="false" />
</bean>
On the same file I have set up the session factory with PostgreSQL dialect:
<!-- Hibernate session factory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="annotatedClasses">
<list>
<value>com.myPackage.dbEntities.domain.User</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<!-- setup transaction manager -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref bean="sessionFactory" />
</property>
</bean>
Finally, the way I am mapping the domain class to the table is:
@Entity
@Table(name = "`TABLE_NAME`")
public class User {
@Id
@Column(name = "USERNAME")
private String username;
Has anyone encountered a similar error?. Any help in solving this issue will be much appreciated.
Please note that question is different to post Cannot simply use PostgreSQL table name (”relation does not exist”)
Apologies for the lengthy post.
I can’t figure out what I’m doing wrong. I’m learning JPA mapping to a relational DB, by following some tutorials on the web, but can’t find one that is straightforward. When I run my project, it gives me an error. I guess it’s upon persisting em.persist();. If I comment that line, all looks good, and no errors, but no data is written to table, obviously. Here’s my code:
persistence.xml (generated, untouched)
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/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_1.xsd">
<persistence-unit name="RESTappPU" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<class>restapp.entities.ContactList</class>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/postgres"/>
<property name="javax.persistence.jdbc.user" value="postgres"/>
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
<property name="javax.persistence.jdbc.password" value="postgres"/>
</properties>
</persistence-unit>
</persistence>
Entity Class(generated, untouched) — Do I need to add some additional ‘relation’ method here?
package restapp.entities;
imports [...]
@Entity
@Table(name = "ContactList")
@NamedQueries({
@NamedQuery(name = "ContactList.findAll", query = "SELECT c FROM ContactList c"),
@NamedQuery(name = "ContactList.findByFirstname", query = "SELECT c FROM ContactList c WHERE c.firstname = :firstname"),
@NamedQuery(name = "ContactList.findByLastname", query = "SELECT c FROM ContactList c WHERE c.lastname = :lastname"),
@NamedQuery(name = "ContactList.findByMobile", query = "SELECT c FROM ContactList c WHERE c.mobile = :mobile"),
@NamedQuery(name = "ContactList.findByEmail", query = "SELECT c FROM ContactList c WHERE c.email = :email")})
public class ContactList implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name = "firstname")
private String firstname;
@Column(name = "lastname")
private String lastname;
@Basic(optional = false)
@Column(name = "mobile")
private String mobile;
@Column(name = "email")
private String email;
// constructors
public ContactList() {
}
public ContactList(String firstname) {
this.firstname = firstname;
}
public ContactList(String firstname, String mobile) {
this.firstname = firstname;
this.mobile = mobile;
}
public ContactList(String firstname, String lastname, String mobile, String email) {
this.firstname = firstname;
this.lastname = lastname;
this.mobile = mobile;
this.email = email;
}
// getter's and setter's
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
// Is something like this what it needs?
// @OneToOne(cascade=CascadeType.PERSIST)
// private Address address;
// @OneToMany(cascade=ALL, mappedBy="customer")
// public Collection<Order> getOrders() {
// return orders;
// }
@Override
public String toString() {
return "nnnn[firstname: " + firstname + "]n"
+ "[lastname: " + lastname + "]n"
+ "[mobile: " + mobile + "]n"
+ "[email: " + email + "]nnnn";
}
}
Java App Class
package restapp;
imports [...]
public class RESTapp {
private static EntityManagerFactory emf;
private static EntityManager em;
public static void main(String[] args) {
// Create EntityManagerFactory for persistent unit named "pu1" to be used in this test
emf = Persistence.createEntityManagerFactory("RESTappPU");
// Persist the customer
// em.persist(list0);
// Persist all entities
createTransactionalEntityManager();
System.out.println("Inserting Customer and Orders... " + insert());
closeTransactionalEntityManager();
}
private static String insert() {
// Create new contact
ContactList list0 = new ContactList();
list0.setFirstname("John");
list0.setLastname("Doe");
list0.setMobile("+351 91 546 33 21");
list0.setEmail("jdoe@fakemail.com");
// Create another contact
ContactList list1 = new ContactList("Jane", "Something", "+351 96 924 14 29", "jsomething@fakemail.com");
list0.toString();
// em.persist(list0);
em.persist(list0);
return "OK";
}
private static void createTransactionalEntityManager() {
// Create a new EntityManager
em = emf.createEntityManager();
// Begin transaction
em.getTransaction().begin();
}
private static void closeTransactionalEntityManager() {
// Commit the transaction
em.getTransaction().commit();
// Close this EntityManager
em.close();
}
}
And when I run my project:
run:
[EL Info]: 2015-06-03 18:30:17.315--ServerSession(1798636297)--EclipseLink, version: Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd
[EL Info]: connection: 2015-06-03 18:30:17.689--ServerSession(1798636297)--file:/home/rsousa/NetBeansProjects/RESTapp/build/classes/_RESTappPU login successful
Inserting Customer and Orders... OK
[EL Warning]: 2015-06-03 18:30:17.794--UnitOfWork(683523720)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
Position: 13
Error Code: 0
Call: INSERT INTO ContactList (firstname, email, lastname, mobile) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: InsertObjectQuery(
[firstname: John]
[lastname: Doe]
Exception in thread "main" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
[mobile: +351 91 546 33 21]
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
[email: jdoe@fakemail.com]
)
Position: 13
Error Code: 0
Call: INSERT INTO ContactList (firstname, email, lastname, mobile) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: InsertObjectQuery(
[firstname: John]
[lastname: Doe]
[mobile: +351 91 546 33 21]
[email: jdoe@fakemail.com]
)
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:157)
at restapp.RESTapp.closeTransactionalEntityManager(RESTapp.java:66)
at restapp.RESTapp.main(RESTapp.java:32)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
Position: 13
Error Code: 0
Call: INSERT INTO ContactList (firstname, email, lastname, mobile) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: InsertObjectQuery(
[firstname: John]
[lastname: Doe]
[mobile: +351 91 546 33 21]
[email: jdoe@fakemail.com]
)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1611)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:962)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:631)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:298)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:377)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:165)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:489)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:301)
at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1737)
at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:226)
at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:125)
at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4207)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1441)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531)
at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:277)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1169)
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:132)
... 2 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
Position: 13
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:890)
... 33 more
Java Result: 1
BUILD SUCCESSFUL (total time: 3 seconds)
IDE: NetBeans | DB: PostGreSQL | Persistence: EclipseLink JPA
I can’t figure out what I’m doing wrong. I’m learning JPA mapping to a relational DB, by following some tutorials on the web, but can’t find one that is straightforward. When I run my project, it gives me an error. I guess it’s upon persisting em.persist();. If I comment that line, all looks good, and no errors, but no data is written to table, obviously. Here’s my code:
persistence.xml (generated, untouched)
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/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_1.xsd">
<persistence-unit name="RESTappPU" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<class>restapp.entities.ContactList</class>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/postgres"/>
<property name="javax.persistence.jdbc.user" value="postgres"/>
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
<property name="javax.persistence.jdbc.password" value="postgres"/>
</properties>
</persistence-unit>
</persistence>
Entity Class(generated, untouched) — Do I need to add some additional ‘relation’ method here?
package restapp.entities;
imports [...]
@Entity
@Table(name = "ContactList")
@NamedQueries({
@NamedQuery(name = "ContactList.findAll", query = "SELECT c FROM ContactList c"),
@NamedQuery(name = "ContactList.findByFirstname", query = "SELECT c FROM ContactList c WHERE c.firstname = :firstname"),
@NamedQuery(name = "ContactList.findByLastname", query = "SELECT c FROM ContactList c WHERE c.lastname = :lastname"),
@NamedQuery(name = "ContactList.findByMobile", query = "SELECT c FROM ContactList c WHERE c.mobile = :mobile"),
@NamedQuery(name = "ContactList.findByEmail", query = "SELECT c FROM ContactList c WHERE c.email = :email")})
public class ContactList implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name = "firstname")
private String firstname;
@Column(name = "lastname")
private String lastname;
@Basic(optional = false)
@Column(name = "mobile")
private String mobile;
@Column(name = "email")
private String email;
// constructors
public ContactList() {
}
public ContactList(String firstname) {
this.firstname = firstname;
}
public ContactList(String firstname, String mobile) {
this.firstname = firstname;
this.mobile = mobile;
}
public ContactList(String firstname, String lastname, String mobile, String email) {
this.firstname = firstname;
this.lastname = lastname;
this.mobile = mobile;
this.email = email;
}
// getter's and setter's
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
// Is something like this what it needs?
// @OneToOne(cascade=CascadeType.PERSIST)
// private Address address;
// @OneToMany(cascade=ALL, mappedBy="customer")
// public Collection<Order> getOrders() {
// return orders;
// }
@Override
public String toString() {
return "nnnn[firstname: " + firstname + "]n"
+ "[lastname: " + lastname + "]n"
+ "[mobile: " + mobile + "]n"
+ "[email: " + email + "]nnnn";
}
}
Java App Class
package restapp;
imports [...]
public class RESTapp {
private static EntityManagerFactory emf;
private static EntityManager em;
public static void main(String[] args) {
// Create EntityManagerFactory for persistent unit named "pu1" to be used in this test
emf = Persistence.createEntityManagerFactory("RESTappPU");
// Persist the customer
// em.persist(list0);
// Persist all entities
createTransactionalEntityManager();
System.out.println("Inserting Customer and Orders... " + insert());
closeTransactionalEntityManager();
}
private static String insert() {
// Create new contact
ContactList list0 = new ContactList();
list0.setFirstname("John");
list0.setLastname("Doe");
list0.setMobile("+351 91 546 33 21");
list0.setEmail("jdoe@fakemail.com");
// Create another contact
ContactList list1 = new ContactList("Jane", "Something", "+351 96 924 14 29", "jsomething@fakemail.com");
list0.toString();
// em.persist(list0);
em.persist(list0);
return "OK";
}
private static void createTransactionalEntityManager() {
// Create a new EntityManager
em = emf.createEntityManager();
// Begin transaction
em.getTransaction().begin();
}
private static void closeTransactionalEntityManager() {
// Commit the transaction
em.getTransaction().commit();
// Close this EntityManager
em.close();
}
}
And when I run my project:
run:
[EL Info]: 2015-06-03 18:30:17.315--ServerSession(1798636297)--EclipseLink, version: Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd
[EL Info]: connection: 2015-06-03 18:30:17.689--ServerSession(1798636297)--file:/home/rsousa/NetBeansProjects/RESTapp/build/classes/_RESTappPU login successful
Inserting Customer and Orders... OK
[EL Warning]: 2015-06-03 18:30:17.794--UnitOfWork(683523720)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
Position: 13
Error Code: 0
Call: INSERT INTO ContactList (firstname, email, lastname, mobile) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: InsertObjectQuery(
[firstname: John]
[lastname: Doe]
Exception in thread "main" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
[mobile: +351 91 546 33 21]
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
[email: jdoe@fakemail.com]
)
Position: 13
Error Code: 0
Call: INSERT INTO ContactList (firstname, email, lastname, mobile) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: InsertObjectQuery(
[firstname: John]
[lastname: Doe]
[mobile: +351 91 546 33 21]
[email: jdoe@fakemail.com]
)
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:157)
at restapp.RESTapp.closeTransactionalEntityManager(RESTapp.java:66)
at restapp.RESTapp.main(RESTapp.java:32)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
Position: 13
Error Code: 0
Call: INSERT INTO ContactList (firstname, email, lastname, mobile) VALUES (?, ?, ?, ?)
bind => [4 parameters bound]
Query: InsertObjectQuery(
[firstname: John]
[lastname: Doe]
[mobile: +351 91 546 33 21]
[email: jdoe@fakemail.com]
)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1611)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:962)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:631)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:298)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:377)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:165)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:489)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:301)
at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1737)
at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:226)
at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:125)
at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4207)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1441)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531)
at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:277)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1169)
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:132)
... 2 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "contactlist" does not exist
Position: 13
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:890)
... 33 more
Java Result: 1
BUILD SUCCESSFUL (total time: 3 seconds)
IDE: NetBeans | DB: PostGreSQL | Persistence: EclipseLink JPA
Фреймворк ssh, используемый проектом; база данных проекта была перенесена с mysql на pgsql, и проблема отсутствия последовательности гибернации возникла после миграции базы данных.
С точки зрения связанной информации, класс сущности в Hibernate использует собственный метод для генерации первичных ключей. Native определяется Hibernate на основе используемой базы данных и использует один из следующих методов: identity, hilo и sequence в качестве метода генерации первичного ключа. .
MySQL использует автоинкремент в качестве первичного ключа, тогда как базы данных Oracle, pgsql и DB2 могут не иметь подобных типов автоинкремента, поэтому для его поддержки необходима последовательность с именем hibernate_sequence. То есть вам нужно создать его вручную после миграции.
нота: В pgsql последовательность создается в шаблоне, в одном шаблоне она есть, а другие шаблоны нужно создавать заново (я такой).
Оператор последовательности hibernate_sequence:
create sequence HIBERNATE_SEQUENCE
minvalue 100000
maxvalue 9999999999999999
start with 100060
increment by 1
cache 20;
После создания этой модели эта проблема была решена.
Примечания: Значение атрибута для создания последовательности можно найти в следующем блоге.
Учебник по синтаксису PostgreSQL-9.6.8 для создания последовательностей, изменения последовательностей и удаления последовательностей
Я выполняю пакетное обновление hibernate jpa и даю мне следующую ошибку
2015-04-21 15:53:51,907 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Thread-283 (HornetQ-client-global-threads-462057890)) SQL Error: 0, SQLState: 42P01
2015-04-21 15:53:51,908 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Thread-283 (HornetQ-client-global-threads-462057890)) ERROR: relation "my_seq_gen" does not exist
Я использую базу данных postgres, и мой идентификатор сгенерирован автоматически
@Id
@SequenceGenerator(name="seq-gen",sequenceName="MY_SEQ_GEN"initialValue=205, allocationSize=12)
@GeneratedValue(strategy= GenerationType.SEQUENCE, generator="seq-gen")
@Column(name=""ID"",unique=true,nullable=false)
private int id;
Это мой фрагмент кода вставки пакета
getEm().getTransaction().begin();
System.out.println("transaction started--------------");
try {
for (Receipt ReceiptEntity : arrReceiptEntity) {
getEm().persist(ReceiptEntity);
}
getEm().getTransaction().commit();
System.out.println("commited");
} catch (Exception exception) {
System.out.println("error----------------------------------------------------------------------");
if(getEm().getTransaction().isActive())
getEm().getTransaction().rollback();
LOG.error(exception);
} finally {
getEm().flush();
getEm().clear();
getEm().close();
}
Я добавил следующее свойство в persistence.xml
<property name="hibernate.id.new_generator_mappings" value="true"/>
Пожалуйста, предложите, что я делаю неправильно.
I am trying to run hibernate on a PostgreSQL 8.4.2 DB. Whenever I try to run a simple java code like:
List<User> users = service.findAllUsers();
I get the following error:
PSQLException: ERROR: relation "TABLE_NAME" does not exist
Since I have option hibernate.show_sql option set to true, I can see that hibernate is trying to run the following SQL command:
select this_.USERNAME as USERNAME0_0_, this_.PASSWORD as PASSWORD0_0_
from "TABLE_NAME" this_
When in reality, it should at least run something like:
select this_."USERNAME" as USERNAME0_0_, this_."PASSWORD" as PASSWORD0_0_
from "SCHEMA_NAME"."TABLE_NAME" as this_
Does anyone know what changes I need to make for Hibernate to produce the right SQL for PostgreSQL?
I have set up the necessary postgreSQL datasource in applicationContext.xml file:
<!-- Use Spring annotations -->
<context:annotation-config />
<!-- postgreSQL datasource -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url"
value="jdbc:postgresql://localhost/DB_NAME:5432/SCHEMA_NAME" />
<property name="username" value="postgres" />
<property name="password" value="password" />
<property name="defaultAutoCommit" value="false" />
</bean>
On the same file I have set up the session factory with PostgreSQL dialect:
<!-- Hibernate session factory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="annotatedClasses">
<list>
<value>com.myPackage.dbEntities.domain.User</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<!-- setup transaction manager -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref bean="sessionFactory" />
</property>
</bean>
Finally, the way I am mapping the domain class to the table is:
@Entity
@Table(name = "`TABLE_NAME`")
public class User {
@Id
@Column(name = "USERNAME")
private String username;
Has anyone encountered a similar error?. Any help in solving this issue will be much appreciated.
Please note that question is different to post Cannot simply use PostgreSQL table name (”relation does not exist”)
Apologies for the lengthy post.
I am trying to run hibernate on a PostgreSQL 8.4.2 DB. Whenever I try to run a simple java code like:
List<User> users = service.findAllUsers();
I get the following error:
PSQLException: ERROR: relation "TABLE_NAME" does not exist
Since I have option hibernate.show_sql option set to true, I can see that hibernate is trying to run the following SQL command:
select this_.USERNAME as USERNAME0_0_, this_.PASSWORD as PASSWORD0_0_
from "TABLE_NAME" this_
When in reality, it should at least run something like:
select this_."USERNAME" as USERNAME0_0_, this_."PASSWORD" as PASSWORD0_0_
from "SCHEMA_NAME"."TABLE_NAME" as this_
Does anyone know what changes I need to make for Hibernate to produce the right SQL for PostgreSQL?
I have set up the necessary postgreSQL datasource in applicationContext.xml file:
<!-- Use Spring annotations -->
<context:annotation-config />
<!-- postgreSQL datasource -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url"
value="jdbc:postgresql://localhost/DB_NAME:5432/SCHEMA_NAME" />
<property name="username" value="postgres" />
<property name="password" value="password" />
<property name="defaultAutoCommit" value="false" />
</bean>
On the same file I have set up the session factory with PostgreSQL dialect:
<!-- Hibernate session factory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="annotatedClasses">
<list>
<value>com.myPackage.dbEntities.domain.User</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<!-- setup transaction manager -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref bean="sessionFactory" />
</property>
</bean>
Finally, the way I am mapping the domain class to the table is:
@Entity
@Table(name = "`TABLE_NAME`")
public class User {
@Id
@Column(name = "USERNAME")
private String username;
Has anyone encountered a similar error?. Any help in solving this issue will be much appreciated.
Please note that question is different to post Cannot simply use PostgreSQL table name (”relation does not exist”)
Apologies for the lengthy post.
у меня возникли проблемы с работой с PostgreSQL и Hibernate, в частности, проблема, упомянутая в названии. Я искал в Сети уже несколько часов, но ни одно из найденных решений не сработало для меня.
Я использую Eclipse Java EE IDE для веб-разработчиков. Идентификатор сборки: 20090920-1017 с HibernateTools, Hibernate 3, PostgreSQL 8.4.3 на Ubuntu 9.10.
вот соответствующие файлы:
сообщение.класс!—15—>
package hello;
public class Message {
private Long id;
private String text;
public Message() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
}
сообщение.hbm.в XML
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="hello">
<class
name="Message"
table="public.messages">
<id name="id" column="id">
<generator class="assigned"/>
</id>
<property name="text" column="messagetext"/>
</class>
</hibernate-mapping>
спящий режим.контекстно-свободная грамматика.в XML
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.password">bar</property>
<property name="hibernate.connection.url">jdbc:postgresql:postgres/tommy</property>
<property name="hibernate.connection.username">foo</property>
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="show_sql">true</property>
<property name="log4j.logger.org.hibernate.type">DEBUG</property>
<mapping resource="hello/Message.hbm.xml"/>
</session-factory>
</hibernate-configuration>
Main
package hello;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class App {
public static void main(String[] args) {
SessionFactory sessionFactory = new Configuration().configure()
.buildSessionFactory();
Message message = new Message();
message.setText("Hello Cruel World");
message.setId(2L);
Session session = null;
Transaction transaction = null;
try {
session = sessionFactory.openSession();
transaction = session.beginTransaction();
session.save(message);
} catch (Exception e) {
System.out.println("Exception attemtping to Add message: "
+ e.getMessage());
} finally {
if (session != null && session.isOpen()) {
if (transaction != null)
transaction.commit();
session.flush();
session.close();
}
}
}
}
структура таблицы:
foo=# d messages
Table "public.messages"
Column | Type | Modifiers
-------------+---------+-----------
id | integer |
messagetext | text |
выход консоли Eclipse при запуске
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.5.1-Final
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: Bytecode provider name : javassist
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration addResource
INFO: Reading mappings from resource : hello/Message.hbm.xml
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: hello.Message -> public.messages
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 20
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: false
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: org.postgresql.Driver at URL: jdbc:postgresql:postgres/tommy
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=foo, password=****}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: PostgreSQL, version: 8.4.3
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: PostgreSQL Native Driver, version: PostgreSQL 8.4 JDBC4 (build 701)
Apr 28, 2010 11:13:54 PM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.PostgreSQLDialect
Apr 28, 2010 11:13:54 PM org.hibernate.engine.jdbc.JdbcSupportLoader useContextualLobCreation
INFO: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 15
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: auto
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL inserts for batching: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JPA-QL strict compliance: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createRegionFactory
INFO: Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: pojo
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Named query checking : enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Check Nullability in Core (should be disabled when Bean Validation is on): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Apr 28, 2010 11:13:55 PM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Hibernate: insert into public.messages (messagetext, id) values (?, ?)
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted. Call getNextException to see the cause.
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: relation "public.messages" does not exist
Position: 13
Apr 28, 2010 11:13:55 PM org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
at org.postgresql.core.v3.QueryExecutorImpl.handleError(QueryExecutorImpl.java:459)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 8 more
Exception in thread "main" org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
at org.postgresql.core.v3.QueryExecutorImpl.handleError(QueryExecutorImpl.java:459)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 8 more
файл журнала PostgreSQL
2010-04-28 23:13:55 EEST LOG: execute S_1: BEGIN
2010-04-28 23:13:55 EEST ERROR: relation "public.messages" does not exist at character 13
2010-04-28 23:13:55 EEST STATEMENT: insert into public.messages (messagetext, id) values (, )
2010-04-28 23:13:55 EEST LOG: unexpected EOF on client connection
если я копирую / вставляю запрос в командная строка postgre и поместите значения В и; после этого он работает.
все в нижнем регистре, так что я не думаю, что это проблема.
если я переключаюсь на MySQL, тот же код того же проекта (я только меняю драйвер,URL, аутентификацию), он работает.
в Eclipse Datasource Explorer я могу пинговать БД, и это удается. Странно, что оттуда я тоже не вижу столиков. Он расширяет общедоступную схему, но не расширяет таблицы. Может быть … какие-то проблемы с разрешением?
спасибо!
Ваш URL JDBC — «jdbc: postgresql: postgres/tommy», что необычно. The документация предлагает «jdbc: / / hostname / databasename». Современные установки поставляются с базой данных» postgres», которая почти определенно не является тем, к чему вы хотите подключиться; я не знаю, насколько строг анализ URL-адресов драйвера JDBC.
что вы ожидаете, что ваше имя базы данных и имя хоста будут? например, каковы ваши параметры psql для подключения к базе данных таким образом?
Совет: в PostgreSQL.conf, некоторые настройки вы можете рассмотреть:
log_connections = on
log_disconnections = on
log_line_prefix = '%t %c %q%u@%h:%d '
Если ошибка — это то, что я думаю (вы подключаетесь к неправильной базе данных), это будет регистрировать такие вещи, как имя базы данных вместе с ошибкой в вашем postgresql.журнал.
Я работаю над приложением Spring Booot, используя Spring Data JPA . В качестве базы данных я использую PostgreSQL, и я обнаружил некоторую проблему с сопоставлением поля первичного ключа с автоматическим увеличением в таблице со связанным полем моего класса сущности.
В своей базе данных я вручную создал эту таблицу:
CREATE TABLE IF NOT EXISTS public."user"
(
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
first_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
middle_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
surname character varying(50) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT user_pkey PRIMARY KEY (id)
)
Обратите внимание, что поле id (мой PK) определено как bigint с ограничением GENERATED ALWAYS , а не как серийный номер < / strong> (это потому, что последовательный тип данных устарел, потому что он не является частью стандарта SQL).
Затем я создал этот класс сущности, отображающий эту таблицу:
package com.easydefi.users.entity;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;
@Entity
@Table(name = "portal_user")
@Data
public class User implements Serializable {
private static final long serialVersionUID = 5062673109048808267L;
@Id
@Column(name = "id")
private int id;
@Column(name = "first_name")
private String firstName;
@Column(name = "middle_name")
private String middleName;
@Column(name = "surname")
private String surname;
public User(String firstName, String middleName, String surname, char sex, Date birthdate, String taxCode,
String eMail, String contactNumber, Date createdAt) {
super();
this.firstName = firstName;
this.middleName = middleName;
this.surname = surname;
}
}
Затем у меня есть этот интерфейс репозитория (на данный момент он пуст, потому что я тестирую только метод save () , напрямую предоставленный JpaRepository ):
public interface UsersRepository extends JpaRepository<User, Integer> {
}
Наконец, я создал этот простой метод модульного тестирования, чтобы проверить вставку новой записи с помощью метода save () моего репозитория:
@SpringBootTest()
@ContextConfiguration(classes = GetUserWsApplication.class)
@TestMethodOrder(OrderAnnotation.class)
public class UserRepositoryTest {
@Autowired
private UsersRepository userRepository;
@Test
@Order(1)
public void testInsertUser() {
User user = new User("Mario", null, "Rossi", 'M', new Date(), "XXX", "xxx@gmail.com", "329123456", new Date());
userRepository.save(user);
assertTrue(true);
}
}
Проблема в том, что при выполнении метода save () я получаю следующее исключение:
Hibernate:
insert
into
portal_user
(first_name, middle_name, surname, id)
values
(?, ?, ?, ?)
2021-11-04 12:17:39.576 WARN 11436 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 428C9
2021-11-04 12:17:39.578 ERROR 11436 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: cannot insert a non-DEFAULT value into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
Поэтому я пытаюсь изменить отображение поля id в свой класс сущности следующим образом:
@Id
@Column(name = "id")
@GeneratedValue(strategy=GenerationType.AUTO)
private int id;
Но при запуске моего тестового метода выполнение метода save () дает мне еще одну ошибку:
Hibernate:
select
nextval ('hibernate_sequence')
2021-11-04 12:20:21.133 WARN 11639 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42P01
2021-11-04 12:20:21.136 ERROR 11639 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "hibernate_sequence" does not exist
Position: 17
В чем проблема? Что мне не хватает? Как я могу попытаться исправить эту проблему?
2 ответа
Лучший ответ
+ Изменить
GeneratedValue(strategy=GenerationType.AUTO)
Чтобы
@GeneratedValue(strategy=GenerationType.IDENTITY)
Поскольку GenerationType.IDENTITY создает primary key с auto increment и автоматически создает таблицу hibernate_sequence в вашей базе данных
2
Faeemazaz Bhanej
4 Ноя 2021 в 15:36
Ошибка определенно в этой строке.
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
Если вам нужно быстрое исправление, вы можете изменить его на приведенный ниже. SERIAL отлично работает в Postgres. Конечно, он может не работать в других базах данных, но как часто ИТ-проект выполняет миграцию БД? Никогда.
id serial PRIMARY KEY NOT NULL,
Решает ли это вашу проблему? Или надо как-то решить без SERIAL почему-то?
0
Arthur Klezovich
4 Ноя 2021 в 14:34