Small Talks/2009/May/Simple Database Access From ZK

Author
Bernd Will

Date
Mar 01, 2009

Version
ZK 3

Direct Database Access from ZK source

The following source code demonstrates, how you can gain access to a database easily direct from the ZK source file.

1. setup a MySQL database on your machine.
2. create an account on your Database and a table "Activities" with two fields (ID, name)
3. enter some test data into that table (e.g. enter some action descriptions)
4. adapt the following source code according to your local database setup and login data

<?page id="testZul" title=" New ZUL Title" cacheable="false" 
	language="xul/html" zscriptLanguage="Java" contentType="text/html;charset=UTF-8"?>
<?init class="org.zkoss.zkplus.databind.AnnotateDataBinderInit"?>



<zk xmlns="http://www.zkoss.org/2005/zul"
	xmlns:h="http://www.w3.org/1999/xhtml" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://www.zkoss.org/2005/zul http://www.zkoss.org/2005/zul/zul.xsd">

<window title="JDBC" border="normal">
	<zscript language="Java">{
		import java.sql.*;
 
		public void submit() {
			
           Connection conn = null;
           try
           {
               String userName = "admin";
               String password = "password";
               String url = "jdbc:mysql://192.168.0.1/MyExampleSchema";
               Class.forName ("com.mysql.jdbc.Driver").newInstance ();
               conn = DriverManager.getConnection (url, userName, password);
               System.out.println ("Database connection established");
               
			   Statement s = conn.createStatement ();
			   s.executeQuery ("SELECT ID, Name FROM Activities where Name like '%"+ name.value+"%'");
			   ResultSet rs = s.getResultSet ();               
			   
			   while (box.getItemCount() > 0) {
			   	box.removeItemAt(0);
			   	}
			   while (rs.next()) 
			   {
			   	Listitem li = new Listitem(); 
			   	li.appendChild(new Listcell(rs.getString("Name")));
			   	box.appendChild(li);
			   }
			   rs.close ();
			   s.close ();
			   
           }
           catch (Exception e)
           {
               System.err.println ("ERROR: "+ e.getMessage());
           }
           finally
           {
               if (conn != null)
               {
                   try
                   {
                       conn.close ();
                       System.out.println ("Database connection terminated");
                   }
                   catch (Exception e) { /* ignore close errors */ }
               }
           }

			
		}
		
	}</zscript>
	<grid>
	<rows>
		<row>Name: <textbox id="name" /><button id="search" label="search" onClick="submit()"/></row> 
	</rows>
	</grid>

	<listbox id="box" mold="paging" />

</window>

</zk>

Though it is possible to access database objects directly from ZK source, you should consider using Java Beans and Pooling mechanism to encapsulate the database access. Beside the effort to recode the database access commands over and over again, you should think about reducing access overhead.

This example should simply demonstrate that it is possible to access a database very easily.

Copyright © Bernd Will. This article is licensed under GNU Free Documentation License.