iBATIS DataMapper
Added by Clinton Begin, last edited by Clinton Begin on Aug 08, 2009  (view change)
Labels: 
(None)


Notice

iBATIS 3 has been released!

See the iBATIS Homepage for more: http://ibatis.apache.org

Opportunity for Change.

January 11th, 2008 marks the 4th Anniversary of the iBATIS 2.0. It has served the community well for four years, but times change. The last few years have been full of innovation and shifts in technology and mindset. The impact of frameworks like Ruby on Rails cannot be ignored. The industry has noticed and finally invested in lightweight frameworks, agile principles and simple solutions first.

Luckily, this shift aligns closer toward iBATIS, not further from it.

iBATIS has always been about simplicity. Since the very beginning, with each release of iBATIS we've made the framework smaller and simpler with fewer dependencies. Yet, we've been able to make the framework faster and more powerful every time.

We hope to continue on this promise with iBATIS 3.0. This whiteboard is where it all begins!

How to work with this page.

  • Committers can change the content of this page.
  • Where there is disagreement among committers, all alternatives should be presented until resolved.
  • Anyone in the community can comment on the content using the comment section below.
  • Committers may make changes to the content based on the comments.
  • Comments acted upon may be deleted to keep the comments relevant to the current state.
  • Comments may be archived if they are deemed irrelevant or are declined (avoid deletion).
  • The committers are charged with ensuring the continuity, consistency and focus of the design is managed.
  • Not all comments or suggestions will be accepted.
  • We'll run a tight ship to ensure this page doesn't turn into a zoo.
  • These rules can change.

Themes for iBATIS 3.0

  • Test driven development
  • Code cleanliness over performance
  • Simple design over flexible design
  • One JAR file
  • No required 3rd party dependencies
  • Better plug-in support
  • Third party plug-ins are hosted elsewhere (sub-project on SF or CodePlex) http://sourceforge.net/projects/ibatiscontrib/

The Methodology

  • Clinton says: When I wrote iBATIS 1.0, it was just me. I hacked it out in the most simple and straightforward way possible, with only high level functional tests (actually, they were JPetStore tests). With iBATIS 2.0 I promised myself I'd write unit tests, but I didn't test drive it. The test coverage for 2.0 is around 63%, but looking deeper it's actually not even that good in my own opinion. Therefore, for 3.0 I'd suggest test driving it. The wise man (you) learns from the mistakes of others (me).

Our team is already very good at developing and maintaining the iBATIS codebase. The source code in our repository is always buildable and even deployable. We don't release when it's stable, because it's always stable. We release when we decide we've hit a milestone that together make a compelling feature set for deployment. We're already quite agile.

To ensure that we have clear direction for the development of iBATIS 3.0, here is a summary of our lifecycle.

Practice Agile Concept
Discussion of new feature/issue on the dev and user mailing lists. customer involvement
Creation of JIRA ticket for new feature/issue. user story
Test drive development of new feature/issue. test always
Commit feature/issue with unit test as a single revision. working software
Automated build and test run upon each checkin. continuous integration
Nightly (or more frequent) builds available for download. release often
Release upon milestone defined by useful feature set decided by the team/community. release planning

Design...Feature by Feature

The following sections describe the design of the iBATIS 3.0 features.

Interface Binding

With the introduction of Java 5 we have new opportunities to improve the user experience. The most important being the elimination of ambiguous type casting and string based calls to SQL maps. For example, the current way to call a SQL Map is this:

Employee employee = (Employee)sqlMapper.queryForList("getEmployee", 5);
   //...and...
   List employees = sqlMapper.queryForList("listAllEmployees");

The problems are obvious. First, in both cases the mapped statement names are string based. This is obviously error prone and ugly. There's no hope for help or hints from the IDE, or even compiler warnings or errors.

The second problem is the type safety of the parameters and results. For example, in the first case, who's to say that the parameter is an integer? And who's to say that you're actually going to get an Employee class back. Again, no compiler warnings or errors. Only runtime exceptions will be reported.

And finally, in the case of collections, the problems are the same. You may get a list of Dogs back, instead of Employees, without warning before runtime.

Overall, it's ugly – still better than JDBC – but not as good as it could be.

With Java 5 we can leverage generics to type the collection and therefore for the first time we can create a consistent API for collections without additional configuration. Instead of declaring types in XML and calling statements via loose string names, we can make effective use of a very descriptive and typesafe Interface type. Nothing new to learn, just a normal interface.

public interface EmployeeMapper {
   Employee getEmployee (int employeeId);
   List<Employee> listAllEmployees();
   }

That's it! iBATIS takes care of the implementation of the interface for us.

We can now call mapped statements in a much cleaner and safer way.

Employee emp = empMapper.getEmployee(5);
   //...and...
   List<Employee> employees = empMapper.listAllEmployees();

No casting, no strings, typesafe parameters and results. The extra code incurred on the interface will be made up by far less configuration in the XML or annotations. At most, we'll have the SQL and any result or parameter mappings that need to be overridden.

The interface tells nearly everything we need to know.

  • Name of the "statement" (same as the method name).
  • Parameter types (if any).
  • Result types (including collection element types).

And most interesting (and possibly controversial) of all...

  • We could even generate the SQL based on conventions in the method name. More about that in the Multilevel Configuration section below.

The bound interface approach also makes it easier for frameworks like Spring to inject just a nice clean mapper class, without exposing the full details of the transaction manager etc.

Transactions, Sessions, and Factories – MapperFactory

So where do we get a "Mapper" instance from? We'll still need some sort of central class for iBATIS configuration and control. The name is up for debate, but for now we'll call it MapperFactory.

In general, the MapperFactory is responsible for transactions and mapper instances. The MapperFactory itself will come from some sort of configuration class (again, more about that in the configuration section below).

MapperFactory factory = someConfiguration.buildMapperFactory();
EmployeeMapper employeeMapper = factory.getMapper (EmployeeMapper.class);

I'm not sure that it gets any easier...but that's it. As for transactions...

  • Clinton says: The concept of a session should be merged with that of a transaction. In iBATIS, session and transaction have largely been one and the same. But they were separated for some artificial architectural reason. There are a few things sessions could offer, but for 80% of cases, they don't matter. Similarly batches were separated from transactions, which caused messy nested try/finally blocks. So lets avoid them for now, unless we find a real need for them.

Transactions will be handled either by iBATIS or some 3rd party framework or container. To date they've been handled by a ThreadLocal instance inside a SqlMapClient instance. We get a lot of questions about thread safety due to this approach, so for no technical reason but perhaps a code clarity reason, we could use the following approach:

Transaction tx = factory.startTransaction();
   try {
   // do work
     tx.commit();
   } finally {
   tx.end();
   }

Batches will be handled by simply starting a batched transaction:

Transaction tx = factory.startBatch();
   try {
   // do work
     // balk executes current batch immediately, returns total rows updated
     // (balks on non-batched transactions)
     int totalRowCount = tx.flush();
   // commit executes batch too, returns total rows updated and commits
     totalRowCount = tx.commit();
   } finally {
   tx.end();
   }

Similar to the existing transaction API, we'll still support isolation levels and other transaction options.

Transaction tx = factory.startTransaction(isolationLevel);
   try {
   // do work
     tx.commit();
   } finally {
   tx.end();
   }

If a third party is handling transactions, the managed connection can be explicitly passed:

// Can be handled by encapsulating framework (e.g. Spring)
   Transaction tx = factory.useConnection(conn);
   try {
   //...do work...
     tx.flush(); // executes batched updates immediately
     // commit simply calls commit on the connection
     // if local commits are allowed, otherwise it is ignored.
     tx.commit();
   } finally {
   tx.end(); // Can also be handled by encapsulating framework (e.g. Spring)
   }

Overall this API is cleaner, simpler and is easier to integrate with 3rd party frameworks like Spring. The MapperFactory can be injected into SpringDAOs if access to the transaction manager, batch or isolation levels is needed. To isolate this further, we could separate the transaction manager from the MapperFactory, or even allow the developer to identify their own TransactionManager interface that uses conventions to loosely bind the transaction management methods to their own class (better isolation of the framework).

Multilevel Configuration

By far the most "old-school" aspect of iBATIS of iBATIS is the configuration. When originally built, XML was thought to be a best practice for configuration. Indeed, XML is still a good choice for iBATIS, as it is a great way to code complex multiline SQL. Therefore XML will remain. However, it will not be the default, or even the solution for most cases.

iBATIS 3.0 will use a multilevel configuration approach. That is, there will be a number of ways to configure iBATIS that work together and override each other. The four ways, in order of application, are:

  • Convention
  • Annotation (overrides convention)
  • XML (overrides convention and annotation)
  • Java API (overrides convention, annotation and XML)

Configuration by Convention

  • Clinton says: This is the biggest departure from our former principles. Anyone who knows me knows I'm not a big fan of generation. But if we're to adapt to changing best practices, we must consider simple solutions for even complex applications. Rest assured, that people who don't like convention based configuration will be able to easily ignore it. I believe it will be very useful, especially for inserts, updates and deletes. Some simple queries will be made easier as well.

By default, iBATIS 3.0 will be configured by convention. Java 5 method signatures have enough information to generate the SQL and map all parameters and results. For example:

Employee getEmployee (int id);

This is enough information to generate:

SELECT id, firstName, lastName FROM Employee WHERE id = ?

The result class is equal to the method return type. Because the method result class is not a collection, we know we're dealing with "SELECT one" semantics. The table name is assumed to be the same as the result class. The parameter is assumed to be ID for "SELECT one" methods (only because Java lacks introspection on parameter names...C# can do this). We can override the default ID parameter if we need to using one of the other configuration options (annotation, XML or Java API). The returned columns are assumed to be equal to the class properties but can be overridden too (see "Options" below).

Collections work the same way:

List<Employee> listAllEmployees ();

In this case we generate:

SELECT id, firstName, lastName FROM Employee

We can tell it's a "SELECT many" because the method returns a collection. We know the WHERE clause is empty because there are no parameters.

If we want parameters for a "query by example", we can support something like this:

List<Employee> findEmployeesLike(Employee employee);

Assuming only lastName is set, all other reference types are null, and primitives are set to some invalid number like -1...we can generate something like:

SELECT id, firstName, lastName FROM Employee WHERE lastName = 'Begin'

Or a better convention by naming could be:

List<Employee> findEmployeeByLastNameAndFirstName (String last, String first);

This will yeild:

SELECT id, firstName, lastName FROM Employee WHERE lastName = 'Begin' AND firstName = 'Clinton'

In C# this will be very nice, because C# can introspect on parameter names. It's quite a bit cleaner, but the same idea:

// C#
   IList<Employee> FindEmployeesLike(string lastName, string firstName);
   
   // And in C# 3.0, we can use anonymous types:
   
   IList<Employee> FindEmployeesLike(object obj);
   //...
   IList<Employee> employees = mapper.FindEmployeesLike(new {LastName="Begin", FirstName="Clinton"});

Updates will work the same way:

void insertEmployee (Employee emp);
   void updateEmployee (Employee emp);
   void deleteEmployee (Employee emp);

We can also accept collections automatically:

int insertEmployee (List<Employee> emps); // returns rows inserted
   int updateEmployee (List<Employee> emps); // returns rows updated
   int deleteEmployee (List<Employee> emps); // returns rows deleted

The generator could be smart enough to code a single statement for mass deletes (and maybe even some updates).

Relationships

We may be able to support some simple relationships. Simply having a complex property type or collection property type on a class is enough information to identify the relationship. Class definitions are descriptive enough to infer the relationship, even though the reverse is not true (i.e. the data model is not descriptive enough).

Standard SQL? What's that?

We're not going to go crazy with proprietary SQL support. In fact, we're not going to support it at all. We'll support the most standard SQL that works for most databases. This shouldn't be a problem considering the basic queries we're supporting in the convention based generation. Any crazy advanced SQL can be done by hand.

Some Possible Configuration Options

The following are some options we could support for SQL generation.

  • Assume that database follows JavaBean naming convention.
  • Translate JavaBean naming convention to UPPERCASE_UNDERSCORE database convention.
  • Primary key does (NOT) include table name.
  • Use SELECT * and lazy mapping for queries instead eager Class/Table mapping via Database Metadata at startup.
  • Use old WHERE clause join syntax
  • Default relationship loading to use join mapping or lazy loading

Configuration with Annotations

Annotations are becoming more popular and many people are choosing it over XML for metaprogramming. Configuration should not be included in annotations, but much of what iBATIS puts in XML is not configuration.

So what is configuration and what is not? Current iBATIS XML files contain 3 things:

  • Configuration
  • Meta information
  • Code

Configuration is anything that changes when you change environments. For example, database connection information, transaction management etc. In some cases if you're deploying to different databases and use proprietary SQL, then the configuration might include selecting the SQL dialect. We can include such things in iBATIS 3.0, so you can code multiple dialects (by hand of course), give them names and configure it in the appropriate location – outside of your .java files. Configuration should not be in .java files.

Meta information includes things like Result Maps, Parameter Maps and Cache Models. This information changes how iBATIS maps your data and how the mappings behave.

Code includes SQL and the dynamic SQL elemenets.

So what should be in annotations?

For the most part, only meta information should be in annotations. Configuration should be left to properties files or XML. Code should be left to Java code or XML. Furthermore, annotations should probably be used only for simple meta information. Annotations (especially in Java) can become complex, unreadable and messy.

So, I'd recommend using Annotations only as a means to override convention based configuration. That is, use it to tweak the odd SQL statement and the odd Result Map or Parameter Map that needs a column or two remapped.

Once again, C# Attributes will look a lot better than Java Annotations for a few reasons:

  • C# supports multiline strings, which makes inline SQL coding a lot more pleasant.
  • C# supports multiple attributes of the same type. Java requires horrendous collection attributes.
  • C# supports attributes with both ordinal and named parameters, making code much cleaner and more terse.

That said, the following is an example of how some of these annotations might look in Java.

NOTE: You'll notice also that I've included inline result syntax, as well as inline parameters. This topic will be controversial since the syntax for results look more like the old syntax for parameters. Overall though, I think this is a really significant improvement and an important one. Some people may choose to ignore inline parameters or results, or both, but many people will use them exclusively in favour of annotations or XML. Also as a sidenote, parameters will always be named from now on. No more "?" ever. The names can be referenced with "decorator annotations" to change properties of the parameter (e.g. types) etc.

//
     // Simple select, string concatenation, inline results(!) and inline parameters
     //
   
   @Select("SELECT #id(EMP_ID:NUMERIC), #firstName(FIRST_NAME:VARCHAR), #lastName(LAST_NAME:VARCHAR) " +
   "FROM EMPLOYEE")
   List<Employee> selectAllEmployees();
   
   //
     // Alternative syntax using an array of strings instead of string concatenation...can be "smarter" than concatenation
     //
     @Select({"SELECT #id(EMP_ID:NUMERIC), #firstName(FIRST_NAME:VARCHAR), #lastName(LAST_NAME:VARCHAR) ",
   "FROM EMPLOYEE",
   "WHERE EMP_ID = @id"})
   Employee selectEmployee(int id);
   
   //
     // Inserts look as you might expect.  We can use getGeneratedKeys to get autogen key values, selectkey still supported
     //
     @Insert({"INSERT INTO EMPLOYEE (EMP_ID, FIRST_NAME, LAST_NAME)",
   "VALUES (@id, @firstName, @lastName)"})
   void insertEmployee(Employee emp);
   
   //
     // Nothing special about update
     //
     @Update({"UPDATE EMPLOYEE SET",
   "EMP_ID=@id(NUMERIC:IN), FIRST_NAME=@firstName(VARCHAR:IN), LAST_NAME=@lastName(VARCHAR:IN)"})
   void updateEmployee(Employee emp);
   
   //
     // Delete is obvious.
     //
     @Delete("DELETE EMPLOYEE WHERE EMP_ID = @id")
   void deleteEmployee(int id);

The following examples are more complicated, almost to the point that this amount of annotation should probably be moved to the XML. I imagine we'll make the annotations full featured, in that anything that can be done in XML will be possible with annotations. However, it will likely be our running recommendation that XML be preferred for complex or even just verbose cases. Some people may choose to ignore the recommendation, and we'll support them in that.

//
     // complex stuff
     //
   
   @ResultClass (Company.class)
   @ConstructorResults({
   @Result(property="id", column="C.COMP_ID"),
   @Result(property="name", column="C.NAME")
   })
   @PropertyResults({
   @Result(property="departments.id", column="D.DEPT_ID"),
   @Result(property="departments.name", column="D.NAME"),
   @Result(property="departments.employee.id", column="E.EMP_ID"),
   @Result(property="departments.employee.firstName", column="E.FIRST_NAME"),
   @Result(property="departments.employee.lastName", column="E.LAST_NAME")
   })
   @Collections ({
   @Collection(type=Department.class, property="departments", groupBy="id"),
   @Collection(type=Employee.class, property="departments.employees", groupBy="departments.id")
   })
   @Select("SELECT #id, #name, " +
   "#departments.id, #departments.name, " +
   "#departments.employees.id, #departments.employees.firstName, " +
   "#departments.employees.lastName " +
   "FROM COMPANY C, DEPARTMENT D, EMPLOYEE E " +
   "WHERE D.DEPT_ID = E.DEPT_ID " +
   "AND C.COMP_ID = D.COMP_ID")
   List<Company> selectAllCompaniesWithJoin();
   
   /*
   * NESTED QUERIES
   */
   
   @ResultClass (Company.class)
   @FieldResults({
   @Result(property="id", column="COMP_ID"),
   @Result(property="name", column="NAME")
   })
   @PropertyResults({
   @Result(property="departments",
   nestedQuery=@QueryMethod(type=CompanyMapper.class, methodName="getDepartmentsForCompany", parameters="id"))
   })
   @Select("SELECT #id, #name FROM COMPANY C ")
   List<Company> selectAllCompaniesWithNestedQueries();
   
   @ResultClass (Department.class)
   @PropertyResults({
   @Result(property="id", column="DEPT_ID"),
   @Result(property="name", column="NAME"),
   @Result(property="employees",
   nestedQuery=@QueryMethod(type=CompanyMapper.class, methodName="getEmployeesForDeparment", parameters="id"))
   })
   @Select("SELECT #id, #name FROM DEPARTMENT WHERE COMP_ID = @id ")
   List<Department> getDepartmentsForCompany(int id);
   
   @ResultClass (Employee.class)
   @PropertyResults({
   @Result(property="id", column="EMP_ID"),
   @Result(property="firstName", column="FIRST_NAME"),
   @Result(property="lastName", column="LAST_NAME")
   })
   @Select("SELECT #id, #firstName, #lastName FROM EMPLOYEE WHERE EMP_ID = @id ")
   List<Employee> getEmployeesForDepartment(int id);

Configuration with XML

XML configuration will be somewhat familiar, however there is a lot of room for improvement. Most notably, we'll support the following:

  • Result and Parameter mappings to fields, constructor parameters and JavaBeans properties.
    • Yet again, C# will rule with regard to constructor parameters, because they can be named. Java will be limited to ordinal constructor parameter mappings.
  • Join mapping and groupBy (N+1 selects solution) can be made much less verbose. We can reduce the need to create sub-resultmaps as well as make the syntax more clearly describe what the intent is (see Collection element below).
  • All Result and Parameter Maps will be "auto-mapped" so only columns that are mismatched against their properties need to be described
    • this dates back to a heated discussion from years back about ignoring missing columns, and/or auto-map extra columns
  • Improved/Simplified typehandler implementation and possibly data type conversion filters
  • The biggest change will be that the XML files will sit beside their Mapper.class counterparts. So EmployeeMapper.xml will be loaded for EmployeeMapper.class in the same classpath by calling something like config.addMapper(EmployeeMapper.class).

Important: People who wish to ignore the new convention and annotation based configuration can do so without missing out on any features. We're not changing the paradigm, we're adding to it. We may even allow people to set their configuration level by specifying "Convention", "Annotation", "XML" or "Java". That is, if they set it to XML, no convention or annotation configuration will be attempted. But even by setting Annotation, XML will still be attempted. So there's definitely a priority order to the configuration levels. Obviously disabling Java API configuration will not be possible.

<Mapper>
   
   <ResultMap id="selectACompanyWithJoin" resultClass="Company">
   <Constructor column="C.COMP_ID"/>
   <Constructor column="C.NAME"/>
   
   <Property name="departments.id" column="D.DEPT_ID"/>
   <Property name="departments.name" column="D.NAME"/>
   <Property name="departments.employee.id" column="E.EMP_ID"/>
   <Property name="departments.employee.firstName" column="E.FIRST_NAME"/>
   <Property name="departments.employee.lastName" column="E.LAST_NAME"/>
   
   <Collection type="Department.class" property="departments" groupBy="id"/>
   <Collection type="Employee.class" property="departments.employees" groupBy="departments.id"/>
   </ResultMap>
   
   <Select id="selectACompanyWithJoin" parameters="id:int,order:string">
   SELECT
   #{id},
   #{name},
   #{departments.id},
   #{departments.name},
   #{departments.employees.id},
   #{departments.employee{,
   #{departments.employees.lastName}
   FROM COMPANY C
   INNER JOIN DEPARTMENT D ON C.COMP_ID = D.COMP_ID
   INNER JOIN EMPLOYEE E ON D.DEPT_ID = E.DEPT_ID
   WHERE
   C.COMP_ID = @{id}
   ORDER BY ${order}
   </Select>
   
   </Mapper>

You'll notice a number of changes, especially with the select statement itself. The markup has changed to:

* #{column} is an inline result column, optional, and redundant in the above examle -- but
   ultimately should be able to describe most any column mapping situation just like an external
   result map.  Many people won't like this, but it will make others very happy.  Sorry, it will
   be a bit confusing that that # is the token used for results now, rather than parameters, but
   it makes more sense.
   
   * @{property} is an inline parameter mapping to one of the provided parameters and its
   properties.  Much like inline parameters are today, very little difference other than the more
   common syntax using the @ symbol for parameters.
   
   * ${property} is an inline substring replacement and is the inspiration for the new syntax and
   does not change at all really.  At most we may introduce simple SQL injection protection,
   possibly optional.

Configuration with Java API

Without going into details, all of the other configuration approaches will make use of a clean, test driven, configuration API that is suitable to release and support as a public API. The entire framework should be configurable from this Java API. In general, it will look something like this:

Reader configFile = Resources.loadAsReader("MapperConfig.xml"); //contains DB connection information etc.
Configuration config = new Configuration(configFile);
config.addMapper (EmployeeMapper.class); // Configures by convention, annotation, and from EmployeeMapper.xml in the same path.
//
//...more config of statements, result maps, parameter maps and cache contexts....syntax unknown...
//
MapperFactory factory = config.buildMapperFactory();
EmployeeMapper empMapper = factory.getMapper(EmployeeMapper.class); //generics will allow us to avoid the cast

Caching

  • Clinton says: Caching has never been very good in iBATIS...at least not as good as most ORMs, like TopLink and Hibernate. Unfortunately this is the reality with an SQL Mapping approach. Without introducing the concept of object identity and a number of other ORMish qualities, we can't support caching to the same level as the others. I don't believe caching is worth the loss of the core principles that make iBATIS different. So, if iBATIS caching is going to remain fairly basic, it should at least be easy. To date, cache configuration has been more work than it's worth. So, I believe we should make caching dead simple to configure, to achieve 80% of the value with 20% of the code. Any cases that require more advanced caching can write a custom cache above the persistence layer. For what it's worth, caching at the persistence layer is the least effective. Why? Because it's without user context. The best place to cache is at the web tier using a fragment caching approach that can understand more about user interactions and usage patterns that the persistence tier can never understand. In testing done with the Middleware Company years ago, we found business/context aware caches outperformed even the most advanced generic/holistic ORM caches every time.

So how is caching configured now?

As an annotation...

@CacheContext("Employee")
   public class EmployeeMapper {
   void insertEmployee(Employee emp);
   void updateEmployee(Employee emp);
   void deleteEmployee(Employee emp);
   Employee getEmployee(int id);
   List<Employee> findEmployeesLike(Employee emp);
   }

Or in XML...

<Mapper cacheContext="Employee">
   <Insert id="insertEmployee" ...> ... </Insert>
   <Update id="updateEmployee" ...> ... </Update>
   <Delete id="deleteEmployee" ...> ... </Delete>
   <Select id="getEmployee" ...> ... </Select>
   <Select id="findEmployeesLike" ...> ... </Select>
   </Mapper>

The Cache Context basically links a bunch of mapped statements together. The results of any statements that query for objects and return either collections or single objects will be cached similar to the way they are today – but without the highly verbose configuration. Any mapped statements in the cache context that modify data (insert/update/delete) will flush the cache.

If there's a need to flush on some other statement (like a weird stored proc that updates and queries or something), it can be overridden using a simple annotation.

@flushCache
   List<Employee> updateAndGetSpecialEmployees();

Or XML...

<Select id="findEmployeesLike" flushCache="true"...> ... </Select>

Dynamic SQL

Thanks to Brandon Goodin, iBATIS has one of the most powerful dynamic SQL facilities around. There's nothing else quite like it. And now I'm about to suggest we completely change it.

iBATIS has always been heavily XML based. However, as it turns out, XML sucks for procedural logic. Unfortunately the Dynamic SQL as it is today is procedural logic expressed in XML. In addition to the simple fact that XML sucks at this, there are also a number of syntactic issues with special characters. Normally we handle special characters by simply wrapping the entire statement in a CDATA section. But with Dynamic SQL, we can end up with relevant XML that we need within the CDATA section. This creates a mess of procedural logic, SQL fragments and CDATA sections. While still better than a lot of alternatives, it's simply not the best we can do.

As I said before, I consider the Dynamic SQL facility to be "code" (as opposed to configuration or meta information). Code should be in a general purpose programming language like Java. So, what I will suggest is that we rebuild the Dynamic SQL facility as a really nice Java API that is every bit as good as the Dynamic SQL facility. This API can be use inside very simple Dynamic SQL Source classes.

Now that everyone is confused, let's use an example:

OldWay.xml
<select id="dynamicGetAccountList" parameterClass="Account" resultMap="account-result" >
   select * from ACCOUNT
   <dynamic prepend="WHERE">
   <isNotNull prepend="AND" property="FirstName">
   ( ACC_FIRST_NAME = #FirstName#
   <isNotNull prepend="OR" property="LastName">
   ACC_LAST_NAME = #LastName#
   </isNotNull>
   )
   </isNotNull>
   <isNotNull prepend="AND" property="EmailAddress">
   ACC_EMAIL like #EmailAddress#
   </isNotNull>
   <isGreaterThan prepend="AND" property="Id" compareValue="0">
   ACC_ID = #Id#
   </isGreaterThan>
   </dynamic>
   order by ACC_LAST_NAME
   </select>
NewWay.java
// Warning: This is not well thought out.  There's a lot we can do with
   // anonymous inner classes, class initializers, generics and chained APIs (JMock)
   // or even scripting languages.  This is simply for the sake of argument.
   public class GetAccountListSQL extends SQLSource {
   public String getSQL(Object param) {
   Account acct = (Account) param;  // Generics might help eliminate this
       append("select * from ACCOUNT");
   prepend("WHERE"); //prepends before next append
       if (exists(acct.getEmailAddress())) {
   append("AND", "ACC_EMAIL like #EmailAddress#"); // append with prepend, first will be overridden
       }
   if (greaterThan(0,acct.getID())) {
   append("AND", "ACC_ID = #ID#");
   }
   prepend(); //clear prepend if any
       append ("order by ACCT_LAST_NAME");
   }
   }

This SQLSource can then be used with an annotation:

@SQLSource(GetAccountListSQL.class)
List<Accout> getAccountList(Account acct);

Or through XML:

<Select id="getAccountList" source="org.apache.GetAccountListSQL" ...> ... </Select>

I think this approach is much cleaner and keeps code where code should be. It also makes it possible to easily introduce new types of SQLSources so that SQL can be generated by scripting languages or (if anyone is really tied to it) even XML...

XML Parameters and Results

We will continue to support primitives, POJOs, JavaBeans and Map types for parameters and results. XML support will also still exist, but will change dramatically. Instead of being supported as a first class type, we'll support it as a higher level type for all object types.

I was thinking of something as simple as introducing a standard iBATIS utility for serializing primitives, POJOs, JavaBeans and Maps. Or we could just tell people to use something like XStream.

Here's a ridiculously short example that should get the point accross.

return As.XML(empMapper.getEmployee(As.Integer("<value>5</value>")));

Hopefully you get the idea...basically XML is a higher level transformation to and from any object type. The benefit is that if you want an XML interface on top of your existing mapped statements, you don't have to remap them all to xml types...

Features Recommended for Exclusion from iBATIS 3.0

  • DAO (deprecated in 2.x)
  • PaginatedList (deprecated in 2.x)
  • NullValue (reason: it's ugly, confusing and pointless)
  • MaxSessions, MaxTransactions, MaxRequests (sessions and requests themselves may not exist in the new design, and TX configuration is the container's job)

Timeline

It's way too early for a timeline. The best we can do is guess at how long it will be before we have a Beta quality release. Perhaps below each committer can give a gut check:

Brandon:
Brice:
Clinton: 6 - 12 months
Gilles:
Jeff:
Jon:
Larry: 12-18 months
Nathan:
Roberto:
Ron:
Sven:
Ted:

ibatis 3.0 (iBATIS DataMapper)
JPA (iBATIS DataMapper)

In the section titled "Standard SQL? What's that?" I have an idea (well, OK, I stole it from the Hibernate guys and simplified it) that would make it easy for people using non-standard SQL. If we make the SQL generation done via an interface it can be pluggable. Then that section becomes:

===
We are not going to go crazy with proprietary SQL support. In fact, we are not going to support it at all.

We will support the most standard SQL that works for most databases. This shouldn't be a problem considering the basic queries we're supporting in the convention-based generation.

We will also provide a plug-in mechanism for users to extend that support to include vendor-specific SQL generation. Those plug-ins will most likely never become a supported part of iBATIS, but the authors and users are free to discuss them on the lists, or manage them however they see fit.

...and as always, any crazy advanced SQL can be done by hand.
===

I also think it would be great to have something concrete that describes the life cycle of a mapped statement's execution. Basically, what happens when and where, and ideally, each stage of that life cycle would be a separate pluggable component.

If someone wants to replace any part of the process, they should be able to do that easily, and at either globally or at the statement level.

IMO, that would make the code simpler (smaller blocks), and also facilitate the use of iBATIS for other types of query creation and data mapping (like the guy who was wanting to do LDAP data mapping).

Posted by Larry Meadors at Jan 09, 2007 09:39

>> I'm not opposed to the idea. However, I can't name a single
>> vendor specific feature that we could generate that would
>> warrant a pluggable generation mechanism.

I can: Oracle key generation is still manual, and since I use Oracle, it's important.

On another note, re: Transactions, another common question is "How do I roll back a transaction?", so I would like to see an explicit rollback() method on the transaction.

Transaction tx = factory.startTransaction(isolationLevel);
      try {
      // do work
        tx.commit();
      } catch(whatever) {
      tx.rollback();
      } finally {
      tx.end();
      }

Yes, I know it's redundant, but I think for most users, it's clearer than "end" meaning "end and rollback unless you have committed". At least for this user it is.

Larry

Posted by Larry Meadors at Jan 10, 2007 06:40

>> Oracle key generation is still manual,

You're right. Oracle will likely always be that way. To support generation of insert statements, we'll need that. (my original comment removed)

>> tx.rollback();

Argh. As much as I hate to do it, we might have to. I agree with the confusion, a lot of people ask about that. In addition to that there are problems with automatically calling (or not calling) rollback() with differences in drivers. For example, with Oracle, calling rollback is expensive, even if you didn't do anything (which is why we have the transactionRequired option). But with Sybase, NOT calling rollback, even after commit, causes the driver to be left in some weird inconsistent state. So I guess just leaving it up to the developers is the best thing.

I will say that I don't think we need both rollback() and end(). We should either use start/commit/end semantics, or start/commit/rollback.

Another option is to make rollback balk if nothing has been done within the transaction, so we could still do something like:

Transaction tx = factory.startTransaction(isolationLevel);
      try {
      // do work
        tx.commit();
      } finally {
      tx.rollbackIfNecessary();
      }

In a sense, we'd just renaming end().

Good thoughts.

Clinton

Posted by Clinton Begin at Jan 10, 2007 09:08

Caching

"Any cases that require more advanced caching can write a custom cache above the persistence layer."

It sounds like we are forcing a closed option on caching rather than opening up the ability for people to "plugin" their own caching. To me it would seem to be a better option to provide a few good hooks that can help users to implement more custom caching rather than lock them out. This may even lead to some possible OID like caching strategies.

Some potential areas may include:

  • Providing access at the time an object is created
  • Expose primary key values pre object creation so that users can substitute an existing cached object into the results if they so desire.
  • Expose information about the sql map being called (id, resultclass, etc...)
  • Provide a way to simply expose all this to a class that implements a Cache interface (or something like). It would be up to them to handle the complicated internals. iBATIS would not be responsible for configuring it in any way. It just gives them information that could be valuable for them to hook in their own cache.

Dynamic SQL

I am completely on board with a Java based Dynamic SQL at the heart of any Dynamic SQL functionality. However, I do not believe the statements regarding XML "sucking" for this kind of use is accurate. IMO the XML markup can be less cluttered, less verbose, and more capable of expressing intention without losing any power. Most, if not all, of the dynamic SQL i have seen is not complicated by the fact that it was XML but rather that it was not a tight and simple set of tags. The nice thing about XML is that it is like being able to have easily nestable methods with optional parameters. Along with that a simple EL would allow for conditional decisions on inclusion of tag body content. You still get completion via a schema or DTD in any good xml editor. On top of this users may want to continue to keep their SQL organized nicely and cleanly together in an xml file. The only thing you would lose is completion on object properties. But, that is inherent in most all that we are doing. It is my opinion that retaining an implementation of the XML expressed dynamic SQL functionality as part of iBATIS is important and valuable.

On a related note, the two examples provided do not express the same SQL statement.

Posted by Brandon Goodin at Jan 10, 2007 16:13

Dynamic SQL

While adding a Java based Dynamic SQL is an additional opportunity, I should consider XML as the preferred way of expressing complicated SQL code. The reason is that you get the SQL out of the code (which is always a good thing imho) and you can customize the sql with no need to touch the code.
Consider the following Oracle related problem
The same product is installed in two different sites and each site for table A generate very different data for column Col. If you have an histogram on column C you may modify the SQL where caluse to use a bind variable or a fixed value helping the CBO (Cost Base Optimizer) whether to choose an index or not. With the sql in the XML you can customize your product for every site, if you use code no.
I think it's worth mention that XML config files can be edited by DBAs while with Java code it's more difficult.
So I second Brandon opionion and suggest to find some way to improve Dynamic SQL functionality both in terms of power and usability

Posted by Giovanni Cuccu at Jan 11, 2007 04:32

I think this proposal is a step in the right direction. While iBATIS is very clean and simple, there is certainly room for improvement. I'm particularly excited about the idea of having SQL generation based on convention, I think it'll be a time-saver feature.

Some comments/questions:

  • Will iBATIS 3.0 be Java 5.0 compatible only? If I understand correctly, some of the new features like Interface Binding will require features only available in Java 5.0. Will iBATIS 3.0 be still usable in Java 1.4, maybe without those features?
  • Interface Binding. The examples provided make use of only one method argument. What happens when there is more than one argument? Will iBATIS wrap them all in a Map automatically? How will the arguments be identified?
  • I like the idea of configuring iBATIS by convention. I agree with Clinton that it will be especially useful for inserts, updates and deletes. We (me and my workmates) rarely do anything fancy with those, so we'd happily let iBATIS generate them for us. However, I'd like to have some flexibility with regards to convention. For instance, on the examples posted it is assumed that the table name and the class name will match. I feel this is forcing users to use a certain database design convention, some users might want to pluralize table names (à la Ruby on Rails/Active Record) or add a prefix (TBL_). It could be easy to override those attributes via annotations or XML configuration, but I think it'd be a good idea to have a pluggable mechanism. Something like a Convention interface that could be implemented by the user. iBATIS would provide a default implementation (DefaultConvention?). This interface could define methods like getTableNameFor(Class clazz), getIdForSelectOne(Class clazz), getIdForSelectMany(Class clazz) etc. Users could implement all those methods or simply override the default implementation where necessary. I see this as a global custom convention mechanism.
  • I agree with Brandon. While I can see some people might prefer a less verbose Java based Dynamic SQL, I'm personally pretty happy with the XML based configuration system. It's definitely more verbose but I don't think it's that bad. It's probably a question of finding a better less verbose design, coupled with a simple EL for conditional decisions. Personally I believe the XML code is cleaner, it's easier to get lost on the Java based one. I'm afraid the Java based SQL would get messy with more complex queries. It's probably a question of taste, but both options should be available.
Posted by Aitor Imaz at Jan 11, 2007 05:10

I must chime in on the dynamic SQL function too. Even though I understand and agree with the sentiment that XML is not a programming language, still I belive we must keep the dynamic SQL capability in XML. For me, the overriding consideration is that DBAs in large shops usually require an SQL review before going to production. If all SQL is in XML files, then it is easy to give the SQL/XML to the DBAs. If not, then there is a huge hassle involved with finding and assembling the SQL for review.

This basic idea of keeping the SQL separate from the code that executes it was the primary factor in my original decision to introduce iBATIS into my projects years ago. Anyone who works in a shop where there is an SQL review needs this kind of function. I think this simple architectural concept has made iBATIS as accepted as it is in large shops. I would hate to see it go - because I'd have to find another solution

But I do think that the dynamic SQL "language" could be improved. I'm in agreement with the old idea of making it look more like JSTL.

Posted by Jeff Butler at Jan 11, 2007 11:11

>> Will iBATIS 3.0 be Java 5.0 compatible only?

Interface binding can actually be done since 1.3 IIRC. It only required the dynamic proxy facilities. However, other features like annotation based config will require Java 5. My working assumption at this point is that we should just support Java 5. The two reasons being:

  1. By the time we have a first release, Java 5 will be 3 years old .
  2. iBATIS 2.x is still a very good solution for Java 1.4.

>> Interface Binding. The examples provided make use of only one method argument.

I've given this a lot of thought. We could support multiple parameters, but in Java (unfortunately) you'll need to name the parameters somehow, or use a messy ordinal notation. For example:

fetchEmployeeByFirstAndLastName (String firstName, String lastName);

Unfotunately in Java we lose the names of the parameters, so the best we could do is something like:

SELECT * FROM EMPLOYEE WHERE FNAME = @1 AND LNAME @2

In C#, the names of the parameters are preserved and can be accessed via reflection, so the SQL can make use of them like this:

SELECT * FROM EMPLOYEE WHERE FNAME = @firstName AND LNAME @lastName

So I'm almost inclined to leave it as a single parameter only. Then, if you want multiple params, you set them in a Map, just like you do today (and in C# 3.0 you can use an anonymous type too).

Oh how I wish Sun would shamelessly copy some C# features for the sake of the future of Java.

Cheers,
clinton

Posted by Clinton Begin at Jan 11, 2007 23:23

>> Interface Binding. The examples provided make use of only one method argument.

> I've given this a lot of thought. We could support multiple parameters, but
> in Java (unfortunately) you'll need to name the parameters somehow, or use
> a messy ordinal notation.

Brainstorming here..thinking as I type.

Could we use annotations to provide the names?

@ParameterNames("firstName", "lastName")
fetchEmployeeByFirstAndLastName (String firstName, String lastName);

Kind of redundant, because you have the names twice, but it should work OK, no?

Larry

Posted by Larry Meadors at Jan 12, 2007 06:51

>> @ParameterNames("firstName", "lastName")

Yeah, we could do that. We can default it to @1 and @2 unless they're overridden by the @ParameterNames annotation...at least until Java can reflect on method names.

The other way we could do it is by using the method name...

>> fetchEmployeeByFirstAndLastName (String firstName, String lastName);

Changing it to "fetchEmployeeByFirstNameAndLastName" lets us grab everything after the "By" and delimiit by "And" leaving only "FirstName" and "LastName".

Cheers,
Clinton

Posted by Clinton Begin at Jan 13, 2007 17:39

PS: In my last post it might not be clear that I'm suggesting doing all 3 approaches....

1) Look for the annotation, use it if there.
1b) C# can look at the parameter names and use those.
2) Look for a "By" clause in the method name.
3) Default to  (1,)2,@3.

It actually won't hurt anything to register the ordinal parameters and the named parameters so that you can use them interchangeably. This will avoid breaking existing statements if they change the name of the method to be more specific.

Clinton

Posted by Clinton Begin at Jan 13, 2007 17:42

I don't know what I typed in that last message, but apparently it's now a text field...thanks confluence...

Posted by Clinton Begin at Jan 13, 2007 17:43

Another feature I'd vote to put on the chopping block for i3 is lazy loading, which seems to be right up there at the top of the list of things that break frequently and we get questions about on the list.

IMO, it is a fragile obfuscation technique that adds no value in any situation that I can think of.

Let's consider an order/orderline type of thing...

In a web application, the request/response granularity is large enough that you need to know if you need the order lines at the same time that you need the order - because once the request is done, you can't go back and get it. Even with ajax you are sending another request.

In a desktop application, you reference to your dao layer to get the order anyway, so if you get an order then later need the order lines it's a method call either way (order.getOrderLineList() or orderDao.getOrderLineList(myOrder.getOrderId()), so there is not really any value added there.

My vote on "Let's give lazy-loading the axe." is +++1.

Posted by Larry Meadors at Jan 24, 2007 11:23

CONFIGURATION API

 Just a quick question on this:

Are we thinking of providing the ability to modify SQL statements at runtime via the API (without requiring server restarts etc)?

Posted by Abdullah Kauchali at Jan 25, 2007 04:07

> Are we thinking of providing the ability to modify SQL statements
> at runtime via the API (without requiring server restarts etc)?

Yes, I think that is one goal of the new configuration API.

One thing that I am wondering though is how much will our ability to do this depend on the class loader? I am not convinced that there is a lot that we can do to make this happen if the class loader caches the resources.

Posted by Larry Meadors at Jan 25, 2007 06:28

>Yes, I think that is one goal of the new configuration API.

Great!

>One thing that I am wondering though is how much will our
> ability to do this depend on the class loader? I am not
> convinced that there is a lot that we can do to make this
> happen if the class loader caches the resources.

IMHO, I don't think we should be using classpath based resource loading to read SQLMap (XML) files in the first place. XML files should be read via standard java.io into configuration classes which then get stored as singletons or global lists.

Posted by Abdullah Kauchali at Jan 25, 2007 08:55

Well, I think the way we do it (using the class loader) is probably the simplest way, but since we are exposing a public API, you are free to implement it any way you wish.

Posted by Larry Meadors at Jan 26, 2007 06:38

hi! I come from China, we have a project name "springside",I use iBatis 2 years,we like this have new feature support Annotations,and we plan extend iBatis 2.3,let it support dynamic SQL ,configuration with Annotations at our project.

Posted by Steven Su at Jan 31, 2007 08:17

<blockquote>Improved/Simplified typehandler implementation and possibly data type conversion filters</blockquote>

I suggest that TypeHandlers should be modal, e.g. could be passed enum constants from the xml. I find myself doing quite a lot of repetitive type handling, currently achieved with shallow subclasses to differentiate the functionality. It might look like this:

<result property="date" column="SSDDCURD"
      typeHandler="datetimeTypeHandler"
      typeMode="datetimeTypeHandler.MODE.Date"/>
      
      <result property="time" column="SSDDCURT"
      typeHandler="datetimeTypeHandler"
      typeMode="datetimeTypeHandler.MODE.Time"/>
Posted by Reuben Firmin at Feb 06, 2007 12:14

iBATIS team.

This whiteboard really has great ideas and features. If implemented iBATIS would be a killer for Java 1.5+ apps.

I think there should be a link from the iBATIS homepage to this whiteboard. I only stumbled across it since I was reading a few posts from the developer archive.

Posted by Claus Ibsen at Feb 17, 2007 05:17

We should add the Improved Dynamic SQL Whiteboard to this page/discussion. I think it's pretty clear that we want to retain the xml dynamic sql alongside the new dynamic capabilities.

Posted by Brandon Goodin at Mar 08, 2007 16:55

I created a project called daozero(http://sourceforge.net/projects/dao-zero), just released version 0.5, and I find it's something similiar with interface binding.
I hope my thought below on dao-zero may be helpful with interface binding feature.
daozero is a spring factory bean that creates dynamic proxy bean to implement interface and call ibatis for interface.
In most cases, it just takes method name as ibatis statement name, and depends on method signature and statement meta data and a little conventions to decide to how to invoke ibatis.
For example,
1. multiple parameters could be combined into one parameter map. Currently I decide that method parameters have to keep the same occurance with statement parameters. I'm also trying to introduce annotation to elimite this limitation. And if for jdk 1.4, additional method signature along with parameter names is allowed to declare to work too. Also, additional method signature could be used to specify statement whose name is different with method name. I don't think redundancy here is problem because in mose cases keeping method name same as statement are enough and there're little occasion need for additional signature to work.
2. method sigature is also used to result transformation and parameter transformation and batch mode, etc. daozero can transform list result to array/set/iterator/other list; also can invoke ibatis for each element in parameters whose type is array/set/iterator/list, with batch mode option.

And I even also considered to support to generate statement by method convention and most of thought are same as ibatis, such as generate a "SELECT address FROM user WHERE name like ..." from a method "getAddressFromUserWithNameLike(...)", but I think it's not a good idea. it work for simple cases but unlike hibernate, there're no much accurate metadata for ibatis, so it will become complicated and error prone for more real case. Perhaps it is better to be an additional tool.

And for dynamic SQL, is following way suitable?
for
<select ...>
SQL "select * from user WHERE name=#name# AND address=#address#
</select>
if #address# is null or something else, truncate the SQL as "select * from user WHERE name=#name#" automatically. a additonal attribute "dynamic=true/false" could be used to declare whether it's dynamic statement.
Or another way is introduce special symbol. for example:
<select ...>
SQL "select * from user WHERE name=#name# AND address=@ address @
</select>
the @...@ means dynamic parameters and can be ignored if null, the "AND" before "address=@ address @" should be ignored automatically. additonal attribute "dynamic=true/false" is no need and become more flexible.

Posted by Yi-ting Qiang at May 14, 2007 10:47; last updated at May 14, 2007 21:33

Dynamic SQL
The main reason (really the only one...) that lead me to evaluate iBATIS and start to introduce it in my development group was that while letting java coders to do their job with objects it allows me to keep SQL code **OUT** of java classes.
We have legacy applications that deals with legacy DB's that contains tons of embedded, more or less dynamic, SQL that have become a nightmare to maintain.
iBATIS is THE SOLUTION because, as far as the result set structure does not change, it allows DBA's to maintain and optimize SQL as DB's evolve without touching a line of code.

So i'm not contrary to allow to manipulate or generate SQL from within java code as far as you continue to support FULL xml configuration for any kind of SQL code.
If i'd be forced to embed again SQL in java classes, then i'd have to develop an in house solution and quit iBATIS.

Posted by Gabriele Garuglieri at May 24, 2007 02:29

I re-read this list of comments, and I don't think anyone is suggesting that we eliminate the ability to use XML to describe your SQL statements.

What is being said is that you will be able to use iBATIS without as much (or possibly any) XML.

Posted by Larry Meadors at May 25, 2007 07:21

Wow.

There are some really great ideas here. Personally, I'm mostly looking forward to being able to populate objects through constructors.

I would like to ask that you consider supporting older versions of the JDK, even if some of the features (annotations obviously) are not available. However, if this would cause problems, then forget it; at some point we all have to move on.

I would like to add a possible idea, that I don't think has been covered here. The possibility of setting properties from field composites. I think this would be especially useful in typehandlers, where I may be creating something like a CompositeIdentifier, which is made up from two or three database fields. Not sure of the syntax for this though.

Posted by Ray at Jun 01, 2007 06:02

I agree with Gabriele Garuglieri comment. I know nobody is suggesting to eliminate XML, but some of these new features can change the base concepts of IBATIS, which IMHO are:

  • FULL control over SQL, that is, no SQL automatic generation
  • ALL SQL is out of the code
  • very simple API

So I think IBATIS team need to take care with it because they can change the direction of the framework, moving it to something like Hibernate, OJB, Toplink and others.
I believe that all users of IBATIS choose it beacuse it's concept is different from the others, so changes in it should be done with careful.

Posted by Anderson Souza at Jun 09, 2007 17:25; last updated at Jun 09, 2007 22:29

I guess that it would be a good thing if the object load engine doesn't make necessary to define the classes using the JavaBeans notation (get/set pairs) - maybe looking for attributes rather than methods...

Cheers!

Posted by Fabio M. Nunes at Jun 15, 2007 13:39

I think IDE for iBATIS to accelerate development is seldom. I have create an IntelliJ IDEA plugin to accelerate development. The url is http://docs.google.com/Doc?id=dc73pj2h_6db54mm. I think a good idea to make development easy should be considered.

Posted by jacky chan at Jun 22, 2007 06:29

I would like to say something about cache.
At first, I have following design principles for cache:
1, One aim: improve speed by share data in restricted memory space.
2, Simple: simple to use, simple to implement.
3, Cache on need, on time, not may need, not all time.
4, Share furthest.

Come back to ibatis, how to say?

1, Do push out too many concepts which are generated in ibatis developing process.
The users of ibatis may not concern how to configure and use cache.
What they care about is performance.

2, Don't try to resolve all problems no matter their content.

How to do in ibatis?
Though ibatis can't implement cache like orm,
ibatis absolutely could do its owner cache, not the cache which is operated like by (key, cachedObject),
for example, the plane cache which supports cache by condition, not by key. I like provide simple code:

Public class Cache {
@param width the cacheing property number
@param length the ResultSet size.
@param limitedTime the max alive time for this cache.
@param maxInactiveTime the max idle time for this cache
public Cache(int width, int length, long limitedTime, long maxInactiveTime){
// do something
}

@param rs
@param cachingPropNames
@param rangeBitSet to specified the cache segment
public void cacheResult(java.sql.ResultSet rs, java.util.List<java.lang.String> cachingPropNames,
java.util.BitSet rangeBitSet) {
// do something
}

public java.lang.Object[][] getResult(java.util.Collection<java.lang.String> propNames,
int beginIndex, int endIndex)

Unknown macro: {// do something} }

How to use it?

Cache cache = new Cache(10, 10000, 1000*30/second/, 1000*5/second/);
BitSet bs = new BitSet();
bs.set(0);bs.set(200); // cache first page.
bs.set(200); bs.set(400); // cache next page.

bs.set(9800);bs.set(10000); // cache last page.

cache.cacheResult(rs, Arrays.asList("id", "name"), bs);

What this sample say?
Cache on need, cache on time.

When and how to flush the cache?
by intersect condition when update or delete.

What's benefit of this plane cache?
high performance, effective space using, easy to use.

Of course there is a lot of to do to complete this plane cache.

Posted by lihy70 at Jun 26, 2007 00:52; last updated at Jun 26, 2007 01:12

It will be much more convenient and more effective if Ibatis support more flexible parameter map.

If Ibatis can handle classes like following, I should consider it as being perfect; 

Maybe it can be solved by dynamic class enhancement. 

class User {
      private String id, pwd;
      private boolean gender; //true:male; false:female
      	private int age; 
      private String name; 
       
      @SqlMethod("SELECT * FROM USERTABLE WHERE AGE>=18"public static List<User> findAdults() {
      return null;
      } 
       
      @SqlMethod("SELECT * FROM USERTABLE WHERE GENDER=FALSE AND AGE>#this.age#")
      
      public List<User> findElderSister() {
      return null;
      } 
       
      @SqlMethod("DELETE FROM USERTABLE WHERE AGE=#user.age# AND GENDER=#user.pwd# AND NAME=#name#")
      public static void removeSameAgeAndSameGender(@SqlParam("user") User user, @SqlParam("name") String name) {
      } 
      } 
Posted by webwheel at Jun 28, 2007 05:21

The technique used in Google's GWT for JavaScript inclusion seems very convenient.

http://code.google.com/webtoolkit/documentation/com.google.gwt.doc.DeveloperGuide.JavaScriptNativeInterface.html

This may be a tidier method than using annotations for SQL strings. One of the benefits of having SQL in XML files (and not embedded in JDBC calls) is that it can be easily cut and paste into traditional SQL editing tools.

class PersonDao {
      
      public native List<Person> findPeopleByAge(Long age) /*sql{
      
      SELECT  first_name as firstName,
      last_name as lastName
      FROM USERTABLE
      WHERE AGE = #age:NUMERIC#
      
      }sql*/
      
      }
Posted by Paul Tuckey at Jul 01, 2007 16:28

Ick.

Please don't take this personally, but I think that would suck.

Other than using a preprocessor, how would we access that?

My vote is that we stick with the annotation or use XML if you want it in a state that is easy to copy/paste.

Posted by Larry Meadors at Jul 02, 2007 07:29

It would be great if the new iBatis allows securing the data source configuration.
Currently the sqlMapConfig.xml includes database username and password from properties file.
This forces the programmer to keep user name and password in plain text.
There should be an option to create the sqlMapConfig in the Java code (less XML files) or some other way to keep the password encrypted.

Posted by Petar Stoqnov at Jul 02, 2007 09:33

This has been discussed a lot, but the bottom line is that if an intruder has access to your applications files, they already own you.

The added complexity adds almost no value: Securing your file system will not only better protect your database, but your application code as well.

IMO, this is a non-feature...however...iBATIS 3 will most likely include xml configuration only as an after thought - the intent is that you will be able to configure it entirely with code if you want, or entirely however else you want to, including Spring, or any other framework that you want to use that supports the creation and injection of POJOs.

Posted by Larry Meadors at Jul 02, 2007 09:41

Things you say about the password in plain text are true.
But consider these points :

  • The application which uses iBatis is not web based - it works on the desktop/intranet. Anybody who access the maschine can access the password.
  • The application is web based and hosted from foreign company. You cannot control how their employees configure every different server.
  • It is much easier to secure a password than a whole server (securing server needs deep knowledge).
  • It is not bad to have second protection line (and not to rely only to administrator).

Every system is stronger as strong is its weakest point. After all, if some feature is often requested, it probably has a value (for someone).

Posted by Petar Stoqnov at Jul 03, 2007 12:45

Larry, this is in response to your rather harsh comment on Paul Tuckey's post above regarding embedding the sql in the body of the method.

I have to disagree; from  an iBatis end-user's perspective Paul Tuckey's technique seems a much cleaner and better solution. The sql after all is in fact the 'body' of the method, not a signature.  Sql in an annotation is a messy solution - the examples given above are unrealistically simple queries (which look okay in an annotation). More often than not queries are much more substantial than this, embedding these in an annotation would be in your words "ick".

It seems you have dismissed the Paul Tuckey method because it is harder to develop, not because it is a poorer solution. That clearly is not the ideal way of delivering the best outcome for iBatis 3.

 My vote is for embedding the SQL in the method as described by Paul Tuckey in the GWT style.

Posted by Paul Wilton at Jul 10, 2007 02:50

I'm sorry, but I do not see how adding a precompiler (which is the only way I can see how to implement this) makes for a cleaner and is a better solution.

In this case, I think an XML file in the same package with the same name as the class, and a mapped statement with the same name as the method would be simpler.

To use Paul's example:

class PersonDao {
      public List<Person> findPeopleByAge(Long age);
      }

PersonDao.xml:

<sqlmap namespace="Person">
      <select id="findPeopleByAge">
      SELECT
      first_name as firstName,
      last_name as lastName
      FROM USERTABLE
      WHERE AGE = #value#
      </sqlmap>

Why do I prefer this? It's simpler for me to build (I don't have to write a precompiler) and it's simpler to you compile your app (you can use maven, ant, or javac, I don't care: It all works, and there are not any added plug-ins or task that have to be written and performed every time you build).

If you want to build something like this (even today for iBATIS 2.x) you can, and it'll still be possible for iBATIS 3. Build it and show me how easy it is and maybe I'll change my mind (...but I doubt it).

For today I am still saying "No" on this one...but thanks for pushing back.

Posted by Larry Meadors at Jul 10, 2007 08:21

Support various of object type, like pojo, SDO DataObject. provide the extensive point for extending.
It is better to make ibatis as a kind of SDO DAS.
Now, it is hard to extend ibatis to support SDO dataobject.

Posted by liaoxingya at Jul 11, 2007 15:50

Maybe you can design a way of configuring ibatis using groovy.

Might be less verbose, and might get well in designing dynamic queries and result mapping

Posted by Isidoro Treviño at Jul 11, 2007 16:17
  • I'd like the xml sql to allow for ANY propriatery sql, especially stored procedures. It's just a string, and what allows me to tune my performance. I now have DBA's editing XML, most don't know Java or C#, so I'd skip comands in java. They LOVE ibatis and hate ORM.
  • As far as caching, I'd like to use the concurrency package from java. That would make it code readable imo, w/ some simpler time decay. And make it easy to implement "my own caching and decay". Document example of how to "broadcast" flushes of result sets using 3rd party.
  • Add support for betwix "toXML()" - from XML to/for CRUD. (I do this now)
  • Add optional CoR/IoC "dispatcher", so that given a map(name/value) or xml string, it routes to right maped(named)statment. (I do this now)
  • Have a in person meeting of key people.
Posted by Vic Cekvenich at Jul 16, 2007 16:34

I'd like to add my vote for better modularity or easier extension points so that we could extend iBATIS for LDAP queries. I love iBATIS, and it would be a great fit for shops that use LDAP.

Let us know how we can contribute to make this happen!

Posted by Gary Thomas at Jul 20, 2007 22:53

Must read:
http://en.wikipedia.org/wiki/Memcached

.V

Posted by Vic Cekvenich at Jul 24, 2007 07:25

The given examples for annotations are good enaugh,
but I like iBATIS as it is possible to write and configure sql statements outside of code.

I suggest to add annotations also for mapping to the statements.

For example, if I have a statement in my sqlMap "findEmployeeById",
I have to write in my code

public Employee findEmployeeById() {
      return (Employee)getClient().queryForObject("findEmployeeById", id);
      }

it would be nice to have something like this

@MapStatement("findEmployeeById", Employee.class)
      public Employee findEmployeeById() {
          return null;
      }
Posted by Aram Mkhitaryan at Jul 25, 2007 00:00; last updated at Jul 25, 2007 00:15

One more suggestion ...

 Yesterday I was mapping a result to the java.util.Set

<resultMap ...>
          ...
      <result ...
      javaType="java.util.Set" select="findEmployees" /> 
      </resultMap> 

and got a lot of trouble because when the lazy loading was enabled the support for Set was broken

 and here comes suggestion ...

what if we have a new attribute "collector"?

<result ...
      javaType="java.util.Set" select="findEmployees"
      collector="myEmployeeCollector" />

this collector will be responsible for handling selected objects and putting in the specified javaType

 this approach will make the code work faster, as now, when lazy loading is not enabled,

as always the result objects are collected in java.util.List and then new HashMap(list) is called for type conversion,

as you see/know, that constructor copies values from the list, but if we have collectors for the defined types (like TypeHandler, TypeHandlerCallback) we could add employees even in our domain objects like office.addEmployee(employee) 

Posted by Aram Mkhitaryan at Jul 25, 2007 00:31

Please consider adding optimistic locking mechanism, as well as bean persistence state management ("new","old","deleted","dirty", etc). Both are very useful in situations where we must create a 'header - detail items' form, e.g product order form, where the header will contain fields such as shipping address, etc, and the detail items will be the ordered product(s). In such form, a user will be able to add, edit, and remove product items, and save the changes LATER when he/she decided that the order is correct and press an OK / Apply button.

Posted by Ming at Jul 25, 2007 02:59

RE: My previous post about doing SQL in remmed out code block
http://opensource.atlassian.com/confluence/oss/display/IBATIS/iBATIS+3.0+Whiteboard?focusedCommentId=8486981#comment-8486981

I've put together a quick prototype that will work as a generator for the existing version of iBATIS.

http://code.google.com/p/ibatis-implgen/

If this feature was added to a future version of iBATIS, I imagine the best way would be a simple APT processor that would extract the SQL at compile time (and validate all other annotations) into XML/properties files and the rest of the annotation processing would be done at runtime.

Posted by Paul Tuckey at Jul 31, 2007 17:25

What really lacked in the previous versions of iBATIS is query by example and clear and convenient dynamic queries. Several times when I suggested using iBATIS for some customers their strongest argument against it was lack of convenient way to form example-like and customer dynamic sql.

I understand that the new version's approach tends to use java code for that, but that still cannot be consedered very convenient. From my experience, propriatery mapping frameworks that used shell-like scripting right inside the mapping XML files were, although out of concept, yet, I must admit it - reasonable. I therefore, still advise us all to consider some simple way of forming dynamic sql.

As for query by axample - my though that it is a must-feature for any modern mapping framework, since most part of query screens can be simplified towards just using this simple feature.

Thank you! Anything else: iBATIS is super, have been using it for Java and .Net and, it just great. The greatest thing - it is simple, thus customers accept it easier)))

Posted by Ilya Bochkov at Aug 06, 2007 06:59

Maybe issues in iBatis 2 could be resolved before we redesign the framework? For some of us, the framework is great as it is and would rather have the current issues we deal with fixed. For instance, serializable results so that lazy loading can be used with other frameworks (like spring):

http://issues.apache.org/jira/browse/IBATIS-409
http://issues.apache.org/jira/browse/IBATIS-419

or approving/completing EHCache implementation:

http://issues.apache.org/jira/browse/IBATIS-73

or even completing Lazy-Load per result:

http://issues.apache.org/jira/browse/IBATIS-246

I think at least completing serializable lazy loading is most important. Many people use Spring now, so not supporting serializability is a major issue!

Otherwise, iBatis 2 is an awesome product. I'd just like to see the previous version's issues worked out before you scrap it completely.

Posted by Tor Tuga at Aug 16, 2007 11:09

Regarding replacing XML when setting up Dynamic SQL: I beg you to consider using something other than Java to configure it. I will post later with a more detailed argument, but suffice it to say for now that a BSF-friendly language might be easier to swallow, or even using something like Ruby or even Python to declare and configure Dynamic SQL seems smarter.

Posted by Randy Hall at Aug 17, 2007 15:08

Caching

"we can't support caching to the same level as the others." 

Caching is an important aspect of every layer of enterprise application and even if we can't support it to the extend that others do it does not mean we shouldn't support it to the extend that we potentially can do. 
I'm completely with Brandon Goodin for open caching policy against close caching, which is in IBATIS.
Sometimes we need the ability to integrate caching, for example if we have multiple instances of IBATIS for the same configuration (consider master/slave DBs one for read/write and one for read) then it would be good to have mechanism to use shared cache in both IBATIS instances, even sometimes we can need to share cache with other technology's cache if there is a caching mechanism already in place. 

"So, if iBATIS caching is going to remain fairly basic, it should at least be easy." 

Having fairly basic cache at least should work fair.
Consider the case when we have user centric application (the most community, social network and similar ones) and all users have their own specific data of X type, this data is not shared among users, but in IBATIS we have cache globally collected for all, so if someone updated his/her own X typed data then cache for all users will be flushed (because it is global cache and not session cache) We have a Session concept in IBATIS where if we open session then it will collect data per session (you can track it by enabling cache logs), seems to be perfect but it is not.
If any of these sessions has an update event then cache for all the sessions is flushed. I hope this is done by mistake and I would love to see at least per session caching working in IBATIS.

Posted by Artavazd Mehrabyan at Aug 31, 2007 01:28; last updated at Aug 31, 2007 01:41

This could go without saying, but one thing I'm missing in iBATIS currently is the ability to type the collections that nested maps use as return types. For example:

<result ... resultMap="namespace.stringNestedMap" javaType="java.util.List&lt;String&gt;"/>

It would be really nice to get strongly typed collections from SqlMapClient. This way we wouldn't have to do unchecked assignments in the code using it.

Posted by Eetu Huisman at Sep 13, 2007 02:42

Specifying generics in the XML has no benefit at all since generics are not enforced at runtime.

Posted by Niels Beekman at Sep 13, 2007 07:48

Dynamic SQL

This is to add another vote for dynamic sql remaining in XML. It is code, as suggested, but SQL code and we would not want that in our app code, frozen until recompilation.

Two way simple relationship mapping (like in parent child relationships) would also be a nice option covering 80% of our application needs.

Posted by Spyros Oikonomopoulos at Sep 13, 2007 10:53

Clinton and others,
Some general comments....

I've used iBATIS for several deployments including YUM brands (KFC in the UK is in the process of deploying apps with iBATIS underneath, Ecolab, and a large MVC web deployment soon to hit the market.)

I love iBATIS and always find myself coming back to it. I recently immersed myself in Hibernate only because I need to understand it and I found myself retching. Compared to iBATIS, I don't like it at all. Why? The simplicity, ease of use, and the embracing of SQL, not the hiding of it, etc. It is especially good when working with preexisting complex DB structures.

I have a couple of general concerns and comments and will get into more detail as time permits.
One feature I really liked is the fact that the SQL is in XML and thus tunable without requiring a new build or code distribution. This may be an unusual circumstance, but we have a very complex sql structure, and have had deployments where, for example, it was discovered that a more efficient query would cut processing time dramatically. In one instance, we actually went from Sybase to MySQL with no code change! We bundled the XML configuration separately from the code really minimizing the impact and associated work on such changes. If the SQL was embedded in the code, a new distribution would be needed with all he associated regression testing, etc.

With this in mind, I am not so keen on doing away with XML based Dynamic SQL because it is clumsy to use. I propose we give this a bit more thought towards a more efficient configuration based implementation. I have no ideas here yet...
The same is true for annotations, although in that case there is a choice. If we start using code for SQL, dynamic or otherwise, how is this different than hard-coding JDBC or Hibernate's HQL? We start to loose a major advantage of iBATIS, one considered probably the most important.

I like the idea of generating some code and some implicit configuration very much. One thing I find cumbersome about iBATIS is all the configuration and repetitive mappings. We almost always use ResultMap for efficiency. I find myself doing this way too often:
<ResultMap id="UserMap" class="com.its.User">
    <resultProperty="id" column="id">
    <resultProperty="firstName" column=" firstName">
    <resultProperty="lastName" column="lastName">
    <resultProperty="age" column="age">
//and on and on....

It would be great if one could create a resultMap and in the absence of an explicit resultPropery mapping, assume field == column, perhaps ignoring case. Then, the result map could be used purely for overriding non-standard behavior, or simply to use a more efficient map. There may be technical reasons why this might not work, but it is a thought. This might require generation of the XML to avoid introspection, so this may not work...

I like the idea of the interface binding very much. I was thinking about Abator and I wonder if the standard CRUD methods could be generated by default? Perhaps by extending a default Mapper interface?

Does Abator play in to this at all? Perhaps some features will be incorporated into the iBATIS core?

Posted by Thomas Bast at Sep 18, 2007 10:29; last updated at Sep 18, 2007 20:58

Hi all,
I'm a newbie here, but I use ibatis always for my db applications from 2.1.x (I don't remember exactly and now 2.2.0.
Now I want to change, because I'd like to use something that permits me to reduce xml and java code:

XML:
Now I need to create sql for each query I want to use, and a resultMap for every select.
Now there are JPAPI, and permits me to don't write any information about resultMap, and no sql for insert, update, delete and single row select queries (only for primary keys). -20% of xml code no?
But I don't like JPAPI when I need to specify, in the Entity classes, the sql for queries that don't matches the entity manager default methods (persist, merge, find and remove).
It's possible to integrate ibatis with JPAPI, and continue using sqlMap with xml to write additional sql and call it from entity manager (I used ibatis for toggle sql code from java code, and I don't understand why now all wants to insert sql code in java code).

JAVA:
Now, in ibatis version 2.2.0, I need configure a dao interface for everything I need before using maps, but when using both sqlmaps and xml files the dao interface could be very useful. It's possible to resolve this problem in ibatis 3.0 (this is the most important feature that cannot permit me to upgrade to ibatis 2.3.0, I had to change 2.2.0 code to implement, in DaoManager, the isolationLevel in transactions)?

ARCHITECTURE:
Now ibatis 2.3.0 works only with one jar. Very good.
JPAPI with Hibernate needs many jar, including JBoss Entity Manager. This is why I don't want to try Hibernate.
Also I tried to find more JPAPI based framework, but I don't like any of it. The best I found for architecture is OpenJPA, but
has many bugs, and I don't like frameworks that modify my code for enhancement or write stupid superclasses for their internal engine (not openjpa but others).
It would be very good if we use JPA with ej3-persistence.jar, jdbc.jar, ibatis.jar, logging.jar and stop. Then if we need DBCP or other we will add them, but few required libraries, please.

PERFORMANCE:
I'm testing JPA and Ibatis is a 5 time better than JPAPI, but JPAPI are abstract, and if you was able to write the most quickly framework for orm, I think you will be able to make already a great work.

It's all, easy no?
Thank you very much.
Alfredo

Posted by Alfredo Marchini at Sep 29, 2007 12:12

Hi for all.

First of all, i want to say that i am not an experienced developer.I was aiming to develop a frameworks that declares SQL queries into XML files and provides a standard and simplified way to represent them as objects and execute them. I made some search on internet and i discover IBatis. So maybe i can contribute with ideas for the new release.   

I can imagine XML structure for the SQL statements which could be like follows:

<queries>
//update USER set name=#name,email=#email where id=#id (where #name, #email, #id are members in parmClass User class)

    <update id="upadteUser" paramClass="User" table="USER">
        <set tablecolumn="name">#name</set>
        <set tablecolumn="email">#email</set>
        <where tablecolumn="id" operator="equal">#login</where>
    </update>
//select * from USER where id=#login (where #login is member in parmClass User class and the attribute list indicates one row to be returned by the query)

    <select id="getUser" paramClass="User" table="USER">
        <return list="false" class="User"></return>
        <where tablecolumn="id" operator="equal">#login</where>
    </select>

//select name, email from USER where country !=#login group by level (where #country is member in parmClass User class and the attribute list="true" indicates many rows to be returned by the query)

    <select id="getUser" paramClass="User" table="USER">
        <return list="true" class="User">
            <field tablecolumn="name">#name</field>
            <field tablecolumn="email">#email</field>
        </return>
        <where tablecolumn="country" operator="not equal">#country</where>
        <group-by tablecolumn="level" />
    </select>
</queries>

the goal is to avoid the "full" writing of the SQL statements in the select, update, insert elements ..Instead we give a full structure that represents SQL statements.

Colud this representation easier to modify and maintain.

thank you very much

Posted by zouabi Med Nabil at Dec 02, 2007 19:35; last updated at Dec 02, 2007 20:08

Is it time for next generation stuff yet?

I like all these annotations and xml everything, but I wish it was simpler. I guess it's so easy to just emerse yourself in the annotations and xml code for hours on end and never get anywhere.

I wish I could just say "COMPUTER" create a link between this db, and automatically find the driver, and automatically create all the entities/objects between database that are in this package and persist them when I create my applications. And then when the computer gets good enough it won't have to ask me what to do anymore.

Make it work with either ant, annotations, or some little sentry utility program that just makes it work, and especially make it separate the production stuff from the developer stuff. Maybe have the sentry say production(thou shalt not touch production code until you have major revision.)

Posted by Marc Noon at Dec 14, 2007 18:03

How about the status of early release of 3.0?

I made use of iBATIS on a real project in the end of 2004, Yes! The project was very successfully! After that, i wrote many articles of iBATIS in Chinese.

As a user, I am very expect the new version of iBATIS.

Posted by rosenjiang at Jan 04, 2008 06:44

What about implementing constructor injection in iBATIS 3.0? I would like most of my domain objects to be immutable, but iBATIS enforces adding setters now.

Posted by Ilya Boyandin at Jan 07, 2008 04:33

Clinton, one feature I really missed in 2.x was the ability of mapping queries to SDOs and, more in general, to create my own kind of mapping.

SDOs are growing of importance and there could be an iBatis-based DAS implementation; maybe that would be part of the Apache Tuscany project.

What do you (all) think about it?

Posted by Alessandro Santini at Jan 09, 2008 09:22

Also I miss reloading of SQL maps and queries without the need to restart the whole application so much! Please consider hot reloading for the next version.

Posted by Ilya Boyandin at Jan 18, 2008 10:16

Hi, I'm adjust iBatis to my Banking project now.
I manage config and sqlMap information in Database.. then I can reloading that information when runtime.
What did you think about this a method?

Posted by Park, chanwook at Jan 29, 2008 18:29

A little scared of the proposed Config by convention, and related SQL generation. I assume it will be easy to disable this proposed default?

At least one of the comments above suggested dropping Lazy Loading support. Here's one vote (at least) for keeping it.

FYI – so far, we are very happy using iBATIS Data Mapper. Full power of SQL, and not hidden in Java code. Some of our DBAs even like it! Simple and elegant design. Pretty good extensibility (TypeHandler, RowHandler). There are several groups in my (large insurance) company using it now, and I expect more in the future. Keep up the good work!

Posted by Hugh Ross at Jan 31, 2008 14:17

Hugh, the idea is to reduce configuration, but not eliminate the flexibility that it provides.

So the default behavior will be to use convention-based configuration and simple sql generation for CRUD operations, but there will still be all of the hooks to easily replace that behavior, too.

Don't worry - we use iBATIS every day, too - so if we booger it up, we have to deal with it too!

Posted by Larry Meadors at Feb 01, 2008 07:06

Was there much thought given to adding more "hooks" or extension points into iBATIS?

For example, allowing a TypeHandler on select="..." results or at the resultMap level?

The DTD allows specifying a TypeHandler on a select="..." result, but iBATIS seems to ignore it.

Posted by Hugh Ross at Feb 01, 2008 13:10

What I miss the most is a central mapping of pojo properties into table columns, which can be used both as a resultMap and parameterMap, and generation of insert, delete, update, select by id statements - the defaults are adequate in 90% of the time.

I hope you're not planning to get rid of batches totally, just replace them with a new solution?

Also, I'd like to see a wider and more flexible lazy-loading support. Not only a N+1 scenario, where children are fetched by individual queries, but also something like fetch='subselect' from hibernate. The developer would provide a query responsible for fetching the children of all objects returned by primary query. In many situations a query returning some 'false positives' would be more efficient, if it could get without executing the original query as a subselect. These false positives would have to be filtered by the framework before returning - either automatically or by a user-specified handler.

In general, it would be nice to provide utility classes / plugins for typical tasks, which include:

  • optimistic locking support (when auto-generating CRUD statements);
  • a polymorphic TypeHandler dispatching the creation of the actual object to TypeHandlers registered for specific values of a dyscriminator column.
  • some support for cascade update/delete of collections and dirty checkings. There are many possible solutions which don't have to give up on the flexibility for the transparency of an ORM. We could map a statement name provided in the code to a hierarchy of statements, one for each class/table involved. The framework would use a specified CascadeHandler managing the update recursion. It would be possible to implement a default one, using annotations to check if an object provides an 'isDirty' field. I would also gladly trade some transparency for the benefits given by collections tracing additions and deletions.

Note that I think of the above as 'standard extensions', which the user may overwrite in any point or decide to omit at all. Most users would probably come up with some proprietary solutions anyway.

Posted by Marcin Mościcki at Feb 11, 2008 12:54

I have to say that I agree with others about java-based Dynamic SQL and the core philosophy behind iBATIS (at least as I understand it). At my current employer we have a homegrown mapping solution that is actually very similar in ideas to iBATIS but everything is done via Java. Two issues that always come up are SQL being hard to debug because you're forced to run the code through a debugger to get the generated SQL and the fact that when you make a change you have to recompile. I am trying to get the team to take a look at, and possibly convert to, using iBATIS because the XML syntax removes SQL completely from the java codebase.

I suppose having Dynamic SQL available as java would be a good addition but again it seems to go against the whole philosophy of iBATIS. Please consider very carefully whether or not this is a feature that really should be added. Yes you can do it....doesn't mean you should.

Posted by Jason Kratz at Feb 19, 2008 16:17

As for dynamic SQL:
maybe one could incorporate into the xml a script for its generation? This wouldn't need recompilation, would have all the power of an in-Java dynamic SQL, and could be potentially modified by administrators - after all, they should be able to write perl or ruby scripts...

Posted by Marcin Mościcki at Mar 03, 2008 06:20

iBatis 3x Feature Request: Field Level Change Indicators for Update Transactions

Currently, I am looking at possible switching from a home grown DAL (data access layer) to iBatis...
iBatis is very similar in framework to the DAL.

The DAL has a holder Object which is the equivalent to iBatis' bean.
The DAL has a Model Object which is simialr to the bean.xml file.

However, the are some key components missing in iBatis that I'd like to see get added to iBatis 3x, which are as follows:

1. In the Bean (the DAL Holder), a boolean dirt indicator exists for each field for Example:
 
     iBatis' Bean:

       public class Person {
                private Long personId;
                private String name;
                private String city;
                public void setPersonId ( Long value ) { this.personId = value; }
                public Long getPersonId () { return this.personId; }
                public void setName ( String value ) { this.name = value; }
                public String getName () { return this.name; }
       
                public void setCity ( String value ) { this.city = value; }
                public String getCity () { return this.city; }
             }

     DAL's Holder Bean:

       public class Person {
                private Long personId; 
                private String name;
                private String city;
                // Note: For the data elements, java native types are not used 
                //          b/c nulls may be acceptable column value in the data model.
       
                // PersonId is the Person Table's PrimarKey, so a Dirt indicator is not needed 
                private boolean _nameDirt = false;
                private boolean _cityDirt = false;
       
                public void setPersonId ( Long value ) { this.personId = value; }
                public Long getPersonId () { return this.personId; }
       
                public void setName ( String value ) { this.name = value; this._nameDirt = true; }
                public String getName () { return this.name; }
                public void setNameDirt ( boolean b ) { this._nameDirt = b; }
                public boolean isNameDirty () { return this._nameDirt; }
       
                public void setCity ( String value ) { this.city = value; this._cityDirt = true; }
                public String getCity () { return this.name; }
                public void setCityDirt ( boolean b ) { this._cityDirt = b; }
                public boolean isCityDirty () { return this._cityDirt; }
       
                public void isDirty() { return _nameDirt || _cityDirt; }
                public void cleanDirt() { this._nameDirt = false; || this._cityDirt = false; }
             }

2. In the DAL Model (iBatis' Xml file) when Update trasnaction is called, the Holder's Dirt is interrogated to see which columns should sent to the Database for the Update transaction.. This allows the programmer the flexibility have more granular control over what gets updated...

I envision the iBatis xml being something along the following:

<update id="updatePerson" parameterClass="Person">
  <isDirty>
    <![CDATA[
      update person set
        <isFieldDirty property="name">name = #name#</isFieldDirty >
        <isFieldDirty property="city">city = #city#</isFieldDirty >
       where personid = #personId#
    ]]>
  <isDirty>
</update>

Note: If the Bean isn't dirty, then a transaction is not necessary.

Note: Also, the isFieldDirty logic would neet to prepend comma(s) when needed... The same Dirt logic can also be applied to the Insert Transaction.

3. The DAL's model also contains an Upsert Transaction. The Logic for upsert is as follows:
  
     If a table's primary key and/or unique key(s) (if defined) is NOT found
     Then perform an Insert transaction
     Else perform an Update transaction keeping intact the Dirty logic

  

Posted by Jason Risley at Mar 06, 2008 10:01

iBatis 3x Feature Request: Field Level Change Indicators for Update Transactions

Cont'd:

One thing I forgot to mention is when a bean is created/populated with the resultset of a select transaction, the bean's cleanDirt method is called....

Posted by Jason Risley at Mar 06, 2008 10:08

RE: Field Level Change Indicators

While I too have often thought field level updates would be beneficial in reducing unnecessary column updates on unchanged fields I think the trade offs may be too large. The issue I see with field level dirty checking is the number of possible DML statements you may have to generate to support all the combinations of possible updates (all the possible subsets of columns).

There are (2^n - 1) possible update statements per object where n is the number of attributes (columns) on the object (table). So even for a modestly sized table with 10 columns we could need up to 1023 update statements to support all the possible field level update combinations.

It's generally a good practice and more efficient for the database (eg. Oracle) to use a prepared statement and the gains in having only 1 of these vs 1000 will likely outweigh any gains by only updating changed columns. The issue starts to lean much more in favor of not having field level updates if you are considering the use of batch statement processing as you will lose the benefits of batching unless your application is always updating the same columns.

Posted by Michael Mansell at Mar 26, 2008 17:15

Is this look interesting? (implemented for fun)

Configurable configurable = (Configurable) map.client();
      
      QueryBuilder builder = configurable.builder(SELECT);
      builder.id("dynamicGetAccountList").parameterClass("Account").resultMap("account-result").addQueryString("select * from ACCOUNT");
      Dynamic dynamic = builder.dynamic().prepend("WHERE");
      {
      dynamic.isNotNull().prepend("AND").property("FirstName").addQueryString("ACC_FIRST_NAME = #FirstName#");
      {
      dynamic.nested().isNotNull().prepend("OR").property("LastName").addQueryString("ACC_LAST_NAME = #LastName#");
      }
      dynamic.isNotNull().prepend("AND").property("EmailAddress").addQueryString("ACC_EMAIL like #EmailAddress#");
      dynamic.isGreaterThan().prepend("AND").property("Id").compareValue("0").addQueryString("ACC_EMAIL like #EmailAddress#");
      }
      builder.build();
Posted by navis at Mar 26, 2008 21:23; last updated at Mar 26, 2008 23:20

RE: Field Level Change Indicators

In most applications, the (2^n-1) combinatorial explosion is a theoretical problem that never occurs in practice. People tend to work according to a few patterns. In some databases, at least, prepared queries are cached on an LRU basis, so that if a large number of different updates show up at some point, they will age out gracefully. So the "1 versus 1000" case typically isn't real.

This is even more relevant for dynamic SQL used for QBE queries. The same dirty-bit mechanism is used to tell which fields the user filled in. Once again, there is a potential combinatorial explosion, but it isn't real. In Oracle 10, parsing an unprepared statement has become so expensive that using prepared statements for all combinations that are actually used for queries is strongly recommended.

Batch updates are typically updating the same columns.

Posted by Ed Staub at Apr 04, 2008 10:09

RE: Field Level Change Indicators

It's a good point that usage patterns will generally reduce the impact of the different combinations of update statements required. I guess what I'm still struggling with / trying to get at is... what is the cost / benefit of going with field level?

While only a few update patterns may emerge do we know what those patterns are in advance so we can have those statements prepared in advance? or will a first "hit" incur the cost of preparing and caching the prepared statement? Is the performance hit and/or added complexity worth having field level updates? Not sure... likely depends on the environment.

As for batching I was more referring to situations were there is a high volume of DML which could be user generated and ad hoc not necessarily a batch operation that would always update the same fields. Under these circumstances I may want/need to batch statement executions and therefore round trips to the DB. So 1000 updates (of potentially different fields) on the same table could be a single statement execution instead of 1000 round trips. Now if the usage pattern tends to limit the number of sets of fields being updated maybe it is acceptable to have say 10 different statements each batching 100... but again there is added complexity here that I'm not sure is offset by the benefits.

Posted by Michael Mansell at May 28, 2008 15:55

In example: Employee getEmployee (int id);
I assume I do not have to write the employee class like I can in 2.0.

I could: Map getEmployee(Map args) ?

No reason for me to start having to write dtos if I do not want to.

.V

Posted by Vic Cekvenich at Jun 26, 2008 13:56

iBatis 3 Feature Request: Choice of implementation of Lists and Maps (Configuration options)

example: Javolution instead jdk default implementation

Posted by Massimiliano Dessì at Jun 26, 2008 16:18

hi,in my project i do that:

DAO:
insert(...){
return smc.insert("insert-" + DB_POSTFIX, ...)
}
the DB_POSTFIX is come from database.properties. it can be oracle/mssql/...

sqlmap:

<insert id="insert-oracle">
...
</insert>

<insert id="insert-mssql">
...
</insert>

In iBatis 3
i want:

DAO:
insert(...){
return smc.insert("insert", ...)
}

sqlmap:

<insert id="insert-oracle">
...
</insert>

<insert id="insert-mssql">
...
</insert>

Posted by bukebushuo at Jul 06, 2008 22:12

The thoughts about iBatis 3 looks great. I'm very looking forward to it.

I have one small suggestion. Is XML the best language for configuration? I don't think so. Maybe the better way is to create some kind of DSL. Why?

The specification of all things can be easier then using XML (including dynamic SQL - it can contain something like suggested navis).

If this idea is looking interesting to you have a look at gradle (for me new maven but without the terrible XML configuration) and its groovy build file (see user's guide). It looks much more easy that using xml.

Posted by Jiří Mareš at Jan 14, 2009 15:34

From a newbie:

Annotations

Are cool. We love them. @Select, @Update, @Delete and @Insert, with only one annotation attribute, linked to an interface DAO method are neat and simple.

Annotations and field name conventions

An example, inspired from your code snippets:

@Select(
      "SELECT #id(EMP_ID:NUMERIC), #firstName(FIRST_NAME:VARCHAR), #lastName(LAST_NAME:VARCHAR) "+
      "FROM EMPLOYEE WHERE LAST_NAME = @name AND AGE < @age")
      List<Employee> selectEmployeeByNameAndAge(String name, int age);

Can we make this simplier ? The goal is to cut and paste the sql code in an sqlClient, and do the less work possible to make it work:

@Select(
      "SELECT EMP_ID as id, FIRST_NAME as firstname, LAST_NAME as lastname "+
      "FROM EMPLOYEE WHERE AGE < #2# AND LAST_NAME = #1#")
      List<Employee> selectEmployeeByNameAndAge(String name, int age);
  • Providing SQL aliases instead of ibatis names: mapping from sql fields to java fields is done through sql aliases. Even if ResultSetMetadata.getColumnLabel() or getColumnName() don't work as expected, the SQL can be parsed to get the "\s+as\s+" marker before the alias.
  • No type in sql: type casting is done considering field's java type of "Employee". For special type casting, a "mapper" attribute can be added to the @Select annotation.
  • "#...#" makes it obvious that it's an external parameter.

Rules for DAO methods args and SQL criterias:

  • if primitive types (or wrapped primitives): SQL criterias are "#numbers#" and filled following order of method args.
  • else (they're full objects): SQL criterias are concatenation of class name and field name (first letter uppercase)
    e.g.
    @Select(
          "... WHERE " +
          "BILL_DATE < #billDate# AND COMPANY_ID = #companyId# AND PRICE > #2# AND PRICE < #1#")
          List<Product> selectProducts(int highPrice,Company company, Bill bill, int lowPrice);

Annotations and groupby

Please don't give up. double configuration (xml and annotation) is a pain. Let's give a try:

class Company {
      long id;
      String name;
      List<Department> departments;
      }
      class Department {
      long id;
      String name;
      List<Employee> employees;
      }
      ...
      @Select("SELECT ID, NAME, " +
      "D.ID AS departmentsId, D.NAME AS departmentsName, " +
      "E.ID AS departmentsEmployeesId, E.FIRST_NAME AS departmentsEmployeesFirstname, " +
      "E.LAST_NAME AS departmentsEmployeesLastname " +
      "FROM COMPANY C, DEPARTMENT D, EMPLOYEE E " +
      "WHERE D.DEPT_ID = E.DEPT_ID " +
      "AND C.COMP_ID = D.COMP_ID")
      @groupBy({"id","departmentsId"})
      List<Company> selectAllCompaniesWithJoin();

... as you see, uppercase in aliases are important. Other letters must be lowercase. @groupby indicates 2 levels of groupby: Company (implicit) and Department. Group identification will be made on Company.id an Department.id. Mapping is done via java reflexion on aliases (e.g. alias 'departmentsEmployeesLastname' becomes 'Company.departments.employees.lastName').

IoC integration and datasource configuration

Making things like this to work:

class MyService {
      @Dao
      EmployeeDao dao;
      
      public void doTheWork(long empId) {
      Employee emp = dao.getEmployee(empId);
      ...
      }
      }
      @Datasource("jdbcFromJndi/myDataSource")
      interface EmployeeDao {
      @Select("...")
      Employee getEmployee(long id);
      }

That's it. All I need to write is here.

Whow

I would dream of a pure jdbc framework with all the sugar-cubes of JPA (annotations, transparency...). Anyway, IBatis3 looks promising. I would follow this with great attention.

Posted by Philippe Bougrier at Feb 17, 2009 21:59

Since constructor injection is on the table, will instantiation via a static and instance factory methods be available, too?

Posted by David Sledge at Jun 29, 2009 16:15

I'm looking for iBatis jar in Maven central repository(this helps me not to upload in enterprise local artifactory). I was not able to locate. Pl. help me some one in this regard.

Posted by Raghu Semburakkiannan at Aug 19, 2009 15:44

I've been using iBatis for two years on a very large project and it has worked mostly well. There have been some issues I have had to work around by updating the original code base (iBaits). Two of the areas that I had to change dealt with logging and the ability to override SQL statements. See below for more detail.

Has the error handling been improved in iBatis 3.0? For example, with the older version of iBatis it can take sometime to track down which column in a ResultMap was not mapped properly. Why doesn't iBatis print out the name of the column, resultmap and the namespace that caused the error? Also, why doesn't iBatis print out the malformed SQL that prevents it from continuing? iBatis could print out at debug level the namespace, SQL tag name and SQL when it is malformed.

Also, does iBatis 3.0 have the ability for a namespace to be defined in more than one file (xml) and override the implementation of an SQL, SELECT, etc statement. Why this is useful? If your working on a product and need to define "core" functionality and then you need to override or add new SQL for each client that you customize the product for this is really helpful. I managed to change the code in the 2.x release of iBatis to support this. It would be really nice if it was part the distribution.

Finally, it would be great if iBatis allowed you to defined a default namespace that reusable SQL code segments could be placed and allow them to be accessible by all namespaces. For example, defining the isolation level in a default namespace for DB2. Latter on if I have to support Oracle. I would only need to change one file.

DB2 isolation level

<sql id="FOR_READ_ONLY" >
FOR READ ONLY
</sql>

Thank you, for a great alternative to Hibernate and straight JDBC.

Posted by Chad Oliver at Sep 01, 2009 07:30

this is a great resource when i write my essay and college essay

Posted by macalister at Mar 03, 2010 01:22
Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators