postgresql 使用 MyBatis 映射输入和输出参数

mapping input and output parameters with MyBatis

提问by zappee

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.

我正在学习如何使用MyBatis。老实说，我非常喜欢这个框架。它易于使用，我对它很满意，因为我可以用它来使用我的 sql 命令:) 我使用 MyBatis 3.4.2 和 PostgreSQL 数据库。

For example I like how easy to execute a query before insert with @SelectKeyannotation. 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).

例如，我喜欢在使用@SelectKey注释插入之前执行查询是多么容易。和数据映射就像一个魅力，如果我的接口方法之前添加一些注释，像这样：@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:

（问题 1）我有一些查询，它允许我使用 null 和正常值，而无需任何额外的“if”java 语句来检查变量是否包含 null 或非 null 值。它们看起来像这样：

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:

使用 JDBC 我需要做到以下几点：

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 userIdbecause 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.

如您所见，我需要传递两次userId，因为这是 JDBC 的工作方式。老实说，我的期望是下面的代码可以与 MyBatis 一起使用，但不幸的是它不起作用。第三个参数仍然需要定义。

I wonder if it is possible to add this feature to MyBatis. That should be fine if MyBatis can bind userIdtwice automatically, something like this:

我想知道是否可以将这个功能添加到MyBatis中。如果 MyBatis 可以自动绑定userId两次，那应该没问题，像这样：

@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:

我实际上所做的解决方法如下。我讨厌它，因为它很棘手，并且需要额外的 java "if" 语句：

@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.

我不知道用 MyBatis 管理这种情况的最佳实践是什么。请指导我。

---

---

(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?

（问题 2）在 的情况下映射@Select。在映射具有相同结果类型的查询结果时，有什么方法可以避免重复代码？

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)@Paramannotation I could not see anything in the documentation about @Paramannotation. That was hard to figure out why my java parameters was not bounded properly. Finally I realized that the @Paramannotation was missing from my code. Why this is not mentioned in the official documentation? I did something on a wrong way and @Paramis not necessary to use?

（问题 3）@Param注解 我在文档中看不到任何关于@Param注解的内容。很难弄清楚为什么我的 java 参数没有正确限制。最后我意识到@Param我的代码中缺少注释。为什么官方文档中没有提到这一点？我以错误的方式做某事并且@Param没有必要使用？

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)

更新（问题 1）

My 1st idea was similar with that @blackwizardmentioned: "Mybatis does bind parameters by name, then once, twice, N times, as may time it is referenced, it works."

我的第一个想法与@blackwizard提到的类似：“ Mybatis确实按名称绑定参数，然后一次、两次、N 次，只要它被引用，它就可以工作。”

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 :(

但这实际上并不能正常工作。如果 userId 不为空，它会起作用。如果它为空，我会得到一个很好的异常，它从数据库中返回。我猜 MyBatis 以错误的方式绑定了空值。也许这是一个错误。我不知道 ：（

Exception:

例外：

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter 
### 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 

Finally I have found three different solutions.

最后我找到了三种不同的解决方案。

(Issue 1: solution 1)

（问题 1：解决方案 1）

I followed what @blackwizardmentioned and I was able to move the condition userId = (userId == null) ? -1 : userIdfrom java to MyBatis level. And it is not bad! The correct syntax is: <if test='userId==null'><bind name='userId' value='-1'/></if>.

我遵循了@blackwizard提到的内容，并且能够将条件userId = (userId == null) ? -1 : userId从 java移动到 MyBatis 级别。而且还不错！正确的语法是：<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 $2error 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.

（问题 1：解决方案 2）我could not determine data type of parameter $2从 postgres返回错误的原因是因为在 null 值的情况下，JDBC 驱动程序无法确定参数的类型。所以让我们手动定义它。

@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.

（问题 1：解决方案 3）第二个解决方案取决于 PorstgreSQL。以下解决方案完全独立于数据库。感谢@blackwizard 的精彩评论。

@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.

我个人更喜欢解决方案 3。它包含较少的附加代码。

回答by blackwizard

Issue 1:

问题 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.

Mybatis 确实是按名称绑定参数，然后一次，两次，N 次，只要它被引用，它就可以工作。

You can do the ifin 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.

您可以在 Mybatis 中执行if：使用 XML 标签，尽管它并不是真的更好……无论如何，这是一个很好的技巧，您有一天可以将其用于其他目的。要在注释值中使用 XML 标记，必须将该值<script>准确地嵌入到字符串的开头和结尾的标记中。

@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}

您也可以使用 typeHandler 设置默认值，只需将其与参数一起使用： #{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.

编辑：回复附加问题：如果你想允许传递空值而不是处理默认值的替换，那么你必须给 Mybatis 一些关于假定的绑定参数类型的提示，因为它无法解析空值的实际类型，因为它不知道/参见 Mapper 接口中的变量声明。所以：#{userId, javaType=int,jdbcType=NUMERIC}。这两种属性中只有一种可能就足够了。

Documentationstates:

文档说明：

Like the rest of MyBatis, the javaTypecan almost always be determined from the parameter object, unless that object is a HashMap. Then the javaTypeshould be specified to ensure the correct TypeHandleris used.

NOTEThe 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.

与 MyBatis 的其余部分一样，javaType几乎总是可以从参数对象中确定，除非该对象是HashMap。然后 java类型应规定以确保正确的类型处理器使用。

注意如果将 null 作为值传递，则 JDBC 需要所有可空列的 JDBC 类型。您可以通过阅读PreparedStatement.setNull()方法的 JavaDocs 自行调查。

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. Documentationstates:

问题 2：您绝对不能重用/相互化您在注释中定义的内容。这不是因为Mybatis，而是注解。您必须在 XML 中定义结果映射，并使用@ResultMap引用它们。文档说明：

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:

问题 3：正如我在您的其他问题中已经回答的那样，@Param 注释具有将方便的参数列表转换为地图的效果，例如：

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.

我同意 Mybatis 文档可能会更好，您会在此处找到更多资源。

However, Mybatis documentationstates the following about @Paramannotation

但是，Mybatis 文档说明了有关@Param注释的以下内容

@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}.

回答by Kazuki Shimizu

Issue 3:

问题 3：

Please try -parameters compile option provided since JDK 8. You can omit a @Param annotation.

请尝试从 JDK 8 开始提供的 -parameters 编译选项。您可以省略 @Param 注释。

See https://github.com/mybatis/mybatis-3/issues/549

见https://github.com/mybatis/mybatis-3/issues/549

Thanks.

谢谢。