DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • Celesta: SQL Database APIs, Schema Migration, and Testing in a Single Java Library
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • The Magic of Apache Spark in Java
  • Apache Cassandra With Java: Introduction to UDT

Trending

  • Selecting the Right Automated Tests
  • What Is Kubernetes RBAC and Why Do You Need It?
  • Memory Management in Java: An Introduction
  • Top 7 Best Practices DevSecOps Team Must Implement in the CI/CD Process
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQLJet: Working With SQLite Databases in Pure Java

SQLJet: Working With SQLite Databases in Pure Java

Alexander Kitaev user avatar by
Alexander Kitaev
·
Sep. 16, 09 · Interview
Like (1)
Save
Tweet
Share
26.38K Views

Join the DZone community and get the full member experience.

Join For Free

SQLJet is an independent pure Java open source implementation of the SQLite RDBMS core functionality. SQLJet provides API to create, read and modify SQLite databases. Below you'll find simple tutorial demonstrating how one could use SQLJet Library to perform basic operations with the database in the SQLite format.

SQLJet first major version (1.0.0) has been just released. For more information and most recent updates please visit SQLJet home page at http://sqljet.com/.

Tutorial Contents

This is a very simple tutorial on how to use SQLJet API to work with the database in SQLite format. The following operations are described:

  • Create new database and configure options
  • Create table and indices
  • Insert records
  • Select records in order specified by index
  • Lookup records matching scope or exact value
  • Update and delete records
  • Drop table and index

Full working source code of this example is available as part of SQLJet project in Subversion repository at Tutorial.java.

Create new database

  File dbFile = new File(DB_NAME);  dbFile.delete();          SqlJetDb db = SqlJetDb.open(dbFile, true);  db.getOptions().setAutovacuum(true);  db.beginTransaction(SqlJetTransactionMode.WRITE);  try {    db.getOptions().setUserVersion(1);  } finally {    db.commit();  }

For the sake of atomicity this example always creates new empty data base (lines 1,2), then SqlJetDb object is created for that file (line 4), opened for writing. As file does not yet exist it will be created.

Sqlite format supports number of options. Some of these options have to be set before anything is changed in the database, even before first transaction is started (line 5), because exact way transaction is executed depends on these very options. Other options should be set inside "write" transaction (line 6).

There are basically two ways to execute certain code as a transaction. First is described above (lines 6 and 10) - write transaction is started and then committed. To roll back a transaction one should call db.rollback() instead of db.commit(), for instance in case exception is thrown from the try/catch block.

Another way is to subclass SqlJetTransaction class and run it with SqlJetDb.runTransaction(...) method:

 Object result = db.runTransaction(new ISqlJetTransaction() {   public Object run(SqlJetDb db) throws SqlJetException {     db.getOptions().setUserVersion(1);     return true;   } } SqlJetTransactionMode.WRITE);

Above method is more convenient in a sense that transaction will be automatically rolled back in case exception is thrown from the run method or committed in case there were no exceptions. On the other side usage of anonymous or inner classes might be inconvenient and then one could prefer the first way to run transaction. In this example we will use first way to save on indentation and curly brackets.

Note, that when you no longer need to work with the database it makes sense to close it by calling SqlJetDb.close() method:

  SqlJetDb db = SqlJetDb.open(dbFile, true);  try {    ...    ...  } finally {    db.close();  }

Create table and indices

We will create one table with three fields and two indices. Third index (for primary key field) will be created automatically. In SQLite format database schema is stored as plain SQL statements and similar statements are used to create tables and indices.

We are using the following statements:

CREATE TABLE employees (second_name TEXT NOT NULL PRIMARY KEY , first_name TEXT NOT NULL, date_of_birth INTEGER NOT NULL) CREATE INDEX full_name_index ON employees(first_name,second_name) CREATE INDEX dob_index ON employees(date_of_birth)

And the following code:

  db.beginTransaction(SqlJetTransactionMode.WRITE);  try {                db.createTable(createTableQuery);    db.createIndex(createFirstNameIndexQuery);    db.createIndex(createDateIndexQuery);  } finally {    db.commit();  }

First index, full_name_index is a composite one - it indexes rows by values of two fields - first_name and second_name. This means that searching using two values (first name and second name) will use this index and will work fast.

dob_index is a simple index of integer type field. SQLite "integer" is always represented as signed long in Java. Here we use long value type to store dates.

Finally, SQLJet will create one more index, because one of the table fields (second_name) is declared as PRIMARY KEY. This index will be names sqlite_autoindex_employees_1 and this name will be available later, so that we will use this index as well.

Note, that database schema is created in a write transaction.

Insert records

Now let fill our employees table we've just created:

  Calendar calendar = Calendar.getInstance();  calendar.clear();   db.beginTransaction(SqlJetTransactionMode.WRITE);  try {    ISqlJetTable table = db.getTable(TABLE_NAME);    calendar.set(1991, 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();  }

Code above is pretty straightforward: we fetch table by name (employees), then call table.insert(...) method passing values of all fields for each row. These fields are second_name, first_name and finally date_of_birth.

SQLJet updates indices automatically on any modifications done to the tables, so there is no need to call other methods.

Select records in order specified by index

Before looking at the code that selects records from the table, lets introduce utility method that simplifies displaying of those records. This method accepts ISqlJetCursor - object of iterator type that represents ordered set of rows - and prints out those rows:

  private static void printRecords(ISqlJetCursor cursor) throws SqlJetException {    try {      if (!cursor.eof()) {        do {          System.out.println(cursor.getRowId() + " : " +                              cursor.getString(FIRST_NAME_FIELD) + " " +                              cursor.getString(SECOND_NAME_FIELD) + " was born on " +                              formatDate(cursor.getInteger(DOB_FIELD)));         } while(cursor.next());      }    } finally {      cursor.close();    }  }

This utility method iterates over ordered row set using cursor.next() method until cursor points behind the last row in the ordered set - cursor.next() return false and cursor.eof() returns true.

At every particular moment of its lifetime, cursor points to one of the rows in the ordered set it represents (or to null row in case end of the ordered row set has been reached) and allows user to get fields values for the very row it points to. Additionally to the fields defined by schema every row has rowId - unique long integer which is, by default, equal to the row number (1-based).

When cursor is no longer needed, cursor.close() method will free associated resources and will make that cursor instance invalid.

Now, when utility method has been introduced it is easy to write code that selects records and print then out:

  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.order(table.getPrimaryKeyIndexName()));  } finally {    db.commit();  }

Method table.order(String indexName) returns all rows in the table in order defined by the index specified. In this case we use index that has been automatically created for the primary key field.

Note, that we run above code in a READ_ONLY transaction. This helps us to make sure that no concurrent write operation influence our row set.

Other examples on how rows might be selected:

  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.order(FULL_NAME_INDEX));  } finally {    db.commit();  }

- in order defined by a composite index, i.e. sorted by a concatenation of a first_name and second_name field values.

  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.open());  } finally {    db.commit();  }

- in order defined by the rowId, i.e. sorted in order rows were added to the table.

  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.order(DOB_INDEX).reverse());  } finally {    db.commit();  }

- from records with less value in date_of_birth field to those with greater values.

Note the use of ISqlJetCursor.reverse() method - it 'reverses' the cursor returning its mirrored copy that will iterate rows in the opposite order. 'Reversed' cursor wraps original cursor, so that later changes its position when former is iterated. It is enough to close reversed cursor to close original one as well.

Lookup records matching scope or exact value

Similar cursor-based approach is used to select only certain records - those that match certain criteria. The difference is that table.lookup(indexName, ...) method is used instead of table.order(indexName, ...).

table.lookup(...) method accepts index name and field values to select records. It is easy to understand this looking at examples:

  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.lookup(FULL_NAME_INDEX, "Alexander"));  } finally {    db.commit();  }

- gets all records with first part of full_name_index equal to 'Alexander'. This prints out two records:

6 : Alexander Kitaev was born on Oct 19, 19774 : Alexander Sinjushkin was born on Aug 19, 1982

And with stricter criteria:

  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.lookup(FULL_NAME_INDEX, "Alexander", "Kitaev"));  } finally {    db.commit();  }

- gets all records with both parts of full_name_index specified. This prints out single record:

6 : Alexander Kitaev was born on Oct 19, 1977

Note, that currently SQLJet only allows to search for a string (specifying beginning of it) using indices, not inside the string (specifying part of it or regular expression). This functionality will be available in the next versions of SQLJet.

Other way to select records matching criteria is to specify scope, not exact field values. It is possible to do with the help of table.scope(...) method that takes index name, range start and end values and returns our old friend cursor:

  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.scope(FULL_NAME_INDEX, new Object[] {"B"}, new Object[] {"I"})); } finally {    db.commit();  }

- prints all records with full_name_index (which is a composite of first_name and second_name) in range from B to I inclusive. Here it means that all employees with first name starting with letter B to I will be selected.

  Calendar calendar = Calendar.getInstance();  calendar.setTime(new Date(System.currentTimeMillis()));  calendar.add(Calendar.YEAR, -30);   db.beginTransaction(SqlJetTransactionMode.READ_ONLY);  try {    printRecords(table.scope(DOB_INDEX,                      new Object[] {Long.MIN_VALUE},                      new Object[] {calendar.getTimeInMillis()})); } finally {    db.commit(); }

- prints all records with date_of_birth value in scope between Long.MIN_VALUE and data thirty years ago from now.

Update and delete records

To modify (update) or delete records SQLJet uses the following algorithm:

  1. Start WRITE transaction.
  2. Select rows you'd like to modify or delete, in other words get a cursor.
  3. Iterate over cursor updating or deleting rows as you go.

Example below deletes records of all employees who are older than thirty years old (lines 08:14). Then it adds one more record (line 17)and changes date_of_birth field value for all records in the table (lines 19:28)):

  Calendar calendar = Calendar.getInstance();  calendar.setTime(new Date(System.currentTimeMillis()));  calendar.add(Calendar.YEAR, -30);  db.beginTransaction(SqlJetTransactionMode.WRITE);  try {    // delete    ISqlJetCursor deleteCursor = table.scope(DOB_INDEX,                                    new Object[] {Long.MIN_VALUE},                                    new Object[] {calendar.getTimeInMillis()});    while (!deleteCursor.eof()) {      deleteCursor.delete();    }    deleteCursor.close();    // insert    table.insert("Smith", "John", 0);    // update    calendar.setTime(new Date(System.currentTimeMillis()));    ISqlJetCursor updateCursor = table.open();    do {       updateCursor.update(             updateCursor.getValue(SECOND_NAME_FIELD),              updateCursor.getValue(FIRST_NAME_FIELD),              calendar.getTimeInMillis());    } while(updateCursor.next());    updateCursor.close();  } finally {    db.commit();  }

Code above is ran, of course, as a WRITE transaction and similar to table.insert(...), delete and update methods does all necessary updates to indices.

Drop table and indices

To drop (delete) table and indices use SqlJetDb.dropTable(String tableName) and SqlJetDb.dropIndex(String indexName) methods.

This is pretty clear and more interesting is how to figure out what tables and indices are contained in particular database. SQLJet provides an API to read database schema and fetching names of all tables and indices is easy:

  db.beginTransaction(SqlJetTransactionMode.WRITE);  try {         Set<String> indices = db.getSchema().getIndexNames();   Set<String> tables = db.getSchema().getTableNames();   for (String tableName : tables) {      ISqlJetTableDef tableDef = db.getSchema().getTable(tableName);      Set<ISqlJetIndexDef> tableIndices = db.getSchema().getIndexes(tableName);      for (ISqlJetIndexDef indexDef : tableIndices) {         if (!indexDef.isImplicit()) {           db.dropIndex(indexDef.getName());         }      }      db.dropTable(tableName);    }  } finally {    db.commit();  }

Above code gets names of all tables stored in the database and list of indices for each table, then drops those indices and tables. It is not necessary to drop first indices and then table - dropping table deletes indices automatically. ISqlJetTableDef and ISqlJetIndexDef objects provides detailed information of table and index including all names, fields and their types.

SQLJet first major version (1.0.0) has been just released. For more information and most recent updates please visit SQLJet home page at http://sqljet.com/.

In case you have any questions on this article or on SQLJet in general, please leave your comments here or contact us at support@sqljet.com.

Database SQLite sql Relational database Java (programming language) code style

Opinions expressed by DZone contributors are their own.

Related

  • Celesta: SQL Database APIs, Schema Migration, and Testing in a Single Java Library
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • The Magic of Apache Spark in Java
  • Apache Cassandra With Java: Introduction to UDT

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: