Dynamic column in sql/jdbc query

Question

I am a student of Computer Science. Now I am working on a project with jdbc. I have to table in database - USER, ROLE.

Where each USER have a one or multiple ROLE. I save the ROLE.ID (primary key or ROLE) in USER.ROLE_ID column.

In java code level I have two entity class also - User.java and Role.java. I can make the simple SQL query with joining these tables.

Please see the following queries -

1. Selecting USER.NAME and ROLE.NAME for USER.NAME='admin' -

SELECT USER.NAME, ROLE.NAME FROM USER, ROLE
   WHERE USER.ROLE_ID = ROLE.ROLE_ID
   AND USER.NAME='admin';

2. Selecting USER.ID and ROLE.ID for the USER.NAME='admin' -

SELECT USER.ID, ROLE.ID FROM USER, ROLE
   WHERE USER.ROLE_ID = ROLE.ROLE_ID
   AND USER.NAME='admin';

I have to make 2 different queries ONLY for the different columns I am selecting. Here most of the query is same. My question is can I do something so that I can dynamically select different (in first case - USER.NAME, ROLE.NAME and Second case USER.ID, ROLE.ID) types of column using a single query?

You can use all those 4 columns in single query. What else do you need? — Raman Shrivastava
– Raman Shrivastava, Commented Jul 7, 2015 at 9:19

Freak · Accepted Answer · 2015-07-07 09:37:15Z

No, You will not have anything like this from JDBC.You have to write your custom function for this.
In my opinion, you should just make a function which is taking two parameters OR the same overloaded functions and then provide them arguments.
here I am providing you some pseudo code for logic

        function getData(Param tableName, Param column1, Param column2){
        String sql = "SELECT "+column1+"','"+column2+"' 
                FROM `" + tableName + "` WHERE "+column1+" = "+column2;
        }

//overloaded
       function getData(Param tableName, Param column1, Param column2,Param column3){
        String sql = "SELECT "+column1+"','"+column2+"','"column3+"' 
                FROM `" + tableName + "` WHERE "+column1+" = "+column2;
        }

But if you are ready to use some APIs then I can suggest you two very good API to win your scenario. you should give a try to
Querydsl
JOOQ
Apache ddlUtils
squiggle-sql
JaQu
.

Madhawa Priyashantha · Accepted Answer · 2015-07-07 09:21:44Z

0

What about

SELECT USER.ID, USER.NAME, ROLE.ID, ROLE.NAME FROM USER, ROLE
   WHERE USER.ROLE_ID = ROLE.ROLE_ID
   AND USER.NAME='admin';

?

I don't really get the question here to be honest.

edited Jul 7, 2015 at 9:21

Madhawa Priyashantha

9,8907 gold badges38 silver badges66 bronze badges

answered Jul 7, 2015 at 9:20

Christophe Schutz

7135 silver badges21 bronze badges

1 Comment

Despicable Over a year ago

if you have some knowledge of databases or JDBC , then it is not a difficult question. He wants something like prepared statement but in some dynamic fashion

Collectives™ on Stack Overflow

Dynamic column in sql/jdbc query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related