Tech notes for sql-sqlite



  1. Simple SQLite3 and NodeJS interactions
  2. SQL: Renaming columns in sqlite
  3. Tomcat 7: JPA using EclipseLink and Sqlite
  4. Tomcat 7: JDBC via JNDI using sqlite

Simple SQLite3 and NodeJS interactions

Firstly install sqlite3, npm -g install sqlite3 then:

const sqlite3 = require('sqlite3');
var yourdb = new sqlite3.Database('yourdb.sqlite3');
yourdb.all("select * from yourtable", [], (e, row) => {
nodejs sql-sqlite javascript

SQL: Renaming columns in sqlite

You can't easily do this in sqlite. Say you have a table with a schema:


And you want to rename the column 'text' to 'sometext', you need to make a new table with that schema, rename the current table, copy all the data over into the new table and then drop the renamed table:

alter table anotherthing rename to anotherthing_1;
create table anotherthing (id number(10) not null, sometext varchar, primary key (id));
insert into anotherthing(id, sometext) select id, text from anotherthing_1;
drop table anotherthing_1;
sql-sqlite sql sql-renamecolumns

Tomcat 7: JPA using EclipseLink and Sqlite

Download EclipseLink, the reference implementation for JPA, from and the sqlite jdbc library, from

Copy, eclipselink.jar, javax_persistence_2.x.x.jar and the sqlite jdbc jar file into your tomcat lib directory, /usr/share/tomcat7/lib in my case.

Now restart tomcat.

Create persistence.xml in src/META-INF/persistence.xml.

  <?xml version="1.0" encoding="UTF-8" ?>
  <persistence xmlns:xsi=""
    <persistence-unit name="example" transaction-type="RESOURCE_LOCAL">
        <property name="javax.persistence.jdbc.driver" value="org.sqlite.JDBC" />
        <property name="javax.persistence.jdbc.url" value="jdbc:sqlite:/var/lib/tomcat7/dbs/test.db" />
        <property name="eclipselink.logging.level" value="ALL" />
        <property name="eclipselink.ddl-generation" value="create-tables" />

The persistence-unit name will be how we will grab a hold of our persistence entity manager factory. The transaction-type say we're using a local database. If it said JTA it would mean we'd have support for transactions over multiple datasources (Tomcat doesn't support this out of the box).

We next tell it about the provider, EclipseLink, in our case. Then the class that will be persisted. Then we set properties to tell it about our driver, our jdbc url, the logging level and how we will generate the database.

We could set eclipselink.ddl-generation to 'drop-and-create-tables' if we want to destroy the database everytime. In a later tutorial we'll detail how to work with an existing database without automatically doing anything, since the eclipselink.ddl-generation is only really useful during development.

Here's what our 'AnotherThing' entity looks like:

package org.denevell.tomcat.entities.write; // Referenced in persistence.xml

public class AnotherThing {
  @Id @GeneratedValue
  private int id;
  private String text;
  public AnotherThing() {
  public int getId() {
      return id;
  public void setId(int id) { = id;
  public String getText() {
      return text;
  public void setText(String text) {
      this.text = text;

Note @Entity defines our class as just that, the @Id annotation says this is the primary key of the entity, and @GeneratedValue say the persistence provider generates its value.

We can now talk to our JPA instance:

// Setup the entity manager
EntityManagerFactory factory =   Persistence.createEntityManagerFactory("example");
EntityManager em = factory.createEntityManager();
// Create it
AnotherThing t = new AnotherThing();
// Add it
EntityTransaction trans = em.getTransaction();
// Fetch them
TypedQuery<AnotherThing> q = em.createQuery("select ting from AnotherThing ting", AnotherThing.class);
List<AnotherThing> results = q.getResultList();
for (AnotherThing thing : results) {
  writer.println(thing.getId() + ": " + thing.getText());
// Close the entity manager

Note we're using our name of the persistence-unit above to get the entity manager factory. Then we get the entity manager (we should only have one of these). Then we create a new object as normal.

We then start an entity transaction (we could use this to rollback if we wanted). Then we persist the object, committing the transaction.

Finally we use the JPQL syntax to get all the 'AnotherThing' objects in the database. Then we close the entity manager, and the entity manager factory.

I did get some error about AnotherThing not being recognised. Unfortunatley I think this is a bug in Tomcat for the most part. Stopping and then starting Tomcat should resolve the problem.

java java-jpa-jpql java-jpa java-tomcat sql-sqlite sql

Tomcat 7: JDBC via JNDI using sqlite

First download the sqlite jdbc driver from and copy it into your lib/ folder in your tomcat directory, /usr/share/tomcat7/lib/. Then restart tomcat so it can find your new jar.

Then add a reference to a new jdbc resource in your web/META-INF/context.xml file. It defines a JNDI name, its class type, the driver class name that relates to the jar we just installed above, and a url to connect to the database. In my case I'm pointing to a directory (which must exist) on the file system.

    <Resource name="jdbc/sqlite"

In addition, you have to add the resource in your web.xml file. Note the name is the same the name above.


Now you can make reference to the database in your Java code. This is lifted from the sqlite example page:

Connection conn = null;
try {
    Context ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sqlite");
    conn = ds.getConnection();
    Statement statement = conn.createStatement();
try {
    statement.executeUpdate("create table thing(x integer)");
} catch (Exception e) { 
     // Could well be already there
statement.executeUpdate("insert into thing values(42)");
ResultSet rs = statement.executeQuery("select * from thing");
while ( {
writer.println("id = " + rs.getInt(1));
} catch (Exception e) {
} finally {
    try {
        if (conn != null) conn.close();
    } catch (SQLException e) {
java java-jndi java-jdbc sql sql-sqlite java-tomcat

Page 1 of 1
Click me