java.sql.SQLSyntaxErrorException：表/视图'x'不存在 [英] java.sql.SQLSyntaxErrorException: Table/View 'x' does not exist

问题描述

我试图在带有GlassFish 4.1的NetBeans 8.0.2 IDE中运行一个名为 AddressBook 的Web应用程序（带有Facelets的JSF），它通过以下代码访问关系数据库在托管bean中：

I am trying to run a web application called AddressBook (JSF with Facelets) in the NetBeans 8.0.2 IDE with GlassFish 4.1 that accesses a relational database via the following code in the managed bean:

@Resource( name="jdbc/addressbook" )
DataSource dataSource;

当我运行应用程序时，我在浏览器中收到以下错误：

When I run the application I get the following error in the browser:

java.sql.SQLSyntaxErrorException: Table/View 'ADDRESSES' does not exist
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
    at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:586)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:255)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:52)
    at com.sun.gjc.spi.ManagedConnectionImpl.prepareCachedStatement(ManagedConnectionImpl.java:992)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:173)
    at addressbook.AddressBean.getAddresses(AddressBean.java:157)

AddressBean.java第157行是：

AddressBean.java line 157 is:

PreparedStatement getAddresses = connection.prepareStatement(
            "SELECT FIRSTNAME, LASTNAME, STREET, CITY, STATE, ZIP " +
            "FROM ADDRESSES ORDER BY LASTNAME, FIRSTNAME" );

包含ADDRESSES表的地址簿数据库是通过以下步骤创建的：

1. 创建连接池

1. Create Connection Pool

1. 右键单击GlassFish服务器并选择View Domain Admin Console


2. 在左侧列的Common Tasks的GlassFish网页中单击


3. JDBC连接游泳池


4. 点击新按钮并添加以下条目：
   
   
   
   
   • AddressBookPool for the Name
   
   
   • 资源类型的javax.sql.DataSource
   
   
   • 数据库供应商的JavaDB
   
   

1. Right click on GlassFish server and select View Domain Admin Console
2. In the GlassFish web page in Common Tasks on the left column click on
3. JDBC Connection Pools
4. Clicked the New Button and added the following entries:
   
   • AddressBookPool for the Name
   • javax.sql.DataSource for Resource Type
   • JavaDB for the Database Vendor

• 属性：; create = true


• DatabaseName：addressbook


• 密码：APP


• 点击完成

• Attributes: ;create=true
• DatabaseName: addressbook
• Password: APP
• Click Finish

创建数据源名称

Create Data Source Name

1. 在左栏的Common Tasks的GlassFish网页中，点击
   
   JDBC资源


2. 点击新建Button并指定JDBC / addressbook作为JNDI名称


3. 选择AddressBookPool作为池名称

当我返回NetBeans服务选项卡时，已在Java DB下创建了地址簿。

When I return to the NetBeans Services tab, addressbook has been created under Java DB.

我成功连接到数据库：jdbc：derby：// localhost：1527 / addressbook [APP on APP]并使APP成为默认架构

I successfully connect to the database: jdbc:derby://localhost:1527/addressbook [APP on APP] and make APP the default schema

我在NetBeans中打开一个文件：addressbook.sql，其中包含用于填充地址簿的SQL。我在上面的连接上运行它，并创建了ADDRESSES表，我可以查看数据。

I open a file in NetBeans: addressbook.sql which contains SQL to populate addressbook. I run it on the above connection and ADDRESSES table is created and I am able to view the data.

AddressBook属性将Java DB Driver作为库。

The AddressBook properties has Java DB Driver as a library.

推荐答案

确保您的web.xml文件（在Configuration Files文件夹中）具有资源引用。示例：

Make sure that your web.xml file (in Configuration Files folder) has the resource reference. Example:

    <resource-ref>
        <res-ref-name>jdbc/db1</res-ref-name>
        <res-type>javax.sql.ConnectionPoolDataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>

其中db1是您的jdbc资源名称。还要确保该值具有正确的资源类型，例如示例中的连接池数据源。

Where db1 is your jdbc resource name. Also ensure that the value has the correct resource type, a connection pool data source in the example.

这篇关于java.sql.SQLSyntaxErrorException：表/视图'x'不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！