CODEWITHSUNDEEP
javamysqlselectdynamictablename
Nawaz
Nawaz

Reputation: 413

How to use dynamic table name in SELECT query using JDBC

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

Upvotes: 3

Views: 15761

Answers (4)

mipo256
mipo256

Reputation: 3140

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.

Upvotes: 0

subani
subani

Reputation: 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();
    }
}

Upvotes: -1

Mureinik
Mureinik

Reputation: 311073

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();

Upvotes: 6

Ahmed Mera
Ahmed Mera

Reputation: 583

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

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

Upvotes: 0

Related Questions