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, Feb 09 '14 at 20:57
Then what would be a way out @Marc-B. Thanks for prompt reply — Nawaz, Feb 09 '14 at 21:00

score 6 · Accepted Answer · edited Jan 01 '22 at 09:06

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 01 '22 at 09:06

Mark Rotteveel

100,966
191
140
197

answered Feb 09 '14 at 21:01

Mureinik

297,002
52
306
350

I tried that way out. the problem being table names do have /,-, etc then howto escape those slashes and dashes – Nawaz Feb 09 '14 at 21:03
MySQL allows you to escape object names (e.g. tables, columns) with forward quotes. I've edited my answer accordingly. – Mureinik Feb 09 '14 at 21:04
@Mureinik, How can I join the table where I am using table name as variable – Malav Shah Aug 04 '15 at 04:22
@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. – Mureinik Aug 04 '15 at 05:17
6

Vulnerable to SQL injection! Don't copy and past this, kids! – SigmaX Apr 27 '16 at 16:00

Ahmed Mera · Answer 2 · 2021-12-31T21:12:24.890

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 '21 at 21:12

answered Aug 03 '20 at 13:49

Ahmed Mera

15
4

2

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. – FluffyKitten Aug 03 '20 at 19:57

score -1 · Answer 3 · edited Aug 23 '23 at 03:59

-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 '23 at 03:59

Abra

19,142
7
29
41

answered Nov 25 '21 at 12:56

subani

1
1

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](/help/how-to-answer). – Community Nov 25 '21 at 16:23
Even though your answer is code only, I don't see how the code you posted answers the original question. – Abra Aug 23 '23 at 03:58

How to use dynamic table name in SELECT query using JDBC

3 Answers3

Linked

Related