Welcome to SeQuaLite!

SeQuaLite is a free, light-weight, java data access framework released under GPL. A proven framework, already used in many applications accross organizations. As SeQuaLite is released under GPL license, you're pretty much free to do whatever you want with it (even use it commercially).

Features

  • Supports C(INSERT)R(SELECT)U(UPDATE)D(DELETE) operations.
  • Supports Lazy-Loading of Objects in an Object Tree. Object can be created as a PROXY or as a NULL object and loaded later when required.
  • Supports save cascade and delete cascade operations.
  • SeQuaLite uses prepared statements for queries. So it's faster and secure. Avoid SQL injections by using SeQuaLite.
  • On the fly query generations.
  • Complex queries / DMLs can be created and used.
  • Supports Paginations. It's easy to created paginated reports.
  • Use sql operators or equaivalent operators in java. This reduces development time significantly. Please see the sample code section for examples.
  • Uses JDK Logging. All executed SQLs are logged if logLevel is set.
  • And many more.

Sample Code

You can browse through the included Sample Code to have a feel of how to use SeQuaLite.

Initialization and factory creation

// Setting Up SeQuaLite Configuration
SessionFactory factory = SessionFactory.getInstance("sequalite.properties");

Session creation

// Create SeQuaLite session,
// which is the facade to the data access layer
Session session = factory.createSession("mysql-prod");

Retrieving objects from database

// Find all the customer with id between 5 and 165
// Order by Last Name Ascending and
// First Name Descending. (Descending is default)
List customerList = session.find(Customer.class,"id>=? && id<=?",new Object[]{5,165}},"lastName ASC, firstName DESC"});


So the above call will translate into:

SELECT * FROM CUSTOMER WHERE ID >= ? AND ID <= ? ORDER BY LAST_NAME ASC, FIRST_NAME DESC

and the logger will print something like:

Aug 18, 2007 7:19:39 AM org.sequalite.dao.BaseDAO find
CONFIG: Executing sql: { SELECT * FROM CUSTOMER WHERE ID >= ? AND ID <= ? ORDER BY LAST_NAME ASC, FIRST_NAME DESC} with param(s): 1={5}, 2={165}


SeQuaLite will look up the table and column names for the java class and field names from the O-R Map (package.sequalite) and construct the query. As it uses prepared statements, the values will be set using preparedStatement.setObject(). By doing this, SeQuaLite avoids SQL injection threats.

Using pagination

// Find all the customer with id between 5 and 165
// Order by Last Name Ascending and
// First Name Descending. (Descending is default)
// Return 50 rows per page
SeQuaLitePageModel model = session.createPageModel(Customer.class,"id>=? && id<=?",new Object[]{5,165}},"lastName ASC, firstName DESC",50);
List list = model.getPage(1);
int pageCount = model.getPageCount();

Retrieving an object

// Retrieve Object with id=10
Customer customer = new Customer();
customer.setId(Long.valueOf(10));
session.realize(customer);

Saving object(s)

// Saving an Object
session.save(customer);
// Saving a List of Objects
session.save(customerList);
// Saving ObjectArray
session.save(customerArray);

Deleting object(s)

// Deleting an Object
session.delete(customer);
// Deleting a List of Objects
session.delete(customerList);
// Deleting ObjectArray
session.delete(customerArray);


Configuring SeQuaLite

As simple as can be

SeQuaLite has two main configuration files

  • sequalite.properties: Contains Logging, JDBC & package related information.
  • package.sequalite: The O-R Mapping file, which maps value objects to database tables. The name can be anything but the file should have a .sequalite extension.

POJO (Value Object) Generation

Use geno to generate value objects

SeQuaLite provides you a simple utility to generate value objects based on database tables

  • Update sequalite.properties

    #sample.jdbc.datasource=java:comp/env/jdbc/sample
    sample.jdbc.driver=org.gjt.mm.mysql.Driver
    sample.jdbc.url=jdbc:mysql://localhost:3306/test
    sample.jdbc.user=root
    sample.jdbc.password=admin
    sample.jdbc.autocommit=false
    sample.jdbc.table.list=all
    sample.pool.maxsize=50
    sample.pool.increment=5
    sample.pool.monitor.interval=5000
    sample.pojo.package=org.sequalite.sample.pojo
    sample.pojo.output.dir=C:/SFProjects/SeQuaLiteSampleApp/src

  • run geno with properties file

    c:\sequalite>geno
    -c sequalite.properties

  • run geno with existing .sequalite file

    c:\sequalite>geno
    -m package.sequalite
    -o C:/SFProjects/SeQuaLiteSampleApp/src