wood burning stoves*
The moose likes JDBC and the fly likes can you put multiple words in a database by using one statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "can you put multiple words in a database by using one statement" Watch "can you put multiple words in a database by using one statement" New topic
Author

can you put multiple words in a database by using one statement

ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
hello everyone
just a quick question.
my string holds the words hello, how, are, you
I want all those values to go into one column name (FIRSTNAMES).
now can i insert them into a sql database by using one preparedstatement?
thanks for your time
ben
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
Sure,
Assuming you have a Connection named con:

This is also assuming the field will allow that many characters. If not, you'll get an SQLException.
[ January 09, 2003: Message edited by: Eric Fletcher ]

My theory of evolution is that Darwin was adopted. - Steven Wright
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
hello thanks for the reply
but i want the words to into the same column name but diffrent fields. is this possible?
ben
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
Originally posted by ben riches:

but i want the words to into the same column name but diffrent fields. is this possible?

hmmm, I'm not sure what you mean, a column IS a field in a DB. It sounds to me like you want to be able to store them in the same place but be able to seperate them if you need to? The best way to do that would probably be to store it in one field with a delimiter and parse it when you select it from the database. The other recourse you have would be to parse it first and store it in different fields in the DB, like FIRSTNAME1, FIRSTNAME2, etc. , but there is no way I know of to have duplicate fields names in any DB.
Sorry if I haven't answered your question, I'm still not 100% sure what you're asking.
E
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
Hello eric cheers for you help
But i will try and explain a bit more agian.
lets say i have a column name called FirstName,
then i want to put 4 values in that column but in diffrent rows but the 4 values are strored in a string.
is this possible?
I hope you understand a bit more.
thanks
ben
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
OK! I got it now!
Yes that is possible, in fact that is one of the primary intended uses of PreparedStatement, to optimize performance of repeated updates. In your case, you'll have to tokenize the String and insert the values into the DB, you could do it like so:

That should insert four new rows in the DB with the different words in the string as the respective values for FIRSTNAMES.
HTH,
E
[ January 10, 2003: Message edited by: Eric Fletcher ]
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
Hello eric thanks for your replys
that last solution worked brilliantly
but how would i put two column names in (FIRSTNAME, LASTNAME)VALUES(?, ?);
and set there values up the same way as before?
thanks for your time eric
ben
[ January 10, 2003: Message edited by: ben riches ]
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188

Then just use the stmt.setXXX(number, value) syntax for each question mark in the values section, i.e.:

etc, etc, as you go down the list of needed fields.
Check out the javadoc for PreparedStatement, it will likely answer a lot of questions for you.
PreparedStatement
HTH,
E
[ January 10, 2003: Message edited by: Eric Fletcher ]
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
Hello again
I am having problems with how to use the setXXX method
And also i want LASTNAMES to have 4 diffrent values from FIRSTNAMES is this possible?
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
Originally posted by ben riches:
Hello again
I am having problems with how to use the setXXX method

The method itself is not "setXXX", there are several different setter methods for PreparedStatement, so I was just using "setXXX" as generic notation. For different datatypes, substitute the datatype for the "XXX", i.e.
stmt.setString(1, "String");
or
stmt.setInt(1, 4);
In your case you only need to worry about String.
Sorry to confuse you, be sure to look at the javadoc on the link I posted before, it lists all the methods for PreparedStatement.
Originally posted by ben riches:

And also i want LASTNAMES to have 4 diffrent values from FIRSTNAMES is this possible?

I'm not sure what you mean...
You can change the query to add another value, then just set the value as we did before, one value will be for the firstname, the second value will be for the second name. If you have them in seperate comma-delimited strings, you will probably have to parse them into seperate collections(i.e. ArrayLists), then loop through the collections setting the values and doing the inserts, just like we did when we tokenized the string before. Is that what you mean?
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
yes eric that is what i wanted to know
thanks for yr time
ben
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
No problem.
Good luck.
E
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
hello again eric right here i go
i have a string with 30 values inside it is it possible to get the first 10 values and put them in a preparedstatement then get the next 10 values and then the next 10?
for example
INSERT INTO table_name (FIRSTNAME, MIDDLE NAME, LASTNAME) VALUES (first 10 values, second 10 values, third 10 values)
thanks again
ben
[ January 13, 2003: Message edited by: ben riches ]
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
You have to set the values for each update individually, so what you suggest wouldn't work. Most likely you would have to parse out the values into seperate ArrayLists, then do something like:

This is also assuming that the ArrayLists are the same size and filled respectively, i.e. the firstname at index 0 is matched with the middle and last names at index 0 in the other Lists.
I will say the way the data is set up makes your life tougher when it comes to mapping it to a DB record. Is there a reason you set up the data this way, or is this simply a learning exercise?
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
hello eric
yes there is a reason i have to set it up like this
it is because i have to get every first name out of a csv file and then every lastname. I have done everything else with my code, It was just the prepared statement i was having problems with.
thanks eric
p.s
if one of the arraylists has a empty string in it would it enter a blank space to the database?
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
Originally posted by ben riches:
if one of the arraylists has a empty string in it would it enter a blank space to the database?

Depends on which DBMS you are using. I know in Oracle an empty string is treated as a null(which is a gross violation of the SQL standard, but I digress). I'm most familiar with Oracle, so I'm not sure(and would actually be surprised) if that is the case for other DBMSs.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: can you put multiple words in a database by using one statement
 
Similar Threads
Ordering Search Results
question about entity bean
Hibernate challenge:
Obtain fresh records from database for next and previous buttons
Authentication/Authorization