// 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();