• 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: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear expert,

I have this String variable which has to be changed to Array.

String[]subject, but I am stuck as to how to setString

I tried the following but the prepared Statement part, I am just clueless

   



Hope someone can guide me how to.

Tks.
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This itself returns an array of String.


So you need to do something like this



 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 37469
539
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, take a look at running a batch of prepared statements since it looks like you want to run the same statement with different parameters. For example, this
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:This itself returns an array of String.


So you need to do something like this





Hi Swastik,

I have changed the object type to using ArrayList cos the thing is that the subject varies between different level and ArrayList is better as in there is no fixed length.  Hope that I am right.

But, then I am stuck cos I tried to use ArrayList<String> subject = request.getParameterValues("subject") but it doesn't work.

So, how do I go about doing it ?

Furthermore, I have problem in the setter also.

Here's my attempt so far :


My controller and my tutorDAOImpl :



another attempt of my tutorImpl but not working



 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You don't need any ArrayList here. 

The following code snippet is fair enough to retrieved the check box elements from html page. 


As an example if 2 check boxes are checked, size will be 2, if 4 check boxes are checked, size will be 4.  The ArrayList thing you are doing is a wrong way.  getParameterValues always returns a String array.
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you provide us some more input here?  Like what is table structure.  How are saving the subjects into table etc.  It might help us to dig into it little more.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:You don't need any ArrayList here. 

The following code snippet is fair enough to retrieved the check box elements from html page. 


As an example if 2 check boxes are checked, size will be 2, if 4 check boxes are checked, size will be 4.  The ArrayList thing you are doing is a wrong way.  getParameterValues always returns a String array.


OK.  But, what do I do on this part ?

Cos you are using String subject[],  and I am not sure how to code on the preparedStatement part


I got this error :

The method setString(int, String) in the type PreparedStatement is not applicable for the arguments (int, String[])
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
m.getSubject() is returning a String array, where as a String is expected here.  It would be nice if you could provide some more input here.  I believe m is referring to some data model class object.  How you are assigning values this class.  What is the table structure etc.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:Can you provide us some more input here?  Like what is table structure.  How are saving the subjects into table etc.  It might help us to dig into it little more.


Hi Swastik,

To answer your question, basically I am starting off with a column where it will stores all the subjects (in my html form, I have many check boxes and the user can tick as many as she likes)

Not sure if this is a good design ?

Hope to have your opinion too.

So, if later I'm going to search functionality, if this tutor has say Chemistry and Biology in her portfolio, her name and other people names that has ticked Chemistry and Biology will appear in the Search List.

 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fair enough.  But it's still not clear what is your database table design. How many fields does it have.  Are you saving the subjects row wise, or in a single column using some delimiters.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:Fair enough.  But it's still not clear what is your database table design. How many fields does it have.  Are you saving the subjects row wise, or in a single column using some delimiters.


Hi Swastik,

The thing is that my question is how to set the String[]subject  in Prepared Statement ?

So, in my column subject, if a user ticked 3 subjects, all the 3 subjects name are going into that column.

How do you setString for the preparedStatement in this case since you mentioned on the above String[] subject = request.getParameterValues("subject") will do the job for the controller..

What do I have to do in order for the preparedStatement to work in this case that will match your above method ?

   ps.setString(14, m.getSubject());
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You mean all selected subjects will be saved in a single column in the database table?
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 66306
152
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How are you expecting to store 3 values in a place that holds only one? It sounds like your database design is flawed.

Please describe the data that you are trying to model.
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's what I have been since a long time.
 
Paul Clapham
Sheriff
Posts: 22828
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tangara goh wrote:So, if later I'm going to search functionality, if this tutor has say Chemistry and Biology in her portfolio, her name and other people names that has ticked Chemistry and Biology will appear in the Search List.


you should stop asking about the code you want to write. Several people have asked you about your database design and instead of answering, you just ask about writing code again. Nobody can help you that way.

So let me ask this: if the "subject" parameter returns you an array containing "Chemistry" and "Biology", should this create two database records, one containing "Chemistry" and the second containing "Biology"? Or did you choose the un-normalized design where you have several columns in one table for subjects? It's impossible for anybody to discuss code to update your database without knowing at least that.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
tangara goh wrote:So, if later I'm going to search functionality, if this tutor has say Chemistry and Biology in her portfolio, her name and other people names that has ticked Chemistry and Biology will appear in the Search List.


You should stop asking about the code you want to write. Several people have asked you about your database design and instead of answering, you just ask about writing code again. Nobody can help you that way.

So let me ask this: if the "subject" parameter returns you an array containing "Chemistry" and "Biology", should this create two database records, one containing "Chemistry" and the second containing "Biology"? Or did you choose the un-normalized design where you have several columns in one table for subjects? It's impossible for anybody to discuss code to update your database without knowing at least that.


I already answered.  Please read it.  I already said that the subjects which the servlet read in through the ParameterValues will all appear in the column name : subject in my database.

Isn't that answered ???  If not, can you be more specific as to what kind of answers are expected.  I don't have problem in my English.  This is the first time I heard that my English is not understandable !!!
 
Paul Clapham
Sheriff
Posts: 22828
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I saw that but I was astonished that you planned to put more than one subject in a column named "subject". That's why I wasn't convinced I could understand you.

So did I understand that correctly? In a normalized database design, if a tutor had three subjects then you would put those in three separate database rows, so that you could readily find tutors for a particular subject. But you're really planning to put all of the subjects in one field in one row?

Okay. If so, how do you plan to get them all in? You need to decide that before you write code and I'm pretty sure you haven't mentioned what your plan is.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:I saw that but I was astonished that you planned to put more than one subject in a column named "subject". That's why I wasn't convinced I could understand you.

So did I understand that correctly? In a normalized database design, if a tutor had three subjects then you would put those in three separate database rows, so that you could readily find tutors for a particular subject. But you're really planning to put all of the subjects in one field in one row?

Okay. If so, how do you plan to get them all in? You need to decide that before you write code and I'm pretty sure you haven't mentioned what your plan is.


Please find attached, you can see all the subject in the same column.

Is this not a good design?

If not, how should the design be done so that it will facilitate search functionality which I plan to work on it later.

It could be done last time but now I can't remember how I did it.

So,  how do you set the Array in Prepared Statement ?
screen-shot-on-database.jpg
[Thumbnail for screen-shot-on-database.jpg]
 
Paul Clapham
Sheriff
Posts: 22828
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tangara goh wrote:Please find attached, you can see all the subject in the same column.


I see some kind of output from a database query. I don't see a database design. I can't tell what your tables are or what their fields are. So it's not really useful. You should show us your database design first.

So,  how do you set the Array in Prepared Statement ?


As I have said before, you can't start programming until you understand how the database is structured.
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have Subjects, Tutors and Students.
To me that's 3 tables...possibly 2 if Tutors and Students are close in content, but I'd start with 3.

Tutors and Students both have Many-to-many relationship with Subjects, so there'll be a mapping table for each.

Trying to squeeze a Many-to-Many into a single database column as you are is not good database design.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:You have Subjects, Tutors and Students.
To me that's 3 tables...possibly 2 if Tutors and Students are close in content, but I'd start with 3.

Tutors and Students both have Many-to-many relationship with Subjects, so there'll be a mapping table for each.

Trying to squeeze a Many-to-Many into a single database column as you are is not good database design.


for my case, I am starting off with one-to-many relationship for a start.

So, it is one tutor to many subjects.

Erm...do you know how to do the prepared Statement for this case then ?

And I think just get the ParameterValues alone is not going to work.

It seems like nobody has the answer and I am kind of surprise because this should be considered still as a beginner topic .........
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, so no mapping tables, but each Subject table has a foreign key to the Tutor table.

Subject: id,  name (varchar), tutor_id (int)

Tutor: id, name, etc etc

is that the table structure you have?
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tangara goh wrote:

It seems like nobody has the answer and I am kind of surprise because this should be considered still as a beginner topic .........


We all can provide you answers, provided we get the proper input from your end.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:OK, so no mapping tables, but each Subject table has a foreign key to the Tutor table.

Subject: id,  name (varchar), tutor_id (int)

Tutor: id, name, etc etc

is that the table structure you have?


Hi Dave,

First, thanks for your patient.  Now, I can see what you are driving at.

Actually, there is only one table - tutor.

And the subject is a column name in the table name tutor.  There is a id in the table tutor but I am stuck at the column name subject because it will have different subject names inside this column when user selected the checked boxes.

Hope that the above is clear.

Basically, it is just a single database with a table tutor.  That's all.
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So , the table design itself seems to have problems.  How are you storing multiple subjects in single column?  As Dave suggested you need to normalize the table design.
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So that's your problem.
You need two tables.

Well, you don't, but it won't work terribly well with just the one.
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even if it works, may be subjects are delimited with comma or something else, but of course not a correct design.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:Even if it works, may be subjects are delimited with comma or something else, but of course not a correct design.


Hi both,

So, you guys think that I should pull out the subjects and then have FK tutorID which is my primary key in tutor table?

In that case, I will have to create a separate insert query in the same DAO, DAOIMpl and controller am I right ?

So, how do I do the insert for the table that has the subjects and tutorID only ?

Do I put multiple insert statements in the controller before update them into the 2 table or I use join or what ?

I wonder if there is a tutorial that talk about this kind of scenario ?

 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assuming that your tutor details already exist in tutor table, here you need only one insert statement that stores record in subjects table.
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your checkboxes can be driven by the data in the Subject table, whether as ids or the names used in the table.

Either way, you'll have an array of values that you can then:
UPDATE Subject
SET tutorId = ?
WHERE name IN (....comma seperated list of values)
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Should we not use insert or update here?
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Your checkboxes can be driven by the data in the Subject table, whether as ids or the names used in the table.

Either way, you'll have an array of values that you can then:
UPDATE Subject
SET tutorId = ?
WHERE name IN (....comma seperated list of values)


Hi Dave,

Pardon me but I am not quite sure when you said WHERE name IN (values)

Is the name referring to the checkbox name in my jsp or the subject in my separate table for storing subjects ?

and the values are they to be replaced by ? ? ?
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!


Hi Dave,

I have now added in subject as one of my models, added in subject DAO and subject DAOImpl.
However, I am still stuck at the preparedStatement part. 
Basically, I am trying to use the same tutor Controller to pass in two inserts including the newly created In as follows :


My subject model :


So, I am back to the same old question how do I set the List in my case is generic type subject in my prepared Statement ?

I had tried the below but I am still stuck at the part I had used my Manager to pass in the insert method, but I need to set it first and I really don't know how to do this part.  Kindly help.



Here is where I am stuck
 
Swastik Dey
Rancher
Posts: 1815
15
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


The above statement is completely wrong.  What are you trying to do?  Create a new record or update existing records?



The above statement is wrong as well.  I believe by m1 you are referring to subject class.  The setSubject method is expecting a List of type Subject but you are passing a String array.  So you need to convert this String array to a list of type subject before you pass it to the setSubject method.

Last but not the least, naming convention.  Ideally class names in java start with upper case characters.
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does the Subject table have a foreign key mapped to the tutor table?
That's the value you need to be SETting in your UPDATE (not INSERT) SQL.

And, you need to read that answer on StackOverflow that I posted to see how to handle the IN clause.
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:

The above statement is completely wrong.  What are you trying to do?  Create a new record or update existing records?



The above statement is wrong as well.  I believe by m1 you are referring to subject class.  The setSubject method is expecting a List of type Subject but you are passing a String array.  So you need to convert this String array to a list of type subject before you pass it to the setSubject method.

Last but not the least, naming convention.  Ideally class names in java start with upper case characters.


Hi Swastik,

I have now managed to fix my Eclipse watever problem and can come back and focus on this now.

Basically, after studying the url forwarded by Dave, I am now rather confused cos why do I need a IN clause in order to insert the subject into the subject table ?

Can I not create a separate insert query and then insert it together with the other insert query for my tutor table ?

Isn't it more clean and straight forward ?

Anyway, here's what I have edited on my code but it is not working out :







Hope you could advise me what is wrong.

Tks.

 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think there's a misunderstanding here.

What does your SUBJECT table look like?

My thinking was that there would be a fixed list of subjects in the table.
You would then assign a tutor to each subject based on what has been selected by the check boxes.
But it sounds like there are no subjects in the table, and they are only added when a tutor is set up?
 
tangara goh
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:I think there's a misunderstanding here.

What does your SUBJECT table look like?

My thinking was that there would be a fixed list of subjects in the table.
You would then assign a tutor to each subject based on what has been selected by the check boxes.
But it sounds like there are no subjects in the table, and they are only added when a tutor is set up?


Hi Dave,

Tks for coming back to my post

Actually, the subject table is only subject_id and subject with reference to tutor_id.

There is this form where there is a section whereby the user can tick as many checkboxes as they like with the id and tag name="subject" and the parameterValues will consist of the subject or rather list of subject that was selected, and the subjects like USEnglish, USMaths will alll go into the table subject, which you guys suggested separated out from the other table tutor which will contain other information like the name, address, age etc etc.

Now, can I just create 2 separate insert sql statement and use a addbatch() to insert them into the two tables separately ?

However, I am not sure the how-to and hope there are tutorials that you can point me to.

Tks.
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!