Win a copy of Mastering Non-Functional Requirements this week in the Design forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

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

 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


Note, that assumes an auto-increment subject_id field.

Anyway, using a PreparedStatement called ps:

That assumes you have an array of subjects and an id for the tutor.


Hi Dave,

Can I know in this case, there is no need for me to implement the DAO and DAOImpl already right ?

I have also been trying to study other others are doing and it seems that they can just do it all together in the controller, without the trouble of creating interfaces and then write out all the methods before pulling out the methods blah blah blah...

And it seems that having DAO and DAOImpl in my case just makes the code more difficult to write, especially the controller part when you create a new instance of the manager class where the insertMethod() is being called.

Hope to have your comments on this ...
 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can do all your code in one method.
That does not mean it is a good idea.

I would create a DAO etc, and this would be an assignSubjectsToTutor method, with a String[] and int as parameters (subjects and tutor id).
That way you can test it in isolation.
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


Note, that assumes an auto-increment subject_id field.

Anyway, using a PreparedStatement called ps:

That assumes you have an array of subjects and an id for the tutor.


Hi Dave,

Here's the ER Diagram.

Could you advice how I can write my insert statement and how to handle the insertion of list of subjects selected by users into the table ?

Furthermore, I have used List<subject>subjects in my model subject.

I have further googled and found that addBatch() does not seem to apply in my design.

I am having problem in the controller where I want to insert both the tutor table and the subjects table data together.  How should I go about achieving that ?
ER-Diagram.png
[Thumbnail for ER-Diagram.png]
 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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"?
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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"?


Yes.  I can have more than one subject entered into the Subject table.  So, the person can choose Chemistry, Physics etc etc and it will all entered into the table.

Here's how I write my sql and controller but it doesn't seem to work....I am thinking of using JPA annotation which will be easier..what do you think ?







There is no error after I submitted the form but the data is not inserted into the database.

What has gone wrong ?
 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

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.
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

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.


Hi Dave,

Sorry I only see this message now while looking for a solution in my latest 404 problem in this site.

Bingo.  You are right.

So, you are saying I tried out using the INSERT and follows by Update for the above table to appear like that in my WebApp/database ?
 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Hi Dave,

First, I would like to thank you for being so patient to me and teaching me something I guess easy for other people but really I am completely lost. 

However, based on your latest reply, I still have no idea how should my bridging table should be like in my case.

Could you kindly let me know how the data structure is ?
 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


But, that is what I have been saying all along that is my Model and why did you say it is wrong ?

Subject table
---------------
Sub_ Id         Sub_Name

1                  Maths
2                  Physics
3                  this could be Maths again because 1 and 2 is entered into this Subject tutor because the tutor has chosen Maths and Physics so the 3rd one which is Maths again is by the 2nd tutor, not the first

Isn't that is what my Subject table suppose to be ?

Hope to have your clarification and confirmation that my Subject table design is correct.



 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?


I think I am roughly getting what you are/were saying now :

Kindly confirm.

You want me to put all the Subject Names  like Maths, Physcis, Chemistry(all unique) like that into a table is that right ?

In that case, my third table teaches subject kindly confirm is it like this ?

tutor Id         Subject Name
---------        -----------------
1                 Math
1                 Physics
2                 Math (So this will be same Math but different tutor Id)
2                 Chemistry

Kindly let me know if the data structure is correct.

Tks.
 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If tutor_id and subject_name make the primary key of that table then yes, that would make more sense to me.
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


One more thing, then do I need a separate table to contain the subjects that the tutor have ticked in the checkbox ?

Or I just include the subjectName(per the ticked in the checkbox) that the tutor will teach all together in the tutor table ?

The subject table will just contain the subjects as per the check boxes for maintenance purpose, since this you said there should be a table like this.

 
Dave Tolls
Ranch Hand
Posts: 2652
29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
tangara goh
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Hi Dave,

I roughly get your point but what about the implementation ?

After I get the data of tutor information and the subjects they would like to teach to go into table 1.

I'll then have to implement method to get the infor from the table 1 and insert it into table 3 with the tutor_id and subject_name is that right ?
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!