CODEWITHSUNDEEP
javamysql
Ryan
Ryan

Reputation: 27

Why ResultSet isn't null,But I can't get data from the ResultSet

public List<Course> searchCourse(String name) {
        List<Course> list = null;
        Course course = null;
        PreparedStatement pstmt = null; 
        ResultSet rs = null,rs1 = null;

        String sql = "select username,course_name from view_teacourse where username=? or course_name=? ";

        try{
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setString(2, name);
            rs = pstmt.executeQuery();
            while(next()){
                String sql1 = "select * from courseinfo where course_name=?";
                pstmt.setString(1, rs.getString("cousre_name"));
                rs1 = pstmt.executeQuery();
                while(rs1.next()){
                    course.setCourseid(rs.getInt("course_id"));
                    course.setCourseName(rs1.getString("course_name"));
                    course.setCourseScheme(rs.getString("course_scheme"));
                    course.setCourseBook(rs.getString("course_book"));
                    course.setCourseTerm(rs.getString("course_term"));
                    course.setIntroduction(rs.getString("introduction"));
                    course.setStartTime(rs.getTimestamp("start_time"));
                    course.setEndTime(rs.getTimestamp("end_time"));
                    course.setValidation(rs.getBoolean("validation"));
                    course.setCourseType(rs.getInt("coursetypeid"));
                    list.add(course);
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBBase.releaseResultSet(rs1);
            DBBase.releaseResultSet(rs);
            DBBase.releaseStatement(pstmt);
            DBBase.releaseConnection(conn);
        }
        return list;
    }

The value of rs.next() is getting false. It cannot go into the while loop. But if I write rs.wasNull() so I got false again. So I'm confused. I can get the right result use the same SQL in my database. When it in a java file it doesn't work! I can't find where is wrong!

Upvotes: 0

Views: 424

Answers (3)

drew moore
drew moore

Reputation: 32680

while(next()){ needs to be while(rs.next()){, doesn't it?

(it does, but @duffymo's answer is the one you should probably pay attention to)

Upvotes: 5

duffymo
duffymo

Reputation: 309008

You can write code this way, but it's a classic (n+1) latency trap. It'll perform worse and worse as the size of the outer result set grows.

You can also bring all the data back in one network roundtrip if you learn how to write a JOIN properly.

If you must do it this way, I would create two PreparedStatement instances: one for the outer loop and another for the inner one.

Upvotes: 1

Tom Hawtin - tackline
Tom Hawtin - tackline

Reputation: 147164

This certainly looks like a typo:

pstmt.setString(1, rs.getString("cousre_name"));

You also don't seem to be assigning anything but null to course. Java's definite assignment rules are your friends; null is not.

Are you sure this is exactly the code you are running?

Upvotes: 0

Related Questions