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 [http://jenkov.com]. (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 [http://aejaks.sourceforge.net/Screen_Shots/index.html] The manual page for MrPersister is at [http://aejaks.sourceforge.net/Documentation/MrPersister/index.html]. ---- !!!!!! %| [Category Development] | [Category Database] |% !!!!!!