[JAVA] Link Processing and SQLite

Summary of how to operate the database using SQLite from Processing3.

I refer to the following sites. http://mslabo.sakura.ne.jp/WordPress/make/processing%E3%80%80%E9%80%86%E5%BC%95%E3%81%8D%E3%83%AA%E3%83%95%E3%82%A1%E3%83%AC%E3%83%B3%E3%82%B9/sqlite%E3%82%92%E4%BD%BF%E3%81%86-db%E3%82%AA%E3%83%BC%E3%83%97%E3%83%B3/

Save sqlite-jdbc in the sketch folder.

First, open Processing and save the file. Then a folder of sketches is created. (This time, save it as DB_test_1.pde)

Next, save the JDBC required to operate SQLite from Processing in the sketch folder.

There are various JDBCs for each SQL language, but JDBC for SQLite can be obtained from the following site. https://github.com/xerial/sqlite-jdbc

For the obtained JDBC, create a new code folder in the sketch folder and save it in it.

This completes the preparations.

Write code using Java SQL package

Since Processing is a Java environment, Java packages can be used. When using SQL from Java, use the java.sql package.

This java.sql package contains classes that can be used to connect / disconnect to the DBMS (DataBase Management System) and send SQL statements.

This time, we will use the DriverManager class to prepare for connection to the DBMS, the Connection class to connect to the DBMS, and the Statement class to send SQL statements.

The final code is below.

DB_test_1.pde

import java.sql.*; //Package import
Connection connection = null; //Declare a class for connection

void setup(){
  //Specify the absolute path to generate the DB file in the sketch folder. If test.test when executing sketch if db does not exist.db is newly created
  String dbName = sketchPath("test.db");
  
  // OPEN DB
  try{
    connection = DriverManager.getConnection("jdbc:sqlite:" + dbName); 
    Statement statement = connection.createStatement();
    statement.setQueryTimeout(30); // set timeout to 30 sec
    
    statement.executeUpdate("drop table if exists person");
    //Send SQL statement to create a new Table
    statement.executeUpdate("create table person(id integer, name)");

    //Try inserting data into the created Table
    statement.executeUpdate("insert into person values(1, 'leo')");
    statement.executeUpdate("insert into person values(2, 'yui')");

    //Select all the contents of Table and load it into Resultset class
    ResultSet rs = statement.executeQuery("select * from person");
    while(rs.next()){ //Display the data read in ResultSet
      // read the result set
      String format = "name: %4s, id: %4d";
      println(String.format(format, rs.getString("name"), rs.getInt("id")));
      //print("name: " + rs.getString("name"));
      //println(", id: " + rs.getInt("id"));
    }
  } catch( SQLException e ){
    println(e.getMessage());
  } finally{
    dbClose();
  }
}

//A function that only summarizes exception handling at the time of database termination
void dbClose(){
  try{
    if(connection != null){
      connection.close();
    }
  } catch (SQLException e){
    e.printStackTrace();
  }
}

Running the above code will create a new test_db file and display the inserted data in the Processing console.

The above is a simple explanation of how to use the database.