Hibernate、mysql和Tomee-org.hsqldb。HsqlException：用户缺少权限或找不到对象

Question

项目可以编译，但是当我尝试:

SupplierEntity entity = em.find(SupplierEntity.class, 1);

或者我试试:

Query query = em.createNamedQuery("SupplierEntity.loadAll");
List<SupplierEntity> entityList =  query.getResultList();

返回错误：

javax.persistence.PersistenceException：org.hibernate.exception.SQLGrammarException：无法准备语句

更新：

根异常是：

原因： 组织.hsqldb.Hsql异常： 用户缺少特权或找不到对象： 供应商信息

我尝试从server.xml中删除池(资源),但项目没有失败，所以我认为我的问题与配置有关。

我搜索了其他类似的帖子，我认为我的代码很好。有什么想法吗？

我用Tomee作为服务器。

实体：

@javax.persistence.Entity
@javax.persistence.Table(name = "SupplierInfo")
@javax.persistence.NamedQueries({

    // Loading list Of Suppliers
    @javax.persistence.NamedQuery(name = "SupplierEntity.loadAll", query = "SELECT supplier FROM SupplierEntity AS supplier"),

})
public class SupplierEntity implements Serializable, Comparable<SupplierEntity>
{
    private static final long serialVersionUID = 7847645372201362008L;

    // ----------- Attribute Definitions ------------
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="supplierId", unique=true, insertable=true, updatable=true, nullable=false)
    private Long supplierId;

    @javax.persistence.Column(name = "supplierCode",  unique = false, nullable = true, insertable = true, updatable = true, length =20)
    private String supplierCode;

    @javax.persistence.Column(name = "supplierName",  unique = false, nullable = true, insertable = true, updatable = true, length =80)
    private String supplierName;

    //Getters and Setters
}

数据库中的表:

CREATE TABLE `SupplierInfo` (
  `supplierId` bigint(20) NOT NULL AUTO_INCREMENT,
  `supplierCode` varchar(20) NOT NULL DEFAULT '',
  `supplierName` varchar(80) NOT NULL DEFAULT '',
  PRIMARY KEY (`nirvanaId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

服务：

@Stateless
public class SupplierService implements Serializable{

@PersistenceContext(unitName = "myapp.supplier")
EntityManager em;

    public int test(){
   ...
   ...
        //SupplierEntity entity = em.find(SupplierEntity.class, 5);
        Query query = em.createNamedQuery("SupplierEntity.loadAll");

        List<SupplierEntity> entityList =  query.getResultList();

     //catch javax.persistence.NoResultException ex
     //catch Exception ex --> the exception entry into here.
    }

Persistence.xml:

<persistence-unit name="myapp.supplier" transaction-type="JTA">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <jta-data-source>java:openejb/Resource/supplierPool</jta-data-source>
    <!--SUPPLIER -->
    <class>com.myapp.entity.SupplierEntity</class>

    <properties>
        <property name="hibernate.hbm2ddl.auto" value="none"/>
        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
        <property name="hibernate.show_sql" value="false"/>
        <property name="hibernate.format_sql" value="false"/>
        <property name="hibernate.jdbc.batch_size" value="25"/>
        <property name="tomee.jpa.factory.lazy" value="true" />
        <!-- <property name="tomee.jpa.cdi" value="false" /> -->
    </properties>
</persistence-unit>

服务器.xml的托米：

<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" initialSize="34" maxActive="377" maxIdle="233"  minEvictableIdleTimeMillis="55000" minIdle="89" name="jdbc/supplierPool" password="pass" removeAbandoned="true" removeAbandonedTimeout="55" testOnBorrow="true" timeBetweenEvictionRunsMillis="34000" type="javax.sql.DataSource" url="jdbc:mysql://xxx.xxx.xxx.xx:3306/myapp_db" username="user" validationInterval="34000" validationQuery="SELECT 1"/>

Answer 1

最后，我的问题得到了解决，包括一个文件资源。WEB-INF中的xml

<resources>
    <Resource id="nirvanaDatasource" type="DataSource">
        JdbcDriver com.mysql.jdbc.Driver
        JdbcUrl jdbc:mysql://xxx.xxx.xxx.xx:3306/myapp
        UserName username
        Password password
    </Resource>
</resources> 

我不明白为什么这个文件是必要的，因为我已经在Tomee的server.xml中包含了池和凭证。

Answer 2

异常是＜code＞org.higbernate.Exception。SQLGrammarException＜/code＞，我们可以从中了解错误来自您的SQL查询。

当我们查看您的SQL查询时：

"SELECT supplier FROM SupplierEntity AS supplier"

我们可以理解为您引用了表中不存在的列< code>supplier，或者您试图将表< code>supplierEntity别名为< code>supplier。

如果您要别名供应商实体，则应删除AS关键字，因此只需使用：

"SELECT supplier FROM SupplierEntity supplier"

或者你可以写:

query = "from SupplierEntity supplier"

Answer 3

尝试更新您的查询

@NamedQuery(name = "SupplierEntity.loadAll", query = "SELECT supplier FROM SupplierEntity supplier")

< code>AS关键字是多余的，而且我认为是错误的