JarCastingDynamic Orm
supported database
- Oracle
- Postgres
- MySQL
- MariaDB
- Hsql
- H2
- Derby
- Firebird
- SQLite
- MSSQL
- DB2
Features
- modify database structure on runtime (use Liquibase)
- create tables
- add/modify columns
- add/remove indexes
- add/remove foreign keys
- etc...
- crud operation on dynamic structures
- insert
- update
- delete (soft delete)
- support optimistic locking (Version column)
- quering to dynamic structures
- select
- CTE
- subqueries
- union
- join
- cache operation
- based on spring cache
- Transaction and External(ehcache, infinispan, redis, etc) cache
- cache queries based on Primary Key, Column, and Column and Values
- synchronization cache with crud operations
- support clustering( if use distributed cache)
- support create Sql sequence on runtime
- support create/update View on runtime
- save/load dynamic structure
- support Composite Primary key
dependencies
- querydsl - crud operation(insert, update, delete), querying (select, union, CTE)
- Spring transaction manager - transaction manager
- Spring cache abstraction - Cache abstraction
- Liquibase - support dynamic structure
- Spring beans - ioc container to connect all parts together
Installation
1. Maven
<dependencies>
<dependency>
<groupId>com.github.vzakharchenko</groupId>
<artifactId>dynamic-orm-core</artifactId>
<version>1.3.1</version>
</dependency>
</dependencies>
2. Spring Xml or Annotation
<!-- transaction Manager -->
<bean id="transactionManager" class="com.github.vzakharchenko.dynamic.orm.core.transaction.TransactionNameManager">
<property name="dataSource" ref="dataSource"/>
<property name="validateExistingTransaction" value="true"/>
</bean>
<!-- enable support annotation -->
<tx:annotation-driven transaction-manager="transactionManager"/>
<bean id="sharedTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
<constructor-arg name="transactionManager" ref="transactionManager"/>
<property name="isolationLevelName" value="ISOLATION_READ_COMMITTED"/>
<property name="timeout" value="30000"/>
</bean>
<bean name="springOrmQueryFactory" class="com.github.vzakharchenko.dynamic.orm.core.SpringOrmQueryFactory">
<property name="dataSource" ref="dataSource"/>
<property name="transactionCacheManager" ref="transaction-cache"/>
<property name="transactionalEventPublisher" ref="transaction-publisher"/>
<property name="transactionManager" ref="transactionManager"/>
</bean>
<!-- The main factory for building queries and data modification -->
<bean name="ormQueryFactory" factory-bean="springOrmQueryFactory" factory-method="getInstance"/>
<!-- Dynamic database supporting -->
<bean class="com.github.vzakharchenko.dynamic.orm.core.dynamic.QDynamicTableFactoryImpl">
<constructor-arg ref="dataSource"/>
</bean>
<!-- Transaction Event Manager-->
<bean id="transaction-publisher"
class="com.github.vzakharchenko.dynamic.orm.core.transaction.event.TransactionAwareApplicationEventPublisher"/>
<!-- Datasource - factory for connections to the physical data source -->
<bean id="dataSource" class="javax.sql.DataSource"
... />
</bean>
<!-- Spring Cache Abstraction Manager. You can use ehcache, Infinispan, Redis and etc... -->
<bean id="cacheManager" class="org.springframework.cache.concurrent.ConcurrentMapCacheManager"/>
<bean id="transaction-cache"
class="com.github.vzakharchenko.dynamic.orm.core.transaction.cache.TransactionCacheManagerImpl">
<constructor-arg name="targetCacheManager" ref="cacheManager"/>
</bean>
Or You can use Annotation:
@Configuration
@EnableTransactionManagement
@EnableCaching
public class SpringAnnotationTest extends CachingConfigurerSupport {
private TransactionNameManager transactionNameManager = new TransactionNameManager();
private DbStructureServiceImpl dbStructureService = new DbStructureServiceImpl();
TransactionAwareApplicationEventPublisher transactionAwareApplicationEventPublisher =
new TransactionAwareApplicationEventPublisher();
@Bean
public PlatformTransactionManager annotationDrivenTransactionManager() {
transactionNameManager.setDataSource(dataSource());
transactionNameManager.setValidateExistingTransaction(true);
return transactionNameManager;
}
@Bean
public DataSource dataSource() {
try {
return DataSourceHelper.getDataSourceHsqldbCreateSchema("jdbc:hsqldb:mem:DATABASE_MYSQL;sql.mys=true");
} catch (Exception e) {
throw new IllegalStateException(e);
}
}
@Bean
public TransactionTemplate sharedTransactionTemplate() {
TransactionTemplate transactionTemplate = new TransactionTemplate();
transactionTemplate.setTransactionManager(transactionNameManager);
transactionTemplate.setTimeout(3000);
transactionTemplate.setIsolationLevel(ISOLATION_READ_COMMITTED);
return transactionTemplate;
}
@Bean
public DbStructureService staticStructure() {
dbStructureService.setDataSource(dataSource());
dbStructureService.setPathToChangeSets("classpath:/changeSets/");
return dbStructureService;
}
@Bean()
public OrmQueryFactory ormQueryFactory() {
OrmQueryFactoryInit.create(dataSource())
.transactionCacheManager(new TransactionCacheManagerImpl(cacheManager()))
.transactionalEventPublisher(transactionAwareApplicationEventPublisher) // event publisher
.debug() // show all sql queries in logger
.cacheRegion("cache-orm") // cache region
.transactionManager(transactionNameManager)
.build();
}
@Bean
@Override
public CacheManager cacheManager() {
return new ConcurrentMapCacheManager();
}
@Bean
public TransactionalEventPublisher transactionalEventPublisher() {
return transactionAwareApplicationEventPublisher;
}
}
3. Example to Use
- autowire factories
@Autowired
private OrmQueryFactory ormQueryFactory;
@Autowired
private QDynamicTableFactory qDynamicTableFactory;
- add @Transactional annotation, or use transaction Manager
@Transactional()
public void testQuery() {
...
}
or
public void testQuery() {
TransactionBuilder transactionManager = ormQueryFactory.transactionManager();
transactionManager.startTransactionIfNeeded();
...
transactionManager.commit();
}
- create schema example
@Transactional()
public void testQuery() {
qDynamicTableFactory.buildTables("firstTable")
.columns().addStringColumn("Id")
.size(255).useAsPrimaryKey().createColumn()
.addStringColumn("TestStringColumn").size(255).createColumn()
.addDateColumn("modificationTime").createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(UUIDPKGenerator.getInstance())
.endPrimaryKey()
.addVersionColumn("modificationTime")
.endBuildTables().buildSchema();
}
- load dynamic structure from current connection
qDynamicTableFactory.loadCurrentSchema();
- save dynamic structure to file
File file = new File(".", "testSchema.json");
qDynamicTableFactory.saveSchema(SchemaUtils.getFileSaver(file));
- load dynamic structure from file
File file = new File(".", "testSchema.json");
qDynamicTableFactory.loadSchema(SchemaUtils.getFileLoader(file));
- get table metadata
QDynamicTable firstTable = qDynamicTableFactory.getQDynamicTableByName("firstTable");
- insert operation
DynamicTableModel firstTableModel1 = new DynamicTableModel(firstTable);
firstTableModel1.addColumnValue("TestStringColumn", "testValue");
ormQueryFactory.insert(firstTableModel1);
- modify table metadata
// add integer column to table
qDynamicTableFactory.buildTables("firstTable")
.columns().addNumberColumn("newColumn", Integer.class).createColumn().endColumns()
.endBuildTables().buildSchema();
- add custom column type
qDynamicTableFactory.buildTables("dynamicTestTable")
.columns()
.addCustomColumn("customColumn")
.column(Expressions::stringPath)
.jdbcType(new NVarcharType())
.createColumn()
.endColumns()
.endBuildTables().buildSchema();
- update operation
firstTableModel1.addColumnValue("newColumn", 122);
ormQueryFactory.updateById(firstTableModel1);
- fetch data
DynamicTableModel firstTableFromDatabase = ormQueryFactory.select().findOne(ormQueryFactory
.buildQuery()
.from(firstTable)
.where(firstTable.getNumberColumnByName("newColumn").eq(122)),
firstTable,
DynamicTableModel.class);
- fetch data with Wildcard
StringPath testColumn = dynamicTable.getStringColumnByName("TestColumn");
// fetch all data from all table
// if you want cache the result you can use selectCache() instead of select()
List<RawModel> rawModels = ormQueryFactory.select().rawSelect(
ormQueryFactory.buildQuery().from(dynamicTable)
.orderBy(testColumn.asc())).findAll(Wildcard.all);
RawModel rawModel = rawModels.get(0);
Object columnValue1 = rawModel.getValueByPosition(0);
Object columnValue2 = rawModel.getValueByPosition(1);
Object columnValue3 = rawModel.getValueByPosition(2);
- fetch data and put result to the cache. Cache record will be evicted if any related table is modified (insert/update/delete operartion)
DynamicTableModel firstTableFromDatabase = ormQueryFactory.selectCache().findOne(ormQueryFactory
.buildQuery()
.from(firstTable)
.where(firstTable.getNumberColumnByName("newColumn").eq(122)),
firstTable,
DynamicTableModel.class);
how it works:
// fetch data and put result to cache
DynamicTableModel firstTableFromDatabase = ormQueryFactory.selectCache().findOne(ormQueryFactory
.buildQuery()
.from(firstTable)
.where(firstTable.getNumberColumnByName("newColumn").eq(122)),
firstTable,
DynamicTableModel.class);
// fetch result from the cache
firstTableFromDatabase = ormQueryFactory.selectCache().findOne(ormQueryFactory
.buildQuery()
.from(firstTable)
.where(firstTable.getNumberColumnByName("newColumn").eq(122)),
firstTable,
DynamicTableModel.class);
// any "firstTable" modification will evict the query result from the cache
ormQueryFactory.insert(new DynamicTableModel(firstTable));
// fetch data and put result to the cache
DynamicTableModel firstTableFromDatabase = ormQueryFactory.selectCache().findOne(ormQueryFactory
.buildQuery()
.from(firstTable)
.where(firstTable.getNumberColumnByName("newColumn").eq(122)),
firstTable,
DynamicTableModel.class);
- limit and offset
ormQueryFactory.selectCache().findOne(ormQueryFactory
.buildQuery()
.from(firstTable).limit(3).offset(3)
.where(firstTable.getNumberColumnByName("newColumn").eq(122)),
firstTable,
DynamicTableModel.class);
- get column value from model
String testStringColumnValue = firstTableFromDatabase.getValue("TestStringColumn", String.class);
- join queries
// fetch data (if you want cache the result you can use selectCache() instead of select() )
List<RawModel> rawModels = ormQueryFactory.select().rawSelect(
ormQueryFactory.buildQuery().from(firstTable)
.innerJoin(secondTable).on(
secondTable.getStringColumnByName("linkToFirstTable").eq(
firstTable.getStringColumnByName("Id")))
.where(secondTable.getBooleanColumnByName("isDeleted").eq(false)))
.findAll(ArrayUtils.addAll(firstTable.all(), secondTable.all()));
RawModel rawModel = rawModels.get(0);
DynamicTableModel firstModelFromJoin = rawModel.getDynamicModel(firstTable);
DynamicTableModel secondModelFromJoin = rawModel.getDynamicModel(secondTable);
@Autowired
private OrmQueryFactory ormQueryFactory;
@Autowired
private QDynamicTableFactory qDynamicTableFactory;
// suspend the current transaction if one exists.
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public void testQuery() {
TransactionBuilder transactionManager = ormQueryFactory.transactionManager();
transactionManager.startTransactionIfNeeded();
// build schema
qDynamicTableFactory.buildTables("firstTable")
.columns().addStringColumn("Id")
.size(255).useAsPrimaryKey().createColumn()
.addStringColumn("TestStringColumn").size(255).createColumn()
.addDateColumn("modificationTime").createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(UUIDPKGenerator.getInstance())
.endPrimaryKey()
.addVersionColumn("modificationTime")
.buildNextTable("secondTable")
.columns().addStringColumn("Id")
.size(255).useAsPrimaryKey().createColumn()
.addBooleanColumn("isDeleted").notNull().createColumn()
.addDateTimeColumn("modificationTime").notNull().createColumn()
.addStringColumn("linkToFirstTable").size(255).createColumn()
.addStringColumn("uniqValue").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(UUIDPKGenerator.getInstance()).endPrimaryKey()
.addSoftDeleteColumn("isDeleted", true, false)
.addVersionColumn("modificationTime")
.index("uniqValue").addUniqueIndex()
.foreignKey("linkToFirstTable").addForeignKey(("firstTable")
.endBuildTables().buildSchema();
transactionManager.commit();
QDynamicTable firstTable = qDynamicTableFactory.getQDynamicTableByName("firstTable");
QDynamicTable secondTable = qDynamicTableFactory.getQDynamicTableByName("secondTable");
// insert data to the first table
transactionManager.startTransactionIfNeeded();
DynamicTableModel firstTableModel1 = new DynamicTableModel(firstTable);
firstTableModel1.addColumnValue("TestStringColumn", "testValue");
ormQueryFactory.insert(firstTableModel1);
// insert data to the second table
DynamicTableModel secondModel1 = new DynamicTableModel(secondTable);
secondModel1.addColumnValue("uniqValue", "123");
secondModel1.addColumnValue("linkToFirstTable", firstTableModel1.getValue("Id"));
DynamicTableModel secondModel2 = new DynamicTableModel(secondTable);
secondModel2.addColumnValue("uniqValue", "1234");
secondModel2.addColumnValue("linkToFirstTable", firstTableModel1.getValue("Id"));
ormQueryFactory.insert(secondModel1, secondModel2);
transactionManager.commit();
// add integer column to table1
transactionManager.startTransactionIfNeeded();
qDynamicTableFactory.buildTables("firstTable")
.columns().addNumberColumn("newColumn", Integer.class).createColumn().endColumns()
.endBuildTables().buildSchema();
transactionManager.commit();
// modify first table
transactionManager.startTransactionIfNeeded();
firstTableModel1.addColumnValue("newColumn", 122);
ormQueryFactory.updateById(firstTableModel1);
// select one value from firstTable where newColumn == 122
DynamicTableModel firstTableFromDatabase = ormQueryFactory.select().findOne(ormQueryFactory
.buildQuery()
.from(firstTable)
.where(firstTable.getNumberColumnByName("newColumn").eq(122)),
firstTable,
DynamicTableModel.class);
// get value of TestStringColumn from firstTable
String testStringColumnValue = firstTableFromDatabase.getValue("TestStringColumn", String.class);
assertEquals(testStringColumnValue, "testValue");
// get value from secondTable and put it to cache
List<DynamicTableModel> tableModels = ormQueryFactory.selectCache().findAll(secondTable);
assertEquals(tableModels.size(), 2);
transactionManager.commit();
// get value from cache
ormQueryFactory.selectCache().findAll(secondTable);
//soft delete the second row of the second Table
transactionManager.startTransactionIfNeeded();
ormQueryFactory.softDeleteById(secondModel2);
transactionManager.commit();
// get new cache records (soft deleted values are not included)
tableModels = ormQueryFactory.selectCache().findAll(secondTable);
assertEquals(tableModels.size(), 1);
// fetch all data from all table
// if you want cache the result you can use selectCache() instead of select()
List<RawModel> rawModels = ormQueryFactory.select().rawSelect(
ormQueryFactory.buildQuery().from(firstTable)
.innerJoin(secondTable).on(
secondTable.getStringColumnByName("linkToFirstTable").eq(
firstTable.getStringColumnByName("Id")))
.where(secondTable.getBooleanColumnByName("isDeleted").eq(false)))
.findAll(ArrayUtils.addAll(firstTable.all(), secondTable.all()));
assertEquals(rawModels.size(), 1);
RawModel rawModel = rawModels.get(0);
DynamicTableModel firstModelFromJoin = rawModel.getDynamicModel(firstTable);
DynamicTableModel secondModelFromJoin = rawModel.getDynamicModel(secondTable);
assertEquals(firstModelFromJoin.getValue("Id"), firstTableFromDatabase.getValue("Id"));
assertEquals(secondModelFromJoin.getValue("Id"), secondModel1.getValue("Id"));
}
SQL INDEX
create index on runtime
qDynamicTableFactory.buildTables("firstTable")
.columns().addStringColumn("Id")
.size(255).useAsPrimaryKey().createColumn()
.addStringColumn("column1").size(255).createColumn()
.addStringColumn("column2").size(255).createColumn()
.endColumns()
.index("column1","column2").addIndex()
.endBuildTables().buildSchema();
create unique index on runtime
qDynamicTableFactory.buildTables("firstTable")
.columns().addStringColumn("Id")
.size(255).useAsPrimaryKey().createColumn()
.addStringColumn("column1").size(255).createColumn()
.addStringColumn("column2").size(255).createColumn()
.endColumns()
.index("column1","column2").clustered().addUniqueIndex()
.endBuildTables().buildSchema();
drop index on runtime
// create schema
qDynamicTableFactory.buildTables("table1").columns()
.addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.addStringColumn("column1").size(255).createColumn()
.addStringColumn("column2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.index("column1", "column2").addIndex()
.endBuildTables().buildSchema();
// drop Index
qDynamicTableFactory.buildTables("table1")
.index("column1", "column2").drop()
.endBuildTables().buildSchema();
Foreign Key
create foreign key on runtime
qDynamicTableFactory.buildTables("table1").columns()
.addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.buildNextTable("table2")
.columns()
.addStringColumn("Id2").size(255).useAsPrimaryKey().createColumn()
.addStringColumn("Id1").size(255).notNull().createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.foreignKey("Id1").addForeignKey("table1")
.endBuildTables().buildSchema();
drop foreign key on runtime
// create table1 and table2
qDynamicTableFactory.buildTables("table1").columns()
.addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.buildNextTable("table2")
.columns()
.addStringColumn("Id2").size(255).useAsPrimaryKey().createColumn()
.addStringColumn("Id1").size(255).notNull().createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.foreignKey("Id1").addForeignKey("table1")
.endBuildTables().buildSchema();
QDynamicTable table1 = qDynamicTableFactory.getQDynamicTableByName("table1");
QDynamicTable table2 = qDynamicTableFactory.getQDynamicTableByName("table2");
// insert Table 1
DynamicTableModel dynamicTableModel1 = new DynamicTableModel(table1);
ormQueryFactory.insert(dynamicTableModel1);
// insert to table 2 with foreign Key
DynamicTableModel dynamicTableModel2 = new DynamicTableModel(table2);
dynamicTableModel2.addColumnValue("id1", dynamicTableModel1.getValue("Id1"));
ormQueryFactory.insert(dynamicTableModel2);
// drop foreign Key
qDynamicTableFactory.buildTables("table2")
.foreignKey("Id1").drop()
.endBuildTables().buildSchema();
// insert to table 2 with foreign Key
DynamicTableModel dynamicTableModel2WithoutForeign = new DynamicTableModel(table2);
dynamicTableModel2WithoutForeign.addColumnValue("id1", "Not Foreign Key Value");
ormQueryFactory.insert(dynamicTableModel2WithoutForeign);
Static Tables(not Dynamic)
- QueryDsl Models (Table Metadata)
@Generated("com.querydsl.query.sql.codegen.MetaDataSerializer")
public class QTestTableVersionAnnotation extends RelationalPathBase<QTestTableVersionAnnotation> {
public static final QTestTableVersionAnnotation qTestTableVersionAnnotation = new QTestTableVersionAnnotation("TEST_TABLE_VERSION_ANNOTATION");
public final NumberPath<Integer> id = createNumber("id", Integer.class);
public final NumberPath<Integer> version = createNumber("version", Integer.class);
public final PrimaryKey<QTestTableVersionAnnotation> idPk = createPrimaryKey(id);
public QTestTableVersionAnnotation(String variable) {
super(QTestTableVersionAnnotation.class, forVariable(variable), "", "TEST_TABLE_VERSION_ANNOTATION");
addMetadata();
}
public QTestTableVersionAnnotation(String variable, String schema, String table) {
super(QTestTableVersionAnnotation.class, forVariable(variable), schema, table);
addMetadata();
}
public QTestTableVersionAnnotation(Path<? extends QTestTableVersionAnnotation> path) {
super(path.getType(), path.getMetadata(), "", "TEST_TABLE_VERSION_ANNOTATION");
addMetadata();
}
public QTestTableVersionAnnotation(PathMetadata metadata) {
super(QTestTableVersionAnnotation.class, metadata, "", "TEST_TABLE_VERSION_ANNOTATION");
addMetadata();
}
public void addMetadata() {
addMetadata(id, ColumnMetadata.named("ID").withIndex(1).ofType(Types.INTEGER).withSize(38).notNull());
addMetadata(version, ColumnMetadata.named("VERSION").withIndex(2).ofType(Types.INTEGER).withSize(38).notNull());
}
}
- Static POJO Model
@QueryDslModel(qTableClass = QTestTableVersionAnnotation.class, tableName = "TEST_TABLE_VERSION_ANNOTATION", primaryKeyGenerator = PrimaryKeyGenerators.SEQUENCE)
@SequanceName("TEST_SEQUENCE")
public class TestTableVersionAnnotation implements DMLModel {
private Integer id;
@Version
private Integer version;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getVersion() {
return version;
}
public void setVersion(Integer version) {
this.version = version;
}
}
Annotations:
- @QueryDslModel - related QueryDsl model.
- qTableClass - queryDsl class
- tableName - Table name
- primaryKeyGenerator - Primary Key generator
- DEFAULT - does not use PK generator
- INTEGER - integer values
- LONG - long values
- UUID - Universally Unique Identifier values (UUID.randomUUID().toString())
- SEQUENCE - Sql Sequence (if database support)
- @SequanceName - Sequance annotation
- @Version - mark field as Optimistic locking. (Supports only TimeStamp and numeric column)
Example of Usage
- insert
TestTableVersionAnnotation testTableVersion = new TestTableVersionAnnotation();
ormQueryFactory.insert(testTableVersion);
- update
testTableVersion.setSomeColumn("testColumn")
ormQueryFactory.updateById(testTableVersion);
- select Version column and put result to cache
Integer version = ormQueryFactory.selectCache().findOne(
ormQueryFactory.buildQuery()
.from(QTestTableVersionAnnotation.qTestTableVersionAnnotation)
.where(QTestTableVersionAnnotation.qTestTableVersionAnnotation.id.eq(testTableVersion.getId()))
, QTestTableVersionAnnotation.qTestTableVersionAnnotation.version);
- join with dynamic table
TestTableVersionAnnotation staticTable = new TestTableVersionAnnotation();
ormQueryFactory.insert(staticTable);
// build dynamic Table with foreign Key to Static Table
qDynamicTableFactory.buildTables("relatedTable")
.columns().addStringColumn("Id").size(255).useAsPrimaryKey().createColumn()
.addNumberColumn("StaticId", Integer.class).createColumn()
.addDateTimeColumn("modificationTime").notNull().createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(UUIDPKGenerator.getInstance()).endPrimaryKey()
.addVersionColumn("modificationTime")
.foreignKey("StaticId").addForeignKey((QTestTableVersionAnnotation.qTestTableVersionAnnotation, QTestTableVersionAnnotation.qTestTableVersionAnnotation.id)
.endBuildTables().buildSchema();
// fetch dynamic table metadata
QDynamicTable relatedTable = qDynamicTableFactory.getQDynamicTableByName("relatedTable");
// insert to dynamic table
DynamicTableModel relatedTableData = new DynamicTableModel(relatedTable);
relatedTableData.addColumnValue("StaticId", staticTable.getId());
ormQueryFactory.insert(relatedTableData);
// fetch with join
// if you want cache the result you can use selectCache() instead of select()
DynamicTableModel tableModel = ormQueryFactory
.select()
.findOne(ormQueryFactory
.buildQuery().from(relatedTable)
.innerJoin(QTestTableVersionAnnotation.qTestTableVersionAnnotation)
.on(relatedTable
.getNumberColumnByName("StaticId", Integer.class)
.eq(QTestTableVersionAnnotation
.qTestTableVersionAnnotation.id))
.where(QTestTableVersionAnnotation
.qTestTableVersionAnnotation.id.eq(staticTable.getId())),
relatedTable);
assertNotNull(tableModel);
assertEquals(tableModel.getValue("Id"), relatedTableData.getValue("Id"));
- drop column
qDynamicTableFactory.buildTables("DynamicTable")
.columns()
.dropColumns("TestColumn")
.endColumns()
.endBuildTables().buildSchema();
- modify column
qDynamicTableFactory.buildTables("DynamicTable")
.columns()
.modifyColumn()
.size("TestColumn", 1)
.finish()
.endColumns()
.endBuildTables()
.buildSchema();
- drop table or View
qDynamicTableFactory
.dropTableOrView("TABLE_OR_VIEW_NAME").buildSchema();
- drop Sequence
qDynamicTableFactory
.dropSequence("sequence_name").buildSchema();
Generate QueryDslModel
pom.xml
<plugin>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-maven-plugin</artifactId>
<version>${querydsl}</version>
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>export</goal>
</goals>
</execution>
</executions>
<configuration>
<jdbcDriver>${driver}</jdbcDriver>
<beanPrefix>Q</beanPrefix>
<packageName>${QmodelPackage}</packageName>
<targetFolder>${targetFolder}</targetFolder>
<jdbcUrl>${jdbcUrl}</jdbcUrl>
<jdbcPassword>${jdbcPassword}</jdbcPassword>
<jdbcUser>${jdbcUser}</jdbcUser>
<sourceFolder />
</configuration>
</plugin>
Generate Static POJO Models
pom.xml
<build>
<plugins>
<plugin>
<groupId>com.github.vzakharchenko</groupId>
<artifactId>dynamic-orm-plugin</artifactId>
<version>1.3.1</version>
<configuration>
<targetQModelFolder>${targetFolder}</targetQModelFolder>
<modelPackage>${ModelPackage}</modelPackage>
<qmodelPackage>queryDsl package name</qmodelPackage>
</configuration>
<executions>
<execution>
<phase>process-sources</phase>
<goals>
<goal>modelGenerator</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
Audit database changes
@Component
public class LogAudit implements ApplicationListener<CacheEvent> {
@Override
public void onApplicationEvent(CacheEvent cacheEvent) {
switch (cacheEvent.cacheEventType()) {
case INSERT: {
for (Serializable pk : cacheEvent.getListIds()) {
System.out.println("insert table " + cacheEvent.getQTable().getTableName()
+ " primarykey = " + pk);
DiffColumnModel diffModel = cacheEvent.getDiffModel(pk);
for (Map.Entry<Path<?>, DiffColumn<?>> entry : diffModel.getDiffModels().entrySet()) {
System.out.println(" --- column " + ModelHelper.getColumnRealName(entry.getKey())
+ " set " + entry.getValue().getNewValue());
}
}
break;
}
case UPDATE: {
for (Serializable pk : cacheEvent.getListIds()) {
System.out.println("update table " + cacheEvent.getQTable().getTableName());
DiffColumnModel diffModel = cacheEvent.getDiffModel(pk);
for (Map.Entry<Path<?>, DiffColumn<?>> entry : diffModel.getOnlyChangedColumns().entrySet()) {
System.out.println(" --- column " + ModelHelper.getColumnRealName(entry.getKey())
+ " set " + entry.getValue().getNewValue()
+ " old value "
+ entry.getValue().getOldValue());
}
}
break;
}
case SOFT_DELETE:
case DELETE: {
System.out.println("delete into table " + cacheEvent.getQTable().getTableName() + " ids = " + ToStringBuilder.reflectionToString(cacheEvent.getListIds(), ToStringStyle.JSON_STYLE));
break;
}
case BATCH: {
List<? extends CacheEvent> transactionHistory = cacheEvent.getTransactionHistory();
for (CacheEvent event : transactionHistory) {
onApplicationEvent(event);
}
break;
}
default: {
throw new IllegalStateException(cacheEvent.cacheEventType() + " is not supported");
}
}
}
}
Create Dynamic Table With Sequence Primary Key Generator
qDynamicTableFactory.
.createSequence("dynamicTestTableSequance1")
.initialValue(1000L)
.increment(10L)
.min(1000L)
.max(10000L)
.addSequence()
.buildSchema();
Create SQL Sequence on runtime
qDynamicTableFactory.buildTables("dynamicTestTable")
.columns().addNumberColumn("ID", Integer.class).useAsPrimaryKey().createColumn()
.addStringColumn("testColumn").size(100).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(new PKGeneratorSequence("dynamicTestTableSequance1")).endPrimaryKey()
.endBuildTables()
.createSequence("dynamicTestTableSequance1")
.initialValue(1000L)
.addSequence()
.buildSchema();
Create SQL View on runtime
qDynamicTableFactory
.createView("testView").resultSet(ormQueryFactory.buildQuery()
.from(QTestTableVersionAnnotation.qTestTableVersionAnnotation), QTestTableVersionAnnotation.qTestTableVersionAnnotation.id)
.addView()
.buildSchema();
use SQL View
if you use selectcache() pay attention to the method "registerRelatedTables"
qDynamicTableFactory
.createView("testView").resultSet(ormQueryFactory.buildQuery()
.from(QTestTableVersionAnnotation.qTestTableVersionAnnotation), QTestTableVersionAnnotation.qTestTableVersionAnnotation.id).addView()
.buildSchema();
QDynamicTable testView = qDynamicTableFactory.getQDynamicTableByName("testView");
assertNotNull(testView);
TestTableVersionAnnotation testTableVersionAnnotation = new TestTableVersionAnnotation();
ormQueryFactory.insert(testTableVersionAnnotation);
// fetch data from table
// if you want cache the result you can use selectCache() instead of select()
TestTableVersionAnnotation versionAnnotation = ormQueryFactory.select()
.findOne(ormQueryFactory.buildQuery(), TestTableVersionAnnotation.class);
assertNotNull(versionAnnotation);
// fetch data from View
DynamicTableModel dynamicTableModel = ormQueryFactory.select()
.findOne(ormQueryFactory.buildQuery().from(testView), testView);
assertNotNull(dynamicTableModel);
// fetch data from View with cache (need manually register related tables with query)
DynamicTableModel dynamicTableModel2 = ormQueryFactory.selectCache().registerRelatedTables(
Collections.singletonList(QTestTableVersionAnnotation.qTestTableVersionAnnotation))
.findOne(ormQueryFactory.buildQuery().from(testView), testView);
assertNotNull(dynamicTableModel2);
SQL subquery (SQL query nested inside a larger query.)
// create database schema
qDynamicTableFactory.buildTables("UnionTable1")
.columns().addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime1").notNull().createColumn()
.addStringColumn("TestColumn1_1").size(255).createColumn()
.addStringColumn("TestColumn1_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime1")
.buildNextTable("UnionTable2")
.columns()
.addStringColumn("Id2").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime2").notNull().createColumn()
.addStringColumn("TestColumn2_1").size(255).createColumn()
.addStringColumn("TestColumn2_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime2")
.endBuildTables()
.buildSchema();
// get unionTable1 Metadata
QDynamicTable unionTable1 = qDynamicTableFactory.getQDynamicTableByName("UnionTable1");
// get unionTable2 Metadata
QDynamicTable unionTable2 = qDynamicTableFactory.getQDynamicTableByName("UnionTable2");
// get column from unionTable1
StringPath testColumn11 = unionTable1.getStringColumnByName("TestColumn1_1");
// get columns from unionTable2
StringPath testColumn21 = unionTable2.getStringColumnByName("TestColumn2_1");
StringPath testColumn22 = unionTable2.getStringColumnByName("TestColumn2_2");
// create subquery
SQLQuery<String> query = SQLExpressions
.select(testColumn21)
.from(unionTable2).where(testColumn22.eq("data2"));
// show the final SQL
String sql = ormQueryFactory.select().showSql(ormQueryFactory.buildQuery().from(unionTable1)
.where(testColumn11.in(query)), unionTable1);
assertEquals(sql, "select \"UNIONTABLE1\".\"ID1\", \"UNIONTABLE1\".\"MODIFICATIONTIME1\", \"UNIONTABLE1\".\"TESTCOLUMN1_1\", \"UNIONTABLE1\".\"TESTCOLUMN1_2\"\n" +
"from \"UNIONTABLE1\" \"UNIONTABLE1\"\n" +
"where \"UNIONTABLE1\".\"TESTCOLUMN1_1\" in (select \"UNIONTABLE2\".\"TESTCOLUMN2_1\"\n" +
"from \"UNIONTABLE2\" \"UNIONTABLE2\"\n" +
"where \"UNIONTABLE2\".\"TESTCOLUMN2_2\" = 'data2')");
// fetch data
// if you want cache the result you can use selectCache() instead of select()
DynamicTableModel tableModel = ormQueryFactory.select().findOne(
ormQueryFactory.buildQuery().from(unionTable1)
.where(testColumn11.in(query)), unionTable1);
Union query with groupBy, orderBy, offset and limit
// create database schema
qDynamicTableFactory.buildTables("UnionTable1")
.columns().addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime1").notNull().createColumn()
.addStringColumn("TestColumn1_1").size(255).createColumn()
.addStringColumn("TestColumn1_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime1")
.buildNextTable("UnionTable2")
.columns()
.addStringColumn("Id2").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime2").notNull().createColumn()
.addStringColumn("TestColumn2_1").size(255).createColumn()
.addStringColumn("TestColumn2_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime2")
.endBuildTables()
.buildSchema();
// get unionTable1 Metadata
QDynamicTable unionTable1 = qDynamicTableFactory.getQDynamicTableByName("UnionTable1");
// get unionTable2 Metadata
QDynamicTable unionTable2 = qDynamicTableFactory.getQDynamicTableByName("UnionTable2");
// get column from unionTable1
StringPath testColumn11 = unionTable1.getStringColumnByName("TestColumn1_1");
StringPath testColumn12 = unionTable1.getStringColumnByName("TestColumn1_2");
StringPath testColumn21 = unionTable2.getStringColumnByName("TestColumn2_1");
StringPath testColumn22 = unionTable2.getStringColumnByName("TestColumn2_2");
// first subquery
SQLQuery<Tuple> query1 = SQLExpressions
.select(testColumn11.as("column1"), testColumn12.as("column2"))
.from(unionTable1).where(testColumn12.eq("data1"));
// second subquery
SQLQuery<Tuple> query2 = SQLExpressions
.select(testColumn21.as("column1"), testColumn22.as("column2"))
.from(unionTable2).where(testColumn22.eq("data2"));
// create UnionBuilder
// if you want cache the result you can use selectCache() instead of select()
UnionBuilder unionBuilder = ormQueryFactory.select()
.unionAll(ormQueryFactory.buildQuery(), query1, query2);
// result order by
unionBuilder
.orderBy("column1").desc().orderBy("column2").asc();
// offset and limit (offset = 0, limit = 2 )
unionBuilder.limit(new Range(0, 2));
// group by result
unionBuilder.groupBy("column1", "column2");
// show final SQL
String sql = unionBuilder.showSql();
assertEquals(sql, "select \"column1\", \"column2\"\n" +
"from ((select \"UNIONTABLE1\".\"TESTCOLUMN1_1\" as \"column1\", \"UNIONTABLE1\".\"TESTCOLUMN1_2\" as \"column2\"\n" +
"from \"UNIONTABLE1\" \"UNIONTABLE1\"\n" +
"where \"UNIONTABLE1\".\"TESTCOLUMN1_2\" = 'data1')\n" +
"union all\n" +
"(select \"UNIONTABLE2\".\"TESTCOLUMN2_1\" as \"column1\", \"UNIONTABLE2\".\"TESTCOLUMN2_2\" as \"column2\"\n" +
"from \"UNIONTABLE2\" \"UNIONTABLE2\"\n" +
"where \"UNIONTABLE2\".\"TESTCOLUMN2_2\" = 'data2')) as \"union\"\n" +
"group by \"column1\", \"column2\"\n" +
"order by \"column1\" desc, \"column2\" asc\n" +
"limit 2\n" +
"offset 0");
// fetch result
List<RawModel> rawModels = unionBuilder.findAll();
// get first record
RawModel rawModel = rawModels.get(0);
// get column1 value
String column1Value = rawModel.getValueByColumnName("column1", String.class);
// get column2 value
String column2Value = rawModel.getValueByColumnName("column2", String.class);
Union count query with cache
// create database schema
qDynamicTableFactory.buildTables("UnionTable1")
.columns().addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime1").notNull().createColumn()
.addStringColumn("TestColumn1_1").size(255).createColumn()
.addStringColumn("TestColumn1_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime1")
.buildNextTable("UnionTable2")
.columns()
.addStringColumn("Id2").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime2").notNull().createColumn()
.addStringColumn("TestColumn2_1").size(255).createColumn()
.addStringColumn("TestColumn2_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime2")
.endBuildTables()
.buildSchema();
// get unionTable1 Metadata
QDynamicTable unionTable1 = qDynamicTableFactory.getQDynamicTableByName("UnionTable1");
// get unionTable2 Metadata
QDynamicTable unionTable2 = qDynamicTableFactory.getQDynamicTableByName("UnionTable2");
// get column from unionTable1
StringPath id1 = unionTable1.getStringColumnByName("Id1");
// get column from unionTable2
StringPath id2 = unionTable2.getStringColumnByName("Id2");
SQLQuery<String> query1 = SQLExpressions
.select(id1)
.from(unionTable1);
SQLQuery<String> query2 = SQLExpressions
.select(id2)
.from(unionTable2);
// create UnionBuilder
UnionBuilder unionBuilder = ormQueryFactory.selectCache()
.unionAll(ormQueryFactory.buildQuery(), query1, query2);
// unionBuilder.groupBy("column1", "column2");
// show final SQL
String sql = unionBuilder.showCountSql();
assertEquals(sql, "select count(*)\n" +
"from ((select \"UNIONTABLE1\".\"ID1\"\n" +
"from \"UNIONTABLE1\" \"UNIONTABLE1\")\n" +
"union all\n" +
"(select \"UNIONTABLE2\".\"ID2\"\n" +
"from \"UNIONTABLE2\" \"UNIONTABLE2\")) as \"union\"");
// fetch result
Long count1 = unionBuilder.count();
// result from cache
Long count2 = unionBuilder.count();
// insert to unionTable1
insert2("someData", "data3"); // ormQueryFactory.insert(unionTable2);
// cache is evicted and get a new value
Long count3 = unionBuilder.count();
CTE with Union query
// create database schema
qDynamicTableFactory.buildTables("UnionTable1")
.columns().addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime1").notNull().createColumn()
.addStringColumn("TestColumn1_1").size(255).createColumn()
.addStringColumn("TestColumn1_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime1)
.endBuildTables()
.buildSchema();
// get unionTable1 Metadata
QDynamicTable unionTable1 = qDynamicTableFactory.getQDynamicTableByName("UnionTable1");
// get column from unionTable1
StringPath testColumn11 = unionTable1.getStringColumnByName("TestColumn1_1");
StringPath testColumn12 = unionTable1.getStringColumnByName("TestColumn1_2");
SimplePath<String> column1 = Expressions.simplePath(String.class, "column1");
SimplePath<String> column2 = Expressions.simplePath(String.class, "column2");
// prepare with operator
SimplePath<Void> withSubquery = Expressions.path(Void.class, "CTE_SUBQUERY");
SQLQuery withQuery = (SQLQuery) ormQueryFactory.buildQuery().with(
withSubquery,
column1,
column2
).as(SQLExpressions
.select(testColumn11.as("column1"), testColumn12.as("column2"))
.from(unionTable1));
// first union subquery
SQLQuery<Tuple> query1 = SQLExpressions
.select(column1, column2)
.from(withSubquery).where(column2.eq("data1"));
// second union subquery
SQLQuery<Tuple> query2 = SQLExpressions
.select(column1, column2)
.from(withSubquery).where(column2.eq("data2"));
// create UnionBuilder
// if you want cache the result you can use selectCache() instead of select()
UnionBuilder unionBuilder = ormQueryFactory.select()
.unionAll(ormQueryFactory.buildQuery(), query1, query2);
// result order by
unionBuilder
.orderBy("column1").desc().orderBy("column2").asc();
// offset and limit (offset = 0, limit = 2 )
unionBuilder.limit(new Range(0, 2));
// group by result
unionBuilder.groupBy("column1", "column2");
// build union query with "with" operator
SQLQuery unionSubQuery = unionBuilder.getUnionSubQuery();
ProjectableSQLQuery sqlQuery = withQuery.select(column1, column2)
.from(unionSubQuery.select(column1, column2));
// show final SQL
assertEquals(ormQueryFactory.select().rawSelect(sqlQuery).showSql(column1, column2),
"with \"CTE_SUBQUERY\" (\"column1\", \"column2\") as (select \"UNIONTABLE1\".\"TESTCOLUMN1_1\" as \"column1\", \"UNIONTABLE1\".\"TESTCOLUMN1_2\" as \"column2\"\n" +
"from \"UNIONTABLE1\" \"UNIONTABLE1\")\n" +
"select \"column1\", \"column2\"\n" +
"from (select \"column1\", \"column2\"\n" +
"from ((select \"column1\", \"column2\"\n" +
"from \"CTE_SUBQUERY\"\n" +
"where \"column2\" = 'data1')\n" +
"union all\n" +
"(select \"column1\", \"column2\"\n" +
"from \"CTE_SUBQUERY\"\n" +
"where \"column2\" = 'data2')) as \"union\"\n" +
"group by \"column1\", \"column2\"\n" +
"order by \"column1\" desc, \"column2\" asc\n" +
"limit 2\n" +
"offset 0)");
// fetch data (if you want cache the result you can use selectCache() instead of select() )
List<RawModel> rawModels = ormQueryFactory.select().rawSelect(sqlQuery).findAll(column1, column2);
RawModel rawModel = rawModels.get(0);
String column1Value = rawModel.getColumnValue(column1);
String column2Value = rawModel.getColumnValue(column2);
count CTE operator (cacheable)
// create database schema
qDynamicTableFactory.buildTables("UnionTable1")
.columns().addStringColumn("Id1").size(255).useAsPrimaryKey().createColumn()
.addDateTimeColumn("modificationTime1").notNull().createColumn()
.addStringColumn("TestColumn1_1").size(255).createColumn()
.addStringColumn("TestColumn1_2").size(255).createColumn()
.endColumns()
.primaryKey().addPrimaryKeyGenerator(PrimaryKeyGenerators.UUID.getPkGenerator()).endPrimaryKey()
.addVersionColumn("modificationTime1)
.endBuildTables()
.buildSchema();
// get unionTable1 Metadata
QDynamicTable unionTable1 = qDynamicTableFactory.getQDynamicTableByName("UnionTable1");
// get column from unionTable1
StringPath testColumn11 = unionTable1.getStringColumnByName("TestColumn1_1");
StringPath testColumn12 = unionTable1.getStringColumnByName("TestColumn1_2");
SimplePath<String> column1 = Expressions.simplePath(String.class, "column1");
SimplePath<String> column2 = Expressions.simplePath(String.class, "column2");
SimplePath<Void> withSubquery = Expressions.path(Void.class, "CTE_SUBQUERY");
SQLQuery withQuery = (SQLQuery) ormQueryFactory.buildQuery().with(
withSubquery,
column1,
column2
).as(SQLExpressions
.select(testColumn11.as("column1"), testColumn12.as("column2"))
.from(unionTable1));
// first subquery
SQLQuery<Tuple> query1 = SQLExpressions
.select(column1, column2)
.from(withSubquery).where(column2.eq("data1"));
// second subquery
SQLQuery<Tuple> query2 = SQLExpressions
.select(column1, column2)
.from(withSubquery).where(column2.eq("data2"));
// create UnionBuilder
UnionBuilder unionBuilder = ormQueryFactory.select()
.unionAll(ormQueryFactory.buildQuery(), query1, query2);
// result order by
unionBuilder
.orderBy("column1").desc().orderBy("column2").asc();
// offset and limit (offset = 0, limit = 2 )
unionBuilder.limit(new Range(0, 4));
// group by result
unionBuilder.groupBy("column1", "column2");
SQLQuery unionSubQuery = unionBuilder.getUnionSubQuery();
ProjectableSQLQuery sqlQuery = withQuery.select(column1, column2)
.from(unionSubQuery.select(column1, column2));
assertEquals(ormQueryFactory.select().rawSelect(sqlQuery).showSql(Wildcard.count),
"with \"CTE_SUBQUERY\" (\"column1\", \"column2\") as (select \"UNIONTABLE1\".\"TESTCOLUMN1_1\" as \"column1\", \"UNIONTABLE1\".\"TESTCOLUMN1_2\" as \"column2\"\n" +
"from \"UNIONTABLE1\" \"UNIONTABLE1\")\n" +
"select count(*)\n" +
"from (select \"column1\", \"column2\"\n" +
"from ((select \"column1\", \"column2\"\n" +
"from \"CTE_SUBQUERY\"\n" +
"where \"column2\" = 'data1')\n" +
"union all\n" +
"(select \"column1\", \"column2\"\n" +
"from \"CTE_SUBQUERY\"\n" +
"where \"column2\" = 'data2')) as \"union\"\n" +
"group by \"column1\", \"column2\"\n" +
"order by \"column1\" desc, \"column2\" asc\n" +
"limit 4\n" +
"offset 0)");
//fetch data and put result to the cache
RawModel rawModel = ormQueryFactory.selectCache().rawSelect(sqlQuery).findOne(Wildcard.count);
Long countValue = rawModel.getAliasValue(Wildcard.count);
//fetch data from the cache
RawModel rawModelFromCache = ormQueryFactory.selectCache().rawSelect(sqlQuery).findOne(Wildcard.count);
Long countValueCache = rawModelFromCache.getAliasValue(Wildcard.count);
// insert to unionTable1
insert1("newValue", "data1"); // ormQueryFactory.insert(dynamicTableModel);
// cache is automatically evicted then get a new value and result put to the cache
RawModel rawModelAndPutNewCache = ormQueryFactory.selectCache().rawSelect(sqlQuery).findOne(Wildcard.count);
Long newCountValue = rawModelAndPutNewCache.getAliasValue(Wildcard.count);
Composite Primary key
// create Database schema
qDynamicTableFactory
.buildTables("testDynamicTableWithCompositeKey")
.columns().addNumberColumn("id1", Integer.class)
.useAsPrimaryKey().createColumn()
.addStringColumn("id2").size(255).useAsPrimaryKey().createColumn()
.addStringColumn("testColumn").size(255).createColumn()
.endColumns().endBuildTables().buildSchema();
// get dynamic table
QDynamicTable table = qDynamicTableFactory
.getQDynamicTableByName("testDynamicTableWithCompositeKey");
// insert Data
DynamicTableModel dynamicTableModel = new DynamicTableModel(table);
dynamicTableModel.addColumnValue("Id1", 1);
dynamicTableModel.addColumnValue("Id2", "2");
dynamicTableModel.addColumnValue("testColumn", "test");
ormQueryFactory.insert(dynamicTableModel);
List<DynamicTableModel> models = ormQueryFactory.selectCache().findAll(table);
assertNotNull(models);
assertEquals(models.size(), 1);
assertEquals(models.get(0).getValue("Id1", Integer.class), Integer.valueOf(1));
assertEquals(models.get(0).getValue("Id2", String.class), "2");
assertEquals(models.get(0).getValue("testColumn", String.class), "test");
remove column from the Composite key
// create Database schema
qDynamicTableFactory
.buildTables("testDynamicTableWithCompositeKey")
.columns().addNumberColumn("id1", Integer.class)
.useAsPrimaryKey().createColumn()
.addStringColumn("id2").size(255).useAsPrimaryKey().createColumn()
.addStringColumn("testColumn").size(255).createColumn()
.endColumns().endBuildTables().buildSchema();
// remove Id2 from primary key
qDynamicTableFactory
.buildTables(table.getTableName())
.primaryKey()
.removePrimaryKey("Id2")
.endPrimaryKey()
.endBuildTables().buildSchema();
// set column Id2 as nullable
qDynamicTableFactory
.buildTables(table.getTableName())
.columns().modifyColumn().nullable("Id2").finish().endColumns()
.endBuildTables().buildSchema();
// insert Data without Id2
DynamicTableModel dynamicTableModel1 = new DynamicTableModel(table);
dynamicTableModel1.addColumnValue("Id1", 1);
dynamicTableModel1.addColumnValue("testColumn", "test");
ormQueryFactory.insert(dynamicTableModel1);