mapping input and output parameters with MyBatis
I am learning how to use MyBatis. 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.
For example I like how easy to execute a query before insert with @SelectKey annotation. And data mapping works like a charm if I add some annotation before the interface method, something like this: @Results({ @Result(property = "javaField", column = "database_field", javaType = TypeHandler.class).
What I do not like (and I hope that you can put me in the the right direction) are the following:
(Issue 1) 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:
SELECT * FROM table
WHERE key_name = ? AND ((? IS NULL AND user_id IS NULL) OR User_id = ?)
With JDBC I need to to the following:
stmt = connection.prepareStatement(query);
stmt.setString(1, "key");
stmt.setString(2, userId);
stmt.setString(3, userId);
As you can see I need to pass twice the userId 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.
I wonder if it is possible to add this feature to MyBatis. That should be fine if MyBatis can bind userId twice automatically, something like this:
@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);
What workaround actually I did is the following. I hate it because it is tricky and additional java "if" statement is necessary:
@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);
I do not know what is the best practice to manage this situation with MyBatis. Please guide me.
(Issue 2) Mapping in case of @Select. Is there any way to avoid repeated code while mapping result of queries with the same result type?
1st query:
@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);
2nd query:
@Select("SELECT * FROM table WHERE <different conditions then before>")
@Results({
<I need to add here the exact same code then before in query 1>
})
SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
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.
(Issue 3) @Param annotation
I could not see anything in the documentation about @Param annotation. That was hard to figure out why my java parameters was not bounded properly. Finally I realized that the @Param annotation was missing from my code. Why this is not mentioned in the official documentation? I did something on a wrong way and @Param is not necessary to use?
That code works fine:
SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
This does not work:
SomeClass findByKeyAndUserId(String key, Long userId);
UPDATE FOR (Issue 1)
My 1st idea was similar with that @blackwizard mentioned: "Mybatis does bind parameters by name, then once, twice, N times, as may time it is referenced, it works."
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 :(
Exception:
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
Finally I have found three different solutions.
(Issue 1: solution 1)
I followed what @blackwizard mentioned and I was able to move the condition userId = (userId == null) ? -1 : userId from java to MyBatis level. And it is not bad! The correct syntax is: <if test='userId==null'><bind name='userId' value='-1'/></if>.
(Issue 1: solution 2)
The reason why I get back could not determine data type of parameter $2 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.
@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);
(Issue 1: solution 3) The 2nd solution depends on PorstgreSQL. The following solution is totally database independent. Thanks for @blackwizard for the nice comment.
@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);
Personally I prefer solution 3. It contains less additional code.
Answer
Issue 1:
Name the parameters:
@Select("SELECT * FROM table key_name = #{key} and ((#{userId} is null and user_id is null) OR user_id = #{userId}")
SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
Mybatis does bind parameters by name, then once, twice, N times, as may time it is referenced, it works.
You can do the if in Mybatis: with XML tags, although it is not really better ... anyway it is good trick you may reuse someday for another purpose. To use XML tags in the annotation value, the value must be embedded into a <script> tag exactly at the beginning and the end of the string.
@Select({"<script>",
"<if 'userId==null'><bind name='userId' value='1'/></if>",
"SELECT * FROM table WHERE key_name = #{key} ",
"AND COALESCE(user_id, -1) = #{userId}",
"</script>"})
SomeClass findByKeyAndUserId(@Param("key") String key, @Param("userId") Long userId);
You may also use typeHandler to set the default value, just use it with the parameter: #{userId, typeHandler=CustomDefaultValueTypeHandler}
Edit: reply to additional question: If you want to allow passing null values instead of handling replacement by default value then you must give Mybatis some hint about supposed type of bound parameter because it cannot resolve the actual type of null since it doesn't know/see the variable declaration in the Mapper interface. So: #{userId, javaType=int,jdbcType=NUMERIC}. Only one of both attributes may be sufficient.
Documentation states:
Like the rest of MyBatis, the javaType can almost always be determined from the parameter object, unless that object is a HashMap. Then the javaType should be specified to ensure the correct TypeHandler is used.
NOTE The JDBC Type is required by JDBC for all nullable columns, if null is passed as a value. You can investigate this yourself by reading the JavaDocs for the PreparedStatement.setNull() method.
Issue 2: you definitely cannot reuse/mutualize what you define in an annotation. This is not because of Mybatis, but annotations. You will have to define result maps in the XML referencing them with @ResultMap. Documentation states:
ResultMap Method N/A This annotation is used to provide the id of a <resultMap> element in an XML mapper to a @Select or @SelectProvider annotation. This allows annotated selects to reuse resultmaps that are defined in XML. This annotation will override any @Results or @ConstructorArgs annotation if both are specified on an annotated select.
Issue 3: as I already answered in your other question, @Param annotations have the effect to transform the convenient parameter list into a map such as:
Map<String, Object> params = new HashMap<String, Object>();
params.put("key", key);
params.put("userId", userId);
I agree that Mybatis documentation could be better and you will find more resources in place such as here.
However, Mybatis documentation states the following about @Param annotation
@Param Parameter N/A If your mapper method takes multiple parameters, this annotation can be applied to a mapper method parameter to give each of them a name. Otherwise, multiple parameters will be named by their position prefixed with "param" (not including any RowBounds parameters). For example #{param1}, #{param2} etc. is the default. With @Param("person"), the parameter would be named
#{person}.