在select子句JOOQ中串联字段

Question

@Override
protected List<Field<?>> selectCustomFields() {
    List<Field<?>> customSelect = new ArrayList<Field<?>>();

    // Fields to use in the concatenation
    Field<?> field1 = field("addr.AddressLine1"),   field2 = field("addr.AddressLine2"),field3 = field("addr.AddressLine3"),
             field4 = field("addr.AddressLine4"),   field5 = field("addr.PostalCode"),  field6 = field("addr.City"),
             field7 = field("addr.State"),          field8 = field("addr.County"),      field9 = field("addr.Country");

    // Create non null/empty conditions
    Condition condLine1 = field1.isNotNull().and(field1.length().ne(0));
    Condition condLine2 = field2.isNotNull().and(field2.length().ne(0));
    Condition condLine3 = field3.isNotNull().and(field3.length().ne(0));
    Condition condLine4 = field4.isNotNull().and(field4.length().ne(0));
    Condition condLine5 = field5.isNotNull().and(field5.length().ne(0));
    Condition condLine6 = field6.isNotNull().and(field6.length().ne(0));
    Condition condLine7 = field7.isNotNull().and(field7.length().ne(0));
    Condition condLine8 = field8.isNotNull().and(field8.length().ne(0));
    Condition condLine9 = field9.isNotNull().and(field9.length().ne(0));

    // Concat address lines when meets condition
    customSelect.add(concat(DSL.when(condLine1, field1),
                            DSL.when(condLine2, field2),
                            DSL.when(condLine3, field3),
                            DSL.when(condLine4, field4),
                            DSL.when(condLine5, field5),
                            DSL.when(condLine6, field6),
                            DSL.when(condLine7, field7),
                            DSL.when(condLine8, field8),
                            DSL.when(condLine9, field9))
                            .as("fullAddress"));
    return customSelect;
}

select 
  concat(
    cast(case when (
               addr.AddressLine1 is not null
               and char_length(cast(addr.AddressLine1 as char)) <> 0
             ) then addr.AddressLine1 end as char), 
    cast(case when (
               addr.AddressLine2 is not null
               and char_length(cast(addr.AddressLine2 as char)) <> 0
             ) then addr.AddressLine2 end as char), 
    cast(case when (
               addr.AddressLine3 is not null
               and char_length(cast(addr.AddressLine3 as char)) <> 0
             ) then addr.AddressLine3 end as char), 
    cast(case when (
               addr.AddressLine4 is not null
               and char_length(cast(addr.AddressLine4 as char)) <> 0
             ) then addr.AddressLine4 end as char), 
    cast(case when (
               addr.PostalCode is not null
               and char_length(cast(addr.PostalCode as char)) <> 0
             ) then addr.PostalCode end as char), 
    cast(case when (
               addr.City is not null
               and char_length(cast(addr.City as char)) <> 0
             ) then addr.City end as char), 
    cast(case when (
               addr.State is not null
               and char_length(cast(addr.State as char)) <> 0
             ) then addr.State end as char), 
    cast(case when (
               addr.County is not null
               and char_length(cast(addr.County as char)) <> 0
             ) then addr.County end as char), 
    cast(case when (
               addr.Country is not null
               and char_length(cast(addr.Country as char)) <> 0
             ) then addr.Country end as char)) as `fullAddress`
from Address as `addr`
  ....

我的问题是，

• 如何正确创建select语句？
• 如何最好地添加空格分隔符？
• 是否有更好的替代JOOQ(when=case)条件子句？

Answer 1

您忘记了大小写。Jooq中的else部分，或Owhere():

// Concat address lines when meets condition
customSelect.add(concat(DSL.when(condLine1, field1).otherwise(""),
                        DSL.when(condLine2, field2).otherwise(""),
                        DSL.when(condLine3, field3).otherwise(""),
                        DSL.when(condLine4, field4).otherwise(""),
                        DSL.when(condLine5, field5).otherwise(""),
                        DSL.when(condLine6, field6).otherwise(""),
                        DSL.when(condLine7, field7).otherwise(""),
                        DSL.when(condLine8, field8).otherwise(""),
                        DSL.when(condLine9, field9).otherwise(""))
                        .as("fullAddress"));

如果您希望在地址部分之间增加一个空格分隔符，可以写：

// Concat address lines when meets condition
customSelect.add(concat(DSL.when(condLine1, field1.concat(" ")).otherwise(""),
                        DSL.when(condLine2, field2.concat(" ")).otherwise(""),
                        DSL.when(condLine3, field3.concat(" ")).otherwise(""),
                        DSL.when(condLine4, field4.concat(" ")).otherwise(""),
                        DSL.when(condLine5, field5.concat(" ")).otherwise(""),
                        DSL.when(condLine6, field6.concat(" ")).otherwise(""),
                        DSL.when(condLine7, field7.concat(" ")).otherwise(""),
                        DSL.when(condLine8, field8.concat(" ")).otherwise(""),
                        DSL.when(condLine9, field9.concat(" ")).otherwise("")).trim()
                        .as("fullAddress"));

我认为这种方法是合理的。当然，您可能不应该一直重复所有这些逻辑，而是创建一个排序循环：

List<Field<String>> list = new ArrayList<>();
for (int i = 0; i < fields.size(); i++) {
    list.add(DSL.when(conditions.get(i), (Field) fields.get(i)).otherwise(""));
}
customSelect.add(concat(list.toArray(new Field[0])).trim().as("fullAddress"));