?
Solved

Store an ArrayList in MySql as a Blob

Posted on 2004-11-12
45
Medium Priority
?
1,633 Views
Last Modified: 2010-08-05
I want to store an ArrayList intact in a db field - a Blob - How do I cast an ArrayList to a Blob so I can use the PreparedStatement setBlob. If there is a better way I'm open to that too.
0
Comment
Question by:mmouer
  • 15
  • 14
  • 11
  • +2
43 Comments
 
LVL 92

Expert Comment

by:objects
ID: 12570928
you need to serialize the list using ObjectOutputStream
0
 
LVL 92

Expert Comment

by:objects
ID: 12570952
ByteArrayOuputStream bout = new ByteArrayOutputStream();
ObjectOutputStream oout = new ObjectOutputStream(bout);
oout.writeObject(list);
oout.close();
InputStream in = new ByteArrayInputStream(bout.toByteArray());
// save your stream to blob field
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 1000 total points
ID: 12571122
You need to use a PreparedStatement with a binary column:

ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream out = new ObjectOutputStream(baos);
out.writeObject(yourArrayList);
out.close();
PreparedStatement ps = conn.prepareStatement("INSERT INTO sometable (someblob) VALUES (?)");
byte[] bytes = baos.toByteArray();
ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
ps.setBinaryStream(1, bais, bytes.length);
0
 
LVL 92

Expert Comment

by:objects
ID: 12571142
> You need to use a PreparedStatement with a binary column:

Thats already been posted.
0
 

Author Comment

by:mmouer
ID: 12571210
Thanks to all
0
 
LVL 92

Expert Comment

by:objects
ID: 12571222
Can you please explain why you accepted that answer, it merely duplicates what was already stated.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12571227
>>it merely duplicates what was already stated

Where?
0
 
LVL 92

Expert Comment

by:objects
ID: 12571233
I'm not going to argue with you about it, I'll get a mod to sort it out.
0
 
LVL 92

Expert Comment

by:objects
ID: 12571239
mmouer,

Can we get some feedback from you please
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12571257
Seems pretty clear to me:

a. petmagdy's is a good example, but unfortunately of doing something different
b. your example is OK, but leaves too much out

0
 
LVL 92

Expert Comment

by:objects
ID: 12571274
No ones asking you :)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12573455
8-)
0
 
LVL 13

Expert Comment

by:petmagdy
ID: 12573482
sorry just a little Q CEHJ.

what is 8-) ? a laught or showing teath?

 ;-)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12573505
LOL - a thank you!
0
 

Author Comment

by:mmouer
ID: 12584581
Using the answer given by Objects an error was generated.
The answer given by CEHJ worked.
That's the only reason I awarded the points the way I did.

Thanks,

Michael
0
 
LVL 92

Expert Comment

by:objects
ID: 12588563
> Using the answer given by Objects an error was generated.

Then you should post the error so I could help you fix it, otherwise it is hard for me to help you.
The code from CEHJ uses the same code as I posted anyways.
0
 

Author Comment

by:mmouer
ID: 12588985
No it's not the same code. You use InputStream. CEHJ used ByteArrayInputStream.
I didn't post the error because CEHJ had already posted his response.
Anyway, I apologize for creating a brouhaha and I appreciate all who commeted.
It's still not working for while CEHJ's solution compiled I get a MySQL syntax error.
0
 
LVL 92

Expert Comment

by:objects
ID: 12589002
> You use InputStream. CEHJ used ByteArrayInputStream.

No I use a ByteArrayInputStream. The type of the var is irrelevant (InputStream is a superclass of ByteArrayInputStream)

InputStream in = new ByteArrayInputStream(bout.toByteArray());

0
 
LVL 92

Expert Comment

by:objects
ID: 12589010
> It's still not working for while CEHJ's solution compiled

code i posted also compiled :)
0
 

Author Comment

by:mmouer
ID: 12589028
Be that as it may your code generated the following and wouldn't compile:
C:\JavaProjects\ColorDifferences\src\com\sherwin\color\colordifferences\IlluminateObserverMySQL.java:88: setBlob(int,java.sql.Blob) in java.sql.PreparedStatement cannot be applied to (java.io.InputStream)
The reason I' m asking an expert is because I'm new at Java - so cut me some slack - I Just  need help not a cat fight.

0
 
LVL 92

Expert Comment

by:objects
ID: 12589059
the code I posted didn't have a call to setBlob() nor did I suggest using it. I didn't post how to use the stream because petmagdy already had so didn't want to repeat his/her comment.
CEHJ just repeated what had already been posted.

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12589322
>>It's still not working for while CEHJ's solution compiled I get a MySQL syntax error.

Let me know and i'll see if i can help
0
 

Author Comment

by:mmouer
ID: 12594685
Venabili,

Yes, please.

objects,

Sometimes it's better to have the whole thing explained in one note otherwise a newbie may not be able to put it together.
Once again, I apologize for stepping on your toes.

CEHJ,

My code - assume all variables and objects are defined:
class to make a stream:
 public boolean buildStream ()
    {
        try
        {
            oout = new ObjectOutputStream (baos);
            oout.writeObject (inList);
            oout.close ();
            bytes = baos.toByteArray ();
            bais = new ByteArrayInputStream (bytes);
        }

main class:
public void setSQL ()
    {
        try
        {
            ByteArrayInputStreamBuilder blobBuilder = new ByteArrayInputStreamBuilder ();
           
            sqlInsertNew.setString (1, Illuminant);
            sqlInsertNew.setInt (2, Observer);
           
            blobBuilder.setInList (xBarList);
            if (blobBuilder.buildStream () == false)
            {
                System.out.println ("Error returned by ByteArrayInputStreamBuilder.buildStream");
            }
            sqlInsertNew.setBinaryStream (3, blobBuilder.getBais (), blobBuilder.getBytesLength ());
           
            blobBuilder.setInList (yBarList);
            if (blobBuilder.buildStream () == false)
            {
                System.out.println ("Error returned by ByteArrayInputStreamBuilder.buildStream");
            }
            sqlInsertNew.setBinaryStream (4, blobBuilder.getBais (), blobBuilder.getBytesLength ());
           
            blobBuilder.setInList (zBarList);
            if (blobBuilder.buildStream () == false)
            {
                System.out.println ("Error returned by ByteArrayInputStreamBuilder.buildStream");
            }
            sqlInsertNew.setBinaryStream (5, blobBuilder.getBais (), blobBuilder.getBytesLength ());
           
            sqlInsertNew.setDouble (6, WhitePointX);
            sqlInsertNew.setDouble (7, WhitePointY);
            sqlInsertNew.setDouble (8, WhitePointZ);
        }
Error:
"You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''A  ',10,'¬í\0sr\0java.util.ArrayListx?Ò™Ça?\0I\0sizexp\0"

SQL:
Insert Into Ill_Obs_31_Point('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945

Thanks for your help,

Michael
0
 

Author Comment

by:mmouer
ID: 12595883
I didn't describe the MySQL Data Definition.

Illuminant char(3)
Observer int(11)
xBarList blob
yBarList blob
zBarList blob
WhitePoint1 double
WhitePoint2 double
WhitePoint3 double
0
 
LVL 20

Expert Comment

by:Venabili
ID: 12595953
Question reopened.

Venabili
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12596279
Maybe this time objects can post code that *does* work ;-)

0
 

Author Comment

by:mmouer
ID: 12596369
The latest was directed to you - you haven't given up have you? :-)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12596420
>>Insert Into Ill_Obs_31_Point('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945


Please post the full sql
0
 

Author Comment

by:mmouer
ID: 12596488
I don't understand the question - that is the full sql generated by the PreparedStatement - The code listing above shows how I build the PrrparedStatement.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12596550
There's no close bracket then
0
 

Author Comment

by:mmouer
ID: 12596717
Insert Into Ill_Obs_31_Point('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12596785
Have you tried the columns defined as binary?
0
 

Author Comment

by:mmouer
ID: 12597243
Isn't Blob binary?
0
 
LVL 92

Expert Comment

by:objects
ID: 12598610
> Sometimes it's better to have the whole thing explained in one note otherwise a newbie may not be able to put it together.

yes but that does not mean they deserve the points as explained by Venabili

> Once again, I apologize for stepping on your toes.

Thats ok, apology accepted :)

CEHJ > Maybe this time objects can post code that *does* work ;-)

already did and you copied it :-D
0
 
LVL 92

Accepted Solution

by:
objects earned 1000 total points
ID: 12598622
try changing your insert staement to include the values keyword:

Insert Into Ill_Obs_31_Point values ('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945)
0
 
LVL 92

Expert Comment

by:objects
ID: 12598655
also adviseable to explicitly include the column names

Insert Into Ill_Obs_31_Point (Illuminant, Observer, xBarList, yBarList, zBarList, WhitePoint1, WhitePoint2, WhitePoint3) values (?, ?, ?, ?, ?, ?, ?, ?)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12598694
>>already did

That's what mmouer told you - maybe you don't believe him?

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12598703
>>also adviseable

Not 'adviseable' but essential if you're including the values keyword
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12598716
LOL I see my answer is now 'assisted' and the 'accepted' one is actually wrong ;-)
0
 

Author Comment

by:mmouer
ID: 12598736
OK. I split the points - I don't know how I missed the Values - I've only been doing SQL for longer than I care to remember.

CEHJ - You got the code right - thanks!

objects - You found the oversight - thanks.

Venabili

Please close - thanks

Michael
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12598766
>>I don't know how I missed the Values

Using VALUES (even when it *is* used correctly) won't make any difference, unless the full table definition differs from the one given under here

>>I didn't describe the MySQL Data Definition.


0
 
LVL 92

Expert Comment

by:objects
ID: 12599103
Thanks mmouer, let me know if you have any questions in the future :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
In this post we will learn different types of Android Layout and some basics of an Android App.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question