You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by michal23849 <mi...@gmail.com> on 2018/08/07 10:27:28 UTC
Ignite with POJO persistency in SQLServer
Hi,
I have my Ignite that has complex objects classes with not only generic Java
data types, but also other classes and arrays of classes.
I managed to setup my PojoStore that successfully writes (write-behind) data
to SQL Server table for all generic data types.
I see no examples or guidelines, how to map the embedded objects to separate
SQL Server tables (or the same SQL Server table).
Please advise.
My EquityClass has the following fields:
Long equityID;
private ListingCode firstCode;
private String equityName;
private String equityType;
private String equityClass;
private Set<Listing> listings;
And I have problem mapping the firstCode and the listings - array of Listing
objects.
Is it possible? If yes, could you share any examples of mapping such
embedded objects into SQL DBs?
Regards,
Michal
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by "slava.koptilin" <sl...@gmail.com>.
Hello,
I am sorry for the late response.
Yes, I would try to use BINARY.
Thanks,
S.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by michal23849 <mi...@gmail.com>.
Thanks for this, this clarifies a lot. Could you only advise which data type
should be used and how to map the field of type which is my class?
Should I use BINARY or this cannot be handled at all? My intuition is that I
should map the my.package.ListingCode to the java.sql.Types.JAVA_OBJECT.
However if it is not supported the only other binary object would be byte[]
- but this also returned an error.
Can you advise any solution?
Thank you
Michal
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by Вячеслав Коптилин <sl...@gmail.com>.
Hello,
Perhaps, I'm missing something but it seems that LONGVARBINARY is not
supported by Apache Ignite.
The full list of available types can be found here:
https://apacheignite-sql.readme.io/docs/data-types
Thanks,
S.
ср, 8 авг. 2018 г. в 18:52, michal23849 <mi...@gmail.com>:
> Hi All,
>
> I tried mapping the fields in number of different combinations based on the
> above, but all the time I am failing with the SQLServerException: The
> conversion from UNKNOWN to UNKNOWN is unsupported.
>
> The mappings I used in the following structure included:
>
> <bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
> <property
> name="databaseFieldType" >
> <util:constant
> static-field="java.sql.Types.LONGVARBINARY"/>
> </property>
> <property
> name="databaseFieldName" value="firstCode" />
> <property
> name="javaFieldType" value="java.lang.Byte[]" />
> <property
> name="javaFieldName" value="firstCode" />
> </bean>
>
> I also checked other combinations of:
> javaFieldTypes:
> my.package.ListingCode
> byte[]
> java.lang.Byte[]
> java.sql.Blob
> Object
>
> to JdbcTypes (java.sql.Types.):
> LONGVARBINARY
> VARBINARY
>
> Based on the SQLServer JDBC driver documentation and Ignite's all this
> should be supported. Could you please shed some more live how the object is
> passed to the driver and how best it should be mapped in XML?
>
> Thank you
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Ignite with POJO persistency in SQLServer
Posted by michal23849 <mi...@gmail.com>.
Hi All,
I tried mapping the fields in number of different combinations based on the
above, but all the time I am failing with the SQLServerException: The
conversion from UNKNOWN to UNKNOWN is unsupported.
The mappings I used in the following structure included:
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<property
name="databaseFieldType" >
<util:constant
static-field="java.sql.Types.LONGVARBINARY"/>
</property>
<property
name="databaseFieldName" value="firstCode" />
<property
name="javaFieldType" value="java.lang.Byte[]" />
<property
name="javaFieldName" value="firstCode" />
</bean>
I also checked other combinations of:
javaFieldTypes:
my.package.ListingCode
byte[]
java.lang.Byte[]
java.sql.Blob
Object
to JdbcTypes (java.sql.Types.):
LONGVARBINARY
VARBINARY
Based on the SQLServer JDBC driver documentation and Ignite's all this
should be supported. Could you please shed some more live how the object is
passed to the driver and how best it should be mapped in XML?
Thank you
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by michal23849 <mi...@gmail.com>.
Thank you for the help!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by aealexsandrov <ae...@gmail.com>.
Yes. In case if you don't want to store it as objects then you can move this
fields to original object:
class a{
Int a;
class b;
}
class b{
int b;
int c;
}
You can change it as next:
class a{
int a,
int b;
int c;
}
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by michal23849 <mi...@gmail.com>.
Andrei,
As I understand you - the only way to map embedded classes is by using
mapping them as objects and store as BLOBs or other VARBINARY fields in SQL
database?
No way to decompose them into separate fields in the tables?
Eg. ListingCode has:
private String code;
private String codeType;
Please confirm if the BLOB is only way or there is any way to get the
embedded object's fields and store them in separate columns.
I understand same goes for the array - only store it in BLOB or redesign the
domain object model, right?
Thank you
Michal
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by aealexsandrov <ae...@gmail.com>.
From web console sources I see next mapping:
{"dbName": "BIT", "dbType": -7, "signed": {"javaType": "Boolean",
"primitiveType": "boolean"}},
{"dbName": "TINYINT", "dbType": -6,
"signed": {"javaType": "Byte", "primitiveType": "byte"},
"unsigned": {"javaType": "Short", "primitiveType": "short"}},
{"dbName": "SMALLINT", "dbType": 5,
"signed": {"javaType": "Short", "primitiveType": "short"},
"unsigned": {"javaType": "Integer", "primitiveType": "int"}},
{"dbName": "INTEGER", "dbType": 4,
"signed": {"javaType": "Integer", "primitiveType": "int"},
"unsigned": {"javaType": "Long", "primitiveType": "long"}},
{"dbName": "BIGINT", "dbType": -5, "signed": {"javaType": "Long",
"primitiveType": "long"}},
{"dbName": "FLOAT", "dbType": 6, "signed": {"javaType": "Float",
"primitiveType": "float"}},
{"dbName": "REAL", "dbType": 7, "signed": {"javaType": "Double",
"primitiveType": "double"}},
{"dbName": "DOUBLE", "dbType": 8, "signed": {"javaType": "Double",
"primitiveType": "double"}},
{"dbName": "NUMERIC", "dbType": 2, "signed": {"javaType":
"BigDecimal"}},
{"dbName": "DECIMAL", "dbType": 3, "signed": {"javaType":
"BigDecimal"}},
{"dbName": "CHAR", "dbType": 1, "signed": {"javaType": "String"}},
{"dbName": "VARCHAR", "dbType": 12, "signed": {"javaType": "String"}},
{"dbName": "LONGVARCHAR", "dbType": -1, "signed": {"javaType":
"String"}},
{"dbName": "DATE", "dbType": 91, "signed": {"javaType": "Date"}},
{"dbName": "TIME", "dbType": 92, "signed": {"javaType": "Time"}},
{"dbName": "TIMESTAMP", "dbType": 93, "signed": {"javaType":
"Timestamp"}},
{"dbName": "BINARY", "dbType": -2, "signed": {"javaType": "byte[]"}},
{"dbName": "VARBINARY", "dbType": -3, "signed": {"javaType": "byte[]"}},
{"dbName": "LONGVARBINARY", "dbType": -4, "signed": {"javaType":
"byte[]"}},
{"dbName": "NULL", "dbType": 0, "signed": {"javaType": "Object"}},
{"dbName": "OTHER", "dbType": 1111, "signed": {"javaType": "Object"}},
{"dbName": "JAVA_OBJECT", "dbType": 2000, "signed": {"javaType":
"Object"}},
{"dbName": "DISTINCT", "dbType": 2001, "signed": {"javaType":
"Object"}},
{"dbName": "STRUCT", "dbType": 2002, "signed": {"javaType": "Object"}},
{"dbName": "ARRAY", "dbType": 2003, "signed": {"javaType": "Object"}},
{"dbName": "BLOB", "dbType": 2004, "signed": {"javaType": "Object"}},
{"dbName": "CLOB", "dbType": 2005, "signed": {"javaType": "String"}},
{"dbName": "REF", "dbType": 2006, "signed": {"javaType": "Object"}},
{"dbName": "DATALINK", "dbType": 70, "signed": {"javaType": "Object"}},
{"dbName": "BOOLEAN", "dbType": 16, "signed": {"javaType": "Boolean",
"primitiveType": "boolean"}},
{"dbName": "ROWID", "dbType": -8, "signed": {"javaType": "Object"}},
{"dbName": "NCHAR", "dbType": -15, "signed": {"javaType": "String"}},
{"dbName": "NVARCHAR", "dbType": -9, "signed": {"javaType": "String"}},
{"dbName": "LONGNVARCHAR", "dbType": -16, "signed": {"javaType":
"String"}},
{"dbName": "NCLOB", "dbType": 2011, "signed": {"javaType": "String"}},
{"dbName": "SQLXML", "dbType": 2009, "signed": {"javaType": "Object"}}
I guess that you can use it in your schemes.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by aealexsandrov <ae...@gmail.com>.
I am not fully sure but according to the specification of java.sql.Types you
can try to use next for Java objects:
https://docs.oracle.com/javase/7/docs/api/java/sql/Types.html#JAVA_OBJECT
https://docs.oracle.com/javase/7/docs/api/java/sql/Types.html#OTHER
<bean
class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<property
name="databaseFieldType">
<util:constant
static-field="java.sql.Types.JAVA_OBJECT"/>
</property>
<property
name="databaseFieldName" value="firstCode"/>
<property
name="javaFieldType"
value="your.package.ListingCode"/>
<property
name="javaFieldName" value="firstCode"/>
</bean>
BR,
Andrei
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by michal23849 <mi...@gmail.com>.
Hi Anrei,
My goal is to map the data using CacheJdbcPojoStore and save the data that I
already have in Ignite to SQLServer.
The data model has embedded classes and I don't know how to map them.
Currently I got the following setup, which works fine, but that is only the
subset of data.
<property name="cacheStoreFactory">
<bean
class="org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory">
<property name="dataSourceBean"
value="SQLDataSource" />
<property name="dialect">
<bean
class="org.apache.ignite.cache.store.jdbc.dialect.SQLServerDialect">
</bean>
</property>
<property name="types">
<list>
<bean
class="org.apache.ignite.cache.store.jdbc.JdbcType">
<property name="cacheName"
value="myCache" />
<property name="databaseTable"
value="EquityCache1" />
<property name="keyType"
value="your.package.EquityKey" />
<property name="keyFields">
<list>
<bean
class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<property
name="databaseFieldType" >
<util:constant
static-field="java.sql.Types.VARCHAR"/>
</property>
<property
name="databaseFieldName" value="identifier" />
<property
name="javaFieldType" value="java.lang.String" />
<property
name="javaFieldName" value="identifier" />
</bean>
<bean
class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<property
name="databaseFieldType" >
<util:constant
static-field="java.sql.Types.VARCHAR"/>
</property>
<property
name="databaseFieldName" value="identifierType" />
<property
name="javaFieldType" value="java.lang.String" />
<property
name="javaFieldName" value="identifierType" />
</bean>
</list>
</property>
<property name="valueType"
value="your.package.Equity" />
<property name="valueFields">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<property
name="databaseFieldType" >
<util:constant
static-field="java.sql.Types.INTEGER"/>
</property>
<property
name="databaseFieldName" value="equityID" />
<property
name="javaFieldType" value="java.lang.Long" />
<property
name="javaFieldName" value="equityID" />
</bean>
<bean
class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<property
name="databaseFieldType" >
<util:constant
static-field="java.sql.Types.VARCHAR"/>
</property>
<property
name="databaseFieldName" value="equityName" />
<property
name="javaFieldType" value="java.lang.String" />
<property
name="javaFieldName" value="equityName" />
</bean>
<bean
class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<property
name="databaseFieldType" >
<util:constant
static-field="java.sql.Types.VARCHAR"/>
</property>
<property
name="databaseFieldName" value="equityType" />
<property
name="javaFieldType" value="java.lang.String" />
<property
name="javaFieldName" value="equityType" />
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</property>
Is it possible to map the firstType (class ListingCode) and the listings
(which is the collection of Listing object)?
Thanks,
Michal
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite with POJO persistency in SQLServer
Posted by aealexsandrov <ae...@gmail.com>.
Hi,
You have pogo class - your.package.EquityClass wuth next fields:
Long equityID;
private ListingCode firstCode;
private String equityName;
private String equityType;
private String equityClass;
private Set<Listing> listings;
and you are going to store in into ignite. In this case, if you can do it
like next:
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="CACHE_NAME"/>
<property name="sqlSchema" value="SQL_SCHEMA"/>
<property name="cacheMode" value="PARTITIONED"/>
<property name="atomicityMode" value="TRANSACTIONAL"/>
<bean class="org.apache.ignite.cache.QueryEntity">
<property name="keyType" value="java.lang.Long"/>
<property name="valueType" value="your.package.EquityClass"/>
<property name="tableName" value="EquityClassTable"/>
<property name="fields">
<map>
<entry key="equityID" value="java.lang.Long"/>
<entry key="equityName" value="java.lang.String"/>
<entry key="equityType" value="java.lang.String"/>
<entry key="equityClass" value="java.lang.String"/>
<entry key="listings" value="java.util.HashSet"/>
<entry key="firstCode" value="your.package.ListingCode"/>
</map>
</property>
<property name="keyFields">
<set>
<value>equityID</value>
</set>
</property>
<property name="indexes">
<list>
<bean class="org.apache.ignite.cache.QueryIndex">
<property name="name" value="EquityClassIdx"/>
<property name="indexType" value="SORTED"/>
<property name="fields">
<map>
<entry key="equityID" value="true"/>
</map>
</property>
</bean>
</list>
</property>
</bean>
</bean>
After that you can use it as next:
IgniteCache<Long, EquityClass> cache = ign.getOrCreateCache("CACHE_NAME");
BR,
Andrei
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/