How to retrieve a set of table data in SQL?

Question

Hi, everyone,

I want to ask a question about the SQL. I have the following table.

Name Phone
 a    1
 b    2
 c    3
 d    4
 e    5

I have an ArrayList(java) which contains the [a,b,c,d,e]. Is it possible to put the ArrayList into the mySQL statement to retrieve the all the phone numbers? Thank you.

Answer 1

you have to create your own statement per java. iterate over the arraylist and add every string element to the statement.

Answer 2

You could create an IN clause for all the names and map the result set to your data:

SELECT Name, Phone FROM Numbers WHERE Name IN ('a','b','c','d','e')

I'm sure there are already libraries that do the needed work for you. You could start at http://www.hibernate.org/ with your research.

Answer 3

Read on SQL IN(...,...,...) operator.

Answer 4

I don't think there is a direct way to do this.
But you have the following options:

1. You can use the IN clause. Create the IN clause dynamically by looping over the list and creating a string. Note: Oracle limits the number of entries in the IN clause to 1000.

2. You can insert the values into a (temp) table and use this table in your SQL, in a IN clause or as a JOIN (I prefer the join)

3. You can have a PL/SQL stored procedure that takes in a (oracle) Array as parameter. And loop through this array in the stored proc. See this for an example.