Re: java.util.UUID to postgres uuid column

Sun, 28 Feb 2010 01:50:18 -0800 

Hello All,

Any idea on this?

Regards
Vikram


Vikram Subbarao wrote:
The uuid in database is stored in a column of type uuid supported by postgres db. Now when i use the query below, i am passing a string and would become VARCHAR to database which results in postgres db complaining that it cannot compare 'Character Varying' and 'uuid' values. I understand implicit casts happen in certain databases (like sql server), but not in postgres.
Also, there are performance issues shuttling between UUID (128 bits) and String (36 Chars for type 4 uuid = 576 bits) and then back for a frequent query like this which seems mostly un-necessary if ibatis could understand and add this as a known type, especially since postgress jdbc supports java.util.UUID as a type. 

Regards
Vikram

meindert wrote:


Hi Vikram,
I don't use the UUID myself and can't answer your question from experience, but I think the real question would be how the ID is stored in the DB.
IBATIS error message is is correct to say that the UUID object does not have a value (like a string or int object) and there is no UUID.getId()
Assuming you use a 'uniqueidentifier' type on the (sql server) db side, I would think you need to query the ID as a string;
<select id="findById" parameterClass="string" resultMap="fullResult" cacheModel="cache"> 
        SELECT users.*
        FROM users
        WHERE id = #value#
</select> 
sqlMapClientTemplate.queryForObject("User.findById", id.toString());


From sql books online:
A column or local variable of *uniqueidentifier* data type can be initialized to a value in the following ways: 

    * By using the NEWID function.
    * By converting from a string constant in the
      form/xxxxxxxx/-/xxxx/-/xxxx/-/xxxx/-/xxxxxxxxxxxx/, in which
      each /x/ is a hexadecimal digit in the range 0-9 or a-f. For
      example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a
      valid *uniqueidentifier* value.
Comparison operators can be used with *uniqueidentifier* values. However, ordering is not implemented by comparing the bit patterns of the two values. The only operations that can be performed against a *uniqueidentifier* value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the *uniqueidentifier* data type.
Merge replication and transactional replication with updating subscriptions use*uniqueidentifier* columns to guarantee that rows are uniquely identified across multiple copies of the table. 


Regards

 Meindert Hoving


*From:* Vikram Subbarao [mailto:vikra...@directi.com]
*Sent:* 11 February 2010 10:13 AM
*To:* user-java@ibatis.apache.org
*Subject:* Re: java.util.UUID to postgres uuid column


Does not work -
com.ibatis.common.beans.ProbeException: There is no READABLE property named 'value' in class 'java.util.UUID' 

Niels Beekman wrote:
Try #value#, this will use your parameter object directly. Using #id# tries to obtain the id property from it. 


------------------------------------------------------------------------

*From:* Vikram Subbarao [mailto:vikra...@directi.com]
*Sent:* Thursday, February 11, 2010 7:50 AM
*To:* user-java@ibatis.apache.org <mailto:user-java@ibatis.apache.org>
*Subject:* Re: java.util.UUID to postgres uuid column


With this approach i tried this in a query -
<select id="findById" parameterClass="java.util.UUID" resultMap="fullResult" cacheModel="cache"> 
        SELECT users.*
        FROM users
        WHERE id = #id#
</select> 

When i call this query as -

    sqlMapClientTemplate.queryForObject("User.findById", id);

/    Note: id is an java.util.UUID object./

I get an error -
com.ibatis.common.beans.ProbeException: There is no READABLE property named 'id' in class 'java.util.UUID' 

Regards
Vikram

Larry Meadors wrote:

This might work:
public class UUIDTypeHandler implements TypeHandlerCallback { 
    @Override
    public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException {
        setter.setObject(parameter);
    }
@Override 
    public Object getResult(ResultGetter getter) throws SQLException {
        return getter.getObject();
    }
@Override 
    public Object valueOf(String s) {
        return UUID.fromString(s);
    }
}
Add this in your sqlmapconfig.xml: <typeAlias alias="UUID" type="java.util.UUID" /> 
<typeHandler javaType="UUID" callback="UUIDTypeHandler"/>
Larry On Tue, Feb 9, 2010 at 3:35 AM, Vikram Subbarao <vikra...@directi.com> <mailto:vikra...@directi.com> wrote: 
    I am using postgres db and would like to map a java.util.UUID to a postgres

    uuid column. Latest postgres driver supports mapping of this if i was

    directly creating my prepared statements but since i use ibatis inbetween, i

    am unable to achive this as ibatis does not seem to understand that

    java.util.UUID can be based down to jdbc. I could use typehandler if the db

    column was a VARCHAR, but since the postgres db is a 'uuid' type, i have  no

    option but to use the driver supported option of using java.util.UUID, but

    it does not work with ibatis for me.


    Please help me if some one has a solution to this.


    Regards

    Vikram


    ---------------------------------------------------------------------

    To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org 
<mailto:user-java-unsubscr...@ibatis.apache.org>

    For additional commands, e-mail: user-java-h...@ibatis.apache.org 
<mailto:user-java-h...@ibatis.apache.org>
--------------------------------------------------------------------- 
To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org 
<mailto:user-java-unsubscr...@ibatis.apache.org>
For additional commands, e-mail: user-java-h...@ibatis.apache.org 
<mailto:user-java-h...@ibatis.apache.org>

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2676 - Release Date: 02/10/10 21:38:00

Reply via email to