how to insert a Java object into MySQL via ColdFusion

[ffcai]

Hi everyone,

  I have searched everywhere on the Internet best I can trying to find the answer. Still no result yet. I did find some code for how to insert a Java object into MySQL using pure Java. It goes like this: first you make a connection with the MySQL database by using Connection object. Then you use PreparedStatement object to prepare the sql statement. Then you call its executeUpdate() method. Done! This is how it is done in Java. But I am trying to see if I can do this directly in ColdFusion. Say I have a Java object myJavaOjbect, which implemens Serializable interface already. I want to insert it into java_objects_t table, which contains a column with BLOB type called my_java_data. Then in ColdFusoin I try to do this:

<cfquery name="insertJavaObject" dataSource="#Application.ds#">

          INSERT INTO java_objects_t (my_java_data) values (#myJavaObject#)

</cfquery>

This query does not work because #myJavaObject# turn out to be a String object, because it automatically calls its toString() method.

Is there a way to insert a Java object into MySQL database table inside ColdFusion page? Or do I need to use pure Java to do this?

Thank you for helping, everyone!

[Owainnorth · Accepted solution]

Use CFQueryParam, as you should be already. Job done. Pow pow pow.

[ffcai]

Thank you. It really worked.  Here is the command I used:

<cfquery name="insertJavaObject" dataSource="#Application.ds#">

          INSERT INTO unqualified_list_t (my_id, unqualified_list) values (

                                <cfqueryPARAM value = "#his.getMyId()#" CFSQLType = 'CF_SQL_BIGINT'>,

                              <cfqueryPARAM value = "#his#" CFSQLType = 'CF_SQL_OTHER'>  )

</cfquery>

his is a Java object. But How do I read back...I am a little frustrated working with ColdFusion. Because Google search does not provide good results for topics related to ColdFusion. I don't know why, I tried like 2hours still can find't out how to read back. I can read back as normal but it is a Byte Array object. What tag can I use to conver it to Java object? The below is the read statement. After this I try to display the BLOB object read, it says "

ByteArray objects cannot be converted to strings."

Then I tried Google search but can't find any threads useful....

Thank you for helping!!

<cfquery name="readJavaObject" dataSource="#Application.ds#">

          select *

          from unqualified_list_t

</cfquery>

[ffcai]

Finally got it to work with this function:

<cffunction name="deSerialize" access="public" returntype="any" hint="deserialize BLOB to Java object">

          <cfargument name="bytesArray" type="binary" required="Yes" hint="the CF object to be serialized">

          <cfset var objByteStream = createObject("java", "java.io.ByteArrayInputStream").init(bytesArray)>

            <cfset var objInputStream = createObject("java", "java.io.ObjectInputStream").init(objByteStream)>

            <cfset var objDeserialized = objInputStream.readObject()>

            <cfreturn objDeserialized>

</cffunction>

I am not kidding...If you know better way, like a tag, to get this job done, please let me know. Much Thanks!!

[ffcai]

I want to add one more question see if anyone knows the answer.

The following method "saveJavaObject" will save the Java object "his" to a database table. In the signature of saveJavaObject method, its cfargument for "theObject" is of type any. But the "his" object passed in that method becomes other type, I think it becomes String type. Anyone knows how to make sure "his" passed to the method saveJavaObject remains "any" type? Thank you!!

<cfinvoke component="aCFCComponent" method="saveJavaObject" returnvariable="result" >

<cfinvokeargument name="theObject" value="his"/>

</cfinvoke>

[-__cfSearching__-]

<cfinvokeargument name="theObject" value="his"/>

You need pound signs around the variable name. Otherwise, you are just passing in the literal characters "his".

<cfqueryPARAM value = "#his#" CFSQLType = 'CF_SQL_OTHER'>

The correct type for MySQL blobs is usually CF_SQL_LONGVARBINARY or CF_SQL_BLOB.

[ffcai]

Good to see you again cfSearching!

Adding # sign really works. I thought I tried # last time...but anyway, thanks.

Also, #his# is a Java object. So using CF_SQL_LONGVARBINARY or CF_SQL_BLOB as the type does not work. The system reports can't convert to '[B' if I use CF_SQL_BLOB, and it reports data invalid error if I use SQL_LONGVARBINARY.

Thanks, finally able to write Java object to MySQL and read it back from MySQL. Great!

I have one more question see if you know the answer.

Below is the block of code I used to read the BLOB back from MySQL then convert it to a Java object. In particular, I use this line "<cfset var bytes = rs.getBytes("unqualified_list")>" to read the BLOB back. I have to use "unqualified_list" String to read it. I can't use an integer index to read it, something like <cfset var bytes = rs.getBytes(1)>. The system will report column '1' not found. Obviously it treats the integer 1 as "1". Do you know a way to work around it?

Thanks!!!

			<cfset var rs = pStatement.executeQuery()>
			<cfif rs.next()>
			<cfset var bytes = rs.getBytes("unqualified_list")>
				<cfset var bis = createObject( "java", "java.io.ByteArrayInputStream").init(bytes)>
				<cfset var ois = createObject( "java", "java.io.ObjectInputStream").init(bis)>
			<cfset object = ois.readObject()>
			</cfif>
			<cfset pStatement.close()>

[-__cfSearching__-]

Good to see you too 🙂

Also, #his# is a Java object. So using CF_SQL_LONGVARBINARY or CF_SQL_BLOB as the type does not work. The system reports can't convert to '[B' if I use CF_SQL_BLOB, and it reports data invalid error if I use SQL_LONGVARBINARY.

Ohh.. I thought #his# contained an array of bytes, which is compatible with longvarbinary and blob. I  have never tried inserting an object with cfqueryparam.

Obviously it treats the integer 1 as "1". Do you know a way to work around it?

ResultSet should accept both a column name or index. You probably just need to add a javacast so it knows which method you want.  ie javacast("int", 1) or javacast("string", "columnName").  But do you need to use java for the query? It seems like you could just query the table and reconstruct the object from the bytes.

Message was edited by: -==cfSearching==-

[ffcai]

This is how I read BLOB from MySQL. I need to read it with rs.getBytes() first. Then convert the bytes array into a java object.

<cfset var rs = pStatement.executeQuery()>

                              <cfif rs.next()>

                                  <cfset var bytes = rs.getBytes("unqualified_list")>

                                        <cfset var bis = createObject( "java", "java.io.ByteArrayInputStream").init(bytes)>

                                        <cfset var ois = createObject( "java", "java.io.ObjectInputStream").init(bis)>

                                  <cfset object = ois.readObject()>

                              </cfif>

<cfset pStatement.close()>

Also, javacast("int", 1) really works. If I really have to type this many characters, why not just use String name as the access index...

[ffcai]

Also, is there a tag to read Java object from BLOB type in MySQL? My readJavaObject() method works. But I have to connect to the database first then read the BLOB, lastly convert the BLOB to a Java object. Too much work I feel. The write procedure is much easier, as you can see, only a few lines of code:

<cfquery name="insertJavaObject" dataSource="#Application.ds#">

          INSERT INTO unqualified_list_t (my_id, unqualified_list) values (

                                <cfqueryPARAM value = "#his.getMyId()#" CFSQLType = 'CF_SQL_BIGINT'>,

                              <cfqueryPARAM value = "#his#" CFSQLType = 'CF_SQL_OTHER'>  )

</cfquery>

Is there something similar to this write procedure above to read BLOB from MySQL and convert to Java object in one or a few tags?

[-__cfSearching__-]

Apparently there is ObjectLoad() and ObjectSave() in CF9. Learn something new every day...

http://www.cfinsider.com/index.cfm/2010/5/4/Serializing--Deserializing-in-ColdFusion-9

[ffcai]

what....I have spent this much time doing something that exist in CF 9 already...

[-__cfSearching__-]

Well with all the new functions, I overlooked it too

[ffcai]

Sorry for the background color above. When I copied code CF_SQL_LONGVARBINARY or CF_SQL_BLOB from your reply, the background changed.