problem in setString method.in retrieving data from the table

i have two tables "Table1" with columns user_name,Password and course ID and another table "course" with columns course_id,course_name.I have used the following code to display the course ID from Table1 according to the user_name received from the login page.using ResultSet rs1.now i want to retrieve the course_name from the table "course" according to the course ID receieve from "Tab开发者_Python百科le1".for that in the second query pstmt2.setString(1, ); what parameter i should use to get the course_id value from the previous query

    HttpSession sess=request.getSession();

    String a=(String)sess.getAttribute("user");
    String b=(String)sess.getAttribute("pass");

    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con=DriverManager.getConnection("jdbc:odbc:ggg");
        Statement st = con.createStatement();



                String query="select * from Table1  where user_name=?";
                PreparedStatement pstmt=con.prepareStatement(query);
                pstmt.setString(1,a);
                ResultSet rs1=pstmt.executeQuery();
        while(rs1.next())

          out.println("<h3>COURSE ID:&nbsp;"+rs1.getString("course ID")+"<h3>");


                String query2="SELECT * from course where course_id=?";
                PreparedStatement pstmt2=con.prepareStatement(query2);

               pstmt2.setString(1,);

               ResultSet rs2=pstmt2.executeQuery();

        while(rs2.next())
        {
          out.println("<h3>course name:&nbsp;"+rs2.getString("course_name")+"<h3>");
        }

why do you go for two turns of database hit, even though you created one time connection object.

modify the query as below

SELECT * from course where course_id = (select course_id from Table1 where user_name=?);

from this query you noneed to give input of courseid also.

No need to hit database twice to get the results that you need. use the query

Select table1.course_id, course.course_name from table1, course where table1.course_id=course_id and table1.user_name=?

This should set the course_id parameter:

pstmt2.setString(1,rs1.getString("course_id"));

Or, as I see the "course_id" column may have a different name in "Table1":

pstmt2.setString(1,rs1.getString("course ID"));

As the other post mentioned there's no need to go to another set of query. Try this example query:

SELECT course.course_id, course.course_name
  FROM table1 t1
 INNER JOIN course c
    ON t1.course_id = c.course_id
 WHERE t1.user_name = ?;

Now if you insist your coding the parameter o your pstmt2.setString(1,); is:

pstmt2.setString(1,rs1.getString("course_id")); //or course ID defending on your column name