public static void main(String[] args) throws SqlJetException {
File dbFile = new File(DB_NAME);
dbFile.delete();
// create database, table and two indices:
SqlJetDb db = SqlJetDb.open(dbFile, true);
// set DB option that have to be set before running any transactions:
db.getOptions().setAutovacuum(true);
// set DB option that have to be set in a transaction:
db.runTransaction(new ISqlJetTransaction() {
public Object run(SqlJetDb db) throws SqlJetException {
db.getOptions().setUserVersion(1);
return true;
}
}, SqlJetTransactionMode.WRITE);
db.beginTransaction(SqlJetTransactionMode.WRITE);
try {
String createTableQuery = "CREATE TABLE " + TABLE_NAME + " (" + SECOND_NAME_FIELD + " TEXT NOT NULL PRIMARY KEY , " + FIRST_NAME_FIELD + " TEXT NOT NULL, " + DOB_FIELD + " INTEGER NOT NULL)";
String createFirstNameIndexQuery = "CREATE INDEX " + FULL_NAME_INDEX + " ON " + TABLE_NAME + "(" + FIRST_NAME_FIELD + "," + SECOND_NAME_FIELD + ")";
String createDateIndexQuery = "CREATE INDEX " + DOB_INDEX + " ON " + TABLE_NAME + "(" + DOB_FIELD + ")";
System.out.println();
System.out.println(">DB schema queries:");
System.out.println();
System.out.println(createTableQuery);
System.out.println(createFirstNameIndexQuery);
System.out.println(createDateIndexQuery);
db.createTable(createTableQuery);
db.createIndex(createFirstNameIndexQuery);
db.createIndex(createDateIndexQuery);
} finally {
db.commit();
}
// close DB and open it again (as part of example code)
db.close();
db = SqlJetDb.open(dbFile, true);
System.out.println();
System.out.println(">Database schema objects:");
System.out.println();
System.out.println(db.getSchema());
System.out.println(db.getOptions());
// insert rows:
db.beginTransaction(SqlJetTransactionMode.WRITE);
try {
Calendar calendar = Calendar.getInstance();
ISqlJetTable table = db.getTable(TABLE_NAME);
calendar.clear();
calendar.set(1981, 4, 19);
table.insert("Prochaskova", "Elena", calendar.getTimeInMillis());
calendar.set(1967, 5, 19);
table.insert("Scherbina", "Sergei", calendar.getTimeInMillis());
calendar.set(1987, 6, 19);
table.insert("Vadishev", "Semen", calendar.getTimeInMillis());
calendar.set(1982, 7, 19);
table.insert("Sinjushkin", "Alexander", calendar.getTimeInMillis());
calendar.set(1979, 8, 19);
table.insert("Stadnik", "Dmitry", calendar.getTimeInMillis());
calendar.set(1977, 9, 19);
table.insert("Kitaev", "Alexander", calendar.getTimeInMillis());
} finally {
db.commit();
}
ISqlJetTable table = db.getTable(TABLE_NAME);
// getting all rows in table, sorted by PK.
System.out.println();
System.out.println(">All employees in order defined by PK (" + table.getPrimaryKeyIndexName() + "):");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
try {
printRecords(table.order(table.getPrimaryKeyIndexName()));
} finally {
db.commit();
}
// getting all rows in table, sorted by PK.
System.out.println();
System.out.println(">All employees in order defined by " + DOB_INDEX + ", reversed:");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
try {
printRecords(table.order(table.getPrimaryKeyIndexName()).reverse());
} finally {
db.commit();
}
// getting all rows in table, sorted by index.
System.out.println();
System.out.println(">All employees in order defined by " + FULL_NAME_INDEX + " :");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
try {
printRecords(table.order(FULL_NAME_INDEX));
} finally {
db.commit();
}
// getting rows in table with exact indexed field value.
System.out.println();
System.out.println(">Alexanders:");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
try {
printRecords(table.lookup(FULL_NAME_INDEX, "Alexander"));
} finally {
db.commit();
}
// getting rows in table with indexed field value in certain scope.
System.out.println();
System.out.println(">Employees with full name in scope [B, I]:");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
try {
printRecords(table.scope(FULL_NAME_INDEX, new Object[] {"B"}, new Object[] {"I"}));
} finally {
db.commit();
}
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date(System.currentTimeMillis()));
calendar.add(Calendar.YEAR, -30);
System.out.println();
System.out.println(">Deleting rows of employees older than 30 years old.");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.WRITE);
try {
ISqlJetCursor deleteCursor = table.scope(DOB_INDEX,
new Object[] {Long.MIN_VALUE},
new Object[] {calendar.getTimeInMillis()});
while (!deleteCursor.eof()) {
System.out.println("Deleting: " +
deleteCursor.getRowId() + " : " +
deleteCursor.getString(FIRST_NAME_FIELD) + " " +
deleteCursor.getString(SECOND_NAME_FIELD) + " was born on " +
formatDate(deleteCursor.getInteger(DOB_FIELD)));
deleteCursor.delete();
}
deleteCursor.close();
} finally {
db.commit();
}
System.out.println();
System.out.println(">After deletion in row id order:");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
try {
printRecords(table.open());
} finally {
db.commit();
}
db.beginTransaction(SqlJetTransactionMode.WRITE);
ISqlJetCursor updateCursor = null;
try {
table.insert("Smith", "John", 0);
calendar.setTime(new Date(System.currentTimeMillis()));
updateCursor = table.open();
do {
updateCursor.update(updateCursor.getValue(SECOND_NAME_FIELD), updateCursor.getValue(FIRST_NAME_FIELD), calendar.getTimeInMillis());
} while(updateCursor.next());
} finally {
updateCursor.close();
db.commit();
}
System.out.println();
System.out.println(">After insertion of a new record and updating dates (by PK):");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
try {
printRecords(table.order(table.getPrimaryKeyIndexName()));
System.out.println();
System.out.println(">Same in order defined by " + FULL_NAME_INDEX + " :");
System.out.println();
printRecords(table.order(FULL_NAME_INDEX));
} finally {
db.commit();
}
System.out.println();
System.out.println(">Dropping tables and indices:");
System.out.println();
db.beginTransaction(SqlJetTransactionMode.WRITE);
try {
Set<String> tables = db.getSchema().getTableNames();
for (String tableName : tables) {
ISqlJetTableDef tableDef = db.getSchema().getTable(tableName);
Set<ISqlJetIndexDef> tableIndices = db.getSchema().getIndexes(tableDef.getName());
for (ISqlJetIndexDef indexDef : tableIndices) {
if (!indexDef.isImplicit()) {
System.out.println("dropping index: " + indexDef.getName());
db.dropIndex(indexDef.getName());
}
}
System.out.println("dropping table: " + tableDef.getName());
db.dropTable(tableDef.getName());
}
} finally {
db.commit();
}
db.close();
}