I am working on this particular problem now for several days and cannot find any solutions. So I'm here.
Situation:
- Table 1 lists all the JBoss in the system.
- Table 2 shows the 1: n ratio of JBoss and JBoss, since any JBoss can have one or more JBoss that it "speaks" to.
- I have a Java servlet with JDBC to connect to a database and issue queries and .jsp pages to display the results.
EDIT:
What I intend to do with the request: My servlet is trying to display all the information associated with this jboss. The user selects jboss from the list of jboss names from the jsp page. Then the query is executed and an error occurs.
EDIT2:
Changing a request for an internal subsegment to where in results in the same error when testing an applet
EDIT4:
I tried to create the view with the request as a script, and then tried to get all the records from the created view using select * from vtest without success.
Problem:
When I try to run the next select with a servlet, this leads to ORA-01427. If I run this statement in Toad for Oracle , I get the desired result.
select * from table1 where number in ( select jboss2 from table2 where jboss1 = ( select number from table1 where name = 'nam1'))
Question: Is there something that I am missing in the request? Is it possible that jdbc cannot handle sub-queries with multiple rows as a result, even if the request itself is correct?
Table 1:
+--------+------+-----------+ | Number | Name | values... | +--------+------+-----------+ | 000001 | nam1 | vals1 | | 000002 | nam2 | vals2 | | 000003 | nam3 | vals3 | +--------+------+-----------+
Table 2:
+--------+--------+ | JBoss1 | JBoss2 | +--------+--------+ | 000001 | 000002 | | 000001 | 000003 | | 000002 | 000003 | +--------+--------+
Result in the toad / desired result:
+--------+------+-----------+ | Number | Name | values... | +--------+------+-----------+ | 000002 | nam2 | vals2 | | 000003 | nam3 | vals3 | +--------+------+-----------+
EDIT3:
Relevant Java classes. Missed irrelevant requests.
class QuickInfoAction implements Action{ @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws ActionException { Connection conn = null; PreparedStatement prep = null; ResultSet rs = null; Map<String,String> queries = Queries.getInfoQueries(request); try { conn = DatabaseConnector.getConnection(); Map<String, Result> res = new HashMap<String, Result>(); for (Map.Entry<String, String> entry: queries.entrySet()) { prep = conn.prepareStatement(entry.getValue()); rs = prep.executeQuery(); while(rs.next()) { res.put(entry.getKey(), ResultSupport.toResult(rs)); } } request.setAttribute("results", res); } catch (Exception e) { throw new ActionException(e.getStackTrace().toString()); } finally { try { conn.close(); prep.close(); rs.close(); } catch (Exception e) { throw new ActionException(e.getStackTrace().toString()); } } return "results"; } } public static Map<String, String> getInfoQueries(HttpServletRequest request) { String jboss_res = "select jboss.name, jboss.port, jboss.apache_nummer, jboss.bere_mandant_id, " + "maschine.name as maschine, maschine.ip_adresse " + "from jboss " + "inner join maschine on jboss.maschine_nummer = maschine.nummer " + "where jboss.name = '" + request.getParameter("jboss") + "'"; String jboss_db = "select datenbank.nummer, datenbank.name, db_schema.name as schema " + "from datenbank " + "inner join db_schema on datenbank.db_schema_nummer = db_schema.nummer " + "where datenbank.nummer = (" + "select datenbank_nummer " + "from jboss_datenbank " + "where jboss_nummer = (" + "select nummer " + "from jboss " + "where name = '" + request.getParameter("jboss") + "'))"; String jboss_tux = "select tuxedo.*, datenbank.name as datenbank, db_schema.name as schema " + "from tuxedo, datenbank,db_schema " + "where tuxedo.nummer = (" + "select tuxedo_nummer " + "from jboss " + "where name = '" + request.getParameter("jboss") + "') " + "and datenbank.nummer = (" + "select datenbank_nummer " + "from tuxedo_datenbank " + "where tuxedo_nummer = tuxedo.nummer) " + "and db_schema.nummer = (" + "select db_schema_nummer " + "from datenbank " + "where nummer = (" + "select datenbank_nummer " + "from tuxedo_datenbank " + "where tuxedo_nummer = tuxedo.nummer))"; String jboss_corr = "select * from jboss where nummer in (" + "select jboss_nummer_2 from jboss_corr where jboss_nummer_1 in (" + "select nummer from jboss where name = '" + request.getParameter("jboss") + "'))"; Map<String, String> queries = new HashMap<String,String>(); queries.put("jboss", jboss_res); queries.put("datenbank", jboss_db); queries.put("tuxedo", jboss_tux); queries.put("corr", jboss_corr); return queries;
Error message
03.07.2017 11:49:29,863 +0200 WARN [at.itsv.ta2mig.jdbc.TA2MigOracleJDBCConnection] (hs0903 http-/0.0.0.0:8080-2) ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile 03.07.2017 11:49:29,864 +0200 INFO [stdout] (hs0903 http-/0.0.0.0:8080-2) error executing action 03.07.2017 11:49:29,864 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) dbgr.exception.ActionException: [Ljava.lang.StackTraceElement;@46708550 03.07.2017 11:49:29,864 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at dbgr.action.QuickInfoAction.execute(QuickInfoAction.java:43) 03.07.2017 11:49:29,864 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at dbgr.servlet.ControllerServlet.doGet(ControllerServlet.java:28) 03.07.2017 11:49:29,865 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at dbgr.servlet.ControllerServlet.doPost(ControllerServlet.java:39) 03.07.2017 11:49:29,865 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) 03.07.2017 11:49:29,865 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) 03.07.2017 11:49:29,865 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295) 03.07.2017 11:49:29,865 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) 03.07.2017 11:49:29,865 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231) 03.07.2017 11:49:29,866 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) 03.07.2017 11:49:29,866 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169) 03.07.2017 11:49:29,866 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:150) 03.07.2017 11:49:29,866 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97) 03.07.2017 11:49:29,866 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102) 03.07.2017 11:49:29,866 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) 03.07.2017 11:49:29,866 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:854) 03.07.2017 11:49:29,867 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653) 03.07.2017 11:49:29,867 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926) 03.07.2017 11:49:29,867 +0200 ERROR [stderr] (hs0903 http-/0.0.0.0:8080-2) at java.lang.Thread.run(Thread.java:744)