2017-08-24

SQL "with"-Statements auf JasperServer

Möchte man in JasperReports with-Statements im Query verwenden, funktioniert das im Studio problemlos, deployed man den Report allerdings auf den Server liefert dieser beim Ausführen eine Security-Exception.

Hintergrund ist das Sicherheitskonzept des JasperServers, welches verhindern soll, dass etwa durch SQL-Injections Schaden an der Datenbank entstehen kann. Leider ist die Validator-Regel zunächst aber etwas zu restriktiv. Zum Glück kann man diese anpassen:

In der Datei

...\jasperserver\WEB-INF\classes\esapi\validation.properties

die Zeile

Validator.ValidSQL=(?is)^\\s*(select|call)\\s+[^;]+;?\\s*$

ersetzen durch

Validator.ValidSQL=(?is)^\\s*(select|call|with)\\s+[^;]+;?\\s*$ 

2017-08-10

Fixing Currency Behaviour in JDeveloper / ADF 12c

As many questions regarding Currency fields in ADF have come to me lately, let me just give you an example of how to tackle these.

It seems, that there is a common error in ADF for German users (or to any other language,where the decimal and grouping delimiter is exactly switched to the English definition).

English (US) Format: $ #,###.##

German Format #.###,##€

As you see, for germans the ',' is changing places wit the '.'

Normally, this would not be much of a deal, but in ADF there seems to be a "hard coded" interpretation somewhere.

So in a simple form, let us type in a salary of 9.999,99 which is a correct entry for the salary field in German.


In case you enter it as the hint provides (that means including the currency symbol) everything seems to work. But for many cases, users are not accustomed to entering the currency symbol.

So let us create our own converter class to fix this issue:

At first, create a java class that is implementing the javax.faces.convert.Converter Interface. This should be annotated by the FacesConverter tag to make it selectable in the UIComponents properties afterwards and removes the requirement to handle the faces-config.xml tracking.


package de.teampb.conv;

import java.math.BigDecimal;

import java.text.DecimalFormatSymbols;
import java.text.NumberFormat;

import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.ConverterException;
import javax.faces.convert.FacesConverter;

import oracle.adf.share.logging.ADFLogger;

/**
 * Custom JSF Faces Converter to convert entries in Currency Input Texts in a correct way. Oracle ADF seems to be a bit
 * confused, if the grouping character in a Locale is '.' and the decimal delimiter is '.' (for example in Germany).
 */
@FacesConverter("de.teampb.conv.CurrencyConverter")
public class CurrencyConverter implements Converter {

    /**
     * Class logger.
     */
    private static final ADFLogger LOG = ADFLogger.createADFLogger(CurrencyConverter.class.getName());

    /**
     * Converter method from UI Entry to data value. Takes an Input String from the UI Component and converts it to a
     * BigDecimal value for data changes.
     *
     * @param facesContext current JSF Context
     * @param uIComponent Component that has a new value
     * @param string Entered String value (may contain groupings, delimiter or currency symbol)
     * @return correctly converted BigDecimal object for the given input
     */
    @Override
    public Object getAsObject(FacesContext facesContext, UIComponent uIComponent, String string) {
        LOG.entering("CurrencyConverter", "getAsObject", new Object[] { uIComponent, string });
        BigDecimal result;

        final Locale locale = facesContext.getViewRoot().getLocale();
        LOG.finest("Locale for Conversion: " + locale.getLanguage());

        if (string != null && !string.isEmpty()) {
            LOG.finest("Parsing numeric sanity of string...");
            Pattern regex = Pattern.compile("[&:;=?@#|]|[a-zA-Z]");
            Matcher matcher = regex.matcher(string);
            if (matcher.find()) {
                NumberFormat f = NumberFormat.getCurrencyInstance(locale);
                throw new ConverterException(f.format(123456.78));
            }
            LOG.finest("...done");

            String res = string;
            DecimalFormatSymbols symbols = new DecimalFormatSymbols(locale);

            // get Locale specific grouping and decimal seperators
            char sep = symbols.getDecimalSeparator();
            LOG.finest("Decimal Separator used: " + sep);
            char grp = symbols.getGroupingSeparator();
            LOG.finest("Grouping Separator used: " + grp);

            // remove groupings
            String valueConverted = string.replace(grp, Character.MIN_VALUE);
            LOG.finest("String removed of groups:" + valueConverted);

            // change decimal seperator to "US" standards
            valueConverted = valueConverted.replace(sep, ".".charAt(0));
            LOG.finest("String with changed decimal separator:" + valueConverted);

            // throw away any non numeric stuff
            res = valueConverted.replaceAll("[^\\d.]+", "");

            LOG.finest("Expected result:" + res);
            result = new BigDecimal(res);
        } else {
            LOG.finest("Input was empty, so create a zero object");
            // this is of course project specific, can also return null etc.
            result = new BigDecimal(0);
        }
        LOG.exiting("CurrencyConverter", "getAsObject", result);
        return result;
    }

    /**
     * Converter method to create a correct currency String for a given data object.
     *
     * @param facesContext current JSF Context
     * @param uIComponent UI Component that will get the String value
     * @param object data value that shall be converted
     * @return correct String representation of data to a set Locale
     */
    @Override
    public String getAsString(FacesContext facesContext, UIComponent uIComponent, Object object) {
        LOG.entering("CurrencyConverter", "getAsString", object);

        final Locale locale = facesContext.getViewRoot().getLocale();

        LOG.finest("Locale for Conversion: " + locale.getLanguage());
        NumberFormat f = NumberFormat.getCurrencyInstance(locale);
        String res = f.format(object);

        LOG.exiting("CurrencyConverter", "getAsString", res);
        return res;
    }
}

Hint: As you can see from the source code, the converter uses the Locale's specific grouping and decimal seperators, so this converter should be working for any given locale and currency representation.

Next, add this converter to a converter component inside the UI Field that should use this converter:



That's it, we can now just enter the values in simple fashion.

If we enter the value 9999,99 once again, we will see the estimated behaviour.





This entry shows, how easy it is to create a converter to a RichUIInput component, another nice use case could be, that you enter a value with a currency and then use the converter to calculate the currency exchange rate and thus convert it to the databases default currency.

Feel free to check out an example project from the German ADF Community (a subgroup of the German Enterprise Application Development Group) github:

ADFCommunityDE/ADFCustomCurrencyConverter

Thanks for the read, cheers!