Java – Error in MyBatis while setting parameters

ibatisjavamybatispostgresql

I get this error when running select in MyBatis with PostgreSQL:

### The error may exist in data/mapper.xml
### The error may involve Transaccion.selectDeFraude-Inline
### The error occurred while setting parameters
### SQL: SELECT transaction_id, card_number, transaction_date, fraud FROM transactions.? ORDER BY card_number, transaction_date ASC;
### Cause: org.postgresql.util.PSQLException: ERROR: error de sintaxis en o cerca de «$1»

I get the error here, in the mapper.xml:

<select id="selectDeFraude" parameterType="String" resultMap="result">
    SELECT transaction_id, card_number, transaction_date, fraud FROM transactions.#{tabla} ORDER BY card_number, transaction_date ASC;
</select>

This is the method that calls the select:

public List<Transaccion> selectDeFraude(String tabla){

    SqlSession session = sqlSessionFactory.openSession();

    try {
        List<Transaccion> list = session.selectList("Transaccion.selectDeFraude", tabla);
        return list;
    } finally {
        session.close();
    }
}

If I replace #{tabla} with the name of the table it works just fine. None of the mapper methods work but all of them work if I replace the #{something} with the appropriate value.

Best Answer

It's not exactly the answer to the question but I got it working now. When mapping a table name with myBatis you should use ${table_name} instead of #{table_name}, and it should be an attribute of the object you passed as an argument.

I changed my code to look like this:

<select id="selectDeFraude" parameterType="Transaccion" resultMap="result">
    SELECT transaction_id, card_number, transaction_date, fraud FROM transactions.${tabla} ORDER BY card_number, transaction_date ASC;
</select>

And I added the property tabla to it and know is working just fine.

Related Solutions

Java optional parameters

There are several ways to simulate optional parameters in Java:

Method overloading.

void foo(String a, Integer b) { //... }

void foo(String a) { foo(a, 0); // here, 0 is a default value for b }

foo("a", 2); foo("a");

One of the limitations of this approach is that it doesn't work if you have two optional parameters of the same type and any of them can be omitted.

Varargs.

a) All optional parameters are of the same type:

    void foo(String a, Integer... b) {
        Integer b1 = b.length > 0 ? b[0] : 0;
        Integer b2 = b.length > 1 ? b[1] : 0;
        //...
    }

    foo("a");
    foo("a", 1, 2);

b) Types of optional parameters may be different:

    void foo(String a, Object... b) {
        Integer b1 = 0;
        String b2 = "";
        if (b.length > 0) {
          if (!(b[0] instanceof Integer)) { 
              throw new IllegalArgumentException("...");
          }
          b1 = (Integer)b[0];
        }
        if (b.length > 1) {
            if (!(b[1] instanceof String)) { 
                throw new IllegalArgumentException("...");
            }
            b2 = (String)b[1];
            //...
        }
        //...
    }

    foo("a");
    foo("a", 1);
    foo("a", 1, "b2");

The main drawback of this approach is that if optional parameters are of different types you lose static type checking. Furthermore, if each parameter has the different meaning you need some way to distinguish them.

Nulls. To address the limitations of the previous approaches you can allow null values and then analyze each parameter in a method body:

void foo(String a, Integer b, Integer c) { b = b != null ? b : 0; c = c != null ? c : 0; //... }

foo("a", null, 2);

Now all arguments values must be provided, but the default ones may be null.

Optional class. This approach is similar to nulls, but uses Java 8 Optional class for parameters that have a default value:

void foo(String a, Optional bOpt) { Integer b = bOpt.isPresent() ? bOpt.get() : 0; //... }

foo("a", Optional.of(2)); foo("a", Optional.absent());

Optional makes a method contract explicit for a caller, however, one may find such signature too verbose.

Update: Java 8 includes the class java.util.Optional out-of-the-box, so there is no need to use guava for this particular reason in Java 8. The method name is a bit different though.

Builder pattern. The builder pattern is used for constructors and is implemented by introducing a separate Builder class:

class Foo {
    private final String a; 
    private final Integer b;

    Foo(String a, Integer b) {
      this.a = a;
      this.b = b;
    }

    //...
}

class FooBuilder {
  private String a = ""; 
  private Integer b = 0;

  FooBuilder setA(String a) {
    this.a = a;
    return this;
  }

  FooBuilder setB(Integer b) {
    this.b = b;
    return this;
  }

  Foo build() {
    return new Foo(a, b);
  }
}

Foo foo = new FooBuilder().setA("a").build();

Maps. When the number of parameters is too large and for most of the default values are usually used, you can pass method arguments as a map of their names/values:

void foo(Map<String, Object> parameters) { String a = ""; Integer b = 0; if (parameters.containsKey("a")) { if (!(parameters.get("a") instanceof Integer)) { throw new IllegalArgumentException("..."); } a = (Integer)parameters.get("a"); } if (parameters.containsKey("b")) { //... } //... }

foo(ImmutableMap.<String, Object>of( "a", "a", "b", 2, "d", "value"));

In Java 9, this approach became easier:
```
@SuppressWarnings("unchecked")
static <T> T getParm(Map<String, Object> map, String key, T defaultValue) {
  return (map.containsKey(key)) ? (T) map.get(key) : defaultValue;
}

void foo(Map<String, Object> parameters) {
  String a = getParm(parameters, "a", "");
  int b = getParm(parameters, "b", 0);
  // d = ...
}

foo(Map.of("a","a",  "b",2,  "d","value"));
```

Please note that you can combine any of these approaches to achieve a desirable result.

Java URL encoding of query string parameters

URLEncoder is the way to go. You only need to keep in mind to encode only the individual query string parameter name and/or value, not the entire URL, for sure not the query string parameter separator character & nor the parameter name-value separator character =.

String q = "random word £500 bank $";
String url = "https://example.com?q=" + URLEncoder.encode(q, StandardCharsets.UTF_8);

When you're still not on Java 10 or newer, then use StandardCharsets.UTF_8.toString() as charset argument, or when you're still not on Java 7 or newer, then use "UTF-8".

Note that spaces in query parameters are represented by +, not %20, which is legitimately valid. The %20 is usually to be used to represent spaces in URI itself (the part before the URI-query string separator character ?), not in query string (the part after ?).

Also note that there are three encode() methods. One without Charset as second argument and another with String as second argument which throws a checked exception. The one without Charset argument is deprecated. Never use it and always specify the Charset argument. The javadoc even explicitly recommends to use the UTF-8 encoding, as mandated by RFC3986 and W3C.

All other characters are unsafe and are first converted into one or more bytes using some encoding scheme. Then each byte is represented by the 3-character string "%xy", where xy is the two-digit hexadecimal representation of the byte. The recommended encoding scheme to use is UTF-8. However, for compatibility reasons, if an encoding is not specified, then the default encoding of the platform is used.

Best Answer

Related Solutions

Java optional parameters

Java URL encoding of query string parameters

See also:

Related Topic