In additions to access database in an event listener, it is common to access database to fulfill an attribute by use of an EL expression. In the following example, we fetch the data from database and represent them with listbox by use of EL expressions.
<zscript>
import my.CustomerManager;
customers = new CustomerManager().findAll(); //load from database
</zscript>
<listbox id="personList" width="800px" rows="5">
<listhead>
<listheader label="Name"/>
<listheader label="Surname"/>
<listheader label="Due Amount"/>
</listhead>
<listitem value="${each.id}" forEach="${customers}">
<listcell label="${each.name}"/>
<listcell label="${each.surname}"/>
<listcell label="${each.due}"/>
</listitem>
</listbox>
There are several way to implement the findAll method.
The simplest way is to retrieve all data in the findAll method, copy them into a list and then close the connection.
public class CustomerManager {
public List findAll() throws Exception {
DataSource ds = (DataSource)new InitialContext()
.lookup("java:comp/env/jdbc/MyDB");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List results = new LinkedList();
try {
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT id, name, surname FROM customers");
while (rs.next()) {
long id = rs.getInt("id");
String name = rs.getString("name");
String surname = rs.getString("surname");
results.add(new Customer(id, name, surname));
}
return results;
} finally {
if (rs != null) try { rs.close(); } catch (SQLException ex) [}
if (stmt != null) try { stmt.close(); } catch (SQLException ex) [}
if (conn != null) try { conn.close(); } catch (SQLException ex) [}
}
}
}
Instead of mixing Java codes with the view, you could use the init Directive to load the data.
<?init class="my.AllCustomerFinder" arg0="customers"?>
<listbox id="personList" width="800px" rows="5">
<listhead>
<listheader label="Name"/>
<listheader label="Surname"/>
<listheader label="Due Amount"/>
</listhead>
<listitem value="${each.id}" forEach="${customers}">
<listcell label="${each.name}"/>
<listcell label="${each.surname}"/>
<listcell label="${each.due}"/>
</listitem>
</listbox>
Then, implement the my.CustomerFindAll class with the org.zkoss.zk.ui.util.Initiator interface.
import org.zkoss.zk.ui.Page;
import org.zkoss.zk.ui.util.Initiator;
public class AllCustomerFinder implements Initiator {
public void doInit(Page page, Object[] args) {
try {
page.setVariable((String)args[0], new CustomerManager().findAll());
//Use setVariable to pass the result back to the page
} catch (Exception ex) {
throw UiException.Aide.wrap(ex);
}
}
public void doCatch(Throwable ex) { //ignore
}
public void doFinally() { //ignore
}
}