Skip to main content
F
ffcai
Inspiring
March 4, 2012
Answered

how to insert a Java object into MySQL via ColdFusion

  • March 4, 2012
  • 4 replies
  • 4124 views

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!

    This topic has been closed for replies.
    Correct answer Owainnorth

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

    4 replies

    F
    ffcaiAuthor
    Inspiring
    March 7, 2012

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

    F
    ffcaiAuthor
    Inspiring
    March 7, 2012

    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__-
    Inspiring
    March 7, 2012

    <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.

    F
    ffcaiAuthor
    Inspiring
    March 7, 2012

    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()>


    F
    ffcaiAuthor
    Inspiring
    March 7, 2012

    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!!

    Owainnorth
    OwainnorthCorrect answer
    Inspiring
    March 5, 2012

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

    F
    ffcaiAuthor
    Inspiring
    March 6, 2012

    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>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices