Java 无法在休眠中提取 ResultSet
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20089031/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
could not extract ResultSet in hibernate
提问by Thanh Duy Ngo
I have a problem with Hibernate. I try to parse to List but It throws an exception: HTTP Status 500 - could not extract ResultSet. When I debug, It fault at line query.list()...
我有Hibernate问题。我尝试解析到列表中，但会抛出异常：HTTP Status 500 - could not extract ResultSet。当我调试时，它在线路上query.list()出错......
My sample code here
我的示例代码在这里
@Entity
@Table(name = "catalog")
public class Catalog implements Serializable {
@Id
@Column(name="ID_CATALOG")
@GeneratedValue
private Integer idCatalog;
@Column(name="Catalog_Name")
private String catalogName;
@OneToMany(mappedBy="catalog", fetch = FetchType.LAZY)
private Set<Product> products = new HashSet<Product>(0);
//getter & setter & constructor
//...
}
@Entity
@Table(name = "product")
public class Product implements Serializable {
@Id
@Column(name="id_product")
@GeneratedValue
private Integer idProduct;
@ManyToOne
@JoinColumn(name="ID_CATALOG")
private Catalog catalog;
@Column(name="product_name")
private String productName;
@Column(name="date")
private Date date;
@Column(name="author")
private String author;
@Column(name="price")
private Integer price;
@Column(name="linkimage")
private String linkimage;
//getter & setter & constructor
}
@Repository
@SuppressWarnings({"unchecked", "rawtypes"})
public class ProductDAOImpl implements ProductDAO {
@Autowired
private SessionFactory sessionFactory;
public List<Product> searchProductByCatalog(String catalogid, String keyword) {
String sql = "select p from Product p where 1 = 1";
Session session = sessionFactory.getCurrentSession();
if (keyword.trim().equals("") == false) {
sql += " and p.productName like '%" + keyword + "%'";
}
if (catalogid.trim().equals("-1") == false
&& catalogid.trim().equals("") == false) {
sql += " and p.catalog.idCatalog = " + Integer.parseInt(catalogid);
}
Query query = session.createQuery(sql);
List listProduct = query.list();
return listProduct;
}
}
My beans
我的豆子
<!-- Scan classpath for annotations (eg: @Service, @Repository etc) -->
<context:component-scan base-package="com.shopmvc"/>
<!-- JDBC Data Source. It is assumed you have MySQL running on localhost port 3306 with
username root and blank password. Change below if it's not the case -->
<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/shoesshopdb?autoReconnect=true"/>
<property name="username" value="root"/>
<property name="password" value="12345"/>
<property name="validationQuery" value="SELECT 1"/>
</bean>
<!-- Hibernate Session Factory -->
<bean id="mySessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="myDataSource"/>
<property name="packagesToScan">
<array>
<value>com.shopmvc.pojo</value>
</array>
</property>
<property name="hibernateProperties">
<value>
hibernate.dialect=org.hibernate.dialect.MySQLDialect
</value>
</property>
</bean>
<!-- Hibernate Transaction Manager -->
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="mySessionFactory"/>
</bean>
<!-- Activates annotation based transaction management -->
<tx:annotation-driven transaction-manager="transactionManager"/>
Exception:
例外：
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:948)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
root cause
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61)
org.hibernate.loader.Loader.getResultSet(Loader.java:2036)
root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'product0_.ID_CATALOG' in 'field list'
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.Util.getInstance(Util.java:386)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
org.hibernate.loader.Loader.getResultSet(Loader.java:2036)
org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836)
org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1815)
org.hibernate.loader.Loader.doQuery(Loader.java:899)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
org.hibernate.loader.Loader.doList(Loader.java:2522)
org.hibernate.loader.Loader.doList(Loader.java:2508)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2338)
org.hibernate.loader.Loader.list(Loader.java:2333)
org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
My Database:
我的数据库：
CREATE TABLE `catalog` (
`ID_CATALOG` int(11) NOT NULL AUTO_INCREMENT,
`Catalog_Name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ID_CATALOG`)
)
CREATE TABLE `product` (
`id_product` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(45) DEFAULT NULL,
`date` date DEFAULT NULL,
`author` varchar(45) DEFAULT NULL,
`price` int(11) DEFAULT NULL,
`catalog_id` int(11) DEFAULT NULL,
`linkimage` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id_product`),
KEY `FK_Product_idx` (`catalog_id`),
CONSTRAINT `FK_Product` FOREIGN KEY (`catalog_id`) REFERENCES `catalog` (`ID_CATALOG`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
采纳答案by Will Keeling
The @JoinColumnannotation specifies the name of the column being used as the foreign key on the targeted entity.
该@JoinColumn批注指定列的名称被用作对目标实体的外键。
On the Productclass above, the name of the join column is set to ID_CATALOG.
在Product上面的类中，连接列的名称设置为ID_CATALOG。
@ManyToOne
@JoinColumn(name="ID_CATALOG")
private Catalog catalog;
However, the foreign key on the Producttable is called catalog_id
但是Product表上的外键叫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 @JoinColumnso that they match. See http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html/entity.html#entity-mapping-association
您需要更改表上的列名或您在 中使用的名称，@JoinColumn以便它们匹配。见http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html/entity.html#entity-mapping-association
回答by Lakshmi
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.
在控制台上显示正在查询的内容。
回答by Gico
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.
我有类似的问题。尝试使用 HQL 编辑器。它将向您显示 SQL（因为您有 SQL 语法异常）。复制您的 SQL 并单独执行。就我而言，问题出在架构定义上。我定义了架构，但我应该将其留空。这引发了与您相同的异常。错误描述反映了实际状态，因为模式名称包含在 SQL 语句中。
回答by sweetfa
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")
回答by Mohammad Badiuzzaman
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;
如果您没有在数据库中创建 'HIBERNATE_SEQUENCE' 序列（如果使用 oracle 或任何基于序列的数据库），您将得到相同类型的错误；
Ensure the sequence is present there;
确保序列存在于那里；
回答by thadian
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")
回答by thadian
Another solution is add @JsonIgnore :
另一个解决方案是添加 @JsonIgnore ：
@OneToMany(mappedBy="catalog", fetch = FetchType.LAZY)
@JsonIgnore
private Set<Product> products = new HashSet<Product>(0);
回答by Saurabh Verma
I Used the following properties in my application.properties file and the issue got resolved
我在 application.properties 文件中使用了以下属性，问题得到解决
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)
回答by Manuel Schmitzberger
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 @Modifyingannotation:
我的问题是我忘记添加@Modifying注释：
@Modifying
@Query(value = "UPDATE data SET value = 'asdf'", nativeQuery = true)
void setValue();
