无法在休眠状态提取 ResultSet

小开

Try using inner join in your Query

    Query query=session.createQuery("from Product as p INNER JOIN p.catalog as c
WHERE c.idCatalog= :id and p.productName like :XXX");
query.setParameter("id", 7);
query.setParameter("xxx", "%"+abc+"%");
List list = query.list();

also in the hibernate config file have

<!--hibernate.cfg.xml -->
<property name="show_sql">true</property>

To display what is being queried on the console.

小开

最佳答案

The @JoinColumn annotation specifies the name of the column being used as the foreign key on the targeted entity.

On the Product class above, the name of the join column is set to ID_CATALOG.

@ManyToOne
@JoinColumn(name="ID_CATALOG")
private Catalog catalog;

However, the foreign key on the Product table is called catalog_id

`catalog_id` int(11) DEFAULT NULL,

You'll need to change either the column name on the table or the name you're using in the @JoinColumn so that they match. See http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html/entity.html#entity-mapping-association

小开

I had similar issue. Try use the HQL editor. It will display you the SQL (as you have a SQL grammar exception). Copy your SQL and execute it separately. In my case the problem was in schema definition. I defined the schema, but I should leave it empty. This raised the same exception as you got. And the error description reflected the actual state, as the schema name was included in SQL statement.

小开

Another potential cause, for other people coming across the same error message is that this error will occur if you are accessing a table in a different schema from the one you have authenticated with.

In this case you would need to add the schema name to your entity entry:

@Table(name= "catalog", schema = "targetSchemaName")

小开

If you don't have 'HIBERNATE_SEQUENCE' sequence created in database (if use oracle or any sequence based database), you shall get same type of error;

Ensure the sequence is present there;

小开

I faced the same problem after migrating a database from online server to localhost. The schema changed so I had to define the schema manually for each table:

@Entity
@Table(name = "ESBCORE_DOMAIN", schema = "SYS")

小开

Another solution is add @JsonIgnore :

@OneToMany(mappedBy="catalog", fetch = FetchType.LAZY)
@JsonIgnore
private Set<Product> products = new HashSet<Product>(0);

小开

I Used the following properties in my application.properties file and the issue got resolved

spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl

and

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

earlier was getting an error

There was an unexpected error (type=Internal Server Error, status=500).
could not extract ResultSet; SQL [n/a]; nested exception is
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:254)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

小开

I had the same issue, when I tried to update a row:

@Query(value = "UPDATE data SET value = 'asdf'", nativeQuery = true)
void setValue();

My Problem was that I forgot to add the @Modifying annotation:

@Modifying
@Query(value = "UPDATE data SET value = 'asdf'", nativeQuery = true)
void setValue();

小开

I was using Spring Data JPA with PostgreSql and during UPDATE call it was showing errors-

'could not extract ResultSet' and another one.
org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query. (Showing Transactional required.)

Actually, I was missing two required Annotations.

@Transactional and
@Modifying

With-

@Query(vlaue = " UPDATE DB.TABLE SET Col1 = ?1 WHERE id = ?2 ", nativeQuery = true)
void updateCol1(String value, long id);

小开

For MySql take in mind that it's not a good idea to write camelcase. For example if the schema is like that:

CREATE TABLE IF NOT EXISTS `task`(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`teaching_hours` DECIMAL(5,2) DEFAULT NULL,
`isActive` BOOLEAN DEFAULT FALSE,
`is_validated` BOOLEAN DEFAULT FALSE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You must be very careful cause isActive column will translate to isactive. So in your Entity class is should be like this:

 @Basic
@Column(name = "isactive", nullable = true)
public boolean isActive() {
return isActive;
}
public void setActive(boolean active) {
isActive = active;
}

That was my problem at least that got me your error

This has nothing to do with MySql which is case insensitive, but rather is a naming strategy that spring will use to translate your tables. For more refer to this post

小开

This message also appears if you by mistake use a reserved keyword for your table (https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-C). When doing a join on a table which has the name the sama as the reserved mysql keyword you would get the same message, although everything else is correct. Hopefully this will spare some time and pain for other people.

小开

I've reproduced similar issue:

Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:279)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)


...


Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'setting0_.advance_payment_first_text' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:975)

And in my specific use case it was because of the conflict between new dump which was used for database and liquibase configuration files data.xml with logic like:

<changeSet  author="liquibase-docs"  id="sqlFile-data">
<sqlFile  dbms="!h2, oracle, mysql"
encoding="UTF-8"
path="..\..\..\sql\data.sql"
relativeToChangelogFile="true"
splitStatements="true"
stripComments="true"/>
</changeSet>

and structure.xml:

<changeSet  author="liquibase-docs"  id="sqlFile-structure">
<sqlFile  dbms="!h2, oracle, mysql"
encoding="UTF-8"
path="..\..\..\sql\structure.sql"
relativeToChangelogFile="true"
splitStatements="true"
stripComments="true"/>
</changeSet>

for old scripts of the project.

After some investigations by checking the correspondence of tables with columns in the code I understood these old scripts overwrote after running the project new dump.

So after removing old scripts data.sql and structure.sql with configuration files data.xml and structure.xml from the project this problem was solved.

小开

in my case, I had changed the ddl property to create-drop, and I ran it after the tables were deleted.

spring.jpa.hibernate.ddl-auto=create-drop

just put the ddl to create, and after that, return to none (not to change the database anymore)

spring.jpa.hibernate.ddl-auto=none

小开

For me the issue was first uppercase/lowercase differences between my local workspace and dev database server's table name.

Example:

select name from customer;

vs

select name from Customer;

By fixing the typo the error was gone.

Hope this will be a solution for someone out there.