Pregunta ¿Cómo usar Anotaciones con iBatis (myBatis) para una consulta IN?


Nos gustaría usar solo anotaciones con MyBatis; Realmente estamos tratando de evitar xml. Estamos tratando de usar una cláusula "IN":

@Select("SELECT * FROM blog WHERE id IN (#{ids})") 
List<Blog> selectBlogs(int[] ids); 

MyBatis no parece poder seleccionar la matriz de entradas y ponerlas en la consulta resultante. Parece "fallar suavemente" y no obtenemos ningún resultado.

Parece que podríamos lograr esto usando mapeos XML, pero realmente nos gustaría evitar eso. ¿Hay una sintaxis de anotación correcta para esto?


21
2017-08-07 01:09


origen


Respuestas:


Creo que este es un matiz de las declaraciones preparadas de jdbc y no de MyBatis. Hay un enlace aquí eso explica este problema y ofrece varias soluciones. Desafortunadamente, ninguna de estas soluciones es viable para su aplicación, sin embargo, sigue siendo una buena lectura para comprender las limitaciones de las declaraciones preparadas con respecto a una cláusula "IN". Una solución (quizás no óptima) se puede encontrar en el lado específico de DB. Por ejemplo, en postgresql, uno podría usar:

"SELECT * FROM blog WHERE id=ANY(#{blogIds}::int[])"

"CUALQUIERA" es lo mismo que "ENTRAR" y ":: int []" es escribir el argumento en una matriz de entradas. El argumento que se alimenta en la declaración debe verse algo así como:

"{1,2,3,4}"

17
2017-08-09 16:35



Creo que la respuesta es la misma que se da en esta pregunta. Puede usar myBatis Dynamic SQL en sus anotaciones haciendo lo siguiente:

@Select({"<script>",
         "SELECT *", 
         "FROM blog",
         "WHERE id IN", 
           "<foreach item='item' index='index' collection='list'",
             "open='(' separator=',' close=')'>",
             "#{item}",
           "</foreach>",
         "</script>"}) 
List<Blog> selectBlogs(@Param("list") int[] ids);

los <script> elemento habilita el análisis dinámico SQL y la ejecución para la anotación. Debe ser el primer contenido de la cadena de consulta. Nada debe estar delante de él, ni siquiera espacio en blanco.

Tenga en cuenta que las variables que puede usar en las diversas etiquetas de script XML siguen las mismas convenciones de nomenclatura que las consultas regulares, por lo que si desea hacer referencia a los argumentos de su método utilizando nombres que no sean "param1", "param2", etc ... necesita prefijar cada argumento con una anotación @Param.


28
2018-03-25 21:19



Tenía alguna investigación sobre este tema.

  1. una de las soluciones oficiales de mybatis es poner su sql dinámico en @Select("<script>...</script>"). Sin embargo, escribir xml en la anotación java es bastante desagradable. piensa sobre esto @Select("<script>select name from sometable where id in <foreach collection=\"items\" item=\"item\" seperator=\",\" open=\"(\" close=\")\">${item}</script>")
  2. @SelectProvider funciona bien. Pero es un poco complicado de leer.
  3. PreparedStatement no le permite establecer la lista de enteros. pstm.setString(index, "1,2,3,4") le permitirá a su SQL así select name from sometable where id in ('1,2,3,4'). Mysql convertirá los caracteres '1,2,3,4' Al numero 1.
  4. FIND_IN_SET no funciona con el índice mysql.

Mira en mybatis mecanismo dinámico sql, ha sido implementado por SqlNode.apply(DynamicContext). Sin embargo, @Select sin <script></script> la anotación no pasará el parámetro a través de DynamicContext 

ver también

  • org.apache.ibatis.scripting.xmltags.XMLLanguageDriver
  • org.apache.ibatis.scripting.xmltags.DynamicSqlSource
  • org.apache.ibatis.scripting.xmltags.RawSqlSource

Asi que,

  • Solución 1: use @SelectProvider
  • Solución 2: Extienda LanguageDriver, que siempre compilará sql para DynamicSqlSource. Sin embargo, todavía tienes que escribir \" en todos lados.
  • Solución 3: Extienda LanguageDriver que puede convertir su propia gramática a mybatis.
  • Solución 4: escriba su propio LanguageDriver que compila SQL con algún renderizador de plantilla, como lo hace el proyecto mybatis-velocity. De esta manera, puedes incluso integrar groovy.

Mi proyecto toma la solución 3 y aquí está el código:

public class MybatisExtendedLanguageDriver extends XMLLanguageDriver 
                                           implements LanguageDriver {
    private final Pattern inPattern = Pattern.compile("\\(#\\{(\\w+)\\}\\)");
    public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
        Matcher matcher = inPattern.matcher(script);
        if (matcher.find()) {
            script = matcher.replaceAll("(<foreach collection=\"$1\" item=\"__item\" separator=\",\" >#{__item}</foreach>)");
        }
        script = "<script>" + script + "</script>";
        return super.createSqlSource(configuration, script, parameterType);
    }
}

Y el uso:

@Lang(MybatisExtendedLanguageDriver.class)
@Select("SELECT " + COLUMNS + " FROM sometable where id IN (#{ids})")
List<SomeItem> loadByIds(@Param("ids") List<Integer> ids);

9
2018-03-16 11:51



He hecho un pequeño truco en mi código.

public class MyHandler implements TypeHandler {

public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
    Integer[] arrParam = (Integer[]) parameter;
    String inString = "";
    for(Integer element : arrParam){
      inString = "," + element;
    }
    inString = inString.substring(1);        
    ps.setString(i,inString);
}

Y usé este MyHandler en SqlMapper:

    @Select("select id from tmo where id_parent in (#{ids, typeHandler=ru.transsys.test.MyHandler})")
public List<Double> getSubObjects(@Param("ids") Integer[] ids) throws SQLException;

Ahora funciona :) Espero que esto ayude a alguien.

Evgeny


6
2017-12-17 15:54



Otra opción puede ser

    public class Test
    {
        @SuppressWarnings("unchecked")
        public static String getTestQuery(Map<String, Object> params)
        {

            List<String> idList = (List<String>) params.get("idList");

            StringBuilder sql = new StringBuilder();

            sql.append("SELECT * FROM blog WHERE id in (");
            for (String id : idList)
            {
                if (idList.indexOf(id) > 0)
                    sql.append(",");

                sql.append("'").append(id).append("'");
            }
            sql.append(")");

            return sql.toString();
        }

        public interface TestMapper
        {
            @SelectProvider(type = Test.class, method = "getTestQuery")
List<Blog> selectBlogs(@Param("idList") int[] ids);
        }
    }

3
2018-04-26 10:10



Me temo que la solución de Evgeny solo parece funcionar porque hay un pequeño error en la muestra del código:

  inString = "," + element;

Lo que significa que inString siempre contiene un único y último número (en lugar de una lista de números concatenados).

Esto debería ser de hecho

  inString += "," + element;

Por desgracia, si se corrige este error, la base de datos comienza a informar excepciones de "número incorrecto" porque mybatis establece "1,2,3" como un parámetro de cadena y la base de datos simplemente intenta convertir esta cadena en un número: /

Por otro lado, la anotación @SelectProvider, como la describe Mohit, funciona bien. Solo se debe tener en cuenta que crea una nueva declaración cada vez que ejecutamos la consulta con diferentes parámetros dentro de la cláusula IN en lugar de reutilizar el PreparedStatement existente (ya que los parámetros dentro de la IN-Clause están siendo codificados dentro del SQL en lugar de ser establecer como parámetros de la declaración preparada). Esto a veces puede provocar pérdidas de memoria en la base de datos (ya que el DB necesita almacenar más y más declaraciones preparadas y potencialmente no reutilizará los planes de ejecución existentes).

Se puede intentar mezclar @SelectProvider y custom typeHandler. De esta forma, uno puede usar @SelectProvider para crear una consulta con tantos marcadores de posición dentro de "IN (...)" como sea necesario y luego reemplazarlos todos en el Administrador de tipos personalizado. Sin embargo, se pone un poco complicado.


2
2017-08-21 10:26



En mi proyecto, ya estamos usando Google Guava, por lo que es un atajo rápido.

public class ListTypeHandler implements TypeHandler {

    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, Joiner.on(",").join((Collection) parameter));
    }
}

1
2018-05-23 15:41



En Oracle, uso una variante de Tokenizer de Tom Kyte para manejar tamaños de lista desconocidos (dado el límite 1k de Oracle en una cláusula IN y el agravante de hacer múltiples INs para evitarlo). Esto es para varchar2, pero se puede personalizar para los números (o simplemente puede confiar en Oracle sabiendo que '1' = 1 / shudder).

Suponiendo que pase o realice encantamientos myBatis para obtener ids como una cadena, para usarlo:

select @Select("SELECT * FROM blog WHERE id IN (select * from table(string_tokenizer(#{ids}))")

El código:

create or replace function string_tokenizer(p_string in varchar2, p_separator in varchar2 := ',') return sys.dbms_debug_vc2coll is
    return_value SYS.DBMS_DEBUG_VC2COLL;
    pattern varchar2(250);
begin
    pattern := '[^(''' || p_separator || ''')]+' ;

    select
        trim(regexp_substr(p_string, pattern, 1, level)) token
    bulk collect into
        return_value
    from
        dual
    where
        regexp_substr(p_string, pattern, 1, level) is not null
    connect by
        regexp_instr(p_string, pattern, 1, level) > 0;

    return return_value;
end string_tokenizer;

0
2018-06-24 12:54



Puede usar un manejador de tipo personalizado para hacer esto. Por ejemplo:

public class InClauseParams extends ArrayList<String> {
   //...
   // marker class for easier type handling, and avoid potential conflict with other list handlers
}

Registre el siguiente controlador de tipo en su configuración MyBatis (o especifique en su anotación):

public class InClauseTypeHandler extends BaseTypeHandler<InClauseParams> {

    @Override
    public void setNonNullParameter(final PreparedStatement ps, final int i, final InClauseParams parameter, final JdbcType jdbcType) throws SQLException {

        // MySQL driver does not support this :/
        Array array = ps.getConnection().createArrayOf( "VARCHAR", parameter.toArray() );
        ps.setArray( i, array );
    }
    // other required methods omitted for brevity, just add a NOOP implementation
}

Puede usarlos así

@Select("SELECT * FROM foo WHERE id IN (#{list})"
List<Bar> select(@Param("list") InClauseParams params)

Sin embargo, esto no trabajo para MySQL, porque el conector MySQL no es compatible setArray() para declaraciones preparadas.

Una posible solución para MySQL es usar FIND_IN_SET en lugar de IN:

@Select("SELECT * FROM foo WHERE FIND_IN_SET(id, #{list}) > 0")
List<Bar> select(@Param("list") InClauseParams params)

Y su manejador de tipos se convierte en:

@Override
    public void setNonNullParameter(final PreparedStatement ps, final int i, final InClauseParams parameter, final JdbcType jdbcType) throws SQLException {

        // note: using Guava Joiner! 
        ps.setString( i, Joiner.on( ',' ).join( parameter ) );
    }

Nota: no sé el rendimiento de FIND_IN_SET, prueba esto si es importante


0
2018-05-31 11:51