Home Software Courses For Programmers Contact Us

Lisptorq 0.1.3 Overview

Lisptorq simplifies creating Java data objects. You specify a database schema and Lisptorq generates all Java source code for you! You use these data objects to read and write data to and from your database.

Lisptorq has only been tested on Derby, but should work for other databases with similar SQL syntax, with a little tweaking. We will discuss how to support other databases later in this article.

We've released Lisptorq under the GPL, but the generated code is entirely yours, to license however you like.

Lisptorq vs Torque

Apache's Torque product is a persistence layer. In addition to automatic code generation, it has (for example) facilities for producing IDs, among other things. It is written entirely in Java, and is a mature product supporting many databases.

Lisptorq is very much inspired by Torque, but is written entirely in (the Jlisp dialect of) LISP. Lisptorq aspires to be:

  1. An easy-to-use code generator, with which you could try out complicated database designs with relative ease. Apache Torque's XML database schema makes this more difficult to do.
  2. Free from external dependencies. Unlike Apache Torque, which requires external Torque classes, Lisptorq output is entirely self-contained. This makes Lisptorq's generated code more transparent.
  3. Easy to extend. If size is a measure of complexity, Lisptorq at 25KB (runtime + generator) would be less complicated than Apache's Torque at 140KB for the runtime and 180KB for the generator. The lower complexity makes Lisptorq easier to extend.

The last item is related to the fact that with LISP, manipulating objects with a hierarchical structure (like database schema) is much easier, compared to Java.

Download & Installation

To install, simply put lisptorq.jar and jlisp.jar in your classpath.

Using Lisptorq

Using Lisptorq to generate your Java data object classes is a simple 4 step process:

  1. Specify the database schema
  2. Specify settings for your database
  3. Generate the Java files
  4. Write your application

Step 1: Specifying the database schema

We will use the simple bookstore application database given in the Apache Torque's tutorial. The database has 3 tables: author, publisher and book. The first table has author information (first and last name). The second table contains publisher information (name). The third table contains book information (title, ISBN). The author id and publisher id will be foreign keys in the book table. The Lisptorq schema representation is as follows:

(import lisptorq.lisp)

define database    
(
    (author
        (author_id    (REQUIRED PRIMARY INTEGER AUTO))
        (first_name   (REQUIRED (VARCHAR 128)  ))
        (last_name    (REQUIRED (VARCHAR 128)  ))
    )

    (publisher
        (publisher_id (REQUIRED PRIMARY INTEGER AUTO))
        (name         (REQUIRED (VARCHAR 128)  ))
    )

    (book
        (book_id (REQUIRED PRIMARY INTEGER AUTO))
        (title   (REQUIRED (VARCHAR 255)  ))
        (ISBN    (REQUIRED (VARCHAR 24)   ))
        (publisher_id    (REQUIRED (FOREIGN publisher) INTEGER ))
        (author_id       (REQUIRED (FOREIGN author)    INTEGER ))
    )
)

You may want to compare this with Apache Torque's XML declaration file, which is much longer. This definition is really a LISP list, and we are really writing a LISP program. The top (import lisptorq.lisp) is a required declaration to import Lisptorq functions contained in lisptorq.jar. Like XML, the Lisptorq definition is also case sensitive.

The actual ordering of attributes for a column is irrelevant. For example, (book_id (REQUIRED PRIMARY INTEGER AUTO)) is equivalent to (book_id (INTEGER REQUIRED AUTO PRIMARY))

Step 2: Specifying the database settings

Next, you specify the database name and other vendor-specific settings. Derby for example, uses a "derby.home" property that must be set. We'll need to add these lines to our LISP program:


(lisptorq:set database-type   derby)
(lisptorq:set database-name   bookstore)
(lisptorq:set derby-home    ./bookstore)
(lisptorq:set package       com.mycompany.myapp)

The first 3 lines set the database type, name and the "derby.home" property (where the database files will be stored) respectively. The last line sets the package for the generated files.

Step 3: Generate the Java files

Finally, we need to put in a LISP command to generate the files: (lisptorq:generate database). Putting it all together, we get:

(import lisptorq.lisp)

define database    
(
    (author
        (author_id    (REQUIRED PRIMARY INTEGER AUTO))
        (first_name   (REQUIRED (VARCHAR 128)  ))
        (last_name    (REQUIRED (VARCHAR 128)  ))
    )

    (publisher
        (publisher_id (REQUIRED PRIMARY INTEGER AUTO))
        (name         (REQUIRED (VARCHAR 128)  ))
    )

    (book
        (book_id (REQUIRED PRIMARY INTEGER AUTO))
        (title   (REQUIRED (VARCHAR 255)  ))
        (ISBN    (REQUIRED (VARCHAR 24)   ))
        (publisher_id    (REQUIRED (FOREIGN publisher) INTEGER ))
        (author_id       (REQUIRED (FOREIGN author)    INTEGER ))
    )
) 


(lisptorq:set database-type   derby)
(lisptorq:set database-name   bookstore)
(lisptorq:set derby-home    ./bookstore)
(lisptorq:set package       com.mycompany.myapp)


(lisptorq:generate database)

To run this LISP program, you save it to a file (say main.lisp) and run java:

java -classpath jlisp.jar;lisptorq.jar;derby.jar net.thinksquared.jlisp.Loader main.lisp

Note that you have to include the Jar files required for your database driver in your classpath.

Lisptorq Output

There are few kinds of generated files:

  • DatabaseSetup contains code to set up your database (with "CREATE TABLE") for the first time.
  • Database initializes the database driver and contains static functions to dish out Connection objects. This class pools Connections.
  • Criteria helps you compose database queries without using SQL.
  • BaseXXXX classes for each table in your database. This class represents a database row, and has get/set methods for each column in the table.
  • BaseXXXXPeer classes for each table in your database. This class has methods to read and write data to/from the database.
  • XXXXPeer classes for each table in your database. These are subclasses of BaseXXXPeer, for you to insert additional functions, if you want.
  • XXXX classes for each table in your database. These are subclasses of BaseXXX, for you to insert additional functions, if you want.

Step 4: Writing your application

Creating the database tables

To create the tables on your database (if they don't exist) you have to run the DatabaseSetup program:

java -classpath jlisp.jar;lisptorq.jar;derby.jar com.mycompany.myapp.DatabaseSetup

Note that you have to include the Jar files required for your database driver in your classpath.

VERY IMPORTANT: If you are using a database other than Derby, then you will have to fill in the "TUDU" sections in the Database class. You'll have to specify the JDBC connection URL used by your database, among other things. Also, Lisptorq does not autogenerate IDs for you - instead, it relies on the database to do this for you. That's what the "AUTO" keyword in the database declaration is for. Lisptorq Collection only correctly does this for Derby. If you're using other databases, you may have to amend the "CREATE TABLE" SQL statements in DatabaseSetup yourself.

The data object classes

The data objects come in pairs of two: A XXXX and BaseXXXX classes to handle data access to a row, XXXXPeer and BaseXXXXPeer to handle persistence.

ONLY override the non-Base classes these will not be overwritten the next time you run Lisptorq. NEVER override the Base classes because they will be autogenerated each time you run Lisptorq.

Inserting Rows

Inserting rows with Lisptorq is easy. Simply instantiate a new data object, set its properties and then call save(). Note: if the primary key ID is "AUTO", then you needn't (actually, you can't) set the ID - this is done by the database. Otherwise, you will have to set the primary key ID too.

From our sample application:

        Publisher springer = new Publisher();
        springer.setName("Springer Verlag");
        springer.save();

        Author lasota = new Author();
        lasota.setFirstName("Andrzej");
        lasota.setLastName("Lasota");
        lasota.save();

The save() method invokes the doInsert() (or doUpdate()) method on the BaseXXXXPeer object. So, we could instead have done:

        Publisher springer = new Publisher();
        springer.setName("Springer Verlag");
        PublisherPeer.doInsert(springer);

Inserting a row into a table with a foreign key is also easy:

        Book chaos = new Book();
        chaos.setTitle("Chaos,Fractals and Noise");
        chaos.setISBN("3-540-94049-9");
        chaos.setPublisher(springer);
        chaos.setAuthor(lasota);
        chaos.save();

Or, you could do it "manually":

        Book chaos = new Book();
        chaos.setTitle("Chaos,Fractals and Noise");
        chaos.setISBN("3-540-94049-9");
        chaos.setPublisherId(springer.getPublisherId());
        chaos.setAuthorId(lasota.getAuthorId());
        chaos.save();

Selecting Rows

Selecting rows is easy too. The BaseXXXXPeer class has a doSelect() method that you use to get data from the database. doSelect() takes a Criteria as an argument, and returns an Iterator. For example, to get all the books from database:

        Criteria crit = new Criteria();
        Iterator books = BookPeer.doSelect(crit);

        Book book = (Book) books.next();
        System.out.println("Author:" + 
                            book.getAuthor().getFirstName() + "," + 
                            book.getAuthor().getLastName());

In the example, even though getAuthor() was called twice, only a single SQL query was generated. The Author object was cached after the first query.

NOTE: To display all books and their authors, we could simply iterate over all books, then call getAuthor() for each book. However, this is inefficient, since we would generate a SQL query for each getAuthor(). A better way is to use a database join. This feature has not yet been implemented in Lisptorq, but will be soon.

We can also select a specific book:

        Criteria crit = new Criteria();
        crit.add(Book.ISBN, "3-540-94049-9");
        Iterator books = BookPeer.doSelect(crit);

We show how to use the Criteria object in more detail later on in this article.

Scrolling

The return value from a doSelect() is actually a Scroller, which is an interface that extends Iterator. It has these additional functions:

  • hasPrevious() and previous() for moving backward through the result set.
  • absolute(int position) for moving the cursor anywhere within the result set.
  • size() which gives the size of the result set.

In this version of Lisptorq (0.1.3), size() is unimplemented, and will throw UnsupportedOperationException if called.

The following example lists the first 3 books in our database in reverse order:

        Scroller books = BookPeer.doSelect(new Criteria());
        books.absolute(4);
        while(books.hasPrevious()){
            Book b = (Book)books.previous();
            System.out.println(b.getTitle());
        }

[NOTE: The very name "Scroller" can be changed to something else if you wish, using the (lisptorq:set) function. For example, (lisptorq:set scroller-classname MyScroller) changes the name to "MyScroller"]

Updating Rows

To update a row, simply change the data object's properties with the "set" methods, then call save(), or call doUpdate() on the XXXXPeer object:

        chaos.setAuthor(mackay);
        chaos.save();

or,

        chaos.setAuthor(mackay);
        BookPeer.doUpdate(chaos);

Deleting Rows

Deleting rows is easy too. The XXXXPeer classes define static doDelete() methods, which accept either a Criteria or a data object to be deleted:

        BookPeer.doDelete(chaos);
        AuthorPeer.doDelete(lasota);
        PublisherPeer.doDelete(springer);

or,

        crit = new Criteria();
        crit.add(Book.ISBN,"3-540-94049-9");
        crit.add(Book.TITLE,"Chaos, Fractals and Noise");
        BookPeer.doDelete(crit);

        AuthorPeer.doDelete(lasota);
        PublisherPeer.doDelete(springer);

Note: Deleting a row from a table that has foreign keys does not automatically cause the foreign keys to be deleted from their tables. In the example, deleting the book did not delete "lasota" and "springer". We had to delete these "manually".

WARNING: doDelete(new Criteria()) will delete all rows from a table.

Transactions

For databases that support transactions, you can create transactions with your data objects:

            Connection conn = Database.getConnection();
            conn.setAutoCommit(false);

            Publisher springer = new Publisher();
            springer.setName("Springer Verlag");
            PublisherPeer.doInsert(springer,conn);
    
            Author lasota = new Author();
            lasota.setFirstName("Andrzej");
            lasota.setLastName("Lasota");
            AuthorPeer.doInsert(lasota,conn);

            Book chaos = new Book();
            chaos.setTitle("Chaos,Fractals and Noise");
            chaos.setISBN("3-540-94049-9");
            chaos.setPublisher(springer);
            chaos.setAuthor(lasota);
            BookPeer.doInsert(chaos,conn);

            conn.commit();

            /* 
               Send the connection back to the pool.
               release() also calls setAutoCommit(true);     
            */
            Database.release(conn);

Customizing the Object Model

To add functionality to the object model, simply add methods to any data model class (Book, Author, Publisher) or the even their peers (BookPeer, AuthorPeer, PublisherPeer). None of these are re-generated when you again run Lisptorq.

IMPORTANT: The BaseXXXX and BaseXXXXPeer classes are always re-generated, so never add functions to them.

As an example, you might want to implement an equals() and the toString() function on the data objects:

        package com.mycompany.myapp;
        import java.sql.*;
        import java.util.Iterator;
        public Publisher extends BasePublisher{

            public boolean equals(Object obj){
                try{
                    Publisher p = (Publisher) obj;
                    return (p.getPublisherId() == publisherId && p.getName().equals(name));
                }catch(Exception e){
                    return false;
                }
            }

            public String toString(){
                return getName();
            }
        }

Or, you might want to implement a selectAll() function on the Peers:

        package com.mycompany.myapp;
        import java.sql.*;
        import java.util.Iterator;
        public PublisherPeer extends BasePublisherPeer{

            public static Iterator selectAll() throws Exception{
                return doSelect(new Criteria());
            }
        }

The Full Application

        package com.mycompany.myapp;
        import java.sql.*;
        import java.util.Iterator;
        public class MyApplication{

            public static void main(String[] args) throws Exception{
                                
                Publisher springer = new Publisher();
                springer.setName("Springer Verlag");
                springer.save();
        
                Author lasota = new Author();
                lasota.setFirstName("Andrzej");
                lasota.setLastName("Lasota");
                lasota.save();    

                Book chaos = new Book();
                chaos.setTitle("Chaos,Fractals and Noise");
                chaos.setISBN("3-540-94049-9");
                chaos.setPublisher(springer);
                chaos.setAuthor(lasota);
                chaos.save();
                  
                /*
                    Print the publisher list
                */        
                for(Iterator pp = PublisherPeer.selectAll(); pp.hasNext();){
                    System.out.println(pp.next());
                }
            }
        }

Advanced Criteria Techniques

Building complex SQL queries

An example of a complex SQL query and the code to build it:

        select * from abc where (a < 1 and b > 2) or (a > 5 and b < 3)

        Criteria crit = new Criteria();
        Criteria.Criterion c1 = new Criteria.Criterion(ABC.A,1,Criteria.LESS);
        Criteria.Criterion c2 = new Criteria.Criterion(ABC.B,2,Criteria.GREATER);
        Criteria.Criterion c3 = new Criteria.Criterion(ABC.A,5,Criteria.GREATER);
        Criteria.Criterion c4 = new Criteria.Criterion(ABC.B,3,Criteria.LESS);

        crit.add(c1.and(c2).or(c3.and(c4)));

Comparators in Lisptorq

The Criteria object has the following comparators:

         Criteria.CUSTOM
         Criteria.EQUAL
         Criteria.NOT_EQUAL
         Criteria.GREATER
         Criteria.GREATER_EQUAL
         Criteria.LESS
         Criteria.LESS_EQUAL
         Criteria.IN
         Criteria.NOT_IN         
         Criteria.JOIN
         Criteria.LIKE

You should use Criteria.CUSTOM for comparisons not included in the above list, like "NOT NULL":

        Criteria crit = new Criteria();
        crit.add(Book.NAME,"NOT NULL",Criteria.CUSTOM);
        Iterator books = BookPeer.doSelect(crit);

Using Criteria to create joins

In many applications, we need to form a many to many relationship between two entities. Consider for example, a "Role" table that has assigned to it permissions from a "Permissions" table. The n:m assignment is done through a junction entity called "RolePermission", which is essentially a list of Role_IDs and corresponding Permission_IDs:

        define database    
        (
            (role
                (role_id    (REQUIRED PRIMARY INTEGER AUTO))
                (name       (REQUIRED (VARCHAR 128)  ))
            )
        
            (permission
                (permission_id    (REQUIRED PRIMARY INTEGER AUTO))
                (name             (REQUIRED (VARCHAR 128)  ))
            )
        
            (role_permission
                (role_id            (REQUIRED PRIMARY INTEGER (FOREIGN role) ))
                (permission_id      (REQUIRED PRIMARY INTEGER (FOREIGN permission) ))
            )
        )

Here's how to list all Roles having Permissions:

        Criteria crit = new Criteria();
        crit.addJoin(Permission.PERMISSION_ID,RolePermission.PERMISSION_ID);
        crit.addJoin(RolePermission.ROLE_ID,Role.ROLE_ID);

        crit.setDistinct();
        crit.addAscendingOrder(Role.NAME);
        
        Iterator roles = RolePeer.doSelect(crit);

To select all Permissions having Roles, you flip the arguments in the "addJoin"s:

        Criteria crit = new Criteria();
        crit.addJoin(RolePermission.PERMISSION_ID,Permission.PERMISSION_ID);
        crit.addJoin(Role.ROLE_ID,RolePermission.ROLE_ID);

        crit.setDistinct();
        crit.addAscendingOrder(Permission.NAME);
        
        Iterator roles = PermissionPeer.doSelect(crit);

Database descriptor details

The database descriptor is a collection of tables:

database := (<table>+)

Each table has a name, followed by a set of fields:

<table>  := (<table-name> <field>+)

Each field is a name followed by an attribute list: 

<field> := (<field-name> (<attribute>+))

The attribute list may contain any combination of the following. Order is UNimportant:

        type := [BIGINT | BLOB | CHAR | (VARCHAR <length>) 
                 CLOB | DATE | DOUBLE | INTEGER | LONG VARCHAR | 
                 REAL | SMALLINT | TIME | TIMESTAMP ]

        autoincrement := (AUTO <start> <end>) or just AUTO for (AUTO 1 1)

        required field := REQUIRED

        primary key := PRIMARY

        foreign key := (FOREIGN <table-name> <foreign-field-name>) 
                    or (FOREIGN <table-name>) 

        default := (DEFAULT <value>)

WARNING: Support for CLOB, BLOB, DATE, TIME and TIMESTAMP is currently poor, and the corresponding functionality undergoing active development.

Tudu List

  • Better support for CLOB, BLOB, DATE, TIME and TIMESTAMP.
  • doJoinXXXX() functions on BaseXXXXPeers.
  • Support for UNIQUE as a column attribute.

Participate!

If you'd like to help or if you've spotted a bug, just drop us a note at: devzone(at)thinksquared.net. We welcome your participation!

Change Log

Changes from 0.1 to 0.1.1

  • Default String values (CHAR, VARCHAR, LONG VARCHAR) now put in correctly
  • escape(null)in Criteria gives " NULL ".
  • Added doSelect(sql,recycle) and doSelect(criteria, recycle) to the BaseXXXXPeer classes. Using recycle=true causes the returned value from the Iterator to be reused ("recycled") after each call of next() on the Iterator. So, do not save copies of the returned data object if you're calling doSelect(.,true).

Changes from 0.1.1 to 0.1.2

  • Return value from a doSelect() is a Scroller. This allows back/forward scrolling and absolute positioning and size determination. Scroller is an interface that extends Iterator, so there's complete backward compatability. The functions available on Scroller are: previous(), hasPrevious(), absolute() and size(), besides those on Iterator. The last two are optional, and Lisptorq does not currrently implement size(). Currently, an UnsupportedOperationException is thrown if size() is called.
  • Constructor for Database removed, and content put into a static{} block. This obviates the need to create an instance of Database to initialize the system.

Changes from 0.1.2 to 0.1.3

  • Bugfix for getXXX() functions for data on foreign tables.