wiki:Devel/Index

Using SQLite database

UTGB Core supports SQLite3 database access. In the default setting, the database files within utgb-core/resource folder can be accessed from UTGB core.

After launching the GWTShell, try the following URL:

​http://localhost:8888/org.utgenome.gwt.utgb.UTGBResourceCenter/dispatcher?actionClass=org.utgenome.gwt.utgb.server.app.SQLiteAccessSample

The source code of this servlet is here: source:trunk/utgb/utgb-core/src/main/java/org/utgenome/gwt/utgb/server/app/SQLiteAccessSample.java

public class SQLiteAccessSample implements RequestHandler
{
    private static Logger _logger = Logger.getLogger(SQLiteAccessSample.class);
        
    public void handle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
        ArrayList<String> speciesList = new ArrayList<String>();
        try
        {
            // this will load database file from utgb-core/resource folder 
            SQLiteAccess sqlite = UTGB.getSQLiteAccess("legacy-track.db");
            List<String> queryResult = sqlite.query("select distinct species from tracks order by species", "species", String.class);
            _logger.debug("species: " + queryResult);
            speciesList.addAll(queryResult);
            
            response.getWriter().println(StringUtil.join(queryResult, ", "));
        }
        catch (DBException e)
        {
            _logger.error(e);
        }

    }

}

Simple SQL query processing with BeanUtil?

Prepare a bean class with appropriate getter/setters;

public class Person 
{
   int id;
   String name;

   public Person() {}
   
   public int getId() { return id; }
   public String getName() { return name; }
   
   public void setId(int id) { return id; }
   public void setName(String name) { return name; }
}

Sample Table data (person table):

person_id	name
1	leo
2	yui

Table data retrieval:

  SQLiteAccess sqlite = UTGB.getSQLiteAccess("sqlite3databasefile.db");
  List<Person> personList = sqlite.query("select person_id as id, name from person", Person.class);
  // personList contains Person(1, leo), Person(2, yui)  

Note that, you have to adjust the column name within the SQL statement in order to call correct setters in the Person class. For example, person_id column is renamed into id, and BeanUtil? library will invoke Person.setId(..) method.

SQLIte JDBC Driver

See ​http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC