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.
CodePudding user response：
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.
CodePudding user response：
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 
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.