0

I am a programmer, new to jsp, trying to write some code.

i have written like 

    <%
            int i=1;

    String connectionURL = "jdbc:mysql://localhost:3306/registration";

     Connection connection = null;
        Statement stmt = null;
        ResultSet rset = null;
        Statement stmt1 = null;
        ResultSet rset1 = null;
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    int updateQuery = 0;
    try {
    connection = DBUtil.createConnection();//DriverManager.getConnection(connectionURL, "root", "root");
    String queryString = "select * from course";
    System.out.println("select all from course table");
     stmt = connection.createStatement();
    System.out.println("Statment object "+ stmt );
    rset = stmt.executeQuery(queryString);
    System.out.println("Result set " + rset );
    while (rset.next()) {
                    System.out.println("  Results...");
                    String s1 = rset.getString(1);
                    Double amount = rset.getDouble("amount");
                    String loginid = rset.getString("loginid");

    String queryString1 = "select * from users where loginid = '"+ loginid +"'";
    System.out.println("select user details from users table");
    System.out.println(queryString1);
     stmt1 = connection.createStatement();
    System.out.println("Statment object "+ stmt1 );
    rset1 = stmt1.executeQuery(queryString1);
    System.out.println("Result set " + rset1 );

             System.out.println("      name -> " + rset1.getString(2));
     %>
        <tr class="subtext1">
<td align="left"><%=i%> </td>
<td align="left"><%=rset.getString("name")%></td>
<td align="left"><%=rset.getString("loginid")%></td>
<td align="left"><%=rset.getString("name")%></td>
<td align="left"><%=rset.getString("email")%></td>
<td align="left"><%=rset.getString("iimbtrack")%></td>
<td align="left"><%=rset.getString("result")%></td>
<td align="left">CourseFee/<%=rset.getString("product")%></td>
<td align="left"><%=amount%></td>
<td align="left"><%=rset.getString("fdate")%></td>
</tr>

When I try to print this, it gives error. But when I remove this line:

System.out.println("      name -> " + rset1.getString(2));

It works. Where am I going wrong?

This is the error I get

statment object com.mysql.jdbc.Statement@429be9
Result set com.mysql.jdbc.ResultSet@10a0d51
java.sql.SQLException: Before start of result set
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
        at com.mysql.jdbc.ResultSet.checkRowPos(ResultSet.java:703)
        at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5653)
        at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5597)
        at org.apache.jsp.WEB_002dINF.jsp.secure.transaction.feesreports_jsp._jspService(feesreports_jsp.java:235)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:679)
        at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:461)
        at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:399)
        at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301)
        at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1056)
        at org.apache.struts.tiles.TilesRequestProcessor.doForward(TilesRequestProcessor.java:261)
        at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:388)
        at org.apache.struts.tiles.TilesRequestProcessor.processForwardConfig(TilesRequestProcessor.java:316)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:231)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1158)
        at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:627)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Hacker
  • 7,798
  • 19
  • 84
  • 154
  • You should use a PreparedStatement for your second query because (1) it's called once for every loop iteration and (2) it has a parameter. Create the object outside of the while loop, then set the parameter inside the while loop. `PreparedStatement ps = conn.preparedStatement("select * from users where loginid = ?"); ps.setString(1, loginId);` – Michael Jul 07 '10 at 23:32

3 Answers3

3

You need to call ResultSet#next() to move the cursor to the next row, otherwise you cannot access any data from the row. If you expect multiple rows, do this in a while loop:

while (rset1.next()) {
    System.out.println(" name -> " + rset1.getString(2));
}

Or if you expect only one row, then do this in an if statement:

if (rset1.next()) {
    System.out.println(" name -> " + rset1.getString(2));
}

See also:

That said, this code is honestly said not the right way to achieve the functional requirement of displaying the data from a DB in a HTML table. This database interaction task shouldn't be done inside a JSP file, but in a real Java class. The JSP page should be kept scriptlet-free.

See also:

Community
  • 1
  • 1
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
1

What is the exact error you are getting? My suspicion is that there is only one column in "users" table while you are asking for second column data. Or if there is no data then the "rset1" will be null.

I strongly suggest you to look for the MVC pattern.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Aravind Yarram
  • 78,777
  • 46
  • 231
  • 327
  • now that you have posted the stack trace the solution is as suggested by "BalusC" – Aravind Yarram Jul 07 '10 at 18:47
  • `ResultSet` is never, **never**, `null`. If there is no data then `ResultSet#next()` will simply return `false`. Or if something fails during obtaining the data, then just a `SQLException` will be thrown. Also see the **Returns:** entry of the [Statement#executeQuery() javadoc](http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#executeQuery%28%29) – BalusC Jul 07 '10 at 18:58
  • you are right...becaz of a quick galnace on the javadoc i got confused "never null" with "null" – Aravind Yarram Jul 07 '10 at 19:09
1

You should explicitly list the columns you are requesting rather than using a wildcard. There's no guarantee as to the order of columns in your result set otherwise. Consider what would happen if somebody added a column in the middle of the table (and yes, this does happen).

TMN
  • 3,060
  • 21
  • 23