How to query Oracle via JDBC for an intersection

Question

I need to check from Java if a certain user has at least one group membership. In Oracle (12 by the way) there is a big able that looks like this:

DocId  | Group
-----------------
    1  | Group-A
    1  | Group-E
    1  | Group-Z
    2  | Group-A
    3  | Group-B
    3  | Group-W

In Java I have this information:

docId = 1
listOfUsersGroups = { "Group-G", "Group-A", "Group-Of-Something-Totally-Different" }

I have seen solutions like this, but this is not the approach I want to go for. I would like to do something like this (I know this is incorrect syntax) ...

SELECT * FROM PERMSTABLE WHERE DOCID = 1 AND ('Group-G', 'Group-A', 'Group-Of-Something-Totally-Different' ) HASATLEASTONE OF Group

... and not use any temporary SQL INSERTs. The outcome should be that after executing this query I know that my user has a match because he is member of Group-A.

score 1 · Accepted Answer · answered Dec 10 '18 at 12:41

1

You can do this (using IN condition):

SELECT * FROM PERMSTABLE WHERE DocId = 1 AND Group IN 
            ('Group-G', 'Group-A', 'Group-Of-Something-Totally-Different')

answered Dec 10 '18 at 12:41

karthik manchala

13,492
1
31
55

How would I set the content of `IN` using jdbc syntax ? – Marged Dec 10 '18 at 12:45
@Marged check [this](https://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause) – karthik manchala Dec 10 '18 at 12:47

How to query Oracle via JDBC for an intersection

1 Answers1