How to use dynamic table name in SELECT query using JDBC

Question

I have 5 or table table to query from \

my syntax i like this

String sql2 = "SELECT * FROM ? WHERE Patient_ID = ?";

pst = conn.prepareStatement(sql2);

        System.out.println("SQL before values are set "+sql2);
        System.out.println("The values of table/test name recieved in TestPrint stage 1 "+tblName);
        System.out.println("The values of test name recieved in TestPrint stage 1 "+key);
        // values are outputted correctly but are not getting set in the query

        pst.setString(1, tblName);
        pst.setLong(2, key);
ResultSet rs2 = pst.executeQuery(sql2);

        while(rs2.next()){

            String ID = rs2.getString("ID");

            jLabel35.setText(ID);
            jLabel37.setText(ID);
            jLabel38.setText(ID);
       // them print command is initiated to print the panel
}

The problem is when i run this i get an error saying ".....you have and error in SQL syntax near ? WHERE Patient_ID = ?"

When i output the sql using system.out.println(sql2);

values are not set in sql2

mysql placeholders can only replace VALUES. you cannot use placeholders for table/field names, or other sql "meta" directives. e.g. sql = '? ? ? ?' -> 'select', '*', 'from', 'mytable' is outright illegal. none of those four things are placeholding a VALUE — Marc B
– Marc B, Commented Feb 9, 2014 at 20:57
Then what would be a way out @Marc-B. Thanks for prompt reply — Nawaz
– Nawaz, Commented Feb 9, 2014 at 21:00

Mark Rotteveel · Accepted Answer · 2022-01-01 09:06:13Z

6

When you prepare a statement, the database constructs an execution plan, which it cannot do if the table is not there. In other words, placehodlers can only be used for values, not for object names or reserved words. You'd have to rely on Java to construct your string in such a case:

String sql = "SELECT * FROM `" + tblName + "` WHERE Patient_ID = ?";

pst = conn.prepareStatement(sql);
pst.setLong(1, key);
ResultSet rs = pst.executeQuery();

edited Jan 1, 2022 at 9:06

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

answered Feb 9, 2014 at 21:01

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

5 Comments

Nawaz Over a year ago

I tried that way out. the problem being table names do have /,-, etc then howto escape those slashes and dashes

Mureinik Over a year ago

MySQL allows you to escape object names (e.g. tables, columns) with forward quotes. I've edited my answer accordingly.

Malav Shah Over a year ago

@Mureinik, How can I join the table where I am using table name as variable

Mureinik Over a year ago

@malavshah it's very inconvenient to give a proper answer in the comments like this. If you have another question, please post a new question with all the relevant details.

SigmaX Over a year ago

Vulnerable to SQL injection! Don't copy and past this, kids!

Ahmed Mera · Accepted Answer · 2021-12-31 21:12:24Z

0

String sqlStatment = "SELECT * FROM " + tableName + " WHERE Patient_ID = ?";

PreparedStatement preparedStatement = conn.prepareStatement(sqlStatment);
preparedStatement.setint(1, patientId);
ResultSet resultSet = preparedStatement.executeQuery();

edited Dec 31, 2021 at 21:12

answered Aug 3, 2020 at 13:49

Ahmed Mera

6475 silver badges14 bronze badges

1 Comment

FluffyKitten Over a year ago

Welcome to Stack Overflow. Code-only answers are discouraged on Stack Overflow because they don't explain how it solves the problem. Please edit your answer to explain what this code does, how it answers the question and how it improves on the accepted answer, so that it is useful to the OP as well as other users also with similar issues.

mipo256 · Accepted Answer · 2024-09-20 15:25:46Z

0

The accepted answer is of course vulnerable to SQL injection. The truth is - the only 100% reliable way of dealing with SQL injection is PreparedStatement usage, which cannot be used in this case. So any possible solution which is already posted here or will be posted here would be vulnerable to SQL injection.

So, if you really have to do this, then you absolutely need to make sure that the abstract tableName is coming from the trusted source. Otherwise, seek for another approach, and trust me, it always exists.

answered Sep 20, 2024 at 15:25

mipo256

3,3661 gold badge17 silver badges39 bronze badges

Comments

Abra · Accepted Answer · 2023-08-23 03:59:37Z

-1

public void getByIdEmployer() throws SQLException {
    Connection con = null;
    try {
        con = jdbcUtil.connectionDtls();
        PreparedStatement ptst = con.prepareStatement(getById);
        ptst.setInt(1, 4);
        ResultSet res = ptst.executeQuery();
        while (res.next()) {
            int empid = res.getInt(1);
            System.out.println(empid);
            String name = res.getString(2);
            System.out.println(name);
            int salary = res.getInt(3);
            System.out.println(salary);
            String location = res.getString(4);
            System.out.println(location);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        con.close();
    }
}

edited Aug 23, 2023 at 3:59

Abra

21k14 gold badges103 silver badges75 bronze badges

answered Nov 25, 2021 at 12:56

subani

11 bronze badge

2 Comments

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Abra Over a year ago

Even though your answer is code only, I don't see how the code you posted answers the original question.

Collectives™ on Stack Overflow

How to use dynamic table name in SELECT query using JDBC

4 Answers 4

5 Comments

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related