First of all, I am quite new to Spring Boot, REST APIs, especially to apache ignite, so this may be a trivial problem, but I couldn't solve it for now.
I have an Oracle 12c database containing a few tables, the two in the question are:
create table la_license_count (
total_count number not null default 100,
active_count number not null default 0,
temporary_count number not null default 0,
id number not null
);
Column 'id' is the pimary key, besides this there is a check constraint and a unique index.
create table la_license_mapping (
client_id varchar2(200 byte) not null,
license_count number not null default 0,
license_type chat(1 byte) not null default 'F',
registered_at date not null default sysdate,
keepalive_time number,
id number not null;
);
Column 'id' is the pimary key, besides this there is a check constraint and a unique constraint.
A Spring Boot based REST API is required to read and update this table. An Apache Ignite cache layer needs to be between the REST API and the database. This was implemented as follows. The whole solution is based on this guide: https://dzone.com/articles/in-memory-data-grid-with-apache-ignite, with some changes based on other StackOverflow and various forum articles when it was not working.
Database bean (customized because password is encrypted in the logs in a custom way). DataSource itself works fine, tested that with by executing a CallableStatement with an autowired JdbcTemplate;
@Configuration
public class DatabaseBean {
@Autowired
private ConfigReader configReader;
@Bean(destroyMethod = "")
public DataSource dataSource() {
DatabaseSettings db = configReader.getDatabaseSettings();
return DataSourceBuilder
.create()
.url(db.getConnectionString())
.username(db.getUserName())
.password(db.isPasswordEncrypted() ? Encryptor.decrypt(db.getPassword()) : db.getPassword())
.driverClassName(db.getDriverClassName())
.build();
}
}
Entity classes:
public class LALicenseCount implements Serializable {
private static final long serialVersionUID = -1L;
private final AtomicLong ID_GEN = new AtomicLong();
private Long id;
private Long totalCount;
private Long activeCount;
private Long temporaryCount;
// getters, setters
public void init() {
this.id = ID_GEN.getAndIncrement();
}
// Various methods for business logic
}
public class LALicenseMapping implements Serializable {
private static final long serialVersionUID = -1L;
private final AtomicLong ID_GEN = new AtomicLong();
private Long id;
@QuerySqlField(index = false)
private String clientID;
private Long licenseCount;
@QuerySqlField(index = false)
private String licenseType;
private Date registeredAt;
private Long keepaliveTime;
// getters, setters
public void init() {
this.id = ID_GEN.getAndIncrement();
}
// Various methods for business logic
}
Repositories:
@Repository
@RepositoryConfig(cacheName = "LALicenseCountCache")
public interface LALicenseCountRepository extends IgniteRepository<LALicenseCount, Long> {
}
@Repository
@RepositoryConfig(cacheName = "LALicenseMappingCache")
public interface LALicenseMappingRepository extends IgniteRepository<LALicenseMapping, Long> {
List<LALicenseMapping> findByClientID(String clientID);
List<LALicenseMapping> findByClientIDAndLicenseType(String clientID, String licenseType);
}
Ignite bean:
@Component
public class IgniteConfigurationBean {
@Autowired
private ConfigReader configReader;
@Autowired
private ApplicationContext applicationContext;
@Bean
public Ignite igniteInstance() {
IgniteConfiguration cfg = new IgniteConfiguration();
cfg.setIgniteInstanceName("ignite-1");
cfg.setPeerClassLoadingEnabled(true);
CacheConfiguration<Long, LALicenseCount> ccfg = new CacheConfiguration<>("LALicenseCountCache");
ccfg.setIndexedTyped(Long.class, LALicenseCount.class);
ccfg.setWriteBehind(true);
ccfg.setReadThrough(true);
ccfg.setWriteThrough(true);
CacheJdbcPojoStoreFactory<Long, LALicenseCount> f = new CacheJdbcPojoStoreFactory<>();
f.setDataSourceBean("dataSource");
f.setDialect(new OracleDialect());
JdbcType t = new JdbcType();
t.setCacheName("LALicenseCountCache");
t.setKeyType(Long.class);
t.setValueType(LALicenseCount.class);
t.setDatabaseTable("la_license_count");
t.setDatabaseSchema(configReader.getDatabaseSettings().getUserName());
t.setKeyFields(new JdbcTypeField(Types.INTEGER, "id", Long.class, "id"));
t.setValueFields(
new JdbcTypeField(Types.INTEGER, "total_count", Long.class, "totalCount"),
new JdbcTypeField(Types.INTEGER, "active_count", Long.class, "activeCount"),
new JdbcTypeField(Types.INTEGER, "temporary_count", Long.class, "temporaryCount"));
f.setTypes(t);
ccfg.setCacheStoreFactory(f);
CacheConfiguration<Long, LALicenseMapping> ccfg2 = new CacheConfiguration<>("LALicenseMappingCache");
ccfg2.setIndexedTyped(Long.class, LALicenseMapping.class);
ccfg2.setWriteBehind(true);
ccfg2.setReadThrough(true);
ccfg2.setWriteThrough(true);
CacheJdbcPojoStoreFactory<Long, LALicenseMapping> f2 = new CacheJdbcPojoStoreFactory<>();
f2.setDataSourceBean("dataSource");
f2.setDialect(new OracleDialect());
JdbcType t2 = new JdbcType();
t2.setCacheName("LALicenseMappingCache");
t2.setKeyType(Long.class);
t2.setValueType(LALicenseCount.class);
t2.setDatabaseTable("la_license_mapping");
t2.setDatabaseSchema(configReader.getDatabaseSettings().getUserName());
t2.setKeyFields(new JdbcTypeField(Types.INTEGER, "id", Long.class, "id"));
t2.setValueFields(
new JdbcTypeField(Types.VARCHAR, "client_id", String.class, "clientID"),
new JdbcTypeField(Types.INTEGER, "license_count", Long.class, "licenseCount"),
new JdbcTypeField(Types.VARCHAR, "license_type", String.class, "licenseType"),
new JdbcTypeField(Types.DATE, "registered_at", java.sql.Date.class, "registeredAt"),
new JdbcTypeField(Types.INTEGER, "keepalive_time", Long.class, "keepaliveTime"));
f2.setTypes(t2);
ccfg2.setCacheStoreFactory(f2);
cfg.setCacheConfiguration(ccfg, ccfg2);
return IgniteSpring.start(cfg, applicationContext);
}
}
Main application class:
@SpringBootApplication
@EnableSwagger2
@ComponentScan(basePackages = {...})
@EnableIgniteRepositories("...")
public class Swagger2SpringBoot extends SpringBootServletInitializer {
@Override
protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
return application.sources(Swagger2SpringBoot.class);
}
public static void main(String[] args) throws Exception {
SpringApplication.run(Swagger2SpringBoot.class, args);
}
}
With this config, the application at least starts. I spent quite some time figuring how to setup the beans, the annotations, dependencies, etc. Now the way I use these:
@Service
public class LicenseManager {
@Autowired
private LALicenseCountRepository laLicenseCountRepository;
@Autowired
private LALicenseMappingRepository laLicenseMappingRepository;
@Autowired
private Ignite ignite;
private LALicenseCount licenseCount;
private IgniteCache<Long, LALicenseCount> laLicenseCountCache;
@PostConstruct
public void init() {
// There is only one LA_LICENSE_COUNT record always in the DB, and the ID is 1
licenseCount = laLicenseCountRepository.findOne(1L);
laLicenseCountCache = ignite.getOrCreateCache("LALicenseCountCache");
}
public Iterable<LALicenseMapping> getAllAllocatedLicenses() {
return laLicenseMappingRepository.findAll();
}
public void allocateNewLicense(String clientID) {
// prechecks
laLicenseCountCache.query(
new SqlFieldsQuery(
sqlReader.getLicense().getUpdateAllocatedCounts()
).setArgs(
licenseCount.getActiveCount(),
licenseCount.getTemporaryCount(),
1L
)
);
// remaining logic
}
}
Please spare the comments regarding the 1L constant in the code, this part will change, this code is just to see if I can read/write the database. So far, the getAllAllocatedLicenses() function seems to be working (no exception happens), I successfully receive an empty JSON array in the response. Problem is that I also receive an empty JSON array after I manually entered a record into the database table (not sure if Ignite should find this record, but I think yes).
Bigger problem is that the write operation fails. The SQL that should be executed comes from a properties file, and looks like this:
update la_license_count set active_count = ?, temporary_count = ? where id = ?
I receive the following stacktraces (only shown until the laLicenseCountCache.query line):
2018-07-11 17:40:33.211 [http-nio-9080-exec-8] INFO h.t.c.e.service.LicenseManager - 123456 - Processing new license allocation for client
2018-07-11 17:40:33.506 [http-nio-9080-exec-8] DEBUG h.t.c.e.service.LicenseManager - 123456 - Checks done, allocating new final license
2018-07-11 17:40:33.515 [http-nio-9080-exec-8] ERROR h.t.c.e.a.LAMiddlewareApiController - Unknown exception
javax.cache.CacheException: Failed to parse query. Table "LA_LICENSE_COUNT" not found; SQL statement:
update la_license_count set active_count = ?, temporary_count = ? where id = ? [42102-196]
at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615)
at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356)
at h.t.c.e.s.LicenseManager.allocateLicenseForCid(LicenseManager.java:127)
at h.t.c.e.s.a.LAMiddlewareApiController.lAMiddlewareLicensesCidGet(LAMiddlewareApiController.java:147)
...
Caused by: org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to parse query. Table "LA_LICENSE_COUNT" not found; SQL statement:
update la_license_count set active_count = ?, temporary_count = ? where id = ? [42102-196]
...
Caused by: org.h2.jdbc.JdbcSQLException: Table "LA_LICENSE_COUNT" not found; SQL statement:
update la_license_count set active_count = ?, temporary_count = ? where id = ? [42102-196]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.command.Parser.readTableOrView(Parser.java:5552)
at org.h2.command.Parser.readTableOrView(Parser.java:5529)
at org.h2.command.Parser.readSimpleTableFilter(Parser.java:796)
at org.h2.command.Parser.parseUpdate(Parser.java:739)
at org.h2.command.Parser.parsePrepared(Parser.java:471)
at org.h2.command.Parser.parse(Parser.java:321)
at org.h2.command.Parser.parse(Parser.java:293)
at org.h2.command.Parser.prepareCommand(Parser.java:258)
at org.h2.engine.Session.prepareLocal(Session.java:578)
at org.h2.engine.Session.prepareCommand(Session.java:519)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepare0(IgniteH2Indexing.java:484)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:452)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:419)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2008)
... 85 common frames omitted
Based on the last exception, it looks like it is trying to access the Oracle DB as if it were an H2 DB? Is that possible? I explicitly set OracleDialect for the Ignite, and Oracle JDBC driver is in the pom.xml file. Or maybe Ignite cannot connect to the DB? (I didn't find anything that would indicate this in the logs.)
What am I missing here?
org.h2.jdbc.JdbcSQLException: Table "LA_LICENSE_COUNT"not found; Check your datasource properties or active profile. - Anton NovopashinCacheJdbcPojoStorein case of using Oracle DB, MySql etc. - sk0x50