JDBC: Data from external SQL database, e.g. SQLITE

Processing Libraries
1

For graphically representing Collatz-series distribution, I need selective access to a big DB (60M records+). Downloading an re-reading TABLE requires too much space (since user interaction should allow Mandelbrot-like zooming in).

So I tried to link my SQLITE3-DB with JDBC. Downloaded the driver, following the explanations on github: sqlite-jdbc-3.42.0.0.jar. But what to do with it, since processing an already packaged JAVA environment, so classpath extensions do not seem to be possible.

I work on W64, so by experimenting, I found a way: I copied it here:
c:.…\processing\core\library\windows-amd64\sqlite-jdbc-3.42.0.0.jar

Miraculously, processing takes it into account when launched. According to SQLITE doc, the jar contains an appropriate .dll for each environment (or other objects for Mac or Linux which are then dynamically copied to a temp directory.

QUESTION: Although this seems to work (see below test code), is it the proper way ? Also, I’d appreciate seeing some official documentation with examples, about integrating JDBC drivers or, more generally, external JARs.

Thanks to “moolder” who gave input on the old forum (but he does not seem to have been migrated to the new forum). His method does not work for me though, but his code was useful.

//JDBC example - no drawing at all, watch console.
//For syntax: See https://forum.processing.org/one/topic/including-an-external-jar

//Important: Copy sqlite-jdbc-3.42.0.0.jar here: 
// c:\...\processing\core\library\windows-amd64\sqlite-jdbc-3.42.0.0.jar

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

Connection colblu;

void setup() {
  size(400, 400);
  String url;

  url = "jdbc:sqlite:c:/Users/czell/sqlite3/colblu10k.db";

  try
  {

    colblu = DriverManager.getConnection(url);
    println("connection ok");

	String sql;
    sql = "select * from colblu where ele between 25 and 30 limit 10"; 
    //colblu above is the name of a table contained in the colblu10k database
    ResultSet rs;
    Statement s;
    s = colblu.createStatement();
    rs = s.executeQuery(sql);
    while (rs.next()) {
      println (rs.getString("ele") + " " + rs.getString("maxi"));
  }
    s.close();
  }
  catch (SQLException e) {
    println(e.getMessage());
  }
}

void draw() {
}
2

This doesn’t answer the question you are asking, but for SQLite (and MySQL) there is the library BezierSQLib by Florian Jenett. I’ve used it, works fine.

3

Thanks, Richard for your reply. Nice collection of utilities, indeed.
As you mention, it does not reply to my question, since the “bezierSQ”-utilities also use “my” sqlitejdbc.

In the meantime, I found a probably more appropriate location for it.

c:.…\processing\core\library\sqlite-jdbc-3.42.0.0.jar

It is from that directory that the other libraries are “classpathed” from, including core.jar as I checked with:

println(“” + System.getProperty(“java.class.path”));

Would be nice to see that in the reference documentation, for non-native Java-ists…