Table not found - JDBC, NetBeans, Tomcat

Question

i am running Java 17.0.1 with Netbeans 12.5, local Apache Tomcat 8.5.73 and JDBC with derbyDB. The Select statement in the code below is not executed, no rows are displayed in the table.

My jsp file:

<%@ page import="java.sql.*" %>

<%
String id = request.getParameter("userid");
String driverclass = "org.apache.derby.jdbc.ClientDriver";
String connectionUrl = "jdbc:derby://localhost:1527/";
String database = "Shop";
String username = "deradmin";
String password = "admin";

try {
Class.forName(driverclass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
%>

<!DOCTYPE html>
<html>
    <body>
        <h1>Shopdatenbank</h1>
        <h2>Kunden</h2>
        <table border="1">
            <thead>
                <tr>
                    <td>Name</td>
                    <td>Strasse</td>
                    <td>Plz</td>
                    <td>Ort</td>
                </tr>
            </thead>
            <%
            try{
            DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());
            connection = DriverManager.getConnection(connectionUrl+database, username, password);
            statement=connection.createStatement();
            String sql ="SELECT * FROM Kunde";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
            %>
            <tbody    
                <tr>
                    <td><%=resultSet.getString("Name") %></td>
                    <td><%=resultSet.getString("Strasse") %></td>
                    <td><%=resultSet.getString("Plz") %></td>
                    <td><%=resultSet.getString("Ort") %></td>
                </tr>
            </tbody>
            <%
            }
            connection.close();
            statement.close();
            } catch (Exception e) {
            e.printStackTrace();
            }
            %>
        </table>
    </body>
</html>

I get following error in the Tomcat log:

java.sql.SQLSyntaxErrorException: Tabelle/View 'KUNDE' ist nicht vorhanden.
    at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:94)
    at org.apache.derby.client.am.SqlException.getSQLException(SqlException.java:325)
    at org.apache.derby.client.am.ClientStatement.executeQuery(ClientStatement.java:514)
    at org.apache.jsp.index_jsp._jspService(index_jsp.java:155)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:465)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:383)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:331)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:196)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:698)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:364)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:624)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1673)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: ERROR 42X05: Tabelle/View 'KUNDE' ist nicht vorhanden.
    at org.apache.derby.client.am.ClientStatement.completeSqlca(ClientStatement.java:2116)
    at org.apache.derby.client.net.NetStatementReply.parsePrepareError(NetStatementReply.java:579)
    at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(NetStatementReply.java:145)
    at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(NetStatementReply.java:54)
    at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(StatementReply.java:41)
    at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(NetStatement.java:156)
    at org.apache.derby.client.am.ClientStatement.readPrepareDescribeOutput(ClientStatement.java:1702)
    at org.apache.derby.client.am.ClientStatement.flowExecute(ClientStatement.java:2381)
    at org.apache.derby.client.am.ClientStatement.executeQueryX(ClientStatement.java:520)
    at org.apache.derby.client.am.ClientStatement.executeQuery(ClientStatement.java:505)
    ... 29 more

"ist nicht vorhanden." - > Table not existing

The table is existing in the DB and I can execute the query without problems in Netbeans. The page is also opened in browser.

Not sure if there is an error in the code or my configuration.

Check what schema you are in. And query for a list of all tables. — Basil Bourque, Nov 25 '21 at 18:16
Hi @BasilBourque, sorry, forgot to mention I am using DerbyDB — DerDave, Nov 25 '21 at 19:53

score 0 · Answer 1 · answered Nov 25 '21 at 19:28

0

First of all, check according to Basil Bourque comment. Second, I don't know with database you use, but some databases, for example PostgreSQL, require commit after creating table, and table won't be visible to other connections without commit.

answered Nov 25 '21 at 19:28

Vladimir Shadrin

312
3
12

I'm using DerbyDB (Java DB). As far as I know there is a Auto-Commit function enabled. I already thought of something similar. I disconnected the DB to test if inserts are deleted, but everything was still available after reconnecting. – DerDave Nov 25 '21 at 20:31

score 0 · Answer 2 · answered Nov 25 '21 at 23:41

Wild guess: Multiple schemas

You have not provided the code that creates a table. So we cannot answer precisely. But my guess is that you have unwittingly switched between schema.

So one schema contains the table you created earlier. Then later you connect to another schema, but alas no such table found in this other schema.

To quote this Derby documentation:

The current schema for any connection defaults to a schema corresponding to the user name. If no user name is supplied then the user name (and hence current schema) defaults to APP.

However even though the current schema is set to the user name, that schema may not exist. A schema is only created by CREATE SCHEMA or creating an object (table etc.) in that schema (this is implicit schema creation).

The one exception to this is the APP schema, which is always created, though applications should not depend on that.

If you do not know the meaning of a schema, see this Answer of mine. That page explains this diagram of mine (meant for Postgres, but apparently applicable to Derby as well except for that default port number 5,432).

Troubleshooting

You should query through your JDBC connection to get a list of all tables in the current schema.

And use your database admin tool to peruse a list of all schema in that database.

Hello, table was created manually in the Database and there is only one schema. I will try to generate the Table and all rows directly in the jsp. — DerDave, Nov 26 '21 at 09:12

Table not found - JDBC, NetBeans, Tomcat

2 Answers2

Wild guess: Multiple schemas

Troubleshooting