Usage

SQLite is serverless. This may sound harmless at first, indeed, SQLite seems to work just like MySQL or PostgreSQL when you wind up the JDBC driver for the first time. But there are some important differences you need to think about.

Hurry up and close your ResultSet!

Only one Connection can write to the database at a time. That's right, the entire database locks. Moreover, no write can finish while there is an open reader. The full and gory details are discussed on the locking page.
This means you need to close your ResultSet, fast.

Queries are cheap

Do a lot of queries. They are cheap. Tasks that you would do on a full DBMS with esoteric features such as cursors should be done on SQLite with multiple queries. A good example of this is scrolling cursors.

When in doubt, use PreparedStatement

This holds true for any database, but with SQLite, you get further benefits. When you call
PreparedStatement prep = conn.prepareStatement(
    "insert into mytable values (?);");
The SQL is parsed by SQLite into an intermediate form, waiting to be filled with a variable and executed. Reusing the PreparedStatement saves re-parsing the SQL.

Transactions are good

Almost all writes should be done in a transaction. It is good form, and if you are doing a large number of updates, it offers great speed benefits. This is slow:
PreparedStatement prep = conn.prepareStatement(
    "insert into mytable values (?);");
for (int i=0; i < 10000; i++) {
    prep.setInt(1, i);
    prep.executeUpdate();
}
This is fast:
PreparedStatement prep = conn.prepareStatement(
    "insert into mytable values (?);");
conn.setAutoCommit(false);
for (int i=0; i < 10000; i++) {
    prep.setInt(1, i);
    prep.executeUpdate();
}
conn.commit();
This can be further improved with batch routines, but the transaction is the most important thing!

Use the JDBC batch routines

JDBC provides many batch interfaces, allowing you to group tasks together. If you have a large set of statements to execute, batches will be slightly faster and much more organised:
PreparedStatement prep = conn.prepareStatement(
    "insert into mytable values (?);");
for (int i=0; i < 10000; i++) {
    prep.setInt(1, i);
    prep.addBatch();
}
conn.setAutoCommit(false);
prep.executeBatch();
conn.commit();
Easy.

Manifest Typing

To quote the SQLite documentation
In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing.)
This is important if you use JDBC meta data. The getColumnType() function will return the type associated with the value in the currently selected row. This is different from other JDBC drivers.