title: mapping input and output parameters with MyBatis
body: <p>I am learning how to use <a href="http://blog.mybatis.org" rel="nofollow noreferrer">MyBatis</a>. Honestly I like this framework very well. It is easy to use and I am happy with it because I can use my sql commands with it :) I use MyBatis 3.4.2 and PostgreSQL database.</p>
  
  <p>For example I like how easy to execute a query before insert with <code>@SelectKey</code> annotation. And data mapping works like a charm if I add some  annotation before the interface method, something like this: <code>@Results({ @Result(property = "javaField", column = "database_field", javaType = TypeHandler.class)</code>.</p>
  
  <p><strong>What I do not like (and I hope that you can put me in the the right direction) are the following:</strong></p>
  
  <hr>
  
  <p><strong>(Issue 1)</strong> I have queries which allows me to use null and normal value without any additional "if" java statement to check whether the variable contains null or not null value. They looks like this:</p>
  
  <pre><code>SELECT * FROM table
  WHERE key_name = ? AND ((? IS NULL AND user_id IS NULL) OR User_id = ?) 
  </code></pre>
  
  <p>With JDBC I need to to the following:</p>
  
  <pre><code>stmt = connection.prepareStatement(query);
  stmt.setString(1, "key");
  stmt.setString(2, userId);
  stmt.setString(3, userId);
  </code></pre>
  
  <p>As you can see I need to pass twice the <em>userId</em> because this is the way how JDBC works. Honestly my expectation was that the following code bellow will work with MyBatis but unfortunately it does not work. The 3rd parameter still need to be define.</p>
  
  <p>I wonder if it is possible to add this feature to MyBatis. That should be fine if MyBatis can bind <em>userId</em> twice automatically, something like this:</p>
  
  <pre><code>@Select("SELECT * FROM table key_name = #{key} and ((#{userId} is null and user_id is null) OR user_id = #{userId})
  SomeClass findByKeyAndUserId(String key, Long userId);
  </code></pre>
  
  <p>What workaround actually I did is the following. I hate it because it is tricky and additional java "if" statement is necessary:</p>
  
  <pre><code>@Select("SELECT * FROM table WHERE key_name = #{key} AND COALESCE(user_id, -1) = #{userId}")
  SomeClass findByKeyAndUserId(String key, Long userId);
  
  userId = (userId == null) ? -1 : userId;
  SomeClass abc = mapper.findByKeyAndUserId(key, userId);
  </code></pre>
  
  <p>I do not know what is the best practice to manage this situation with MyBatis. Please guide me.</p>
  
  <hr>
  
  <p><strong>(Issue 2)</strong> Mapping in case of <code>@Select</code>. Is there any way to avoid repeated code while mapping result of queries with the same result type?</p>
  
  <p>1st query:</p>
  
  <pre><code>@Select("SELECT * FROM table WHERE ...")
  @Results({
          @Result(property = "key", column = "key_name", javaType = String.class),
          @Result(property = "value", column = "key_value", javaType = String.class),
          @Result(property = "userId", column = "user_id", javaType = Long.class),
          @Result(property = "interval", column = "interval", javaType = Long.class),
          @Result(property = "description", column = "description", javaType = String.class),
          @Result(property = "status", column = "status", typeHandler = StatusTypeHandler.class)
  })
  SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
  </code></pre>
  
  <p>2nd query:</p>
  
  <pre><code>@Select("SELECT * FROM table WHERE &lt;different conditions then before&gt;")
  @Results({
          &lt;I need to add here the exact same code then before in query 1&gt;
  })
  SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
  </code></pre>
  
  <p>Can I reuse somehow the mapping related code? I need to add mapping because I use special type handler for status field. I use annotation based configuration.</p>
  
  <hr>
  
  <p><strong>(Issue 3)</strong> <code>@Param</code> annotation
  I could not see anything in the documentation about <code>@Param</code> annotation. That was hard to figure out why my java parameters was not bounded properly. Finally I realized that the <code>@Param</code> annotation was missing from my code. Why this is not mentioned in the official documentation? I did something on a wrong way and <code>@Param</code> is not necessary to use?</p>
  
  <p>That code works fine:</p>
  
  <pre><code>SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
  </code></pre>
  
  <p>This does not work:</p>
  
  <pre><code>SomeClass findByKeyAndUserId(String key, Long userId);
  </code></pre>
  
  <hr>
  
  <p><strong>UPDATE FOR (Issue 1)</strong></p>
  
  <p>My 1st idea was similar with that <strong>@blackwizard</strong> mentioned: <em>"Mybatis does bind parameters by name, then once, twice, N times, as may time it is referenced, it works."</em></p>
  
  <p>But this actually does not work properly. It works if the userId is not null. If it is null I get a nice exception which comes back from the database. I guess that MyBatis binds null value in a wrong way. Maybe it is a bug. I do not know :(</p>
  
  <p>Exception:</p>
  
  <pre><code>org.apache.ibatis.exceptions.PersistenceException: 
  ### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $2
  ### The error may exist in com/.../dao/TableDao.java (best guess)
  ### The error may involve ....dao.Table.findByKeyAndUserId-Inline
  ### The error occurred while setting parameters
  ### SQL: SELECT * FROM table WHERE key_name = ? AND (? IS NULL AND user_id IS NULL) OR user_id = ? AND status = 1
  ### Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $2
  </code></pre>
  
  <p>Finally I have found three different solutions.</p>
  
  <p><strong>(Issue 1: solution 1)</strong></p>
  
  <p>I followed what <strong>@blackwizard</strong> mentioned and I was able to move the condition <code>userId = (userId == null) ? -1 : userId</code> from java to MyBatis level. And it is not bad! The correct syntax is: <code>&lt;if test='userId==null'&gt;&lt;bind name='userId' value='-1'/&gt;&lt;/if&gt;</code>.</p>
  
  <p><strong>(Issue 1: solution 2)</strong>
  The reason why I get back <code>could not determine data type of parameter $2</code> error from postgres is because in case on null value the JDBC driver is not able to determine the type of the parameter. So let's define it manually.</p>
  
  <pre><code>@Select("SELECT * FROM table "
          + "WHERE key_name = #{key} AND ((#{userId}::BIGINT IS NULL AND user_id IS NULL) OR user_id = #{userId})")
  })
  SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
  </code></pre>
  
  <p><strong>(Issue 1: solution 3)</strong>
  The 2nd solution depends on PorstgreSQL. The following solution is totally database independent. Thanks for @blackwizard for the nice comment.</p>
  
  <pre><code>@Select("SELECT * FROM table "
          + "WHERE key_name = #{key} AND ((#{userId, jdbcType=BIGINT} IS NULL AND user_id IS NULL) OR user_id = #{userId})")
  })
  SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
  </code></pre>
  
  <p>Personally I prefer solution 3. It contains less additional code.</p>