Member Avatar for Hakoo

I connected jsp with mysql. i m beg. level programmer in JSP. So, plz dont comment on my code writing style, bt plz help me to improve ma programming. I can execute query bt whn i try RESULTSET to retrive, getting error.
my code is below :

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

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>

   <body>
<%!     Connection Db;
                ResultSet rs;
                Statement Dr;
                String query, pass, typ,pnt,user;

%>

 <%
            Class.forName ("com.mysql.jdbc.Driver");
                Db = DriverManager.getConnection("jdbc:mysql://localhost/bug_track", "root","root");
                query= "Select * from login" ;
                Dr = Db.createStatement();
                rs=Dr.executeQuery(query);


do {
 user = rs.getString(1);
 pass = rs.getString(2);
 typ = rs.getString(3);
 pnt = user + "   "  +pass+"  "+typ;
 %>

 <p> <%=pnt%> </p>
 <%
 }while(rs.next());
     %>


 </body>
</html>
Edited by mike_2000_17 because: Fixed formatting
  • 2 Contributors
  • 3 Replies
  • 123 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Smith5646

Dani AI

Generated

The exception is coming from reading the ResultSet before moving its cursor. After executeQuery() the cursor is positioned before the first row, so calling getString(...) immediately (as in the lines you reported) raises an SQLException. is correct: iterate only after rs.next() (use while(rs.next()) to process all rows, or if(rs.next()) when expecting a single row).

For a safer, modern approach move database logic out of the JSP and use a DAO or servlet. Use try-with-resources, a PreparedStatement, column names (not numeric indexes) and a DataSource/connection pool. Example DAO pattern (simplified) that demonstrates resource handling and iteration:

public List<User> fetchAll(DataSource ds) throws SQLException {
    List<User> users = new ArrayList<>();
    String sql = "SELECT username, password, type FROM login";
    try (Connection c = ds.getConnection();
         PreparedStatement ps = c.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            users.add(new User(
                rs.getString("username"),
                rs.getString("password"),
                rs.getString("type")
            ));
        }
    }
    return users;
}

Additional notes and cautions: do not embed DB credentials or JDBC code directly in JSP; use JSTL/EL or populate a request attribute in a servlet and iterate in the view. Use PreparedStatement to avoid SQL injection. Close/auto-close Connection, Statement and ResultSet (try-with-resources). For current MySQL drivers, review the Connector/J docs and the JDBC basics for correct driver and connection options.

References: JDBC basics (Oracle JDBC tutorial) and MySQL Connector/J docs ().

Recommended Answers

All 3 Replies

Member Avatar for Smith5646

It has been a while so I could be remembering this incorrectly.

I believe you need to do your rs.next() before your rs.getstring(). The execute query does not retrieve any data from the database, only prepares it for the first rs.next(). By having a do...while, your while is not executed until the end of the do loop.

Try changing your
do
{
}while rs.next()
to
while rs.next()
{
}

Let me know if that doesn't fix the problem.

Member Avatar for Hakoo

I connected jsp with mysql. i m beg. level programmer in JSP. So, plz dont comment on my code writing style, bt plz help me to improve ma programming. I can execute query bt whn i try RESULTSET to retrive, getting error.
my code is below :

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

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
   
   <body>
<%!     Connection Db;
                ResultSet rs;
                Statement Dr;
                String query, pass, typ,pnt,user;

%>

 <%
            Class.forName ("com.mysql.jdbc.Driver");
                Db = DriverManager.getConnection("jdbc:mysql://localhost/bug_track", "root","root");
                query= "Select * from login" ;
                Dr = Db.createStatement();
                rs=Dr.executeQuery(query);


do {

 pnt = user + "   "  +pass+"  "+typ;
 %>

 <p> <%=pnt%> </p>
 <%
 }while(rs.next());
     %>


 </body>
</html>

I copied ths code from "Complete Reference : J2EE". Actually, i don thnk thr is problem. but getting error in,

user = rs.getString(1);
 pass = rs.getString(2);
 typ = rs.getString(3);
Member Avatar for Smith5646

Remember that a "do while" loop executes the logic inside the loop and then executes the while. In your case, you are trying to do an rs.getString(1) but have never done the rs.next to load the values.

Your code should look like this

<%
while(rs.next)
{
      user = rs.getString(1);
      pass = rs.getString(2);
      typ = rs.getString(3);
%>
pnt = user + " " +pass+" "+typ;
%>
<p> <%=pnt%> </p>
<%
}; <== Not sure if you need the ";".  I forget the syntax for the end of a while loop.
%>

If this still blows up, please repost your code and tell exactly what line is blowing up.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.