Clever Geek Handbook

Create dynamic SQL with abnormal

I don't want to delete and re-insert every row, so I used the following to try and use multiple anomalous rows:

SQL("""
            delete from PERSON_ROLES 
            WHERE person_id = {userId}
            and role_id not in ({rolescommastring})
            )
            """).on('userId -> userId,
                    'rolescommastring -> rolescommastring).execute()

The above line is not like it and I get:

c.j.b.PreparedStatementHandle - delete from PERSON_ROLES WHERE person_id = 1460 and role_id not in ( '1, 3, 8, 9' )

Is it possible to create dynamic sql with anorms?

+3


source to share


2 answers


Anorm does not support 'IN' clauses. This is the same for most ORMs (like scala - slick) as prepared statements do not support IN clauses

The process I'm using is https://groups.google.com/forum/#!topic/play-framework/qls6dhhdayc/discussion



Basically something like this should work

val params = List(1, 3, 8, 9) 
val paramsList = for ( i <- 0 until params.size ) yield ("role_id" + i)

SQL("""
        delete from PERSON_ROLES 
        WHERE person_id = {userId}
        and   role_id not in ({%s})
        )
        """.format(paramsList.mkString("},{"))).on('userId -> userId ++ 
                paramsList.zip(params)).execute()
0


source


Since Anorm 2.3, multivalued parameters are supported.

SQL("""
        delete from PERSON_ROLES 
        WHERE person_id = {userId}
        and   role_id not in ({rolescommastring})
        )
        """).on('userId -> userId,
                'rolescommastring -> Seq("id1", "id2", "id3")).execute()


There are more similar examples at https://www.playframework.com/documentation/2.3.x/ScalaAnorm

+4


source






More articles:


All Articles