You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Naveen <na...@gmail.com> on 2018/03/07 06:11:48 UTC
Does Merge statement (DML) work with JDBC client driver
Hi
We are using Ignite 2.3
We would like to have 2 Ignite clusters running - one with Ignite native
persistence and second with Oracle
database as persistent layer.
So keeping this in mind, we would like to the build our code base which
should work for both the clusters.
I have read that ignite is not fully SQL compliant when we used 3rd party
Persistence, may be some of the SQLs are not supported with RDBMS.
Can we get any document which gives us this information about the SQLs which
are not supported with Oracle
Do we have any checklist sort off to validate what works for both and
accordingly we can use.
Thanks
Naveen
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Does Merge statement (DML) work with JDBC client driver
Posted by vkulichenko <va...@gmail.com>.
Naveen,
Ignite provides out of the box implementation for RDBMS. The easiest way to
integration would be to use Web Console to generate all required POJO
classes and configurations:
https://apacheignite-tools.readme.io/docs/automatic-rdbms-integration
-Val
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Does Merge statement (DML) work with JDBC client driver
Posted by Naveen <na...@gmail.com>.
Hi Stan
I do not want to Oracle with native persistence, I only wanted to use Oracle
persistent layer.
Are you sure, we need to implement cacheStore for each table we have in the
cluster ?
If that is the case, we need to have separate code base for Oracle as
persistence layer and another version of code base for native persistence?
At the moment, since I am using native persistent, I just created tables
thru JDBC and doing all the writes and reads also thru JDBC, so I have not
developed any POJO for any of the tables.
Is my understanding correct ?
Thanks
Naveen
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Does Merge statement (DML) work with JDBC client driver
Posted by Stanislav Lukyanov <st...@gmail.com>.
The XML config seems fine, although AFAIU you don’t want to use Oracle together with native persistence, so you can remove
dataStorageConfiguration section.
Yes, you’ll need to implement a CacheStore similarly to the example you’ve posted.
It’s also described here: https://apacheignite.readme.io/docs/3rd-party-store.
Thanks,
Stan
From: Naveen
Sent: 7 марта 2018 г. 15:20
To: user@ignite.apache.org
Subject: RE: Does Merge statement (DML) work with JDBC client driver
Hi Stan
Currently I am using JDBC thin driver to connect to Ignite cluster for both
read/write.
Below is the config xml I am using
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd">
<bean class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="clientMode" value="true"/>
<property name="igniteInstanceName" value="IgnitePOC"/>
<property name="dataStorageConfiguration">
<bean class="org.apache.ignite.configuration.DataStorageConfiguration">
<property name="defaultDataRegionConfiguration">
<bean
class="org.apache.ignite.configuration.DataRegionConfiguration">
<property name="persistenceEnabled" value="true"/>
</bean>
</property>
</bean>
</property>
<property name="discoverySpi">
<bean
class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
<property name="ipFinder">
<bean
class="org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder">
<property name="addresses">
<list>
<value>10.144.114.113:47500..47502</value>
<value>10.144.114.114:47500..47502</value>
<value>10.144.114.115:47500..47502</value>
</list>
</property>
</bean>
</property>
</bean>
</property>
</bean>
</beans>
If i wanted to use Oracle as Persistent layer,what changes I should be doing
for the config XML
As mentioned in the examples, do I need to implement CacheStore for each
table load, write, remove operations sameway mentioned below
public class CacheJdbcPersonStore extends CacheStoreAdapter<Long, Person> {
// This mehtod is called whenever "get(...)" methods are called on
IgniteCache.
@Override public Person load(Long key) {
try (Connection conn = connection()) {
try (PreparedStatement st = conn.prepareStatement("select * from
PERSONS where id=?")) {
st.setLong(1, key);
ResultSet rs = st.executeQuery();
return rs.next() ? new Person(rs.getLong(1), rs.getString(2),
rs.getString(3)) : null;
}
}
catch (SQLException e) {
throw new CacheLoaderException("Failed to load: " + key, e);
}
}
// This mehtod is called whenever "put(...)" methods are called on
IgniteCache.
@Override public void write(Cache.Entry<Long, Person> entry) {
try (Connection conn = connection()) {
// Syntax of MERGE statement is database specific and should be
adopted for your database.
// If your database does not support MERGE statement then use
sequentially update, insert statements.
try (PreparedStatement st = conn.prepareStatement(
"merge into PERSONS (id, firstName, lastName) key (id) VALUES (?, ?,
?)")) {
for (Cache.Entry<Long, Person> entry : entries) {
Person val = entry.getValue();
st.setLong(1, entry.getKey());
st.setString(2, val.getFirstName());
st.setString(3, val.getLastName());
st.executeUpdate();
}
}
}
catch (SQLException e) {
throw new CacheWriterException("Failed to write [key=" + key + ",
val=" + val + ']', e);
}
}
// This mehtod is called whenever "remove(...)" methods are called on
IgniteCache.
@Override public void delete(Object key) {
try (Connection conn = connection()) {
try (PreparedStatement st = conn.prepareStatement("delete from PERSONS
where id=?")) {
st.setLong(1, (Long)key);
st.executeUpdate();
}
}
catch (SQLException e) {
throw new CacheWriterException("Failed to delete: " + key, e);
}
}
// This mehtod is called whenever "loadCache()" and "localLoadCache()"
// methods are called on IgniteCache. It is used for bulk-loading the
cache.
// If you don't need to bulk-load the cache, skip this method.
@Override public void loadCache(IgniteBiInClosure<Long, Person> clo,
Object... args) {
if (args == null || args.length == 0 || args[0] == null)
throw new CacheLoaderException("Expected entry count parameter is not
provided.");
final int entryCnt = (Integer)args[0];
try (Connection conn = connection()) {
try (PreparedStatement st = conn.prepareStatement("select * from
PERSONS")) {
try (ResultSet rs = st.executeQuery()) {
int cnt = 0;
while (cnt < entryCnt && rs.next()) {
Person person = new Person(rs.getLong(1), rs.getString(2),
rs.getString(3));
clo.apply(person.getId(), person);
cnt++;
}
}
}
}
catch (SQLException e) {
throw new CacheLoaderException("Failed to load values from cache
store.", e);
}
}
// Open JDBC connection.
private Connection connection() throws SQLException {
// Open connection to your RDBMS systems (Oracle, MySQL, Postgres, DB2,
Microsoft SQL, etc.)
// In this example we use H2 Database for simplification.
Connection conn =
DriverManager.getConnection("jdbc:h2:mem:example;DB_CLOSE_DELAY=-1");
conn.setAutoCommit(true);
return conn;
}
}
OR are there any other ways we can implement this
Thanks
Naveen
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Does Merge statement (DML) work with JDBC client driver
Posted by Naveen <na...@gmail.com>.
Hi Stan
Currently I am using JDBC thin driver to connect to Ignite cluster for both
read/write.
Below is the config xml I am using
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd">
<bean class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="clientMode" value="true"/>
<property name="igniteInstanceName" value="IgnitePOC"/>
<property name="dataStorageConfiguration">
<bean class="org.apache.ignite.configuration.DataStorageConfiguration">
<property name="defaultDataRegionConfiguration">
<bean
class="org.apache.ignite.configuration.DataRegionConfiguration">
<property name="persistenceEnabled" value="true"/>
</bean>
</property>
</bean>
</property>
<property name="discoverySpi">
<bean
class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
<property name="ipFinder">
<bean
class="org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder">
<property name="addresses">
<list>
<value>10.144.114.113:47500..47502</value>
<value>10.144.114.114:47500..47502</value>
<value>10.144.114.115:47500..47502</value>
</list>
</property>
</bean>
</property>
</bean>
</property>
</bean>
</beans>
If i wanted to use Oracle as Persistent layer,what changes I should be doing
for the config XML
As mentioned in the examples, do I need to implement CacheStore for each
table load, write, remove operations sameway mentioned below
public class CacheJdbcPersonStore extends CacheStoreAdapter<Long, Person> {
// This mehtod is called whenever "get(...)" methods are called on
IgniteCache.
@Override public Person load(Long key) {
try (Connection conn = connection()) {
try (PreparedStatement st = conn.prepareStatement("select * from
PERSONS where id=?")) {
st.setLong(1, key);
ResultSet rs = st.executeQuery();
return rs.next() ? new Person(rs.getLong(1), rs.getString(2),
rs.getString(3)) : null;
}
}
catch (SQLException e) {
throw new CacheLoaderException("Failed to load: " + key, e);
}
}
// This mehtod is called whenever "put(...)" methods are called on
IgniteCache.
@Override public void write(Cache.Entry<Long, Person> entry) {
try (Connection conn = connection()) {
// Syntax of MERGE statement is database specific and should be
adopted for your database.
// If your database does not support MERGE statement then use
sequentially update, insert statements.
try (PreparedStatement st = conn.prepareStatement(
"merge into PERSONS (id, firstName, lastName) key (id) VALUES (?, ?,
?)")) {
for (Cache.Entry<Long, Person> entry : entries) {
Person val = entry.getValue();
st.setLong(1, entry.getKey());
st.setString(2, val.getFirstName());
st.setString(3, val.getLastName());
st.executeUpdate();
}
}
}
catch (SQLException e) {
throw new CacheWriterException("Failed to write [key=" + key + ",
val=" + val + ']', e);
}
}
// This mehtod is called whenever "remove(...)" methods are called on
IgniteCache.
@Override public void delete(Object key) {
try (Connection conn = connection()) {
try (PreparedStatement st = conn.prepareStatement("delete from PERSONS
where id=?")) {
st.setLong(1, (Long)key);
st.executeUpdate();
}
}
catch (SQLException e) {
throw new CacheWriterException("Failed to delete: " + key, e);
}
}
// This mehtod is called whenever "loadCache()" and "localLoadCache()"
// methods are called on IgniteCache. It is used for bulk-loading the
cache.
// If you don't need to bulk-load the cache, skip this method.
@Override public void loadCache(IgniteBiInClosure<Long, Person> clo,
Object... args) {
if (args == null || args.length == 0 || args[0] == null)
throw new CacheLoaderException("Expected entry count parameter is not
provided.");
final int entryCnt = (Integer)args[0];
try (Connection conn = connection()) {
try (PreparedStatement st = conn.prepareStatement("select * from
PERSONS")) {
try (ResultSet rs = st.executeQuery()) {
int cnt = 0;
while (cnt < entryCnt && rs.next()) {
Person person = new Person(rs.getLong(1), rs.getString(2),
rs.getString(3));
clo.apply(person.getId(), person);
cnt++;
}
}
}
}
catch (SQLException e) {
throw new CacheLoaderException("Failed to load values from cache
store.", e);
}
}
// Open JDBC connection.
private Connection connection() throws SQLException {
// Open connection to your RDBMS systems (Oracle, MySQL, Postgres, DB2,
Microsoft SQL, etc.)
// In this example we use H2 Database for simplification.
Connection conn =
DriverManager.getConnection("jdbc:h2:mem:example;DB_CLOSE_DELAY=-1");
conn.setAutoCommit(true);
return conn;
}
}
OR are there any other ways we can implement this
Thanks
Naveen
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Does Merge statement (DML) work with JDBC client driver
Posted by Stanislav Lukyanov <st...@gmail.com>.
Hi Naveen,
When you’re using Ignite SQL queries they aren’t processed by the Oracle’s SQL engine.
Instead, Ignite will process the SQL itself (via H2 database engine) and get the data from its cache.
For that to work you need to first preload the data to the cache via a CacheStore.loadCache() – and to implement
that you’ll need to query the data from Oracle using Oracle SQL.
Other words, when you use Oracle as the persistent storage and access Ignite’s data via SQL
you have 2 SQL dialects:
- Ignite’s (basically H2’s) dialect used by the application to get data from Ignite’s cache
- Oracle’s dialect used by the CacheStore implementation to load the data from Oracle DB
If you use Ignite native persistence, you will not have a CacheStore and no second SQL dialect.
The queries that the application use to access Ignite’s cache stay the same since Ignite’s SQL doesn’t
rely on which persistence mechanism is enabled.
For the reference on the SQL syntax supported by Ignite please see https://apacheignite-sql.readme.io/docs/dml.
Thanks,
Stan
From: Naveen
Sent: 7 марта 2018 г. 9:11
To: user@ignite.apache.org
Subject: Does Merge statement (DML) work with JDBC client driver
Hi
We are using Ignite 2.3
We would like to have 2 Ignite clusters running - one with Ignite native
persistence and second with Oracle
database as persistent layer.
So keeping this in mind, we would like to the build our code base which
should work for both the clusters.
I have read that ignite is not fully SQL compliant when we used 3rd party
Persistence, may be some of the SQLs are not supported with RDBMS.
Can we get any document which gives us this information about the SQLs which
are not supported with Oracle
Do we have any checklist sort off to validate what works for both and
accordingly we can use.
Thanks
Naveen
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/