Using in Clause in a Native SQL Query

Using IN clause in a native sql query

JPA supports named list parameters, in your case:

@NamedQuery(
    name="fooQuery",
    queryString="select f from Foo f where f.status in (?1)"
)

Query q = entityManager.createNamedQuery("fooQuery");

List<String> listParameter = new ArrayList<>();
listParameter.add("NEW");
listParameter.add("OLD");

q.setParameter(1, listParameter); 
return q.getResultList();

Using IN clause in a native sql query with Hibernate 3.2.2

Answered my own question and should have RTFM'd before posting. The "trick" is to use query.setParameterList() as opposed to query.setParameter().

How to write IN clause in hibernate native(createNative) query?

You should consider using named parameter bindings instead of adding the parameters directly by concatenation as follows:

Query query =  
    entityManager.createNativeQuery("select *"
        + " from merchant_transactions mt" 
        + " inner join appl_merchant_txn_statuses mts on mt.merchant_txn_status_id = mts.merchant_txn_status_id"
        + " where mt.customer_id = :customer_id"
        + " and mt.merchant_transaction_type_id = :merchant_transaction_type_id"
        + " and mt.merchant_txn_status_id in (:status_ids)", MerchantTransaction.class);

query.setParameter("customer_id", _merchantTransaction.getCustomerId());
query.setParameter("merchant_transaction_type_id", _merchantTransaction.getMerchantTransactionType().getMerchantTransactionTypeId());
query.setParameter("status_ids", statusIds);

Advantages of named parameter bindings as described in hibernate best-practices here:

you do not need to worry about SQL injection,
Hibernate maps your query parameters to the correct types and
Hibernate can do internal optimizations to provide better
performance.

As a side note, I see that you are using JPA's Entity Manager createNativeQuery, so this will work:
query.setParameter("status_ids", statusIds);

If you were using Hibernate createSQLQuery, you need this:

query.setParameterList("status_ids", statusIds);

JPA passing list to IN clause in named native query

A list is not a valid parameter for a native SQL query, as it cannot be bound in JDBC. You need to have a parameter for each argument in the list.

where u.user_id in (?id1, ?id2)

This is supported through JPQL, but not SQL, so you could use JPQL instead of a native query.

Some JPA providers may support this, so you may want to log a bug with your provider.

Native query IN clause throwing sql error

JPA doesn't support array parameters for native queries. Either rewrite your code to use JPQL, or set each entry for IN clause like it's described in the following answer: JPA passing list to IN clause in named native query

How to use a dynamic parameter in a IN clause of a JPA named query?

JPA support the use of a collection as a list literal parameter only in JPQL queries, not in native queries. Some JPA providers support it as a proprietary feature, but it's not part of the JPA specification (see https://stackoverflow.com/a/3145275/1285097).

Named parameters in native queries also aren't part of the JPA specification. Their behavior depends on the persistence provider and/or the JDBC driver.

Hibernate with the JDBC driver for Oracle support both of these features.

List<String> selectedValues = Arrays.asList("STRING1", "STRING2");
final String parameterizedQuery = "select * from SOMETABLE where SOMEFIELD in (:selectedValues)";
return em.createNativeQuery(parameterizedQuery)
         .setParameter("selectedValues", selectedValues)
         .getResultList();

Symfony2.3 raw sql query with IN Clause

Answer:

List of Parameters Conversion
DoctrineDBALTypes Conversion

So there are at least two mistakes you did. The first is what @Alarid said: you should not implode your array. The second is that you have to use DoctrineDBALTypes Conversion for IN clause when running a prepared statement.

And finally your query goes this:

$stmt = $this->getDoctrine()->getEntityManager()
        ->getConnection()
        ->prepare('SELECT t1.id , t1.name , t2.start_date , t2.end_date
        FROM table1 t1 , table2 t2
        WHERE t1.id = t2.matchId AND  t1.id IN (:ids)');

$stmt->bindValue('ids', $idSArray, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
$stmt->execute();

Or alternative:

$stmt = $this->getDoctrine()->getEntityManager()
    ->getConnection()
    ->executeQuery('SELECT t1.id , t1.name , t2.start_date , t2.end_date
        FROM table1 t1 , table2 t2
        WHERE t1.id = t2.matchId AND  t1.id IN (:ids)',
        array('ids' => $idSArray),
        array('ids' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
    )
;