How to use a tablename variable for a java prepared statement insert
You can't. You need to contruct the sql with string concatenation/placeholder with String.format. prepared statement is for the column values not for table name.
Using Prepared Statements to set Table Name
A table name can't be used as a parameter. It must be hard coded. So you can do something like:
private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [" + reportDate + "?]";
How to pass table name to a Prepared Statement in a SELECT COUNT query?
You can only bind values in a PreparedStatement
, not syntactic elements or object names (in this case, the table name). You'll have to resort to string manipulation:
final String query = String.format("SELECT COUNT(*) FROM %s", tablename);
final PreparedStatement preparedStatement = connection.prepareStatement(query);
final ResultSet resultSet = preparedStatement.executeQuery();
Note that there are no placeholders in this query, so it's questionable whether there's really any advantage in using a PreparedStatement
as opposed to a plain old Statement
.
prepared statement - using parameter to specify table name
It is not possible. The prepare statement is persistent execution plan - and execution plan contains pined source of data - so tables, column names cannot be mutable there.
When you change table, columns, then you change the semantic of query - you will got different execution plan and then this behave is not possible in prepared statements. The main use case of prepared statements is reusing of execution plans - plan once, execute more. But there are some principal limits - only some parameters can be changed.
Can I parameterize the table name in a prepared statement?
Short answer to your question is "no".
In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement.
One way of thinking of this is "things that can be substituted at runtime execution of the statement without altering its meaning". The table name(s) is not one of those runtime values, as it determines the validity of the SQL statement itself (ie, what column names are valid) and changing it at execution time would potentially alter whether the SQL statement was valid.
At a slightly higher level, even in database interfaces that emulate prepared statement parameter substitution rather than actually send prepared statements to the database, such as PDO, which could conceivably allow you to use a placeholder anywhere (since the placeholder gets replaced before being sent to the database in those systems), the value of the table placeholder would be a string, and enclosed as such within the SQL sent to the database, so SELECT * FROM ?
with mytable
as the param would actually end up sending SELECT * FROM 'mytable'
to the database, which is invalid SQL.
Your best bet is just to continue with
SELECT * FROM {$mytable}
but you absolutely should have a white-list of tables that you check against first if that $mytable
is coming from user input.
Safe way to use table name as parameter in JDBC query
I would try to solve the design problem, so you don't have to set the table name dynamically. If this is not possible, I would go for a design where you manage a list of available tables and users pick one from there, BY ID, so you can retrieve the real table name from the chosen id and replace the table name placeholder with it, avoiding any chance of sql injection in the table name replacement.
Table name as parameter using PDO/MySQL prepared statement
Table and Column names cannot be replaced by parameters in PDO.
see Can PHP PDO Statements accept the table or column name as parameter?
Related Topics
Find Combination(S) Sum of Element(S) in Array Whose Sum Equal to a Given Number
Laravel: Property [Name] Does Not Exist on This Collection Instance
Session Destroy When One Browser Tab Gets Closed
Internal Server Error While Run Laravel on Localhost
How to Insert Md5 Hash Value in Ms SQL Server
Laravel - Getting Current Month and Year Only
How to Compare Comma Separated Ids on Left Join
How to Install Extension for PHP Via Docker-Php-Ext-Install
Increase a Value Like = +1 in MySQL and PHP
Trim Country Code from Phone Number in PHP
How to Store Values from Foreach Loop into an Array
Getting Date Format M-D-Y H:I:S.U from Milliseconds