ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vikram Subbarao <vikra...@directi.com>
Subject Re: java.util.UUID to postgres uuid column
Date Sun, 28 Feb 2010 09:49:12 GMT
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:vikram.s@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:vikram.s@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 <vikram.s@directi.com> <mailto:vikram.s@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-unsubscribe@ibatis.apache.org <mailto:user-java-unsubscribe@ibatis.apache.org>
>>
>>     For additional commands, e-mail: user-java-help@ibatis.apache.org <mailto:user-java-help@ibatis.apache.org>
>>
>>      
>>
>>      
>>
>>         
>>
>>  
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org <mailto:user-java-unsubscribe@ibatis.apache.org>
>> For additional commands, e-mail: user-java-help@ibatis.apache.org <mailto:user-java-help@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
>>

Mime
View raw message