GWT + iBATIS

In this arcticle you'll find out two ways about how GWT-application could access the database on the server side using iBATIS. This materials will be useful for developers who create GWT-applications such as database front-ends.

Why iBATIS?

Frequently developers have task to create useful and functional user interface application to manipulate data (we refered this in post about our solution of  this problem -  GWT-PF). GWT is perfectly applicable for these tasks as good instrument for creating interface and client-server communications. For this purposes GWT has own RPC-interface to remote procedure call or possibility of using JSON-structures to exchange data with server.

But when you have to interact with database on the server-side appears the question about how to unify and automate this process. For example you can use directly JDBC driver of desired database and transfer untyped data in arrays. This solution is not useful in our case because on client-side we work with entities declared as typed classes. As example let's take a look at Customer class which represents customer:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

public class Customer {
	public Customer() {}

	private Integer id;
	private String name;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

We will create corresponding table with two fields id and name. Declaring this class once on GWT client-layer code we want to get load, create, edit and delete customers functionality. So we need data access layer in our application which will transfer data from database table to our class instance and vice versa. Also we want to get solution which is independent from concrete DB. This will make possible to connect to another JDBC-driver and change application environment. This tasks can be solved by iBATIS - 5 years old developing solution supported by Apache Software Foundation. Using XML-configuration iBATIS allows to represent data in Java-classes, to handle null-values processing, stored procedures calls, types conversion and many else. In this publication we don't look for another solutions of this kind of tasks - it will be topic for the next articles.

Creating RPC-service

Create empty GWT-project and using documentation implement RPC-service for loading customers list from the server database. Do following:

1. Declare client-side interface:

 1
 2
 3
 4
 5

public interface CustomerService extends RemoteService {
	
	Customer[] select();

}

2. Asynchronous interface:

 1
 2
 3
 4

public interface CustomerServiceAsync {

	void select(AsyncCallback callback);
}

3. Service loader by URL "/customer":

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

public class CustomerServiceLoader {
	
	public static CustomerServiceAsync getService()
	{
		CustomerServiceAsync calService = (CustomerServiceAsync) GWT
		.create(CustomerService.class);
		ServiceDefTarget target = (ServiceDefTarget) calService;
		String moduleRelativeURL = GWT.getModuleBaseURL() + "customer";
		target.setServiceEntryPoint(moduleRelativeURL);
		return calService;
	}

}

4. Service implementation on server:

 1
 2
 3
 4
 5
 6
 7
 8

public class CustomerServiceImpl extends RemoteServiceServlet implements CustomerService {

	public Customer[] select() {
		// TODO Auto-generated method stub
		return null;
	}

}

5. Edit GWT XML-configuration - add row:

 1

<servlet path="/customer" class="net.pleso.GWTiBatis.server.customer.CustomerServiceImpl"></servlet>

6. Class Customer must implements IsSerializable:

 1

public class Customer implements IsSerializable

Now RPC-service is ready.

Connecting iBATIS

Create 'lib' directory in the root of project and copy there last version of iBATIS. Also JDBC-driver for selected database must be placed there. In this example we use PostgreSQL, so XML-configuration will be created for this DB. Both jar-files (ibatis and postgre-jdbc) must be added to project Build Path.

Now we can create XML-configuration for iBATIS named SqlMapConfig.xml:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

<!DOCTYPE sqlMapConfig      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
	
  <!-- Configure a built-in transaction manager.  If you\'re using an 
       app server, you probably want to use its transaction manager 
       and a managed datasource -->
  <transactionManager type="JDBC" commitRequired="true">
    <dataSource type="SIMPLE">

      <property name="JDBC.Driver" value="org.postgresql.Driver"/>
      <property name="JDBC.ConnectionURL" value="jdbc:postgresql://${host}:${port}/${database}"/>
      <property name="JDBC.Username" value="${login}"/>
      <property name="JDBC.Password" value="${password}"/>
    </dataSource>
  </transactionManager>

 

  <!-- List the SQL Map XML files. They can be loaded from the 
       classpath, as they are here (com.domain.data...)--> 

</sqlMapConfig>

This file must be placed on the server-side of application. In this case we use SimpleDataSource from iBATIS. Parameters host, port, database, login, password should be replaced with correct values. If these parameters changes or you want to specify them somewhere else  iBATIS can handle it.

Creating SQL Map XML

It is desirable to create separate SQL Map XML for each logic entity. This map is mapping of SQL-queries results to Java class fields and vice versa. Create such map for Customer class (file Customer.xml):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Customer">

  <typeAlias alias="Customer" type="net.pleso.GWTiBatis.client.customer.Customer"/>

  <resultMap id="CustomerResult" class="Customer">

    <result property="id" column="id"/>
    <result property="name" column="name"/>
  </resultMap>
 
  <select id="selectCustomer" resultMap="CustomerResult"> 
 	select * from customer
  </select>
  
</sqlMap>

Here we used our class Customer from GWT and simple select-query for selecting all customers from "customer" table. "resultMap" parameter specifies mapping between database column and class field (they are same in our case). Add this mapping configuration to SqlMapConfig.xml:

 1

<sqlmap resource="net/pleso/GWTiBatis/server/customer/Customer.xml"></sqlmap>

Implementing access to the database on server-side

As follows from documentation, SqlMapClient is iBATIS helper class which allows to make database queries. Now create SqlMapManager class which will instantiate SqlMapClient:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

public class SqlMapManager {

	public static SqlMapClient getSqlMapClient(Properties authProps) {
		try {
			Reader reader = Resources
			.getResourceAsReader("net/pleso/GWTiBatis/server/SqlMapConfig.xml");
			SqlMapClient sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader, authProps);
	        reader.close();
	        return sqlMapper;
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage(), e);
		}
	}
}

Static method getSqlMapClient receives authentication parameters and database location, loads SQL Map XML and creates SqlMapClient instance. Everything is ready for query executing. Write RPC-service CustomerServiceImpl implementation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

private Properties getAuthProps(){
	Properties props = new Properties();
	props.setProperty("host", "localhost");
	props.setProperty("port", "5432");
	props.setProperty("database", "gwtibatis");
	props.setProperty("login", "postgres");
	props.setProperty("password", "12345");
	
	return props;
}

public Customer[] select() {
	SqlMapClient client = SqlMapManager.getSqlMapClient(getAuthProps());
	
	try {	
		List li = client.queryForList("selectCustomer");			
		return (Customer[])li.toArray(new Customer[li.size()]);				
	} 
	catch (SQLException e){
		e.printStackTrace();
		throw new RuntimeException(e.getMessage(), e);			
	}
}

Outputting retrieved data on client-side

Outputting retrieved data in GWT-client:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

public void onModuleLoad() {
  
  final Grid grid = new Grid(1, 2);
  grid.setText(0, 0, "id");
  grid.setText(0, 1, "name");
  
  CustomerServiceAsync service = CustomerServiceLoader.getService();
  service.select(new AsyncCallback() {
	    public void onSuccess(Object result) {
	    	Customer[] customers = (Customer[]) result;
	    	for (int row = 0; row < customers.length; ++row) {
	    	    grid.resizeRows(grid.getRowCount() + 1);
	    		grid.setText(row + 1, 0, customers[row].getId().toString());
	    		grid.setText(row + 1, 1, customers[row].getName());
	        }
	    }

	      public void onFailure(Throwable caught) {
	        Window.alert("Failure RPC call");
	      }
	    }
	    );

   RootPanel.get("slot1").add(grid);
 }

Do not forget to create database table and fill it with test data:

 1
 2
 3
 4
 5
 6
 7

CREATE TABLE customer
(
  id serial NOT NULL,
  name character varying,
  CONSTRAINT customer_pk PRIMARY KEY (id)
) 
WITHOUT OIDS;

When project is launched we can see loaded data on Grid component.

Handling null values

In the beginning of this article, when creating Customer class we used classes Integer and String for fields but we could use int for id. This decision is connected with null values handling. iBATIS automatically assigns null value for field if it is presented by class type. Thats why it is recommended to use class types in fields of business entities. In this case null-value check will be supported by Java language.

Related data, foreign keys

Lets see the example where table customer has foreign key to another table - city. In database this will be represented in field city_id which is reference on foreign key and saves its value. For working with it we need to add field city_id of type Integer to our class.

When you need to retrieve more foreign data - write one more business logic method which loads City data by city_id. If you need to mix City and Customer - create appropriate View in database or create more complicatied select-query in iBATIS configuration.

Handling custom data types using TypeHandlerCallback

One of known problems GWT developers facing with is absence of long (bigint) type in JavaScript It is described in Google Web Toolkit Language Support. Long type value is presented in JavaScript as double-precision floating point values. So if you use big numbers (for example bigint in PostgreSQL) there is possibility that transfered data will be different on the client and server sides because of casting to smaller double type. This problem can be solved using String instead of long. In this case we use big numbers as identifiers and not using them to make calculations on client side. This is acceptable for us. Besides database work with this data as usual (e.g. as with bigint).

This approach can be realized with iBATIS using TypeHandlerCallback. This interface allows you to create own handler for custom or existing type. You can see the example of such handler for BigInt (which stores long type, as string) in the sources of demo-application GWT-PF. Here  is the code of this handler, which is imported in SqlMapConfig.xml configuration next way:

 1

<typealias type="net.pleso.framework.client.dal.types.BigInt" alias="BigInt"></typealias>

Code got from demo-applications for GWT-PF:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

public class BigIntHandlerCallback implements TypeHandlerCallback {

	public Object getResult(ResultGetter getter) throws SQLException {
		if (getter.getObject() == null)
			return null;
		else
			return new BigInt(getter.getLong());
	}

	public void setParameter(ParameterSetter setter, Object parameter)
			throws SQLException {
		Long value = null;
		if (parameter != null)
			value = ((BigInt) parameter).getAsLong();		
		
		if (value == null)
			setter.setNull(Types.BIGINT);
		else
			setter.setLong(value.longValue());
	}

	public Object valueOf(String value) {
		if (value != null)
			return new BigInt(value);
		else
			return null;
	}
}

In this way we get working BigInt type with no problems while transferring it between client and server. It can be used in business-entities. All work with database interaction is managed by iBATIS using TypeHandlerCallback implementation.