Ibatis - The SQL Map File(s)

Now that we have a DataSource configured and our central configuration file is ready to go, we will need to provide the actual SQL Map file which contains our SQL code and the mappings for parameter objects and result objects (input and output respectively).

Continuing with our example above, let’s build an SQL Map file for the Person class and the PERSON table. We’ll start with the general structure of an SQL document, and a simple select statement:

Person.xml
The above example shows the simplest form of SQL Map. It uses a feature of the SQL Maps framework that automatically maps the columns of a ResultSet to JavaBeans properties (or Map keys etc.) based on name matching. The #value# token is an input parameter. More specifically, the use of “value” implies that we are using a simple primitive wrapper type (e.g. Integer; but we’re not limited to this).

Although very simple, there are some limitations of using the auto-result mapping approach. There is no way to specify the types of the output columns (if necessary) or to automatically load related data (complex properties), and there is also a slight performance implication in that this approach requires accessing the ResultSetMetaData. By using a resultMap, we can overcome all of these limitations. But, for now simplicity is our goal, and we can always change to a different approach later (without changing the Java source code).

Most database applications don’t simply read from the database, they also have to modify data in the database. We’ve already seen an example of how a simple SELECT looks in a mapped statement, but what about INSERT, UPDATE and DELETE? The good news is that it’s no different. Below we will complete our Person SQL Map with more statements to provide a complete set of statements for accessing and modifying data.

Person.xml

---