How to set Arrays in prepared Statement and read in more than one checked box values ?

Dave Tolls wrote:This is why I've been trying to get your to think about your database model.
I still don't know what this is modelling.

Anyway,
the general batching, assuming a SQL like:
INSERT INTO subject (name, tutor_id) VALUES(?,?)

Note, that assumes an auto-increment subject_id field.

Anyway, using a PreparedStatement called ps:
for (String subject : subjects) {    ps.setString(1, subject);    ps.setInt(2, tutorId);    ps.addBatch(); } ps.executeBatch();
That assumes you have an array of subjects and an id for the tutor.

Dave Tolls wrote:This is why I've been trying to get your to think about your database model.
I still don't know what this is modelling.

Anyway,
the general batching, assuming a SQL like:
INSERT INTO subject (name, tutor_id) VALUES(?,?)

Note, that assumes an auto-increment subject_id field.

Anyway, using a PreparedStatement called ps:
for (String subject : subjects) {    ps.setString(1, subject);    ps.setInt(2, tutorId);    ps.addBatch(); } ps.executeBatch();
That assumes you have an array of subjects and an id for the tutor.

Dave Tolls wrote:In what way does batching a load of related INSERTs not apply?

About your model.  So you can have multiple entries in your Subject table for, say, "Chemistry"?

Dave Tolls wrote:I can't follow that code easily, since the indentation is a bit out.

Anyway, so you would expect the output in your Subject table to be something like:
subject_id name tutor_id 1 Maths 1 2 Physics 1 3 English 2 4 Maths 2
just so I know what you're aiming at.

As for the SQL,
Have you tried using that SQL directly against your database?
Because it looks like a mix between an INSERT and an UPDATE to me.

Dave Tolls wrote:That's not a very good data structure for the table.
You have multiple 'Maths' subjects.

This is what I've been driving at.
Having multiple Subjects that are the same.

Dave Tolls wrote:If it were me, the first model that comes to mind for a Tutor Subject relationship is:

Tutor > - < Subject

That is Many-to-Many.

This supposes that a single subject is represented by a single row in the Subject table, so only one Maths subject, or one Physics, etc.

So Subject, at a minimum, would have name as the basic start point, which would be the Primary Key.  You might prefer to have an id field as well, but it's not actually necessary if we're ensuring the names are unique.
Tutor would have an id and name, with id as the Primary Key.
Finally the Many-to-Many would be mapped as a table (Tutor_Subject is fine) which would have a tutor_id and a subject_name.  The Primary Key would be both columns.

Dave Tolls wrote:In which case it is not the same model, as my definition of Subject above prevents you creating two Maths Subjects.

Why is the Maths taught by Tutor 1 different to the one taught by Tutor 2?

Dave Tolls wrote:If tutor_id and subject_name make the primary key of that table then yes, that would make more sense to me.

Dave Tolls wrote:You need a table to map a Tutor to multiple Subjects, and also a Subject to multiple Tutors.

That's the Tutor_Subject table above, with a tutor_id and subject_name.

Remember, this is all presupposing your subject checkboxes are driven from a Subject table.

Dave Tolls wrote:Well, I used 'name' since I was guessing you might be sending the subject name (which I would assume was unique).
You could always send a subject id.  That's up to you.

For the IN, the most secure way is to build the list of '?' placeholders and the do a load of setString (or setInt if using ids) on the PreparedStatement.

The initial answer on this SO thread is the usual way to build up the IN, using String.join and Collections.nCopies...it's a lot easier than it used to be!