This is an automated email from the ASF dual-hosted git repository.
doebele pushed a commit to branch version3
in repository https://gitbox.apache.org/repos/asf/empire-db.git
The following commit(s) were added to refs/heads/version3 by this push:
new 017f72b EMPIREDB-362 support SQL concat() function added
017f72b is described below
commit 017f72b9604d67b771cdefcf86d633e046d853fc
Author: Rainer Döbele <[email protected]>
AuthorDate: Mon Jan 31 13:24:44 2022 +0100
EMPIREDB-362 support SQL concat() function added
---
.../org/apache/empire/samples/db/SampleApp.java | 7 +-
.../java/org/apache/empire/commons/DateUtils.java | 39 ++++++---
.../org/apache/empire/commons/ObjectUtils.java | 7 +-
.../java/org/apache/empire/db/DBColumnExpr.java | 22 +++++
.../main/java/org/apache/empire/db/DBUtils.java | 2 +-
.../empire/db/expr/column/DBConcatFuncExpr.java | 96 ++++++++++++++++++++++
.../java/org/apache/empire/dbms/DBSqlPhrase.java | 1 +
.../apache/empire/dbms/hsql/DBMSHandlerHSql.java | 12 ++-
.../empire/dbms/oracle/DBMSHandlerOracle.java | 9 +-
9 files changed, 170 insertions(+), 25 deletions(-)
diff --git
a/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
b/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
index 5f38a91..d9e9516 100644
---
a/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
+++
b/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
@@ -52,7 +52,6 @@ import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.w3c.dom.Document;
-
public class SampleApp
{
// Logger
@@ -616,7 +615,8 @@ public class SampleApp
SampleDB.Payments PAY = db.PAYMENTS;
// The following expression concats lastname + ', ' + firstname
- DBColumnExpr EMPLOYEE_FULLNAME = EMP.LASTNAME.append(",
").append(EMP.FIRSTNAME).as("FULL_NAME");
+ // DBColumnExpr EMPLOYEE_FULLNAME = EMP.LASTNAME.append(",
").append(EMP.FIRSTNAME).as("FULL_NAME");
+ DBColumnExpr EMPLOYEE_FULLNAME = EMP.LASTNAME.concat(", ",
EMP.FIRSTNAME).as("FULL_NAME");
DBColumnExpr PAYMENTS_LAST_YEAR =
PAY.AMOUNT.sum().as("PAYMENTS_LAST_YEAR");
// The following expression extracts the extension number from the
phone field
@@ -755,8 +755,7 @@ public class SampleApp
DBColumnExpr PCT_OF_DEPARTMENT_COST =
qryEmpTotal.column(EMP_TOTAL).multiplyWith(100).divideBy(qryDepTotal.column(DEP_TOTAL));
// Create the employee query
DBCommand cmd = db.createCommand();
- cmd.select(EMP.ID, EMP.FIRSTNAME, EMP.LASTNAME);
- cmd.select(DEP.ID, DEP.NAME.as("DEPARTMENT"));
+ cmd.select(EMP.ID, EMP.FIRSTNAME, EMP.LASTNAME,
DEP.NAME.as("DEPARTMENT"));
cmd.select(qryEmpTotal.column(EMP_TOTAL));
cmd.select(PCT_OF_DEPARTMENT_COST.as("PCT_OF_DEPARTMENT_COST"));
// join Employee with Department
diff --git a/empire-db/src/main/java/org/apache/empire/commons/DateUtils.java
b/empire-db/src/main/java/org/apache/empire/commons/DateUtils.java
index edba917..7ae31ef 100644
--- a/empire-db/src/main/java/org/apache/empire/commons/DateUtils.java
+++ b/empire-db/src/main/java/org/apache/empire/commons/DateUtils.java
@@ -30,6 +30,7 @@ import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
+import org.apache.empire.exceptions.InvalidArgumentException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -209,18 +210,22 @@ public class DateUtils
return df.format(date);
}
- public static String formatTime(Date d, Locale locale, boolean withSeconds)
+ public static String formatTime(Date date, Locale locale, boolean
withSeconds)
{
+ if (date==null)
+ return StringUtils.EMPTY;
int style = (withSeconds ? DateFormat.MEDIUM : DateFormat.SHORT);
DateFormat df = DateFormat.getTimeInstance(style,
getSafeLocale(locale));
- return df.format(d);
+ return df.format(date);
}
- public static String formatDayOfWeek(Date d, Locale locale, boolean
longFormat)
+ public static String formatDayOfWeek(Date date, Locale locale, boolean
longFormat)
{
+ if (date==null)
+ return StringUtils.EMPTY;
SimpleDateFormat sdf = new SimpleDateFormat("", getSafeLocale(locale));
Calendar c = Calendar.getInstance(getSafeLocale(locale));
- c.setTime(d);
+ c.setTime(date);
int dayOfWeek = c.get(Calendar.DAY_OF_WEEK);
if (longFormat)
return sdf.getDateFormatSymbols().getWeekdays()[dayOfWeek];
@@ -228,11 +233,13 @@ public class DateUtils
return sdf.getDateFormatSymbols().getShortWeekdays()[dayOfWeek];
}
- public static String formatMonth(Date d, Locale locale, boolean longFormat)
+ public static String formatMonth(Date date, Locale locale, boolean
longFormat)
{
+ if (date==null)
+ return StringUtils.EMPTY;
SimpleDateFormat sdf = new SimpleDateFormat("", getSafeLocale(locale));
Calendar c = Calendar.getInstance(getSafeLocale(locale));
- c.setTime(d);
+ c.setTime(date);
int month = c.get(Calendar.MONTH);
if (longFormat)
return sdf.getDateFormatSymbols().getMonths()[month];
@@ -240,10 +247,12 @@ public class DateUtils
return sdf.getDateFormatSymbols().getShortMonths()[month];
}
- public static int getWeekOfYear(Date d, Locale locale)
+ public static int getWeekOfYear(Date date, Locale locale)
{
+ if (date==null)
+ throw new InvalidArgumentException("date", date);
Calendar c = Calendar.getInstance(getSafeLocale(locale));
- c.setTime(d);
+ c.setTime(date);
return c.get(Calendar.WEEK_OF_YEAR);
}
@@ -256,12 +265,14 @@ public class DateUtils
return sdf.getDateFormatSymbols().getShortMonths()[month];
}
- public static String formatYear(Date d, Locale locale)
+ public static String formatYear(Date date, Locale locale)
{
+ if (date==null)
+ return StringUtils.EMPTY;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy",
getSafeLocale(locale));
Calendar c = Calendar.getInstance(getSafeLocale(locale));
- c.setTime(d);
- return sdf.format(d);
+ c.setTime(date);
+ return sdf.format(date);
}
/*
@@ -342,16 +353,22 @@ public class DateUtils
public static String formatDate(LocalDate localDate, Locale locale)
{
+ if (localDate==null)
+ return StringUtils.EMPTY;
return getLocalDateFormatter(locale).format(localDate);
}
public static String formatDate(LocalDateTime localDateTime, Locale locale)
{
+ if (localDateTime==null)
+ return StringUtils.EMPTY;
return
getLocalDateFormatter(locale).format(localDateTime.toLocalDate());
}
public static String formatDateTime(LocalDateTime localDateTime, Locale
locale, boolean withSeconds)
{
+ if (localDateTime==null)
+ return StringUtils.EMPTY;
return getLocalDateTimeFormatter(locale,
withSeconds).format(localDateTime);
}
diff --git a/empire-db/src/main/java/org/apache/empire/commons/ObjectUtils.java
b/empire-db/src/main/java/org/apache/empire/commons/ObjectUtils.java
index a755f49..f15154e 100644
--- a/empire-db/src/main/java/org/apache/empire/commons/ObjectUtils.java
+++ b/empire-db/src/main/java/org/apache/empire/commons/ObjectUtils.java
@@ -795,9 +795,8 @@ public final class ObjectUtils
}
/**
- * Formats a given date object to a standard date string.
- * The date string is locale independent and has the follwowing format:
- * "yyyy-MM-dd hh:mm:ss"
+ * Formats a given date object to a standard ISO date string.
+ * The format is "yyyy-MM-dd hh:mm:ss"
*
* @param date the date to be formated
* @param withTime indicates whether the date string should include the
time or not
@@ -805,6 +804,8 @@ public final class ObjectUtils
*/
public static String formatDate(Date date, boolean withTime)
{
+ if (date==null)
+ return null;
if (withTime)
return dateTimeFormatter.get().format(date);
else
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
b/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
index e6685b2..5cd965a 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
@@ -34,6 +34,7 @@ import org.apache.empire.db.expr.column.DBAliasExpr;
import org.apache.empire.db.expr.column.DBCalcExpr;
import org.apache.empire.db.expr.column.DBCaseExpr;
import org.apache.empire.db.expr.column.DBConcatExpr;
+import org.apache.empire.db.expr.column.DBConcatFuncExpr;
import org.apache.empire.db.expr.column.DBConvertExpr;
import org.apache.empire.db.expr.column.DBCountExpr;
import org.apache.empire.db.expr.column.DBDecodeExpr;
@@ -1310,5 +1311,26 @@ public abstract class DBColumnExpr extends DBExpr
{
return new DBOrderByExpr(this, true);
}
+
+ /**
+ * concatenates a list of expressions to the current column
+ * @param concatExprs the expressions to concat
+ * @return the concat expression
+ */
+ public DBColumnExpr concat(DBColumnExpr... concatExprs)
+ {
+ return new DBConcatFuncExpr(this, concatExprs);
+ }
+
+ /**
+ * concatenates a list of expressions to the current column
+ * @param separator a string to insert between each of the expressions
+ * @param concatExprs the expressions to concat
+ * @return the concat expression
+ */
+ public DBColumnExpr concat(String separator, DBColumnExpr... concatExprs)
+ {
+ return new DBConcatFuncExpr(this, separator, concatExprs);
+ }
}
\ No newline at end of file
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
b/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
index 6448f5d..c78d49c 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
@@ -742,7 +742,7 @@ public class DBUtils implements DBContextAware
*/
public final <T extends DataListEntry> List<T> queryDataList(DBCommand
cmd, Class<T> entryClass, DataListHead head)
{
- return queryDataList(cmd, createDefaultDataListFactory(entryClass,
head), 0, MAX_QUERY_ROWS);
+ return queryDataList(cmd, createDefaultDataListFactory(entryClass,
head), 0, -1);
}
/**
diff --git
a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBConcatFuncExpr.java
b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBConcatFuncExpr.java
new file mode 100644
index 0000000..8fbe8c1
--- /dev/null
+++
b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBConcatFuncExpr.java
@@ -0,0 +1,96 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.db.expr.column;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.dbms.DBSqlPhrase;
+import org.apache.empire.exceptions.InvalidValueException;
+
+/**
+ * DBConcatExpression
+ * @author doebele
+ */
+public class DBConcatFuncExpr extends DBAbstractFuncExpr
+{
+ private final DBColumnExpr first;
+ private final String separator;
+ private final DBColumnExpr[] others;
+
+ /**
+ * create concat expression
+ * @param first
+ * @param separator
+ * @param others
+ */
+ public DBConcatFuncExpr(DBColumnExpr first, String separator,
DBColumnExpr... others)
+ {
+ super(first, null, false, DataType.VARCHAR);
+ // remember
+ this.first = first;
+ this.separator = separator;
+ this.others = others;
+ }
+
+ /**
+ * create concat expression
+ * @param first
+ * @param others
+ */
+ public DBConcatFuncExpr(DBColumnExpr first, DBColumnExpr... others)
+ {
+ this(first, null, others);
+ }
+
+ @Override
+ protected String getFunctionName()
+ {
+ return "concat";
+ }
+
+ @Override
+ public void addSQL(StringBuilder buf, long context)
+ {
+ // get template
+ String template =
getDatabase().getDbms().getSQLPhrase(DBSqlPhrase.SQL_FUNC_CONCAT);
+ int placeholder = template.indexOf('?');
+ if (placeholder>=0)
+ buf.append(template.substring(0, placeholder));
+ // concat
+ first.addSQL(buf, context);
+ for (int i=0; i<others.length; i++)
+ {
+ if (placeholder>=0)
+ buf.append(", ");
+ else
+ buf.append(template);
+ // insert separator string
+ if (separator!=null && separator.length()>0)
+ { // add a separator
+ buf.append("'");
+ buf.append(separator);
+ buf.append("', ");
+ }
+ others[i].addSQL(buf, context);
+ }
+ if (placeholder>=0)
+ buf.append(template.substring(placeholder+1));
+ }
+
+}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java
b/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java
index bca9a1a..a964f74 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java
@@ -44,6 +44,7 @@ public enum DBSqlPhrase
SQL_FUNC_LTRIM ("ltrim(?)"),
SQL_FUNC_RTRIM ("rtrim(?)"),
SQL_FUNC_ESCAPE ("? escape '{0}'"),
+ SQL_FUNC_CONCAT ("concat(?)"),
// Numeric
SQL_FUNC_ABS ("abs(?)"),
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java
b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java
index a546432..1d513f6 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java
@@ -203,7 +203,7 @@ public class DBMSHandlerHSql extends DBMSHandlerBase
// Convert to text
case VARCHAR:
case CHAR:
- if (format != null)
+ if (format instanceof String)
{ // Convert using a format string
if (srcType == DataType.INTEGER || srcType ==
DataType.AUTOINC)
{
@@ -215,7 +215,15 @@ public class DBMSHandlerHSql extends DBMSHandlerBase
return "to_char(?, '"+format.toString()+"')";
}
}
- return "convert(?, CHAR)";
+ else if (format instanceof Number)
+ { // size given
+ int size = ((Number)format).intValue();
+ return "convert(?,
"+destType.name()+"("+String.valueOf(size)+"))";
+ }
+ else
+ { // default
+ return "convert(?, "+destType.name()+"(255))";
+ }
case INTEGER:
{
return "convert(?, BIGINT)";
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java
b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java
index 0ec6447..eea9835 100644
---
a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java
+++
b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java
@@ -205,6 +205,7 @@ public class DBMSHandlerOracle extends DBMSHandlerBase
case SQL_FUNC_LTRIM: return "ltrim(?)";
case SQL_FUNC_RTRIM: return "rtrim(?)";
case SQL_FUNC_ESCAPE: return "? escape '{0}'";
+ case SQL_FUNC_CONCAT: return " || ";
// Numeric
case SQL_FUNC_ABS: return "abs(?)";
case SQL_FUNC_ROUND: return "round(?,{0})";
@@ -251,7 +252,7 @@ public class DBMSHandlerOracle extends DBMSHandlerBase
case VARCHAR:
case CHAR:
case CLOB:
- if (format != null)
+ if (format instanceof String)
{ // Convert using a format string
return "to_char(?, '"+format.toString()+"')";
}
@@ -260,7 +261,7 @@ public class DBMSHandlerOracle extends DBMSHandlerBase
case INTEGER:
case FLOAT:
case DECIMAL:
- if (format != null)
+ if (format instanceof String)
{ // Convert using a format string
return "to_number(?, '"+format.toString()+"')";
}
@@ -268,13 +269,13 @@ public class DBMSHandlerOracle extends DBMSHandlerBase
// Convert to date
case DATE:
case DATETIME:
- if (format != null)
+ if (format instanceof String)
{ // Convert using a format string
return "to_date(?, '"+format.toString()+"')";
}
return "to_date(?)";
case TIMESTAMP:
- if (format != null)
+ if (format instanceof String)
{ // Convert using a format string
return "to_timestamp(?, '"+format.toString()+"')";
}
