aejaks.sourceforge.net

MrPersister

MrPersister

Description

Mrpersister provides high level access to relational databases using JDBC. Some features include:

  • non-intrusive, mrpersister objects co-exists easily with ordinary JDBC access
  • automatic mapping between a Java object and a table or result set
  • transaction scoping
  • ad-hoc queries
  • automatic connections via a Datasource (optional)

Mrpersister uses the Java library of the same name, a lightweight Object-Relational Mapping (ORM) library created by Jacob Jenkov The mrpersister package for JTcl adds several custom features, as well as hides some functionality that does not have value in a scripting environment.

Mrpersister has close integration with Java objects. In order to easily bridge into the Tcl script world, the Hyde package can be used to create Java objects. The DbObjBuilder procedure (see below) uses Hyde for object creation.

Mrpersister consists of several IncrTcl classes, and a set of Tcl procedures.

  • PersistenceManager - the primary class for connection and transaction management.
  • Daos - a class holding the various Data Access Objects.
  • GenericDao - a class that generically maps Java objects to table rows.
  • JdbcDao - a class that provides easy access to lower level JDBC-oriented access.
  • MapDao - a class that performs ad-hoc queries, with results placed into a Map object.
  • DbObjBuilder - a set of procedures to dynamically build Java objects based on table or result set definitions.
  • DbObjForm - builds an Æjaks WindowPane pop-up form to display a java object built with DbObjBuilder, highly customizable for labels, columns, provides standard validations and calls user supplied validation. Select, insert, update and delete database operations can be performed, or a user callback procedure can perform other database operations.

Users of mrpersister should also be familiar with Java JDBC programming. See also:

Usage

Include the mrpersister package in Tcl source files: 

package require mrpersister

All classes and commands exists in the mrpersister namespace. Any SQL error encountered during processing will cause an error condition, with the Mr. Persister library throwing an exception. To catch any exceptions, use the JTcl java::try .... catch ... command, for example: 


::mrpersister::PersisternceManager perman $datasource
perman createDaos daos
daos createGenericDao gendao

java::try {

      gendao insert $someObject

} catch {com.jenkov.mrpersister.itf.PersistenceException perEx} {
     puts "Mr Persister Exception: [$perEx toString]"
     puts "       SQL error cause: [$perEx getCause]"
}

PersistenceManager

PersistenceManager is the only class that is directly constructed, all other objects are created by other methods. Only one PersistenceManager object should be created. PersistenceManager can be constructed with a javax.sql.DataSource object, in which connections will automatically be created when needed, or a specific connection can be supplied when creating a Daos object.

Constructor

::mrpersister::PersistenceManager name ?datasource-obj?

where:

  • name - the name of the PersistenceManager object, or #auto to have a default name assigned.
  • datasource-obj - an optional argument, which must be a javax.sql.DataSource object.

Examples: 

# create a PersistenceManager object, no datasource.
::mrpersister::PersistenceManager perman


# 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

Methods

  • createDaos name ?connectionObj?

    createDaos creates a Daos object name. Name may be #auto, in which a name will be automatically generated. If the PersistenceManager object was created without a datasource, a connectionObj (java.sql.Connection) object must be supplied.


  • setDataSource datasource

    sets a datasource object for the PersistenceManager.


  • execute daosObjName ?connectionObj? code
  • executeTransaction daosObjName ?connectionObj? code

    execute and executeTransaction creates a Daos object named daosObjName and executes the Tcl code. executeTransaction will execute the code within a database transaction. If the code raises an Tcl error, then the transaction will be rolled-back, otherwise if the code finishes successfully, the transacton will be commited. execute does not execute using a database transaction, and any error condition terminate the code, and cause the error to be re-thrown. execute does not alter the state of the jdbc connection's autoCommit status.

    If the PersistenceManager object was not created with a datasource, a connectionObj object (java.sql.Connection) must be supplied. When the code has finished, the daosObjName object is deleted, and the connection is closed. If a connectionObj was specified, the daosObjName object will be deleted but will not close the connection.


  • read sqlstmt resultSetObjName ?connectionObj? code
  • readPrepared preparedStmtObj resultSetObjName code

    read and readPrepared executes a SQL query SELECT, creates a java.sql.ResultSet object named resultSetObjName, and executes code for each row returned. read will create a connection from the PersistenceManager's datasource if available, otherwise the connectionObj object (java.sql.Connection) must be supplied. readPrepared executes a prepared statement object (java.sql.PreparedStatement). For both methods, a java.sql.ResultSet object is created named resultSetObjName for the life of the query, and deleted after all rows have been read.


  • update sqlstmt ?connectionObj?
  • updatePrepared preparedStmtObj

    update and updatePrepared execute a SQL INSERT, UPDATE, or DELETE statement, and return the number of rows affected. update will create a connection from the PersistenceManager's datasource if available, otherwise the connectionObj object (java.sql.Connection)) must be supplied. updatePrepared executes a prepared statement object (java.sql.PreparedStatement). update can also be used for data definitions such as CREATE TABLE, etc.
    JDBC resources (Statement, ResultSet, and Connection, if automatic connection management is used) are automatically acquired and closed when executing the execute, executeTransaction, read, readPrepared, update, and updatePrepared methods.

Daos

The Daos object holds the Data Access Objects. When the Daos object is deleted, the connection will also be closed if the connection was obtained from the PersistenceManager's datasource. Otherwise, the user should close the connection when appropriate. Also, when the Daos object is deleted, any GenericDao, JdbcDao, or MapDao object created wth it will also be deleted. Use the IncrTcl delete object command to delete a Daos object: 

itcl::delete object $daos

Constructor

The Daos constructor should not be called directly. Instead, create the Daos object by using the PersistenceManager createDaos method. 

# using a datasource
::mrpersister::PersistenceManager perman $datasource
perman createDaos daos


# using a jdbc connecton object
::mrpersister::PersistenceManager perman
perman createDaos daos $connection

Methods

  • getConnection

    Returns the JDBC connection object held by this Daos object.


  • getGenericDao name

    Creates a GenericDao object of the given name. Name can be #auto, in which a name will be automatically generated.


  • getMapDao name

    Creates a MapDao object of the given name. Name can be #auto, in which a name will be automatically generated.


  • getJdbcDao name

    Creates a JdbcDao object of the given name. Name can be #auto, in which a name will be automatically generated.


GenericDao

The GenericDao object provides automatic mapping between a Java object and a table. Most SELECT, INSERT, UPDATE, and DELETE operations can be performed in a single method call.

Constructor

The GenericDao constructor should not be called directly. Instead, create the GenericDao object by using the Daos getGenericDao method. 

# using a datasource
::mrpersister::PersistenceManager perman $datasource
perman createDaos daos
daos getGenericDao gendao


# using a jdbc connecton object
::mrpersister::PersistenceManager perman
perman createDaos daos $connection
daos getGenericDao gendao

Methods

Note: for methods that have objectMappingKey as their first argument, any of the following can be used as the value for that argument:

  1. a Java object of a particular type. The class name of the object is used to derive the table name on which to operate, excluding the package name.
  2. a java.lang.Class object. The class name is used to derive the table name on which to operate, excluding the package name.
  3. a string specifing the fully qualified Java object name. The class name is used to derive the table name on which to operate, excluding the package name.
    Many methods allow objectMappingKey to be optional, and are indicated by ?objectMappingKey?. In these cases, the object being updated, deleted, or inserted is used to determine the database table.
    Example, any of the following can be used to read all of the rows from the table called bar: 
    set obj [java::new com.foo.Bar]
set class [$obj getClass]
set name com.foo.Bar
set listOfBars [gendao readList $obj   "select * from bar"]
set listOfBars [gendao readList $class "select * from bar"]
set listOfBars [gendao readList $name  "select * from bar"]

For methods that have a primaryKeyObj argument, the argument should be a Java object that matches the data type of the table's primary key.

For example, if a table has an int primary key, then the primaryKeyObj should be of type java.lang.Integer; likewise, if the table primary key datatype is bigint, the primaryKeyObj should be of type java.lang.Long: Refer to the Appendix below for SQL data types to Java types mappings. 

set key [java::new Integer 42]
set aBar [gendao readByPrimaryKey com.foo.bar $key]

See Appendix below for information on how SQL datatypes are mapped to Java objects or primitive types.

  • closeConnection

    Closes the JDBC connection associated with the GenericDao object.


  • executeUpdate sqlstmt ?javaObjArray? | ?javaCollectionObj?

    Executes a SQL INSERT, UPDATE, or DELETE statement. If the sqlstmt contains substitution paramaters, a prepared statement object will be created and the optional javaObjArray or javaCollectionObj must supply the parameters.


  • getConnection

    Returns the JDBC connection object used by the GenericDao object.


  • getUpdateResult ?int1?

    Returns a MrPersister UpdateResult object, or list of objects. int1 specifies a sequence of the update method executed and returns one UpdateResult object. If int1 is omitted, a java.util.List of UpdateResults is returned.


  • commit

  • rollback

    Causes any uncommited transaction to be commited or rolled-back. The setAutoCommit method must have previously be set to 0 (false), otherwise rollback has no effect.


  • setAutoCommit boolean1

    Sets the autocommit status of the GenericDao object's JDBC connection.


  • delete ?objectMappingKey? object

    Delete the row correspoding the the object objectMappingKey, or if the ?objectMappingKey? is a class object or class name, delete the row matching object.


  • deleteBatch java.util.Collection-obj
  • deleteBatch ?objectMappingKey? java.util.Collection-obj

    Deletes the rows corresponding to the objects found in the java.util.Collection-obj. If ?objectMappingKey? is not specified, determine the table based on the first object found in java.util.Collection-obj.


  • deleteBatchByPrimaryKeys objectMappingKey java.util.Collection-obj

    Deletes the rows corresponding to the keys found in the list of java.util.Collection-obj. The object type of the keys should match the SQL datatype of the table.


  • deleteBatchByPrimaryKeys_IntegerList objectMappingKey int-list
  • deleteBatchByPrimaryKeys_LongList objectMappingKey long-list
  • deleteBatchByPrimaryKeys_BigDecimalList objectMappingKey bigdecimal-list
  • deleteBatchByPrimaryKeys_StringList objectMappingKey string-list

    Deletes the rows corresponding to the keys found in the list of values. The list of values is automatically transformed into a Java Collection of objects of the specified type, then invoke deleteBatchByPrimaryKey


  • deleteByPrimaryKey objectMappingKey primaryKeyObj

    Delete a row corresponding to the primaryKeyObj.


  • deleteByPrimaryKey_Integer objectMappingKey int
  • deleteByPrimaryKey_Long objectMappingKey long
  • deleteByPrimaryKey_BigDecimal objectMappingKey bigdecimal
  • deleteByPrimaryKey_String objectMappingKey String

    Delete a row corresponding to the argument value. The argument value is passed as a plain value, which is used to automatically construct a Java object of the indicated type. These are convenience methods that contruct a primaryKeyObj of the specified type from the argument value, then invoke deleteByPrimaryKey


  • insert ?objectMappingKey? java.util.Collection-obj

    Inserts the rows found in the java.util.Collection-obj. If objectMappingKey is not specified, determine the table based on the first object found in java.util.Collection-obj.


  • insertBatch java.util.Collection-obj
  • insertBatch ?objectMappingKey? java.util.Collection-obj

    Inserts the rows in batch found in the java.util.Collection-obj. If ?objectMappingKey? is not specified, determine the table based on the first object found in java.util.Collection-obj.


  • read args
  • read_Object_SqlString objectMappingKey sqlstmt
  • read_Object_PreparedStatement objectMappingKey preparedStmntObj
  • read_Object_ResultSet objectMappingKey resultSetObj
  • read_Object_SqlString_Collection objectMappingKey sqlstmt java.util.Collection-obj
  • read_Object_SqlString_ObjectArr objectMappingKey sqlstmt javaObjArray

    Read a single row from the table mapped by objectMappingKey and return a object. The method read can accept any of the arguments of the specific read_* commands, and will select the correct read method based on the type of agruments. sqlstmt is a SQL SELECT statement, which may include positional substitutions. If the statement contains substitutions, either java.util.Collection-obj or javaObjArray must be specified to supply the arguments. Positional parameter values specified in the java.util.Collection-obj or javaObjArray should be java.lang.* objects or java objects of the appropriate type. resultSetObj specifies a result set from which to read, rather than a table. All read methods cast the object returned into the same type as the objectMappingKey.


  • readListByPrimaryKeys objectMappingKey java.util.Collection-obj

    Reads multiple rows from the table mapped by objectMappingKey and returns a java.util.List collection of objects. java.util.Collection-obj specifies the values of the primary keys from which to select rows. Values in this list should be of type java.lang.*, or the appropriate java object.


  • readListByPrimaryKeys_IntegerList objectMappingKey int-list
  • readListByPrimaryKeys_LongList objectMappingKey long-list
  • readListByPrimaryKeys_BigDecimalList objectMappingKey bigdecimal-list
  • readListByPrimaryKeys_StringList objectMappingKey string-list

    Reads multiple rows from the table mapped by objectMappingKey and returns a java.util.List collection of objects. The list of values is automatically transformed into a Java Collection of objects of the specified type, then invokes readListByPrimaryKeys.


  • readByPrimaryKey objectMappingKey primaryKeyObj

    Reads a single row from the table mapped by objectMappingKey and returns a Java object cast into the type of objectMappingKey. java.util.Collection-obj specifies the values of the primary keys from which to select rows. Values in this list should be of type java.lang.*, or the appropriate java object.


  • readByPrimaryKey_Integer objectMappingKey int
  • readByPrimaryKey_Long objectMappingKey long
  • readByPrimaryKey_BigDecimal objectMappingKey bigdecimal
  • readByPrimaryKey_String objectMappingKey string

    Reads a single row from the table mapped by objectMappingKey and returns a Java object cast into the type of objectMappingKey. These are convenience methods that contruct a primaryKeyObj of the specified type from the argument value, then invoke readByPrimaryKey


  • readList args
  • readList_Object_SqlString objectMappingKey sqlstmt
  • readList_Object_PreparedStatement objectMappingKey preparedStmtObj
  • readList_Object_ResultSet objectMappingKey resultSetObj
  • readList_Object_SqlString_Collection objectMappingKey sqlstmt java.util.Collection-obj
  • readList_Object_SqlString_ObjectArr objectMappingKey sqlstmt ''javaObjArray

    Reads multiple rows from the table mapped by objectMappingKey and return a java.util.List collection of result objects. The method readList can accept any of the arguments the the specific readList_* commands, and will select the correct read method based on the type of agruments. sqlstmt is a SQL SELECT statement, which may include positional substitutions. If the statement contains substitutions, either java.util.Collection-obj or javaObjArray must be specified to supply the arguments. Positional parameter values specified in the java.util.Collection-obj or javaObjArray should be java.lang.* objects or java objects of the appropriate type. resultSetObj specifies a result set from which to read, rather than a table.


  • update ?objectMappingKey? object

    Update the row correspoding the the object ?objectMappingKey?, or if the ?objectMappingKey? is a class object or class name, update the row matching object.


  • updateOptimistic ?objectMappingKey? object originalObject

    Update the row correspoding the the object ?objectMappingKey?, or if the ?objectMappingKey? is a class object or class name, update the row matching object, using optimistic update strategy. All fields of orginialObject must match the corresponding column values. If the update is successfull, the command return 1, indicating that the row has not been updated by another process since reading originalObject. If the row has been updated since reading originalObject, the row will not be updated, and the command returns 0.


  • updateBatch java.util.Collection-obj
    updateBatch ?objectMappingKey? java.util.Collection-obj

    Updates the rows in batch found in the java.util.Collection-obj. If ?objectMappingKey? is not specified, determine the table based on the first object found in java.util.Collection-obj.


  • updateBatchByPrimaryKeys java.util.Collection-obj java.util.Collection-old-primkeys-objs
  • updateBatchByPrimaryKeys ?objectMappingKey? java.util.Collection-obj java.util.Collection-old-primkeys-obj

    Updates the rows corresponding to the keys found in the list of java.util.Collection-obj. The object type of the keys should match the SQL datatype of the table. The collection of java.util.Collection-old-primkeys-objs must be the same size as the java.util.Collection-obj argument, and be in the same sequence. If ?objectMappingKey? is not specified, determine the table based on the first object found in java.util.Collection-obj.


  • updateByPrimaryKey objectMappingKey oldprimaryKeyObj
  • updateByPrimaryKey objectMappingKey object oldprimaryKeyObj

    Update a row corresponding to the oldprimaryKeyObj.


  • updateByPrimaryKey_Integer objectMappingKey int
  • updateByPrimaryKey_Long objectMappingKey long
  • updateByPrimaryKey_BigDecimal objectMappingKey bigdecimal
  • updateByPrimaryKey_String objectMappingKey string

    Update a row corresponding to the argument value. The argument value is passed as a plain value, which is used to automatically construct a Java object of the indicated type. These are convenience methods that contruct a primaryKeyObj of the specified type from the argument value, then invoke updateByPrimaryKey



JdbcDao

The JdbcDao provides access to the JDBC connection object, in addition to use of the GenericDao or MapDao objects.

Constructor

The JdbcDao constructor should not be called directly. Instead, create the JdbcDao object by using the Daos getJdbcDao method. 

# using a datasource
::mrpersister::PersistenceManager perman $datasource
perman createDaos daos
daos getJdbcDao jdbcdao


# using a jdbc connecton object
::mrpersister::PersistenceManager perman
perman createDaos daos $connection
daos getJdbcDao jdbcdao

Methods

  • readLong sqlstmt ?javaObjArray?

    Read a single Long value (typically a key value). sqlstmt may contain substitution parameters, if so, then javaObjArray must be specified to supply the parameter values.


  • getConnection

    Returns the JDBC connection object.


  • readIdString sqlstmt ?javaObjArray?

    Reads a list of rows, and returns the first column in the result set as a string in format (id1, id2, id3, ...). The result string is typically used in a SELECT * FROM ... WHERE IN (id1, id2, id3, ...) statement.


  • execute connectionObjName code
  • executeTransaction connectionObjName code

    execute and executeTransaction set the JDBC connection object named connectionObjName and executes the Tcl code. executeTransaction will execute the code within a database transaction. If the code raises an Tcl error, then the transaction will be rolled-back, otherwise if the code finishes successfully, the transacton will be commited. execute does not execute using a database transaction, and any error condition terminate the code, and cause the error to be re-thrown. execute does not alter the state of the jdbc connection's autoCommit status.


  • read sqlstmt resultSetObjName code
  • readPrepared preparedStmtObj resultSetObjName code

    read and readPrepared executes a SQL query SELECT, creates a java.sql.ResultSet object named resultSetObjName, and executes code for each row returned. read will create a connection from the readPrepared executes a prepared statement object (java.sql.PreparedStatment). For both methods, a java.sql.ResultSet object is created named resultSetObjName for the life of the query, and deleted after all rows have been read.


  • update sqlstmt
  • updatePrepared preparedStmtObjObj

    update and updatePrepared execute a SQL INSERT, UPDATE, or DELETE statement, either from sqlstmt or a preparedStmtObj object. update can also be used for data definitions such as CREATE TABLE, etc. The number of rows affected is returned.


MapDao

The MapDao object provides simpilied ad-hoc query capability. Results of a SELECT are placed into a java.util.Map object, or list of such objects.

Constructor

The MapDao constructor should not be called directly. Instead, create the JdbcDao object by using the Daos getMapDao method. 

# using a datasource
::mrpersister::PersistenceManager perman $datasource
perman createDaos daos
daos getMapDao mapdao


# using a jdbc connecton object
::mrpersister::PersistenceManager perman
perman createDaos daos $connection
daos getMapDao mapdao

Methods

  • readMap sqlstmt ?javaObjArray?
  • readMapList sqlstmt ?javaObjArray?

    readMap and readMapList execute a SQL SELECT statement . sqlstmt may contain substitution parameters, if so, then javaObjArray must be specified to supply the parameter values. readMap reads a single result set and returns a java.util.Map object containing the column- as keys, values are java.lang.Object objects, and will likely need to be cast to the proper object datatype, or have it's toString method invoked to return a string. readMapList returns a java.util.List object containing multiple Map objects, each Map element represents one row returned.


    Key values of each Map object are the column labels from the SQL Select statement, not column names. In the case where no column aliases are specified, this is the same as the column name.
    Due to differences in JDBC driver implementations, key values may be in upper case, lower case, or mixed case. Aggregate functions (MAX, MIN, AVG, SUM, etc.) are also driver dependent, and may return the name of the function, with or without the associated column name, or empty keys.
    Example: 
    select id, name from contacts

    select id as PRIMARY_KEY, name as CUSTOMER_NAME from contacts

    In the first example, the key values will be "id" or "ID", and "name" or "NAME". In the second case, the key values will be "PRIMARY_KEY" and "CUSTOMER_NAME".

DbObjBuilder

DbObjBuilder is a set of utility procedures to build Java classes based on database table definitions or result sets. The package Hyde is used to build the Java classes dynamically.

  • ::mrpersister::DbObjBuilder objName ?-connection connectionObj? ?table table-name? ?-resultset resultsetObj? ?-catalog catalog-name? ?-schema schema-name? ?-package package-name? ?-primitivetypes 0 | 1?

    DbObjBuilder builds a Java object based on a table definition or result set. objName is used as the class name. If -table table-name is not specified, then the objName is used to locate a table of the same name. When building a object based on a table, then the -connection connectonObj must be specified. Otherwise, a -resultset resultsetObj must be specified, and the object is built using the result set meta data.
    -catalog catalog-name and -schema schema-name can be specified to limit search the database catalog to a specific catalog or schema. The default values for each a [java::null], which does not limit table name searching. Either -catalog or -schema can be an empty string, which limits table search to those that name no catalog or schema.
    -package package-name specifies an optional java package namespace for the object. The default is the package name hyde. -package may be an empty string, in which the Java object will not have a package name.
    -primitivetypes 1, which specifies the object property datatypes be primitive types where possible (int, long, float, etc.) Primary key columns and colums that allow NULL values are always Java wrapper types (Integer, Long, Float, etc.) -primitivetypes 0 is the default.
    DbObjBuiler returns a list of two elements, the first element is a flat list of database column or result set information consisting of column-name datatype-numeric-code datatype display-size scale-size nulls-allowed. The second elemet is a flat list of object property-name property-datatype.
    Objects are built using the Hyde package ::hyde::jclass command. ::hyde::jclass options -tostring * and -hashequals * are specified. In addition, two methods are defined to return database and object property information. getDbColumns returns the database information lists (same as the first element returned by 'DbObjBuilder.) getOjbProperties returns the object property information (same as the second element returned by 'DbObjBuilder.)
    The class built with DbObjBuilder has two constructors, a simple constructor that takes no arguments, and a copying constructor that takes one argument, an object of the same type. The copying constructors creates a new object, and copies all of the fields of the object argument into the newly constructed argument. This is useful when using the GenericDao method updateOptimistic, to create a copy of the object to be updated, while preserving the object as read from the database.


    Examples: 
    ::mrpersister::DbObjBuilder State    -connection $con
::mrpersister::DbObjBuilder Employee -connection $con -table emp
::mrpersister::DbObjBuilder EmpDept  -resultset $resultObj -package ""
# note that the classes are built with a default package name of '''hyde''', unless
# specified otherwise
set stateObj    [java::new hyde.State]
set newStateObj [java::new hyde.State $stateObj]
set employeeObj [java::new hyde.Employee]
set empDeptObj  [java::new EmpDept]

  • ::mrpersister::DbPrimaryKeys table-name connection-obj ?-catalog catalog-name? ?-schema schema-name?

    DbPrimaryKeys returns a list of primary key column(s) for the specified table. A connection-obj is required. Optional '-catalog catalog-name and -schema schema-name'' may be specified to limit table searching, same as DbObjBuilder.


  • ::mrpersister::DbForeignKeys table-name connection-obj ?-catalog catalog-name? ?-schema schema-name?

    DbForeignKeys return a list of column-name table-name table-primary-key-column for each foreign key defined for a table. column-name is the name of the column on which the foreign key is defined; table-name and table-primary-key-column are the table and primary key columns in that table. A connection-obj is required. Optional '-catalog catalog-name and -schema schema-name'' may be specified to limit table searching, same as DbObjBuilder.

DbObjForm

DbObjForm is procedure to build an Æjaks WindowPane pop-up form, based on a Java object (built with DbObjBuilder). The form is highly customizable based on optional parameters:

  • ::mrpersister::DbObjForm dbObj action callback-proc ?-columns num-display-columns? ?-labelList label-list? ?-validate user-validation-proc? ?-primaryKey primary-key-column? ?-inputList input-list? ?-excludeList exclude-list? ?-windowOpts window-options? ?-inputWidth input-width? ?-persistenceManager persistence-manager-object? ?-connection connectionObj? ?-select 0 | 1? ?-dbAction insert | update | updateOptimistic | delete

    Required parameters are dbObj action and callbackProc. dbObj is a java object built using DbObjBuilder. action specifies one of the following actions:

    insert
    add
    update
    modify
    edit
    delete
    remove
    select
    view
    (other)

    action determines the type of buttons available on the form, and whether fields allow input or are display only. Insert or add imply a database insert; update, modify, edit imply a database update; delete or remove imply a database delete; select, view, (or anything else) implies a database select operation.

    callbackProc is a required name of a procedure to be called upon completion of the form. The callbackProc is called with the following parameters:


    action dbObj result

    action is one of
    canceled - form was canceled
    noaction - form action button pressed, but no dbAction was specified
    insert - dbAction insert was successful
    delete - dbAction delete was successful
    update - dbAction update was successful
    updateOptimistic - dbAction update was successful
    failure-insert - dbAction insert failed
    failure-delete - dbAction delete failed
    failure-update - dbAction update failed
    failure-updateOptimistic - dbAction update failed


    result is the the number of rows inserted, updated, or deleted, for a dbAction, an error string if the dbAction failed, or a null string for canceled or noaction. Note - if -dbAction updateOptimistic was specified, a result of 1 indicates that the row was successfully updated, while a result of 0 indicates that the update was syntactically correct, but no rows were updated. This inidicates the the row was updated or deleted by another process. A normal course of action would be to re-select the row, and offer the user another chance for updating.

    ?-columns num-display-columns? specifies the number of columns to display in the form, the defautl is 1.

    ?-labelList label-list? specifes a list of column-name label pairs to display for a column name. The default is the name of the database column, with the first letter capitalized, and any underscores replaced by spaces. Any NOT NULL columns is also marked with an icon, and has toolTipText as Required.

    ?-validate user-validation-proc? specifes a procedure to be called for user form validation. Default validation is performed automatically, to ensure that columns marked NOT NULL contain a value, integer, floating point, and numeric columns contain a valid number, and date or timestamp column contain a valid date string. After database validation is passed, the dbObj is set with values from the form, and the user-validation-proc is called with the folloing parameters:


    action top dbObj dbAction

    action is the action parameter from DbObjForm, top is the top level window pane object. DbObj propertis are named $top.c.grid.$property. dbObj is the database object filled in with values. dbAction is the database action specified, or null string if none.

    The validate procedure should raise a Tcl error if the database action should not be performed, and any other window responses (such as closing the DbObjForm window, displaying error messages, etc. are the responsibility of the validate procedure. The validate procedure can also be used to set other dbObj properties prior to that dbAction.

    ?-primaryKey primary-key-column? specifies the primary key column. This option is required if the -select 1 option is also specified, in order to retrieve the dbObj from the database.

    ?-inputList input-list? specifies a list of column-name input-type pairs. input-type specifies the type and type options to be displayed for a column name. The default for any column without an input-list specification is a TextField. Each input-list element should be sub-list of one or two elements, where the first element is the input type, and the second element is a required or optional parameter. Valid input-list types are:

    SelectField {value text value text ...} - the value of the value text will be inserted in the dbObj; the text will be displayed in the SelectField. The list of values and text is required.

    Date - a button displaying date, invokes aejaks_chooseDate library proc to set a Date. Should only be specified for properties whose type is java.util.Date. Date is display as yyyy-mm-dd

    DateTime {clockspec} - a button displaying date and time, invokes aejaks_chooseDate library proc to set a Date. Should only be specified for properties whose type is java.util.Date or java.sql.Timestamp. DateTime is displayed as yyyy-mm-dd hh:mm:ss Clockspec is optional, and should have the value 12hr or 24hr to show the Hours as 1-12am/pm or 0-24. 24hr is the default. Milliseconds or nanoseconds are not supported for java.sql.Timestamp objects.

    TextArea {heightspec} - a TextArea entry. Heightspec is an optional Extent value to specify the height of the TextArea. Default is 60px.


    ?-excludeList exclude-list? specifies a list of column names to be excluded from the form. Typically, primary and foreign key values already set in the dbObj are excluded, as well as any properties that will be set in the user validation procedure.

    ?-windowOpts window-options? specifies a list of WindowPane options to control WindowPane size, color, modal behavior, etc. Any valid WindowPane options can be specified.

    ?-inputWidth input-width? specifis the width of each input widget.

    ?-persistenceManager persistence-manager-object? specifies a PersistenceManager object to be used for the -select and/or -dbAction options.

    ?-connection connectionObj? specifes a JDBC connection object for use by the PersistenceManager object. Connection is not required if the PersistenceManager was created with a DataSource object.

    ?-select 0 | 1? specifies that the object should be selected from the database prior to form display. This option requries a valid -persistenceManager (and possibly -connection), as well as a -primaryKey option to specify the primary key column. The dbObj must be set with the value of the primary key to select. If a row cannot be read, an error condition is raised and the form is not displayed.

    ?-dbAction insert | update | delete specifies a database action to be performed. This option requires -persistenceManager (and possibly a -connection) option. The database action is performed after the default database validation and any user supplied -validate procedure.


    Examples: 
    proc emloyeeDbCB {dbAction dbObj result} {
      puts "$dbAction [$dbObj getName], result: $result"
}
# empname label displayed as "Employee Name", addr as "Address"
::mrpersister::DbObjForm $employeeObj Add employeeDbCB \
  	-persistenceManager $perman -dbAction insert \
  	-lableList [list empname "Employee Name" addr "Address"]
# notes is a varchar(2000) column, were we want a taller than default TextArea
# sex is a char(1) column with valid
#    values 'f' or 'm', displayed in list as 'Female' or 'Male'
# birthday is a Date value
::mrpersister::DbObjForm $employeeObj Update employeeDbCB \
  	-inputList [list notes {TextArea 200} sex {SelectField {f Female m Male}} birthday Date ]
set employeeObj [java::new hyde.Employee]
$employeeObj setId 43
# don't allow primary key to be changed
::mrpersister::DbObjForm $employeeObj Update employeeDbCB \
  	-select 1 -primaryKey id -persistenceManager perman \
  	-dbAction update -exclude id

DbObjGetPropertyAsObj

DbObjGetPropertyAsObj is a helper procedure to get a value from an DbObjBuilder object as a new Java object of the appropriate type, or use the optional value.

  • ::mrpersister::DbObjGetPropertyAsObj dbObj property-name ?optional-value?
    A property that contains a java null is returned as the JTcl null value [java::null]. Otherwise, a Java object is returned containing the value from the dbObj. If optonal-value is specified, that value is used to create the object, of the Java type specified by the associated dbObj column type.. Primitive types are converted into Java wrapper types. See the SQL Datatype Mapping section below for objects that are created for corresponding database column datatypes.

    If the optiona-value is specified, the value must be appropriate for the dbObj column type. Boolean values should be 0 or 1, integer values of the appropriate size, floating point, or BigDecimal numbers as strings. A Date object optional-value can be specified as an integer number of seconds, an long number of milliseconds, or an string that can be parsed into a Date.

SQL Datatype Mapping

SQL datatypes are mapped to the corresponding Java datatypes for the GenericDao objects and DbObjBuilder. When -primitivetypes 1 is specified for DbObjBuilder and the column doesn't allow NULL and the column is not a primary key columns, then the primitive type is mapped. Otherwise, the Java wrapper type is specified.

  • BIGINT - java.lang.Long or long
  • BIT, BOOLEAN - java.lang.Boolean or boolean
  • CHAR, VARCHAR, LONGVARCHAR, CLOB - java.lang.String
  • DATE, TIME - java.util.Date
  • TIMESTAMP - java.sql.Timestamp
  • DECIMAL, NUMERIC - java.math.BigDecimal
  • DOUBLE - java.lang.Double or double
  • FLOAT, REAL - java.lang.FLOAT or float
  • INTEGER - java.lang.Integer or int
  • SMALLINT - java.lang.Short or short
  • TINYINT - java.lang.Byte or byte
  • all others - byte[]

JDBC Driver Information

The following is a non-comprehensive list of JDBC drivers, and is subject to change.

  • DB2
    Vendor Site http://www.ibm.com/db2/
    Driver Name com.ibm.db2.jcc.DB2Driver
    Sample URL jdbc:db2://127.0.0.1:50000/dbname

  • Derby
    Vendor Site http://db.apache.org/derby/
    Embedded
    Driver Name org.apache.derby.jdbc.EmbeddedDriver
    Sample URL jdbc:derby:path-to-derby-db-files;create=true
    Remote Server
    Driver Name org.apache.derby.jdbc.ClientDriver
    Sample URL jdbc:derby://127.0.0.1/testdb

  • FireBird (Interbase)
    Vendor Site http://firebird.sourceforge.net/
    Driver Name org.firebirdsql.jdbc.FBDriver
    Sample URL jdbc:firebirdsql:127.0.0.1/10007:/path-to-dbname.fdb

  • FrontBase
    Vendor Site http://www.frontbase.com/
    Driver Name jdbc.FrontBase.FBJDriver
    Sample URL jdbc:FrontBase://127.0.0.1/dbname

  • H2
    Vendor Site http://www.h2database.com/
    Driver Name org.h2.Driver
    Embedded
    Sample URL jdbc:h2:file:/path-to-h2-db-files/dbname
    In-memory
    Sample URL jdbc:h2:mem:dbname
    Remote Server
    Sample URL jdbc:h2:tcp://127.0.0.1:8084/dbname

  • HSQLDB
    Vendor Site http://hsqldb.sourceforge.net/
    Driver Name org.hsqldb.jdbcDriver
    Embedded
    Sample URL jdbc:hsqldb:file:/path-to-hsql-db-files
    Remote Server
    Sample URL jdbc:hsqldb:hsql://127.0.0.1

  • Ingres
    Vendor Site http://opensource.ca.com/projects/ingres/
    Driver Name com.ingres.jdbc.IngresDriver
    Sample URL jdbc:ingres://127.0.0.1:II7/dbname

  • MySQL
    Vendor Site http://www.mysql.com/
    Driver Name com.mysql.jdbc.Driver
    Sample URL jdbc:mysql://127.0.0.1/dbname

  • OpenBase
    Vendor Site http://www.openbase.com/
    Driver Name com.openbase.jdbc.ObDriver
    Sample URL jdbc:openbase://127.0.0.1/dbname

  • Oracle (8, 8i, 9i, 10i)
    Vendor Site http://oracle.com/
    Driver Name oracle.jdbc.driver.OracleDriver
    Sample URL jdbc:oracle:thin:@127.0.0.1:1521:dbname

  • PostgreSQL (7.4, 8.*)
    Vendor Site http://www.postgresql.org/
    Driver Name org.postgresql.Driver
    Sample URL jdbc:postgresql://127.0.0.1:5432/dbname

  • SQLite
    Vendor Site http://www.zentus.com/sqlitejdbc/
    Driver Name org.sqlite.JDBC
    Sample URL jdbc:sqlite:dbname

  • SQLServer
    Microsoft Driver - SQL Server 2005
    Vendor Site http://www.microsoft.com/sql/default.asp
    Driver Name com.microsoft.sqlserver.jdbc.SQLServerDriver
    Sample URL jdbc:sqlserver://127.0.0.1;databaseName=dbname
    Microsoft Driver - SQL Server prior to 2005
    Vendor Site http://www.microsoft.com/sql/default.asp
    Driver Name com.microsoft.jdbc.sqlserver.SQLServerDriver
    Sample URL jdbc:microsoft:sqlserver://127.0.0.1;databaseName=dbname;SelectMethod=cursor
    jTDS Driver
    Vendor Site http://jtds.sourceforge.net/
    Driver Name net.sourceforge.jtds.jdbc.Driver
    Sample URL jdbc:jtds:sqlserver://127.0.0.1:5000/dbname

  • Sybase
    Sybase Driver
    Vendor Site http://www.sybase.com/
    Driver Name com.sybase.jdbc3.jdbc.SybDriver
    Sample URL jdbc:sybase:Tds:127.0.0.1:5000/dbname
    jTDS Driver
    Vendor Site http://jtds.sourceforge.net/
    Driver Name net.sourceforge.jtds.jdbc.Driver
    Sample URL jdbc:jtds:sybase://127.0.0.1:5000/dbname;useLOBs=false;TDS=4.2

MrPersister Javadoc

MrPersister is a lightweight Object-Relational Mapper (ORM) library used by the mrpersister package for JTcl. It greatly simplifies database access by using Java objects mapped to a database row or query result.

MrPersister Javadocs.

The version used in Æjaks is 3.8.4.

The MrPersister home page at Jenkov.com, which has several useful tutorial guides and further usage information.