Is it possible to pass attribute name of table itself as a string in mysql?

Question

I have a table with attributes id and name1,name2,name3.....name10, where all these ten names belong to the id.

id   name1 name2 name3 name4 name5 name6 name7 name8 name9 name10
201  ijk   lmn   xyz   abc   efg   hks   dkm   jjl   dkn   awt
202  mjl   pan   van   slm   tko   kds   jar   slk   dkf   asd

Now I need to retrieve the id, whichever the name among 10 names was encountered. If it is a print statement, it would be very easy to increment the numbers using the loop by keeping the string "name" as it is. But how to do it when using the mysql statement? First of all is it even possible to pass the attribute name as a string?

eg: when passing string for where condition,

String uname=jf1.getText();
PreparedStatement stmt=conn.prepareStatement("Select * from staff where id = ?");
stmt.setString(1,uname);
ResultSet rs=stmt.executeQuery();
rs.next();

But when I need to check all attributes from name1 to name10, can it be like this?

String uname=jf1.getText();
PreparedStatement stmt=conn.prepareStatement("Select id from staff where ?=?");
for(int i=1;i<=10<i++)
stmt.setString(1,"name"+i);
stmt.setString(2,uname);

Where uname is given by user through text field.

@Luiggi Mendoza, how is it possible for you to edit the title at the same moment I press the post question button? — learner
– learner, Commented Nov 21, 2014 at 15:49
That's not possible. You posted your question at 15:46:49Z and my edit was at 15:47:02Z. There were some seconds of difference :) — Luiggi Mendoza
– Luiggi Mendoza, Commented Nov 21, 2014 at 15:52

Mooolo · Accepted Answer · 2014-11-21 16:11:33Z

1

Wouldn't be easier with a IN !

SELECT
   id
FROM
   staff
WHERE
   ? IN (name1, name2, name3, name4, name5,
         name6, name7, name8, name9, name10);

answered Nov 21, 2014 at 16:11

Mooolo

4282 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

learner Over a year ago

This method helped in the an easy way.

Sergey Kalinichenko · Accepted Answer · 2014-11-21 15:56:27Z

1

No, this is not possible: neither table name nor table attribute can be passed as a parameter value. If you run the code with ?=?, JDBC would instruct RDBMS to compare the two values ("name"+i and uname) to get the result.

In this situation you need to generate the query dynamically. This is fine, assuming that attribute names are properly sanitized.

An alternative could be a statement that covers all ten columns, like this:

PreparedStatement stmt=conn.prepareStatement(
    "select * from staff where ? = case ? when 1 then name1 when 2 then name2 when 3 then name3 ... else null end"
);

Now you can bind the value to the first parameter, and the number of name# column to the second parameter.

edited Nov 21, 2014 at 15:56

answered Nov 21, 2014 at 15:49

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

5 Comments

learner Over a year ago

I think the sentence should be "neither table attribute nor the table name". Anyway, then how can I accomplish this operation?

learner Over a year ago

So I assume stmt.setString should be assigned upto 10 as well. eg: stmt.setString(1,name1); stmt.setString(2,name2);....stmt.setString(10,name10);

Sergey Kalinichenko Over a year ago

@learner It should be stmt.setInt(2, i) because case compares the ? parameter to integers. uname is at index 1 now. The rest of your code remains the same - i.e. there are only two parameters.

learner Over a year ago

This way of querrying will not work if at any time additional attribute(like name11 or address etc) was added right?

Sergey Kalinichenko Over a year ago

@learner Correct - adding a new attribute name would require you to change the SQL for the query. You could potentially make it configurable to avoid a recompile, but it's likely that you would need to recompile anyway to support the rest of the functionality associated with the newly added attribute. You can also generate the SQL with a simple loop producing a CASE expression that gets embedded in the rest of the query.

Collectives™ on Stack Overflow

Is it possible to pass attribute name of table itself as a string in mysql?

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related