CODEWITHSUNDEEP
sqljdbc
Milan
Milan

Reputation: 981

SQLSyntaxError using setString JDBC on preparedStatement

I want to query a simple 'SELECT * FROM '. I made a method:

String sql = 
            "SELECT ? " +
            "FROM ?";
    try (
            PreparedStatement stmt = conn.prepareStatement(sql);
            ){

        stmt.setString(1, "*");
        stmt.setString(2, "nuclide_kinds"); 

        System.out.println(stmt.toString());
        ResultSet rs = stmt.executeQuery();
    }

When I want to execute this, it gives a SQLSyntaxError. The toString() prints: SELECT '*' FROM 'nuclide_kinds'.

How does this come? Are the magical extra ' the cause?

Upvotes: 0

Views: 64

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109147

You simply cannot use parameters for specifying object names like table or column names.

That the MySQL JDBC driver seems to build an almost correct query is because by default it replaces parameters inside the driver, however as those parameters are strings it quotes (and escapes) them making them unusable as object names. Most other database drivers send the original query string for prepare to the server and only send the parameter values on execute. However that distinction doesn't matter for this problem: you can't parametrize object names.

If you want to dynamically control the object names, then you need to concatenate them in the query string yourself, or use a solution like for example jOOQ, see for example jOOQ as a SQL builder.

Upvotes: 1

Related Questions