Re: how to resolve org.postgresql.util.PSQLException: ResultSet notpositioned properly, perhaps you need to call next. - Mailing list pgsql-jdbc
| From | Rob Sargent |
|---|---|
| Subject | Re: how to resolve org.postgresql.util.PSQLException: ResultSet notpositioned properly, perhaps you need to call next. |
| Date | |
| Msg-id | E30447FA-D267-4727-B0AD-A28FC6A90A4A@gmail.com Whole thread Raw |
| In response to | how to resolve org.postgresql.util.PSQLException: ResultSet notpositioned properly, perhaps you need to call next. (Karen Goh <karenworld@yahoo.com>) |
| Responses |
Re: how to resolve org.postgresql.util.PSQLException: ResultSet notpositioned properly, perhaps you need to call next.
(Karen Goh <karenworld@yahoo.com>)
|
| List | pgsql-jdbc |
On Sep 2, 2019, at 12:28 AM, Karen Goh <karenworld@yahoo.com> wrote:String[] subjectNames = request.getParameterValues("txtsubjects");
String zipcode = request.getParameter("location");
List<String> subjList = new ArrayList<String>(Arrays.asList(subjectNames));
List<tutor> myList = new ArrayList<>();
ArrayList<String> subList = new ArrayList<>();
CopyOnWriteArrayList<String>newSub = new CopyOnWriteArrayList<String>();
tutor t = new tutor();
String sql1 = "select t.tutor_contact_no, t.zipcode, t.tutor_id, t2.subject_name FROM s_tutor t JOIN tutor_subject t2 ON t.tutor_id = t2.tutor_id where t.zipcode = ? And t2.subject_name = Any((?))";
PreparedStatement ps2 = connection.prepareStatement(sql1);
ps2.setString(1, zipcode);
for (int i = 0; i < subjectNames.length; i++) {
Array array = connection.createArrayOf("text", subjList.toArray());
ps2.setArray(2, array);
}
ResultSet rs = ps2.executeQuery();
while (rs.next()) {
t.setContactNo(rs.getString("tutor_contact_no"));
t.setZipcode(rs.getString("zipcode"));
t.settutor_id(rs.getInt("tutor_id"));
for (String s: subjList)
if (s.contains(rs.getString("subject_name")))
subjList.add(rs.getString("subject_name"));
newSub.addAll(subjList);
}
myList.add(t);
request.setAttribute("tutors", myList);
request.setAttribute("sub", newSub);
Please. I hope someone can tell me what's wrong with the code or how to overcome this error...I have been struggling for 2 weeks already.
Thanks.
}
myList.add(t);
request.setAttribute("tutors", myList);
request.setAttribute("sub", newSub);
I put your code into my editor as if it were an entire method. That formatted version is below.
I find the for loop very odd. See comments with your code below, in bold for easy reference:
void func() {
// subjectNames set from the request
String[] subjectNames = request.getParameterValues("txtsubjects");
String zipcode = request.getParameter("location”);
// subjList built from subjectNames
List<String> subjList = new ArrayList<String>(Arrays.asList(subjectNames));
List<tutor> myList = new ArrayList<>();
ArrayList<String> subList = new ArrayList<>();
CopyOnWriteArrayList<String>newSub = new CopyOnWriteArrayList<String>();
tutor t = new tutor();
String sql1 = "select t.tutor_contact_no, t.zipcode, t.tutor_id, t2.subject_name FROM s_tutor t JOIN tutor_subject t2 ON t.tutor_id = t2.tutor_id where t.zipcode = ? And t2.subject_name = Any((?))";
PreparedStatement ps2 = connection.prepareStatement(sql1);
ps2.setString(1, zipcode);
for (int i = 0; i < subjectNames.length; i++) {
// subjList add to query
Array array = connection.createArrayOf("text", subjList.toArray());
ps2.setArray(2, array);
}
ResultSet rs = ps2.executeQuery();
while (rs.next()) {
t.setContactNo(rs.getString("tutor_contact_no"));
t.setZipcode(rs.getString("zipcode"));
t.settutor_id(rs.getInt("tutor_id"));
for (String s: subjList)
// test if one entry of subjList matches the current row’s “subject_name” value,
// repeatedly asking for “subject_name” from current rs
if (s.contains(rs.getString("subject_name")))
// if so add current value to list being iterated
subjList.add(rs.getString("subject_name"));
newSub.addAll(subjList);
// If I follow the intent correctly (match subjects to tutor) I believe the following would be more effective
String rsSubject = rs.getString("subject_name");
if (subjList.contains(rsSubject)) {
newSub.add(rsSubject);
}
// However your sql seems to be asking for all tutors in a given zipcode and what subjects they handle. You might want to at least order the return set by tutor to deal with one tutor over several result rows.
Alternatively you could group subjects into an array per tutor:
String sql1 = "select t.tutor_contact_no, t.zipcode, t.tutor_id, array_agg(t2.subject_name) FROM s_tutor t JOIN tutor_subject t2 ON t.tutor_id = t2.tutor_id where t.zipcode = ? And t2.subject_name = Any((?)) group by t.tutor_contact_no,t.zipcode,t.tutor_id”;
and grab the array of tutor’s subjects.
Further your responce doesn’t tie specific tutor to specific subjects. Perhaps you can add List<String>subjects to your “tutor” class?
}
myList.add(t);
request.setAttribute("tutors", myList);
request.setAttribute("sub", newSub);
}
I hope this helps.
pgsql-jdbc by date: