嵌入的Glassfish SQL语句失败 [英] Embedded Glassfish SQL statement fail

问题描述

我开发了一些单元测试，但是在所有的SQL语句上都失败了，你可以看看这个日志和配置吗？

Jul 01， 2012 4:33:05 PM
org.glassfish.persistence.common.Java2DBProcessorHelper executeDDLs
警告：PER01000：已得到SQLException执行语句CREATE TABLE
CAR（ID BIGINT NOT NULL，BRAND VARCHAR（ 255）NOT NULL，描述
VARCHAR（255），FUEL VARCHAR（255）不NU'\\ LL，GEARBOX VARCHAR（255）NOT
NULL，KILOMETERS INTEGER NOT NULL，MODEL VARCHAR（255）NOT NULL ，PRICE
INTEGER NOT NULL，TYPE VARCHAR（255）NOT NULL，YEAR INTEGER NOT NULL，
EQUIPM \ ENT_ID BIGINT，SECURITY_ID BIGINT，PRIMARY KEY（ID））：
java。 sql.SQLSyntaxErrorException：语法错误：在
行1列263处遇到YEAR。Jul 01，2012 4:33:05 PM
org.glassfish.persistence.common.Java2DBProcessorHelper executeDDLs
警告：PER01000：得到SQLException执行语句CREATE TABLE
VEHIC LE（ID BIGINT NOT NULL，使VARCHAR（255）不为NULL，模型
VARCHAR（255）NOT NULL，TRIM VARCHAR（255）\ NOT NULL，YEAR INTEGER NOT
NULL，PRIMARY KEY ））：java.sql.SQLSyntaxErrorException：语法
error：在第1行第100列遇到TRIM。Jul 01，2012 4:33:06
PM org.glassfish.persistence.common。 Java2DBProcessorHelper executeDDLs
警告：PER01000：得到SQLException执行语句ALTER TABLE
CAR添加约束FK_CAR_SECURITY_ID FOREIGN KEY（SECURITY_ID）
REFERENCES SECURITY（ID）：java.sql.SQLSy\ ntaxErrorException： 'ALTER
TABLE'不能在'CAR'上执行，因为它不存在。 Jul 01，
2012 4:33:06 PM
org.glassfish.persistence.common.Java2DBProcessorHelper executeDDLs
警告：PER01000：已得到SQLException执行语句ALTER TABLE
CAR ADD CONSTRAINT CAR_EQUIPMENT_ID FOREIGN KEY（EQUIPMENT_ID）
REFERENCES EQUIPMENT（ID）：java.sql.SQLSy\ ntaxErrorException：'ALTER
TABLE'无法在'CAR'上执行，因为它不存在。 7月01，
2012 4:33:06 PM com.sun.ejb.containers.BaseContainer initializeHome
INFO：EJB5181：EJB的可移植JNDI名称VehicleEJB：
[java：global / classes / VehicleEJB！com.thevehiclelist.VehicleEJB，
java：global / classes / VehicleEJB] Jul 01，2012 4:33:06 PM
com.sun.ejb.containers.BaseContainer initializeHome INFO：
EJB5181：EJB BookEJB的可移植JNDI名称：
[java：global / classes / BookEJB！se.while_se.BookEJB，
java：global / classes / BookEJB] Jul 01，2012 4:33:06 PM
com.sun.ejb.containers.BaseContainer initializeHome INFO：
EJB5181：EJB的可移植JNDI名称CarEJB：
[java：global / classes / CarEJB，
java：global / class / CarEJB！se.while_se.CarEJB] Jul 01，2012 4:33:07 PM
org.eclipse.persistence.session.file：/ tmp / gfembed468260415984552053tmp / applications / classes / _carcmsPU
警告：本地异常堆栈：异常[EclipseLink-4002]（Eclipse
持久服务 - 2.3.2.v20111125-r10461）：
org.eclipse .persistence.exceptions.DatabaseException内部
异常：java.sql.SQLSyntaxErrorException：语法错误：在第1行第39列遇到
TRIM错误代码：20000调用：INSERT INTO
VEHICLE （ID，MAKE，MODEL，TRIM，YEAR）VALUES（？，？，？，？，？）
bind => [5个参数绑定] Query：InsertObjectQuery（Vehicle [id = 1，year = 2007，make = Volvo，model = V70，trim = 2.4D]）
at org.eclipse.persistence.exceptions.DatabaseException.sqlException（DatabaseException.java:333）
at org.eclipse.persistence.internal.databaseaccess .DatabaseAccessor.basicExecuteCall（DatabaseAccessor.java:644）
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall（DatabaseAccessor.java:535）
at org.eclipse.persistence.internal.sessions .AbstractSession.basicExecuteCall（AbstractSession.java:1717）
在org.eclipse.persistence.sessions.server.ClientSession.executeCall（ClientSession.java:253）
org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall（DatasourceCallQueryMechanism.java:193）
（org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.java:193）
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall（DatasourceCallQueryMechanism.java:193）
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject（DatasourceCallQueryMechanism.java:342）
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject（StatementQueryMechanism.java:162）
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject（StatementQueryMechanism.java:177）
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite（DatabaseQueryMechanism.java:472）
org.eclipse.persistence.queries.InsertObjectQuery.executeCommit（InsertObjectQuery.java:80）
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChan geSet（InsertObjectQuery.java:90）
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet（DatabaseQueryMechanism.java:287）
at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery（ WriteObjectQuery.java:58）
at org.eclipse.persistence.queries.DatabaseQuery.execute（DatabaseQuery.java:844）
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork（DatabaseQuery.java：
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:2871）

我的persistence.xml ：

 <？xml version =1.0encodi NG = UTF-8 >？; 
< persistence xmlns =http://java.sun.com/xml/ns/persistence
 xmlns：xsi =http://www.w3.org/2001/XMLSchema-instance 
 xsi：schemaLocation =http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd
 version = 2.0 > 
 
< persistence-unit name =carcmsPUtransaction-type =JTA> 
< provider> org.eclipse.persistence.jpa.PersistenceProvider< / provider> 
< jta-data-source> jdbc / __ default< / jta-data-source> 
 
< class> se.while_se.Book< / class> 
< class> se.while_se.Car< / class> 
< class> com.thevehiclelist.Vehicle< / class> 
 
 
 
<属性> 
< property name =javax.persistence.jdbc.drivervalue =org.apache.derby.jdbc.EmbeddedDriver/> 
< property name =javax.persistence.jdbc.urlvalue =jdbc：derby：MyTestDb; create = true/> 
< property name =javax.persistence.jdbc.uservalue =/> 
< property name =javax.persistence.jdbc.passwordvalue =/> 
< property name =eclipselink.ddl-generationvalue =drop-and-create-tables/> 
 
< / properties> 
< / persistence-unit> 
< /余辉> 
  

和我的pom.xml

 <依赖性> 
< dependency> 
< groupId> junit< / groupId> 
< artifactId> junit< / artifactId> 
< version> 4.8.1< / version> 
< scope> test< / scope> 
< /依赖关系> 
< dependency> 
< groupId> org.eclipse.persistence< / groupId> 
< artifactId> javax.persistence< / artifactId> 
< version> 2.0.0< / version> 
< /依赖关系> 
< dependency> 
< groupId> org.eclipse.persistence< / groupId> 
< artifactId> eclipselink< / artifactId> 
< version> 2.3.2< / version> 
< /依赖关系> 
< dependency> 
< groupId> org.glassfish.main.extras< / groupId> 
< artifactId> glassfish-embedded-all< / artifactId> 
< version> 3.1.2< / version> 
< scope>提供< / scope> 
< /依赖关系> 
< dependency> 
< groupId> org.apache.derby< / groupId> 
< artifactId> derby< / artifactId> 
< version> 10.8.2.2< / version> 
< scope>提供< / scope> 
< /依赖关系> 
 
< /依赖关系> 
  

我没有任何线索，可以试用Google搜索，但没有找到任何参考代码这个东西..你可以请看看最新的错误在我的配置或建议我的一些文件？

最好的regars

解决方案

第一个错误是您尝试创建名为YEAR的列，但YEAR是Derby中的保留字。您应该选择另一个列名称。第二个是你试图创建一个列TRIM，也是一个保留字。在这里还可以选择另一个列名。

从我所看到的剩余错误是由创建第一个表期间失败引起的。

它可能更容易，也更少出错，首先手动创建表并使用IDE创建相应的持久化类（Netbeans使这非常容易，我假设其他IDE具有类似的功能）。还有一件事：为了避免自动创建255个字符宽的varchar列，您可以在注释中添加所需的长度。在那里你也可以修复数据库的列名。例如，在数据库中使用名为interiortrim的32字符宽非空列，但在代码中修剪：

  @Column（name = INTERIORTRIM，可空= false，长度= 32）
私人字符串修剪; 
  

I develop some unit tests but fail on all SQL statements, can you please look at this log and configuration?

Jul 01, 2012 4:33:05 PM org.glassfish.persistence.common.Java2DBProcessorHelper executeDDLs WARNING: PER01000: Got SQLException executing statement "CREATE TABLE CAR (ID BIGINT NOT NULL, BRAND VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(255), FUEL VARCHAR(255) NOT NU\ LL, GEARBOX VARCHAR(255) NOT NULL, KILOMETRES INTEGER NOT NULL, MODEL VARCHAR(255) NOT NULL, PRICE INTEGER NOT NULL, TYPE VARCHAR(255) NOT NULL, YEAR INTEGER NOT NULL, EQUIPM\ ENT_ID BIGINT, SECURITY_ID BIGINT, PRIMARY KEY (ID))": java.sql.SQLSyntaxErrorException: Syntax error: Encountered "YEAR" at line 1, column 263. Jul 01, 2012 4:33:05 PM org.glassfish.persistence.common.Java2DBProcessorHelper executeDDLs WARNING: PER01000: Got SQLException executing statement "CREATE TABLE VEHICLE (ID BIGINT NOT NULL, MAKE VARCHAR(255) NOT NULL, MODEL VARCHAR(255) NOT NULL, TRIM VARCHAR(255) \ NOT NULL, YEAR INTEGER NOT NULL, PRIMARY KEY (ID))": java.sql.SQLSyntaxErrorException: Syntax error: Encountered "TRIM" at line 1, column 100. Jul 01, 2012 4:33:06 PM org.glassfish.persistence.common.Java2DBProcessorHelper executeDDLs WARNING: PER01000: Got SQLException executing statement "ALTER TABLE CAR ADD CONSTRAINT FK_CAR_SECURITY_ID FOREIGN KEY (SECURITY_ID) REFERENCES SECURITY (ID)": java.sql.SQLSy\ ntaxErrorException: 'ALTER TABLE' cannot be performed on 'CAR' because it does not exist. Jul 01, 2012 4:33:06 PM org.glassfish.persistence.common.Java2DBProcessorHelper executeDDLs WARNING: PER01000: Got SQLException executing statement "ALTER TABLE CAR ADD CONSTRAINT CAR_EQUIPMENT_ID FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT (ID)": java.sql.SQLSy\ ntaxErrorException: 'ALTER TABLE' cannot be performed on 'CAR' because it does not exist. Jul 01, 2012 4:33:06 PM com.sun.ejb.containers.BaseContainer initializeHome INFO: EJB5181:Portable JNDI names for EJB VehicleEJB: [java:global/classes/VehicleEJB!com.thevehiclelist.VehicleEJB, java:global/classes/VehicleEJB] Jul 01, 2012 4:33:06 PM com.sun.ejb.containers.BaseContainer initializeHome INFO: EJB5181:Portable JNDI names for EJB BookEJB: [java:global/classes/BookEJB!se.while_se.BookEJB, java:global/classes/BookEJB] Jul 01, 2012 4:33:06 PM com.sun.ejb.containers.BaseContainer initializeHome INFO: EJB5181:Portable JNDI names for EJB CarEJB: [java:global/classes/CarEJB, java:global/classes/CarEJB!se.while_se.CarEJB] Jul 01, 2012 4:33:07 PM org.eclipse.persistence.session.file:/tmp/gfembed468260415984552053tmp/applications/classes/_carcmsPU WARNING: Local Exception Stack: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "TRIM" at line 1, column 39. Error Code: 20000 Call: INSERT INTO VEHICLE (ID, MAKE, MODEL, TRIM, YEAR) VALUES (?, ?, ?, ?, ?) bind => [5 parameters bound] Query: InsertObjectQuery(Vehicle [id=1, year=2007, make=Volvo, model=V70, trim=2.4D]) at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717) at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:253) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:342) at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162) at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177) at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:472) 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:287) at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844) at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743) 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:2871)

My persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0">

    <persistence-unit name="carcmsPU" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/__default</jta-data-source>

        <class>se.while_se.Book</class>
        <class>se.while_se.Car</class>
        <class>com.thevehiclelist.Vehicle</class>



        <properties>
            <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:derby:MyTestDb;create=true" />
            <property name="javax.persistence.jdbc.user" value="" />
            <property name="javax.persistence.jdbc.password" value="" />
            <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />

        </properties>
    </persistence-unit>
</persistence>

And my pom.xml

<dependencies>
                <dependency>
                    <groupId>junit</groupId>
                    <artifactId>junit</artifactId>
                    <version>4.8.1</version>
                    <scope>test</scope>
                </dependency>
                <dependency>
                    <groupId>org.eclipse.persistence</groupId>
                    <artifactId>javax.persistence</artifactId>
                    <version>2.0.0</version>
                </dependency>
                <dependency>
                    <groupId>org.eclipse.persistence</groupId>
                    <artifactId>eclipselink</artifactId>
                    <version>2.3.2</version>
                </dependency>
                <dependency>
                    <groupId>org.glassfish.main.extras</groupId>
                    <artifactId>glassfish-embedded-all</artifactId>
                    <version>3.1.2</version>
                    <scope>provided</scope>
                </dependency>
                <dependency>
                    <groupId>org.apache.derby</groupId>
                    <artifactId>derby</artifactId>
                    <version>10.8.2.2</version>
                    <scope>provided</scope>
                </dependency>

            </dependencies>

I dont have any clue what it can be, have tryed googling around a lot, but did not found any reference code for this thing.. Can you please see whats wrong in my configuration or advice me for some documentation?

Best regars

解决方案

The very first error is that you try to create a column called YEAR, but YEAR is a reserved word in Derby. You should select another column name. The second one is that you try to create a column TRIM, also a reserved word. Here also select another column name.

From what I can see the remaining errors are caused by the failure during the creation of the first tables.

It might be easier and less error prone to first create the tables manually and the use your IDE to create the corresponding persistence class (Netbeans makes that very easy, I assume other IDEs have a comparable function).

One more thing: to avoid the automatic creation of 255 character wide varchar columns you can add the desired length in the annotation. There you can also fix the database column name. Example, with a 32 char wide not null column called interiortrim in the db but trim in code:

@Column(name = "INTERIORTRIM", nullable = false, length=32)
private String trim;

这篇关于嵌入的Glassfish SQL语句失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！