Hello,
On Wed, 2019-09-04 at 07:33 +0000, Karen Goh wrote:
> Here's the entire code again:
>
> 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 : subjList)
>
As I said last time, I'm not an "expert".
subjectNames is declared as an array.
In your "for" loop you will be doing ps2.setArray(2, array); multiple
times depending on the number of subjects contained in subjectNames.
I suspect the query is failing and as there is no try .. catch block to
trap the SQLException, this is not reported back to your code.
If you alter the postgresql.conf to log all SQL statements, restart,
run your test again, look in the log file, it will show the passed
parameter values. You can then verify that that the array passed as the
second parameter is correct.
HTH,
Robert
