MrPersister

MrPersister is a database access library for Jacl, and currently available in the distribution of Æjaks.

MrPersister is built around the Java library of the same name, developed by Jacob Jenkov [L1 ]. (The Java library has since been renamed as Butterfly Persistence.)

Since MrPersister works in a Java/Jacl environment, it is built on top of Java's JDBC, and is non-intrusive. Ordinary JDBC access can be freely intermingled with MrPersister.

To use MrPersister, you first construct a PersistenceManager object. For easy connection management, a javax.sql.DataSource can be specified, allowing the PersistenceManager object to open and close the connection as needed.

# create a PersistenceManager object using a datasource

# first, lookup the web server's datasource using JNDI
set dsname jdbc/mydatasource
set ic [java::new javax.naming.InitialContext]
set ds [java::cast javax.sql.DataSource [$ic lookup $dsname]]

::mrpersister::PersistenceManager perman $ds

The PersitenceManager object also contains a number of methods to allow Tcl code to be executed within the scope of a database transaction, read (e.g. SELECT) or update (e.g. INSERT, UPDATE, or DELETE) based on a SQL statement.

Next, obtain the Daos object. If a datasource was used to construct the PersistenceManager object, simply retreive the Daos object; otherwise, specify a JDBC java.sql.Connection object as an argument:

perman createDaos daos

MrPersister has three primary data access objects: GenericDao, MapDao, and JdbcDao.

GenericDao is primarily focused around Object-Relational Mapping. This allow tables and result sets of SELECT queries to directly populate Java objects. GenericDao guesses the name of the database table from the Java object class name, and column names derived from accessors of the Java object. Lists of objects can also be returned from a query that returns more than one row.

Since the GenericDao object expects to use Java object, and Jacl is a scripting language, how do we cross the divide? Easy! we use DbOjbBuilder to dynamically build a Java object from either a table name, or a result set from a previous JDBC query. DbOjbBuilder uses the Hyde package to build Java classes on-the-fly.

::mrpersister::DbObjBuiler Employee -connection $con

Now get the GenericDao object and perform database actions:

daos getGenericDao gendao

set emp1 [gendao readByPrimaryKey_Long hyde.Employee $primaryKey]
$emp1 setSalary [expr [$emp1 getSalary] * 0.10]
gendao update $emp1

In this example, the update method knows the table based on the object, and uses the primary key to construct a SQL UPDATE statement, and executes it.

MapDao supports ad-hoc queries, without the need to first build a Java class. A SQL query that returns one row returns a java.util.Map object, and queries that return multiple rows creates a java.util.List of Map objects.

perman createDaos daos
daos getMapDao mapdao
set listOfEmp [mapdao readMapList "select * from employee"]

# use the java::for command to get each Map row from the list
::java::for {java.util.Map aRow} $listOfEmp {

    # databases usually present column names as upper case
    # and since each Map element is itself a java.lang.Object (e.g. String, Integer, Long, etc.)
    # we must use one of the objects' methods to retrieve its contents. 
    # toString method works well for most values:

    puts "[[$aRow get EMP_NAME] toString] salary: [[$aRow get SALARY] toString]"
}

JdbcDao offers a few convenience methods for reading primary keys, returning a formatted list of keys, and similar execute, read, and update methods as in PersistenceManager:

perman createDaos daos
daos getJdbcDao jdbcdao
 
jdbcdao read "select * from employee" resultSet {
    # read makes the java.sql.ResultSql object available as named,
    # and loops for each row in the result set.
    
    puts "[$resultSet getString EMP_NAME] salary: [$resultSet getString SALARY]"
}

Besides DbObjBuilder mentioned earlier, MrPersister contains a powerful Æjaks widget: DbObjForm. This widget builds a dialog window to add, edit, delete, or update a database object. The Æjaks demo program rolodex.tcl makes use of DbObjForm. You can see a screen shot of DbObjForm in action at [L2 ]

The manual page for MrPersister is at [L3 ].