CODEWITHSUNDEEP
javamysqldatabasejdbc
masud.m
masud.m

Reputation: 155

Dynamic column in sql/jdbc query

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?

Upvotes: 2

Views: 2723

Answers (2)

Freak
Freak

Reputation: 6873

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
.

Upvotes: 3

Christophe Schutz
Christophe Schutz

Reputation: 613

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.

Upvotes: 0

Related Questions