Hibernate Criteria API 等效于 Oracle 的解码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5861866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Criteria API Equivalent to Oracle's Decode
提问by Josh Johnson
What would the equivalent of Oracle's DECODE()function be in the Hibernate Criteria API?
在 Hibernate Criteria API 中，Oracle 的DECODE()函数的等价物是什么？
An SQL example of what I need to do:
我需要做的一个 SQL 示例：
SELECT DECODE(FIRST_NAME, NULL, LAST_NAME, FIRST_NAME) as NAME ORDER BY NAME;
Which returns LAST_NAME to NAME in the event that FIRST_NAME is NULL.
如果 FIRST_NAME 为 NULL，则将 LAST_NAME 返回给 NAME。
I would prefer to use the Criteria API but could use HQL if there's no other way.
我更喜欢使用 Criteria API，但如果没有其他方法，可以使用 HQL。
采纳答案by Josh Johnson
Ended up adding a formula for it:
最终为它添加了一个公式：
<property name="name" formula="coalesce(first_name, last_name)"/>
I'm concerned about cross-database problems and possibly efficiency problems with this approach so I'm willing to change the accepted answer.
我担心这种方法的跨数据库问题和可能的效率问题，因此我愿意更改已接受的答案。
回答by MarkOfHall
回答by Jeffrey Kemp
For the example you give, you could use COALESCE().
对于您给出的示例，您可以使用 COALESCE()。
回答by Brian Deterling
You can use sqlRestriction to call the native decode function.
您可以使用 sqlRestriction 调用本机解码函数。
session.createCriteria(Table.class).add(Restrictions.sqlRestriction("decode({alias}.firstName,null, {alias}.lastName, {alias}.firstName)"))
session.createCriteria(Table.class).add(Restrictions.sqlRestriction("decode({alias}.firstName,null, {alias}.lastName, {alias}.firstName)"))
With HQL, the Oracle dialect already has coalesce and nvl functions, or if you really need decode, you could subclass the dialect and add it as a custom function. I don't know if Hibernate supports a variable length number of arguments like decode does, but worst-case, you could create decode1, decode2, etc to support different numbers of arguments.
使用 HQL，Oracle 方言已经具有合并和 nvl 函数，或者如果您确实需要解码，您可以将方言子类化并将其添加为自定义函数。我不知道 Hibernate 是否像 decode 一样支持可变长度的参数数量，但在最坏的情况下，您可以创建 decode1、decode2 等来支持不同数量的参数。
Or, if you aren't using the column in a where or group by, you could just bring both attributes back and do the check in Java.
或者，如果您不在 where 或 group by 中使用该列，您可以将这两个属性都带回来并在 Java 中进行检查。
回答by TryChai
You can Use Hibernate @Type attribute,Based on your requirement you can customize the annotation and apply on top of the fied. like :
您可以使用 Hibernate @Type 属性，根据您的要求，您可以自定义注释并应用到字段之上。喜欢 ：
public class PhoneNumberType implements UserType {
@Override
public int[] sqlTypes() {
return new int[]{Types.INTEGER, Types.INTEGER, Types.INTEGER};
}
@Override
public Class returnedClass() {
return PhoneNumber.class;
}
// other methods
}
First, the null SafeGet method:
首先，null SafeGet 方法：
@Override
public Object nullSafeGet(ResultSet rs, String[] names,
SharedSessionContractImplementor session, Object owner) throws HibernateException,
SQLException {
int countryCode = rs.getInt(names[0]);
if (rs.wasNull())
return null;
int cityCode = rs.getInt(names[1]);
int number = rs.getInt(names[2]);
PhoneNumber employeeNumber = new PhoneNumber(countryCode, cityCode, number);
return employeeNumber;
}
Next, the null SafeSet method:
接下来是 null SafeSet 方法：
@Override
public void nullSafeSet(PreparedStatement st, Object value,
int index, SharedSessionContractImplementor session)
throws HibernateException, SQLException {
if (Objects.isNull(value)) {
st.setNull(index, Types.INTEGER);
} else {
PhoneNumber employeeNumber = (PhoneNumber) value;
st.setInt(index,employeeNumber.getCountryCode());
st.setInt(index+1,employeeNumber.getCityCode());
st.setInt(index+2,employeeNumber.getNumber());
}
}
Finally, we can declare our custom PhoneNumberType in our OfficeEmployee entity class:
最后，我们可以在我们的 OfficeEmployee 实体类中声明我们的自定义 PhoneNumberType：
@Entity
@Table(name = "OfficeEmployee")
public class OfficeEmployee {
@Columns(columns = { @Column(name = "country_code"),
@Column(name = "city_code"), @Column(name = "number") })
@Type(type = "com.baeldung.hibernate.customtypes.PhoneNumberType")
private PhoneNumber employeeNumber;
// other fields and methods
}
This might solve your problem, This will work for all database. if you want more info refer :: https://www.baeldung.com/hibernate-custom-types
这可能会解决您的问题，这将适用于所有数据库。如果您想了解更多信息，请参阅 :: https://www.baeldung.com/hibernate-custom-types
