[
https://issues.apache.org/jira/browse/IGNITE-20024?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
bin.yin updated IGNITE-20024:
-----------------------------
Description:
h3. Question：
{{First, I use `IgniteCache#loadCache` load from RDBMS. When I use
IgniteCache.get(key) to get the corresponding entity, ignite throws the
following error "Can not set java.time.LocalDateTime field
org.apache.ignite.examples.model.Person.createdTime to java.sql .Timestamp"}}
h3. Conditions and usage：
# This code run in 'ignite-examples' module;
# In ignite 2.14.0, I use H2 as the underlying storage;
# In `{{{}CacheJdbcPojoStoreFactory`{}}}, I configured the mapping between the
`{{{}datetime type`{}}} of the database and JDK8's `{{{}LocalDateTime`{}}}:
{{new JdbcTypeField(Types.TIMESTAMP, "created_time", LocalDateTime.class,
"createdTime")}}
h3. Problem analysis:
By default, {{CacheJdbcPojoStore}} uses
{{org.apache.ignite.cache.store.jdbc.JdbcTypesTransformer#getColumnValue}} to
parse jdbc's ResultSet.
For the {{LocalDateTime}} type of JDK8, {{java.sql.ResultSet#getObject(int)}}
is used to obtain the value of the specified column (here it is read as
{{{}java.sql.Timestamp{}}}). When the object is finally constructed, the type
is inconsistent Causes {{sun.reflect.FieldAccessor#set}} assignment to fail.
*Java Code:*
CacheJdbcPojoStore.java:
{code:java}
package org.apache.ignite.examples.datagrid.store.auto;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.CacheAtomicityMode;
import org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory;
import org.apache.ignite.cache.store.jdbc.JdbcType;
import org.apache.ignite.cache.store.jdbc.JdbcTypeField;
import org.apache.ignite.cache.store.jdbc.dialect.H2Dialect;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.examples.model.Person;
import org.apache.ignite.examples.util.DbH2ServerStartup;
import org.h2.jdbcx.JdbcConnectionPool;
import javax.cache.configuration.Factory;
import javax.sql.DataSource;
import java.sql.Types;
import java.time.LocalDateTime;
/**
* To start the example, you should:
* <ul>
* <li>Start H2 database TCP server using {@link DbH2ServerStartup}.</li>
* <li>Start example using {@link CacheAutoStoreExample}.</li>
* </ul>
* <p>
*/
public class CacheJdbcPojoStore {
private static final String CACHE_NAME =
CacheJdbcPojoStore.class.getSimpleName();
public static void main(String[] args) throws Exception {
DbH2ServerStartup.populateDatabase();
try (Ignite ignite = Ignition.start("example-ignite.xml")) {
System.out.println();
System.out.println(">>> Cache store example started.");
try (IgniteCache<Long, Person> cache =
ignite.getOrCreateCache(cacheConfiguration())) {
cache.loadCache(null, Long.class.getName(), "SELECT * FROM
PERSON WHERE ID = 3");
System.out.println(cache.get(3L));
}
finally {
ignite.destroyCache(CACHE_NAME);
}
}
}
private static CacheConfiguration<Long, Person> cacheConfiguration() {
CacheConfiguration<Long, Person> cacheCfg = new
CacheConfiguration<>(CACHE_NAME);
cacheCfg.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
// Configure JDBC store.
CacheJdbcPojoStoreFactory<Long, Person> factory = new
CacheJdbcPojoStoreFactory<>();
factory.setDialect(new H2Dialect());
factory.setDataSourceFactory((Factory<DataSource>) () -> {
return
JdbcConnectionPool.create("jdbc:h2:tcp://localhost/mem:ExampleDb", "sa", "");
});
JdbcType jdbcType = new JdbcType();
jdbcType.setCacheName(CACHE_NAME);
jdbcType.setDatabaseTable("PERSON");
jdbcType.setKeyType(Long.class);
jdbcType.setValueType(Person.class);
jdbcType.setKeyFields(new JdbcTypeField(Types.BIGINT, "id", Long.class,
"id"));
jdbcType.setValueFields(
new JdbcTypeField(Types.BIGINT, "id", Long.class, "id"),
new JdbcTypeField(Types.VARCHAR, "first_name", String.class,
"firstName"),
new JdbcTypeField(Types.VARCHAR, "last_name", String.class,
"lastName"),
new JdbcTypeField(Types.TIMESTAMP, "created_time",
LocalDateTime.class, "createdTime")
);
factory.setTypes(jdbcType);
cacheCfg.setCacheStoreFactory(factory);
cacheCfg.setIndexedTypes(Long.class, Person.class);
return cacheCfg;
}
}
{code}
Person.java:
{code:java}
package org.apache.ignite.examples.model;
import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.concurrent.atomic.AtomicLong;
import org.apache.ignite.cache.affinity.AffinityKey;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
import org.apache.ignite.cache.query.annotations.QueryTextField;
/**
* Person class.
*/
public class Person implements Serializable {
/** */
private static final AtomicLong ID_GEN = new AtomicLong();
/** Name of index by two fields (orgId, salary). */
public static final String ORG_SALARY_IDX = "ORG_SALARY_IDX";
/** Person ID (indexed). */
@QuerySqlField(index = true)
public Long id;
/** Organization ID (indexed). */
@QuerySqlField(index = true, orderedGroups = @QuerySqlField.Group(name =
ORG_SALARY_IDX, order = 0))
public Long orgId;
/** First name (not-indexed). */
@QuerySqlField
public String firstName;
/** Last name (not indexed). */
@QuerySqlField
public String lastName;
@QuerySqlField
public LocalDateTime createdTime;
/** Resume text (create LUCENE-based TEXT index for this field). */
@QueryTextField
public String resume;
/** Salary (indexed). */
@QuerySqlField(index = true, orderedGroups = @QuerySqlField.Group(name =
ORG_SALARY_IDX, order = 1))
public double salary;
/** Custom cache key to guarantee that person is always colocated with its
organization. */
private transient AffinityKey<Long> key;
/**
* Default constructor.
*/
public Person() {
// No-op.
}
/**
* Constructs person record.
*
* @param org Organization.
* @param firstName First name.
* @param lastName Last name.
* @param salary Salary.
* @param resume Resume text.
*/
public Person(Organization org, String firstName, String lastName, double
salary, String resume) {
// Generate unique ID for this person.
id = ID_GEN.incrementAndGet();
orgId = org.id();
this.firstName = firstName;
this.lastName = lastName;
this.createdTime = LocalDateTime.now();
this.salary = salary;
this.resume = resume;
}
/**
* Constructs person record.
*
* @param id Person ID.
* @param orgId Organization ID.
* @param firstName First name.
* @param lastName Last name.
* @param salary Salary.
* @param resume Resume text.
*/
public Person(Long id, Long orgId, String firstName, String lastName,
double salary, String resume) {
this.id = id;
this.orgId = orgId;
this.firstName = firstName;
this.lastName = lastName;
this.createdTime = LocalDateTime.now();
this.salary = salary;
this.resume = resume;
}
/**
* Constructs person record.
*
* @param id Person ID.
* @param firstName First name.
* @param lastName Last name.
*/
public Person(Long id, String firstName, String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.createdTime = LocalDateTime.now();
}
/**
* Gets cache affinity key. Since in some examples person needs to be
collocated with organization, we create
* custom affinity key to guarantee this collocation.
*
* @return Custom affinity key to guarantee that person is always
collocated with organization.
*/
public AffinityKey<Long> key() {
if (key == null)
key = new AffinityKey<>(id, orgId);
return key;
}
/**
* {@inheritDoc}
*/
@Override public String toString() {
return "Person [id=" + id +
", orgId=" + orgId +
", lastName=" + lastName +
", firstName=" + firstName +
", createdTime=" + createdTime +
", salary=" + salary +
", resume=" + resume + ']';
}
} {code}
DbH2ServerStartup.java:
{code:java}
package org.apache.ignite.examples.util;
import java.io.IOException;
import java.io.StringReader;
import java.sql.SQLException;
import org.apache.ignite.IgniteException;
import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.tools.RunScript;
import org.h2.tools.Server;
/**
* Start H2 database TCP server in order to access sample in-memory database
from other processes.
*/
public class DbH2ServerStartup {
/** Create table script. */
private static final String CREATE_PERSON_TABLE =
"create table if not exists PERSON(id bigint AUTO_INCREMENT PRIMARY
KEY, first_name varchar(50), last_name varchar(50), created_time TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);";
/** Sample data script. */
private static final String POPULATE_PERSON_TABLE =
"delete from PERSON;\n" +
"insert into PERSON(id, first_name, last_name) values(1, 'Johannes',
'Kepler');\n" +
"insert into PERSON(id, first_name, last_name) values(2, 'Galileo',
'Galilei');\n" +
"insert into PERSON(id, first_name, last_name) values(3, 'Henry',
'More');\n" +
"insert into PERSON(id, first_name, last_name) values(4, 'Polish',
'Brethren');\n" +
"insert into PERSON(id, first_name, last_name) values(5, 'Robert',
'Boyle');\n" +
"insert into PERSON(id, first_name, last_name) values(6, 'Wilhelm',
'Leibniz');";
/**
* 初始化数据库
*
* @throws SQLException if
*/
public static void populateDatabase() throws SQLException {
// Try to connect to database TCP server.
JdbcConnectionPool dataSrc =
JdbcConnectionPool.create("jdbc:h2:tcp://localhost/mem:ExampleDb", "sa", "");
// Create Person table in database.
RunScript.execute(dataSrc.getConnection(), new
StringReader(CREATE_PERSON_TABLE));
// Populates Person table with sample data in database.
RunScript.execute(dataSrc.getConnection(), new
StringReader(POPULATE_PERSON_TABLE));
}
/**
* Start H2 database TCP server.
*
* @param args Command line arguments, none required.
* @throws IgniteException If start H2 database TCP server failed.
*/
public static void main(String[] args) throws IgniteException {
try {
// Start H2 database TCP server in order to access sample in-memory
database from other processes.
Server.createTcpServer("-tcpDaemon").start();
Server.createWebServer("-webDaemon").start();
populateDatabase();
}
catch (SQLException e) {
throw new IgniteException("Failed to start database TCP server", e);
}
try {
do {
System.out.println("Type 'q' and press 'Enter' to stop H2 TCP
server...");
}
while ('q' != System.in.read());
}
catch (IOException ignored) {
// No-op.
}
}
}
{code}
was:
h3. Question：
{{First, I use `IgniteCache#loadCache` __ load from RDBMS. When I use
IgniteCache.get(key) to get the corresponding entity, ignite throws the
following error "Can not set java.time.LocalDateTime field
org.apache.ignite.examples.model.Person.createdTime to java.sql .Timestamp"}}
h3. Conditions and usage：
# This code run in 'ignite-examples' module;
# In ignite 2.14.0, I use H2 as the underlying storage;
# In `{{{}CacheJdbcPojoStoreFactory`{}}}, I configured the mapping between the
`{{{}datetime type`{}}} of the database and JDK8's `{{{}LocalDateTime`{}}}:
{{new JdbcTypeField(Types.TIMESTAMP, "created_time", LocalDateTime.class,
"createdTime")}}
h3. Problem analysis:
By default, {{CacheJdbcPojoStore}} uses
{{org.apache.ignite.cache.store.jdbc.JdbcTypesTransformer#getColumnValue}} to
parse jdbc's ResultSet.
For the {{LocalDateTime}} type of JDK8, {{java.sql.ResultSet#getObject(int)}}
is used to obtain the value of the specified column (here it is read as
{{{}java.sql.Timestamp{}}}). When the object is finally constructed, the type
is inconsistent Causes {{sun.reflect.FieldAccessor#set}} assignment to fail.
*Java Code:*
CacheJdbcPojoStore.java:
{code:java}
package org.apache.ignite.examples.datagrid.store.auto;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.CacheAtomicityMode;
import org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory;
import org.apache.ignite.cache.store.jdbc.JdbcType;
import org.apache.ignite.cache.store.jdbc.JdbcTypeField;
import org.apache.ignite.cache.store.jdbc.dialect.H2Dialect;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.examples.model.Person;
import org.apache.ignite.examples.util.DbH2ServerStartup;
import org.h2.jdbcx.JdbcConnectionPool;
import javax.cache.configuration.Factory;
import javax.sql.DataSource;
import java.sql.Types;
import java.time.LocalDateTime;
/**
* To start the example, you should:
* <ul>
* <li>Start H2 database TCP server using {@link DbH2ServerStartup}.</li>
* <li>Start example using {@link CacheAutoStoreExample}.</li>
* </ul>
* <p>
*/
public class CacheJdbcPojoStore {
private static final String CACHE_NAME =
CacheJdbcPojoStore.class.getSimpleName();
public static void main(String[] args) throws Exception {
DbH2ServerStartup.populateDatabase();
try (Ignite ignite = Ignition.start("example-ignite.xml")) {
System.out.println();
System.out.println(">>> Cache store example started.");
try (IgniteCache<Long, Person> cache =
ignite.getOrCreateCache(cacheConfiguration())) {
cache.loadCache(null, Long.class.getName(), "SELECT * FROM
PERSON WHERE ID = 3");
System.out.println(cache.get(3L));
}
finally {
ignite.destroyCache(CACHE_NAME);
}
}
}
private static CacheConfiguration<Long, Person> cacheConfiguration() {
CacheConfiguration<Long, Person> cacheCfg = new
CacheConfiguration<>(CACHE_NAME);
cacheCfg.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
// Configure JDBC store.
CacheJdbcPojoStoreFactory<Long, Person> factory = new
CacheJdbcPojoStoreFactory<>();
factory.setDialect(new H2Dialect());
factory.setDataSourceFactory((Factory<DataSource>) () -> {
return
JdbcConnectionPool.create("jdbc:h2:tcp://localhost/mem:ExampleDb", "sa", "");
});
JdbcType jdbcType = new JdbcType();
jdbcType.setCacheName(CACHE_NAME);
jdbcType.setDatabaseTable("PERSON");
jdbcType.setKeyType(Long.class);
jdbcType.setValueType(Person.class);
jdbcType.setKeyFields(new JdbcTypeField(Types.BIGINT, "id", Long.class,
"id"));
jdbcType.setValueFields(
new JdbcTypeField(Types.BIGINT, "id", Long.class, "id"),
new JdbcTypeField(Types.VARCHAR, "first_name", String.class,
"firstName"),
new JdbcTypeField(Types.VARCHAR, "last_name", String.class,
"lastName"),
new JdbcTypeField(Types.TIMESTAMP, "created_time",
LocalDateTime.class, "createdTime")
);
factory.setTypes(jdbcType);
cacheCfg.setCacheStoreFactory(factory);
cacheCfg.setIndexedTypes(Long.class, Person.class);
return cacheCfg;
}
}
{code}
Person.java:
{code:java}
package org.apache.ignite.examples.model;
import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.concurrent.atomic.AtomicLong;
import org.apache.ignite.cache.affinity.AffinityKey;
import org.apache.ignite.cache.query.annotations.QuerySqlField;
import org.apache.ignite.cache.query.annotations.QueryTextField;
/**
* Person class.
*/
public class Person implements Serializable {
/** */
private static final AtomicLong ID_GEN = new AtomicLong();
/** Name of index by two fields (orgId, salary). */
public static final String ORG_SALARY_IDX = "ORG_SALARY_IDX";
/** Person ID (indexed). */
@QuerySqlField(index = true)
public Long id;
/** Organization ID (indexed). */
@QuerySqlField(index = true, orderedGroups = @QuerySqlField.Group(name =
ORG_SALARY_IDX, order = 0))
public Long orgId;
/** First name (not-indexed). */
@QuerySqlField
public String firstName;
/** Last name (not indexed). */
@QuerySqlField
public String lastName;
@QuerySqlField
public LocalDateTime createdTime;
/** Resume text (create LUCENE-based TEXT index for this field). */
@QueryTextField
public String resume;
/** Salary (indexed). */
@QuerySqlField(index = true, orderedGroups = @QuerySqlField.Group(name =
ORG_SALARY_IDX, order = 1))
public double salary;
/** Custom cache key to guarantee that person is always colocated with its
organization. */
private transient AffinityKey<Long> key;
/**
* Default constructor.
*/
public Person() {
// No-op.
}
/**
* Constructs person record.
*
* @param org Organization.
* @param firstName First name.
* @param lastName Last name.
* @param salary Salary.
* @param resume Resume text.
*/
public Person(Organization org, String firstName, String lastName, double
salary, String resume) {
// Generate unique ID for this person.
id = ID_GEN.incrementAndGet();
orgId = org.id();
this.firstName = firstName;
this.lastName = lastName;
this.createdTime = LocalDateTime.now();
this.salary = salary;
this.resume = resume;
}
/**
* Constructs person record.
*
* @param id Person ID.
* @param orgId Organization ID.
* @param firstName First name.
* @param lastName Last name.
* @param salary Salary.
* @param resume Resume text.
*/
public Person(Long id, Long orgId, String firstName, String lastName,
double salary, String resume) {
this.id = id;
this.orgId = orgId;
this.firstName = firstName;
this.lastName = lastName;
this.createdTime = LocalDateTime.now();
this.salary = salary;
this.resume = resume;
}
/**
* Constructs person record.
*
* @param id Person ID.
* @param firstName First name.
* @param lastName Last name.
*/
public Person(Long id, String firstName, String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.createdTime = LocalDateTime.now();
}
/**
* Gets cache affinity key. Since in some examples person needs to be
collocated with organization, we create
* custom affinity key to guarantee this collocation.
*
* @return Custom affinity key to guarantee that person is always
collocated with organization.
*/
public AffinityKey<Long> key() {
if (key == null)
key = new AffinityKey<>(id, orgId);
return key;
}
/**
* {@inheritDoc}
*/
@Override public String toString() {
return "Person [id=" + id +
", orgId=" + orgId +
", lastName=" + lastName +
", firstName=" + firstName +
", createdTime=" + createdTime +
", salary=" + salary +
", resume=" + resume + ']';
}
} {code}
DbH2ServerStartup.java:
{code:java}
package org.apache.ignite.examples.util;
import java.io.IOException;
import java.io.StringReader;
import java.sql.SQLException;
import org.apache.ignite.IgniteException;
import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.tools.RunScript;
import org.h2.tools.Server;
/**
* Start H2 database TCP server in order to access sample in-memory database
from other processes.
*/
public class DbH2ServerStartup {
/** Create table script. */
private static final String CREATE_PERSON_TABLE =
"create table if not exists PERSON(id bigint AUTO_INCREMENT PRIMARY
KEY, first_name varchar(50), last_name varchar(50), created_time TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);";
/** Sample data script. */
private static final String POPULATE_PERSON_TABLE =
"delete from PERSON;\n" +
"insert into PERSON(id, first_name, last_name) values(1, 'Johannes',
'Kepler');\n" +
"insert into PERSON(id, first_name, last_name) values(2, 'Galileo',
'Galilei');\n" +
"insert into PERSON(id, first_name, last_name) values(3, 'Henry',
'More');\n" +
"insert into PERSON(id, first_name, last_name) values(4, 'Polish',
'Brethren');\n" +
"insert into PERSON(id, first_name, last_name) values(5, 'Robert',
'Boyle');\n" +
"insert into PERSON(id, first_name, last_name) values(6, 'Wilhelm',
'Leibniz');";
/**
* 初始化数据库
*
* @throws SQLException if
*/
public static void populateDatabase() throws SQLException {
// Try to connect to database TCP server.
JdbcConnectionPool dataSrc =
JdbcConnectionPool.create("jdbc:h2:tcp://localhost/mem:ExampleDb", "sa", "");
// Create Person table in database.
RunScript.execute(dataSrc.getConnection(), new
StringReader(CREATE_PERSON_TABLE));
// Populates Person table with sample data in database.
RunScript.execute(dataSrc.getConnection(), new
StringReader(POPULATE_PERSON_TABLE));
}
/**
* Start H2 database TCP server.
*
* @param args Command line arguments, none required.
* @throws IgniteException If start H2 database TCP server failed.
*/
public static void main(String[] args) throws IgniteException {
try {
// Start H2 database TCP server in order to access sample in-memory
database from other processes.
Server.createTcpServer("-tcpDaemon").start();
Server.createWebServer("-webDaemon").start();
populateDatabase();
}
catch (SQLException e) {
throw new IgniteException("Failed to start database TCP server", e);
}
try {
do {
System.out.println("Type 'q' and press 'Enter' to stop H2 TCP
server...");
}
while ('q' != System.in.read());
}
catch (IOException ignored) {
// No-op.
}
}
}
{code}
> When MySQL is used as the underlying storage, an exception is thrown "Can not
> set java.time.LocalDateTime field
> org.apache.ignite.examples.model.Person.createdTime to java.sql.Timestamp"
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: IGNITE-20024
> URL: https://issues.apache.org/jira/browse/IGNITE-20024
> Project: Ignite
> Issue Type: Bug
> Components: persistence
> Reporter: bin.yin
> Assignee: bin.yin
> Priority: Major
>
> h3. Question：
> {{First, I use `IgniteCache#loadCache` load from RDBMS. When I use
> IgniteCache.get(key) to get the corresponding entity, ignite throws the
> following error "Can not set java.time.LocalDateTime field
> org.apache.ignite.examples.model.Person.createdTime to java.sql .Timestamp"}}
> h3. Conditions and usage：
> # This code run in 'ignite-examples' module;
> # In ignite 2.14.0, I use H2 as the underlying storage;
> # In `{{{}CacheJdbcPojoStoreFactory`{}}}, I configured the mapping between
> the `{{{}datetime type`{}}} of the database and JDK8's
> `{{{}LocalDateTime`{}}}:
> {{new JdbcTypeField(Types.TIMESTAMP, "created_time", LocalDateTime.class,
> "createdTime")}}
> h3. Problem analysis:
> By default, {{CacheJdbcPojoStore}} uses
> {{org.apache.ignite.cache.store.jdbc.JdbcTypesTransformer#getColumnValue}} to
> parse jdbc's ResultSet.
> For the {{LocalDateTime}} type of JDK8, {{java.sql.ResultSet#getObject(int)}}
> is used to obtain the value of the specified column (here it is read as
> {{{}java.sql.Timestamp{}}}). When the object is finally constructed, the type
> is inconsistent Causes {{sun.reflect.FieldAccessor#set}} assignment to fail.
>
> *Java Code:*
> CacheJdbcPojoStore.java:
>
> {code:java}
> package org.apache.ignite.examples.datagrid.store.auto;
> import org.apache.ignite.Ignite;
> import org.apache.ignite.IgniteCache;
> import org.apache.ignite.Ignition;
> import org.apache.ignite.cache.CacheAtomicityMode;
> import org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory;
> import org.apache.ignite.cache.store.jdbc.JdbcType;
> import org.apache.ignite.cache.store.jdbc.JdbcTypeField;
> import org.apache.ignite.cache.store.jdbc.dialect.H2Dialect;
> import org.apache.ignite.configuration.CacheConfiguration;
> import org.apache.ignite.examples.model.Person;
> import org.apache.ignite.examples.util.DbH2ServerStartup;
> import org.h2.jdbcx.JdbcConnectionPool;
> import javax.cache.configuration.Factory;
> import javax.sql.DataSource;
> import java.sql.Types;
> import java.time.LocalDateTime;
> /**
> * To start the example, you should:
> * <ul>
> * <li>Start H2 database TCP server using {@link DbH2ServerStartup}.</li>
> * <li>Start example using {@link CacheAutoStoreExample}.</li>
> * </ul>
> * <p>
> */
> public class CacheJdbcPojoStore {
> private static final String CACHE_NAME =
> CacheJdbcPojoStore.class.getSimpleName();
> public static void main(String[] args) throws Exception {
> DbH2ServerStartup.populateDatabase();
> try (Ignite ignite = Ignition.start("example-ignite.xml")) {
> System.out.println();
> System.out.println(">>> Cache store example started.");
> try (IgniteCache<Long, Person> cache =
> ignite.getOrCreateCache(cacheConfiguration())) {
> cache.loadCache(null, Long.class.getName(), "SELECT * FROM
> PERSON WHERE ID = 3");
> System.out.println(cache.get(3L));
> }
> finally {
> ignite.destroyCache(CACHE_NAME);
> }
> }
> }
> private static CacheConfiguration<Long, Person> cacheConfiguration() {
> CacheConfiguration<Long, Person> cacheCfg = new
> CacheConfiguration<>(CACHE_NAME);
> cacheCfg.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
> // Configure JDBC store.
> CacheJdbcPojoStoreFactory<Long, Person> factory = new
> CacheJdbcPojoStoreFactory<>();
> factory.setDialect(new H2Dialect());
> factory.setDataSourceFactory((Factory<DataSource>) () -> {
> return
> JdbcConnectionPool.create("jdbc:h2:tcp://localhost/mem:ExampleDb", "sa", "");
> });
> JdbcType jdbcType = new JdbcType();
> jdbcType.setCacheName(CACHE_NAME);
> jdbcType.setDatabaseTable("PERSON");
> jdbcType.setKeyType(Long.class);
> jdbcType.setValueType(Person.class);
> jdbcType.setKeyFields(new JdbcTypeField(Types.BIGINT, "id",
> Long.class, "id"));
> jdbcType.setValueFields(
> new JdbcTypeField(Types.BIGINT, "id", Long.class, "id"),
> new JdbcTypeField(Types.VARCHAR, "first_name", String.class,
> "firstName"),
> new JdbcTypeField(Types.VARCHAR, "last_name", String.class,
> "lastName"),
> new JdbcTypeField(Types.TIMESTAMP, "created_time",
> LocalDateTime.class, "createdTime")
> );
> factory.setTypes(jdbcType);
> cacheCfg.setCacheStoreFactory(factory);
> cacheCfg.setIndexedTypes(Long.class, Person.class);
> return cacheCfg;
> }
> }
> {code}
> Person.java:
>
> {code:java}
> package org.apache.ignite.examples.model;
> import java.io.Serializable;
> import java.time.LocalDateTime;
> import java.util.concurrent.atomic.AtomicLong;
> import org.apache.ignite.cache.affinity.AffinityKey;
> import org.apache.ignite.cache.query.annotations.QuerySqlField;
> import org.apache.ignite.cache.query.annotations.QueryTextField;
> /**
> * Person class.
> */
> public class Person implements Serializable {
> /** */
> private static final AtomicLong ID_GEN = new AtomicLong();
> /** Name of index by two fields (orgId, salary). */
> public static final String ORG_SALARY_IDX = "ORG_SALARY_IDX";
> /** Person ID (indexed). */
> @QuerySqlField(index = true)
> public Long id;
> /** Organization ID (indexed). */
> @QuerySqlField(index = true, orderedGroups = @QuerySqlField.Group(name =
> ORG_SALARY_IDX, order = 0))
> public Long orgId;
> /** First name (not-indexed). */
> @QuerySqlField
> public String firstName;
> /** Last name (not indexed). */
> @QuerySqlField
> public String lastName;
> @QuerySqlField
> public LocalDateTime createdTime;
> /** Resume text (create LUCENE-based TEXT index for this field). */
> @QueryTextField
> public String resume;
> /** Salary (indexed). */
> @QuerySqlField(index = true, orderedGroups = @QuerySqlField.Group(name =
> ORG_SALARY_IDX, order = 1))
> public double salary;
> /** Custom cache key to guarantee that person is always colocated with
> its organization. */
> private transient AffinityKey<Long> key;
> /**
> * Default constructor.
> */
> public Person() {
> // No-op.
> }
> /**
> * Constructs person record.
> *
> * @param org Organization.
> * @param firstName First name.
> * @param lastName Last name.
> * @param salary Salary.
> * @param resume Resume text.
> */
> public Person(Organization org, String firstName, String lastName, double
> salary, String resume) {
> // Generate unique ID for this person.
> id = ID_GEN.incrementAndGet();
> orgId = org.id();
> this.firstName = firstName;
> this.lastName = lastName;
> this.createdTime = LocalDateTime.now();
> this.salary = salary;
> this.resume = resume;
> }
> /**
> * Constructs person record.
> *
> * @param id Person ID.
> * @param orgId Organization ID.
> * @param firstName First name.
> * @param lastName Last name.
> * @param salary Salary.
> * @param resume Resume text.
> */
> public Person(Long id, Long orgId, String firstName, String lastName,
> double salary, String resume) {
> this.id = id;
> this.orgId = orgId;
> this.firstName = firstName;
> this.lastName = lastName;
> this.createdTime = LocalDateTime.now();
> this.salary = salary;
> this.resume = resume;
> }
> /**
> * Constructs person record.
> *
> * @param id Person ID.
> * @param firstName First name.
> * @param lastName Last name.
> */
> public Person(Long id, String firstName, String lastName) {
> this.id = id;
> this.firstName = firstName;
> this.lastName = lastName;
> this.createdTime = LocalDateTime.now();
> }
> /**
> * Gets cache affinity key. Since in some examples person needs to be
> collocated with organization, we create
> * custom affinity key to guarantee this collocation.
> *
> * @return Custom affinity key to guarantee that person is always
> collocated with organization.
> */
> public AffinityKey<Long> key() {
> if (key == null)
> key = new AffinityKey<>(id, orgId);
> return key;
> }
> /**
> * {@inheritDoc}
> */
> @Override public String toString() {
> return "Person [id=" + id +
> ", orgId=" + orgId +
> ", lastName=" + lastName +
> ", firstName=" + firstName +
> ", createdTime=" + createdTime +
> ", salary=" + salary +
> ", resume=" + resume + ']';
> }
> } {code}
> DbH2ServerStartup.java:
>
>
> {code:java}
> package org.apache.ignite.examples.util;
> import java.io.IOException;
> import java.io.StringReader;
> import java.sql.SQLException;
> import org.apache.ignite.IgniteException;
> import org.h2.jdbcx.JdbcConnectionPool;
> import org.h2.tools.RunScript;
> import org.h2.tools.Server;
> /**
> * Start H2 database TCP server in order to access sample in-memory database
> from other processes.
> */
> public class DbH2ServerStartup {
> /** Create table script. */
> private static final String CREATE_PERSON_TABLE =
> "create table if not exists PERSON(id bigint AUTO_INCREMENT PRIMARY
> KEY, first_name varchar(50), last_name varchar(50), created_time TIMESTAMP
> DEFAULT CURRENT_TIMESTAMP);";
> /** Sample data script. */
> private static final String POPULATE_PERSON_TABLE =
> "delete from PERSON;\n" +
> "insert into PERSON(id, first_name, last_name) values(1, 'Johannes',
> 'Kepler');\n" +
> "insert into PERSON(id, first_name, last_name) values(2, 'Galileo',
> 'Galilei');\n" +
> "insert into PERSON(id, first_name, last_name) values(3, 'Henry',
> 'More');\n" +
> "insert into PERSON(id, first_name, last_name) values(4, 'Polish',
> 'Brethren');\n" +
> "insert into PERSON(id, first_name, last_name) values(5, 'Robert',
> 'Boyle');\n" +
> "insert into PERSON(id, first_name, last_name) values(6, 'Wilhelm',
> 'Leibniz');";
> /**
> * 初始化数据库
> *
> * @throws SQLException if
> */
> public static void populateDatabase() throws SQLException {
> // Try to connect to database TCP server.
> JdbcConnectionPool dataSrc =
> JdbcConnectionPool.create("jdbc:h2:tcp://localhost/mem:ExampleDb", "sa", "");
> // Create Person table in database.
> RunScript.execute(dataSrc.getConnection(), new
> StringReader(CREATE_PERSON_TABLE));
> // Populates Person table with sample data in database.
> RunScript.execute(dataSrc.getConnection(), new
> StringReader(POPULATE_PERSON_TABLE));
> }
> /**
> * Start H2 database TCP server.
> *
> * @param args Command line arguments, none required.
> * @throws IgniteException If start H2 database TCP server failed.
> */
> public static void main(String[] args) throws IgniteException {
> try {
> // Start H2 database TCP server in order to access sample
> in-memory database from other processes.
> Server.createTcpServer("-tcpDaemon").start();
> Server.createWebServer("-webDaemon").start();
> populateDatabase();
> }
> catch (SQLException e) {
> throw new IgniteException("Failed to start database TCP server",
> e);
> }
> try {
> do {
> System.out.println("Type 'q' and press 'Enter' to stop H2 TCP
> server...");
> }
> while ('q' != System.in.read());
> }
> catch (IOException ignored) {
> // No-op.
> }
> }
> }
> {code}
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
