mybatis-user

Providing "global" parameters in mapper files

Classic

List

Threaded

8 messages Options

Mike Fotiou

Providing "global" parameters in mapper files

Hi everyone,

I almost always have to write bilingual web applications. Often this means choosing one field over another in a SQL query (e.g. englishDescription vs frenchDescription) and/or ordering a list of data based on the language (e.g. for a drop-down list). While the ordering can be handled at the action level in Java code, it often makes sense and is a bit more performant if it can be done with SQL.

The locale is in the HTTP session. What I would like to do it always have the language (and perhaps other information, like the logged in user id) available to any mapper statement.

The idea would be to use things like:

<if test="language == 'en'"> order by ....</if>

#{userId}

without having to pass them into the mapper method.

Any ideas as to how to implement this? Can I add these parameters to the OGNL stack that MyBatis uses? Would this be done in a plugin?

Thanks,

Mike

Javier Domingo

Re: Providing "global" parameters in mapper files

Shouldn't you design your database, with a table with descriptions, language and id, with a FK to languages? In that way, you don't have to create different queries.

I know it is more complex, but I think that is the correct way, so that if you want to add another language, you don't have to rewrite your DB...

Cheers,

Javier Domingo

2012/4/30 Mike Fotiou <[hidden email]>

Hi everyone,

I almost always have to write bilingual web applications. Often this means choosing one field over another in a SQL query (e.g. englishDescription vs frenchDescription) and/or ordering a list of data based on the language (e.g. for a drop-down list). While the ordering can be handled at the action level in Java code, it often makes sense and is a bit more performant if it can be done with SQL.

The locale is in the HTTP session. What I would like to do it always have the language (and perhaps other information, like the logged in user id) available to any mapper statement.

The idea would be to use things like:

<if test="language == 'en'"> order by ....</if>

OR

#{userId}

without having to pass them into the mapper method.

Any ideas as to how to implement this? Can I add these parameters to the OGNL stack that MyBatis uses? Would this be done in a plugin?

Thanks,

Mike

Mike Fotiou

Re: Providing "global" parameters in mapper files

Hi Javier,

Yes, ideally that would be the case, but I'm writing web apps for a strictly bilingual audience (French & English).

But I don't have to create different queries, these are just issues of different ORDER BY columns. For example, a combo box of province names would be sorted differently for English vs. French users.

Likewise, if the user was French, you would show him the French description of an item rather than the English.

It's often very efficient and easy to extract the correct field name or ordering in the SQL itself. The flexible set-up you mention would still require knowledge of the user's language in the SQL to specify the right language ID when joining and filtering.

Mike

On Mon, Apr 30, 2012 at 8:40 PM, Javier Domingo <[hidden email]> wrote:

Shouldn't you design your database, with a table with descriptions, language and id, with a FK to languages? In that way, you don't have to create different queries.

I know it is more complex, but I think that is the correct way, so that if you want to add another language, you don't have to rewrite your DB...

Cheers,

Javier Domingo

2012/4/30 Mike Fotiou <[hidden email]>
Hi everyone,

I almost always have to write bilingual web applications. Often this means choosing one field over another in a SQL query (e.g. englishDescription vs frenchDescription) and/or ordering a list of data based on the language (e.g. for a drop-down list). While the ordering can be handled at the action level in Java code, it often makes sense and is a bit more performant if it can be done with SQL.

The locale is in the HTTP session. What I would like to do it always have the language (and perhaps other information, like the logged in user id) available to any mapper statement.

The idea would be to use things like:

<if test="language == 'en'"> order by ....</if>

OR

#{userId}

without having to pass them into the mapper method.

Any ideas as to how to implement this? Can I add these parameters to the OGNL stack that MyBatis uses? Would this be done in a plugin?

Thanks,

Mike

Terenyi

Re: Providing "global" parameters in mapper files

Hi,

You have the same problems as we had...

My attached diff and class on your "help with caching of 'association' nested
select" thread (Mar 16) contains a parameter transformer mechanism, which we
use to extend the parameters for every statement with fixed ones. Exactly for
the same reasons: language and row based security.

There is no "out of the box" solution for this problem in myBatis.

Best regards,
Balázs

P.S.: There is some more info on thread "Adding where clauses from database."
from 2011-07-13

On 2012. April 30. 23:03:25 Mike Fotiou wrote:

> Hi Javier,
>
> Yes, ideally that would be the case, but I'm writing web apps for
> a strictly bilingual audience (French & English).
>
> But I don't have to create different queries, these are just issues of
> different ORDER BY columns. For example, a combo box of province names
> would be sorted differently for English vs. French users.
>
> Likewise, if the user was French, you would show him the French description
> of an item rather than the English.
>
> It's often very efficient and easy to extract the correct field name or
> ordering in the SQL itself. The flexible set-up you mention would still
> require knowledge of the user's language in the SQL to specify the right
> language ID when joining and filtering.
>
> Mike
>
> On Mon, Apr 30, 2012 at 8:40 PM, Javier Domingo <[hidden email]> wrote:
> > Shouldn't you design your database, with a table with descriptions,
> > language and id, with a FK to languages? In that way, you don't have to
> > create different queries.
> >
> > I know it is more complex, but I think that is the correct way, so that
> > if you want to add another language, you don't have to rewrite your
> > DB...
> >
> > Cheers,
> >
> > Javier Domingo
> >
> >
> >
> > 2012/4/30 Mike Fotiou <[hidden email]>
> >
> >> Hi everyone,
> >>
> >> I almost always have to write bilingual web applications. Often this
> >> means choosing one field over another in a SQL query (e.g.
> >> englishDescription vs frenchDescription) and/or ordering a list of
> >> data
> >> based on the language (e.g. for a drop-down list). While the ordering
> >> can be handled at the action level in Java code, it often makes sense
> >> and is a bit more performant if it can be done with SQL.
> >>
> >> The locale is in the HTTP session. What I would like to do it always
> >> have the language (and perhaps other information, like the logged in
> >> user id) available to any mapper statement.
> >>
> >> The idea would be to use things like:
> >>
> >> <if test="language == 'en'"> order by ....</if>
> >>
> >> OR
> >>
> >> #{userId}
> >>
> >> without having to pass them into the mapper method.
> >>
> >> Any ideas as to how to implement this? Can I add these parameters to
> >> the OGNL stack that MyBatis uses? Would this be done in a plugin?
> >>
> >> Thanks,
> >>
> >> Mike

Javier Domingo

Re: Providing "global" parameters in mapper files

I insist, that independently of the number of languages you have, a fully normalized relational DB can't have that type of structure. So that patch you said, is the only way to support that type of cases.

Javier Domingo

2012/5/1 Terényi Balázs <[hidden email]>

Hi,

You have the same problems as we had...

My attached diff and class on your "help with caching of 'association' nested
select" thread (Mar 16) contains a parameter transformer mechanism, which we
use to extend the parameters for every statement with fixed ones. Exactly for
the same reasons: language and row based security.

There is no "out of the box" solution for this problem in myBatis.

Best regards,
Balázs

P.S.: There is some more info on thread "Adding where clauses from database."
from 2011-07-13

On 2012. April 30. 23:03:25 Mike Fotiou wrote:
> Hi Javier,
>
> Yes, ideally that would be the case, but I'm writing web apps for
> a strictly bilingual audience (French & English).
>
> But I don't have to create different queries, these are just issues of
> different ORDER BY columns. For example, a combo box of province names
> would be sorted differently for English vs. French users.
>
> Likewise, if the user was French, you would show him the French description
> of an item rather than the English.
>
> It's often very efficient and easy to extract the correct field name or
> ordering in the SQL itself. The flexible set-up you mention would still
> require knowledge of the user's language in the SQL to specify the right
> language ID when joining and filtering.
>
> Mike
>
> On Mon, Apr 30, 2012 at 8:40 PM, Javier Domingo <[hidden email]> wrote:
> > Shouldn't you design your database, with a table with descriptions,
> > language and id, with a FK to languages? In that way, you don't have to
> > create different queries.
> >
> > I know it is more complex, but I think that is the correct way, so that
> > if you want to add another language, you don't have to rewrite your
> > DB...
> >
> > Cheers,
> >
> > Javier Domingo
> >
> >
> >
> > 2012/4/30 Mike Fotiou <[hidden email]>
> >
> >> Hi everyone,
> >>
> >> I almost always have to write bilingual web applications. Often this
> >> means choosing one field over another in a SQL query (e.g.
> >> englishDescription vs frenchDescription) and/or ordering a list of
> >> data
> >> based on the language (e.g. for a drop-down list). While the ordering
> >> can be handled at the action level in Java code, it often makes sense
> >> and is a bit more performant if it can be done with SQL.
> >>
> >> The locale is in the HTTP session. What I would like to do it always
> >> have the language (and perhaps other information, like the logged in
> >> user id) available to any mapper statement.
> >>
> >> The idea would be to use things like:
> >>
> >> <if test="language == 'en'"> order by ....</if>
> >>
> >> OR
> >>
> >> #{userId}
> >>
> >> without having to pass them into the mapper method.
> >>
> >> Any ideas as to how to implement this? Can I add these parameters to
> >> the OGNL stack that MyBatis uses? Would this be done in a plugin?
> >>
> >> Thanks,
> >>
> >> Mike

Kurth, Felix

RE: Providing "global" parameters in mapper files

In reply to this post by Terenyi

I "solved" the problem of "global parameters" with a crude interceptor plug-in hack, in my case to provide the TableNames dynamically :
I introduced a new parameter type %{gobalParameter} (Note the "%")

// The interceptor code:

@Intercepts({@Signature(type= Executor.class, method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class InterceptQuery extends InterceptBase implements Interceptor {

public Object intercept(Invocation invocation) throws Throwable {
return invocation.getMethod().invoke(invocation.getTarget(), inteceptArgs(invocation.getArgs()));
}
}

// The base class
public abstract class InterceptBase implements Interceptor {

private TableNameService tableNameService;

@SuppressWarnings({ "unchecked", "rawtypes" })
protected Object[] inteceptArgs(Object[] args) {

MappedStatement st = (MappedStatement) args[0];

try {
Field field = st.getClass().getDeclaredField("sqlSource");
field.setAccessible(true);
SqlSource sqlSource = (SqlSource) field.get(st);
field.set(st, new DynamicSqlSourceProxy(sqlSource, tableNameService) );
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return args;

}

public Object plugin(Object target) {
return Plugin.wrap(target, this);
}

public void setProperties(Properties properties) {

}

public TableNameService getTableNameService() {

return tableNameService;
}

public void setTableNameService(TableNameService tableNameService) {

this.tableNameService = tableNameService;
}

}

/**
* This proxy class wraps around the generated sql statements and replaces all %{} occurrences
* with strings from Tokenhandler service
*/
public class DynamicSqlSourceProxy implements SqlSource {

SqlSource impl;
GenericTokenParser parser;

public DynamicSqlSourceProxy(SqlSource impl, TokenHandler handler) {
this.impl = impl;
this.parser = new GenericTokenParser("%{", "}", handler);
}

@Override
public BoundSql getBoundSql(Object parameterObject) {

BoundSql sql = impl.getBoundSql(parameterObject);
String newsql = parser.parse(sql.getSql());

try {
Field sqlField = BoundSql.class.getDeclaredField("sql");
sqlField.setAccessible(true);
sqlField.set(sql,newsql);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}

return sql;
}

}

With this @Intercept annotation, all select queries are intercepted. Apply this for other query types as well, for example:

@Intercepts({@Signature(type= Executor.class, method = "update",args = {MappedStatement.class, Object.class})})
public class InterceptUpdate extends InterceptBase implements Interceptor {

public Object intercept(Invocation invocation) throws Throwable {

return invocation.getMethod().invoke(invocation.getTarget(), inteceptArgs(invocation.getArgs()));
}

}

This code does not work with nested selects because the @Intercept annotation does not work on them, as I posted earlier with no answer.

Now a Statement can look like
select * from %{TableName} where .....

Yes, I know, a really crude hack. Any ideas to do this better?

Cheers, Felix

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Terényi Balázs
Sent: Dienstag, 1. Mai 2012 20:43
To: [hidden email]
Subject: Re: Providing "global" parameters in mapper files

Hi,

You have the same problems as we had...

My attached diff and class on your "help with caching of 'association' nested
select" thread (Mar 16) contains a parameter transformer mechanism, which we
use to extend the parameters for every statement with fixed ones. Exactly for
the same reasons: language and row based security.

There is no "out of the box" solution for this problem in myBatis.

Best regards,
Balázs

P.S.: There is some more info on thread "Adding where clauses from database."
from 2011-07-13

On 2012. April 30. 23:03:25 Mike Fotiou wrote:

Mike Fotiou

Re: Providing "global" parameters in mapper files

Hi Felix,

I solved it in an equally hackish way, where I convert the incoming parameter into a Map or add to an existing Map using this plugin interceptor on queries. I use Guice in my projects, so I inject the injector directly into the plugin during the Mybatis bootstrap phase (part of Mybatis-Guice). This does force me to "name" the parameter in single-parameter method calls to "default", so the query would have to use #{default.propery} instead of just #{#property}. Your way has the benefit of not requiring any changes to regular Mybatis behaviour, but does introduce a new syntax, which I actually like since it is very clear that a global parameter is being used. I'm going to give it a try, thanks!

Here is the plugin I used:

@Intercepts({

@Signature(type=Executor.class, method="query", args={MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})

})

public class GlobalParameterPlugin implements Interceptor {

@Override

public Object intercept(Invocation invocation) throws Throwable {

// If it's a Map already, just add the locale and any other additional parameters; otherwise, replace the parameter object with a Map and add the additional parameters

Object oldParameter = invocation.getArgs()[1];

Map<String, Object> newParameter = null;

if ( oldParameter instanceof java.util.Map ) {

newParameter = (Map)oldParameter;

} else {

newParameter = new HashMap<String, Object>();

if ( oldParameter != null ) {

newParameter.put("default", oldParameter);

}

newParameter.put("language", getLanguage()); // the getLanguage actually get an HttpSession instance from the Guice injector; any other parameters could go here as well.

invocation.getArgs()[1] = newParameter;

return invocation.proceed();

}

On Fri, May 4, 2012 at 7:52 AM, Kurth, Felix <[hidden email]> wrote:

I "solved" the problem of "global parameters" with a crude interceptor plug-in hack, in my case to provide the TableNames dynamically :
I introduced a new parameter type %{gobalParameter} (Note the "%")

// The interceptor code:

@Intercepts({@Signature(type= Executor.class, method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class InterceptQuery extends InterceptBase implements Interceptor {

public Object intercept(Invocation invocation) throws Throwable {
return invocation.getMethod().invoke(invocation.getTarget(), inteceptArgs(invocation.getArgs()));
}
}

// The base class
public abstract class InterceptBase implements Interceptor {

private TableNameService tableNameService;

@SuppressWarnings({ "unchecked", "rawtypes" })
protected Object[] inteceptArgs(Object[] args) {

MappedStatement st = (MappedStatement) args[0];

try {
Field field = st.getClass().getDeclaredField("sqlSource");
field.setAccessible(true);
SqlSource sqlSource = (SqlSource) field.get(st);
field.set(st, new DynamicSqlSourceProxy(sqlSource, tableNameService) );
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return args;

}

public Object plugin(Object target) {
return Plugin.wrap(target, this);
}

public void setProperties(Properties properties) {

}

public TableNameService getTableNameService() {

return tableNameService;
}

public void setTableNameService(TableNameService tableNameService) {

this.tableNameService = tableNameService;
}

}

/**
* This proxy class wraps around the generated sql statements and replaces all %{} occurrences
* with strings from Tokenhandler service
*/
public class DynamicSqlSourceProxy implements SqlSource {

SqlSource impl;
GenericTokenParser parser;

public DynamicSqlSourceProxy(SqlSource impl, TokenHandler handler) {
this.impl = impl;
this.parser = new GenericTokenParser("%{", "}", handler);
}

@Override
public BoundSql getBoundSql(Object parameterObject) {

BoundSql sql = impl.getBoundSql(parameterObject);
String newsql = parser.parse(sql.getSql());

try {
Field sqlField = BoundSql.class.getDeclaredField("sql");
sqlField.setAccessible(true);
sqlField.set(sql,newsql);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}

return sql;
}

}

With this @Intercept annotation, all select queries are intercepted. Apply this for other query types as well, for example:

@Intercepts({@Signature(type= Executor.class, method = "update",args = {MappedStatement.class, Object.class})})
public class InterceptUpdate extends InterceptBase implements Interceptor {

public Object intercept(Invocation invocation) throws Throwable {

return invocation.getMethod().invoke(invocation.getTarget(), inteceptArgs(invocation.getArgs()));
}

}

This code does not work with nested selects because the @Intercept annotation does not work on them, as I posted earlier with no answer.

Now a Statement can look like
select * from %{TableName} where .....

Yes, I know, a really crude hack. Any ideas to do this better?

Cheers, Felix

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Terényi Balázs
Sent: Dienstag, 1. Mai 2012 20:43
To: [hidden email]
Subject: Re: Providing "global" parameters in mapper files

Hi,

You have the same problems as we had...

My attached diff and class on your "help with caching of 'association' nested
select" thread (Mar 16) contains a parameter transformer mechanism, which we
use to extend the parameters for every statement with fixed ones. Exactly for
the same reasons: language and row based security.

There is no "out of the box" solution for this problem in myBatis.

Best regards,
Balázs

P.S.: There is some more info on thread "Adding where clauses from database."
from 2011-07-13

On 2012. April 30. 23:03:25 Mike Fotiou wrote:
> Hi Javier,
>
> Yes, ideally that would be the case, but I'm writing web apps for
> a strictly bilingual audience (French & English).
>
> But I don't have to create different queries, these are just issues of
> different ORDER BY columns. For example, a combo box of province names
> would be sorted differently for English vs. French users.
>
> Likewise, if the user was French, you would show him the French description
> of an item rather than the English.
>
> It's often very efficient and easy to extract the correct field name or
> ordering in the SQL itself. The flexible set-up you mention would still
> require knowledge of the user's language in the SQL to specify the right
> language ID when joining and filtering.
>
> Mike
>
> On Mon, Apr 30, 2012 at 8:40 PM, Javier Domingo <[hidden email]> wrote:
> > Shouldn't you design your database, with a table with descriptions,
> > language and id, with a FK to languages? In that way, you don't have to
> > create different queries.
> >
> > I know it is more complex, but I think that is the correct way, so that
> > if you want to add another language, you don't have to rewrite your
> > DB...
> >
> > Cheers,
> >
> > Javier Domingo
> >
> >
> >
> > 2012/4/30 Mike Fotiou <[hidden email]>
> >
> >> Hi everyone,
> >>
> >> I almost always have to write bilingual web applications. Often this
> >> means choosing one field over another in a SQL query (e.g.
> >> englishDescription vs frenchDescription) and/or ordering a list of
> >> data
> >> based on the language (e.g. for a drop-down list). While the ordering
> >> can be handled at the action level in Java code, it often makes sense
> >> and is a bit more performant if it can be done with SQL.
> >>
> >> The locale is in the HTTP session. What I would like to do it always
> >> have the language (and perhaps other information, like the logged in
> >> user id) available to any mapper statement.
> >>
> >> The idea would be to use things like:
> >>
> >> <if test="language == 'en'"> order by ....</if>
> >>
> >> OR
> >>
> >> #{userId}
> >>
> >> without having to pass them into the mapper method.
> >>
> >> Any ideas as to how to implement this? Can I add these parameters to
> >> the OGNL stack that MyBatis uses? Would this be done in a plugin?
> >>
> >> Thanks,
> >>
> >> Mike

Jose María Zaragoza

Re: Providing "global" parameters in mapper files

In reply to this post by Kurth, Felix

>
> With this @Intercept annotation, all select queries are intercepted. Apply this for other query types as well, for example:

Interesting.
Where I can find more info about this annotation ?

Thanks