home.


Tagged: sqlite


Getting started with SQLite and Android

Here’s a code sample to quickly add and query an sqlite database. There are better ways. This is the basic.

Here’s the helper that creates your database, and optionally upgrades it:

private static class DatabaseHelper extends SQLiteOpenHelper {
  private static final String SQL_CREATE_ENTRIES =
          "CREATE TABLE whoopwhoop (" +
                  "_id INTEGER PRIMARY KEY NOT NULL," +
                  "t text NOT NULL)";
  public DatabaseHelper(Context context) {
    super(context, "yourdatabasename", null, 1);
  }
  @Override public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL(SQL_CREATE_ENTRIES);
  }
  @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {}
}

Next let’s use that to create the database, pass content values into it to insert and then query it.

SQLiteDatabase db = new DatabaseHelper(YOUR_APPLICATION_CONTEXT).getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("t", "woooooo");
long insert = db.insert("whoopwhoop", "null", cv);
Log.d("HIYA", ""+insert);

Here’s the code to query it. You should be using placeholder values in rawQuery to make it sql injection safe.

Cursor query = db.rawQuery("SELECT * FROM whoopwhoop", null);
query.moveToFirst();
Log.d("HIYA", ""+query.getInt(query.getColumnIndex("_id")));
Log.d("HIYA", query.getString(query.getColumnIndex("t")));
android android-sqlite android-database

Java: Testing a REST service with a clean database (using sqlite)

You can test REST responses like so with Jersey’s client api.

    YourResponseObject result = service
       .path("somepath")
       .type(MediaType.APPLICATION_JSON)
       .put(YourResponseObject.class, yourInputObject);

    assertTrue(result.isSuccessful());

But your responses may depend on the state of your database.

And since you’re not running your tests from a WAR, or what have you, you have no direct access to populate its seed or delete it.

The best way to do this is to create a rest method to clear the database to use during development, and remove in production.

The method to delete the database would look like:

    EntityTransaction trans = mEntityManager.getTransaction();
    trans.begin();
    Query q = mEntityManager.createQuery("delete from UserEntity");
    q.executeUpdate();
    trans.commit();
    closeEntityConnection();

It may be possible access the JPA database if the tests are run in a WAR, but I haven’t tried that. Any experience would be welcomed in the comments.

java REST java-testing jersey sqlite jpa

SQL: Renaming columns in sqlite

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

CREATE TABLE ANOTHERTHING (ID NUMBER(10) NOT NULL, TEXT VARCHAR, PRIMARY KEY (ID));

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;
sqlite sql sql-renamecolumns

Tomcat 7: JPA using EclipseLink and Sqlite

Download EclipseLink, the reference implementation for JPA, from http://www.eclipse.org/eclipselink/downloads/ and the sqlite jdbc library, from https://bitbucket.org/xerial/sqlite-jdbc/downloads.

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="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
      version="2.0"
      xmlns="http://java.sun.com/xml/ns/persistence">
    <persistence-unit name="example" transaction-type="RESOURCE_LOCAL">
      <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
      <class>org.denevell.tomcat.entities.write.AnotherThing</class>
      <properties>
        <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" />
      </properties>
    </persistence-unit>
 </persistence>

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

@Entity
public class AnotherThing {
  @Id @GeneratedValue
  private int id;
  private String text;
  public AnotherThing() {
  }
  public int getId() {
      return id;
  }
  public void setId(int id) {
      this.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();
t.setText("Heya");      
// Add it
EntityTransaction trans = em.getTransaction();
trans.begin();
em.persist(t);
trans.commit();
// 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
em.close();
factory.close();

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 jpql jpa tomcat sql sqlite

Tomcat 7: JDBC via JNDI using sqlite

First download the sqlite jdbc driver from https://bitbucket.org/xerial/sqlite-jdbc/downloads 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.

<Context>
    <Resource name="jdbc/sqlite"
    type="javax.sql.DataSource"
    driverClassName="org.sqlite.JDBC"
    url="jdbc:sqlite:/var/lib/tomcat7/dbs/test.db"
    />
</Context>

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

...
<resource-ref>
    <res-ref-name>jdbc/sqlite</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
</resource-ref>
...

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 (rs.next()) {
writer.println("id = " + rs.getInt(1));
} catch (Exception e) {
    writer.println(e.getMessage());
} finally {
    try {
        if (conn != null) conn.close();
    } catch (SQLException e) {
        writer.println(e);
    }
}
java tomcat-jndi tomcat-jdbc jdbc sql sqlite tomcat

Page 1 of 1