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