Java and Sqlite

Why SQLite is so slow with JDBC?

You are create a String with 50k rows, this means you are creating 50k * 5 String (each concatenation create a new String instance. This is what kills your performance.

while (rs.next()) {
    script += "(";
    script += rs.getInt(1) + ", '" + rs.getString(2) + "', '" + rs.getString(3) + "'";
    script += "),";
}

I noticed that you don't excute the String script, so if you just want to create a String, use a StringBuilder

StringBuilder script = new StringBuilder("insert into table1 values ");
    while (rs.next()) {
        script.append("(")
              .append(rs.getInt(1)).append(", '")
              .append(rs.getString(2)).append("', '")
              .append(rs.getString(3)).append("'")
          .append("),");
    }

script.setLength(script.length() - 1); //to remove the last comma.

String query = script.toString();

The StringBuilder prevent the huge number of String instance created for nothing.

If you want to insert those values after that, use a PreparedStatement directly instead of building a query :

PreparedStatement psInsert = c1.prepareStatement("insert into table1 values (?,?,?)");
while (rs.next()) {
    psInsert.setInt(1, rs.getInt(1));
    psInsert.setString(2, rs.getString(2));
    psInsert.setString(2,rs.getString(3));

    psInsert.execute();
}

Then if you want to improve this, use the batch system to send small block of insert. Using Statement.addBatch() and Statement.executeBatch()

 while (rs.next()) {
    psInsert.setInt(1, rs.getInt(1));
    psInsert.setString(2, rs.getString(2));
    psInsert.setString(2,rs.getString(3));

    psInsert.addBatch();
    if(batchSize++ > 100){ //Execute every 100 rows
        psInsert.executeBatch();
        batchSize = 0;
    }
}

if(batchSize > 0){ //execute the remainings data
      psInsert.executeBatch();
}

StringBuilder Benchmark

Not an official one, just a Duration of a simple execution

LocalTime start = LocalTime.now();
StringBuilder sb = new StringBuilder("Foo;");
for(int i = 0; i < 50_000; i++){
    sb.append("Row").append(i).append(";\n");
}
System.out.println(Duration.between(start, LocalTime.now()).toNanos());
String s = sb.toString();
System.out.println(s.substring(0, 50));

This takes 15 nanoseconds

LocalTime start = LocalTime.now();
String s = "Foo;";
for(int i = 0; i < 50_000; i++){
    s += "Row" + i + ";\n";
}
System.out.println(Duration.between(start, LocalTime.now()).toMillis());
System.out.println(s.substring(0, 50));

This takes >6 seconds

Java and SQLite

The wiki lists some more wrappers:

• Java wrapper (around a SWIG interface): http://tk-software.home.comcast.net/
• A good tutorial to use JDBC driver for SQLite. (it works at least !) http://www.ci.uchicago.edu/wiki/bin/view/VDS/VDSDevelopment/UsingSQLite
• Cross-platform JDBC driver which uses embedded native SQLite libraries on Windows, Linux, OS X, and falls back to pure Java implementation on other OSes: https://github.com/xerial/sqlite-jdbc (formerly zentus)
• Another Java - SWIG wrapper. It only works on Win32. http://rodolfo_3.tripod.com/index.html
• sqlite-java-shell: 100% pure Java port of the sqlite3 commandline shell built with NestedVM. (This is not a JDBC driver).
• SQLite JDBC Driver for Mysaifu JVM: SQLite JDBC Driver for Mysaifu JVM and SQLite JNI Library for Windows (x86) and Linux (i386/PowerPC).

What is the simplest Java ORM supporting Sqlite?

This is self-marketing but my ORMLite package has native support for Sqlite – as well as MySQL, Postgres, Microsoft SQL Server, H2, Derby, and HSQLDB. It uses annotations to configure the persisted classes and is simple to get up to speed.

Here is the online docs on how to get started with the package:

I have experience with the org.xerial.sqlite-jdbc JDBC driver which seems to work very well.

Java JDBC on SQLite

The classpath separator is the colon (":"), not the semicolon. It's only a semicolon on windows, which is probably why the tutorial uses that. So, just update the classpath param to: -cp .:sqlite-jdbc-3.27.2.1jar :)

NB: sidenote, but, sqlite is a weird fit for java. something like h2 makes a lot more sense. SQLite gives virtually no benefits: You still need to ship an SQLite engine and launch it from within the process if you want it to feel lightweight, OR you need to explicitly ask the user to install the thing, at which point you might as well ask them to install a full featured DB like postgres. H2 is in-process, does not require you to ship one executable for each and every os/architecture combination you intend to support, and does not require messing about with trying to make the host OS launch native apps. It 'just works'. You should use sqlite only if you have a specific need for precisely sqlite and nothing else will do (for example, you're on android, which provides sqlite for you, or, you're trying to inspect or change firefox data storage, which are sqlite dbs).

Willena sqlite jdbc cannot open SqlCipher db

Ok, so I ended up finding the creator of the repository. And he solved it easily and answered really fast.

Here is the solution:
Here are a few things that could be tested:

1. Use version 3.31.1
2. Try to do the database connection using "jdbc:sqlite:file:C:\Users\User1\Desktop\test.db?cipher=sqlcipher&key=password123"as URI (notice the added "file:").
3. Try to add the legacy parameter for SQLCipher as available here (https://github.com/Willena/sqlite-jdbc-crypt#aes-256-bit-cbc---sha1sha256sha512-hmac-sqlcipher). The URI will become something like this: "cipher=sqlcipher&key=password123&legacy=4"

This is now working for me. I recommend that others use it if they are interested in an easy way to do sqlcipher version 4 similarly to how it is done in an android project.

How to identify exception with SQLite database query?

It is critical to properly interpret exception (i.e. error) details. If you cannot do that, then you cannot properly debug your code. Dumps of exception details can get messy, but here are the key points. Notice that the type of exception and the overall problem are described by

android.database.sqlite.SQLiteException: no such column: Joker (code 1)

SQLite is expecting to find a column called Joker. But why? To answer that, there is another clue in the exception details:

SELECT * FROM movie_favorites WHERE _id=Joker

The rest of the exception details are just about the code trace and are not helpful since you already know the code which has the problem. So let's look at the details of the SQL which caused the problem:

... WHERE _id=Joker

SQLite will interpret Joker as a column, because it is not wrapped in quotes like a string. But since it is not a column in the table, SQLite has no idea what Joker is. It doesn't know that you mean to match the movie name. Really, a string should be wrapped in quotes, something like 'Joker' so that SQLite knows that it is a string.

If we analyze it even further, we see that it is trying to compare a column named _id to the name of a movie. But most of the time (really all the time except is weird cases), a name like _id is an integer primary key. My guess is that this query is comparing the wrong column completely. Here is just a guess at what the proper SQL should look like... only a guess:

SELECT * FROM movie_favorites WHERE movie_title = 'Joker'

To get that result, you either need to fix the existing code to include the proper column and to wrap movie.getTitle() in quotes...

OR

Even better would be to use proper SQL query parameters (prepared statements) to avoid SQL injection and other bugs... like if the move title contains quotes. How to do that cannot be answered here since a full tutorial is beyond the scope of this question, but it can be discovered by searching elsewhere for "how to use sql parameters in java".

Once this bug is fixed, the button code may work just fine. But if it doesn't then you research that bug separately and possibly ask a new question.

---

Related Topics