mysql - Multiple sql queries in one connection using java -
i'm trying make different queries in single db connection can't find way. query in function of wery results before. code:
string query_disco="select * disco"; string name = ""; string adress=""; string web=""; int disco_id=0; string query_disco_var="select * disco_var"; float price=0; string style=""; string hin=""; string hout=""; string party_name=""; int day=0; string doctype=""; string doctable=""; string docbody=""; calendar calendar = calendar.getinstance(); try { dao a= new dao(); resultset b =a.executarsql(query_disco); day = calendar.get(calendar.day_of_week); doctype ="<!doctype html public\"--รข€?//w3c//dtd html 4.0 " +"transitional//en\">\n"; docbody = "<html>\n"+ "<head><title>helloworld!</title></head>\n"+ "<body bgcolor=\"#fdf5e6\">\n"+ "<h1>disco</h1>\n"+ "<table border=1>"+ "<tr> <th> name </th> <th> adress </th> <th> web </th> <th> price </th>" + "<th> style </th> <th> horari </th> <th> party name </th> </tr>"; while(b.next()){ disco_id=b.getint("disco_id"); name=b.getstring("name"); adress=b.getstring("adress"); web=b.getstring("web"); query_disco_var = query_disco_var + " disco_id = " + disco_id + " , day = " + day; dao d= new dao(); resultset c = d.executarsql(query_disco_var); price=c.getfloat("price"); style=c.getstring("style"); hin=c.getstring("hin"); hout=c.getstring("hout"); party_name=c.getstring("party_name"); doctable = doctable + "<tr> <td> "+name+" </td> <td> "+adress+" </td> <th>" +web+ "</td> </tr>"+ price+ "</td> <td>" +style+ "</td> <td>" +hin+ "-" +hout+ "</td> <td>" +party_name+ "</td> </tr>"; query_disco_var="select * disco_var"; } } catch (exception e) { // todo auto-generated catch block e.printstacktrace(); } out.println(doctype+docbody+doctable+ "</table>" + "</body></html>"); } /** * @see httpservlet#dopost(httpservletrequest request, httpservletresponse response) */ protected void dopost(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { // todo auto-generated method stub } }
the connection de db works right script:
public class dao { private connection connexio; private statement sentencia; /** * * @throws exception */ public dao() throws exception { string user="trmcfunt"; string password="44445555"; class.forname("com.mysql.jdbc.driver").newinstance(); connexio= drivermanager.getconnection("jdbc:mysql://localhost/iguanaparty_db_test?user="+user+"&password="+password); sentencia=connexio.createstatement(); } /** * * @param query * @return * @throws sqlexception */ public resultset executarsql(string query)throws sqlexception{ return sentencia.executequery(query); } /** * * @throws sqlexception */ public void desconnectarbd() throws sqlexception { sentencia.close(); connexio.close(); } }
an i'm getting exepcions:
java.sql.sqlexception: before start of result set @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:1074) @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:988) @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:974) @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:919) @ com.mysql.jdbc.resultsetimpl.checkrowpos(resultsetimpl.java:854) @ com.mysql.jdbc.resultsetimpl.getstringinternal(resultsetimpl.java:5772) @ com.mysql.jdbc.resultsetimpl.getstring(resultsetimpl.java:5692) @ com.mysql.jdbc.resultsetimpl.getfloat(resultsetimpl.java:2615) @ com.mysql.jdbc.resultsetimpl.getfloat(resultsetimpl.java:2635) @ controllers.helloworld.doget(helloworld.java:87) @ javax.servlet.http.httpservlet.service(httpservlet.java:621) @ javax.servlet.http.httpservlet.service(httpservlet.java:728) @ org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:305) @ org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:210) @ org.apache.catalina.core.standardwrappervalve.invoke(standardwrappervalve.java:222) @ org.apache.catalina.core.standardcontextvalve.invoke(standardcontextvalve.java:123) @ org.apache.catalina.authenticator.authenticatorbase.invoke(authenticatorbase.java:502) @ org.apache.catalina.core.standardhostvalve.invoke(standardhostvalve.java:171) @ org.apache.catalina.valves.errorreportvalve.invoke(errorreportvalve.java:99) @ org.apache.catalina.valves.accesslogvalve.invoke(accesslogvalve.java:953) @ org.apache.catalina.core.standardenginevalve.invoke(standardenginevalve.java:118) @ org.apache.catalina.connector.coyoteadapter.service(coyoteadapter.java:408) @ org.apache.coyote.http11.abstracthttp11processor.process(abstracthttp11processor.java:1023) @ org.apache.coyote.abstractprotocol$abstractconnectionhandler.process(abstractprotocol.java:589) @ org.apache.tomcat.util.net.jioendpoint$socketprocessor.run(jioendpoint.java:310) @ java.util.concurrent.threadpoolexecutor.runworker(threadpoolexecutor.java:1145) @ java.util.concurrent.threadpoolexecutor$worker.run(threadpoolexecutor.java:615) @ java.lang.thread.run(thread.java:724) regards.
resultset c = d.executarsql(query_disco_var); price = c.getfloat("price"); before getting values result set, must call next(), place cursor on first row. otherwise, mentioned in exception, you're before start of result set:
resultset c = d.executarsql(query_disco_var); if (c.next()) { price = c.getfloat("price"); also, learn prepared statements, because passing parameters query concatenating them makes code vulnerable sql injection attacks, ad break as, example, 1 of string parameters contains single quote.
Comments
Post a Comment