Before you can do anything with butler you have to define the database(s) you are working with. The easiest way to this is to put a file named databases.xml in your working directory (given by the system property user.dir). Below follows an example of the configuration file. You can also find a template file in the conf folder in the download distribution.
<databases xmlns="http://butler.sourceforge.net/sql"> <database alias="my-database"> <driver>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver> <url>jdbc:microsoft:sqlserver://host:port;User=john;Password=pwd</url> <jar>path/msbase.jar;path/mssqlserver.jar;path/msutil.jar</jar> </database> </databases>
The jar-element is optional. If you prefer to include the jdbc jar files in you classpath, you can do that.
The main idea behind butler is the make a an extension to the JDBC API. The things that programmers should be able to do with JDBC, they should be able to with butler. The advantage is that the butler api is easier to use and is more object-oriented.
Butler uses the same concept of database structure as JDBC. The starting point is the database, which corresponds to a javax.sql.DataSource instance. The easies way to get a reference to a database is to use butler.sql.DatabaseBroker.
Database db = DatabaseBroker.getInstance().getDatabase("my-database");
In this case a database with the alias "my-database" should be defined in the file databases.xml described above.
A database contains a number of catalogs. Not every database vendor use the concept of catalogs. For example Oracle does not. But JDBC does, so if the database vendor does not support catalogs, butler makes a default catalog for every database. Below is shown how to get the "Northwind", which is the demo catalog in SQL Server, for a database.
Catalog cat = db.getCatalog("Northwind");
And if a database without catalogs is uses you can do like this:
Catalog cat = db.getCatalog("default");
or
Catalog cat = db.getDefaultCatalog();
A catalog is a container for a number of schemas. In fact a schema is a database user that is the owner to database objects (tables, indexes, etc). In some databases users a global in the database among all catalogs, and in some databases every catalog has its own users. Butler has a hiearcial view on this. A catalog has one or more schemas. If the database vendor uses the concept of global users, the schemas are repeated for all catalogs. The following code shows how to get a reference to a schema.
Schema schema = cat.getSchema("dbo");
The schema is the container for the tables. So, now we can start doing the real work.
The easiest way to fetch a record is by the primary key. First you have to get a reference to the table.
Table table = schema.getTable("Customers");
Next step is simply to fetch the record by supplying the primary key.
Record rec = table.findByPK("BLONP");
In this table, Customers, the primary key consist of only one column. In case the primary key contains multiple columns there is another findByPK method that takes a butler.util.Tuple as argument.
Normally you have to fetch records by another criteria than the primary key. To do this you creates a query.
Query query = table.createQuery();
Now you have a embryo to a query definition that will query records in the table referenced by table variable. A query corrensponds to a SQL-statement. The next step is to define what columns you want to fetch. (If you don't define columns to fetch all columns will be selected.)
query.addColumn(table.getColumn("CustomerID")); query.addColumn(table.getColumn("CompanyName")); query.addColumn(table.getColumn("ContactName"));
In the example above the three columns CustomerID, CompanyName and ContactName will be selected.
Next task is to set the filter that will be used to filter what records that will be selected (the where-clause). There are a number of different types of filters and it is also possible to define your own filter types. The most simple filter checks equality.
EqualsFilter contactNameFilter = new EqualsFilter(table.getColumn("ContactName"), "John Smith"); query.setFilter(contactNameFilter);
In this case the query will check if the column "ContactName" equals a specified values. The generated where-clause will be "where ContactName = 'John Smith'".
A more complex example of a filter type is the range filter.
RangeFilter customerIDFilter = new RangeFilter(table.getColumn("CustomerID"), "AAA", "BBB"); query.setFilter(customerIDFilter);
In this case the query will check if the column "CustomerID" is within a specified interval. The generated where-clause will be "where (CustomerID >= 'AAA' and CustomerID < 'BBB')".
Of course there are also filters corresponding to the boolean operators "and" and "or". An example of this may looks like following.
EqualsFilter customerIDFilter = new EqualsFilter(table.getColumn("CustomerID"), "ABC"); EqualsFilter contactNameFilter = new EqualsFilter(table.getColumn("ContactName"), "John Smith"); query.setFilter(new Or(customerIDFilter, contactNameFilter));
The Or-filter takes to nested filters and checks if either of theese filters evaluates true. The generated where-clause will be "where (CustomerID='ABC' or ContactName='John Smith')".
In many scenarios, a query should return all records where some of the columns have a given value. This can be done by buildning a nested And-filter containing Eq-filters. But an simpler way is to use a QBEFilter (Query By Example), that takes a template record as argument in the constructor.
Record rec = table.newRecord(); rec.setContactName("John Smith"); rec.setCompanyName("Caradio"); query = table.createQuery(); query.setFilter(new QBEFilter(rec));
After the query is created you can run the query and get a record list as a result.
RecordList recList = query.run();
The butler.sql.RecordList interface is extention of the java.util.List interface. To get the resulted records you just use one of the List methods (get, iterator etc).
Once you have one record you can navigate to related records using
getRelatedRecord
or getRelatedRecords
methods.
The examples below shows
how to get the order records for one customer, and how to get the customer
that belongs to one order.
customer = customerTab.findByPK("BLONP"); orders = customer.getRelatedRecords(orderTab); order = orderTab.findByPK("1234"); customer = order.getRelatedRecord(orderTab);
In the examples above, the calls of getRelatedRecord
and
getRelatedRecords
will cause an extra database access. In some cases you might want to select
borth customer and order data in the initial select, to gain performance.
This is done by joining tables in a query. One table must always be choosen as the
start table in a query. The elements in the resulting RecordList will be
Record
objects
of this table. These Record
objects will have references to related
records. Following
example demonstrates how to do a join between the tables Customers and Orders.
Query q = customersTab.createQuery(); q.join(orderTab.getForeignKey("FK_Orders_Customer")); RecordList recList = q.run();
This query will generate a SQL statement that joins the Orders and
Customers tables on the column(s) in given by the foreign key "FK_Orderss_Customers".
The returned RecordList
will contain all records in the Customers
table. To get the orders that belongs to a given customer, you do like before:
call the getRelatedRecords
method on the customer Record
object.
In JDBC the result would be a two-dimensional ResultSet, but butler will create a more easy-to-use hieracial object structure.
Once you have a reference to a Record
object you can manipulate that
record by the set-methods like this.
rec.set("ContactName", "John Doe");
The make the change persistent you have to call the save
method on
the Record
object.
rec.save();
Notice that only the columns changed will be updated in the database.
If you have a RecordList
and want to change multiple records, you can
call the save method on the RecordList
. Butler will traverse will
RecordList
and save the modified records.
If you want to create a new record you call the addRecord
method on
the Table
object. After setting the column values you just call the save
method and the record will be stored in the database.
If you want to create a new record in a table you just call the addRecord
method on your Table
object. This method returns a new empty record that are not
yet inserted in the database. Now you can call the set-methods for the columns
you want to give values. If you don't, the column will get a null value.
Finally, you call the save
method of the Record
instance, and the record
will be inserted in the database.
rec = customerTag.addRecord(); rec.set("CustomerID", 12345); rec.set("ContactName", "Donald Duck"); rec.set("CompanyName", "DuckCompany"); rec.save();
Deleting a records is very simple. First you have to pick the record
up by a query call or a call to findByPK
. Now you just have to call
the delete
method on the Record
object, and the record will be deleted
from the table in the database.
In the examples above generic schema, table and record classes is used. This means for example that table and columns are located by their name given as a string, for example.
Column col = table.getColumn("CustomerID");
But if you don't want to risk getting a runtime exception because you misspelled a column name, you can generate a Table and Record class for every table in your schema. Instread your code will look like this.
Column col = table.customerID();
In the generic Record class the get
method returns an Object
and
the set
metod takes an Object
as argument.
In a generated Record subclass, getXXXX
and setXXXX
methods
is generated for each column, where XXXX is the column name. The type for
theese methods is the type of the column in the database. So, by generating
Record
subclasses, you get stronger typing. The example belows shows the use
of set
and get
methods.
customer.setContactName("John Smith"); name = customer.getContactName();
The generator will also generate methods for relation navigation. If we
have two related tables, Orders and Customers, OrdersRecord
will
have a getCustomer
method and CustomersRecord
will
have a getOrderRecords
method. The example below show the use
of these methods.
orders = customer.getOrders(); customer = order.getCustomer();
To run the generator, you execute the ant task "generate". The generator will expect to find a database.xml file in the conf subfolder in the butler directory. You can create this file by renaming the file databases-template.xml in the same directory, and update the file with correct configuration data.
When you start the generator you will be prompeted for the name of database, catalog and schemas. The availible names will also be prompted for you. You also have to tell where to put the sources and the package name for the sources.
The generator will produce one subclass for the specified schema. And one Table and Record subclass for every table in that schema.
It is also possible to generate a schema definition as a xml file with the generator. The purpose to do this is to improve performance, becase database metadata does not have to but retrived from the database runtime. The generated xml file will be used instead.
Butler has a set of swing components that makes it very fast to develop client applications that queries and edits data in a database. Theese components can be connected together (as listeners) and form complex applications.
Lets suppose that you want to create a query panel for the following query.
Table table = schema.getTable("Orders"); Query query = table.createQuery(); RangeFilter orderIDFilter = new RangeFilter(table.getOrderIDColumn()); EqualsFilter customerIDFilter = new EqualsFilter(table.getCustomerIDColumn()); query.setFilter(new OrFilter(orderIDFilter, customerIDFilter));
To create the query panel first have to create a query controller.
QueryController controller = new QueryController(query);
The controller is a ActionListener
and will retieve the query parameters
and run the query when the search action is fired.
When you build up your client you have to create a filter panel for every leaf filter in your query, and register the panel to the controller.
filterPanel = new RangeFilterPanel(orderIDFilter, false); controller.registerFilterPanel(filterPanel); filterPanel = new EqualsFilterPanel(customerIDFilter, false); controller.registerFilterPanel(filterPanel);
The RangeFilterPanel
will be a JPanel
with two
JTextFields
. One for
the min value and one for the max value. The EqualsFilterPanel
will
produce one JTextField
for the value. The second parameter in the
constructors defines if a label with the column name should be produced
or not.
Finally you will have a "Search"-button that triggers the search event.
You just create a JButton
like this.
JButton button = new JButton("Search"); button.setActionCommand(QueryController.SEARCH_ACTIONCOMMAND); button.addActionListener(controller);
If a custom layout for the query panel is not needed, a QueryPanel
can
be used. In this case a QueryPanel
can be constructed using a
Query
object.
queryPanel = new QueryPanel(query);
In this case a panel containing both filter panels and "Search"-button will be created.
In the section above we have created a query panel. But we also want a component for showing the result. The result can be shown in a JTable using the subclass RecordListTable.
DRecordListTable descriptor = new DRecordListTable(table); RecordListTable recListTable = new RecordListTable(descritpor); controller.addRecordSelectionListener(recListTable);
Now you have a JTable that will show the result from the previous query.
If you want to define what columns, and in what order they will appear in the table, you should use the addColumn method.
descriptor.addColumn(table.getColumn("OrderID")); descriptor.addColumn(table.getColumn("CustomerID"));
In most cases, you want to be able to delete records. For creating such column, create a DeleteTableColumn instance instead.
If you want to show a column from a related table (many-to-one relation), you can use the following code.
descriptor.addColumn(orderTab.getForeignKey("FK_Orders_Customers"), customerTab.getColumn("CompanyName"));
Records in a RecordListTable can be edited directly in the table. For string and numeric values a JFormattedTextField that is aware of the database datatype, will be used. For numeric values, only numeric values will be accepted. For date values, a special date editor will be used.
The RecordListTable is also an ActionListener, responding to the action command "save" and "new", which will save the records or insert a new empty record.
If only one record should be edited, a RecordEditor should be used. This component will produce a table with one row for each column, or foreign key, you want to edit. The table will have two columns, for the column name and a editor for the column value. The RecordEditor is also an ActionListener that listens for the action commands "save" and "new".
If you want to edit a foreign key value, there are options. First, you can have a text field followed by a lookup-button. If the button is pressed, a selection panel will pop up. The second option is to have a combo box with all valid values. The example below shows the second option. The combo box will show the LastName-column value for all related records.
DRecordEditor desc = new DRecordEditor(table); desc.addColumn(table.getColumn("OrderDate")); desc.addForeignKey(table.getForeignKey("FK_Orders_Employees"), employeeTab.getColumn("LastName")); RecordEditor editor = new RecordEditor(desc);
The column values will be edited with the same editors that RecordListTable uses.
The RecordEditor implements also (like RecordListTable) the RecordSelectionListener interface. This means that a RecordEditor can listen to a QueryController, but only if the corresponding query returns a single record (by primary key or unique index). The RecordEditor can also listen to record selection events from a RecordListTable.
In most cases database-centric client applications follow the same patterns. There are two very common patters. In the first pattern, there use to be a search panel at the top, followed by a spread view with the search result. The second pattern is a detail view for editing one record and below a tabbed pane for editing related records.
In Butler, there are two classes. ListForm and DetailForm for buildning clients according to these patterns. They uses the components QueryPanel, RecordListTable, RecordEditor and RelatedTabbedPane to create a complete client.
To create a form, you first need to create a corresponing descritor object,
DListForm
and DDetailForm
, from the
butler.client.form
package. Instead of creating descriptor objects,
it is also possible to describe the forms in an xml document. The example
below show a list form for the table Orders.
<list-form table="Orders"> <query-panel> <and-filter> <equals-filter column="OrderID"/> <fk-filter-panel fk="FK_Orders_Customers"> <combo-record-selector column="CompanyName"/> </fk-filter-panel> </and-filter> </query-panel> <record-list-table> <column name="OrderID"/> <column name="OrderDate"/> <column fk="FK_Orders_Customers" column="CompanyName" header="Customer"/> <column fk="FK_Orders_Employees" column="LastName" header="Employee"/> </record-list-table> </list-form>
Butler has a JSP tag library for using the butler API.
The queries that will be used in JSP pages has to be defined in xml files, available for the web application. To define a query, put a xml-file with the name queryname.xml in the subfolder named queries, to the web application directory. Here follows an example on a query definition.
<query xmlns="http://butler.sourceforge.net/sql" name="OrdersQuery" database="northwind" catalog="Northwind" schema="dbo" table="Orders"> <column name="*"/> <join table="Customers"> <column name="*"/> </join> <or-filter> <range-filter column="OrderID" id="OrderID"/> <equals-filter column="CustomerID" id="CustomerID"/> </or-filter> </query>
This xml defines a query for that table Orders that also joins the Customers table. All columns in both tables will be selected. The query filter is a or-filter with one range-filter and one equals-filter.
To make a query panel you have to make a html form element. Inside this element you use the filter tag to create input elements for the different filters.
<butler:filter query='OrdersQuery' filter='OrderID'/>
The code above will produce input elements for the filter with the id 'OrderID' in the query named 'OrdersQuery'. Now you just have to put a submit input element into the form.
In the jsp page that will show the query result, you first have to write a query element that populates the query with the parameters and runs the query.
<butler:query name="OrdersQuery"/>
In the code above the query named 'OrderQuery' will be created, populated with parameters from the request object. Finally the query will be executed and the resulting RecordList will be added to the page context.
Next step is to present the resulting RecordList in the JSP page. This is most likely done by a html table. To do this, you should use the record-iterator and value elements, like this.
<table border="1"> <tr> <th>ID</th> <th>Date</th> <th>Contact</th> </tr> <butler:record-iterator query="OrdersQuery"> <tr> <td><butler:value record="OrdersQuery" column="OrderID"/></td> <td><butler:value record="OrdersQuery" column="OrderDate"/></td> <td><butler:value record="OrdersQuery" fk="FK_Orders_Customers" column="ContactName"/></td> </tr> </butler:record-iterator> </table>
The record-iterator tag will iterate through the RecordList and the value tag will pick a column value from the current record (or a record related to the current).
To do a JSP page for editing a record, you first have to use the query tag to pick the record to select.
<butler:table name="Orders" alias="northwind" catalog="Northwind" schema="dbo"/> <butler:query type="findByPK" table="Orders"/>
The table tag is a convenience tag for selecting a table. In the query tag you can see that no query name is given. Instead you find the type and table attributes. If type equals to 'findByPK', one record from the given table is selected by the primary key, populated from the request parameters. The record is added to the page context.
Next you have to make a html form and inside you should first put a primary-key tag.
<butler:primary-key record="Orders"/>
This tag will produce hidden input elements with the primary key values.
Now, you just insert editor tag for the columns you want to make editable.
<editor record="Orders" column="CustomerID"/>
In the page that will handle the submit request from this form, you just have to put a update tag, for saving the changes of the record.
<butler:update record="Orders"/>
The column values will be picked up from the request parameters.
Butler is aware of the datatype of database columns. Information about
datatype is used by the GUI components and when values are formatted. Butler
has built-in support for datatypes that normally occurs in databases, like
strings, numbers, dates and BLOBS. But it is also possible to define custom
datatypes by implementing the interface Datatype
and corrensponding
ValueEditor
implementations if needed. Custom ValueEditor
implementations should be registered in ValueEditorBroker
.
Sometimes it is not enough to validate single column values using the
datatype concept in Butler. If two or more valid column values can form an
invalid record state, you need to use a RecordValidator
. Before
a record will be saved, the registered validator will be asked to check if the
state of the record is valid or not.
If you want to special logic to be performed before or after records
are updated, inserted, deleted or column values are assigned, a
RecordListener
can be used. One scenario when you want to use
a record listener is when a table has a timstamp column that are updated
with the current time every time the record is updated or inserted.
In some situations, the mapping between tables and classes is not one-to-one. One way to solve this problem is to use virtual columns.
Virtual columns can be based on a caluculated value. In this case
a FunctionColumn should be registered to the table. The FunctionColumn
has a ColumnFunction
that calculates the value based on the values
in the rest of the record. In this case, the column will be read-only.
If you need writable virtual columns, use VirtualColumn
and implement
(and register) a RecordListener
to do the updates that
corresponds to changes in
the virtual column value.